negative values when calculating percentages

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.

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

PGoldy

|||

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
.
.
.

|||

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