Dynamic Groups in SSRS Reports with MDX

Providing a dynamic group in SSRS with SQL is not all that hard. It is also a common business requirement when combining reports. In example, I see very often an old system providing multiple similar reports to its users – that is reports with exactly the same layout but with different items on rows or columns. I also see quite often SSRS requirements asking me to mock the old functionality and build multiple almost identical reports. My personal preference is to combine them in one if possible, thus reducing the maintenance and even development effort and cost. In SQL we have the convenience to choose how to name the output columns. As long as we have a stable dataset – that is a constant number of columns with the same data types called the same way, SSRS does not differentiate between them and treats them the same way. This allows us to flexibly change their actual contents. The standard approach in MDX would be to create all possible “groups” in a large dataset and then use one or another in the report as required through expressions. This, however, leads to slower execution times, larger chunks of data going around the network and the need to aggregate within SSRS at almost every data cell.

Instead, we can build the same functionality by constructing MDX dynamically through the use of parameters. Let’s examine a practical case to illustrate the concept a bit more clearly. If we assume that the Adventure Works executives asked me to create two simple reports showing years across the columns, their Internet Sales Amount in the data area and on one of them product categories on rows, while on the other one – the countries from which the sales originate from. The layout would be the same – a tablix, which has one column group – Year (calendar), Internet Sales Amount in the data cell and one of the two dimension hierarchies (Product Category or Country) on rows. The MDX for the datasets would also be fairly similar:

Year

Country

As we can see, the only difference here is the set on rows. Instead of providing two reports we can combine them in one. The standard approach I see all developers implementing is crossjoining all sets together and then aggregating in SSRS. To switch between the two grouping fields there would be a parameter “Group By”. The sample MDX for this report would be:

And the layout in SSRS:

The row group (and the expression within the row cells) here is based on a GroupBy parameter with two values – “product” or “country”. In SSRS the expression for them looks like this:


When the user selects Product as a value for the Group By parameter, our report renders grouped with the Product categories on rows. In this particular case the dataset is relatively small even when we do a full crossjoin between years, product categories and countries. However, in many cases the number of items on rows can be prohibitive. Therefore, it is better if we can avoid doing this by dynamically constructing the MDX expression. We can add another (Internal) parameter – GroupMDX to our report with the following expression as its default value:

Then, we can replace the report dataset with the following MDX:

To make it execute, I added as a sample query parameter GroupMDX with a value of [Product].[Product Categories].[Category]*[Customer].[Customer Geography].[Australia]. Note that you should use the same hierarchies as sample parameters as what you are actually using in your MDX parameter – otherwise you will get empty results (e.g. if I were to use [Customer].[Country].[Australia] as a sample in this case the actual dataset would not be able to figure out that the hierarchy has been changed and will return an empty set on rows for country).

Unlike with SQL, though, we have a problem – every time the query executes with a different set as a parameter value it returns different result set and its fields collection does not link well to them:

Luckily, there is a workaround. We can add a bit of custom code to our report (Report Properties -> Code). For more details you can read the following MSDN article. In a nutshell, we can check in the custom code for missing fields, thus guarding against the error we are receiving. The slightly modified code snippet from the MSDN page is:

Now, we can wrap every possibly disappearing fields reference within our report with a call to the function. In our case, we can replace the row group expression, as well as the row cell expression with the following expression:

Now we can run our report and get the results we expect. Every time we change the Group By parameter and run the report, SSRS constructs a different MDX query and sends it to SSAS getting only the results it needs. It is a fiddly technique, but in many cases can be a lifesaver – in particular when the large crossjoin method is just too slow.

And the report rdl can be downloaded here: [listyofiles folder=”wp-content/DynamicGroups”]

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”]

Line Breaks in SSRS

There are a number of ways we can add a line break to a Reporting Services string and these have been described in multiple articles online. Doing this we can get multiple lines per cell. The following content is intended to be a quick reference rather than a description of a new or better way to do it.

