Copying Key figure from Cube 1 to Cube 2

[Former Member]

Hello Experts,

I'm a beginner in IP and do have one question:

I've a IP layout created on top of an aggregation level which is on top of a MultiProvider that encompasses both a real-time as well as Standard Cube.

The layout looks like the following:

For a single Customer and Product Hierarchy

                    Period1    Period2   Period3

Revenue            20

Driver 1             30

Driver 2             40

Total Revenue   90

Now I would like to copy this value of '90' to another cube for same customer,Product Hierarchy,Posting Period and so on, which will be disaggregated to lower SKU's.

How shall I achieve this?

Really Appreciate your guidance on this.

Thanks & regards,

Jomy

[Former Member · Accepted solution]

You should create on the same Aggr.Lev planning function with type Formula. Then choose Infoprovider as Char for changing. Also I don't understand what is Driver1, Driver2, Revenue etc. If it is a char then you have to add this char to Chars for changing, if it is a Key Figure - you shouldn't choose it.

When you begin to create formula you have two options:

Drivers is a Char

<Infoprovider, CharDriver, KeyFigure>

DATA l_ch as CharDriver.

DATA l_sum as f.

FOREACH l_ch

  f = f + {CUBE1, l_ch, 0AMOUNT}.

ENDFOR.

{CUBE2, Total Revenue, 0AMOUNT} = f.

Drivers is a bunch of KF's

<Infoprovider, KeyFigure>

{CUBE2, Total Revenue} = {CUBE2, Revenue} + {CUBE2, Drvier1} + {CUBE2,Drvier2}.

I hope it will help you.

[Former Member · Accepted solution]

Hello Michael,

It really helped me with some of the FOX formula. I do have some questions:

Drivers are bunch of Key figures. This aggregation level is created on top of the Multiprovider encompassing both a Standard cube from where we are getting Revenue. Lets Say it as Cube 1.

Drivers are coming from Cube 2. lets says it as Cube 2.

Planning layout is for a single Customer and Product and for each month. So basically what I need to do here:

Sum up the total Revenue as Revenue from Cube1 + Driver1 from Cube 2 + Driver2 from Cube 2 for each Customer, Product and for every month.

Then copy this Total Revenue for each particular Customer, Product and that Particular Month to Cube 3.

How can we achieve this in a FOX formula ?

Question 2:, What might be interesting to know is, whether this Cube3 has to be part of a separate Multiprovider or can be part of this Multiprovider only from the design standpoint.

Question 3: Finally How to make sure that this planning function is executed when we save the Planning Layout ?

Really appreciate your guidance on this.

Thanks & regards,

Jomy

[Former Member · Accepted solution]

Hello Michael,

I think, I've tried and understood how to achieve solution for Question 1 and Question 2.

I did the following:

DATA I_FISCPER TYPE 0FISCPER3.

DATA I_CSUP TYPE Product.

DATA I_CUST TYPE Customer.

FOREACH I_CUST,I_CSUP,I_FISCPER.

  {AMOUNT,I_FISCPER,Cube3,I_CSUP,I_CUST} =

                                                 {Driver1,I_FISCPER,Cube2,I_CSUP,I_CUST} +

                                                 {Driver2,I_FISCPER,Cube2,I_CSUP,I_CUST} +

                                                 {AMOUNT,I_FISCPER,Cube1,I_CSUP,I_CUST}.

ENDFOR.

Then, I've added Cube 3 in the same MultiProvider.

Let me know, If I did something which was not needed.

Need your input in Question 3.

I've a Planning sequence, which I've created and want to execute that planning sequence when I save the IP Query with planned data.

One other question, which is of interest to me is, right now I've achieved all these considering Driver 1 KF value as absolute $ amount. Suppose it is a % field and hence when the user enters the percentage figure( which should internally calculate as 5% of 20 and then save to the cube), then what all changes need to happen in the design? i.e.

For a single Customer and Product Hierarchy

                       Period1    Period2   Period3

Revenue           20

Driver 1              5%

Driver 2              40

Total Revenue   61

Thanks & regards,

Jomy

[Former Member · Accepted solution]

First of all, about your code.

As I see, you are copying data from one customer to the same customer (the same situation for product). So you don't have to choose these chars as "Fields to be changed", coz these fields doesn't changed. You could leave them unselected. Then all your data will be splited into several packages with similar values of unselected fields. In each package there will be new rows with new values of "fields to be changed".

Q2. Frankly speaking, I don't understand you. But IMHO one MultiProvider with all necessary cubes is a good solution.

Q3. It depends on interface you use. But everwhere there is an message area. Place it in your layout and you'll get message something like "Function F1 finished succesfully".

Here is place where you could finf in WAD:

Unfortunately I have only Russian interface. It is Extended group and System messages object. Or something like this. I'm not sure about translation. In BEx just choose object whith the same icon.

By the way, don't forget to switch on messages in Layout params:

So choose Web-Template, and turn on first two options.

I hope it will help you.

[Former Member · Accepted solution]

Hello Michael,

Thanks for your reply, unfortunately this last question of mine is missed.

Question 4:

One other question, which is of interest to me is, right now I've achieved all these considering Driver 1 KF value as absolute $ amount. Suppose it is a % field and hence when the user enters the percentage figure( which should internally calculate as 5% of 20 and then save to the cube), then what all changes need to happen in the design? i.e.

For a single Customer and Product Hierarchy

                       Period1    Period2   Period3

Revenue           20

Driver 1              5%

Driver 2              40

Total Revenue   61

How can we achieve this including the Total Revenue ?

BTW, My layout as of now is Analysis for Office.

Thanks & regards,

Jomy

[Former Member · Accepted solution]

DATA I_FISCPER TYPE 0FISCPER3.

DATA I_CSUP TYPE Product.

DATA I_CUST TYPE Customer.

FOREACH I_CUST,I_CSUP,I_FISCPER.

  {AMOUNT,I_FISCPER,Cube3,I_CSUP,I_CUST} =

                                                 {Driver2,I_FISCPER,Cube2,I_CSUP,I_CUST} +

                                                 {AMOUNT,I_FISCPER,Cube1,I_CSUP,I_CUST} *

                                                 (1 + {Driver1,I_FISCPER,Cube2,I_CSUP,I_CUST} / 100).

ENDFOR.

Maybe something like this?

About interface - it doesn't matter, just place object with this icon somewhere in your layout and you get desired messages.