Archive for the ‘SSAS Querying from SSRS’ Category

SSRS 2008 R2 OLAP reports and overwriting MDX parameters

Pyngback from:

SSRS 2008 (and SSRS 2008R2) have some tremendous reporting enhancements, but unfortunately introduced a feature that caught some people by surprise. The issue is a rather intricate one, and goes as follows:

Suppose you create a report against an OLAP cube. You start by creating a report dataset, using the OLAP query designer. You drag some columns from the cube metadata into the result set area, and you also define one of the OLAP dimensions as a parameter. SSRS creates a second dataset with MDX code that retrieves member values from the dimension attribute you defined as a parameter in the primary dataset. (This allows SSRS to display the parameter values in a report dropdown).

With me so far?

Now suppose you want to modify the MDX code for the 2nd dataset (which SSRS builds as a hidden dataset), to implement some custom behavior for the dropdown. For example, instead of showing all cities in a dropdown, you only want to show those cities with sales (or sales in the last 6 months, etc.) So you retrieve the MDX code for the 2nd (hidden) dataset, and you modify the MDX code. You run the report again, and the dropdown shows values the way you want to show them.

OK, so life is good…but this story isn’t over.

Now go back to the original (primary) dataset and add a column/measure. (For example, maybe initially the report had 4 measures, and you have to add a fifth). So you add a measure to the primary dataset, you save it, you modify the report layout to add the new measure/column, and then you run the report…

And then you discover something – the dropdown parameter isn’t showing the values in the dropdown according to the logic you implemented when you manually modified the MDX code. As a matter of fact, the dropdown is showing the available parameter values as if you’d never modified the MDX code at all!!!

Here’s what happened – when we changed the primary dataset to add a new column, SSRS (well, specifically, Business Intelligence Development Studio) overwrote our custom MDX code with the original MDX code for the dimension parameter!!!

OUCH! This is actual behavior in SSRS 2008 and 2008 R2. As for a workaround, there’s good news and bad news. The good news is that there’s a workaround in the form of a setting. The bad news (well, more like “not great news”) is that BIDS doesn’t expose the setting – we need to modify the actual XML code in the RDL file.

So let’s look at an actual example:

Suppose we create a simple OLAP report against AdventureWorks 2008. We want to show Internet Sales Amount by Product Category, and we want the user to select a country from the Customer Dimension/Country Attribute (Figure 1)

Figure 1:

After we save this dataset, SSRS creates a 2nd dataset that retrieves the countries from the Customer dimension. By default this 2nd dataset doesn’t show in the DataSet window pane. However, we can right-click and show Hidden Datasets, which will wind up displaying the 2nd dataset (which SSRS called CustomerCountry, in Figure 2):

Figure 2:

If we right-click on this 2nd dataset and modify the query, SSRS loads the query designer with the generated MDX code (Figure 3). The code retrieves all countries in alphabetic order, plus the All Country (Customer) total.

Figure 3:

Now suppose we want to modify the query (and therefore the contents of the dropdown) to show the countries in order of sales for the country. So we modify the MDX query to use the MDX ORDER function, to show the countries in order based on sales, in Figure 4: (i.e. country with the highest sales will be first in the dropdown)

Figure 4:

Now we run the report (which shows Product Category and Internet Sales amount, and the prompt for Country with the correct sort order), in Figure 5:

Figure 5:

OK, now let’s go back to the original main dataset, and add a new column (Internet Gross Profit), in Figure 6:

Figure 6:

We add the column to the designer as well, and then go run the report (Figure 7). Notice how the dropdown appears to have “lost” the behavior we implemented with our custom MDX code. This is the root of the behavior in SSRS 2008. What happened was that when we modified the dataset in the previous step, SSRS overwrote our custom MDX changes (without warning us!) To prevent this behavior, we need to modify the RDL file manually, to add a new setting (next step).

Figure 7:

We need to open the RDL file manually, which you can do by right-clicking on the report in Solution Explorer and taking the option “View Code”. At the end of the XML content for the hidden dataset, add the following line: <rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate> (Figure 8).

Figure 8:

