none
Report Designer: Selections of Measures. RRS feed

  • 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
    Monday, February 9, 2009 3:05 PM

Answers

  • 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/
    • Marked as answer by fix105 Wednesday, February 11, 2009 9:11 AM
    • Unmarked as answer by fix105 Thursday, February 12, 2009 7:06 AM
    • Marked as answer by fix105 Friday, February 13, 2009 4:13 PM
    Tuesday, February 10, 2009 6:37 PM
    Moderator
  • 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.
    Monday, February 9, 2009 7:35 PM
    Answerer

All replies

  • 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.
    Monday, February 9, 2009 7:35 PM
    Answerer
  • 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/
    Monday, February 9, 2009 9:32 PM
    Moderator
  • 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
    Tuesday, February 10, 2009 1:50 PM
  •  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/
    Tuesday, February 10, 2009 2:33 PM
    Moderator
  • Ok, I have tried this query, I have received a Column with value Null for All rows.


    Thanks for your help.
    fix
    Tuesday, February 10, 2009 3:03 PM
  •  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
    Tuesday, February 10, 2009 5:52 PM
  • 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/
    • Marked as answer by fix105 Wednesday, February 11, 2009 9:11 AM
    • Unmarked as answer by fix105 Thursday, February 12, 2009 7:06 AM
    • Marked as answer by fix105 Friday, February 13, 2009 4:13 PM
    Tuesday, February 10, 2009 6:37 PM
    Moderator
  • 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
    Wednesday, February 11, 2009 9:15 AM
  • 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
    Wednesday, February 11, 2009 2:52 PM
  • Anyone have a solution ?
    fix
    Friday, February 13, 2009 7:07 AM
  • 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
    Friday, February 13, 2009 4:13 PM
  • 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/
    Friday, February 13, 2009 10:09 PM
    Moderator
  • Strange think.
    With the OLE DB Connection, I Can retreive ALL Measures.
    With the Analysis Services Connection, I Can't.


    fix
    Monday, February 16, 2009 9:55 AM
  • 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
    Monday, September 21, 2009 6:56 PM