Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Formatting SQL Server Reporting Services Reports that have large text values


By:   |   Last Updated: 2016-11-30   |   Comments (4)   |   Related Tips: > 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.

Sample Report

 

What we really want is a report that looks like this. This way we can shorten the list, but still get the comment details.

Final Report

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.

Can Grow Property

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.

Modfied Report

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.

Hidden Parameter

Now drag and drop the parameter in the design view as shown below.

Drag Parameter on report

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.

Expression

Now go to the Action tab and change the action and values as shown in the image below.

Configure Parameter

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.

Final Report
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


Last Updated: 2016-11-30


next webcast button


next tip button



About the author
MSSQLTips author Rahul Mehta Rahul Mehta is a Project Architect/Lead working at Tata Consultancy Services focusing on ECM.

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    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, October 26, 2018 - 6:54:53 AM - trudy Back To Top

 Could you please post the expression necessary for step 3 as this is what I'm looking for.

Thank you.

 


Wednesday, August 02, 2017 - 8:38:42 AM - Greg Robidoux Back To Top

Hi Sarika,

take a look at this page: https://www.mssqltips.com/sql-server-tip-category/220/professional-development-interview-questions-bi/

-Greg


Wednesday, August 02, 2017 - 7:15:10 AM - Sarika Back To Top

Hi Rahul,
Great brief on "Formatting SQL Server Reporting Services Reports that have large text values". Can you share some interview questions on SSRS.

Thanks & Regards
Sarika.


Wednesday, July 19, 2017 - 1:20:58 PM - nceba Back To Top

 Hi

 

May you please provid me with the expresion on the third step as im strugling with diplaying my omment on the text box.

 


Learn more about SQL Server tools