none
MDX Query filter in PPS RRS feed

  • Question

  • Hi,

    The requirement is to show the ProClarity report in the PPS Dashboard. The PC report has a filter which use the Calcaulated Members defined in the PC report (Not in SSAS Cube). Our of this few Calculated members, few has been selected as default values.

    The same filter needs to be created in the PPS dashboard. I thought of use the MDX filter template and start write the MDX script like below. I just try create the Calculated Members as similar as ProClarity report. But unable to proceed on this.

    With Member [Dim].[Hierarchy].[Stk Rolls]
    AS
    Aggregate({ [Dim].[Hierarchy].&[SS] })
    Member [Dim].[Hierarchy].[Mkg Rolls]
    AS
    Aggregate({ [Dim].[Hierarchy].&[MR] })

    The requirement is to show this calculated members (Stk Rolls, Mfg Rolls, etc) in the filter and select few as default values. It would be helpful is you can assist on this. I am a learner of MDX.

    Thanks, Jey


    --Jey
    Wednesday, February 2, 2011 9:20 AM

Answers

All replies

  • The MDX Query filter option is used to return a list of items to display.  You will not be able to create calculated members to return.  This logic would need to be pushed into the calculations portion of the cube where the logic would be centralized for reference.

    Take a look at this article that explains the use and provides some examples - http://office.microsoft.com/en-gb/performancepoint-server/create-a-filter-by-using-the-mdx-query-template-in-dashboard-designer-HA010261782.aspx.

    In your examples above you are using the Aggregate function on a single member.  I guess I am a bit confused as to what you are trying to display.  If you just want to display the two members you can do that in the query portion, but it could also be done with the members selection filter option as well.

    Aggregate (MDX) - http://msdn.microsoft.com/en-us/library/ms145524.aspx

    Just want to point out one other item, with the MDX Query filter you are unable to set a default value like you are with the members selection.


    Dan English's BI Blog
    • Edited by Dan English Thursday, February 3, 2011 11:53 AM added info about default values
    Thursday, February 3, 2011 11:52 AM
  • Thanks Dan for your inputs.  As you mentioned there is nothing to do with the Calculated Member value.

    To explain my scenario in detail,

    1. The ProClarity report has few Calculated Members in the report scope. This set of calculated members used as filter in ProClarity report.

    2. When we use the same report in PPS Dashboard page, the ProClarity report filter will be displayed in the Dashboard page. Also, this filter drop down will be populated with the ProClarity report calculated member names (example 'Stk Rolls', 'Mkg Rolls', etc).

    3. We would like to replace this ProClarity report filter with the PPS Dashboard filter.

    I agree that it is always good to have this calculated members defined in SSAS Cube. But in this case, we just would like to display the ProClarity report in the Dashboard.

    Still I am not clear in populate the 'Stk Rolls', 'Mkg Rolls', etc names in the PPS filter drop down require the same needs to be defined in SSAS Cube or any work around in PPS itself.

    Does this question make sense?


    --Jey
    Thursday, February 3, 2011 1:10 PM
  • You have two options from what I can tell to implement this.  The first one would be to move the logic to the cube.  The second would be to simply create an Excel file to use as a tabular filter that contains the display name you want and then the MDX syntax for the information required by the ProClarity report.

    For an example of the tabular configuration take a look about halfway down in this posting - http://blogs.msdn.com/b/performancepoint/archive/2008/11/18/filters-for-tabular-data-sources-and-tabular-filters-explained.aspx.


    Dan English's BI Blog
    • Marked as answer by Jeyakumarvs Thursday, February 3, 2011 5:03 PM
    Thursday, February 3, 2011 2:18 PM
  • Perfect Dan. Second option works well for me. Thanks a lot!!
    --Jey
    Thursday, February 3, 2011 5:04 PM
  • Hello Dan,

    I am using MDX filter which returns me all Quarters of current and Next fiscal year. Query is as follows:

    strtoset("[Date].[Fiscal].[Month].&[" + format(Now(), "yyyy") +
    "]&["+format(now(),"MM") +
    "].parent.parent.firstchild:[Date].[Fiscal].[Month].&[" + format(DateAdd("yyyy",+1,Now()), "yyyy") +
    "]&["+format(now(),"MM") + "].parent.parent.lastchild" )

    I want to set current Quarter default selected on dashboard, can i do that?

    Friday, February 11, 2011 7:15 AM
  • With the MDX Query you do not have an option to set a default.  Once a user makes a selection the default is pretty much obsolete unless you modify the stored procedure that pulls the users selection from the database or modify the setting to cache the users selections.
    Dan English's BI Blog
    Friday, February 11, 2011 1:05 PM
  • If do not have future date's data then sorting your filter to descending would always display current quarter.
    http://dailyitsolutions.blogspot.com/
    Friday, February 11, 2011 3:33 PM
  • Hi,

    Thanks Dan.

    MDX filter saves user's last selection and displays that option selected when navigated or revisited the page.

    Workarround for this is to use 'Filter Actions' filter by editing the sharepoint page and 'add webpart'. This filter holds the filter values to be passed to charts untill user clicks on 'Apply Filters'. In this filter there is facilty to 'SAVE AS MY DEFAULTS'.

    If user sets current QTR as his default value, every time he visits the page current QTR is default selected.

    (Also check webpart property 'Show Apply Filters button' in left pane. this will hide the button but has facility to set default.)


    ++
    • Proposed as answer by Abhijeet_persi Thursday, March 10, 2011 7:38 AM
    Tuesday, March 8, 2011 9:32 AM