Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SSRS Textbox Tips and Tricks


By:   |   Updated: 2012-08-29   |   Comments (12)   |   Related: > Reporting Services Development

Problem

As you begin to work with SQL Server Reporting Services, you find may the need to utilize some more advanced options for cells and textboxes that get dropped onto Tablixes. What options are available?  Check out this tip to learn more.

Solution

SQL Server Reporting Services (SSRS) offers several different options for working with textboxes on a report. Some of these options include the following three items:

  • Adding multiple textboxes to a single cell on a tablix.
  • Adding a chart or graph to a tablix.
  • Special formatting options and adding line feed / carriage returns.

Let's take a look at examples of each.

Multiple Textboxes in a single cell of a SQL Server Reporting Services Report

It may seem counter intuitive, but SSRS provides the functionality to add multiple textboxes into a single cell on a Matrix. Several reasons exists for wanting to embed these multiple textboxes. First, in the header rows or columns, it can be beneficial to space out the certain text or even images onto specific parts of an individual cell or it may be helpful to have two distinct records under a single column header. Displaying a sparkline or chart immediately next to a numeric value is also another valid reason (this process will be covered in the next section below). So how can multiple textboxes be added to a single cell. I will be using the AdventureWorks database and some related reports to demonstrate these tips.

Step 1 is to drag the Rectangle Object into a cell.

Drag the rectangle object into the SSRS report

Step 2 is to drag multiple textboxes onto the Rectangle object. Then resize the individual textboxes as needed. Also, "text" can be added to the textboxes as in the example below, or if within the data area, individual fields can be added.

Drag textboxes onto the rectangle then resize and alignDefine the data for the text box in SQL Server Reporting Services
Define the text box data field in SSRS

The end result is multiple textboxes each with its own border, but contained within a single cell.

Multiple textboxes in SSRS

Charts and Sparklines within a textbox or Cell of an SSRS Report

This next tip may also appear counter intuitive, but charts and sparklines can be added to one cell within a tablix row, which produces a succinct chart just for the particular row group. For example, we may want to see sales broken out by store; however, we may also want to see a trended monthly sales line chart for each of the respective stores.

This first step in this process is to drag a chart or sparkline object to the cell.

Drag the Sparkline reports into an SSRS report

Next, add the Sparkline Properties for the values and groups.

Add the sparkline properties in SSRS

The end result is a report which displays not only current sales, but also a trended graph showing the sales trend. Of course, you can utilize other charts instead of a line chart. As displayed in the second figure below, pie charts create an excellent breakout for each category for a given group, which in turn means you would not have to create a separate chart for each group. That is quite handy!

Chart in text box result for three years

SSRS pie chart for two years

Special formatting and Carriage Returns in SQL Server Reporting Services

By the nature of using SSRS, we are wanting to present our data in a nice, "formatted" structure, otherwise we would just run a query and work with the results. SSRS does a great job of handling basic formatting within a cell that has just a single field. However, what happens if for instance you are concatenating two fields of different data types into one textbox? Often times this situation occurs when displaying a report run date which you would like to display as "Report Run Date: 05/13/2013" (not including the time). Which standard format do you use for that textbox? The answer is to use the SSRS formatting function to format each piece of the concatenated expression in the cell. To accomplish this process, first drag the Execution Time Built In Field to the footer.

Drag the execution time into the footer of the SSRS report

Next, right click on the textbox and select Expression. Adjust the expression to read as follows.

Add Report Run Time with the execution time for the SSRS report

The footer with the current expression displays as follows.

Report date and time without formatting in Reporting Services

However, if we only want the date to show (and not the time), we can not just format the textbox with a short date format because of the "Report Run Date" string. Fortunately, we can use the Format function to actually apply the date format to just the 2nd part of the expression. The new expression syntax is displayed below.

Formatting the expression text for the execution time to MM/DD/YY

The footer now displays only the date. For more information on SSRS Expressions including the format function, see Expression Examples on MSDN: http://msdn.microsoft.com/en-us/library/ms157328.aspx .

Report date with only the date formatting

Using this same textbox, say that we want to have the date appear on the line below the "Report Run Date:" expression. As noted in the below example, we can insert "chr(10) & chr(13)" into our expression which instructs SSRS to insert a carriage return / line feed into the textbox upon rendering.

