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




Array

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 |