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.
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
Note:
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.
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.
IMG001: Scenario Description
- 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.
IMG002:Table Output
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"
IMG003:Adjustment Measure Creation
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:
IMG004: Calculated Measure Creation
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".
IMG005: Input Template Layout and Data Source Filter
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
IMG006: Adj data on INPUT data source member (Model Data)
Note:
- 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.
IMG007: Calculated account created in calculation workspace
IMG008: Calculated account created in Account dimension
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.
- SAP Managed Tags:
4 Comments
