Formatting SSRS Report
Introduction
The SSRS report once ready needs to be formatted to make it pixel perfect for viewing and printing. The formatting can be classified into the following types:
- Conditional Formatting
- Unconditional Formatting
Conditional Formatting
The conditional formatting is done using the IIF and switch statements. For conditional formatting can be used to display the alternate rows with the specific color codes.
Unconditional Formatting
Unconditional formatting can be used to format the report for the date and number values in a report. For example displaying the date in the Indian format, with the default date is displayed in the US format.
Implementing Formatting in SSRS Report
Let work thorugh a sample report formatting exercise.I created a sample report whose output is below. As you can see the report is just the raw draft of the requirements given to the developer but still not in the state to present it to the end business user and also lacks the basic formatting for the displaying the data as well.
We can begin with setting the Title to the center. The SSRS has a tool bar button to horizontally center align the Titles or any other report item in the exact center of the page. The tool also has the similar functionality for the vertical center alignment of a report item.
The following screenshots show the steps to format a number in the SSRS for applying the thousand separators to the numbers. You may also add the currency symbol to the Amount fields used in the reports.
Right click on the textbox of the Number field you want to format and go to the textbox properties :
Click on the number and select the Number in the category. Click the check box for the thousands :
To display the values in the currency format right click on the sale amount field
In the text box properties click on the currency and check the thousand separator box. By default it will show the Dollar sign ($) but you can customize it to specify the currency of your country by clicking on the symbol button.
Next we will do some conditional formatting to display the report rows in the alternate color for better visualizations. For this click on the details row on the tablix and right click and go to the properties (F4) and enter the following code in the background color expression:
= IIF(RowNumber (Nothing) Mod 2 = 0,"LightGrey", "Transparent")
This will display the Tablix rows in the alternate colors. You may change the color scheme as per your requirement.
The sales amount total is being shown in the thousands and therefore only few digits can been seen as the values have been formatted to thousands.
· Now to format the date to be displayed in the Indian format. Right click on the date textbox and go to expressions and add the following expressions to format the date:
Preview the report to see the output which will be as follows:
More details on the date formatting can be found on the msdn documentation. You can find it here.
Alternatively, the conditional formatting can also be achieved by using the switch statement as follows:
Click on the Sales Amount and press F4.
Go to the color and specify the following code in the expression
To make it look a bit more professional I will just add some fields, displaying the report run date and the report user who has generated the report.
You can do this by adding the Global variables such as the page numbers and the userId fields which are available as the built-in fields when you click on the expressions. Also I have used the built in DateTime function Now to get today’s date and time.
Finally the report will look like the following which has been made pixel perfect for the printing as well.
Going forward you may also want to refer to MSDN which gives a detail documentation on the SSRS formatting tips here
]]>