Interactive Viewer also enables you to specify a custom format for numeric, date‑and‑time, and string data, in cases where the available standard formats do not meet your requirements. To apply a custom format to data, you specify a format pattern.
Defining a custom number format
You can define a custom number format using special symbols to construct a format pattern. A format pattern shows where to place currency symbols, thousands separators, and decimal separators. Table 8‑4 shows examples of custom format patterns and their effects on numeric data.
Table 8‑4 Results of custom number formats
Format pattern
Data in the database
Result of formatting
0000.00
12.5 124.5 1240.553
0012.50 0124.50 1240.55
#.000
100 100.25 100.2567
100.000 100.250 100.257
$#,###
2000.00 20000.00
$2,000 $20,000
ID #
15
ID 15
Defining a custom date‑and‑time format
You can define a custom date‑and‑time format using special symbols, shown in Table 8‑5, to construct a format pattern. Use custom date formatting only for reports viewed in a single locale. Custom formats always display dates in the format you set, which can be inappropriate in other locales. For example, if you use the format MM‑dd‑yy, the date January 10, 2006 always appears as 01‑10‑06, regardless of the locale in which you view the report. For locales that customarily display dates in day‑month‑year format, Interactive Viewer interprets the date 01‑10‑06 as October 1, 2006.
Table 8‑5 Symbols for defining custom date‑and‑time formats
Symbol
Description
Example
yy
Short year
08
yyyy
Long year
2008
MM
Month as a number
07
MMM
Short month name
Jul
MMMM
Full month name
July
d
Day in month
10
W
Week in month
2
w
Week in year
28
DD
Day in year
192
E
Short day of week
Thu
EEEE
Long day of week
Thursday
H
Hour in day (0 ‑ 23)
0
k
Hour in day (1 ‑ 24)
24
K
Hour in AM/PM (0 ‑ 11)
0
h
Hour in AM/PM (1 ‑ 12)
12
a
AM/PM
12:00:00AM
mm
Minutes
30
ss
Seconds
55
Table 8‑6 shows examples of custom formats and their effects on a date stored as April 15, 2006 12:15:30 PM in the database.
Table 8‑6 Results of custom date formats
Format pattern
Result of formatting
MM‑dd‑yy
04‑15‑06
E, M/d/yyyy
Fri, 4/15/2006
MMM d
Apr 15
MMMM
April
yyyy
2006
W
3 (the week in the month)
w
15 (the week in the year)
DD
105 (the day in the year)
h:mm:ss
12:15:30
Defining a custom string format
You can define a custom string format using special symbols to construct a format pattern. Table 8‑7 describes these symbols. See Table 8‑8 for examples.
Table 8‑7 Symbols for defining custom string formats
Symbol
Description
@
Character placeholder. Each @ character displays a character in the string. If the string has fewer characters than the number of @ symbols that appear in the format pattern, spaces appear. Placeholders are filled from right to left, unless you specify an exclamation point (!) at the beginning of the format pattern.
&
Same as @, except that if the string has fewer characters, spaces do not appear.
!
Specifies that placeholders are to be filled from left to right.
>
Converts string characters to uppercase.
<
Converts string characters to lowercase.
Table 8‑8 shows examples of custom string format patterns and their effects on string data.
Table 8‑8 Results of custom string formats
Format pattern
Data in the data source
Results of formatting
(@@@) @@@‑@@@@
6175551007 5551007
(617) 555‑1007 ( ) 555‑1007
(&&&) &&&‑&&&&
6175551007 5551007
(617) 555‑1007 () 555‑1007
!(@@@) @@@‑@@@@
6175551007 5551007
(617) 555‑1007 (555) 100‑7
!(&&&) &&&‑&&&&
6175551007 5551007
(617) 555‑1007 (555) 100‑7
!(@@@) @@@‑@@@@ + ext 9
5551007
(555) 100‑7 + ext 9
!(&&&) &&&‑&&&& + ext 9
5551007
(555) 100‑7 + ext 9
>&&&‑&&&&&‑&&
D1234567xy
D12‑34567‑XY
<&&&‑&&&&&‑&&
D1234567xy
d12‑34567‑xy
Reverting to default formats
If you applied a number, date‑and‑time, or string format to a column of data, you can restore these formats to those in the original report. Select and right‑click the column. Choose Format➛Format Data. Then, select Unformatted from the drop‑down list.
Copying a format to other columns
When working with columns of similar data types, you can apply a format to data in one column, and copy the format to data in other columns. For example, consider a simple report that lists sales price, profit, and revenue for products sold in a region. If you format the sales price column as currency in US dollars, you can copy this format to the profit and revenue columns, so that all amounts are formatted as currency in US dollars.
How to copy a data format
1 Select and right‑click the column containing the formatting properties you want to share. Choose Format➛Copy Format. Copy Format appears, as shown in Figure 8‑9.
Figure 8‑9 Sharing formatting properties with other columns
2 Select the column to which you want to copy the current formatting properties. To select multiple columns, press Ctrl, then select each column.
Choose OK. The report displays the copied formatting properties in the specified columns.
