Problem with subtotals while building report from cube

Question

I am having a problem with one of my report. The MDX that I am using for building the cube is

SELECT

{

[Measures].[M1],

[Measures].[M2], 

[Measures].[M3]

}

ON COLUMNS,

NON EMPTY

{

[DateTime].[By Year].[Month].members}*

{[X1].[X2].[X3].members,

[x1].[X2].[All]}*

{[Y1].[Y2].[Y3].members,[Y1].[y2].[All]}*

{[Z1].[Z2].[Z3].members,[Z1].[Z2].[All]}

ON ROWS

FROM [Cube])

report needs

X	YYYYYYYY	ZZZ	50.00%	60.00%	10.00%	55.00%
		ZZZZZZ	60.00%	44.00%	-16.00%	64.65%
		YYYYYYYY Subtotal
	YYYYYY	ZZZ	40.00%	66.00%	26.00%	43.76%
		ZZZZZZ	55.00%	NA	NA	74.44%
		YYYYYY Subtotal
X Sub Total
Grand total (Sum of all X)

I am able to get the values of Z but the subtotal for Y is repeated for all the Items aboce in a separate block underneath the each Y's col instead of giving one single row with subtotals. Same with subtotals of X. but I see grand totals at last rowset

Answer 1

Hi,

Do you mean that the YYYYYYY subtotal is the sum of all Z's value which are in subgroup "YYYYYYYY"(same with the YYYYYY subtotal)? If so, you can use the following expression:=SUM(Fields!theZfiled.value,"YGroup").

Please let me know if i misunderstand.

thanks,
Jerry

Answer 2

we dont need to use sum(field value) when its data is coming from cube.