SSRS Formatting
by David Dye
After working for years with other reporting products I
made the move to SSRS and found that one of the more difficult learning curves
was to find formatting codes. I
began with what I thought was a rather simplistic report that contained a simple
tabular report with only a few columns. I had no problem with grouping my data, creating the expressions for the
footers and headers, or even creating default values for parameters, but hit a
wall when I began trying to format my dates and numbers. After finally finding the Format
property I was at a loss of what to put in it. The below are the formatting codes that I have compiled since that day:
made the move to SSRS and found that one of the more difficult learning curves
was to find formatting codes. I
began with what I thought was a rather simplistic report that contained a simple
tabular report with only a few columns. I had no problem with grouping my data, creating the expressions for the
footers and headers, or even creating default values for parameters, but hit a
wall when I began trying to format my dates and numbers. After finally finding the Format
property I was at a loss of what to put in it. The below are the formatting codes that I have compiled since that day:
Date/Time Formatting Codes
| Date Format Codes | Description | Example |
| d | Short date | 3/12/2006 |
| D | Long date | Monday, June 1, 2006 |
| t | Short time | 7:12 PM |
| T | Long time | 6:15:55 AM |
| f | Long date/short time | Monday, June 1, 2006 7:12 PM |
| F | Long date/long time | |
| g | Short date/short time | 3/12/2006 19:12 |
| G | Short date/long time | 3/12/2006 19:12 |
| M or m | Month and day only | 5-Nov |
| Y or y | Month and year only | October, 2002 |
| Yyyy | Four digit year | 2008 |
| yy | Two digit year | 8 |
| MMMM | Full month name | April |
| MMM | Three character month | Jan |
| MM | One or two digit month | 4 |
| Dddd | Full day name | Friday |
| Ddd | Three character abbrev. | Sat |
| dd | Two digit day always | 4 |
| d | One or two digit day | 4 |
| hh | Two digit hour always 12 hr clk | 8 |
| h | One digit hour 12 hr clk | 8 |
| HH | Two digit hour always 24 hr clk | 20 |
| H | One or two digit hour 24 hr clk | 8 |
| Mm | Two digit minutes | 24 |
| ss | Two digit seconds | 24 |
| tt | Two character AM or PM | AM |
| t | One character AM or PM 24 hr clk | A |
NUMERIC FORMAT CODES
| Numeric Format Codes | Description | Example |
| # | Optional place holder | |
| 0 | Required number placehoolder | |
| % | Percentage .95 becomes 95% | |
| C | Two digit numbers & local currency char | $12,345.95 (For US currency) |
| D or D2 | Decimal value, optionally can specify precision | 56.95 |
| E or E12 | Specific notation, optionally can specify precision | 1.23E+08 |
| P or P1 | Percentage, optionally can specify precision | 87.4 |
| N or N# | Numeric, including thousands comma separator prec. Optional |
1,000 |
Now that
you have these formatting codes where exactly do they go? From within
Business Intelligence Development Studio you can right click on the textbox or
group and choose Properties and from the properties window select the
Format tab :
you have these formatting codes where exactly do they go? From within
Business Intelligence Development Studio you can right click on the textbox or
group and choose Properties and from the properties window select the
Format tab :
or click
on the object and browse to the format property in the Properties pane
on the object and browse to the format property in the Properties pane
and enter the formatting code:
The
results for numeric formatting is:
results for numeric formatting is:
| Format | Value | Formatted Value |
| N0 | 10000.00 | 10,000 |
| N1 | 10000.00 | 10,000.0 |
| C | 10000.00 | $10,000.00 |
| D2 | 10000.00 | 10000 |
| P1 | 10000.00 | 1,000,000.0% |
| E | 10000.00 | 1.000000E+004 |
The
results for date/time formatting is:
results for date/time formatting is:
| Format | Value | Formatted Value |
| d | 2/3/2010 12:30.000 AM |
2/3/2010 12:30:00 AM |
| D | 2/3/2010 12:30.000 AM |
Wednesday, February 03, 2010 |
| t | 2/3/2010 12:30.000 AM |
12:30 AM |
| T | 2/3/2010 12:30.000 AM |
12:30:00 AM |
| f | 2/3/2010 12:30.000 AM |
Wednesday, February 03, 2010 12:30:00 AM |
| F | 2/3/2010 12:30.000 AM |
Wednesday, February 03, 2010 12:30 AM |
| g | 2/3/2010 12:30.000 AM |
2/3/2010 12:30 AM |
| G | 2/3/2010 12:30.000 AM |
2/3/2010 12:30:00 AM |
| M or m | 2/3/2010 12:30.000 AM |
February 03 |
| Y or y | 2/3/2010 12:30.000 AM |
February, 2010 |
| Yyyy | 2/3/2010 12:30.000 AM |
2010 |
This post covered simplistic out of the box formatting and
will be followed by more complex formatting of numeric, data/time, and string
types, until then you can also refer to MSDN on line documentation:
will be followed by more complex formatting of numeric, data/time, and string
types, until then you can also refer to MSDN on line documentation:
Share this post with friends
