PerformancePoint Filter MDX Query with or conditions

Question

I am trying to mimic this filter functionality in a PerformancePoint MDX query filter, which is then connected to a scorecard.

SELECT 
[Dim Status Month].[MONTHS].MEMBERS ON COLUMNS,

FILTER ([Dim Project].[LOB].MEMBERS,

    (
    strtomember('[Dim Project].[PM Login].&[' + CUSTOMDATA() + ']')
    or
    strtomember('[Dim Project].[TM Login].&[' + CUSTOMDATA() + ']')
    or
    strtomember('[Dim Project].[PAL Login].&[' + CUSTOMDATA() + ']')
    or
    strtomember('[Dim Project].[DO Login].&[' + CUSTOMDATA() + ']')
    or
    strtomember('[Dim Project].[ED Login].&[' + CUSTOMDATA() + ']')

    )
) ON ROWS
FROM [GLOBALDELIVERYREPORTING]

CustomerData() being the current user name.

It works fine with single conditions such as:

strtomember('[Dim Project].[PAL Login].&[' + CUSTOMDATA() + ']')

I cannot figure out the syntax for using multiple conditions, even though they work fine when I run them directly against the cube.

So, I dropped Filter Text Here in the MDX Query window of filter, and grabbed the query from SQL profiler and this is what it ends up looking like:

WITH SET [7afbf0ad32e34820ab610fb5927a5f6e] AS HEAD({ StrToSet("Filter Text Here") }, 5001)
                        SELECT 
                        { 
                            IIF(COUNT([7afbf0ad32e34820ab610fb5927a5f6e])=0, { },
                            {(EXTRACT([7afbf0ad32e34820ab610fb5927a5f6e], [7afbf0ad32e34820ab610fb5927a5f6e](0)(0).Dimension) AS [7afbf0ad32e34820ab610fb5927a5f6eX]), 
                            (GENERATE(
                                [7afbf0ad32e34820ab610fb5927a5f6eX],
                                {
                                    { [7afbf0ad32e34820ab610fb5927a5f6eX].CURRENT.ITEM(0).PARENT, [7afbf0ad32e34820ab610fb5927a5f6eX].CURRENT.ITEM(0) }(0) 
                                }, 
                                ALL))})
                        }
                        DIMENSION PROPERTIES MEMBER_TYPE ON 0,
                        { } ON 1
                        FROM [GlobalDeliveryReporting]

Answer 1

Does it allow you to move the filter to the WHERE clause?

SELECT 
  [Dim Status Month].[MONTHS].MEMBERS ON COLUMNS,
  [Dim Project].[LOB].MEMBERS ON ROWS
FROM [GLOBALDELIVERYREPORTING]
WHERE
   (
    strtomember('[Dim Project].[PM Login].&[' + CUSTOMDATA() + ']')
    ,strtomember('[Dim Project].[TM Login].&[' + CUSTOMDATA() + ']')
    ,strtomember('[Dim Project].[PAL Login].&[' + CUSTOMDATA() + ']')
    ,strtomember('[Dim Project].[DO Login].&[' + CUSTOMDATA() + ']')
    ,strtomember('[Dim Project].[ED Login].&[' + CUSTOMDATA() + ']')
   );

Try EXISTS function instead:

SELECT 
  [Dim Status Month].[MONTHS].MEMBERS ON COLUMNS
 ,Exists
  (
    [Dim Project].[LOB].MEMBERS
   ,{
      StrToMember('[Dim Project].[PM Login].&[' + CustomData() + ']')
     ,StrToMember('[Dim Project].[TM Login].&[' + CustomData() + ']')
     ,StrToMember('[Dim Project].[PAL Login].&[' + CustomData() + ']')
     ,StrToMember('[Dim Project].[DO Login].&[' + CustomData() + ']')
     ,StrToMember('[Dim Project].[ED Login].&[' + CustomData() + ']')
    }
  ) ON ROWS
FROM [GLOBALDELIVERYREPORTING];

Or if you are sure FILTER is working then try nesting your filters like this:

SELECT 
  [Dim Status Month].[MONTHS].MEMBERS ON COLUMNS
 ,Filter
  (
    Filter
    (
      Filter
      (
        Filter
        (
          Filter
          (
            [Dim Project].[LOB].MEMBERS
           ,StrToMember('[Dim Project].[PM Login].&[' + CustomData() + ']')
          ),
         ,StrToMember('[Dim Project].[TM Login].&[' + CustomData() + ']')
        )
       ,StrToMember('[Dim Project].[PAL Login].&[' + CustomData() + ']')
      )
     ,StrToMember('[Dim Project].[DO Login].&[' + CustomData() + ']')
    )
   ,StrToMember('[Dim Project].[ED Login].&[' + CustomData() + ']')
  ) ON ROWS
FROM [GLOBALDELIVERYREPORTING];

Maybe you just need a simple set notation like the following:

{
  StrToMember('[Dim Project].[PM Login].&[' + CustomData() + ']')
 ,StrToMember('[Dim Project].[TM Login].&[' + CustomData() + ']')
 ,StrToMember('[Dim Project].[PAL Login].&[' + CustomData() + ']')
 ,StrToMember('[Dim Project].[DO Login].&[' + CustomData() + ']')
 ,StrToMember('[Dim Project].[ED Login].&[' + CustomData() + ']')
}