Ratio as Calculated Key Figure

Hello Experts,

I have two cubes linked into a multiprovider. Both have many characteristics but I will list just the important ones

0PROJECT - project code

ZPROJYEAR - project year

ZPROJECT1 - project subdivision (common structure for all projects) (values like E1, E2, E3...)

0VTYPE - value type

0METYPE - key figure type

0STKEYFIG - statistical key figure

One cube contains the actuals and the other statistical values.

I have defined two restricted key figures:

Actuals (amount)

- 1FYBN = 0AMOUNT

- 0VTYPE = 10 - actuals

Statustics (quantity)

- 1FYBN = 0QUANITY

- 0METYPE = 2500 - Statistical Key Figure

- 0STKEYFIG - a hierarchy node to get the proper key figure with one UoM

The both cubes and datasources have detail (granularity) down to the project (0PROJECT) and project year (ZPROJYEAR) but only the Actuals have details on ZPROJECT1 while all the Statistics are posted to one technical code E1

I would like to create a calcuated key Actuals/Statistics that would respect all the characteristics in the query filter or row/column division except for ZPROJECT1. If define a simple calculated field "Ratio" as Actuals/Statistics I get following results:

Data in Multiprovider:

0PROJECT ZPROJYEAR ZPROJECT1 Amount Statistics

Project1 2010 E1 100 50

Project1 2010 E2 50

Project1 2010 E3 30

Project1 2010 E4 40

Project2 2011 E1 120 55

Project2 2011 E2 60

Project2 2011 E3 10

Project2 2011 E4 10

Project3 2011 E1 200 80

Project3 2011 E2 90

Project3 2011 E3 50

Project3 2011 E4 60

Report 1

0PROJECT Amount: Statistics: Ratio:

Project1 220 50 4,40

Project2 200 55 3,64

Project3 400 80 5,00

that is OK

Report 2

ZPROJYEAR ZPROJECT1 Amount: Statistics: Ratio:

2010 E1 100 50 2,00

E2 50 #DIV/0!

E3 30 #DIV/0!

E4 40 #DIV/0!

Total 2010 220 50 4,40

2011 E1 320 135 2,37

E2 150 #DIV/0!

E3 60 #DIV/0!

E4 70 #DIV/0!

Total 2011 600 135 4,44

but what I needed was

ZPROJYEAR ZPROJECT1 Amount: Statistics: Ratio:

2010 E1 100 50 2,00

E2 50 50 1,00

E3 30 50 0,60

E4 40 50 0,80

Total 2010 220 50 4,40

2011 E1 320 135 2,37

E2 150 135 1,11

E3 60 135 0,44

E4 70 135 0,52

Total 2011 600 135 4,44

I have found that I can mark the Restricted key figrue Statistics as constant Selection. But then it ignores all the filters and the statistics is then 185 for all values.

Can anyone suggest a solution for this?

Regars

Jiri