SSRS cascading report parameters using MDX queries

SSRS report parameters cascading is a regular usability requirement. In this tutorial, I will demonstrate how to proceed using MDX queries. The background to this is, that the default queries generated by the SSRS wizards are far below the standard we wish to deliver. Let’s dive in using the famous Multidimensional AdventureWorks DW OLAP Project.

Lets start by creating a Dataset for the first parameter in the cascade. Start in the Dataset Query designer as shown below.

QueryDesigner1

The MDX query I used is like this:

WITH
MEMBER [Measures].[ParCaption] AS [Product].[Category].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParValue] AS [Product].[Category].CURRENTMEMBER.UNIQUENAME

SELECT
{[Measures].[ParCaption], [Measures].[ParValue]} ON COLUMNS,
[Product].[Category].ALLMEMBERS ON ROWS
FROM [Adventure Works]

Next step is actually creating these report parameters, the first Parameter P_ProductCategory should be set like this:

Parameter_1

Parameter_2

The second parameter needs to be created exactly the same way once you prepare its Dataset as described in the next step.

Continue by creating another SSRS Dataset used for the second parameter P_ProductSubcategory in the cascade. This parameter value gets calculated on the fly as you pick the first parameter value.

WITH
MEMBER [Measures].[ParCaption] AS [Product].[Subcategory].CURRENTMEMBER.NAME
MEMBER [Measures].[ParValue] AS [Product].[Subcategory].CURRENTMEMBER.UNIQUENAME

SELECT
{[Measures].[ParCaption], [Measures].[ParValue]} ON COLUMNS,
[Product].[Subcategory].[Subcategory] ON ROWS
FROM [Adventure Works]
WHERE STRTOSET(@P_ProductCategory)

Notice the STRTOSET function. In case we would look for a boolean value, we could use STRTOMEMBER instead. In case we would look for multiple parameters, you would write WHERE ( STRTOSET(@P_ProductCategory), STRTOMEMBER(@ProductBooleanParameter) )

To make this work, we need to set the parameters of the second Dataset like this:

Dataset_Report

Notice you might run into an error (actually a VS bug) when writing the MDX query related to the Dataset in the query editor saying  “The query contains the XXXXXName parameter, which is not declared.” In that case, review the forum here but the solution is rather quick. Spoiler: Look for the Query Parameters icon in the top menu ( highlighted in orange box in the Query designer printscreen in the first screenshot from above) and set your parameters for the first time manually with some default value as well, that should make things work here.

Next step is creating the result dataset for the SSRS Report matrix. The query I used is trivial and is set like this:

SELECT (
[Product].[Category].[Category],
[Product].[Subcategory].[Subcategory],
[Product].[Product].[Product]) ON ROWS,
[Measures].[Order Count] ON COLUMNS
FROM (
SELECT (STRTOSET(@P_Product_Category), STRTOSET(@P_Product_Subcategory)) ON COLUMNS
FROM [Adventure Works]
)

Notice here, that in MDX you cannot use the same dimension hierarchy more then once, so you cannot use it in the SELECT and WHERE at the same time. This is the reason I decided to go for a Sub-Select, but there are many other options you can easily find on the internet. And here you go, after choosing Bikes and Clothing in the Product Category Parameter, you get only the relevant Product Subcategories, below are few screenshots of the simple SSRS Report:

Parameter_3

Report

Advertisements

Safe and predictable MDX queries for PowerPivot data loads

I have spent most of the last month digging deep and developing in PowerQuery and PowerPivot. I must say that I am impressed and there is still a lot more for me to learn. However I am not that much impressed about the documentation available online as there have been some issues I could not find an useful answer to.

Today I would like to share one quick tip related to querying OLAP database from PowerQuery or directly from PowerPivot. Imagine that you would place in the data loader section of a PowerQuery or PowerPivot a really basic MDX query looking like this:

Query 1:

SELECT
[Measures].[Internet Order Count] ON 0,
NONEMPTY([Customer].[Customer].[Customer],[Measures].[Customer Count]) ON 1
FROM
(SELECT
[Sales Territory].[Sales Territory].[Group].&[Europe] ON 0
FROM
(SELECT [Reseller].[Business Type].&[Specialty Bike Shop] ON 0
FROM
[Adventure Works])
)
WHERE [Product].[Category].&[40]

The results are:

Capture2

