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

IndexDescription
colThe 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 AsUse
First Name: KevinFirst 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:

CharacterDisplays
#Significant Digits Only
0Significant Digits or Insignificant Zeros
comma1. 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
periodDecimal 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 DisplayAsUse
1234.591234.6####.#
8.98.900#.000
.6310.60.#
44.398$44.40$0.00
1234.568$1,234.57$#,##0.00
5.255.25%0.00%
1200012,000#,###
1200012#,
1220000012.20.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
YYYears 00-99
YYYYYears 0001-2999
MMonths 1-12
MMMonths 01-12
MMMMonths Jan-Dec
MMMMMonths January-December
MMMMMMonths J-D
DDays 1-31
DDDays 01-31
DDDDays Sun-Sat
DDDDDays 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 AsUse
January 1, 2006MMMM D, YYYY
Sunday, Jan 01, 2006DDDD, MMM DD, YYYY
2006-01-01YYYY-MM-DD
1/1/06M/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
hHours 0-23
hhHours 00-23
mMinutes 0-59
mmMinutes 00-59
sSeconds 0-59
ssSeconds 00-59
0Milliseconds 0-9 (rounded to the nearest tenth)
00Milliseconds 00-99 (rounded to the nearest hundredth)
000Milliseconds 000-999
aa/p
aaam/pm
aaaa.m./p.m.
AA/P
AAAM/PM
AAAA.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 AsUse
3:30:30.345 PMh:mm:ss.000 AA
3:30:30.35 P.M.h:mm:ss.00 AAA
3:30:30.3 PMh:mm:ss.0 AA
03:30:30 Phh:mm:ss A
15:30:30h:mm:ss
15:30h: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
YYYears 00-99
YYYYYears 0001-2999
MMonths 1-12
MMMonths 01-12
MMMMonths Jan-Dec
MMMMMonths January-December
MMMMMMonths J-D
DDays 1-31
DDDays 01-31
DDDDays Sun-Sat
DDDDDays Sunday-Saturday
hHours 0-23
hhHours 00-23
mMinutes 0-59
mmMinutes 00-59
sSeconds 0-59
ssSeconds 00-59
0Milliseconds 0-9 (rounded to the nearest tenth)
00Milliseconds 00-99 (rounded to the nearest hundredth)
000Milliseconds 000-999
aa/p
aaam/pm
aaaa.m./p.m.
AA/P
AAAM/PM
AAAA.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 AsUse
January 1, 2006 at 3:30 PMMMMM D, YYYY 'at' h:mm aa
Sunday, Jan 01, 2006DDDD, MMM DD, YYYY
2006-01-01 15:30:30YYYY-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 AsUse
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")

See Also

ColumnList

  • No labels