locked
Filtering Dimension members based on other dimension's attribute RRS feed

  • Question

  • Hello,

    I have 2 dimensions: [Suppliers] and [Business Units]

    I have a report that shows supplier with their spend amount and a Business Units Filter.

    In this report I want to show suppliers for only 3 Business Units (let's say X,Y and Z). This means that the report would totally ignore any selections in the Business Units Filter unless it is a subset of those 3 Business Units (X,Y and Z).

    The Filter should display all Business units and not only those 3.

    What I though about is: making a calculated attribute from the Data Source View of the Cube in the Business Unit Dimension called [IsYes], it should carry a value of '1' of the BU (Business Unit) belongs to {X,Y or Z} else carries '0'. Here's it's code:

     

    CASE WHEN BuisnessUnitName = 'X' OR BuisnessUnitName = 'Y'

    OR BuisnessUnitName = 'Z'

    THEN 1 else 0 END

     

    I then placed the query of my PerformancePoint Report as follows:

     

    SELECT

    NON Empty{ [Measures].[Spend] }

    ON COLUMNS,

     

    NON EMPTY {[Top 50 Suppliers]}

    ON ROWS

     

    FROM [Spend Demo]

     

    WHERE (<<BU>>, [Buisness Unit].[Is Aerospace].&[1])

     

    Where <<BU>> represents the parameter that I'll bind to the BU Filter.

     

    When I select more than a member from the Filter I'm faced with this msg:

    "The MDX function failed because the coordinate for the 'BU' attribute contains a set."

     

    Any suggestions?


     


    omar
    Monday, June 21, 2010 10:43 AM

All replies

  • Hi,

    I have not completely understood your requirement. Please correct me if i am wrong. You have 2 dimenions supplier and business units. Based on business unit name filter you select in the report the supplier dimension has to get filtered. IF this is the requirement then you can just use an MDX filter at the dashboard level&nbsp;and write an expression over there.

     

    something like [Business Unit].[Business Unit].[Business Unit Name].members

     

    HTH,

    Ram

    Monday, June 21, 2010 11:31 AM
  • Change your WHERE clause to the following:

    WHERE FILTER({<<BU>>}, [Buisness Unit].[Is Aerospace].name = "1")

    Maybe your report parameter already includes the curly braces, if so, then omit that part.


    Dan English's BI Blog
    Tuesday, June 22, 2010 11:16 AM