By: Dallas Snider | Comments | Related: > 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.
When we create our report, we put three of the four columns returned by the stored procedure in our tablix in SSRS.
When we run the report we would like to convert the temperature from Celsius to Fahrenheit and convert the date to YYYYMMDDHHMISS.
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.
In the Expression builder's "Set expression for: Value" text box, the value of the temperature field is the default value.
We can look through the Category values to see the multitude of formatting options and values we can assign to the text box.
To convert the temperature to Fahrenheit, we will multiply the temperature value by 9.0/5.0 and then add 32 to this product.
When we preview our report, we can see that the temperature column is now in 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.
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.
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.
We will change the header for temperature to tell the report viewer that the temperature is now in Fahrenheit.
When we preview the report, we can see the Temperature and Collection Date Time columns are now displaying as desired.
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.
- SQL Server Reporting Services Tutorial
- SQL Server Reporting Services (SSRS) Repeating Headers On Pages
- SQL Server Reporting Services (SSRS) Controlling Report Page Breaks
- Add a linear trendline to a graph in SQL Server Reporting Services
- Create a Greenbar Report in SQL Server Reporting Services
About the author
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