SSRS Expressions

The easiest is to add a vbCrLf to an expression like this:

=”Line 1″ + vbCrLf + “Line 2”

An easy way to remember the syntax is vb for VB, Cr for Carriage Return and Lf for Line Feed.

Alternatively, you can replace any character (in example a pipe), which exists in your data with vbCrLf and are not bound to CHAR(10) only – in case your query gets simple because you already have a suitable line break string in it. If you have a string like: “A|B|C”, and you want to replace the “|” character with a line break, you can do the following:

=Replace(“A|B|C”,”|”,vbCrLf)

The result is A, B and C on a separate line.

SQL

A more flexible and many times desirable way to do it is to add the line break to the data query. In SQL this would be by adding a CHAR(10) for the same purpose since CHAR(10) is Line Feed in SQL. Have a look at this query:

SELECT ‘Line 1’ + CHAR(10) + ‘Line 2’

If you use this in SSRS you will get the same output as with vbCrLf. This way we can construct our reports with line breaks in the database, giving us some flexibility.

MDX

In MDX we can also do something like this:

WITH
MEMBER [Measures].[Split Members] AS
“Line 1” + Chr(10) + “Line 2”
SELECT
{
[Measures].[Split Members]
} ON 0
FROM [Adventure Works]

This is much like in SQL – we just use the Chr() function to do the same.

Custom Code

We can also do the same with custom code. By using custom code we can split strings and create additional custom operations. For more info about using custom code for this purpose you can have a look at the following article:

http://www.kodyaz.com/articles/reporting-services-add-line-break-between-words-custom-code.aspx

Note that the operations that the author is performing can be done in SSRS natively with its Replace() function. Nevertheless, the article shows the fundamentals in a simple way.

Another interesting thing is that SSRS recognises CHAR(10) as both Carriage Return and Line Feed. In my testing (on SQL Server 2008 R2), CHAR(10) or Chr(10) does exactly the same as CHAR(10)+CHAR(13). Therefore we do not need to worry about adding a Carriage Return (CHAR(13)) in front of CHAR(10) in SSRS.

If for some reason you have troubles with CHAR(10) from SQL or other sources and you know it is in your character string, you can try replacing it with vbCrLf in SSRS.

3×3 Worst Development Practices 2010 with SSIS, SSAS and SSRS

There are some practices which are outright bad and some which are sometimes bad. I will try to make a list of top three outright worst practices per the main components of SSIS, SSAS and SSRS which I have annoyed me most throughout 2010 (and previous years). I am sure there are many more, but these are ones which I have witnessed often enough to deserve some attention.

SSIS

Using default names for all components in a control flow and a data flow

I really dislike opening projects and seeing only generic names. I have built some in the past because of insufficient time to document everything, but I hated creating them and later leaving them like that. I have also seen developers with time on their hands carelessly omitting them. The worst practice is to actually rename them to an equally unintelligible, if better, language. Many ETL-out-of-the-box tools actually do that and make subsequent maintenance much harder.

Performing joins with Merge Join

Chaining 5 Merge Joins and placing Sort components between them is something SQL Server does faster and with much less resources. Therefore doing joins in the database is a no-brainer even if your preference lies with a fully SSIS-idised process.

Not using configurations

Lucky, in Denali we are getting a completely revamped project configurations approach. Even if imperfect, Package Configurations are a must for every SSIS project. Migrating projects, changing databases, setting variables are just too good to miss.

SSAS

Using MDX for everything

MDX has an aura of a language which allows you to do everything much faster than SQL. Well, this is not entirely true and in the majority of cases a change in the design will allow avoidance of MDX, which in turn removes a whole layer of complexity allowing tremendous improvements over any programmatical approach.

Importing too much dimensional data

Often developers underestimate the impact of unnecessarily large dimensions with many useless attributes. Here the best approach is to add more as you need them rather than import everything because we can. Cubes are smaller and less complex with less attributes, processing is faster, and the most important thing is that the users actually like having less clutter.

