How to show running totals

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

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

|||

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

|||

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

|||

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.

|||

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

|||

And here's an old OLAP NewsGroup post from George Spofford, explaining how to set up a "time analysis" dimension in As 2000 - which is a precursor to the "Time Intelligence Calculations" in AS 2005. To show running totals from the beginning, you could add a [Time Series].[ATD] calculation, using PeriodsToDate ( [Time].[All], [Time].CurrentMember ) ...

http://groups.google.com/group/microsoft.public.sqlserver.olap/msg/4c59dc7518614323?hl=en&

>>

microsoft.public.sqlserver.olap > Can MDX tell if a member is calculated member? From: George Spofford - view profile Date: Fri, Aug 23 2002 8:19 am Email: George Spofford <geo...@.dsslab.com> Groups: microsoft.public.sqlserver.olap Not yet rated Rating: show options Reply | Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse | Find messages by this author The direct answer is: Sort of. However, your simplest solution is to implement a "time analysis utility dimension", which moves your Current, MTD, QTD, YTD calculations into a separate dimension from time. First, the interesting part. The expression Intersect (Dimension.Members, { [Dimension].[@.X@.] }).Count will return 0 if @.X@. refers to a calc member, 1 otherwise. Second, the possibly better solution: A time analysis utility dimension has no all level and 1 real member in a dimension table named something like "Current" with a key value like 0 or 1. You can create and process the dimension as usual. Bring the dimension into the cube, join it to the fact table on any column of the fact table, and set the member key in the cube the constant 0 or 1 (matching Current's key). Add calculated members to the cube on this dimension: CREATE MEMBER [Time Series].[YTD] AS 'Aggregate ( PeriodsToDate ( [Time].[Year], [Time].CurrentMember ), ([Time Series].[Current]) )' CREATE MEMBER [Time Series].[QTD] AS 'Aggregate ( PeriodsToDate ( [Time].[Quarter], [Time].CurrentMember ), ([Time Series].[Current]) )' A query for YTD sales at August 2002 is a query for the tuple ([Measures].[Sales], [Time Series].[YTD], [Time].[Aug 2002])

>>

|||

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.