Report run date formatted with a carriage return and line feed

Now the date appears on line 2 in the below figure. Inserting a line feed is often handy for titles in the header and footer area of a report.

New date formatting with the cr and lf in SSRS
Next Steps


Last Updated: 2012-08-29


get scripts

next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

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.



    



Thursday, September 15, 2016 - 12:06:11 PM - Ramya Back To Top

 Hi Scott,

What if I want to name the Chart lines in case of line graph. For example, If i'm showing Profit and loss lines , I want to name the Chart lines accordingly. Which property I should use to do it? If I use lables, the tect appears for each data point.

 

Thank you,

Ramya

 


Friday, April 03, 2015 - 10:09:54 AM - Teun van den Biggelaar Back To Top

Hi Scott,

This also occurs when we just email the report (Rendered as MHTML (web archive).
Any thoughts?

Thanks, Teun


Friday, April 03, 2015 - 9:48:41 AM - Scott Murray Back To Top

PDF ingnores much of the padding settings.


Friday, April 03, 2015 - 9:26:39 AM - Teun van den Biggelaar Back To Top

Hi Scott, 

The report is rendered by PDF. Does this mean it's not possible at all ?

Thanks, Teun


Friday, April 03, 2015 - 7:17:33 AM - Scott Murray Back To Top

What rendering format is the subscription using?  PDF and Excel sometimes ignore the padding.


Friday, April 03, 2015 - 5:26:59 AM - Teun van den Biggelaar Back To Top

Hi Scott, 

Great article! At the moment I'm working on a report myself and I'm faciing with a little issue I can't resolve at the moment.
Within the report I specify that a row has no bottom padding (0pt), this shows up perfectly in the report. Once I make a subscription to the report by emailing it, it ignores the padding settings.

Do you have any idea if I missed a setting?

 

Thanks in advance,
Teun 


Friday, December 12, 2014 - 12:46:28 AM - hameed Back To Top

Hello Scott,

Thanks for the suggestion.

I am a novice in java script. Kindly help me with java script to be popped up on the SSRS report, and the process to be followed.

Expert people like you explaining such limitations with SSRS and ways to overcome them will be very helpful for so many people like me.

It would be very helpful for me, If I get the help at the earliest possible.Thanks in advance.

 

Thanks & Regards

AJ. Hameed


Thursday, December 11, 2014 - 12:02:32 PM - Scott Back To Top

3000 characters is a large number for tooltips.  I would recommend a javascript popup instead. 


Thursday, December 11, 2014 - 8:13:10 AM - hameed Back To Top

Hi Scott,

 

I have got fed up by looking up sites for the below issue I am facing.

I am Using Standard BIDS 2012.I have developed a matrix report in SSRS 2012 and deployed on the report server.

Once I render the report I am facing the below issue.

I have been struggling from a long time to increase the length of the tool tip in my matrix report.

For a each grid box [Detail] in my matrix report i am displaying a flag upon hovering it, I have to show a Text containing around 3000 Characters. But In My report, the text is showing until 512 characters only.

Kindly help me on this.

Thanks & Regards

AJ. Hameed

 


Saturday, February 22, 2014 - 8:37:59 PM - Scott Back To Top

You could possiblly add / use the code fucntionality, but out of the box that is not possible.


Tuesday, February 18, 2014 - 3:32:30 PM - John Back To Top

This may be a little 'advanced' as to the target of this article but; here goes:

I need to be able to store an Expression in a database, specifically, Oracle.

I would like for the Expression to run just like any other expression but, it has to come from the reports DataSet object. Is there any kind of say an EXEC command that would tell SSRS to execute the contents of the field returned by the DataSet instead of display it?

If this does not exists it would be a highly desirable feature as this would enable to make the entire report database driven.

Thank you.


Thursday, August 30, 2012 - 8:21:59 AM - Pavel Pawlowski Back To Top

Nice tips for beginners. I only mention, that instead of writing chr(10) & chr(13) it is more comfortable to use the vbCrLf constant which represents the Carriage Return + Line Feed so the expression can look like ="Report Run Date:" & vbCrLf & Format(Globals!ExecutionTime, "MM/dd/yyyy")


Learn more about SQL Server tools