locked
Passing a parameter to be used as the measure in a SSRS report RRS feed

  • Question

  • Hello all, I have a dashboard with a linked scorecard and PPS Analytical chart where the measure displayed on the chart changes when you click a KPI in the card by passing a custom property 'chartlinkage' which has a value of the underlying measure for the chosen KPI.  This works OK but as the drill through is disabled due to the chart using custom MDX I am looking to replace the flat and visually naff PPS chart with something a little more exciting from SSRS but I cannot work out how to pass the clicked KPI measure in the card to a parameter in the MDX in the report.

    So my query is do I have to:

    1.Use a case statement in the MDX of the dataset of the SSRS report something like

    WITH MEMBER Measures.MyMeasure
    AS CASE Parameters!ChartLinkage.Value
       WHEN "Fees Costs" Then [Measures].[Fees Costs]
       WHEN  "Ground Rent Costs" Then [Measures].[Ground Rent Costs]
       WHEN  "Insurance Costs" Then [Measures].[Insurance Costs]
       WHEN  "Leasehold Costs" Then [Measures].[Leasehold Costs]
       WHEN  "Legal Costs" Then [Measures].[Legal Costs]
       WHEN  "Letting Costs" Then [Measures].[Letting Costs]
       WHEN  "Maintenance Costs" Then [Measures].[Maintenance Costs]
       WHEN  "Other Costs" Then [Measures].[Other Costs]
       WHEN  "Unallocated Costs" Then [Measures].[Unallocated Costs]
       WHEN  "Utilities Costs" Then [Measures].[Utilities Costs]
       ELSE  [Measures].[Total Costs]
     End
         
     SELECT NON EMPTY { [Measures].[MyMeasure] } ON COLUMNS, NON EMPTY { ([Time].[English Month Name].[English Month Name].ALLMEMBERS ) }
    DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
    ( SELECT ( [Last12ReportingMonths] ) ON COLUMNS FROM [MyCube])
    2. Rather than passing text and using a case statemnet is there a way to pass the actual measure as I was in PPS e.g. @ChartLinkage =[Measures].[Legal Costs]
    2. Use some equivalent of a stored procedure to custom make the MDX on the fly depending upon a passed parameter
    3. Something else.

    Any help gratefully received.


    TIA


    Friday, January 29, 2010 10:02 PM

All replies

  • As far as your first configuration, I don't believe you have this setup properly.  You shouldn't be doing any custom MDX, you should just be passing the value that you have stored in the custom property you added to the KPI to the grid to swap out the measure being referenced.

    Passing Context from Scorecards to other Report Views

    For the second setup you would need to look at doing some parameterized MDX where you would pass in the name of the measure (like 'Measures.[Total Costs]') to the parameter in the SSRS report and then your MDX would look like the following:

    = "WITH MEMBER Measures.MyMeasure
    AS " + Parameters!ChartLinkage.Value + "

    SELECT NON EMPTY { Measures.MyMeasure } ON COLUMNS, NON EMPTY { ([Time].[English Month Name].[English Month Name].ALLMEMBERS ) }
    DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
    ( SELECT ( [Last12ReportingMonths] ) ON COLUMNS FROM [MyCube])"



    Dan English's BI Blog
    Saturday, January 30, 2010 9:50 PM
  • Dan, thanks for the steer. I have changed the MDX of the SSRS report to:

    WITH MEMBER Measures.MyMeasure

    AS @ChartLinkage

    SELECT NON EMPTY { [Measures].[MyMeasure] } ON COLUMNS, NON EMPTY { ([Time].[English Month Name].[English Month Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( [Last12ReportingMonths] ) ON COLUMNS FROM [MyCube])

    and added the chart linkage parameter in the query designer and it works.

    I have also change the chart title to the expression

    =mid(Parameters!ChartLinkage.Value,13,len(Parameters!ChartLinkage.Value)- 14) to dynamically change the title.

     


    Thanks for your help.

    Kevin

    Sunday, January 31, 2010 9:31 AM
  • Glad that you got it working.

    I would still revisit the other link I provided to leverage an Analytical Chart instead.  That will provide the interactive capabilities out-of-the-box and ability to drilldown to detail (assuming it isn't a calculated member).  You don't need to configure parameters in the Analytical Chart in the custom MDX at all, you can just link the custom property on the KPI to your measure in the chart (no customization of MDX required).
    Dan English's BI Blog
    Sunday, January 31, 2010 1:25 PM
  • Dan, thanks for the suggestion but i really dont need the drill down for this part of the project but I do need it to look good and the PPS charts aren't really up to the mark as the users are seeing Dundas Charts elsewhere in Sharepoint...

    Thanks Again.

    Kevin
    Sunday, January 31, 2010 4:09 PM
  • A further query on this now that I have the MDX accepting a parameter and drawing the chart correctly -  how do I get the SSRS Chart Y Axis number format to use the format_string of the measure (Which is passed as a parameter so can be of many different types). Do I need to write a function to convert the SSAS format string to SSRS Format if so does anyone have an example?

    I have had to resurrect the SSRS reports as the PPS analytical charts are taking 15 seconds + to render against 3 for the SSRS when each are embeded in a dashboard then published on a sharepoint page.

    TIA

    Kevin

     

     

    Friday, April 16, 2010 3:08 PM