locked
Creating a Performance Point Dashboard Filter using the MDX editor RRS feed

  • Question

  • Hi  there.

    I am trying to create a Performance Point Dashboard filter using a MDX query and can't seem to get it working.
    Here is what I am trying to accomplish.

    1. Leverage the customdata() to read the NTID of the user logged in

    2. Read the View Type of the user: 1, 2 or 3

    3. Depending on the View Type, control what the users see on the filter.

    Here's What I have on the MDX right now.

    Thanks so much in advance.

    CASE
    WHEN [CCR Security].[Home CC].Currentmember.Properties( "Login" )="XXXX\t827638"
    THEN
       (CASE
          WHEN  ([CCR Security].[Home CC].Currentmember.Properties( "View Type" ))=1 THEN
             Descendants([CCA Hierarchy].[CCA Parent Node Key].&[171779],,LEAVES)
          WHEN  ([CCR Security].[Home CC].Currentmember.Properties( "View Type" ))=2 THEN
             Descendants([CCA Hierarchy].[CCA Parent Node Key].&[171779].firstsibling)
         ELSE Descendants([CCA Hierarchy].[CCA Parent Node Key].&[171779])
       END)
    ELSE Descendants([CCA Hierarchy].[CCA Parent Node Key].&[171779])
    END


    • Edited by mytkchi Tuesday, September 25, 2012 9:27 PM
    Tuesday, September 25, 2012 9:08 PM

All replies

  • Your syntax looks fine, have you validated your conditional values? Try running a SQL Profiler trace on your Analysis services. Run the filter and see what query is passed on to SSAS from PPS.


    http://dailyitsolutions.blogspot.com/

    Wednesday, September 26, 2012 1:44 PM
  • Do you have the security setup on the SSAS side to handle and filter for CustomData?  Just trying to figure out your use of CurrentMember here with "login" as to how you are trying to relate this.

    Here is a good posting that shows how to leverage CustomData with SSAS and in PPS (it is PPS 2007, but still will function the same).

    http://nickbarclay.blogspot.com/2008/01/pps-data-connection-security-with.html

    I am assuming this is what is being displayed currently - Descendants([CCA Hierarchy].[CCA Parent Node Key].&[171779])

    Is that correct or are you seeing something else?


    Dan English's BI Blog

    Thursday, September 27, 2012 12:31 AM
  • Yes, Dan, I have setup security to handle CustomData.(Thanks for the wonderful link by the way.)

    So in place of "XXXX\t827638", I would use CustomData() to call the NTID.
    My problem is that when I enter this MDX on the Performance Point MDX editor, it doesn't seem to recognize the syntax and does not return a value for [CCR Security].[Home CC].Currentmember.Properties( "View Type" ).

    I have been trying to write the MDX on my SQL Server Management Studio and have gotten this far.
    Where could I put the filtering for my CustomData()?
    I am somewhat a newbie to MDX and appreciate your help with the syntax.

    Thanks.

    WITH
    MEMBER [Measures].[CCR Amt] as [Measures].[CCA Amt]
    MEMBER [Measures].[Login] as   [CCR Security].[Home CC].currentmember.Properties( "Login" )
    MEMBER [Measures].[View Type] as ([CCR Security].[Home CC].Currentmember.Properties( "View Type" ))

    SELECT {[Measures].[CCR Amt], [Measures].[Login], [Measures].[View Type]} ON COLUMNS,
      {CASE
       WHEN [Measures].[View Type]=1 THEN Descendants([CCA Hierarchy].[CCA Parent Node Key].&[171779],,LEAVES)
       WHEN [Measures].[View Type]=2 THEN Descendants([CCA Hierarchy].[CCA Parent Node Key].&[171779].firstsibling)
       ELSE [CCA Hierarchy].[CCA Parent Node Key].&[171779].CHILDREN
      END} ON ROWS
    FROM [FDM HR Summary]


    • Edited by mytkchi Thursday, September 27, 2012 4:19 PM
    Thursday, September 27, 2012 4:18 PM