How to use Restrict formula for calculated measure to restrict dimension members for an input template in SAP Analytics Cloud Planning
In SAP Analytics Cloud Planning, lot many input templates need to be developed in order to capture planned values.
While developing the input template, often, we come across requirement that for a particular dimension, member the member from other dimension needs to be fixed. To explain further, lets us take the below example for the new model where measures are used. Similar functionality can be used for account members too in the new model.
- The measure “Base Volume” is always tagged to Data Source dimension member “S4HANA” which is non editable for the users (because the data source for base volume is SAP S4HANA in this example).
- The measure “Adjustments” is always tagged to Data Source dimension member “Input” which can be input by the user.
- Finally, the measure “Final Volume” needs to be calculated and the data needs to be stored on “Calculated” member of Data Source dimension
Also, the user does not want to see the additional column of data source in the input template as this column is more for the developers to manage the data on right audit trails so, ideally, the layout of input template should be as shown below for the user.
Now, having understood the problem statement, let us see how to achieve this using calculated measure
In order to store the value for measure “Adjustments” always on “Input” data source, following steps needs to be followed:
Step1: Create the required measure i.e. “Adjustments” for which the value needs to be stored on data source “Input”
Step 2: Create a calculated measure by switching to “Calculations” workspace and use “Restrict” formula to restrict the measure for the Datasource dimension as shown below:
Formula : RESTRICT ([Adj], [d/DataSource] = “INPUT”)
More dimensions can also be restricted via AND functionality e.g. RESTRICT ([Adj], [d/DataSource] = “INPUT” AND [d/Date] = “202206”)
Step 3: Now include “ADJ_INP” in the input template instead of “Adj” dimension member.
By doing so, whenever an entry is made on the member ADJ_INP the value always gets stored on measure “Adj” and Data Source member “INPUT”.
Also, all the members of the Data Source dimension can be included in the filter of the table as shown but the value for “Adjustments” will always be stored on “INPUT” Data Source member and it wont get distributed.
The results can be validated in the model data as shown post publishing
- Restrict formula allows only measures or account as the first parameter. Account and measure can not be used simultaneously in one formula.
- Calculated account formula can be maintained in calculation workspace or in the formula column in the account dimension as shown below. Both can be used interchangeably.
To conclude, the formula column was always available to write calculated account however, this feature for calculated measure is defined in the calculated tab of the model. Restrict formula is a great option to restrict various other dimension members for an account or measure. With this blog you have understood how to use of Restrict formula for calculated measure.
Thanks for reading this blog post, hopefully the blog post was informative. This is my first blog hence, looking for feedback or thoughts from you all.
Thanks for the blog-post with very useful content with Great insights!
Agree, thanks Shefali
Thank you! 😊
Is this way of working SAP recommended best practice? Because I do see challenges when combining this with currency conversion, data locking, data actions etc..
Is there a reasoning why not to use other capability like validation rules, cross calculations in story, role-based write access, ... etc.
Wondering what is future proof & the best practice.. 🤔
I have used this with data action, validation rules along with data locks in my project and it worked fine for us!
Thanks for your comments and feedback.