none
MDX Query filter in PPS RRS feed

  • Question

  • hi, this works in  MDX Query Analyzer but not when the same query is used as  mdx filter in PPS

     

    WITH MEMBER Measures.x AS UserName
    SELECT Measures.x ON COLUMNS
    FROM [testcube]


    Error in SQL Profiler:
     

     

    Query (1, 10) Parser: The syntax for 'WITH' is incorrect.

     

    Query Text:

    SELECT { WITH MEMBER Measures.x AS UserName

    SELECT Measures.x ON COLUMNS

    FROM [cube1] } DIMENSION PROPERTIES MEMBER_TYPE on 0 FROM [cube1]

     


    newbie
    Monday, July 27, 2009 9:58 PM

Answers

  • Based on what you have shown I would guess that you would want to do something along these lines:

    exists([testdim].[group name].children, filter([testdim].[user name].[user name].members,
    [testdim].[user name].currentmember.name = username))


    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    • Edited by Dan English Wednesday, July 29, 2009 11:18 PM update a dimension name
    • Marked as answer by Maadhavi Thursday, July 30, 2009 10:32 PM
    Wednesday, July 29, 2009 10:09 PM

All replies

  • The MDX Query filter is not a spot where you would put an actual MDX Query.  I know based on the name you would think this, but it is a way for you to generate a set of values (a SSAS Named Set).  So you could use items like Product.Category.Members or Geography.Country.Members.  This would create a list of members that would change based on records being added, removed, or changed in the dimension (works a little different than the Member selection, but no default member selection option).

    So you could return a dimension member or set of members from a dimension to use, but not measures.  You could do something like StrToMember("[Employee].[Login ID].&[" + UserName + "]") if you wanted to.

    What are you trying to do with this filter?  Maybe if you provide some more details as to how you are wanting to utilize this we can figure out some options for you.
    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    Wednesday, July 29, 2009 1:31 AM
  • this is the query which i need to add to PPS filter,   return groupnames based on username.  ( this has nothing to do with security)..just a filter

    SELECT {} ON 0,

    [testdim].[Group Name].members ON 1

    FROM [cube1]

    Where Filter( [testdim].[user name].[user name].members, [testdim].[user name].currentmember.name = username())

     i tried ,

    Filter( [testdim].[group name].members, [testdim].[user name].currentmember.name = username())   this neither errors out when checked in sql profiler or returns results


    newbie
    Wednesday, July 29, 2009 5:35 PM
  • Based on what you have shown I would guess that you would want to do something along these lines:

    exists([testdim].[group name].children, filter([testdim].[user name].[user name].members,
    [testdim].[user name].currentmember.name = username))


    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    • Edited by Dan English Wednesday, July 29, 2009 11:18 PM update a dimension name
    • Marked as answer by Maadhavi Thursday, July 30, 2009 10:32 PM
    Wednesday, July 29, 2009 10:09 PM
  • Actually this would work also: exists([testdim].[group name].children, strtomember("[testdim].[user name].[user name].[" + username + "]"))
    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    • Edited by Dan English Wednesday, July 29, 2009 11:21 PM updated dimension names
    Wednesday, July 29, 2009 11:20 PM
  • Dan,
    I have another dilemma that is similar. I am trying to create an MDX filter where it has a hierarchy of Filter([Time].[Month Description].members, [Time].[Month Description].CurrentMember) and it keeps giving me a parameter with all the months defaulting to ALL first. I need it to default to the current month. Please help.

    Paul
    Saturday, March 6, 2010 9:43 AM
  • Please don't add a questions to a thread that has already been answered.  In the future you should create a new thread with your question.

    Based on your requirement I would configure the Time on your SSAS data source and utilize a Time Intelligence filter.  What you are trying to do with CurrentMember is not correct.  CurrentMember does not equal current month.

    http://blogs.msdn.com/performancepoint/archive/2007/07/02/working-with-time-intelligence-in-performancepoint-monitoring.aspx
    Dan English's BI Blog
    Saturday, March 6, 2010 11:43 AM
  • Sorry about the adding a question to a new thread. Thanks for the link to the Time Intelligence piece. I will work with that.
    Tuesday, March 9, 2010 10:55 PM