It could easily happen, that if you choose WHERE condition returning no rows, in this case determined by the non-existing Product Category, (or if your user would not have sufficient security rights under the OLAP role ), that the PowerPivot model could become corrupted and loose a relationship related to one of the (not)returned columns. You can rewrite the mentioned Query 1 in a much better way to ensure, that you always get the needed columns no matter how many rows are retrieved.

Query 2:

WITH
MEMBER [iCustomerName] AS [Customer].[Customer].CURRENTMEMBER.NAME
MEMBER [iCustomerKey] AS [Customer].[Customer].CURRENTMEMBER.Properties( "Key(0)" )

SELECT
{[iCustomerKey],[iCustomerName],[Measures].[Internet Order Count]} ON 0,
NONEMPTY([Customer].[Customer].[Customer],[Measures].[Customer Count]) ON 1
FROM
(SELECT
[Sales Territory].[Sales Territory].[Group].&[Europe] ON 0
FROM
(SELECT [Reseller].[Business Type].&[Specialty Bike Shop] ON 0
FROM
[Adventure Works])
)
WHERE [Product].[Category].&[40]
The results are:
Capture1
As you can see from the next picture, for Query 1 we have no Customer Name column in the model, for Query 2 we are just fine. I must admit that Customer Name is not a great example since we have the Customer Key returned anyway, but I guess you can see the point where I’m heading here. To stay on the safe side, write bullet-proof MDX queries!
 tabular1

Some thoughts on running PowerQuery in 32bit Excel 2013/2016 while connecting to XLS files

I really do like PowerQuery for the ease of use, its powerfulness and the self-explanatory ETL code in M. However I must say, that although Microsoft claims its a Self Service BI tool, things can get quite complex, especially if your clients have 32 bit Excel installed on their machines. So quite often the issues come up, when the clients try to connect via PowerQuery to huge local Excel data source files and at some point hit the out of memory error message. Now in 32bit Excel, you need to watch the virtual allocated memory in Process Explorer application. This useful application can be downloaded from here. You need to catch the Excel process and in the properties, you can easily find this value. No sense watching Excel memory claims from the Windows built-in Task Manager app. Quite often you can see, that Excel committed memory charge is around 500 MB, however you are receiving the out of memory message, because virtual memory allocated at this point can be around 1.95 GB, which I consider the point, where you can be certain that Excel will crash ( The point of no return ). You might be lucky and the virtual memory allocation might rise up to around 2,1 GB, but you are really surfing the threshold here. Very likely you will not be able to save the file at this point as well. So the recommendations based upon my personal experience are:

  • Whenever possible, try to load data from .csv instead of .xls files in case you need to load local data
  • Load only the columns you are sure you will need in your data model, no sense loading for example DWH system columns like GUIDs, PKs, System Dates etc..
  • Set the PowerQuery current workbook settings in a lightweight manner so that it does not automatically detect column data types, does not automatically create relationships between tables, does not update relationships when refreshing queries loaded to the data model, when possible, ignore the privacy levels
  • Track the virtual allocated memory in the Process Explorer application while adding each query. You might find a query loading just a few rows that causes high virtual memory allocation for no obvious reason, and that’s the perfect time to start tuning the query steps one after another. ( Unfortunately I have not found so far a way, how to do some more accurate query performance tracking when the queries are loading data from local Excel file source, however when loading data from different data sources, you have a pretty decent option setting the PowerQuery tracking ON and then you can load the log text file in another PowerQuery and set some basic transformations on the file to get the specific query duration etc. ).
  • When not needed, disable loading query results to a Worksheet
  • Try avoiding chaotic and memory consuming steps like adding columns, changing their datatypes and then removing them at the end of the query flow
  • Disable COM Excel add-ins because of their memory consumption
  • When nothing else seems to work, you can try downloading and installing Large Address Aware capability change for Excel from here , this KB released in May 2016 raises the 32 bit Excel memory limit from 2 to 3 GB

And that’s all I can think of at the moment. Don’t forget , that you should reserve at least 500 MB for PowerPivot if you plan to load the data into the data model. If I come across any more recommendations, I will share them here, however combining these steps served me pretty well.

SSRS reports personalisation

I would like to talk here about SSRS reports personalisation. The point is, that there are situations, when you feel you could do a little more to ease the use in some specific reports.

For example you have 20 additional columns and you can show or hide these columns based on what the user chose to see last time. Obviously this solution must also contain a parameter listing all the possible columns called ShowFields. Now we know, that SSRS 2008 lets you receive parameterized reports based on subscription to reports that run on demand. When you subscribe to a report that runs on a snapshot basis, your subscription must use the parameters values defined in the snapshot. But what if you cannot use subscriptions ?

I came across 2 scenarios when report subscription was not possible. One was, that you had an browser based CRM / ERP type application which used embedded SSRS reports running from an Iframe in the app and the second was that you might have had an custom build container-browser for SSRS reports project. Personally I dont feel these options are that rare, as both mentioned were used at my last employer, and the second mentioned, the custom build container for SSRS reports was an front-end to a pretty robust financial Data Warehouse solution.

So what will be the steps in this process?

1.If the user runs the report for the first time, the ShowFields multi-valued report parameter shows all column names for this report, user might change the values chosen in the ShowFields parameter and they are being sent with SSRS global variables Windows login of the user and the report name to the stored procedure ReportColumnParameterCache_DeleteInsert residing at the end of the stored procedure displaying this report dataset

2.This SP deletes the cache from last time and inserts the current values

3.Next time the user runs the report, the ShowFields multi-value parameter default values are being filled from an UDF retrieving cached results for the current user and the current report

4.The process is repeated except that now the parameter contains fields chosen by the user last time

Let me guide you through the steps needed to accomplish this report personalization. Lets start with some DB back-end stuff. I remember when I was developing this solution, I prepared an report -level configuration table rpt.Report holding Report_ID INT set as identity column , ReportName, ReportDescription, IsActive flag and IsVisible flag ( for report listing in the application purposes – this way you could really quickly state that the report is under construction, is obsolete etc. ). The sky is the limit here. The second configuration table rpt.ReportColumn would be used for report column configuration holding ReportColumn_ID INT as Identity, Report_ID from the rpt.Report table, ReportColumnName, IsActive flag,  IsVisible flag for country-specific purposes ( when a column might be never needed for users from some country in a multi-country enviroment ) and IsAdditional flag determining whether this column is an additional or a non-additional column. The next step might be a little bit time consuming as you must fill these tables with values , but you can come up with some clever string concatenation generating insert statements in Excel.

Now you need to create a table holding the report parameter cache. It must be a quick and a narrow one for some solid performance. I called mine rpt.ReportColumnParameterCache and it had just ReportColumnParameterCache_ID as an Identity column, ReportColumn_ID, and the Windows user login UserID named ReportUser NVARCHAR(100) column. MSDN describes UserID as The ID of the user running the report. If you are using Windows Authentication, this value is the domain account of the current user. The value is determined by the Reporting Services security extension, which can use Windows Authentication or custom authentication. 

image001

And what are the next steps? We have to create a stored procedure ( lets name it rpt.ReportColumnParameterCache_DeleteInsert ) which has input parameters from SSRS report parameters UserID, ReportName (global variables) and ColumnNames ( ColumnNames would look like Column1,Column2,Column3 coming from the ShowFields report multi-valued parameter explained in the next paragraph) . Firstly this SP would delete your login-related cache for this report from the last time. I remember doing some basic measurements using time statistics and the scenario with deleting and inserting the cache rows was a little bit faster in my enviroment then any other option available. In the next step, this SP would transform the ReportName to the Report_ID from rpt.Report and then the ColumnNames into ReportColumn_IDs using a table value UDF and an inner join to the rpt.ReportColumn based on ReportColumnName and ReportName. Here are some examples how to implement a custom split-string function or in case you are lucky and already using MSSQL 2016, you can go with the internal SPLIT_STRING function. In the last step, this SP would insert these values into the rpt.ReportColumnParameterCache table. This SP has to be located at the end of the stored procedure used for retrieving the data into the report main dataset.

On the SSRS side, we need to create a report parameter holding multiple string values called ShowFields. This parameter should have available values fulfilled from a query showing available columns for this Report. The default values would be the values you have stored in your ReportColumnParameterCache table for your Windows login and this report, so in this case, it would be the result of a function containing a select, where the ReportUser value equals your Windows login (globals!UserID) and the ReportName (globals!ReportName) equals the value in the rpt.Report table.

The last step is to set the affected columns visibility in the SSRS report table / matrix based on a formula saying something like:

=iif(instr(Join(Parameters!ShowFields.Value,”, “) ,”Column1″) <> 0 and instr(Join(Parameters!ShowFields.Value,”, “) ,”None”) = 0,false,true)