If you are unable to create a new account, please email support@bspsoftware.com

 

How to create a running total of actual/plan depending on a expression

Started by erwink, 12 Aug 2015 08:50:43 AM

Previous topic - Next topic

erwink

I'm running 10.2.1 with DMR

I've a report with

               |Month              |
               |Actual   | Plan   |
_______________________
Project      | ACost  | PCost |

I would like to have a running total till end of year for all Actual if exist, otherwise with Plan

I started with

               |Month                             |
               |Actual   | Plan   | ACumul |
______________________________
Project      | ACost  | PCost | ACumul|

with ACumul = running-total ([Actual Cost] for [Project])

Result is as expected; 1st month ACumul= Acost, and afterwards cumulated

then I created the same for Plan

               |Month                                           |
               |Actual   | Plan   | ACumul | PCumul |
______________________________________
Project      | ACost  | PCost | ACumul | PCumul |

with PCumul = running-total ([Plan Cost] for [Project])

Result is wrong. 1st month PCumul <> PCost,  but  afterwards it is correctly cumulated

PlanCost
----------
[Dimensions].[Plan_MS].[RequestedCostEUR]
Aggr: Total
Rollup Agg: Auto

ActualCost
----------
[Dimensions].[Actual_MS].[CostEUR]
Aggr: Total
Rollup Agg: Auto

I did the test on a small Table and was successful with: total( [Plan Cost] within set periodsToDate ([Dimensions].[Time].[TimeH].[Time(All)], currentMember ([Dimensions].[Time].[TimeH] )), [Project])

On my full blown report it doesn't work. See attached file
Any Idea what I do wrong? Shall I use something else than running-total