BPC Logic Script: using hierarchy as attribute?

[Former Member]

Hi all,

I am working with BPC 10 SP5 for NW, calculation engine ABAP, BW 7.4.

Here is a snapshot of CASH_BUDGET model:

We want to fill a certain region of our cube with constant value. More specifically, for each base member of ENTITY dimension, we need to fill an entire branch containing the said base member with the same constant. For example if the user specifies node R30, we need to write the constant to members R10, R20, R30, R40, R50.

here is the code:

*SELECT(%BTYPELIST%, "[ID]", BUDGET_TYPE, "[LIMITS] = '1' " )

*XDIM_MEMBERSET AA_NUMBER = AA_NONE

*XDIM_MEMBERSET AUDITTRAIL = LIM

*XDIM_MEMBERSET BUDGET_OBJECT = NONE

*XDIM_MEMBERSET BUDGET_TYPE = %BTYPELIST%

*XDIM_MEMBERSET COFOG = FA_NONE

*XDIM_MEMBERSET CURRENCY = LC

*XDIM_MEMBERSET C_ACCOUNT = <all>

*XDIM_MEMBERSET C_ACCOUNT_DT = CAD_NONE

*XDIM_MEMBERSET FLOW = F99_1

*XDIM_MEMBERSET GRANTS = NONE

*XDIM_MEMBERSET TIME_DT = TD_NONE

*XDIM_MEMBERSET VERSION = CURRENT

*XDIM_MEMBERSET C_ACCOUNT as %ACCL% = BAS(LIM_ACCOUNTS)

*SELECT( %ENTITYLIST%, "[PARENTH1]", ENTITY,"[ID]= [%ENTITY_SQLSET%]  ")

*WHEN_REF_DATA = MASTER_DATA

*WHEN C_ACCOUNT

*IS %ACCL%

*WHEN ENTITY.PARENTH1

*IS %ENTITYLIST%

*WHEN CATEGORY

*IS "RES"

*REC(EXPRESSION = 10e+10, FLOW = "F99_1" )

*IS "RE"

*REC(EXPRESSION = 10e+10, FLOW = "F99_4" )

*ENDWHEN

*ENDWHEN

*ENDWHEN

In UJKT we test this script with Data Region:

CATEGORY= RES

ENTITY= G21

TIME= 2013

Result in UJKT:

LGX:

*XDIM_MEMBERSET AA_NUMBER = AA_NONE

*XDIM_MEMBERSET AUDITTRAIL = LIM

*XDIM_MEMBERSET BUDGET_OBJECT = NONE

*XDIM_MEMBERSET BUDGET_TYPE = %BTYPELIST%

*XDIM_MEMBERSET COFOG = FA_NONE

*XDIM_MEMBERSET CURRENCY = LC

*XDIM_MEMBERSET C_ACCOUNT = <all>

*XDIM_MEMBERSET C_ACCOUNT_DT = CAD_NONE

*XDIM_MEMBERSET FLOW = F99_1

*XDIM_MEMBERSET GRANTS = NONE

*XDIM_MEMBERSET TIME_DT = TD_NONE

*XDIM_MEMBERSET VERSION = CURRENT

*XDIM_MEMBERSET TIME = 2013

*XDIM_MEMBERSET ENTITY = G21, F10

*XDIM_MEMBERSET CATEGORY = RES

*WHEN_REF_DATA = MASTER_DATA

*WHEN C_ACCOUNT

*IS L_C_101,L_C_102,L_C_15,L_C_205,L_C_206,L_C_4,L_C_41,L_C_45,L_C_450,L_C_452,L_C_5,L_C_50,L_C_55,L_C_60,L_C_600,L_C_601,L_C_650,L_C_70,L_C_71,L_C_FININVESTMENTS,L_C_INVESTEERINGUD,L_C_KOGU- KULUD,L_C_KULUD

*WHEN ENTITY.PARENTH1

*IS %ENTITYLIST%

*WHEN CATEGORY

*IS RES

*REC(EXPRESSION = 10e+10, FLOW = F99_1 )

*IS RE

*REC(EXPRESSION = 10e+10, FLOW = F99_4 )

*ENDWHEN

*ENDWHEN

*ENDWHEN

