none
Filter possibility in Analytic Chart/Grid RRS feed

  • Question

  • Hi all once again,

     

    I wantes to ask, if it is possible to filter data in analytic grid as we need. Eg, we have a table with such data:

    Miscellaneous 70,385 242,087 -18,222 -500
    Navision 1,065,034 1,244,235 518,994 14,638
    Office management 41,751 257,994 130,148 -220
    Payroll 15,711 19,819
    SAP 39,583 34,334
    Scala 380,958 311,451 183,645 1,160

     It is Gross Profit(measure) by Business Lines (dim) in quarters (dim). I need to exclude negative values (in this eg., show only first 2 quarters data for Miscellaneous and first 3 quarters data for Office mngm).

     

    Now I have MDX

    SELECT
    { [Measures].[GLGPActualLt] } * HIERARCHIZE( { [Dim Calendar].[Quarter].&[1 quarter], [Dim Calendar].[Quarter].&[2 quarter], [Dim Calendar].[Quarter].&[3 quarter], [Dim Calendar].[Quarter].&[4 quarter] } )
    ON COLUMNS,

    { FILTER([Dim Business Type].[BusinessType].[BusinessType].ALLMEMBERS,[Measures].[GLGPActualLt]>0) }
    ON ROWS

     

    Filter FILTER([Dim Business Type].[BusinessType].[BusinessType].ALLMEMBERS,[Measures].[GLGPActualLt]>0) works only if all values for Business Type are negative - then the line (Business Type) is removed.

     

    Thank you very much in advance for an effective help.

     

    Regards,

    Skirma

     

     

    Friday, September 19, 2008 8:16 AM

Answers

  • Finally solved it by myself:

     

    WITH
    MEMBER [Dim Calendar].[Year].[ Aggregation] AS 'AGGREGATE( EXISTING { <<Year>> } )', SOLVE_ORDER = 0
    MEMBER [Dim Country].[Country Name EN].[ Aggregation] AS 'AGGREGATE( EXISTING { <<Country>> } )', SOLVE_ORDER = 0
    MEMBER [Measures].[GP] AS 'IIF([Measures].[GLGPActualLt]>0,[Measures].[GLGPActualLt],NULL)'

     

    SELECT
    HIERARCHIZE( { [Dim Calendar].[Quarter].&[1 quarter], [Dim Calendar].[Quarter].&[2 quarter], [Dim Calendar].[Quarter].&[3 quarter], [Dim Calendar].[Quarter].&[4 quarter] } ) * { [Measures].[GP] }
    ON COLUMNS,

    { FILTER([Dim Business Type].[BusinessType].[BusinessType].ALLMEMBERS,[Measures].[GLGPActualLt]>0) }
    ON ROWS

     

    It means that I replace needed measure (GLGPActualLt) with IIf formula and I get empty cells in Grid inspite of negative values.

     

    Regards,

    S.

    Friday, September 19, 2008 1:28 PM