Report Designer: Selections of Measures.

Question

 

Hi,
 
I would like to create a report (with a chart). I'm using different parameters. For the category fields (axe x), i'm using Date and Hour (2 groups) and for Series fields that's the measures value.
When I have created the DataSet ->  I have dropped 3 dimensions in the Filter pane and Date - Hour + 2 Measures in the Data pane.

I would like to build a report more dynamic. Instead of drag and drop measures in the Data Pane, I would like to have a sort of list of measure, and users will be able to select the measure to see the Chart for the selected value.

Do you know if it's possible to do that ?

Thanks you,

Fix

---

fix

Answer 1

Please clarify if I'm understanding this correctly.  You:

• Are using an SSAS data source for your report
• Want the person viewing the report to be able to choose which Measure shows up in the chart while they are viewing their report.

If that's what you want, it is possible.  You will need to write some custom MDX to do it.  At a high level, here's what you need to do:

1. Create a report parameter that lists the measures that you want to query
• The parameter 'value' should be its unique name, and the measure 'label' should be its caption
• You can hard-code this list, of have it coming from the cube
2. Add a parameter to your existing MDX query that gets its value from the report parameter you added above
3. Update the MDX query to use the StrToMember to convert the parameter value (i.e., measure Unique Name) into the actual measure

Let me know if this helps or you'd like more details.

Thanks,
Denesh.

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 2

In addition to what Denesh says, another option is to change the query . SSAS is designed to facilitate ad hoc reporting. Business users could use Report Builder 2.0 and change the query ad hoc by using the MDX Query Designer.

---

Home page and blog: http://www.prologika.com/

Answer 3

Exactly, That's what I need.

My problem is: how can I have the List of Measures ?

I cannot Hard-Code the list, I need to retreive data's from the cube.
I have tried the Select [Measures].AllMembers. -> Not Good.

Maybe I need something else coming from the cube ?

Kind regards,

---

fix

Answer 4

 select null on 0,

[Measures].AllMembers on 1 --to get regular and calculated, or just .Members to get regular measures only

from <cube>

---

Home page and blog: http://www.prologika.com/

Answer 5

Ok, I have tried this query, I have received a Column with value Null for All rows.


Thanks for your help.

---

fix

Answer 6

 If I understand correctly, that's what you need.  You need to first get a list of all the measures in the cube which you will use to create a parameter with.  Set the parameter's source to the results of this query.  Then, your users can pick the measure they want and you will take the value of that parameter and use it in the query that feeds the chart so that they just get the measures that the user selected. 

I think what might be the issue here is that you are getting all the measures back and maybe you only want the user to have a choice amongst a smaller set of measures because they don't have any data associated with a particular measure.  If that's the case, you need to modify the query you are using for the parameter to possibly use the CROSSJOIN function or use a WHERE clause where you can intersect something else you know about the user running the report or even to scope the parameters to something where Measures.Value > 0 or for a specific time period as examples.  Otherwise, getting back the list of all the measures is what I would expect you need.

-Sean

---

Program Manager, SQL Server Reporting Services

Answer 7

Actually, the query works in SSMS but it doesn't in Report Designer probably because the results are requested as a flattened dataset as opposed to a cellset. So, we need a hack. The following query should give you the results you need. You can ignore the first column in the dataset.

WITH SET MYSET AS HEAD([Date].[Date].[Date].MEMBERS, COUNT(MEASURES.ALLMEMBERS)-1)

MEMBER MEASURES.MeasureUniqueName AS  MEASURES.ALLMEMBERS.ITEM(RANK([Date].[Date].CURRENTMEMBER, MYSET)-1).UNIQUENAME

MEMBER MEASURES.MeasureDisplayName AS MEASURES.ALLMEMBERS.ITEM(RANK([Date].[Date].CURRENTMEMBER, MYSET)-1).NAME

SELECT {MEASURES.MeasureUniqueName, MEASURES.MeasureDisplayName} ON 0,

MYSET ON 1

FROM [Adventure Works]

---

Home page and blog: http://www.prologika.com/

Answer 8

Thanks you for everyone that was really helpful.

Sean, thanks too, your answer will help me in the future.

At the moment, I don't need to load particular measure. But I will try your solution later, when I will have more time :)

---

fix

Answer 9

Sorry I'm back :)

1. Create a report parameter that lists the measures that you want to query
• The parameter 'value' should be its unique name, and the measure 'label' should be its caption
• You can hard-code this list, of have it coming from the cube
2. Add a parameter to your existing MDX query that gets its value from the report parameter you added above
3. Update the MDX query to use the StrToMember to convert the parameter value (i.e., measure Unique Name) into the actual measure

About these points:

1. Now I have my list in a DataSet
  -> I have created a new Parameter "Indicator" Which attached with the DataSet "List Measures". Value is MeasureUniqueName and Label is MeasureDisplayName.
2. In my principal DataSet (used for the Chart), I added a new Parameter -> Ref to the "Indicator" Parameter.
3. For the MDX Query, Instead of Select NON Empty { [Measures].[MeasuresNameUnique] } I have written SELECT NON EMPTY { StrToMember(@Indicator) }.


When I want to leave, I receive a popup error : Parameter Indicator doesn't exist. So I push on the icone "Query Parameters" - Add a new Parameter Indicator. But I have to define a default value for each filter (I don't want that).


After to define each Filter, That's work. When I change the selected measure, that doesn't work.

What's wrong with my modification ?

Thanks you.

---

fix

Answer 10

Anyone have a solution ?

---

fix

Answer 11

My problem is solved.

Solution:

Query Designer -> "Design Mode" -> Click on the button "Query Parameters" -> Add @Indicators with a default value.

In the MDX Code :

WITH MEMBER SelectedMeasure
AS STRTOMEMBER(@Indicators)

SELECT NON EMPTY {SelectedMeasure } ON COLUMNS

Oh of course, you have to create a link between your Parameter Indicator and your DataSet (The one created to Select ALL Measure)

OK.

Thanks you ALL. :-)

---

fix

Answer 12

I'd personally bypass the SSRS Analysis Services provider and use the OLE DB Provider for Analysis Services to avoid some of nagging limitations of the built-in provider. With the OLE DB provider, you have to resort to expression-based queries but you gain an ultimate freedom :-).

BTW, for the sake of completeness, I provided an alternative solution to obtain a list of measures and optionally measure properties that uses the SSAS MDSCHEMA_MEASURES schema rowset.

---

Home page and blog: http://www.prologika.com/

Answer 13

Strange think.
With the OLE DB Connection, I Can retreive ALL Measures.
With the Analysis Services Connection, I Can't.

---

fix

Answer 14

Hi,
I have a very similar situation. I have 3 measures which are needed to populated at the report parameter. When the user selects one measure, the data has to get populated in the detailing region. Can you guide me how to do it. I am not able to load the parameter into query parameter window. reply asap.
Thanks
bonny