Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
5 replies Latest Post - ‏2013-03-12T15:28:10Z by Paul Mendelson
SystemAdmin
SystemAdmin
1959 Posts
ACCEPTED ANSWER

Pinned topic MDX filter/slicer question

‏2013-02-07T09:11:22Z |
Hi all,

I have a question about how to deal with a specific report requirement.
What I want to do is simply to slice the query on a different level then the one I show on the rows.

For example:

Let's say I have a level "Product Type" above the leaf level "Product".

I want the "Product Type" on the rows.
But I want to filter out a few Products based on a certain condition.
I cannot do that simply because I don't have the Products on the rows, I have the Product Type on the rows, so I cannot filter(Products, tuple(currentMember(Products), Scenario3, currentMember(Month)), actual>0)

But the pseudo code above shows what I want to achieve, I want to show only products that has n actual value in Scenario3, for example. And then aggregate them to their parents, in the Product Type level on the rows.

Am I totally missing something here? Can I use something else?

Thanks in advance.

regards,
David
Updated on 2013-03-12T15:28:10Z at 2013-03-12T15:28:10Z by Paul Mendelson
  • DaveCummings
    DaveCummings
    72 Posts
    ACCEPTED ANSWER

    Re: MDX filter/slicer question

    ‏2013-02-07T13:55:44Z  in response to SystemAdmin
    Hi,

    Are you receiving an error and are you using Dynamic Query Mode? What data source? What you're describing is supported in most scenarios.

    Eg, you can have Product Type on an edge, then create a detail filter that is Product in (p1,p2). Or, you can slice on a set of products. Both will trigger a re-aggregation for the Product Types on the report.

    Thanks
    Dave
  • SystemAdmin
    SystemAdmin
    1959 Posts
    ACCEPTED ANSWER

    Re: MDX filter/slicer question

    ‏2013-02-07T16:13:14Z  in response to SystemAdmin
    Thanks Dave,

    the problem is that I don't know what products I want to filter on, I want to keep all the products that have a value in a certain tuple, for example, like above, a value in "Scenario 3" for the current month.

    I am on TM1 and DQM.

    I don't get any error message cause I don't know how to design it. ;)

    Regards,
    David
    • DaveCummings
      DaveCummings
      72 Posts
      ACCEPTED ANSWER

      Re: MDX filter/slicer question

      ‏2013-02-07T16:30:17Z  in response to SystemAdmin
      Try creating a new report with one set expression that returns the members you want to slice on. That way you can validate your expression and use that in the slicer or detail filter.

      Note the slicer or detail filter expression does not take context from the report and is only used to get the set of members. Similar to a sub query to find the members for your report. So no current member expressions.

      Eg, you can create a detail filter like this:

      level in (FILTER (level, tuple(Scenario 3, CurrentMonth) > value ))

      Replace Scenario 3 and CurrentMonth with an expression that returns those members.
      • SystemAdmin
        SystemAdmin
        1959 Posts
        ACCEPTED ANSWER

        Re: MDX filter/slicer question

        ‏2013-03-06T10:38:15Z  in response to DaveCummings
        Yeah, I know what you mean, the problem is that for the CurrentMonth in your example, I would want all the products one by one.

        I want to show Product Types but I would want to filter on a lower level, eg Products.

        something like:

        filter(Product type, tuple(descendants(Product type,1),Scenario 3)>0)

        regards,
        David
        • Paul Mendelson
          Paul Mendelson
          61 Posts
          ACCEPTED ANSWER

          Re: MDX filter/slicer question

          ‏2013-03-12T15:28:10Z  in response to SystemAdmin
          This is slightly difficult, but not impossible.

          First, I'm assuming your xtab looks something like:

          
          Corner   | Columns ---------+--------- RowsNode |  <1234>
          


          You'll create a new data item and nest it in the rows node. For the purpose of this example, I'm writing this for two levels down instead of the direct children.

          The data item expression should be:

          
          total(currentMeasure within set filter(descendants(currentMember([Cube].[Dimension].[Hierarchy]),[Cube].[Dimension].[Hierarchy].[Products]),<Boolean Condition>))
          


          Your xtab should look like:

          
          Corner            | Columns ---------+--------+--------- RowsNode | Nested |  <1234>
          


          If you want to hide the nested node, you can set the boxtype on that node, and the xtab corner to none. Drag in a Crosstab Space to the left of the Columns Node, right click on the node, select Member Fact Cells and set the display type on those to none.

          I was experimenting with the generate function without much success, unfortunately it seems like the MDX engine doesn't like that very much. If it did work, then you wouldn't need the extra node.