Now to test, go back and repeat step 4 (to put the custom MDX code back into the hidden dataset), and then pull up the main dataset and add another column, such as Internet Freight Cost (Figure 9). Previously, this would overwrite our custom MDX code. However, because of the SuppressAutoUpdate setting, SSRS will not update the MDX code.

Figure 9:

So if we go run the report after making the dataset change, our MDX code now stays!

Figure 10:

Posted at 10:00 PM in SetFocus BI/SQL | Permalink

SSAS Dynamic Parameters and SSRS/Dmvs querys


Posted on February 1, 2012 by devinknight

Dynamic Management Views are a very powerful tool that are part of Analysis Services in 2008 and 2008 R2.  They give you the ability to monitor Analysis Services usage and metadata.

MSDN articles on SSAS DMVs –

Good post on using SSAS DMVs –

A report developer could leverage the capability to read Analysis Services metadata to build a report that can dynamically change the type of information that is displayed on a report.

Let’s walk through a basic example of how we can Dynamic Management Views to build a dynamic Reporting Services report.  I’m starting with a basic report that has already been built off of an Analysis Services cube that looks like this:


As you can see it shows Internet Sales by category over several calendar years.  The MDX query used for this is pretty standard as you can see.

Select       [Measures].[Internet Sales Amount] ON Columns        ,NON EMPTY ([Date].[Calendar].[Calendar Year]        ,[Product].[Product Categories].[Category]) ON Rows    From        [Adventure Works]

I start by changing the query to use an in session calculation with the WITH clause so I can basically alias it to [Measures].[Generic Measure].  By aliasing the measure it will make since more later because the field name doesn’t identify with any specific measure.

With       Member [Measures].[Generic Measure]            AS STRTOVALUE(“[Measures].[Internet Sales Amount]”)

Select       [Measures].[Generic Measure] ON Columns        ,NON EMPTY ([Date].[Calendar].[Calendar Year]        ,[Product].[Product Categories].[Category]) ON Rows    From        [Adventure Works]

The next step is to create a parameter in the report so I can pass in the measure name to the query dynamically.  Eventually I will populate this parameter using a DMV but for now I’ll just have it as a text field that I can type in the measure name.

Now to make this query dynamic with my newly created @MeasureName parameter I replace the dataset query with an expression that looks like this:

=”With ”   +”    Member [Measures].[Generic Measure] ”    +”        AS STRTOVALUE(“””+Parameters!MeasureName.Value+”””)  ”

+”Select ”   +”    [Measures].[Generic Measure] ON Columns ”    +”    ,NON EMPTY ([Date].[Calendar].[Calendar Year] ”    +”    ,[Product].[Product Categories].[Category]) ON Rows ”    +”From ”    +”    [Adventure Works] ”


You’ll notice that I’ve basically changed the query to a string in the expression except for the measure name which I’ve changed to use my new parameter.  This report will now work dynamically but the user would have to type in the measure they would like to see in the report.  Not only would they have to type it but they must know the MDX for it.  For example, users would have to type [Measures],[Internet Tax Amount].  Obviously, that’s not going to happen so we need to make this simpler for the user.

This is where Dynamic Management Views help.  We can use the mdschema_measures DMV to return back a list of measures from our cube.  The following query will return back all my measures names, MDX, and format:

SELECT Measure_Name, Measure_Unique_Name, Default_Format_String   FROM $System.mdschema_measures    WHERE Cube_Name = ‘Adventure Works’    ORDER BY Measure_Name

Unfortunately, not all format strings translate to Reporting Services but this could be manipulated using expressions.  Using this query we can create a new dataset and have that dataset populate our @MeasureName parameter.  The dataset cannot be used in entered into a dataset using the Query Builder because it’s not really MDX.  So you must select to build an expression on the new dataset and paste in the query.  It does not require an actual expression so remote the equal sign before the query.


With the dataset completed go back to the @MeasureName properties and change the available values to use the Measure_Unique_Name and Measure_Name fields to populate the parameter dropdown box.


Now that the parameter dropdown box it populated with the DMV query go ahead and preview the report.  You’ll see you can select any measure front the dropdown box and it will change the information shown on the report as shown below: