Display other than (None) when a Key Figure has no value at displayed Planning Level

[prasad_mandre]

Hello Experts,

I have a IBP excel add-in specific requirement. I would like to display something other than (None) when a key figure doesn't have anything available for displayed planning level. Basically, I would like to know how to change below formula = EPMOlapMemberO("[XXXXX].[].[__NULL]","","(None)","","000")

This seems to be a combination of EPM and IBP. I don't have EPM ribbon installed in my excel add-in.

e.g. KF1 and KF2 are maintained at PERPROD level hence when I see them at PERPRODLOC level it displays (None) as shown below

Even if I will be able to change color of the text to match background if it says (None) then it will suffice my purpose.

Please Note: I have tried EPM Edit Report >> Member Names >> Dimension >> Enter Name for Member >> Override option. This is a bit rigid for our requirement. Applicable only for selected Dimension. We want something applicable by default to all dimensions in open planning view. User should see same result for any combination of Planning Level selection.

Thanks in advance.

Regards,

PKM

[piyush_parekh · Accepted solution]

Hi,

You can use EPM formatting to achieve the same. You need not have to install EPM add in. IBP add in is sufficient. Here is how:

1. Open planning view template. Click on Edit View dropdown and select View Formats. This will unhide "EPMFormattingSheet". In EPMFormattingSheet, go to Dimension Member/Property Formatting section, and click on checkbox next to it to enable this section. Also select Priority to Row Format radio button.

2. Scroll down to Row group in the same section. Select Apply checkbox corresponding to Formatting on Specific Member/Property. Click on Add Member/Property button. A pop-up window will open. In the left panel, scroll down to select key figures. From right panel, select "KF1". You will see small checkmark next to "KF1" . Click on Add to Multiple Selection.

3. Continue with the same window and select attribute for which it shows "None'. In your case, Location ID. From right panel, Select (None) and click on Add to Multiple Selection button.Now move to Multiple Selection Overview tab and select both checkboxes next to Key Figures and Location ID dimension. Click on OK to continue. This will create union of these two fields. It will start reflecting in EPM sheet as KF1||(None)

4. Next to this member, Under Header section, select Conditional Formatting in Use. Apply Conditional Formatting to the Label cell as you mentioned - Change the color of cell same as that of background color of planning view if text contains "None". Go to your planning view and click on Refresh.

Please keep in mind using too many conditional formatting could deteriorate Refresh performance. Hope this helps!

Regards,

Piyush

[prasad_mandre · Accepted solution]

Hi Piyush,

Thanks for your detailed answer. It is helpful!

However, my primary issue still remains unanswered. I would like to apply this logic dynamically to any dimension and key figure combination selected in planning view by user. Here, we are limiting this formatting to only specified Planning level(Location ID) and KF1.

If user selects any other dimension then this logic wont be applicable there. I will have to manually add this logic to that combination in EPMformatting.

Thanks

[piyush_parekh · Accepted solution]

Hi,

It looks like this is a standard behavior and cannot be overwritten by admin settings. May be you can reach out to SAP via OSS or raise an improvement request via customer influence portal in the following link.

https://influence.sap.com/sap/ino/#/

Regards,

Piyush

[prasad_mandre]

piyush.parekh Yes, it seems raising OSS is the only remaining option. Thanks for your inputs.

Regards,

Prasad