How to calculate running total on diemensional data at report level?

[shrini]

Hi Frns,

   How can we calculate running total on diemensional data at report level as source from cube.
Pls let me know if any,we dont have the running total function for diemensional data.So if any possible ways pls let me know.Its urgent


Thanks,
Shrini. 

[PaulM]

The running functions are strictly relational. You can try the following though:

Code: [Select]

total([Measure] within set periodsToDate ([Cube].[Dates].[Years].[All],currentMember ([Cube].[Dates].[Years])))
Let's say your crosstab has months on the rows and the measure on the columns. Using this as the default measure should be exactly what you need. For each row it will generate a set of all of the months up to the current row with the periodsToDate function. It will then total the [Measure] for that set.

The referenced level in the periodsToDate, Year, defines where the months begin for that set.
An example:


Corner    |  Measure  |  Running  |
----------+-----------+-----------|
January   |     1     |      1    |
February  |     2     |      3    |
March     |     3     |      6    |
April     |     4     |     10    |
May       |     5     |     15    |
June      |     6     |     21    |


If you selected Quarters instead of years you'd get something like:

Corner    |  Measure  |  Running  |
----------+-----------+-----------|
January   |     1     |      1    |
February  |     2     |      3    |
March     |     3     |      6    |
April     |     4     |      4    |
May       |     5     |      9    |
June      |     6     |     15    |


The draw back is that this is not a true running. You can't select a range of members and expect it to only run against that range, it will always run against the level.

[shrini]

HI MFGF,

     I have worked around for the solution you gave me but its showing a parsing error.Let me tell you what i have done--

total([PP_ACC_RCV_AMNT] within setPeriods ToDate([YM],currentMember([YM])))

This is what i have placed in the expression but unable to get and got a parsing error- QE-DEF-0459 CCLException.

Can u please sort me out of this issue.

Thanks,
Shrini

[MFGF]

HI MFGF,

     I have worked around for the solution you gave me

Hi Shrini,

Whoa!!  Nope - not me.    I take no credit for this elegant technique whatsoever.  It's our resident guru PaulM who posted this reply for you.

Looking at the expression you posted:

1. Can you check that [PP_ACC_RCV_AMNT] is a valid measure.
2. "within setPeriods ToDate()"  should be "within set periodsToDate()"
3. [YM] shoukd be a fully qualified level from your hierarchy - I would expect to see [Namespace].[Dimension].[Hierarchy].[Level] as the structure.

MF.

[shrini]

HI MFGF<

    Sorry for that mistake i jus thought u bcoz u used to respond to everyone so i thought you can tell me any ways to calculate running total at report level for DMR data as data from cube.


Thanks,
Shrini

[MFGF]

Hi Shrini,

Just took another look at your expression, and there is another probable issue - the second [YM] in your expression should be the fully qualified hierarchy name - [Namespace].[Dimension].[Hierarchy]

Check through these suggestions and correct the expression where necessary, and you should find Paul's solution works fine.

Good luck!

MF.

[shrini]

Hi MFGF,

      I have gone thru n implemented the syntax said by Paul.The prob is that its not showing running total when i have a set of values for an yearmonth(ex-199002) n if i have a single value for an yearmonth(199004) its showing me the running total.Let me give you a give you a clear pic of my report output:-

--------------------------------------------------------------------------
                    199002                                                199004

            PP  Rcv     Running Total                       PP  Rcv     Running Total
-------------------------------------------------------------------------
Prod    271485.00                                             30141.00    30141.00

Prod1  30141.00                                               30141.00    30141.00 

Prod2  30147.00

Prod3  30153.00

Prod4  30159.00

Prod5  30165.00

Prod6  30171.00

Prod7  30177.00

Prod8  30183.00

Prod9  30189.00

   And its summimg up all the values of the year months and displaying on the top PPRcv Column
 The above way i am getting the output.So no running total for 199002

So pls consider my example and pls guide me out thru this issue


Hoping result from you asap.

Thanks,
Shrini

[PaulM]

Are you using the same level in the function as in the columns?

In your example if YM is the month level, you need to specify the year level. Remember, the running-total in this function will reset for each new member referenced in the level.

By using this function ...

Code: [Select]

