SQLPam's Blog

February 10, 2012

In SSRS, Long Tool Tips Don’t Auto-Magically Wrap

Filed under: SSRS — sqlpam @ 5:58 pm

So here is the issue: The user wants detailed information as to how data is derived.  There is no way to shortcut it – you have to get wordy.  So you store the verbiage in a VARCHAR(MAX) column and populate the tool tips using this. 

Works great!  There is the verbiage and off the screen it goes.  What?  It isn’t smart enough to wrap to stay on the screen – not really.  

So now what? 

Here’s what I did. 

I found the longest tool tip in the table and then broke it out into 100 character segments.  I created new fields for each needed segment and updated them in the table. 

But it was more than that – I broke it out based on breaks so that words were not chopped.  My solution was not elegant but here it is:

My original tool tip field was GrpToolTip.  I added GrpToolTip1, GrpToolTip2, GrpToolTip3, GrpToolTip4, and GrpToolTip5 – all VARCHAR(100).

I look for the original Tool Tip to be greater than 100.  I determine the location of the first space after 80 to allow for longer non-breaking text and pull the left most characters based on the results.

If the original Tool Tip was less than 100 characters – I replace the new appropriate new Tool Tip with the original.

Next I update the original Tool Tip to remove the data moved to the new tool tip.

Then repeat the process thru all the new fields.

UPDATE rptReportTemplate
SET GrpToolTip1 = LEFT(GrpToolTip, charindex(‘ ‘, GrpToolTip, 80))
WHERE LEN(GrpToolTip) > 100

UPDATE rptReportTemplate
SET GrpToolTip1 = GrpToolTip
WHERE LEN(GrpToolTip) <= 100

UPDATE rptReportTemplate
SET GrpToolTip = REPLACE(GrpToolTip, GrpToolTip1, ”) 

UPDATE rptReportTemplate
SET GrpToolTip2 = LEFT(GrpToolTip, charindex(‘ ‘, GrpToolTip, 80))
WHERE LEN(GrpToolTip) > 100

UPDATE rptReportTemplate
SET GrpToolTip2 = GrpToolTip
WHERE LEN(GrpToolTip) <= 100

UPDATE rptReportTemplate
SET GrpToolTip = REPLACE(GrpToolTip, GrpToolTip2, ”) 

UPDATE rptReportTemplate
SET GrpToolTip3 = LEFT(GrpToolTip, charindex(‘ ‘, GrpToolTip, 80))
WHERE LEN(GrpToolTip) > 100 

UPDATE rptReportTemplate
SET GrpToolTip3 = GrpToolTip
WHERE LEN(GrpToolTip) <= 100

UPDATE rptReportTemplate
SET GrpToolTip = REPLACE(GrpToolTip, GrpToolTip3, ”)

You get the picture…

Now that I have all the data segmented into the proper lengths, I update the stored proc that feed the report to include the new segmented fields instead of the original field.  I need to refresh the report’s dataset so it sees the new fields. 

The next step is what all this was about.  Where the tool tips were displayed, instead of just setting the tool tip to the original tool tip field – I utilize an expression to force the next line down.  Here is the expression: 

=IIF(LEN(Fields!GrpToolTip1.Value)>0,Fields!GrpToolTip1.Value,””) +
          IIF(LEN(Fields!GrpToolTip2.Value)>0,CHR(10) + LTRIM(Fields!GrpToolTip2.Value),””) +
          IIF(LEN(Fields!GrpToolTip3.Value)>0,CHR(10) + LTRIM(Fields!GrpToolTip3.Value),””) +
          IIF(LEN(Fields!GrpToolTip4.Value)>0,CHR(10) + LTRIM(Fields!GrpToolTip4.Value),””) +
          IIF(LEN(Fields!GrpToolTip5.Value)>0,CHR(10) + LTRIM(Fields!GrpToolTip5.Value),””)

Note that I only include the CHR(10) when the segment is populated and I also strip off leading spaces for an even left edge.  That could have been accomplished with the above code with a little more effort – but it works here. 

Not rocket science – but hopefully it will save a few steps for someone in the future.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: