Sometimes you’ll find yourself in the position of building SSRS reports where you have parameters with a large number of available values. Using the Adventure Works cube as an example, if you were to drag the Customer attribute from the Customer dimension onto the filter area of the Query Designer for a simple query and check the Parameters box like so:
…you’d end up with a parameter where you can choose any customer to filter on – and there are 18485 customers on that hierarchy.
If you right-click on your data source in the Report Data pane (in BIDS in SSAS 2008) and check the Show Hidden Datasets option, you can see the MDX query that BIDS generates to return the list of available values for the parameter query:
Here’s what the query will look like for the Customers hierarchy for the Customer dimension:
WITH
MEMBER [Measures].[ParameterCaption]
AS [Customer].[Customer].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue]
AS [Customer].[Customer].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel]
AS [Customer].[Customer].CURRENTMEMBER.LEVEL.ORDINAL
SELECT
{[Measures].[ParameterCaption]
, [Measures].[ParameterValue]
, [Measures].[ParameterLevel]}
ON COLUMNS
, [Customer].[Customer].ALLMEMBERS
ON ROWS
FROM [Adventure Works]
On my laptop this query executes in just over 1.5 seconds. Not bad, you might think, for a query that returns a fairly large number of rows. But we can do better!
This query returns all customers on rows and three columns: the caption of each member, the unique name, and the ordinal of the level (which is used for indenting the caption of each member in the dropdown list for the parameter, so you can easily distinguish between members on different levels). These values are returned as calculated members, but they can also be obtained as member properties and this is the key to tuning the query. So, if you create a new OLEDB connection to the cube (ie you don’t use the built-in Analysis Services connection type but you create a data source that connects to the cube using the OLEDB connection type)…
…and then create a new dataset with the following query:
WITH
MEMBER MEASURES.DUMMY AS NULL
SELECT
{MEASURES.DUMMY}
ON COLUMNS
, [Customer].[Customer].ALLMEMBERS
DIMENSION PROPERTIES UNIQUE_NAME, MEMBER_CAPTION, LEVEL_NUMBER
ON ROWS
FROM [Adventure Works]
CELL PROPERTIES VALUE
You’ll find you get almost exactly the same data back, although this time the query returns in around 0.2 seconds.
A few things need to be noted here. First of all, although MDX allows you to put an empty set on columns, in the query above I had to create a dummy calculated measure that returned null because otherwise the query didn’t return any rows from the OLEDB data source. Secondly, in the new query the All Member unique name and caption come out as nulls – that’s normal behaviour for flattened rowsets (which is what you get when you run queries through an OLEDB connection), unfortunately, and again something we’re going to have to work around ourselves. Thirdly, we also need to create a column with indented member names – the original parameter dataset did this using a SSRS expression in a calculated field – although in this case, where there’s only one level underneath the all member, we could probably skip this and not hurt usability.
To trap the nulls and make sure the All Customers member appears as a parameter option, you can use a calculated field on the new dataset with an expression like this:
=iif(
Fields!Customer_Customer_Customer_UNIQUE_NAME.Value is Nothing
, "[Customer].[Customer].[All Customers]"
, Fields!Customer_Customer_Customer_UNIQUE_NAME.Value)
And to generate the indented captions you can use an expression like this:
=iif(
Fields!Customer_Customer_Customer_UNIQUE_NAME.Value is Nothing
, "All Customers"
, " " + Fields!Customer_Customer_Customer.Value)
You then need to delete the original parameter dataset, point the report parameter to the new dataset and bind these two columns to it value and label fields. And lo and behold, you have a report that runs just over a second faster than it did before. This might seem like a lot of hassle to go through for such a small gain, but if you have more than one large parameter the time savings will add up and your users will notice the difference.
Any idea why the performance gain is due to? Did you get a chance to look at the profiler? I was just thinking why there is so much of gain.
Profiler doesn\’t tell you anything here. I think it\’s just that the rewritten version goes direct to the formula engine to get the properties, whereas the original has the overhead of calculated members. But I\’m not really sure.
If you want to edit the auto-gen\’d MDX instead of recreate, you can use the suppressing flag. You\’ll probably want to do this for a couple of reasons.1. If you simply EDIT the MDX and save…then go back to the Original Datasource that created the Parameter Dataset…it WILL overwrite your changes.2. I haven\’t tested this…but even if you use the "create a new parameter DS and delete the auto-gen\’d one"…again, going back into the original dataset and modifying may simply create another hidden DS for you. Whether or not that gets wired up automagically to your Parameter is irrelevant..you now have that super slow hidden Param DS sucking up resources again.So drop to Design and use the Suppressing flag on the dataset. Exact syntax here:http://blog.summitcloud.com/2009/12/suppress-auto-update-of-mdx-parameter-datasets-in-reporting-services-2008/
Thanks David – that\’s very useful to know!
For performance I usually run the parameter-MDX as part of SSIS after recalculating the cubes. I store the result in a SQL Server table and pulls the parameter list from the SQL-source. I guess this will always be the fastest way to load a parameter list in SSRS.
Was just wondering about your comment on the query being faster maybe because it picks from the value from FE. Since the WITH clause is there, wouldn’t it be from the SE cache rather than the FE cache?
No, what I meant is that the value is being calculated and returned by the formula engine. The presence of the WITH clause would prevent these values being cached after the query has finished, but I assumed in this article that all queries were running on a cold SE and FE cache.
Just had a look at the traces and I think the reason for the extra time is because
– there is an extra activity in terms of querying the dimension which goes for Scenario 1 (you basically have to query the dimension to display the 3 calculated members) while in scenario 2, you dont have to query the dimension as null is being displayed
– also, since 3 measures are there in the axes, the Serialize EventsSubClass would be 3 times more in scenario
When I changed the first query also to display one measure like
WITH
MEMBER [Measures].[ParameterCaption]
AS [Customer].[Customer].CURRENTMEMBER.MEMBER_CAPTION
SELECT
{[Measures].[ParameterCaption]
}
ON COLUMNS
, [Customer].[Customer].ALLMEMBERS
DIMENSION PROPERTIES UNIQUE_NAME, LEVEL_NUMBER
ON ROWS
FROM [Adventure Works]
then the only difference in the trace activity was the query dimension for the one measure.
Time taken in my laptop was
1) Scenario 1 with 3 measures – 1.1 secs
2) Scenario 1 with 2 measures – .9 secs
3) Scenario 1 with 1 measure – .7 secs
4) Scenario 2 with dummy measure – .5 secs
which indicates that around .2 secs were being used for the Query dimension activity for each measure being displayed.
Disclaimer : Test conditions might not have been ideal, but each scenario was tested 3 times each and all times, similar results were returned.
You could simplify a little bit more the query by using this kind of syntax even I’m not sure it will provide any performance benefit on a cold cache
SELECT
{[Measures].[Customer]}
ON COLUMNS
, [Customer].[Customer].ALLMEMBERS
DIMENSION PROPERTIES UNIQUE_NAME, MEMBER_CAPTION, LEVEL_NUMBER
ON ROWS
FROM $Customer
CELL PROPERTIES VALUE
It returns exactly the same data and avoid the WITH clause.
Furthermore it works perfectly well with both provider (integrated one and OLEDB, but your query is working as well with the 2 kinds of provider)
Hi Romuald,
The problem with this approach is that you can only query dimension cubes (ie use $DimensionName in the FROM clause) if you’re an administrator, and you probably don’t want to let SSRS connect to SSAS using an administrator account.
Chris
Chris,
I totally forgot this limitation !
For sure it’s not an option in the “real world”.
Therefore, you can use an intermediate version of the query and skip the dummy measure :
SELECT
{}
ON COLUMNS
, [Customer].[Customer].ALLMEMBERS
DIMENSION PROPERTIES UNIQUE_NAME, MEMBER_CAPTION, LEVEL_NUMBER
ON ROWS
FROM [Adventure Works]
CELL PROPERTIES VALUE
But there’s another problem with this query – which is why I didn’t use this approach in my post! If you use this query with the SSAS data source it doesn’t return the dimension properties; and if you use this query with the OLEDB data source then it doesn’t return any rows. The reason why I used a dummy measure is to get around this second problem.
Chris,
I’ve tested it with the SSAS data source. I’ve added the 2 calculated fields to fix the All Customer UniqueName and indented Caption as you explain in your post and it works without any problem. I use the 2008 R2 RTM version (with CU6) maybe you encounteur the missing property behavior on another version ?
HTH
Romuald
I’m using R2 but not CU6 – maybe it’s been fixed, then.
Hi Chris,
I’ve tested the query on R2 RTM (w/o any CU) on a another machine and it works. In fact, It works also on SSRS 2005, once again with SSAS datasource, so I’m confident it will also work on 2008.
What I’ve noticed, is that the DIMENSION PROPERTIES must contain UNIQUE_NAME (and not MEMBER_UNIQUE_NAME as query designer put by default) and in this case you will have one field named as well which contains the correponding data in addition of the Caption one.
Hmm, that’s interesting – thanks! You’re right, I’d been using MEMBER_UNIQUE_NAME when testing…
Hi Chris,
I am facing a problem with SSRS parameters passing for summary to detail report (implementing drill through functionality) . My datasource is SSAS Cube.
I checked in the sql profiler to troubleshoot the MDX script error. I noticed that the error is not constant. I suspect it might be with the sequence in which the parameters MDX scripts excuted to load the dataset for the paramters (i have 6 parameters in total)
Please advice…
Regards,
Sandesh
Can you give more details about what you’re doing exactly, and what the error is?
Thanks for reply. Actually, I have 2 dimensions (i.e geography and time). geography dimension as country, ,state and city roll-up. Time dimension as Year, Quarter and Month roll-up.
I have parametrized these roll-ups in the mentioned sequence. The Summary report works perfectly fine. And when I navigate to detail report from summary by passing the parameters. I am getting error like “Query execution failed for dataset “Month” Parser: The syntax for ‘ ‘ is incorrect.”
When, I extracted only the MDX Script from the sql profiler trace. I noticed that when detail report execution the MDX script to load Month’s parameter’s dataset is executed before Quarter Dataset.
Regards,
Sandesh
I’m not really a SSRS expert and I don’t know the answer to this one – have you tried asking on the SSRS MSDN Forum?
I was hoping this would work with the Tabular Model, but it doesn’t seem to. As results, I only get the member caption and the measure columns. No unique names or levels. Does tabular model not recognize member properties?
It works ok for me – can you try using a DMX query instead of an MDX query in SSRS?
With MDX, I got the UNIQUE_NAME to appear, but no luck with the LEVEL_NUMBER
Hmm, which version of SSRS are you using?
Visual Studio 2010, SSRS 11.0.3000.0
Hmm, well sorry, I have no idea what’s going on here I’m afraid.
Hi,
Good evening. I have some question in SSRS.
Question :–
I am using SSRS.
I have below requirement from the client side.
1.) In report viewer , We want filter the result set of report viewer from user end like excel sheet filter.
Is it Possible ?
2) In Report view , I am showing only five column from dataset into table but While export reports to excel then I want to export all coumns of dataset.
Dataset has 25 columns.
3.)Generate a drop down list from report view result set column and after that apply filter on that.. using SSRS
I want to make a drop down list from the report view table result set .
I want to column name as value of drop down list .
After that I want to filter result set on selected value filed + operator
like,=,>, + text box value.
expression like —
ddl value + operator = 100;
salary(ddl value) >= 2000(text box value) .
Is it Possible ?
4.) show report header above the report parameters in report view using SSRS?
Please suggest.
Hi Mukesh, to be honest I’m not the best person to ask these questions. Can I suggest you post them on the SSRS MSDN forum?
Thanks for kind info…
I have already posted above question at SSRS MSDN.
I get reply on this. but I still have one doubt .
show report header above the report parameters in report view using SSRS?
is It possible?
No, I don’t think so