560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
|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!|
I couldn't resist adding this quick blog on how to achieve the following effect:
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:
SSRS jumps you to the films for the studio you have chosen.
To get this report working, first create a grouped table:
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:
Select this text box first.
You can now set the textbox's Bookmark property:
The bookmark for each studio will be its name (we assume these are uniquely defined).
We'll allow the matrix at the top of the report containing the bookmarks to have a variable number of 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:
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 (
ROW_NUMBER() OVER(ORDER BY StudioName)
(StudioRank-1) % @cols + 1
CAST(((StudioRank-1) / @cols) AS int)
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:
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!).
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:
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):
The row and column group headers have been deleted.
This matrix does have row and column grouping, even though the headers have been deleted:
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:
The only thing remaining now is to get the studio names to be clickable.
Finally, attach an action property to each studio name. First display the matrix data cell's textbox properties:
Right-click on the matrix data cell to show its properties.
You can now assign an Action property:
When a user clicks on a matrix cell they should see the named studio.
The end result is a thing of beauty:
Here we've chosen to display 6 columns in our top grid of clickable links.
|When:||28 Mar 20 at 10:56|
It an amazing trick. But how can I hide the subreport by default, and show it only when someone clicks on a link?
|When:||29 Mar 20 at 12:17|
Go to the visbility properties of the bottom report (or perhaps of a rectangle containing it). You can set the visibility to False, and underneath that set a ToggleItem property so that it pops into view when you click on one of the top links. I haven't tested this (the C******* virus is taking much of our time at the moment), but I can't seen any reason why it wouldn't work.
25 Aytoun Street