SSRS – Format() Function

The most common fact for the data representation is that this is information being pulled from a stored procedure and we don't really know what the query was and which kind of data formatting is using in the script. This data formatting can be easily done with some very common expression formatting in SSRS. If we are developing an SSRS report to show gross sales which are broken down in order date, cost, gross profit, gross profit %, order count, total sales etc.

A standard principle is to separate data from display, so use the Value property to store the data in its native data type and use the Format property to display it how we want to display the data. This data formatting become very urgent when exporting our report to Excel because if we have the right data type for our data then it will export to Excel as the right data type as we defined in the report.

Most Common Data Representation Formats - As we know that Reporting Services already introduced enhanced rich text functionality to permit mixed formatting, or even mixed tooltips & actions, within a single textbox and different formats could include mixing font sizes, colors, and other things like bold and italics within one textbox. Some very common data formatting are given below:

1. Various date formats
2. Currency with 2 decimal places
3. Number with 4 decimal places
4. Percentage with decimal places
5. Negative value format

To understand the report data formatting, we can use the following data to generate the report –

SELECT CONVERT(VARCHAR(8), GETDATE(), 12) AS ORD_NO, CAST(GETDATE() AS DATE) AS ORD_DATE, 121 AS QTY, 121 * 1.5 AS SALES, 121 * 1.2 AS COST, 121 * 1.5 - 121 * 1.2 AS MARGIN UNION SELECT CONVERT(VARCHAR(8), GETDATE() + 1, 12) AS ORD_NO, CAST(GETDATE() + 1 AS DATE) AS ORD_DATE, 120 AS QTY, 120 * 1.75 AS SALES, 120 * 1.12 AS COST, 120 * 1.75 - 120 * 1.12 AS MARGIN UNION SELECT CONVERT(VARCHAR(8), GETDATE() + 2, 12) AS ORD_NO, CAST(GETDATE() + 2 AS DATE) AS ORD_DATE, 32 AS QTY, 32 * 21.75 AS SALES, 32 * 21.12 AS COST, 32 * 21.75 - 32 * 21.12 AS MARGIN

Report will be look like as given below after using the above script –

To apply the format settings in the existing report column, we need to right click on the column and choose the text box properties as given below –

In the Textbox properties window, choose number to see all the available formats where we can choose the pre-define formats. If you want to add your own formats then we can choose the custom format as given below- 

Or we can use the column/Textbox properties window and fill the Format as given below-

Various date formats - Date formatting in SQL Server Reporting Services (SSRS) is a very common requirement where customers look for a very specific format and it is become very important to understand the basic components of date formats.

Date Format Expression	Result
=FORMAT(Fields!ORD_DATE.Value,"d")	M/d/yyyy
=FORMAT(Fields!ORD_DATE.Value,"d/M/yyyy")	d/M/yyyy
=FORMAT(Fields!ORD_DATE.Value,"MM-dd-yyyy")	MM-dd-yyyy
=FORMAT(Fields!ORD_DATE.Value,"dd-MM-yyyy")	dd-MM-yyyy

Currency with 2 decimal places – Currency formatting is very important for the report because every country has its own currency.

Currency Format Expression	Result
=FORMAT(Fields!SALES.Value,"'$'0.00;('$'0.00)")	$158.00
=FORMAT(Fields!SALES.Value,"'$'#,0.00;('$'#,0.00)")	$1,158.00
=FORMAT(Fields!SALES.Value,"'$'#,0.00)	-$58.00

Number with 4 decimal places –To display the number with n number decimal points, we can use the following format() settings -

Number Format Expression	Result
=FORMAT(Fields!QTY.Value,"0.000;(0.000)'')	158.000
=FORMAT(Fields!QTY.Value,"0.00;(0.00)'')	158.00
=FORMAT(Fields!QTY.Value,"0.0;(0.0)'')	158.0
=FORMAT(Fields!QTY.Value,"0;(0)'')	158

Percentage with decimal places –To display the percentage number with n number decimal points, we can use the following format() settings -

Percentage number Format Expression	Result
=FORMAT(Fields!MARGIN.Value,"0.00%;(0.00%)'')	158.00%
=FORMAT(Fields!MARGIN.Value,"0.000%;(0.000%)'')	158.00o%
=FORMAT(Fields!QTY.Value,"0.0%;(0.0%)'')	158.0%
=FORMAT(Fields!QTY.Value,"0%;(0%)'')	158%