BPS FOX Formula to copy data from one period to range of reriod.

[Former Member]

Hi all,

I need help in writing fox formula for the following requirement.

I want to copy records from one period to a range of period. for example..

Existing data:

C.code, SKU, CG, fiscper, amount

1000, M1, CG1, 001.2010, 120.10

Desired result:

C.code, SKU, CG, fiscper, amount

1000, M1, CG1, 002.2010, 120.10

1000, M1, CG1, 003.2010, 120.10

1000, M1, CG1, 004.2010, 120.10

My design:

1. I created the following variables in planning area

a) "refperiod" and assigned value i.e.last month.(user exit)

b) "rangeperiod" which is being populated via user exit from 2 other variables values ("fromperiod" & "toperiod")- users will feed value of these 2 variable while executing planning function.

2. I used "rangeperiod" variable as a value of period char in Planning level.

3. "Key figure" and "fiscper" are selected as "Fields to be changed" in planning function.

4. I have written the following FOX in Parameter group.

DATA ref_per TYPE 0fiscper.

DATA range_per TYPE 0fiscper.

DATA Amt type F.

ref_per = varv ( "refperiod").

Amt = ( 0amount, ref_per).

FOREACH range_per.

( 0amount, range_per) = Amt.

ENDFOR.

In cube, there is a value for 0amount KF for reference period.

When I execute planning function, I see message records read 0, changed 0 and generated 0.

Can you guys help me in correcting my fox?

Thanks,

SAC

Edited by: SAP ACCOUNTANT on Sep 4, 2010 2:21 PM

Edited by: SAP ACCOUNTANT on Sep 4, 2010 2:30 PM

[former_member5472]

Hi,

DATA TO_CGR TYPE 0CUST_GROUP.

Foreach TO_CGR in variable 'CGRP_GRP'.

Don't give variable name in quotes.



DATA TO_CGR TYPE 0CUST_GROUP.

Foreach TO_CGR in variable CGRP_GRP.
......
endfor.

Thanks

Pratyush

[Former Member]

Hi Pratyush,

Unfortunately, Still I am getting the same syntax error.

Formula error: , was expected.

Did you use this syntax before? I tried whole SDN but could not find any code where somebody is using foreach statement on variable.

I am working on BI 3.5. (BPS)

Could you please also reply to my another query of using third DO loop in my existing loop to copy data to all SKU's?

Thanks,

SAC

[former_member5472]

Hi,

You can use the foreach loop on variable like this

data l_variable type <info object name on which variable was crreated>

foreach l_variable in variable <Global variable name which has the values >

Thanks

Pratyush

[Former Member]

Hi Pratyush,

I am using foreach loop on variable like this.....

DATA TO_CGR TYPE 0CUST_GROUP.

Foreach TO_CGR in variable 'CGRP_GRP'.

when I check syntex..it gives me the following error....

FORMULA ERROR:, WAS EXPECTED

and it points to foreach loop (after TO_CGR )

I would really appreciate if you please correct me where I an making mistake/s.

PS: Global BPS variable CGRP_GRP is 'characteristic value' variable type 'user defined values' created on planning area.

Thanks,

SAC

[Former Member]

Hi Guys,

Have anybody got solution to this?

How I can use foreach statement on variable?

Do I need another Do statement to copy data to other SKU's (whicha are in variable)? and how?

Thanks,

SAC

[former_member5472]

Hi,

You have to keyword foreach Variable IN VARIABLE Var instead of foreach CG.

Foreach works when there is data already present in the cube/buffer. Since in your case you are trying to generate new records with new values it will not work..

DATA REF_PER TYPE 0FISCPER.
DATA FR_PER TYPE 0FISCPER.
DATA TO_PER TYPE 0FISCPER.
DATA REF_CG TYPE OCUST_GRP.
DATA CG TYPE OCUST_GRP.
DATA AMT TYPE F.

FR_PER = VARV( 'fr_per' ).
TO_PER = VARV( 'to_per' ).
REF_PER = VARV( 'ref_per' ).
REF_CG = VARV( 'ZCUST_GRP' ). " Variable type-User defined values

AMT = {0AMOUNT,REF_CG,REF_PER}.
foreach Variable IN VARIABLE <global varable name>
DO.
{0AMOUNT,CG,FR_PER} = AMT.
FR_PER = TMVL(FR_PER,1).
IF FR_PER > TO_PER.
EXIT.
ENDIF.
ENDDO.

also check the copy to all target option...That will be much easier to do without coding..

Thanks

Pratyush

[Former Member]

Hi Pratyush,

Thanks for your reply, I was expecting the same type of answer.

foreach Variable IN VARIABLE <global varable name>

but ,the above line is not clear to me, When I try this I get error. Could you please give me a complete code for this line.

Details:

char name = 0CUST_GRP

Global variable defined at planning area = ZCUST_GRP

Local variable defined as data = CG

Thanks,

SAC

[Former Member]

Hi,

