EPM Formatting has been a powerful tool for all the planners who are exploring the strength of SAP IBP to solve their supply chain business issues.
On the similar lines , I am writing this blog to demonstrate as to how can we utilize the EPM formatting option to restrict the users to input a key figure values based on pre defined conditions in excel UI.
Usually we write the conditions in Key figure calculations or we create an alert key figure with those conditions and then display alert once the data is saved or we run simulation.
In this blog I explore the possibility of informing/restricting the user as soon as the value is entered and it violates the condition.
In this example , we have a condition where in Sales team has decided not to provide any Sales discount more than 60%. So they want to restrict the discount value entered in the planning views to be always below 60%.
The below in the Sales Plan View and we have a KF for Sales Price Discount where user can enter the discount %.
To open the EPM Formatting sheet , Select Edit Planning View–> View Formats ,
You will see that a new sheet appears, Open it
Since we are trying to put the condition on the KF , we will try to indetify the KF under Row section of the sheet. (Please note that if the KF is not displayed you can add it via Add Member/Property option)
Now for the KF Sales Fcst Price Discount , for the column ‘Use’ (as above) , double click to select the ‘Data Validation’ option and click OK
Now for the same KF , under Column ‘Data’ , Right Click –> EPM–>Data Validation
Once you select Data Validation , a new pop up box will appear , under settings maintain the validation criteria. Since we are displaying this KF in % we are giving 0.6 value.
Under Input message , please specify as to what message you want to display to user when the cells for that KF is selected.
Under, Error Alert , you can Select the style either as Error (Stop) , Warning or Information. Give the title and description and Click Ok.
Now EPF Formatting is completed. Return to the main sheet and Refresh the sheet. You will see that on selecting any cell of that KF you get an info message displayed.
If user inputs any value which violates the condition , system throws an error pop up.(since we have created a Stop style)
In this way we can restrcit user inputs based on our business logic using EPM Sheet formatting.
Hope you like the approach.
Open for feedback.
PS : Some EPM Formatting help :https://help.sap.com/viewer/b28ffdd739bf45678ef36c44e64652d7/1711/en-US/e073d4c2207e4800a50605c011e0baf7.html