SSRS Cascading Parameters Refresh: Solved

There is a long-standing issue with cascading parameters in SSRS – when changing the selection of the “parent” parameter the default selection of the dependent parameter does not always get refreshed. This is closed as “By Design” by Microsoft in the corresponding Connect item:

https://connect.microsoft.com/SQLServer/feedback/details/268032/default-does-not-get-refreshed-for-cascading-parameters

The reason given is that since the users may have changed the selection in the dependent parameter drop-down we do not want to overwrite their changed with the default values every time they select something else in the parent parameter. This is a valid reason, but in some cases it is actually desirable and expected for the defaults to change. Since the refresh behaviour cannot be controlled in SSRS the only way to deal with a requirement specifying a complete refresh was to tell the users it was not possible.

However, after playing a little bit around the various options I found a hack/workaround/solution which actually works and allows us to do a complete refresh – hence this post. The actual trick comes from the fact that the dependent parameter gets refreshed only when its values are invalidated by the selection in the first parameter. In example, if a user de-selects a value in the parent parameter and selects another value which prompts a complete change in the second (dependent) parameter, SSRS will actually apply the default selection by default. This got me thinking that if we can invalidate the dependent parameter every time a parameter changes we will enforce a complete refresh. An easy way to do this is to attach a value such as a GUID obtained with the NEWID() T-SQL function, or the output from GETDATE(). An old workaround I have provided in the Connect item was an idea based on GETDATE(). However, in this post I will show how we can use NEWID() with the same outcome.

Firstly, let’s assume we have the following tables in SQL Server:

Parameter (e.g. dimension) table pTable

Fact table fTable

p1k is for parameter 1 key, p1l for parameter 1 label

In preparation for creating our report we can create the following three stored procedures (script is attached in the end of the post):

There are a few things that may need clarification.

• The first stored procedure is a standard procedure to retrieve the distinct values for the parent parameter from a typical dimension table;
• The TvFMVParamSplit function splits strings in the format ‘a,b,c’ to a table containing a, b and c on each row. We need to use such a function to split multi-select parameter value strings which SSRS returns when dealing with multi-selectable parameters;
• In the second stored procedure we attach a GUID to all parameter keys. Every time this stored procedure gets executed the GUIDs are different and the output from the stored procedure is different, as well. We use this to invalidate the available and default values for the dependent parameter;
• When we use the key+guid strings in the third stored procedure we need to strip the GUID out of the string. We know that the output from NEWID() has LENgth of 36, so we REPLACE the last 36 characters in each selected value with an empty string, thus eliminating the redundant manually attached part and we are left with the key only;
• The string which SSRS constructs for the multiple selections gets quite much longer than when passing ints – we get 36 extra characters per value. Therefore, this is less efficient and cumbersome than passing straight keys. It could be better to have a flag in SQL Server which we can toggle every execution to 0 or 1 instead of using a GUID as it would be shorter but would introduce a slight extra bit of complexity I avoided for the purposes of this post.

The overall goal is to cause a complete refresh of all values in the second parameter whenever its stored procedure is called by SSRS (after change in the parent parameter).

Now we are ready to use these datasets in SSRS:

1. Create a new Data Source pointing to the database containing out tables and stored procedures;
2. Create a Data Set p1 for the parent parameter;
3. Create a @p1 report parameter (multi-select, text) with available values obtained from the usp_p1 stored procedure (p1k as Value, p1l as Label);
4. Create a Data Set p2 for the dependent parameter, which accepts @p1 as a parameter;
5. Create a @p2 report parameter (multi-select, text) with available and default values obtained from the usp_p2 procedure (p2k as Value, p2l as Label).

An interesting thing to note is that this setup will actually not quite work. If we just perform these 5 steps and test we will notice that the values for @p2 do not always get refreshed on change of @p1. Half by chance I made @p2 Internal. Then I added an extra @p3, which is then used on the report and did this the trick. Therefore, the actual steps required to complete the creation of our report are:

1. Set the @p2 report parameter to be Internal;
2. Create a @p3 report parameter (multi-select, text), with Available Values Value expression of =Parameters!p2.Value and Label of =Parameters!p2.Label. The Default Values should be =Parameters!p2.Value;

