locked
negative values when calculating percentages RRS feed

  • Question

  • I get negative values for percentage calculation in a MDX query. The MDX query has a crossjoin between two sets containing calculated members from the same dimension, one of the calculated members being a percentage value. I'm not sure why some of the percentage values are negative.

    Another problem I'm facing is that the percentage value is not being displayed as per the FORMAT_STRING property, in my Reports in Reporting Services 2005 that use the data generated by the MDX query.

    Any help or suggestion is appreciated.

    Friday, October 13, 2006 6:24 PM

Answers

  • Hi. Thanks for the detailed query and example.

    I don't see why you get a  negative percentage, but I see you're using the calculated members to get values which are normally available in the cube without the use of a calculated member when you construct the right query. I think you should reconstruct your query to use the WHERE clause and re-define, and eliminate, some calculated members to get the correct results Here are my recommendations:

    (1) Use the WHERE cluase to slice your qeury by the desired measure: WHERE (Measures.Number)

    (2) Reference ITEMA dimension on the columns.

    (3) Eliminate the following calculated members because they are not needed and we can derive the dsired values from normal intersections in the cube: (a) MEMBER [ITEMA].[ITEMA].itemmember, (b) MEMBER [ITEMA].[ITEMA].TOTAL

    (4) Change the definition of MEMBER [ITEMA].[ITEMA].ITEMPERC to reference the correct cube intersections.

    Assumption: ITEMA hierarchy has an "all" member, aggregation type for Measures.Number is SUM.

    Here's the new query with the recommended changes:

    WITH
    MEMBER [ITEMA].[ITEMA].ITEMPERC AS
    ' Iif(IsEmpty([ITEMA].[ITEMA].[All ITEMA]),0,([ITEMA].[ITEMA].CurrentMember / [ITEMA].[ITEMA].[All ITEMA])) ', FORMAT_STRING = '0.0%'

    NON EMPTY {[ITEMA].[ITEMA].MEMBERS, [ITEMA].[ITEMA].[All ITEMA], [ITEMA].[ITEMA].ITEMPERC} ON COLUMNS,
    crossjoin({ITEMB.ITEMB.children},{ITEMC.ITEMC.children,[ITEMC].[ITEMC].[others]}) on rows
    FROM [MyCube]
    WHERE ([Measures].[NUMBER])

    Hoe this helps.

    PGoldy

    Saturday, October 14, 2006 3:03 PM

All replies

  • Hi. Can we see the MDX query you're using and a small data sample which provides the negative percentage?

    PGoldy

    Friday, October 13, 2006 8:36 PM
  •  

    Hope this will give you an idea:

     

    WITH
    MEMBER [ITEMA].[ITEMA].itemmember
    as

    ' (ITEMA.ITEMA.&[itemmember], [Measures].[NUMBER]) '

    MEMBER [ITEMA].[ITEMA].TOTAL
    as

    ' SUM(ITEMA.ITEMA.Members, [Measures].[NUMBERS]) '

    MEMBER [ITEMA].[ITEMA].ITEMPERC
    as

    ' Iif(IsEmpty([ITEMA].[ITEMA].TOTAL),0,([ITEMA].[ITEMA].itemmember / [ITEMA].[ITEMA].TOTAL)) ', FORMAT_STRING = '#.#%'

    select [Measures].[NUMBER] on columns,

    non empty crossjoin({[ITEMA].[ITEMA].MEMBERS,[ITEMA].[ITEMA].OTHERS,[ITEMA].[ITEMA].itemmember,[ITEMA].[ITEMA].TOTAL,[ITEMA].[ITEMA].ITEMPERC},
    crossjoin({ITEMB.ITEMB.children},{ITEMC.ITEMC.children,[ITEMC].[ITEMC].[others]})) on rows
    FROM [MyCube]

    Data Snapshot:

                                         ITEMA_1  ITEMA_2  ........  ITEMA_itemmember     ITEMA_TOTAL      ITEMA_ITEMPERC

    - ITEMB_1 
                        ITEMC_1       10            20                            10                                   100                           -0.1
                        ITEMC_2        5              6                               0                                    150                            0.0
                        ITEMC_3        0              1                               2                                        4                            0.5

    - ITEMB_2
                        ITEMC_1 ...................................................................
                        ITEMC_2 ...................................................................
                        ITEMC_3 ...................................................................
    + ITEMB_3
    + ITEMB_4
    .
    .
    .

    Friday, October 13, 2006 9:48 PM
  • Hi. Thanks for the detailed query and example.

    I don't see why you get a  negative percentage, but I see you're using the calculated members to get values which are normally available in the cube without the use of a calculated member when you construct the right query. I think you should reconstruct your query to use the WHERE clause and re-define, and eliminate, some calculated members to get the correct results Here are my recommendations:

    (1) Use the WHERE cluase to slice your qeury by the desired measure: WHERE (Measures.Number)

    (2) Reference ITEMA dimension on the columns.

    (3) Eliminate the following calculated members because they are not needed and we can derive the dsired values from normal intersections in the cube: (a) MEMBER [ITEMA].[ITEMA].itemmember, (b) MEMBER [ITEMA].[ITEMA].TOTAL

    (4) Change the definition of MEMBER [ITEMA].[ITEMA].ITEMPERC to reference the correct cube intersections.

    Assumption: ITEMA hierarchy has an "all" member, aggregation type for Measures.Number is SUM.

    Here's the new query with the recommended changes:

    WITH
    MEMBER [ITEMA].[ITEMA].ITEMPERC AS
    ' Iif(IsEmpty([ITEMA].[ITEMA].[All ITEMA]),0,([ITEMA].[ITEMA].CurrentMember / [ITEMA].[ITEMA].[All ITEMA])) ', FORMAT_STRING = '0.0%'

    NON EMPTY {[ITEMA].[ITEMA].MEMBERS, [ITEMA].[ITEMA].[All ITEMA], [ITEMA].[ITEMA].ITEMPERC} ON COLUMNS,
    crossjoin({ITEMB.ITEMB.children},{ITEMC.ITEMC.children,[ITEMC].[ITEMC].[others]}) on rows
    FROM [MyCube]
    WHERE ([Measures].[NUMBER])

    Hoe this helps.

    PGoldy

    Saturday, October 14, 2006 3:03 PM