The column's output format.
Usage
Set_Property(OLECtrlEntID, "OLE.ColumnFormat[col]", StringValue)
Values
StringValue can be any string meeting the following format requirements:
Syntax: Depends on the column type. See Remarks for details.
Default: ""
Indices
| Index | Description |
|---|---|
| col | The index to a column in the report table |
Remarks
The ColumnFormat property determines how the column's data is displayed. The syntax of this field depends upon the column Type. See ColumnList for possible types. Listed below are the supported types and their respective format syntax.
Text
You can format text, but you're limited to prefixes and suffixes. You can use any characters in the Format field, but the string "%S%" will always be swapped with the cell's contents. Any other character is treated as a literal and is displayed as is. Currently, there is no way to show "%S%" as a literal, i.e., it will alwaysbe swapped with the cell's contents.
| To Display As | Use |
|---|---|
| First Name: Kevin | First Name: %S% |
| Kevin Esquire | %S% Esquire |
| Code guru Kevin works for SRP Computer Solutions, Inc. | Code guru %S% works for SRP Computer Solutions, Inc. |
Number
Numbers are stored as floating point values within the Report Table. Thus, if your data contains decimal values, make sure you include the decimal when you load the Report Table. In other words, there is no equivalent MD2 format for the Report Table. Instead, the Report Table uses an Excel-like syntax to format and round off decimal numbers.
You can use any characters in the Format field, but '#', '0', commas, and periods are reserved characters. Any other character is treated as a literal and is displayed as is. Thus, you may use literal characters in your format to prefix or suffix the data as you choose. If you need to use any reserved characters literally, then surround your literal text in single or double quotes. If you need to display quotes, place two of them side by side. The reserved characters are interpreted as follows:
| Character | Displays |
|---|---|
| # | Significant Digits Only |
| 0 | Significant Digits or Insignificant Zeros |
| comma | 1. If within the number, then commas separate every three digits |
| 2. If trailing the number, then the number is shown as an integer divided by 1000 for each trailing comma | |
| period | Decimal point. The first decimal denotes the beginning of the fractional portion of the number. All subsequent decimals are treated as literals |
Here are some example number formats:
| To Display | As | Use |
|---|---|---|
| 1234.59 | 1234.6 | ####.# |
| 8.9 | 8.900 | #.000 |
| .631 | 0.6 | 0.# |
| 44.398 | $44.40 | $0.00 |
| 1234.568 | $1,234.57 | $#,##0.00 |
| 5.25 | 5.25% | 0.00% |
| 12000 | 12,000 | #,### |
| 12000 | 12 | #, |
| 12200000 | 12.2 | 0.0,, |
Date
Dates are stored in a proprietary format within the Report Table. Currently, dates must be in OI's internal format when loading them into the Report Table. In other words, the Report Table will not parse string dates on your behalf. Like Numbers, the Report Table uses an Excel-like syntax to format dates.
You can use any characters in the Format field, but 'Y', 'y', 'M', 'm', 'D', and 'd' are reserved characters. Any other character is treated as a literal and is displayed as is. Thus, you may use literal characters in your format to prefix or suffix the data as you choose. If you need to use any reserved characters literally, then surround your literal text in single or double quotes. If you need to display quotes, place two of them side by side. The reserved characters are interpreted as follows:
| Character(s) | Displays |
|---|---|
| YY | Years 00-99 |
| YYYY | Years 0001-2999 |
| M | Months 1-12 |
| MM | Months 01-12 |
| MMM | Months Jan-Dec |
| MMMM | Months January-December |
| MMMMM | Months J-D |
| D | Days 1-31 |
| DD | Days 01-31 |
| DDD | Days Sun-Sat |
| DDDD | Days Sunday-Saturday |
Only uppercase letters are used in the examples here, but lowercase are supported as well. However, uppercase is recommended because DateTime (below) uses capital 'M' to distinguish months from minutes.
Here are some example date formats, all them for the date January 1, 2006:
| To Display As | Use |
|---|---|
| January 1, 2006 | MMMM D, YYYY |
| Sunday, Jan 01, 2006 | DDDD, MMM DD, YYYY |
| 2006-01-01 | YYYY-MM-DD |
| 1/1/06 | M/D/YY |
| Happy New Year! It's January 01! | "Happy New Year! It's" MMMM DD! |
Time
Times are stored in a proprietary format within the Report Table. Currently, times must be in OI's internal format when loading them into the Report Table. In other words, the Report Table will not parse string time on your behalf. The Report Table uses an Excel-like syntax to format dates.
You can use any characters in the Format field, but 'H', 'h', 'M', 'm', 'S', 's', 'A', 'a', and '0' are reserved characters. Any other character is treated as a literal and is displayed as is. Thus, you may use literal characters in your format to prefix or suffix the data as you choose. If you need to use any reserved characters literally, then surround your literal text in single or double quotes. If you need to display quotes, place two of them side by side. The reserved characters are interpreted as follows:
| Character(s) | Displays |
|---|---|
| h | Hours 0-23 |
| hh | Hours 00-23 |
| m | Minutes 0-59 |
| mm | Minutes 00-59 |
| s | Seconds 0-59 |
| ss | Seconds 00-59 |
| 0 | Milliseconds 0-9 (rounded to the nearest tenth) |
| 00 | Milliseconds 00-99 (rounded to the nearest hundredth) |
| 000 | Milliseconds 000-999 |
| a | a/p |
| aa | am/pm |
| aaa | a.m./p.m. |
| A | A/P |
| AA | AM/PM |
| AAA | A.M./P.M. |
Only lowercase letters are used in the examples here, but uppercase are supported as well. However, lowercase is recommended because DateTime (below) uses lowercase 'm' to distinguish minutes from months.
Here are some example time formats, all them for the time 3:30:30.345 PM:
| To Display As | Use |
|---|---|
| 3:30:30.345 PM | h:mm:ss.000 AA |
| 3:30:30.35 P.M. | h:mm:ss.00 AAA |
| 3:30:30.3 PM | h:mm:ss.0 AA |
| 03:30:30 P | hh:mm:ss A |
| 15:30:30 | h:mm:ss |
| 15:30 | h:mm |
| The meeting is at 3:30pm | "The meeting is at" h:mmaa |
DateTime
DateTimes are stored in a proprietary format within the Report Table. Currently, date & times must be in OI's internal format when loading them into the Report Table. In other words, the Report Table will not parse string date & times on your behalf. Like individual Dates and Times, the Report Table uses an Excel-like syntax for formatting.
You can use any characters in the Format field, but 'Y', 'y', 'M', 'm', 'D', 'd', 'H', 'h', 'S', 's', 'A', 'a', and '0' are reserved characters. Any other character is treated as a literal and is displayed as is. Thus, you may use literal characters in your format to prefix or suffix the data as you choose. If you need to use any reserved characters literally, then surround your literal text in single or double quotes. If you need to display quotes, place two of them side by side. The reserved characters are interpreted as follows:
| Character(s) | Displays |
|---|---|
| YY | Years 00-99 |
| YYYY | Years 0001-2999 |
| M | Months 1-12 |
| MM | Months 01-12 |
| MMM | Months Jan-Dec |
| MMMM | Months January-December |
| MMMMM | Months J-D |
| D | Days 1-31 |
| DD | Days 01-31 |
| DDD | Days Sun-Sat |
| DDDD | Days Sunday-Saturday |
| h | Hours 0-23 |
| hh | Hours 00-23 |
| m | Minutes 0-59 |
| mm | Minutes 00-59 |
| s | Seconds 0-59 |
| ss | Seconds 00-59 |
| 0 | Milliseconds 0-9 (rounded to the nearest tenth) |
| 00 | Milliseconds 00-99 (rounded to the nearest hundredth) |
| 000 | Milliseconds 000-999 |
| a | a/p |
| aa | am/pm |
| aaa | a.m./p.m. |
| A | A/P |
| AA | AM/PM |
| AAA | A.M./P.M. |
Note that 'M' must be used for months and 'm' for minutes. All other codes can be either case.
Here are some example date & time formats, all them for the date & time January 1, 2006 at 3:30:30.245 PM:
| To Display As | Use |
|---|---|
| January 1, 2006 at 3:30 PM | MMMM D, YYYY 'at' h:mm aa |
| Sunday, Jan 01, 2006 | DDDD, MMM DD, YYYY |
| 2006-01-01 15:30:30 | YYYY-MM-DD hh:mm:ss |
Bool
Bools are stored as 1 or 0 within the Report Table, and booleans must be in this format when loading them into the Report Table. In other words, the Report Table will not parse string booleans on your behalf, such as "Yes," "No," "True" or "False." The Report Table uses a simple syntax for formatting.
You can use any characters in the Format field, but '[', '|', and ']' are reserved characters. Any other character is treated as a literal and is displayed as is. Thus, you may use literal characters in your format to prefix or suffix the data as you choose. If you need to use any reserved characters literally, then surround your literal text in single or double quotes. If you need to display quotes, place two of them side by side. The reserved characters are interpreted as follows (assuming a value of 1):
| To Display As | Use |
|---|---|
| True | [True|False] |
| Yes | [Yes|No] |
| T | [T|F] |
| The value is: True | "The value is:" [True|False] |
| Use quotes to show [, |, or ] when the value is True | "Use quotes to show [, |, or ] when the value is" [True|False] |
Example
// Set the date format for column 2 Set_Property(@Window:".OLE_REPORTTABLE", "OLE.ColumnFormat[2]", "MM/DD/YYYY")
