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.

February 1, 2012

and so it begins… SQL Saturday Tampa 110

Filed under: SQL Saturday — sqlpam @ 10:22 pm

I am starting the push for getting things together for our next SQL Saturday in Tampa.  This will be my fifth.  Every year seems to be a little bigger and hopefully, better.  The call for speakers just closed.  This year it has been a bit daunting.  We received 110 sessions by 43 speakers.  Based on the numbers, a lot of these were multiple sessions per speaker.

I want to try something a little different this year – based on some of the recent events put on by PASS, I am asking the community to assist with selecting the sessions.  The first pass thru the mix will be to limit one session per speaker.  To this end, I have done the geeky thing of importing the sessions and writing a report that would give me the information to best make the decisions- in SSRS of course – and exported it to Excel.  It is now located at http://tinyurl.com/6uwy7s4If you are game, please download and fill in your preferences for each speaker.  It will look empty so you will need to navigate to the top.  When you have done what you want – plese email it to me at SQLSaturdayPam@live.com.  Your assistance with would be greatly appreciated.

I’ll keep you informed as to our progress…

Ammendment: I received a response via twitter that had a comma delimited string of session IDs – this worked well if you would prefer to not mess with the spreadsheet.  Thanks again

Blog at WordPress.com.