You need to have records to read and manipulate for using FOREACH.

You can also check FOREACH IN SELECTION statement but easiest approach is either to use standard copy function or DO statement.

Try something like this syntax.

DATA ref_per TYPE 0fiscper.
DATA fromperiod TYPE 0fiscper.
DATA toperiod TYPE 0fiscper.
DATA Amt type F.

ref_per = varv ("refperiod").
fromperiod = varv ("fromperiod").
toperiod = varv ("toperiod").

Amt = ( 0amount, ref_per).

DO.
IF fromperiod <= toperiod .
( 0amount, fromperiod) = Amt.
fromperiod = TMVL(fromperiod,1).
ELSE.
EXIT.
ENDIF.
ENDDO.

[Former Member]

Hi Mark / Ergin ,

Thanks you both for replying.

Standard function copy to several data target: I tried this option but doent work in scenario where I want to copy data to range of period (This range is defined by users dynamically via From period and To period variables). I selected "Fiscper" as field to be changed . I assigned a single value variable in old value section and another multiple value variable in new value section. when I execute this, I get a error message that "select single value". If I put manual range of period in NEW VALUE section then it works.

Could you pls correct me if I am missing something?

Fox: It is working perfectly when I use Do and Enddo loop as suggested by Ergin.

Ergin: we are not usiing FOREACH syntax in this code but you are suggesting in your reply???

Fields to be changed are "Fiscper" and "Keyfigure name".

-

---

Now, In my other scenario I want to copy this single record to a range of periods as well as a all CG's assigned to a user as follows:

Existing data:

C.code, SKU, CG, fiscper, amount

1000, M1, CG1, 001.2010, 120.10

Desired result:

C.code, SKU, CG, fiscper, amount

1000, M1, CG1, 002.2010, 120.10

1000, M1, CG1, 003.2010, 120.10

1000, M1, CG2, 002.2010, 120.10

1000, M1, CG2, 003.2010, 120.10

1000, M1, CG3, 002.2010, 120.10

1000, M1, CG3, 003.2010, 120.10

And so on.......

Here, fields to be changed are "Fiscper", "CG" and "Keyfigure name".

Could you please give me a sample code..I know this will be similar to the above code but here, for CG there is not a range.. instead multiple CG values will be assined to a user defined variable . I created a user defined variable and assigned multiple CG values to this variable.

My code:

DATA REF_PER TYPE 0FISCPER.

DATA FR_PER TYPE 0FISCPER.

DATA TO_PER TYPE 0FISCPER.

DATA REF_CG TYPE OCUST_GRP.

DATA CG TYPE OCUST_GRP.

DATA AMT TYPE F.

FR_PER = VARV( 'fr_per' ).

TO_PER = VARV( 'to_per' ).

REF_PER = VARV( 'ref_per' ).

REF_CG = VARV( 'ZCUST_GRP' ). " Variable type-User defined values

AMT = {0AMOUNT,REF_CG,REF_PER}.

Foreach CG.

DO.

{0AMOUNT,CG,FR_PER} = AMT.

FR_PER = TMVL(FR_PER,1).

IF FR_PER > TO_PER.

EXIT.

ENDIF.

ENDDO.

Endfor.

This is not working?

Please correct me where i am wrong.

Thanks,

SAC

[Former Member]

Hi All,

Still waiting for reply..I am missing something in my FOX. Please let me know what is the solution for this.

Thanks,

SAC

[former_member93896]

SAC,

I tested the copy to multiple target values. It works using a variable of type "select options". If it does not work for you, please open a customer message so SAP support can analyze the issue.

Regards,

Marc

SAP Techology RIG

[Former Member]

Hi Marc,

I am working with BPS..and IN BPS variable I can not see variable type "select options". . Please let me know what I am missing.

I found a solution of my issue in FOX via double loop (Do..Enddo)..i.e.

DO.

XX_CGR = XX_CGR + 1.

IF XX_CGR > YY_CGR.

EXIT.

ENDIF.

AMT = {AMT ,REF_CGR, REF_PER}.

DO.

{0AMOUNT , TO_CGR, FR_PER} = AMT.

FR_PER = TMVL(FR_PER,1).

IF FR_PER > TO_PER.

EXIT.

ENDIF.

ENDDO.

ENDDO.

It worked perfectly.

Now, In my next scenarion, I want to to copy existing record to all SKU's as well not only to all periods and Customer group.

It means new records for all materials, all CG and range of period which are in respective variables.

shall I include one more DO LOOP in the same FOX or do we have cleaver solution to get this.

Thanks,

SAC

Edited by: SAP ACCOUNTANT on Sep 10, 2010 11:48 AM

[former_member93896]

Hello SAC,

you don't need FOX for this. Use the standard copy function to copy to multiple targets:

http://help.sap.com/saphelp_nw70/helpdata/en/7f/62fc3f8fc2c542e10000000a1550b0/frameset.htm

Regards,

Marc

SAP Techology RIG