Walkthrough: When groups behave, they're groovy. Multilinks!

by Lisa Nicholls Sun, May 31 2009 18:25

When I wrote, a few months ago, that group bands are a real PITA, I didn't mean to imply that you should never use them.

Recently we had a requirement for a list of items that should each be linked to a different request to an external application.  As you know, and if you don't you can read this other recent post, SSRS has lots of ways to provide linkage and jumps to external content.  The question, this time, was how to put multiple links in one cell.

I'm going to leave RS 2008's placeholder control out of this discussion.  Obviously, if you can fashion the HTML in another application and just dump it all in one field, you can use a placeholder control to display whatever HTML, including multiple links, you need.  But it's not always so easy, especially when the items to be linked occur in separate rows in the first place.  That's the scenario we're considering here.

0. Sample data for this walkthrough 

I've used the MySQL World tutorial database as well as NorthWind, occasionally, to illustrate this particular point to VFP folks, because you can always find some way to fashion a link from geographic data, for example:

"http://encyclopedia.thefreedictionary.com/" + city

... but I'm getting a little bored with it and our needs for this walkthrough are really simple.  So I've chosen an example that's a little closer to home, in fact it's downright self-referential:

SELECT 'Dynamic Page Breaks in SSRS: Going to Eleven' Label,
           'http://spacefold.com/lisa/2009/03/08/default.aspx' Link , 
           'March' CMonth
UNION
SELECT
'Coming back into the light',
           'http://spacefold.com/lisa/2009/05/10/default.aspx' , 
           'May'
UNION
SELECT
'Whitespace occasionally can jump',
           'http://spacefold.com/lisa/2009/05/17/default.aspx', 
           'May'
UNION
SELECT
'Buddha''s dream',
           'http://spacefold.com/lisa/2009/05/22/default.aspx', 
           'May'
ORDER BY 2

... that's all you'll need to follow here.

1. Consider the base case

The normal way to provide a link is to use the "Navigate" or "action" facility on an RDL display object, such as a textbox.

In this case, we'll have a textbox with the expression Fields!Label.Value in it, and we'll link to the expression Fields!Link.Value.  That seems simple enough.

Here's the equally simple-minded result. I'm going to cut and paste the output right out the MHTML export rather than using a screenshot, just for fun, so you can click on the links.  (The other shots in this post are regular images, but I've used the underline style for the links so you can recognize them easily):

MultiLinks - LSN Recent Posts

 

simple table

 

     

March

Post

Title

 

2009/03/08

Dynamic Page Breaks in SSRS: Going to Eleven

May

Post

Title

2009/05/10

Coming back into the light

2009/05/17

Whitespace occasionally can jump

2009/05/22

Buddha's dream

 

2. Nest one data region inside another

The trick to having some elements repeat multiple times against another, in an RDL, is simply to nest data regions. 

In the following example, I've put a List inside another list. A list will repeat once for each relevant row of data, and it can contain free-form content by, in turn, containing other RDL display elements, such as text boxes and images.  In this case I've used the inner list to create a "bulleted list" of links. I've used a different color for the bullet just to make sure you can see the distinct textbox elements within the single list container. 

The outer list is distinguished from the inner content because it repeats on a different basis (in this case, I've grouped the posts by month, using the expression =Fields!CMonth.Value). 

The outer list must be grouped. But, as the note on the report surface says, you can use containers to group report display elements to make them easier to move as a unit.  If you don't happen to need a grouping value, just group the outer container on a constant.

3. Now build a better table

So now you can take that inner list container that we used above, and put it right into a cell of a (group) row of a table.  The result gives us exactly what we're looking for in the table, without any fancy or finicky HTML-generation code to bring together the multiple data rows.

 

See how easy that was? It was almost fun. And we owe it all to group expressions.

Poor, dear little group expressions. So terribly misunderstood.

They don't want to be evil.   

Tags:

Reporting | SQL Server