cancel
Showing results for 
Search instead for 
Did you mean: 

FOX formula to move actuals and restatements to one cube

former_member246680
Participant
0 Kudos

Hi All,

I have written a small code to move GL Actuals from one cube to another. The client has another requirement where the actuals are supposed to be extracted together with restatements into one cube. Restatements are sitting in a separate cube. Can you kindly help with a FOX formula for that.

Thanks a lot

Jordan

Accepted Solutions (1)

Accepted Solutions (1)

cornelia_lezoch
Active Contributor
0 Kudos

well you have to ask your question more in detail.

former_member246680
Participant
0 Kudos

Hi Cornelia,

Thanks for your reply. Let me give you some detail.

I have an multi-provider ZSIS having several infocubes which include ZGLACT which is for actuals. There is also an infocube ZRES where restatements are loaded through a flat file. Other cubes contain plan data, capex data and so forth. We have created an infocube for GL planning for SAP BPC where GL actuals as well as restatements are supposed to be extracted to. We are supposed to write a FOX code to write to extract the data from the multiprovider into the GL planning cube. Exclusions for GL actual data include capex data as well as HR data which has some GL accounts for it.

My question is on to write the FOX code. Keyfigures include 0SALES and 0BALANCE.

Fields to be changed also include currency type, version, fiscal year, profit center, 0infoprov.

I hope I have given enough detail

Many thanks

Jordan

cornelia_lezoch
Active Contributor
0 Kudos

Hi Jordan,

yes, I understand what you want to do - thats easy.

So what is the question? We can not give general and basic training here. We only answer specified questions.

So start your fox, and ask a detailed question if there is one.

the code should be something like:

{keyfigure, target cube, fields to be changed} = {keyfigure, source cube, fields to be changed}.

regards

Cornelia

former_member246680
Participant
0 Kudos

Hi Cornelia,

How do you include restatements into  the formula? Restatements are usually changes to financial statements. Are they added in the formula syntax?

Regards

Jordan

cornelia_lezoch
Active Contributor
0 Kudos

hi Jordan,

what is restatement? Is it a key figure and a data record in a cube? Is it a calculation result - what is the caluclation rule?

regards

Cornelia

cornelia_lezoch
Active Contributor
former_member246680
Participant
0 Kudos

Hi Cornelia,

Restatements are data records coming from a separate cube and come through a key figure.

Regards

Jordan

former_member246680
Participant
0 Kudos

Thanks Cornelia,

Let me look through. I have an idea,

many thanks

Jordan

cornelia_lezoch
Active Contributor
0 Kudos

so what is the exact question???

do you want to add several key figure from several cubes into one key figure in one cube?

then it something like

{target key figure, target cube} = {source key figure1, source cube1} + {source key figure2, source cube2}.

please start writing your code, post the code and post the exact question

former_member246680
Participant
0 Kudos

Hi Cornelia,

I have written the following formula. It can see the records but its failing to write them to the source cube.

DATA KF TYPE KEYFIGURE_NAME.

DATA CURR TYPE 0CURTYPE.

DATA FP TYPE 0FISCPER.

DATA PP TYPE 0FISCPER3.

DATA VAR TYPE 0FISCVARNT.

DATA FYEAR TYPE 0FISCYEAR.

DATA CUBE TYPE 0INFOPROV.

DATA PC TYPE ZMIPROFIT.(profit center)

DATA PPC TYPE ZMIPRTPRF.(partner profit center)

DATA VER TYPE ZMIVER.(version)

VER = VARV(ZFM_VER). (variable)

FOREACH CURR, FP, PP, VAR,FYEAR,PC,PPC,VER IN REFDATA.

IF VER = '#'.

{0SALES, '10' FP,PP,FYEAR,'TARGETCUBE',PC,PPC,#} = {0SALES,'00',FYEAR,'SOURCECUBE1', PC,PPC,#}.

{0SALES,10,FP,PP,FYEAR,'TARGETCUBE',PC,PPC,#} = {0SALES,'00',FYEAR,'SOURCECUBE2', PC, PPC, #}.

ENDIF.

ENDFOR.

cornelia_lezoch
Active Contributor
0 Kudos

Hi Jordan,

why are there so many fields to be changed?

From what i can see, you need only

key figure,

that first char which has value 00 and 10

and the cube.

I am not sure about those FP and PP in the target definition. You can´t have char in the target operand but not in the source. So either they do not change - then take them out, or they change, then define what is their value in the source.

leave out the if ver = #.

don´t even put it into the fields to be changed, but make a selektion in the filter. If you cant leave it out, then keep the if ...  the way you have it around that code which I state below.

since a formula goes through the target records first, you need to check for existing values in the source first.

so in the end the code should look like:

if {0sales, 00, sourcecube} <> 0.

{0sales, 10, targetcube} = {0sales, 00, sourcecube}.

endif.

regards

Cornelia

cornelia_lezoch
Active Contributor
0 Kudos

one more thing - if you want to add the 2 numbers from 2 source cubes into the same key figure in the target cube, you need to make and addition like I explained in a former post.

otherwise the second statement will always overwrite the first.

and you might need to do in the if clause a check for both source cubes.

maybe it´s a good idea you start with one source cube.

And if you got this one running, then do the addition with the data from the second cube.

regards

Cornelia

former_member246680
Participant
0 Kudos

Thanks a lot Cornelia for your helpful responses.

Regards

Jordan

Answers (0)