1. Create a Data Set main from the usp_main stored procedure and make sure that its parameter is populated by @p3, not @p2;
2. Add a table in the report with two columns, which show p2l and amt from the main data set.

Now if we deploy (also in the Preview window) we can test the outcome by changing selections of p1. Since there are quite a few fiddly steps involved in getting this to work I am attaching the working rdl (SQL Server 2008 R2) and a T-SQL script (SQL 2008) which creates the tables, populates them with data and creates the stored procedures (in Adventure Works) required for this workaround to work as described here. Please note that the same should work in SSRS 2005 but I have not tested it in that environment. Please add a comment to this post if you test it and it indeed works.

[listyofiles folder=”wp-content/CascadingParameters”]

Stacked Charts: A Little Swiss Army Knife of SSRS

I am a fan of Jon Peltier for his work around Excel visualisation. It is incredible how many ways we can tweak Excel charts to get great results – out of the box, with a little bit of development effort. In terms of flexibility and power, SSRS is very similar to Excel and two of its charting components – the Stacked Column chart and the Stacked Bar chart allow us to create some interesting non-default charts.

Let’s see an example of a typical stacked graph based on a simple T-SQL dataset:

SELECT ‘A’ AS description, 0 AS fPoint, 100 AS sPoint
UNION
SELECT ‘B’ AS description, 50 AS fPoint, 200 AS sPoint

In the designer:

And when rendered:

This is a typical bar chart and there is nothing new here. The interesting part starts when we change the colour of the fPoint to No Color. The same graph, rendered starts looking like something else:

Hmm, it looks like…a Gantt Chart! If we were to replace A and B with task names and the X axis with date/time values we have the opportunity to create a chart somewhat resembling a real Gantt chart. In the data set we must observe the rule that fPoint shows time when the task has begun and sPoint – its duration. For a detailed walk-through you can check out this article on SQL Server Central.

But the post does not finish here. With the same technique (different data set) we can create the following, as well:

A pyramid chart! The data set has its own set of rules, which are different to the Gantt chart. The data set used for this example was:

SELECT ‘A’ AS description, 5 AS fPoint, 1 AS sPoint
UNION
SELECT ‘B’ AS description, 3 AS fPoint, 5 AS sPoint
UNION
SELECT ‘C’ AS description, 2 AS fPoint, 7 AS sPoint
UNION
SELECT ‘D’ AS description, 0 AS fPoint, 11 AS sPoint

The easiest way to show how to build the data set is to imagine we have a third series – tPoint:

SELECT ‘A’ AS description, 5 AS fPoint, 1 AS sPoint, 5 AS tPoint
UNION
SELECT ‘B’ AS description, 3 AS fPoint, 5 AS sPoint, 3 AS tPoint
UNION
SELECT ‘C’ AS description, 2 AS fPoint, 7 AS sPoint, 2 AS tPoint
UNION
SELECT ‘D’ AS description, 0 AS fPoint, 11 AS sPoint, 0 AS tPoint

The sum of fPoint, sPoint and tPoint should always be the same and equal to the base of the pyramid and to maintain the symmetry, we want fPoint and tPoint to be the same. In general, the formula for this is:

fPoint = (Max(sPoint) – sPoint) / 2

We have to guard against 0/2 as we will always have one case (for the Max(sPoint)) in the data set where this equals 0.

And one last example of the power of the stacked charts in SSRS:

Here the Stacked Bar is changed to Stacked Column chart type in SSRS and the data set used is:

SELECT ‘A’ AS description, 5 AS fPoint, 3 AS sPoint, 0.1 AS tPoint
UNION
SELECT ‘B’ AS description, 3 AS fPoint, 8 AS sPoint, 0.1 AS tPoint
UNION
SELECT ‘C’ AS description, 2 AS fPoint, 1 AS sPoint, 0.1 AS tPoint
UNION
SELECT ‘D’ AS description, 1 AS fPoint, 6 AS sPoint, 0.1 AS tPoint

The tPoint is the marker for our targets and we can adjust the thickness by altering our data set. One limitation is that we cannot have targets lower than the top of the actual (i.e. the tPoint will always appear above, and not in the middle of, the fPoint bar), but this could be useful if we know that tPoint must appear above or just on top of fPoint (e.g. sPoint = 0 will give us a marker capping the bar). We can change this by adding one more series – topPoint, which will be 0 when we are below, or on target and will show the amount above target when we have some:

