Data columns in BPS layout

[Former Member]

Hi,

My current BPS layout has three columns, they are cost,price and margin respectively. The user now ask to add another column call "alert" after the margin column.

The logic is that if the margin is less than 10%, then the alert column should marked with "X", otherwise leave it blank.

In order to add a column after margin column, the added column have to be key figure which will not allowed characteristics,while all the other key figure type such as amount, quantity, date ect will not take

"X".

Any idea or suggestion.

[Former Member]

the business user insist an alert column need to be added right after the margin column, so the characterisc is not an option. I think the only way to satisfy the requirment is to make the maker as a special key figurwe data sucha as "99999999999" instead of "X".

[Former Member]

JW,

If Margin is the last column on your layout, what you do is in the last layout definition screen, add the locl excel formula to the column after where the margin would appear. Then you just use simple excel formula to do it.

For things to appear only if there is data in the row, I typically use an IF in the excel formula and check for values in cost, price, margin. Actually it would be 2 nested IFs. One to check for value and the other to determine the 10% and mark it with a X... In purely excel formula, there is no distinction between key figures and characteristics since it is only Excel.

Hope this helps.

[Former Member]

This BPS layout is not going to use web, it's only going to use SAP GUI, different users going to use the same application at different time,Let say the first user did planning, and save it, then the second user may open it again, he is going to more planning or only redo those items which had been marked with "X" in alert column- which means margin is under goal, they do not want to to get that information through the BEX report but only the BSP layout. That's why I had to add the alert column for that purpose.

[Former Member]

In stead of adding extra key figures to enable this process, you could consider versioning or use a marker characteristic.

The first user still enters price and cost only. A background function can calculate and deliver the under-goal items into a special version where the admin, I suppose, could further process the line items. No need to store the margin or alert.

Hope this helps.

William

[Former Member]

Is your 3rd column "margin" calculated out of "price" and "cost"? If so I am interested to know why you would even want to physically store "margin" itself.

What you could do is embed a query into the same web application where the layout is, containing only "price" and "cost". You should have no problem showing dynamically in the web report "margin" and any sort of alert you want.

Hope this helps.

William

[Former Member]

The reason why we need to store the alert is to make the all the user be aware of that product.

Let say that the goal is to have the margin for all the producat above 10%, if any product's margin is less that 10%, the alert column should be marked with X, then all the user will notice of this and should do something to avoid it when they do their planning.

Is Excel formula can achive this?

[Former Member]

Hi,

We had a similar point we solved as Marc said : using a Excel formula. Problem was to do it dynamically so that it depended on the lead column (let'say Product). The lead column can provide different number of products depending on header selection. We did a macro to have the formula on all the lines of the layout (no more).

Is this possible in Web interface as we know that macro cannot be used in Web ?

Thanks

Tony

[gerd_schoeffl]

Hi,

If you use the Office Web Component ("Excel in Web") then you could use a similar technique via VB script. As OWC can do calculations "on the fly" no round trip is needed after a change of a key figure.

When using non OWC web interfaces a solution is possible by using the Web Interface Extensions ("Exit Class"). Unfortunately a server round trip is necessary to do the calculation after a change.

Best regards,

Gerd Schoffl

SAPMetWeaver RIG BI EMEA

[former_member93896]

Hi JW,

why do you want to store the alert? Just use a local formula in the Excel spreadsheet or use a BEx exception report for this purpose.

Regards,

Marc

SAP NetWeaver RIG