calculated measure in drill reports

Hi All

My report(FC) looks like below:

Facility - weigth - cost

The hierarchy is : Facility -> MDC -> SRG.

The result objects are : Facilty,MDC,SRG, Weigth
Cost is calculated locally.

So when I run the report Faclity - Weigth - Cost is shown initially. and when I drill to MDC , MDC- Weigth - Cost should be shown.
like this I should drilldown to SRG also.

Now my problem is:

For the next level of hierarchy i.e MDC
the cost should be (weigth for that particular MDC / Total Weigth ) * total Cost for that facility (which is availble at the previous level).

SO basically what I want is when I drill down to next level in that level I want to refer to total cost which is available in previous level.

How to do that?
I will explain by an example:

First level of report:( when I run the report)

Facility – Weigth – cost
A – 10 – 30000
B – 20 – 40000
C – 25 – 50000

Here cost is coming from an excel sheet ( another data provider in which cost is available for each Facility and Facility is linked in both the Data providers)

when I drill on Facility A I want my report to look like below:

MDC – Weigth – cost
M1 – 4 – 12000
M2 – 3 – 9000
M3 – 3 – 9000
SUM 10 – 30000

Here cost is apportioned to each MDC . Ie. cost for M1 = (weigth / sum of wiegth) * 30000 (which is available at prevous level of hierachy)

When I drill down to another level ie. SRG on M1 it souhld look like below:
SRG – weigth – cost
S1 – 2 – 6000
S2 – 1 – 3000
S3 – 1 – 3000

Could anyone tell me how to do this?
Any work arounds? or can we do with local variable?

I tried creating a local variable cost = weigth / sum forall * CostfromExcel.
its not working proeperly when i Drill down.

Any suggestions

Thank you very much in advance.


BigV :australia: (BOB member since 2003-12-04)