FOX calculation in BPS

[Former Member]

Hi Experts,

I am new to BPS and I'm facing a FOX formula creation problem so some help is needed. We have a planning layout where users may enter amounts in 5 rows. Lead Column has 'Item' and Data Column has 'Posting period' and 'Version'. We have got 12 columns for each month with key figure ZNETTO, version 'T1' and Posting period '0FISCPER3'. Row totals are in the last column.

I have tried to create a FOX formula to do the following calculation from the user entries: = ({Row1Row2Row3}) - ({Row4 + Row5}).

The calculation function would be placed behind a button on the planning layout.

My latest try was: OPERAND DISPLAY {Key Figure Name,Item,Fiscal year}

but I got always some error stating that 'Item' is not recognized Item or some syntax error.

Now some help would be really valuable; how the code should look like and what should I select for 'Fields to Be Changed' and for 'Fields for Condition' ?

Let's start with this, many thanks for your help in advance!

Best regards,

Riku

[Former Member · Accepted solution]

Hi,

Fields to be changed should be only those which differ between the columns - only 'month' (posting period) i suppose.

For help regarding your error, please post the error message and your complete code. It would be easier then, to help you.

[Former Member · Accepted solution]

Hi, and thanks for your answer!

Here is my latest for code/formula:

Operand Display {Key Figure Name,Item,Fiscal year}

***

{ZNETTO, 890999, 2007} = ({ZNETTO,

359999, 2007} + {ZNETTO, 330058, 2007} + {ZNETTO, 371000, 2007}) - ({ZNETTO, 773000, 2007} + {ZNETTO, 775000, 2007}).

***

'Formula error: Formula element '890999' could not be recognized'

I was thinking that 'Item' (0CS_ITEM) would be enough to differentiate as these 5 plan-enabled rows are each assigned to different Item. Currently I have also row 6 assigned to an Item '890999' under 'Lead Columns'. Net Sales (ZNETTO) is my key figure.

As I understand this, it should be pretty simple calculation but haven't been so far able to make it work.

Hope this helps to spot reveal more about my problem.

Thanks again,

Riku

[Former Member · Accepted solution]

Hi,

Sorry I did not see the question properly... you are operating on rows - i answered thinking it was columns. Your code doesn't seem to have any problem. This error comes when the value "890999" is not present in master-data. Please check this.

[Former Member · Accepted solution]

Hi and thanks again for replying,

I have checked my InfoObject '0CS_ITEM' and this 890999 is present..

To clarify more, below how I need the function to work. 'Total' column calculation is working fine already as it was easy to define in Data Column 'Total' [selection]. Users may enter numbers on rows 1-5 and respectively columns 'Target 1' - 'Target 12'.

-

---

Target 1 - Target 2 - ... Total

Row1 -

---

5--

---

20--

---

25

Row2 -

---

5--

---

20--

---

25

Row3 -

---

10--

---

20--

---

30

Row4 -

---

5--

---

10--

---

15

Row5 -

---

5--

---

5--

---

10

******

Row6----10

---

45--

---

55

Any idea?

Thanks,

Riku

[Former Member · Accepted solution]

Hi,

Is the <i>Item</i> in operand display the same as 0CS_ITEM? Along with the error message, it must be telling you the line no. and column where it encountered the error. Is that the same line where you have written the code ZNETTO, 890999, 2007} = ({ZNETTO,

359999, 2007} +........ ?

[Former Member · Accepted solution]

Hi,

I got the formula work by adding 'Cons Chart of Accounts' to the Operand Display:

{Key Figure Name,Cons Chart of Accts,Item}. This was also in the master data so that solved this issue.

Now I have created a button to my layout that uses this function but it doesn't write the data to my infocube for Item 890999 (5 data records were read, 0 of them were changed, 0 generated) . I made row6 (890999) Comparison Row.

So maybe I should differentiate something more in the formula, or?

Many thanks,

Riku

[Former Member · Accepted solution]

Run the function with trace in the workbench and analyse the records. You should get the required hints.

[Former Member · Accepted solution]

I believe, 890999 might be last data to be included in master data.

You might have not activated the infobject after updating this item.

Can you please check by updating the item infobject and then check?

Regards

Abhijit Misra

[Former Member · Accepted solution]

Thanks again guys, but it's not the master data activation issue either (I wish it was!).

When I chose all the characteristics to field to be changed and = 1000. it wrote this then in to the cube. We are still troubleshooting and trying to find the solution so any ides are welcome! Something related to this Consolidation Chart of Accounts + Item combination...

Best regards,

Riku