Leaving NULLs in the relational database

SSAS does not like NULLs. I have not seen a case when NULLs are necessary in dimension tables. While I would leave a NULL for a measure when there is no data for it, too many of these can be quite harmful and it may be a case of a bloated measure group, as well.

SSRS

Storing business logic in report queries

While we can, we should never store SQL and MDX logic which can be implemented in stored procedures or calculated measures in the reports. Maintaining SSRS queries is much harder and less transparent. There are performance implications around caching, too.

Merging data sets in SSRS

This is hard to be done, and when it is, it is always bad. Data sets should be merged in the queries – not in the reports.

Building massive reports

In many organizations reports are seen as a transport mechanism to Excel. Users generate large, hundreds of thousands of rows report with a large amount of columns and sometimes even basic calculations. All this can be done simply and easily on the database level without going through SSRS.

Passing unCONSTRAINED Set and Member parameters between reports in Reporting Services

By default SSRS MDX queries get a StrToMember or StrToSet functions with a CONSTRAINED flag. However, many developers do not quite know why it is there or what it actually does. Books-On-Line contains this statements:

StrToMember

  • When the CONSTRAINED flag is used, the member name must be directly resolvable to a qualified or unqualified member name. This flag is used to reduce the risk of injection attacks via the specified string. If a string is provided that is not directly resolvable to a qualified or unqualified member name, the following error appears: “The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER function were violated.”
  • When the CONSTRAINED flag is not used, the specified member can resolve either directly to a member name or can resolve to an MDX expression that resolves to a name.
  • StrToSet

  • When the CONSTRAINED flag is used, the set specification must contain qualified or unqualified member names or a set of tuples containing qualified or unqualified member names enclosed by braces {}. This flag is used to reduce the risk of injection attacks via the specified string. If a string is provided that is not directly resolvable to qualified or unqualified member names, the following error appears: “The restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated.”
  • When the CONSTRAINED flag is not used, the specified set specification can resolve to a valid Multidimensional Expressions (MDX) expression that returns a set.
  • Therefore, if you have a CONSTRAINED flag you have to specify exact members or sets (e.g. [Date].[Year].[2009], or {[Date].[Year].[2009],[Date].[Year].[2010]}). If you omit the flag, you can pass to the StrToMember an expression, which evaluates to a member (e.g. [Date].[Year].[Year].Members.Item(0)), and to StrToSet an expression, which evaluates to a set (e.g. NONEMPTY([Date].[Year].[Year].Members, [Measures].[Amount]).

    The flexibility which removing CONSTRAINED offers can be quite powerful when passing parameters between reports. In example, we may want to pass a parameter to a drill-through report from two different summary reports, where each of those uses a different subset of dimension members, which in turn can be derived by different set expressions.

    Major drawbacks of using this approach is the severe performance hit it leads to, as well as a possible “MDX injection” vulnerability. Since in most cases we would  be using the passed parameters in a subcube expression or on the slicer axis (WHERE clause), this should not lead to as bad performance as we would get if we would use it inside a calculation. However, when we need to use a parameter directly in a calculated measure, we would be better off avoiding an unCONSTRAINED function. 

     Therefore, we may instead use SetToStr on the summary reports and pass a String parameter to a CONSTRAINED StrToSet function in the drill-through report. This way we are first resolving the set expression once and passing it on to the underlying report as a string. We could do that in a calculated measure returning a String, then passed on as a Field to the drill-through parameter. However, in the rare case where we have many rows travelling from the SSAS server to the SSRS server, this could be slow.

    So, whether we use a StrToSet without CONSTRAINED flag, or a String parameter constructed by a SetToStr function, is dependent on the actual scenario, but it is good to have both options in our arsenal of tools and techniques when we need to implement some non-quite-standard piece of functionality.