SSRS Formatting

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

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:

 

Comments ( 4 )

  1. Replykoteswararaot11

    I wont dispaly the date format like this Wednesday ,October 2,2013 as i see u r site i am using for this format "D" but it not showing what i need the format what can i do for this format .

    • Replysqlsafety

      You can email me at derekman1@msn . Com

Leave a Reply


Hit Counter provided by Sign Holders