By: Rahul Mehta | Comments (5) | Related: > Reporting Services Formatting
Problem
In SQL Server Reporting Services (SSRS), a report sometimes has large text columns that can distort the overall view of the report results and make the report not as useful. In this tip we look at one way of solving this problem and still providing all of the details.
Solution
To demonstrate this, I used the AdventureWorks2016 database and Production.ProductReview table. I created a simple SSRS report which shows four columns (ProductID, Reviewer Name, Review Date and Comments) as shown below. As you can see the Comments column has long descriptive data and this skews the output of the report. We want to make the output more user friendly.
What we really want is a report that looks like this. This way we can shorten the list, but still get the comment details.
Solving the Problem
As we can see, the comments column stretches the row size. In order to decrease the size and display the comments, we have to perform the following steps:
- First Step: decrease the size of Comments column.
- Second Step: define a parameter and put the parameter in the report.
- Third Step: configure the comments column to show the entire value as a parameter near the report.
Step 1 - Decrease the Width in SQL Server Reporting Services
For the first step, to decrease the size of the comments column, select the "Comments" column and in the property pane change the "CanGrow" property to False as shown below.
Once the property is changed, the report will look something like the image below (note I changed the Review Date format as well). It looks better, but the problem is we are unable to see all of the comments.
Step 2 - View all Data for a Descriptive Column in SQL Server Reporting Services
For the second step, to see the entire comments value, we need to define a parameter. To do so, go to the "Report Data" pane and right click on "Parameters" and click "Add a Parameter". Then define a hidden parameter as I have done in the below image.
Now drag and drop the parameter in the design view as shown below.
Step 3 - Configure Comments in SQL Server Reporting Services
For the third step, we have to configure the comments column to show the detail comments on the report. To do so, right click on the comments column in the design view and select "Expression" as shown in the below image.
Now go to the Action tab and change the action and values as shown in the image below.
Save and Review the SQL Sever Reporting Services Report
Save all changes and load the report. When you click on one of the comments in the main report, you will see the detailed data on the right as shown in the image below.
Next Steps
- As you build SSRS reports with detailed output, consider this technique to build the reports without distorting the main data set.
- More SSRS tips
About the author
Rahul Mehta is a Project Architect/Lead working at Tata Consultancy Services focusing on ECM.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
