Unable to read from a custom measure

[former_member605951]

Hi Experts,

For performing the entity allocation in our project, we have a to consider the last 13 periods average of the Entity actual data. To do so, we have created a custom measure named R13, which holds the average data for last 13 actual periods. Using this measure, we have designed the script logic to read the R13(custom) measure data and on top of which we are calculating the allocation(%) values which will be used for allocating the forecast data.

The issue we are experiencing is that the data is not being read from the R13(custom) measure and it is by default taking the Periodic measure and also posting to the Periodic measure. Also, while trying to using this custom measure in input schedule, it is working fine and reads the value from it.

We are using BPC 10.1 NW Standarad, SP 13.

Please find the script logic below .Any leads on the above scenario would be of great help. Thank you!

*XDIM_MEMBERSET P_CATEGORY = %P_CATEGORY_SET%

*XDIM_MEMBERSET P_CUSTOMER = %P_CUSTOMER_SET%

*XDIM_MEMBERSET P_TIME = %P_TIME_SET%,2020.INP

*XDIM_MEMBERSET P_PACKAGE = %P_PACKAGE_SET%

*XDIM_ADDMEMBERSET MEASURES = R13, PERIODIC

*XDIM_ADDMEMBERSET P_ENTITY = BAS(PB)

*WHEN P_TIME

*IS %P_TIME_SET%

*WHEN P_PACKAGE

*IS %P_PACKAGE_SET%

*WHEN P_CATEGORY

*IS ACTUAL

*WHEN P_CUSTOMER

*IS %P_CUSTOMER_SET%

*WHEN P_DATASRC

*IS CONSUMPTION

*WHEN P_ACCOUNT

*IS 1000

*WHEN P_ENTITY

*IS BAS(PB)

*WHEN MEASURES

*IS R13

*REC(EXPRESSION=(%VALUE%)/([P_CATEGORY].[ACTUAL],[P_PROMO].[NO_PROMO],[P_DATASRC].[CONSUMPTION],[P_INSTANCE].[NO_IN],[P_CUSTOMER].[TOT_DSD],[P_PACKAGE].[TOT_PKG],[P_ENTITY].[PB]),P_DATASRC=USER_INPUT,P_TIME=2019.INP,P_ACCOUNT=WKPCT)

*ENDWHEN

*ENDWHEN

*ENDWHEN

*ENDWHEN

*ENDWHEN

*ENDWHEN

*ENDWHEN

*ENDWHEN

[former_member224107]

Hi Sindhuja, you are able to use the custom measure in the logic, using it directly in the EXPRESSION statement.

*REC(EXPRESSION=[MEASURES].[R13]/([P_CATEGORY].[ACTUAL],[P_PROMO].[NO_PROMO],[P_DATASRC].[CONSUMPTION],[P_INSTANCE].[NO_IN],[P_CUSTOMER].[TOT_DSD],[P_PACKAGE].[TOT_PKG],[P_ENTITY].[PB],

[MEASURES].[R13]

),P_DATASRC=USER_INPUT,P_TIME=2019.INP,P_ACCOUNT=WKPCT)

However, you need to be careful where your trigger is for the calculation. If you scope in the current period, and if no value exists for a certain combination where we had data in the past, then nothing will get calculated for this combination. You have to analyze whether this situation will occur. Alternatively, however, you can use a full MDX expression to overcome that issue.

Example

*SELECTCASE [TIME].CURRENTMEMBER.NAME
*CASE %P_TIME_SET%

[P_ACCOUNT].[#WKPCT] = ([P_ACCOUNT].[1000],[P_DATASRC].[CONSUMPTION],[MEASURES].[R13])

*ENDSELECT

*COMMIT

[former_member605951]

Hi Wouter,

We have tried using custom measure directly in the REC statement, but were not successful. This is the error message we get:

UJK_EXECUTION_EXCEPTION:MEASURES not specified

[former_member186338]

I do not recommend using custom measure in script logic. The performance can be terrible 🙂

[former_member224107]

If you are running on HANA, the performance is ok. If HANA_MDX is activated,

it is possible that part of that MDX is executed in HANA.

[former_member186338]

I don't see HANA in the question author system 🙂

[former_member224107]

HANA is now such a commodity that it is not mentioned anymore 🙂

Off course, performance could be an issue, but he will be able to assess whether

this is a bottleneck or not. Using the measure the script will be much more readable.