locked
Filtering RRS feed

  • Question

  • I have created a set via the Setup Panel. I take lots of companies look at all there divisions for a particular date and propesctive estimate points of this dates(i.e 3m, 6m). These are up against 5 measures, Now I wanted to see only the divisions and estimate points where measure 3 is equal to zero how could I achieve this.

     

    I have right clicked on the grid and filtered, but it will only allow me to filter one estimate point at a time, two max. Then when I use the selector I cannot use it on the personal set I made.

     

    Date            
    3M 6M 1Y 2Y 3Y 4Y 5Y
    Company Division Measure1 0 0
        Measure2 0 0
        Measure3 0 0
        Measure4 6.97 6.97
        Measure5 0 0

     

     

    Someone please help

     

    Jabba

    Wednesday, November 7, 2007 3:30 PM

Answers

  •  

    Good Morning,

     

    The difficulty you are running into is that the filter doesn't allow you to disclude a column selection (e.g., Time, or division if you pivot the grid.)   What you can do, is create the filter and remove the Time reference alltogether (shown in bold below) from the MDX directly (View | MDX Editor).   

     

    Code Block

     

     

    SELECT { [Time].[Calendar].[Year].&[2004], [Time].[Calendar].[Year].&[2005], [Time].[Calendar].[Year].&[1], [Time].[Calendar].[Year].&[2007] } ON COLUMNS ,

     

    { FILTER( { { { [Customer].[Customer].[Channel].&[1], [Customer].[Customer].[Channel].&[2] } * { [Measures].[Sales Amt], [Measures].[Units], [Measures].[On Time], [Measures].[On Time Delivery %], [Measures].[Gross Margin %] } } }, ( [Time].[Calendar].[All Time], [Measures].[On Time] ) > 5.00000000000000 ) } ON ROWS

     

    FROM [Performance]

    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL

     

     

    This however will not persist upon further navigation. 

     

    Thank you

    Dawn

    Monday, November 12, 2007 4:23 PM

All replies

  •  

    Good Morning,

     

    The difficulty you are running into is that the filter doesn't allow you to disclude a column selection (e.g., Time, or division if you pivot the grid.)   What you can do, is create the filter and remove the Time reference alltogether (shown in bold below) from the MDX directly (View | MDX Editor).   

     

    Code Block

     

     

    SELECT { [Time].[Calendar].[Year].&[2004], [Time].[Calendar].[Year].&[2005], [Time].[Calendar].[Year].&[1], [Time].[Calendar].[Year].&[2007] } ON COLUMNS ,

     

    { FILTER( { { { [Customer].[Customer].[Channel].&[1], [Customer].[Customer].[Channel].&[2] } * { [Measures].[Sales Amt], [Measures].[Units], [Measures].[On Time], [Measures].[On Time Delivery %], [Measures].[Gross Margin %] } } }, ( [Time].[Calendar].[All Time], [Measures].[On Time] ) > 5.00000000000000 ) } ON ROWS

     

    FROM [Performance]

    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL

     

     

    This however will not persist upon further navigation. 

     

    Thank you

    Dawn

    Monday, November 12, 2007 4:23 PM
  • Another option you might consider is adding some sort of criteria to the companies that will let the Selector find them in a filter.  For instance, you could add an attribute relationship that the Selector could filter on, and then you would be able to have the Selector build your set, and then apply your measure criteria.

    Monday, November 12, 2007 11:47 PM