locked
PPS Dashboard- Dynamic filter value from cube RRS feed

  • Question

  • Dear users,

    I have the following requirement. I am developing a dashboard in PPS and it has a scorecard. For that dashboard, it is required to pass a Month value (eg MAY 2010); so I have chosen the filter option.

    The Month value to be populated in the filter drop down box is not a straight forward one. We need to select the recent Month Value for which the ABC AMT measue value value is available. So, I thouht of MDX Query filter is the good option.

    Below is the result set from the cube (Note: Used MDX script to retreive this result set). In this, we need to apply some logic to retrieve MAY 2010 and populate it in the filter drop down box as a default value in PPS Dashboard.

     

    ABC AMT

    JAN 2010

    100

    FEB 2010

    110

    MAR 2010

    120

    APR 2010

    90

    MAY 2010

    130

    JUN 2010

    0

    Please guide me to implement this.

    Thanks

     

    Wednesday, June 23, 2010 12:45 PM

Answers

  • Hi,

      Please correct me if i am wrong. You need to display the months as a dropdown where there is a value available for the particular month as a default.  Is it ok if the months drop down appearing in descending order?  if its fine with you, what you can do is, you can paste the below code with modifications w.r.t your time dimension and paste it in the MDX filter to arrange the filter in the descending order

    (Order([Month].[Dim Month].[Dim Month], RANK([Month].[Dim Month].Currentmember, [Month].[Dim Month].[Dim Month]),BDESC))

    Then for the above expresion you could apply a NONEMPTY function with the measure included and this will give you May 2010 by default.

    Nonempty((Order([Month].[Dim Month].[Dim Month], RANK([Month].[Dim Month].Currentmember, [Month].[Dim Month].[Dim Month]),BDESC)),[Measure].[Measure Name])

    The only drawback is your month gets arranged in the descending order.

    HTH,

    Ram

     

     

    • Marked as answer by Jeyakumarvs Thursday, June 24, 2010 2:15 PM
    Thursday, June 24, 2010 3:41 AM

All replies

  • Hi,

      Please correct me if i am wrong. You need to display the months as a dropdown where there is a value available for the particular month as a default.  Is it ok if the months drop down appearing in descending order?  if its fine with you, what you can do is, you can paste the below code with modifications w.r.t your time dimension and paste it in the MDX filter to arrange the filter in the descending order

    (Order([Month].[Dim Month].[Dim Month], RANK([Month].[Dim Month].Currentmember, [Month].[Dim Month].[Dim Month]),BDESC))

    Then for the above expresion you could apply a NONEMPTY function with the measure included and this will give you May 2010 by default.

    Nonempty((Order([Month].[Dim Month].[Dim Month], RANK([Month].[Dim Month].Currentmember, [Month].[Dim Month].[Dim Month]),BDESC)),[Measure].[Measure Name])

    The only drawback is your month gets arranged in the descending order.

    HTH,

    Ram

     

     

    • Marked as answer by Jeyakumarvs Thursday, June 24, 2010 2:15 PM
    Thursday, June 24, 2010 3:41 AM
  • Hi Ram, Thanks for your reply. It works, but there is some data issue (ABC AMT value).

    The below query returns wrong data for ABC AMT value for all the Month.

    select
    Nonempty(
    (
    Order(
    [Period].[Period].[Acct Month],
    RANK(
     [Period].[Acct Month].Currentmember,
     [Period].[Acct Month]
     )
    ,BDESC
    )
    )
    ,
    [Measures].[ABC AMT]
    ) on 0
    from
    [Cube]

    But, the below query returns correct data (as of cube)


    select
    Nonempty([Measures].[ABC AMT]) on 0,
    Nonempty(
    (
    Order(
    [Period].[Period].[Acct Month],
    RANK(
     [Period].[Acct Month].Currentmember,
     [Period].[Acct Month]
     )
    ,BDESC
    )
    )
    ,
    [Measures].[ABC AMT]
    ) on 1
    from
    [Cube]

    What is the wrong in the first query?

    Thanks

    Thursday, June 24, 2010 10:38 AM
  • From your query i am seeing that you are not placing your measure on the column axis. could you let me know what is the error message or the data that is coming from the first query?  First query which i gave was the expression to just paste it in the MDX  query filter of PPS.

     

    Ram

     

     

    Thursday, June 24, 2010 11:02 AM
  • Yes, you are right. That is the only difference between the two queries. But teh first query returns wrong data like below. I am wondering, where this data comes from.

    Jan-10 Feb-10 Mar-10 Apr-10 May-10 Jun-10
    50 200 70 20 100 40

    Note: Please refer the correct data at my first post.

    I know that the expression that you have provided is for the PPS purpose. I just wanted to validate the data before using it in PPS filter, but ended up some data issue.

    Thanks

    • Edited by Jeyakumarvs Thursday, June 24, 2010 11:42 AM additional information
    Thursday, June 24, 2010 11:41 AM
  • The only thing i am guessing is that when you run the first query , it is picking the default measure that is defined in the cube. Could you check which is the default measure set up in the cube and then change the default measure to [Measures].[ABC AMT] and try running the first query again?

     

    HTH,

    Ram

    Thursday, June 24, 2010 11:49 AM
  • Thanks Ram. You are right. I have set this measure ABC AMT as a default measure and it works.

    I do have another question.

    I would like to apply Tail function in the above MDX Query to get the last Month period like MAY 2010. I need the filter functionality in scorecard, but not required to display the filter drop down list (hidden) in dashboard page. Basically dont want the user to change the month selection.

    Is there any work around for this?

    Thursday, June 24, 2010 12:49 PM
  • In this case since you have it in descending order you could simply leverage the HEAD function instead - http://technet.microsoft.com/en-us/library/ms144859.aspx. You could also look at setting up Time Intelligence in PPS and utilize that functionality as well if you want to simply lock in the month - http://blogs.msdn.com/b/performancepoint/archive/2007/07/02/working-with-time-intelligence-in-performancepoint-monitoring.aspx.  That is just another option, but that does not take into account data in the fact table.


    Dan English's BI Blog
    Thursday, June 24, 2010 1:07 PM