locked
KPI target MDX RRS feed

  • Question

  • Hi,
    I am trying to put the target of my KPI as an MDX formula.
    My target contain the average of last six months data.
    But when i put the following query it give me error as it require a tuple expression whereas its an numerical expression.

    avg

    ({(strtomember('[Fix Actualid].[monthnum].&['+cstr(month(vba![date]()))+']')),

    (

    strtomember('[Fix Actualid].[monthnum].&['+cstr(month(vba![date]()))+']')).lag(1),

    (

    strtomember('[Fix Actualid].[monthnum].&['+cstr(month(vba![date]()))+']')).lag(2),

    (

    strtomember('[Fix Actualid].[monthnum].&['+cstr(month(vba![date]()))+']')).lag(3),

    (

    strtomember('[Fix Actualid].[monthnum].&['+cstr(month(vba![date]()))+']')).lag(4),

    (

    strtomember('[Fix Actualid].[monthnum].&['+cstr(month(vba![date]()))+']')).lag(5)}

    ,[Measures].[Fact Call Count])

    Please let me know the correct way to implement it.

    Thanks
    lntinfotech
    Friday, April 3, 2009 10:41 AM

Answers

  • The code samples I provided where used in both SSMS and PPS and that is where I tested the MDX formula in a KPI within a Scorecard with the Adventure Works DW data.  Make sure you are using the double quotes instead of single, both will work in SSMS, but only the double will work in PPS in the MDX formula.
    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    • Marked as answer by Rahul_DWH Tuesday, April 7, 2009 1:21 PM
    Monday, April 6, 2009 4:22 PM

All replies

  • Have you tried running this in SQL Server Management Studio to test this calculation just to verify it is working properly.  I did a sample against the Adventure Works DW similar to this and it is working fine.  The one thing I noticed though was that you need to use double quotes (") instead of single quote ('), otherwise I received an ERROR! and it stated in the following in the Application Eventlog:

    Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: Query (2, 126) Parser: The syntax for '[Date]' is incorrect.

    So here is the MDX forumula I used to get the '3 Month Avg' in the Target:

    Avg(
    
    {
    
    strtomember("[Date].[Fiscal].[Month].&[2003]&["+cstr(month(vba![date]())) +"]"),
    
    strtomember("[Date].[Fiscal].[Month].&[2003]&["+cstr(month(vba![date]()))+"]").lag(1),
    
    strtomember("[Date].[Fiscal].[Month].&[2003]&["+cstr(month(vba![date]()))+"]").lag(2)
    
    },
    
    [Measures].[Reseller Sales Amount]
    
    )
    
    


    The one question I had about your MDX and what you are attempting to pull back is the last six month information.  In looking at your attribute you are just pulling at Month and not incorporating Month & Year, so you would be getting the overall average of these months over time, not for a specific Month & Year.  Assuming that you want the Month & Year, but maybe not.

    Also, instead of doing each month, you can look at doing a range of values using a starting and ending date seperated by a colon (:) like the following to simplify the code:

    Avg(
    
    {
    
    strtomember("[Date].[Fiscal].[Month].&[2003]&["+cstr(month(vba![date]())) +"]"):
    
    strtomember("[Date].[Fiscal].[Month].&[2003]&["+cstr(month(vba![date]()))+"]").lag(2)
    
    },
    
    [Measures].[Reseller Sales Amount]
    
    )


    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    • Edited by Dan English Saturday, April 4, 2009 2:23 PM just specifying the last code block to be sql
    Saturday, April 4, 2009 2:20 PM
  • Hi Dan,
    Thanks for your answer bro...
    the MDX expression is working fine in SSMS but not in PPS.....
    I don't know whether you tried it in PPS or not.

    Yeah you are right i have to incorporate the month as well as year for the calculation....its not working in pps.

    lntinfotech
    Monday, April 6, 2009 3:15 PM
  • The code samples I provided where used in both SSMS and PPS and that is where I tested the MDX formula in a KPI within a Scorecard with the Adventure Works DW data.  Make sure you are using the double quotes instead of single, both will work in SSMS, but only the double will work in PPS in the MDX formula.
    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    • Marked as answer by Rahul_DWH Tuesday, April 7, 2009 1:21 PM
    Monday, April 6, 2009 4:22 PM