-------------------------------------------------------------------------------------------------------------------------------------

LOG:

UJA_ADMIN_ERROR:Invalid attribute name (PARENTH1) in dimension (ENTITY)

Here is a snapshot of ENTITY dimension structure:

Here is a snapshot of ENTITY dimension Hierarchy:

Here is a snapshot of C_ACCOUNT dimension Hierarchy:

So obviouslz the line

*SELECT( %ENTITYLIST%, "[PARENTH1]", ENTITY,"[ID]= [%ENTITY_SQLSET%]  ")

is the problem here. We are trying to use the hierarchy the same way we would use an attribute and that is not working. However, we hope that there is a way to do it. Does anybody know how to do it? Is it even possible to use Hierarchy in *Select or *XDIM_FILETER or even *XDIM_MEMBERSET <Dimesnion> as <Expression>  ? We know of a way to use BAS() function, but that function works when only one member is passed to it, not a variable possibly containing several members .

[former_member186338 · Accepted solution]

Hi Karolis,

Definitely the PARENTH1 "property" is not supported in SELECT, the issue is here

*SELECT( %ENTITYLIST%, "[PARENTH1]", ENTITY,"[ID]= [%ENTITY_SQLSET%]  ")

by the way, the syntax [ID]= [%ENTITY_SQLSET%] is also incorrect, have to be something like [ID]= %ENTITY_SET%

I will check your logic and propose some solution.

Vadim

[former_member186338 · Accepted solution]

Ups, there are some other issues like number format EXPRESSION = 10e+10...

Please, clearly specify, what is the user input for this DM. How many and what type of entities will select user?

Vadim

[Former Member · Accepted solution]

1. Well the constant format works for me in a different script, but ,in general, any constant would work for me here. We can use constant "100" for the moment.

2. The users will select 1,2,3 or more base ENTITY members ( I think we will demand that they not to leave it at default value "<all>")

Example 1:

User selects R10, we want to fill with constant the following members: R10, R20, R30, R40, R50 - all children of R member

Example 2:

User selects R10 and H62 , we want to fill with the same constant the following members: R10 to R50 and H10 to H80 - all children of R and H members

Example 3:

User selects R20, R30 and H61, we want to fill with the same constant the following members: R10 to R50 and H10 to H80 - all children of R and H members

The script from DM:

PROMPT(SELECTINPUT,,,,"%CATEGORY_DIM%,%TIME_DIM%,%ENTITY_DIM%")

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SUSER,%USER%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPPSET,%APPSET%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPP,%APP%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SELECTION,%SELECTION%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,LOGICFILENAME,MAKE_UNLIMITED_TEST.LGF)

[Former Member · Accepted solution]

I think if PARENTH1 worked as property here (hypothetically)

*SELECT( %ENTITYLIST%, "[PARENTH1]", ENTITY,"[ID]= [%ENTITY_SQLSET%]  ")

I would find a way to replace %ENTITY_SQLSET% with something that works, so this should definitely not be a problem.

BTW, I was thinking of using the function SIBLINGS , but I never got it to work. I was thinking about something along the lines of

*SELECT(%ENTITYLIST%, "[ID]", ENTITY, " [ID] = [%ENTITY_SET%].Siblings ")

...

...

...

*WHEN ENTITY

*IS %ENTITYLIST%

...

But of course that did not work for me.

[former_member186338 · Accepted solution]

To my mind it's a bit confusing way of user input... What if the user will select 2 base members of the same parent?

For base member selection you can always create an additional property that will duplicate PARENTH1 (require maintenance ) and use this property in SELECT.