SELECT ‘A’ AS description, 5 AS fPoint, 3 AS sPoint, 0.1 AS tPoint, 0 AS topPoint
UNION
SELECT ‘B’ AS description, 3 AS fPoint, 8 AS sPoint, 0.1 AS tPoint, 0 AS topPoint
UNION
SELECT ‘C’ AS description, 1 AS fPoint, 0 AS sPoint, 0.1 AS tPoint, 1 AS topPoint
UNION
SELECT ‘D’ AS description, 1 AS fPoint, 6 AS sPoint, 0.1 AS tPoint, 0 AS topPoint

Then we can add it to the chart and colour the series in the same colour as the fPoint series:

When we render this we get:

There we go, the limitation has been lifted.

Note that for each different variation of the charts we are using customised data sets. The data transformations needed may not be trivial in some cases, but for most purposes they will be very simple and easy to work with in T-SQL; and because we keep this logic in T-SQL and just feed it ready for plotting to SSRS these charts render fast – a good practice worth re-iterating here.

Feeds in SSRS Reports

The XML data source in SSRS can be very powerful. Not only for querying web services but also for showing feed data on our reports. In this post I will show you how we can show the contents of my favourite blog site SQLBlog.com in an SSRS report.

First we need to get the feed URL. In this case it is: http://sqlblog.com/blogs/MainFeed.aspx. Once we have this, we can use it in a new XML data source in SSRS just as it is:

Then, we can use it in a data set in our report. The little tricky part here is that SSRS expects us to send a query to the data source. We do not need to do this for an RSS feed and instead we can leave the query blank.

However, this results in an empty fields list and hitting Refresh Fields does not help. To overcome this problem we need to know what the fields are at the first place. We can open Query Designer and click on the Execute button (!). The result is a number of rows corresponding to the feed items and columns containing various types of information about the items.

In the SQLBlog case, we get one line for each category the author has placed the feed in, while in other cases we may get one line per feed item or another variation. It is always necessary to inspect the contents and ensure we understand what we get from the data source in order to deal with the feed effectively later on because the feed contents vary. Many columns are irrelevant and we need to select only the ones we need. From the sample feed I wanted to show just the title, link, pubDate, creator and the description on my report. Since the field list was blank I added these fields manually as Query Fields.

Then, I placed these fields in a table to check the result.

I could see all items as expected, but the formatting was off. Additionally, there were multiple items repeating because of the multiple categories for each article. With a bit of work I managed to group/format/lay out the items appropriately, add report actions for the links and generally transform the table in a nice report, which I could use as my default feed reader.

And the rendered report:

By the way, the second item is very interesting – Alberto Ferrari has been just awarded the Microsoft MVP award for 2011!

One problem that you may find is the default authentication SSRS uses for an XML data source – Do not use credentials. This will cause a problem once the report is deployed, so I would recommend this should be changed to something else (in example, Windows Authentication).

There we go – we have a fully functional blog reader. We can enhance this by providing a list of authors (like a table of contents with linked reports), report parameters filtering the table, and other nice to have things. Of course, there are many feed readers out there any you may wonder why you would build your own in SSRS. Well, think about not only RSS but also other content which comes through XML – in example web service content which we can query through SOAP, twitter feeds, etc. Additionally, incorporating such content within a SSRS dashboard or report could come in handy if we get our hands on a feed showing financial information. One other source for content could be Microsoft’s Azure Data Marketplace – after all the data there comes in feeds and it could be handy to show it directly on our reports with a little bit of formatting.

Download the sample report here:

[listyofiles folder=”wp-content/SSRSFeeds”]

Load Test Your SSAS Instance

If you are wondering how performant your SSAS box will be with multiple users executing various queries in an extremely cool visual way, you now have the option to go to codeplex and download the new AS Performance Workbench by Rob Kerr from BlueGranite. It is awesome! And to prove it is – have a look at the following YouTube demo (6m 24s): http://www.youtube.com/watch?v=Kn4-3JTyDXM.

For any comments, requests and lod cheering you can also visit the project discussion page: http://asperfwb.codeplex.com/discussions.

Enjoy!