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 – http://msdn.microsoft.com/en-us/library/ms126079.aspx
Good post on using SSAS DMVs – http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/
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:
Devin Knight 
Hi Devin,
Do you know how I can limit the list of measures returned by specifying the returned measures in the WHERE clause? I’m aware that the IN operator is not available in DMV’s.
Any help would be greatly appreciated.
Thanks in advance
Carl
Unfortunately you can’t use ‘in’ but here’s an except from the link i have at the top of this post that may help.
We can use ‘and’, ‘or’ and ‘not’. But there is no ‘like’, ‘’ (not equal to), ‘in’ and ‘join’:
select * from AW.[$Dim Product].[$Dim Product] where Color = ‘Blue’ or Color = ‘Silver’
select * from $system.discover_object_activity where not object_rows_returned > 0
Hi Devin,
Thanks for your reply.
I forgot to mention that I did try using the OR operator. However, unfortunately, when enclosing my specified values within single quotes the OR operator turned from the colour blue, indicating it is in use, to green and I received a parser error. I first copied and pasted your example into my query and substituted the measure and values for my own. Unfortunately, this still provided an error.
It appears that the single quotes are ‘commenting out’ my values from the expression.
I have therefore resolved the issue by enclosing my query within double quotes and passing the expression query as a string.
This seems to have worked.
I forgot to mention to you that I am querying against a cube. Do you think that this could have been what caused the issue?
Thanks for your help.
Carl
Hi
I want at first user select measure, then select dimensions related to the selected measures.
I think using measure parameter in select dimensions is useful, but unfortunately I couldn’t place the parameter in the dimension data set query. do you have any Idea?
for example I want to select the measures of a parameters cube (selected by user):
SELECT Measure_Name, Measure_Unique_Name, Default_Format_String, Cube_Name
FROM $System.mdschema_measures
WHERE Cube_Name = Parameters!Cubes.Value
Order by Measure_Name
but it is not work!
Unfortunately the cube name is one thing that can’t be dynamic. You may try to make the entire dataset a string expression and see if that works.
How and where do you tell it to use the Default_Format_String?
Unfortunately the Default_Format_String does not directly translate to SSRS formatting so you would need to use a SWITCH statement in an SSRS expression to translate the format to something SSRS can handle.