Breake Logic Run by dimension

[former_member622327]

Hi, experts!

We have such logic

*SELECT(%C_R%, "REGION", "P_CC", "ID='%P_CC_SET%'")
*SELECT(%C_FA%, "ID", "P_CC", "REGION='%C_R%' AND DPCODE='FA' AND LEVEL='3'")

*XDIM_MEMBERSET P_DATASRC = KPI
*XDIM_MEMBERSET P_ACCT = F2004030000

*WHEN P_ACCT
*IS F2004030000
*REC(P_CC = %C_FA%)
*ENDWHEN
*COMMIT

We select Cost Center with different one DPCODE (Department Code) in this Region and try write value from it in Cost Center from this Region but with DPCODE = FA.

When we run logic for 1 Cost Center it works perfect.

But then we try run logic for several Cost Centers we got errors in debuglogic.

No records returned by *SELECT(%C_R%, "REGION", "P_CC", "ID='3_ADY_CF_PRO,3_OMS_CF_PRO'")
No records returned by *SELECT(%C_FA%, "ID", "P_CC", "REGION='' AND DPCODE='FA' AND LEVEL='3'")

How can we run this logic only for 1 Cost Center at time, but select in package several cost centers?

For example for first run logic only with 3_ADY_CF_PRO, and Next for 3_OMS_CF_PRO and so on

Best regards

iaroslav

[JohnL]

Hi Iaroslav,

You won't be able to run this logic for more than one CostCenter because your *SELECT statements are setup for only 1 unique value.

*SELECT(%C_R%, "REGION", "P_CC", "ID='%P_CC_SET%'")

This statement gets executed for the above *SELECT in SQL: SELECT REGION from mbrP_CC WHERE ID = '%P_CC_SET%'

*SELECT(%C_FA%, "ID", "P_CC", "REGION='%C_R%' AND DPCODE='FA' AND LEVEL='3'")

This statement gets executed for the above *SELECT in SQL: SELECT ID from mbrP_CC WHERE REGION='%C_R%' AND DPCODE='FA' AND LEVEL='3'

You could try making the values a property of the P_CC dimension so you can use this statement:

*REC(P_CC = P_CC.C_FA) (C_FA is a property of the P_CC dimension holding the dimension member values you want.

There is also an undocumented variable called %DIMNAME_SQLSET% which returns a list of dim members enclosed in single quotes.

So you can try this:

*SELECT(%C_R%, "REGION", "P_CC", "ID IN (%P_CC_SQLSET%)")

However, your second *SELECT statement will not work because of this statement:

REGION='%C_R%

HTH,

John