locked
MDX tuple formula in the Dimension Data Source Mapping of a KPI RRS feed

  • Question

  • Hi All,

    I'm trying to build a Scorecard in Dashboard Designer using some KPI...

    the default selection of measure and dimension filter is not going to work for my scope and I need to build a MDX tuple forumala...

    unfortunatelly my skills in MDX are very poor, so I'm asking help on how write a MDX quesry to the cube in SSAS...

    I have to select a mesure filtered buy some dimensions...

    I need to shoe in the Actual value of my KPI, the "Profit Margin"

    filtering the [TBL TIME DIMENSION] in the today date (please notice that the today date is a string....)

    and filtering the [Dim Contract Type Name].[Contract Type Name] with 2 values: "Billable" OR "Active"

    I have try something like the following, but is not working.....

    ([Measures].[Profit Margin],

    ([TBL TIME DIMENSION].[TBL TIME DIMENSION].&[20100219],

    [Dim Contract Type Name].[Contract Type Name].&[Billable],

    [Dim Contract State Code].[Contract Type Name].&[Active]))

    any suggestion???

    how can I generate the today date in a string in the following format: "year""month""day" (20100219)???

    thanks all

    Vit

    Friday, February 19, 2010 8:19 AM

Answers

  • The MDX for this should look something like the following if you were trying to get reference to the current date:

    SUM(({[Dim Contract Type Name].[Contract Type Name].&[Billable],
    [Dim Contract State Code].[Contract Type Name].&[Active]},
    strtomember("[TBL TIME DIMENSION].[TBL TIME DIMENSION].&[" + format(now(),"yyyyMMdd") + "]")),[Measures].[Profit Margin])

    Now if I recall correctly in PPS you use a single quote versus the double quotes.  So if it doesn't work with double quotes try it with the single.

     


    Dan English's BI Blog
    Friday, January 27, 2012 2:53 AM

All replies

  • In case you ended up with a solution please provide it here so others (like me :) ) can benefit from it. I'm also interested in that sort of data manipulation.

    Regards,

    Thursday, January 26, 2012 10:06 PM
  • The MDX for this should look something like the following if you were trying to get reference to the current date:

    SUM(({[Dim Contract Type Name].[Contract Type Name].&[Billable],
    [Dim Contract State Code].[Contract Type Name].&[Active]},
    strtomember("[TBL TIME DIMENSION].[TBL TIME DIMENSION].&[" + format(now(),"yyyyMMdd") + "]")),[Measures].[Profit Margin])

    Now if I recall correctly in PPS you use a single quote versus the double quotes.  So if it doesn't work with double quotes try it with the single.

     


    Dan English's BI Blog
    Friday, January 27, 2012 2:53 AM