You can also ask user to enter parent members using PROMPT(SELECT,... instead of PROMPT(SELECTINPUT,... for %ENTITY_DIM%

Then:

*WHEN ENTITY.PARENTH1

*IS %ENTITY_SET%

You can also define some entity groups and create the property with the group ID and ask user to select group ID in the PROMPT(COMBOBOX,... In this case the groups can't intersect.

B.R. Vadim

[former_member186338 · Accepted solution]

Sorry,

I don't know were you found %ENTITY_SQLSET% - for BPC NW the scope variable is always %DIMNAME_SET%!

And no functions are supported in WHERE condition of SELECT, only text strings, =<> AND OR

Vadim

[Former Member · Accepted solution]

I found the %DIMNAME_SQLSET% in http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/80ffae4b-99d5-2a10-4e9f-96149969a...

it is an old documents, but it is the most comprehensive and readable to me that I could find, I also use this spread sheet for for MS and NW BPC version discrepancies

https://docs.google.com/spreadsheet/ccc?key=0AlNX_-VUdSnudFhZSlpibzJ1NmpTX0N1Ty1WcUZUbFE&authkey=CM3...

(The MS version is full of functions that could help me in this situation! )

In this series of posts

I have found a few lines that were similar to what I need:

*XDIM_GETMEMBERSET P_CC=[P_CC].[H1].[AAPJ].CHILDREN

           *APP=PLANNING

           *XDIM_MEMBERSET P_DataSrc=INPUT

           *CRITERIA [P_ACCT].[H1].[CE0001000]>1000 

      *ENDXDIM

I would try to use PARENT MDX keyword instead of CHILDREN and expect to get something useful, but I never managed to make it work with this syntax in *XDIM_MEMBERSET

Overall MDX in BPC script logic is even more confusing than MDX its self to me. Maybe thats because I know some SQL and can break though some paradigm shift .

"And no functions are supported in WHERE condition of SELECT, only text strings, =<> AND OR" - well, good to know.

[Former Member · Accepted solution]

If the user will select 2 base members of the same parent , we would want to fill the same members with the same constant as if the user selected only one of the base members. I agree that his is cumbersome, but our clients business process apparently requires this.

I will try to use the suggestion you gave with selecting parent members only, but so far I was able to select only the base members in the prompt.

It is very sad that I can not find a way to make Hierarchy work the way attributes work for me, because I sill have a lot of implementation to do and I would need this feature A LOT.

[former_member186338 · Accepted solution]

"I found the %DIMNAME_SQLSET% in http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/80ffae4b-99d5-2a10-4e9f-96149969a...

This document is about OLD MS 4.2 Version, actually at this time (2006) only MS version was present!!! Forget about this document, it's 90% not applicable to the current BPC NW 10.

Unfortunately the second document is also containing a lot of information applicable to MS version only. And Part 2 you are referencing is 80% about MS!!! But stated that it's about NW. Ex.: *XDIM_GETMEMBERSET - MS version only.

Recommendation - use ONLY BPC 10 NW online help. It's more or less OK.

Vadim

[former_member186338 · Accepted solution]

"I will try to use the suggestion you gave with selecting parent members only, but so far I was able to select only the base members in the prompt." Because you are using PROMPT(SELECTINPUT,... and I have explicitly mention to use PROMPT(SELECT,... - SELECT will allow user to select both parents and base members. There is no way to restrict it to parents only.

Vadim

[Former Member · Accepted solution]

Hi Vadim,

do you mean the "Help" button at the top-right corner of BPC web application?

[former_member186338 · Accepted solution]

Yes:

There are also some errors here, but not a lot And they even update it with the functionality provided with new SP's (unfortunately without mentioning the SP level).

B.R. Vadim

[Former Member · Accepted solution]

I think I already found one error

But thanks! The button never worked on my computer (still doesn't) so I never bothered, thought it was just another thing that is not working. A colleague has just sent me the web link (of the button) and now I am able to see the info.

[former_member186338 · Accepted solution]

"I think I already found one error" - share, please!

[Former Member · Accepted solution]

In *SELECT description they have:

"The SELECT statement is fairly limited, as it only supports the equal sign (=) , not equal to (<>) and cannot be used to combine multiple filter criteria with AND or OR keywords."

And you already mentioned above  (and I am using I one of my scripts) that OR and AND actually can be used in *SELECT    

[former_member186338 · Accepted solution]

Yes, this part is from old BPC 7X help. They did not updated it...

But *DESTINATION_APP was updated to explain ADD_DIM on property!

Unfortunately they forget to mention that in BPC NW 10 (compared to 7.5) the statement *DESTINATION_APP is valid to only nearest WHEN/ENDWHEN loop (in 7.5 it was valid to all following WHEN/ENDWHEN)

You can also search SAP notes for "K2", "script logic" etc.

Vadim