Tuesday, January 6, 2015

Formatting Numbers and Dates in RDLC Report

You can specify a format for numeric and date values by updating the Format property of its text box with a formatting string. For example, you can set a text box for a numeric field to display the number as currency. Reporting Services uses Microsoft .NET Framework formatting strings or you can create a custom formatting string for the Format property.

NoteNote:
To apply formatting to a text box, the text box must contain an expression, for example, =Fields!LineTotal.Value or =1000. If the text box is not an expression, that is, if the text in the text box does not begin with the equal (=) sign, then the text is interpreted as a string and formatting does not apply.

Formatting Numbers

The following table lists common .NET Framework number formatting strings.
Format string
Name
C or c Currency
D or d Decimal
E or e Scientific
F or f Fixed-point
G or g General
N or n Number
P or p Percentage
R or r Round-trip
X or x Hexadecimal
You can modify many of the format strings to include a precision specifier that defines the number of digits to the right of the decimal point. For example, a formatting string of D0 formats the number so that it has no digits after the decimal point. You can also use custom formatting strings, for example, #,###.

Formatting Dates

The following table lists common .NET Framework date formatting strings.
Format string
Name
d Short date
D Long date
t Short time
T Long time
f Full date/time (short time)
F Full date/time (long time)
g General date/time (short time)
G General date/time (long time)
M or m Month day
R or r RFC1123 pattern
Y or y Year month

You can also a use custom formatting strings; for example, dd/MM/yy. For more information about .NET Framework formatting strings, see Formatting Types.

Expression
Result
 =Globals!ExecutionTime
03-07-2012 17:16
 =Format(Globals!ExecutionTime,"dd MMMM yyyy")
07-Mar-12
 =Format(Globals!ExecutionTime,"dd/MM/yyyy")
13-03-2012
 =Format(Globals!ExecutionTime,"dd-MM-yyyy")
13-03-2012
 =Format(Globals!ExecutionTime,"MM-dd-yyyy")
03-13-2012
 =Format(Globals!ExecutionTime,"yyyy-MM-dd HH mm")
13-03-2012 19:11
 =Format(Globals!ExecutionTime,"yyyy-MM-dd HH mm tt")
2012-03-13 19:11 PM
 =Format(Globals!ExecutionTime,"yyyy-MM-dd hh mm tt")
13-03-2012 19:11
 =Format(Globals!ExecutionTime,"dddd, MMMM dd yyyy")
Tuesday, March 13 2012
 =Format(Globals!ExecutionTime,"ddd,MMM d yyyy")
Tue,Mar 13 2012
 =Format(Globals!ExecutionTime,"ddd, MMM d "'"yy")
Sun, Jan 8 '06
 =Format(Globals!ExecutionTime,"dddd, MMMM dd")
Sunday, January 08
 =Format(Globals!ExecutionTime,"M/yy")
01-Jun
 =Format(Globals!ExecutionTime,"d")
01-08-2006
 =Format(Globals!ExecutionTime,"D")
Sunday, January 08, 2006
 =Format(Globals!ExecutionTime,"f")
Sunday, January 08, 2006 11:53 PM
 =Format(Globals!ExecutionTime,"F")
Sunday, January 08, 2006 11:53:52 PM
 =Format(Globals!ExecutionTime,"g")
01-08-2006 23:53
 =Format(Globals!ExecutionTime,"G")
01-08-2006 23:53
 =Format(Globals!ExecutionTime,"m")
Jan-08
 =Format(Globals!ExecutionTime,"r")
Sun, 08 Jan 2006 23:53:52 GMT
 =Format(Globals!ExecutionTime,"s")
2006-01-08T23:53:52
 =Format(Globals!ExecutionTime,"t")
11:53 PM
 =Format(Globals!ExecutionTime,"T")
11:53:52 PM
 =Format(Globals!ExecutionTime,"u")
2006-01-08 23:53:52Z
 =Format(Globals!ExecutionTime,"U")
Monday, January 09, 2006 5:53:52 AM
 =Format(Globals!ExecutionTime,"y")
January, 2006


29 comments:

  1. Hi I am working on Reports using RDLC. I want to get a result of date format in MM/dd/yyyy. But i did not get it.

    Could you please help me on this..

    ReplyDelete
    Replies
    1. Use the following expression
      =Format(Globals!ExecutionTime,"MM/dd/yyyy")

      Delete
  2. Hi, is there a way to print the plus sign, a mean FORMAT(152.558,0,'') and return +152.55 , i tried this but it didn´t work, only show when my decimal was in negative.

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. sorry i meant format(152.558,0,'< Precision,2:2 >< Integer >< Decimals >< 1000Character,, >');

    ReplyDelete
  5. i have same issue
    i have value like 7854.4545
    and i want only 7854.45
    for i do ROUND(7854.4545,0.01)

    ReplyDelete
    Replies
    1. Hi,
      In format property apply the format as - "#,##0.00"

      It will make decimal field to round 2 decimal place only

      Delete
  6. Sir Give me any example of setrange and setfilter

    ReplyDelete
  7. Sir I want like this " 10th June, 2016 "

    What I Do..?

    ReplyDelete
  8. I wanted something like "mm/dd/yyyy hh:ss" but hh:ss in 24hr format

    ReplyDelete
    Replies
    1. Read blog carefully. Solution is already provided

      Delete
  9. Hello,

    This is a really nice article, just one question: is there any way we can show date time in the same format to that of the system, if i change the format of system date time, same format would get updated in the report.
    Please suggest

    ReplyDelete
    Replies
    1. It is default behavior of RDLC Report. If you doesn't apply any format on expression.

      Delete
  10. hiii sir year last two digit in use in rdlc

    ReplyDelete
  11. hello sir, can we make "#,##0.00" dynamically?

    ReplyDelete
  12. Hi i m using rdlc report and report language set de-De but i want date time fields values in en-US yyyy-MM-dd HH:mm:ss formate and i m using =format(CDate(Fields!CreateTime.Value).ToString("yyyy-MM-dd HH:mm:ss")) but not working proper

    ReplyDelete
  13. Very nice explained

    ReplyDelete