locked
Using MDX in KPI Data Mappings for Target values RRS feed

  • Question

  • As per the PPS Team Blog, I have been trying to create a target value equivalent to parallel period 1yr in the past.

     

    http://blogs.msdn.com/performancepoint/archive/2007/06/15/mdx-in-dashboards-scorecards-and-views.aspx

     

    I have established the correct MDX to generate the data set I want, but I want the KPI Data Mapping to run based off a filter...in other words, I have a filter that allows you to select the month for your query, and I want the target to be driven by this filter to select the parallel period (1 yr) to the actual value KPI. The above example shows how to do this but not driven by a filter parameter (or even in great detail...where does the MDX go?)

     

    I have tried adding the following MDX to the "Use MDX tuple formula" under the Dimensional Data Sourc Mapping for the target value:

    ParallelPeriod ([Date of Posting].[Fiscal Yr-Mth of Posting].[Fiscal Month of Posting], 12, <<UniqueName>>.DefaultMember)

     

    with no success (generates an error! on the KPI list)...

     

    How are data mappings structured?

     

    Thanks

     

     

     

    Monday, November 5, 2007 7:23 PM

Answers

  • Hi Ludis,

    The MDX tuple formula area in a KPI data mapping does not support the <<UniqueName>> argument; it doesn’t need to in this case. The PPS team blog post detailed it as part of a filter link formula which is where the <<UniqueName>> can be used. You need to use pure MDX in the KPI data mapping area. In this case CURRENTMEMBER should do the trick for you.

    Try this:

    1.       Create the filter link (for now, do not configure a filter link formula) in the dashboard to provide the correct month/year value to the scorecard filter endpoint. The user selects the fiscal month they want in the filter and the scorecard KPI data is then sliced by that value.

    2.       Your target MDX formula needs to reference the month in the year prior to the value selected in the filter – use CURRENTMEMBER. i.e. the selected Fiscal Month value. You don’t need to reference <<UniqueName>> in this context. This is assuming the filter created is using members of the [Date of Posting]. [Fiscal Yr-Mth of Posting] attribute. Also remember that this is a tuple formula, the ParallelPeriod function returns a member, so you need to tell it what measure you wish to retrieve the value for. So the MDX formula would need to be something like:

     

    (

    ParallelPeriod ([Date of Posting].[Fiscal Yr-Mth of Posting].[Fiscal Month of Posting], 12, [Date of Posting].[Fiscal Yr-Mth of Posting].CURRENTMEMBER),

    [Measures].[MeasureYouWantToUseForTheTarget]

    )

     

    Cheers,

    Nick
    Monday, November 5, 2007 8:32 PM

All replies

  • Hi Ludis,

    The MDX tuple formula area in a KPI data mapping does not support the <<UniqueName>> argument; it doesn’t need to in this case. The PPS team blog post detailed it as part of a filter link formula which is where the <<UniqueName>> can be used. You need to use pure MDX in the KPI data mapping area. In this case CURRENTMEMBER should do the trick for you.

    Try this:

    1.       Create the filter link (for now, do not configure a filter link formula) in the dashboard to provide the correct month/year value to the scorecard filter endpoint. The user selects the fiscal month they want in the filter and the scorecard KPI data is then sliced by that value.

    2.       Your target MDX formula needs to reference the month in the year prior to the value selected in the filter – use CURRENTMEMBER. i.e. the selected Fiscal Month value. You don’t need to reference <<UniqueName>> in this context. This is assuming the filter created is using members of the [Date of Posting]. [Fiscal Yr-Mth of Posting] attribute. Also remember that this is a tuple formula, the ParallelPeriod function returns a member, so you need to tell it what measure you wish to retrieve the value for. So the MDX formula would need to be something like:

     

    (

    ParallelPeriod ([Date of Posting].[Fiscal Yr-Mth of Posting].[Fiscal Month of Posting], 12, [Date of Posting].[Fiscal Yr-Mth of Posting].CURRENTMEMBER),

    [Measures].[MeasureYouWantToUseForTheTarget]

    )

     

    Cheers,

    Nick
    Monday, November 5, 2007 8:32 PM
  • Sweet!...I also was coming to this same conclusion, except for my desire to use a

    Code Block
    <<UniqueName>>

     

     

    parameter...again I find myself overdoing it (new to MDX)...I see that the filter is automatically feeding the member to the scorecard:

     

    Code Block
    ([Measures].[STP Charges], ParallelPeriod ([Date of Posting].[Fiscal Yr-Mth of Posting].[Fiscal Month of Posting], 12, [Date of Posting].[Fiscal Yr-Mth of Posting].[Fiscal Month of Posting].CURRENTMEMBER ))

     

     

     

    Nick...you da man!

     

    PS I am a big fan of your blog

     

    Thanks!!

     

    Ludis

     

     

     

    Monday, November 5, 2007 8:58 PM
  • No problem Ludis. Glad to have been of help.

     

    Thanks for the positive feedback on the blog - much appreciated.

     

    Cheers,

    Nick

    Monday, November 5, 2007 9:03 PM
  •  

    Hi,

     

    I have two date list filters one is for Fromdate and another is for Todate. My KPI should get the actual values from the cube for given date range(Between Fromdate and Todate).Can u show me how to create MDX tuple formula for this.

    Thanks in advance.

     

    Bobby.

    Friday, May 23, 2008 6:47 AM