I have a cube with many dimensions including one called WorkDate YMD. I also have a measure called Hours.
At the moment I can see hours against any level of the WorkDate dimension, but I have a requirement now for running totals.
For example, the data at the moment is this
Jan 4 hours, Feb 5 hours March 6 Hours
As well as the actual Hours the users now want to see the running totals.
So the data will be
Jan 4 hours, Feb 9 hours March 15 hours
I know this should be easy as a calculated member in the cube using MDX and therefore visible in excel pivot tables but I cannot see how to accomplish this correctly ans easily.
TIA
How to show running totals
WontSetup
Swarna
Hi Neil. Thanks for the explanation. I provided very query specific solution to your uestion - as if you are using Reporting Services. Your explanation shows that what I gave you isn't very useful - especially in Excel where you don't control the specific MDX. Sorry about that. In ieu of an MDX course you may want to pick up Spoffords book on MDX Solutions (http://www.amazon.com/s/ref=nb_ss_gw/102-1292512-3894526 url=search-alias%3Daps&field-keywords=MDX+spofford&Go.x=12&Go.y=7). This is the SL 2005 edition, bt the MDX basics didn't change from SQL 2000, and if you search the net you can probably find e SQL2000 edition.
Good Luck.
PGoldy
nmfl
Hi Thanks for further information. Sounds like what you want is a running total for each month which starts at the project begin date. Here's a query which November as the project start, and provides a running total through April (spanning the year boundary).
WITH MEMBER Measures.[Running Total] AS
'
SUM(WorkDate.WorkDate.[November]:WorkDate.WorkDate.CurrentMember, Measures.Hours)
'
SELECT
{WorkDate.WorkDate.November
,WorkDate.WorkDate.December
,WorkDate.WorkDate.January
,WorkDate.WorkDate.February
,WorkDate.WorkDate.March
,WorkDate.WorkDate.April} ON COLUMNS,
{Measures.[Running Total]} ON ROWS
FROM [Your Cube]
Hope this helps.
PGoldy
m.dawood
Thanks guys,
After all this help, I've been able to get what I'm after, the mdx for running totals is
sum(periodstodate([work date].[(all)]),[measures].[hours])
where [work date] is the date dimension and [hours] are the measure to be cumulated.
Thanks
Neil.
Spuddo
Hi Paul,
I think I need to push my boss harder to let me go onto an MDX course.
The cube I have is against MS Project server data, so the users want to see cumulative hours for many selected of grouped projects or tasks, so I cannot hard code November to April unfortunately.
I guess if possible I'm after a simple mdx query that I can drop into the calculated section in SS2000 Analysis Services so that it appears as a measure that they can select with my normal measure of Hours back in Excel, and so that they can do a line graph and show how the projects hours are growing.
Thanks for your help
Neil.
aamer4u
Thanks for the info, but I don't think I explained the issue properly, there is another dimension of project so the work date rather than being Year to Date cumulative totals it is cumalitive from the start of the project so for example Nov 5 hours, Dec 2 Hours, Jan 4 Hours Total 11 hours should be viewed as Nov 5 hours, Dec 7 hours, Jan 11 hours
btw, yes WorkDate is a time dimension of Year / Month / Day and my users want to see the cumulative totals on a monthly basis.
TIA
Tryin2Bgood
Hi. If you're WorkDate dimension is of type "Time" you can use the YTD() function as follows:
YTD(WorkDate.WorkDate.CurrentMember, Measures.Hours)
If WorkDate is NOT a type Time dimension you can get the same functionality from:
SUM(PeriodsToDate([WorkDate].[WorkDate].<Level name>, [WorkDate].[WorkDate].[<level name>].CurrentMember), Measures.Hours)
Hope this helps.
PGoldy