Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Videos          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

SQL Server Reporting Services Expression Builder to Reformat or Convert Text Box Values


By:   |   Read Comments   |   Related Tips: > Reporting Services Formatting

Problem

I need to reformat or convert the data returned by a stored procedure to my SQL Server Reporting Services (SSRS) report, but I am not allowed to alter the stored procedure. What options are available?

Solution

In this tip, we show how you can use Expressions in SQL Server Reporting Services (SSRS) to reformat or convert data values in a text box. Expressions allow you to control the display of the data in the report when you cannot alter the query.

In the image below we have a stored procedure named getSensorData that returns four columns of data.

Stored procedure that returns our data

When we create our report, we put three of the four columns returned by the stored procedure in our tablix in SSRS.

Original report design

When we run the report we would like to convert the temperature from Celsius to Fahrenheit and convert the date to YYYYMMDDHHMISS.

Original report preview

Convert temperature from Celsius to Fahrenheit for SSRS report

Let's begin with the Temperature. Right-click on the Temperature data text box and select "Expression..." to display the Expression builder window.

Right-click and choose Expression

In the Expression builder's "Set expression for: Value" text box, the value of the temperature field is the default value.

Default value when the Expression builder is displayed

We can look through the Category values to see the multitude of formatting options and values we can assign to the text box.

Formatting options in the Expression Builder

To convert the temperature to Fahrenheit, we will multiply the temperature value by 9.0/5.0 and then add 32 to this product.

Expression to convert from Celsius to Fahrenheit

When we preview our report, we can see that the temperature column is now in Fahrenheit.

Temperature successfully converted to Fahrenheit

Convert date format for SSRS report

Now, let's convert the date to YYYYMMDDHHMISS. Right-click on the Collection Date Time data text box and select "Expression..." to display the Expression builder window.

Display the Expression builder to convert the date

In the Expression builder's "Set expression for: Value" text box, the value of the Collection Date Time field is the default value. In the "Category" box, when we expand the "Common Functions" and the "Date & Time" functions are displayed.

Expression builder date and time functions

We will use the Year, Month, Day, Hour, Minute and Second functions to build an expression to convert the date to YYYYMMDDHHMISS as shown below.

Build an expression to convert the date to YYYYMMDDHHMISS

We will change the header for temperature to tell the report viewer that the temperature is now in Fahrenheit.

Change the header to indicate Fahrenheit

When we preview the report, we can see the Temperature and Collection Date Time columns are now displaying as desired.

Conversions work correctly in Preview mode
Next Steps

Take a few minutes to explore the different functions and values that can be utilized through the SSRS Expression builder. Feel free to experiment with the options available in the Expression builder to take control of how your data is displayed.

Also, check out more tips and tutorials on SSRS MSSQLTips.com.



Last Update:






About the author
MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools