Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

Data formatting in SSRS – Querychat

End-user experience is a very important thing to be taken care of when it comes to data reporting. If we want our report to be accepted and utilized by users, we need to format it in the way that it is clear and easy to use. This article explains how to format text, number, dates, as well as how to provide user-friendly report in terms of it’s design.

In order to explore data formatting options in SSRS, we will use the data provided in the table below. First we will make a basic report that will group the students based on the department they belong to, and afterward, we will add some formatting to the report in order to make the report more user-friendly. 

Table 1. Student table sample data

A report which displays the data from the Students table is presented in the picture below.  Figure 1. Basic report structure

Examples of static formatting

Three examples will be elaborated covering the following situations:

  • String formatting
  • Date formatting
  • Number formatting

String formatting

We want to format the title of the report in the following way:

  • Change “Font Name” to “Times New Roman”
  • Change “Font Size” to “22pt”
  • Set the title to be “Bold” and “Italic”

We can do this in two ways:

  • Using the Report Formatting toolbar

Figure 2: Allocation of the Report Formatting toolbar

In case the Toolbar is not visible, we can make it visible by clicking the menu View -> Toolbars -> Report Formatting from the Visual Studio menu

  • Selecting the Tool Box, going to Properties tab and locating Font section

Figure 3. Formatting the report title

As we can see, we have plenty of possibilities when it comes to textbox formatting: we can format the border of the textbox in the Border section, set the alignment properties of the textbox in the Alignment section, change the color of the textbox in the Fill section and others.

Date formatting

To change the format of the date columns like “25 Jun 2019”, we will follow these steps: 

1. Right-click on the column and select Text Box Properties…

Figure 4. Selecting Text Box properties

2. Go to Number tab, in Category section select Date, and in Type section select “31 Jan 2000”

Figure 5. Setting the date format

3. Save the changes and preview the report to see the result

Figure 6. Report title formatted

Number formatting

To display the Fee Amount values as an integer number, we will follow these steps:

1. Right-click on the column and select Text Box Properties…

Figure 7. Selecting Text Box Properties

2. Go to Number tab, in Category section select Number, and for Decimal Places value enter 0

Figure 8. Setting the number format

3. Save the changes and preview the report to see the result

Figure 9. Fee Amount column formatted

Example of dynamic formatting

In this example, we want to format the Name column in a way that we want to make department names uppercase and make only there values bold and italic. Leave student names as they are.

The process is the following:

1. Select the column name we want to adapt, right-click on it and click Expression

Figure 10. Defining expression on column value

2. Use the function UCase against department names in the following way: we use if statement and check if departmentid contains the value. In case department id is null, the row contains department name, and we use the UCase function against that string in order to make all characters upper-case. Otherwise, the string remains unchanged.

Figure 11. Using UCase function in an expression

3. Save the changes and preview the report to see the result               

Figure 12. Preview of modified Name column

Now, if we want to make department names bold and italic, we will follow these steps:

1. Double-click the column, change the markup type to HTML and click to define the expression value

Figure 13. Setting Markup type property of the column

2. In the expression, add html tags as string around the observed string

Figure 14. Adding HTML elements to the expression

3. Save the changes and preview the report to see the result

Figure 15. Preview of modified Name column

We also have the possibility to change the background color of table rows. If you want to know how to do that, please take a look into the article “Setting row color in SSRS”.

Instant livechat to an Expert!

Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service! 

Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc