SSRS Formatting Expressions

 All formatting options described will be available for download on a .pdf in the next few weeks at the new site.

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:

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/200619:12
G	Short date/long time	3/12/200619: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 Propertiesand from the properties window select the Format tab :

 

or click on the object and browse to the format property in the Properties pane

and enter the formatting code.

 

 

The results for numeric formatting are:

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 are:

Format	Value	Formatted Value
d	2/3/2010 12:30.000AM	2/3/2010 12:30:00AM
D	2/3/2010 12:30.000AM	Wednesday, February03, 2010
t	2/3/2010 12:30.000AM	12:30 AM
T	2/3/2010 12:30.000AM	12:30:00 AM
f	2/3/2010 12:30.000AM	Wednesday, February03, 2010 12:30:00 AM
F	2/3/2010 12:30.000AM	Wednesday, February03, 2010 12:30 AM
g	2/3/2010 12:30.000AM	2/3/2010 12:30 AM
G	2/3/2010 12:30.000AM	2/3/2010 12:30:00AM
M or m	2/3/2010 12:30.000AM	February 03
Y or y	2/3/2010 12:30.000AM	February, 2010
Yyyy	2/3/2010 12:30.000AM	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:

http://msdn.microsoft.com/en-us/library/ms157406.aspx