Post reply

How can i remove rows where the actual budget and variance are all zero

  • June 4, 2010 at 6:39 am

    #225345

    I have tried using a Filter command but am getting an empty mdx error please see attached query below

    SELECT NON EMPTY { [Measures].[MONTH_ACTUAL], [Measures].[MONTH_BUDGET], [Measures].[MONTH_BUDGET_VARIANCE] } ON COLUMNS, NON EMPTY { ([Year and Period].[YEAR].[YEAR].ALLMEMBERS * [Year and Period].[PERIOD_DESCRIPTION].[PERIOD_DESCRIPTION].ALLMEMBERS * [Nominal Hierarchy].[US_NOMINAL_GRP_EXP_DET].[US_NOMINAL_GRP_EXP_DET].ALLMEMBERS * [Nominal Hierarchy].[US_NOMINAL_GRP_EXP_DET_DESC].[US_NOMINAL_GRP_EXP_DET_DESC].ALLMEMBERS * [Nominal Hierarchy].[US_NOMINAL_CODE].[US_NOMINAL_CODE].ALLMEMBERS * [Nominal Hierarchy].[US_NOMINAL_CODE_DESC].[US_NOMINAL_CODE_DESC].ALLMEMBERS * [Cost Centre Hierarchy].[COST_CENTRE_CODE].[COST_CENTRE_CODE].ALLMEMBERS * [Cost Centre Hierarchy].[COST_CENTRE_DESCRIPTION].[COST_CENTRE_DESCRIPTION].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@CompanyCOMPANYCODE, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@CurrencyCURRENCYCODE, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@YearandPeriodPERIODDESCRIPTION, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Nominal Hierarchy].[US_NOMINAL_GRP_EXP_DET].&[0993], [Nominal Hierarchy].[US_NOMINAL_GRP_EXP_DET].&[0992], [Nominal Hierarchy].[US_NOMINAL_GRP_EXP_DET].&[0994], [Nominal Hierarchy].[US_NOMINAL_GRP_EXP_DET].&[5999] } ) ON COLUMNS FROM ( SELECT ( { [Year and Period].[YEAR].&[2010] } ) ON COLUMNS FROM [SS_FACT_ACT_TARG_FORE_BUDG]))))) WHERE ( IIF( STRTOSET(@CurrencyCURRENCYCODE, CONSTRAINED).Count = 1, STRTOSET(@CurrencyCURRENCYCODE, CONSTRAINED), [Currency].[CURRENCY_CODE].currentmember ), IIF( STRTOSET(@CompanyCOMPANYCODE, CONSTRAINED).Count = 1, STRTOSET(@CompanyCOMPANYCODE, CONSTRAINED), [Company].[COMPANY_CODE].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    I want to display only the lines that have values in any of the actual budget or variance measures effectivelty removing the case where all 3 are zero

    any help would be greatly appreciated

  • July 2, 2010 at 3:08 am

    #1188077

    Is it not as simple as...

    Right Click on Tablix Properties -> Filters -> Add...?

    I want to display only the lines that have values in any of the actual budget or variance measures effectivelty removing the case where all 3 are zero

    any help would be greatly appreciated

    You may want to add a calculated column so that the sum of all three (= zero) to filter on this field...

    HTH,

    GSC

    gsc_dba

  • July 12, 2010 at 4:01 am

    #1191376

    Hi,

    Specify expressions in filter ur matrix..

    eg :

    Fields!variance.value >0 like that

    Regards,
    Gayathri 🙂

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply