Use SQL, parameters and bookmarks to create a grid of clickable links
To make report navigation easier, you can create a 2-dimensional matrix of clickable links at the top of a report. This blog shows you how!

Posted by Andy Brown on 06 August 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Creating a Matrix of Dynamic Bookmarks in SSRS

I couldn't resist adding this quick blog on how to achieve the following effect:

Checkerboard matrix of links

When you click on a film studio, it takes you to that studio's films in the same report.

When you click on the Braveworld Productions box above (say), this is what you'll see:

Films for given studio

SSRS jumps you to the films for the studio you have chosen.

 

This blog doesn't show you how to achieve the checkerboard effect above - for that, see my much longer blog on creating alternating colour effects, which in turn references a much longer blog on custom assemblies

Creating the Basic Report and Bookmarks

To get this report working, first create a grouped table:

Grouped table of films by studio

This table groups films by the studio making them.

 

You now need to associate a bookmark with each studio's group header, to tell SSRS where to jump to.  First select the group header's text box:

The StudioName group header

Select this text box first.

 

You can now set the textbox's Bookmark property:

Assign a bookmark to a textbox

The bookmark for each studio will be its name (we assume these are uniquely defined).

 

Creating a Parameter  for the Number of Columns

We'll allow the matrix at the top of the report containing the bookmarks to have a variable number of columns:

Matrix with 7 columns

Here we've specified the matrix should have 7 columns.

To do this, add a parameter (called cols in our case) to the report:

The report parameter for the columns
 

Creating the SQL for the Matrix

To create the matrix of bookmarks, we need a stored procedure upon which to base it.  Here's one which would work:

CREATE PROCEDURE spBookmarks(@cols int) AS

 

WITH RankedStudios AS (

SELECT

ROW_NUMBER() OVER(ORDER BY StudioName)

AS StudioRank,

StudioName

FROM

tblStudio )

SELECT

(StudioRank-1) % @cols + 1

AS ColumnHeader,

CAST(((StudioRank-1) / @cols) AS int)

AS RowHeader,

StudioName

FROM

RankedStudios

When you run this stored procedure with a parameter value for @cols of 5, say, you get a row number and column number for each row:

The stored procedure output

Each row has its row and column position within the matrix.

 

This SQL uses a Common Table Expression, and the ROW_NUMBER function to number the rows returned (it's not for the faint-hearted!).

Creating the Matrix of Clickable Links

Now that we have a stored procedure and a means to pass its parameter value, it's time to create the matrix.  To do this, first create an additional dataset for the report based on the stored procedure above:

Bookmarks dataset

The dataset for our matrix, giving the column header number, row header number and studio name to display.

 

You can now create a matrix looking something like this to display the clickable links (although as yet they won't be clickable):

Matrix of links

The row and column group headers have been deleted.

 

This matrix does have row and column grouping, even though the headers have been deleted:

Row and column grouping

You must set the row and column grouping fields, to make each studio appear in the right cell.

 

If you create this correctly, you should now have a 2-dimensional table of studio names:

Table of studios

The only thing remaining now is to get the studio names to be clickable.

Making the Studio Names Clickable

Finally, attach an action property to each studio name.  First display the matrix data cell's textbox properties:

Matrix data cell properties

Right-click on the matrix data cell to show its properties.

 

You can now assign an Action property:

Action property for bookmark

When a user clicks on a matrix cell they should see the named studio.

 

The Final Result

The end result is a thing of beauty:

Result for 6 columns

Here we've chosen to display 6 columns in our top grid of clickable links.

Enjoy! 

This blog has 0 threads Add post