total([PP_ACC_RCV_AMNT] within set periodsToDate([YM],currentMember(hierarchy([YM]))))
you would get this:

Month     | Month    |  Measure  |  Running  |
----------+----------+-----------+-----------|
January   |January   |     1     |      1    |
February  |February  |     2     |      2    |
March     |March     |     3     |      3    |
April     |April     |     4     |      4    |
May       |May       |     5     |      5    |
June      |June      |     6     |      6    |



because you're explicitly telling it to restart the numbering for each row in YM for the current row.

Try this instead:

Code: [Select]

total([PP_ACC_RCV_AMNT] within set periodsToDate(ancestor(currentMember([YM]),2),currentMember(hierarchy([YM]))))

[MFGF]

Lol.  Thanks Paul.  I just this second replied to a PM from Shrini asking the same question, but my suggestion was slightly different, albeit the same.  Just for fun (and for completeness of the thread, because PMs are not a good way of sharing ideas with the community), here was my stab:

total([PP_ACC_RCV_AMNT] within set periodsToDate([Your Namespace].[Your Time Dimension].[Your Time Hierarchy].[Your Year Level],currentMember([Your Namespace].[Your Time Dimension].[Your Time Hierarchy])))

I hadn't originally twigged that [YM] was the month level not the year level, but the lightbulb came on when I saw the results.

My belated effort is exactly the same concept as you suggest Paul, but using the Year level in the expression rather than using the ancestor() function, and using the hierarchy rather than using the hierarchy() function.  It just goes to show there's more than one way to end up with the same result

All credit to you for coming up with the idea in the first place, by the way - it's a very neat solution, sir!

MF.

[shrini]

HI Paul,

    I have jus gone thru ur expression syntax:-

total([PP_ACC_RCV_AMNT] within set periodsToDate(ancestor(currentMember([YM]),2),currentMember(hierarchy([YM]))))

Hierarchy[YM] and currentMember[YM] how should we specify them

In my report i have for

Name Space:-DM
Dimension- [MonthTime]
Hierarchy- [Month Time]
Level- [YM]

and  have framed it as -----

 total([pp_acc] within set periodsToDate(ancestor(currentMember([DM].[Month Time].[Month Time].[YM]),2),currentMember([DM].[Month Time].[Month Time])))

So i f any changes let me know and i have error as invalid coercion from level to hierarchy

Thanks,
Shrini                           

[PaulM]

My mistake! get rid of the currentMember in the ancestor function. the currentMember only accepts hierarchies as a parameter.

[shrini]

Hi Paul,

  As u said i have done it but getting error,removing of currentMember from ancestor and my expression framed is below ---


total([PP_ACC_RCV_AMNT] within set periodsToDate(ancestor([DM12_SSS].[Month Time].[Month Time].[YM],2),currentMember([DM12_SSS].[Month Time].[Month Time])))

And i am getting an error --

QE-DEF-0459 CCLException
QE-DEF-478 Invalid coercion from 'level' to 'member'


Thanks,
Shrni

[MFGF]

Hi,

Just a quick sanity check - your time dimension does have a year level, doesn't it?

If so, instead of using the ancestor() function on [YM] to obtain the Year level, why not just drag in the Year level itself?

total([PP_ACC_RCV_AMNT] within set periodsToDate([DM12_SSS].[Month Time].[Month Time].[Your Year Level],currentMember([DM12_SSS].[Month Time].[Month Time])))

If you don't have a year level, can you tell us what the structure of the Month Time hierarchy is (ie what levels you have?)

MF.

[shrini]

Hi MFGF,

   why do u get errors like invalid coercion from 'level' to 'hierarchy'

Pls let me know frm yesterday i was jus sorting for getting rid of it for my expression

Thanks,
Shrini

[MFGF]

Hi,

This is telling you that you have used a dimensional level in your expression in a place where a dimensional hierarchy was expected to be found.  Typically you will get this error when using functions like currentMember(), which will only accept a hierarchy as the argument, but where you have placed a level as the argument.

A couple of questions, if I may (which may help us to understand your structures a little better and help you get this working).

1. What levels exist in the [Month Time] hierarchy if you expand it in the package tree on the left?
2. Is it correct thet the [Month Time] hierarchy exists within a dimension also called [Month Time]?

Thanks,

MF.