Getting Started Formatting SSRS Reports

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.  

s2.jpg

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.

s10.jpg


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




Related Articles :

  • No Related Articles Found

One Response to “Getting Started Formatting SSRS Reports”

  1. Very good article, very good tips that I use every day when developing reports in SSRS. Great job!!! And thank you…

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |