Hi
I need some help writing a calculated member in AS 2005 that calculates a
subtotal by month and repeats the subtotal in every row returned for the
month. See below for the example output. ie. Need to create a calc that
returns "Item Total By Month"

Month Item Unit Item Total by Month
Jan-06 Item1 2 7
Jan-06 Item2 4 7
Jan-06 Item3 1 7
Mar-06 Item1 10 22
Mar-06 Item2 7 22
Mar-06 Item3 5 22
Feb-06 Item1 22 37
Feb-06 Item2 12 37
Feb-06 Item3 3 37

I have a dundas stacked column report in RS 2005 and I want to display the
total unit figure by month at the top of each monthly stack. There doesn't
seem to be an option on the graph to display the total as a label (I can
display the individual item units as labels) so I plan to plot the first
total value for each month on the stack graph as a second series.

Any suggestion in writing the MDX would be greatly appreciated.

Cheers
Kevin

WITH MEMBER '[Measures].[Month SubTotal] AS
'SUM([Time].CurrentMember.Parent, [Measures].[ItemTotal]'
SELECT ....

I don't know what your time hiearchy is, but if it's something like:

[Time].[Year].[Month].[Day] and that's the grain of your query, you can back
the currentmember up one level to Month using the Parent.

I don't do enough of these to drum out a member that will dynamically
determine which level you're on and then pick the month level...

-Tim



"Kevin Wilson" <KevinWilson (AT) discussions (DOT) microsoft.com> wrote