Getting Started Formatting SSRS Reports
Formatting SSRS Report
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
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 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.
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
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
· 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
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
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