locked
Fix Value Goal question RRS feed

  • Question

  • I have KPI, for example, Profit Margin%, say, we have three product lines (A, B and C), however they have different target for Profit Margin%,

    so Product A - target 20%, Product B - target 30% and Product C - target 30%, product dimension is on the row and Actual (from cube), and Target are at Column.

    Is there any way I can hard code this with different targets, without adding new target?

    Many thanks

    Don


    Don

    Tuesday, April 3, 2012 11:19 PM

Answers

  • The best method to troubleshoot is to run a SQL Profiler trace on your Analysis services server. Then run the dashboard. You would see the MDX created by PPS and passed on to SSAS. Copy, fix and try to run that MDX script in Management Studio.

    Hope you are using correct dimension names and members since I have just typed references here.


    http://dailyitsolutions.blogspot.com/

    • Marked as answer by Dz0001 Tuesday, April 17, 2012 8:47 PM
    Monday, April 9, 2012 2:45 PM

All replies

  • I look at data mapping, there is MDX Tuple Formula at dashboard designer, maybe this is the place to do this?

    If product A and its children, then target is 20%

    If Product B and its children, then target is 30%

    if Product C and its children, then target is 30%

    Appreciate it if someone can provide pointer on this in MDX


    Don


    • Edited by Dz0001 Wednesday, April 4, 2012 2:07 PM
    Wednesday, April 4, 2012 1:59 PM
  • You seem to be right in terms of where you need to make those changes.

    For MDX you would need to use something like:

    CASE 
    WHEN EXISTS ( Descendants ([Product dim].&[Product A],,SELF_AND_AFTER), [Product dim].currentmember)
    THEN Measures.[20%]
    
    WHEN EXISTS ( Descendants ([Product dim].&[Product B],,SELF_AND_AFTER), [Product dim].currentmember)
    THEN Measures.[30%]
    
    WHEN EXISTS ( Descendants ([Product dim].&[Product C],,SELF_AND_AFTER), [Product dim].currentmember)
    THEN Measures.[30%]
    END
    


    http://dailyitsolutions.blogspot.com/

    Wednesday, April 4, 2012 4:48 PM
  • Umair,

    Thanks for quick reply. I modify the MDX and put in MDX tuple formula, it takes it without error, however when I put KPI into score card, then I got "error retrieveing data..".

    Did I do anything wroing in the expression, also is there way I can see more detail error?


    Don

    Wednesday, April 4, 2012 5:21 PM
  • The best method to troubleshoot is to run a SQL Profiler trace on your Analysis services server. Then run the dashboard. You would see the MDX created by PPS and passed on to SSAS. Copy, fix and try to run that MDX script in Management Studio.

    Hope you are using correct dimension names and members since I have just typed references here.


    http://dailyitsolutions.blogspot.com/

    • Marked as answer by Dz0001 Tuesday, April 17, 2012 8:47 PM
    Monday, April 9, 2012 2:45 PM