Script Logic - REC into a dimension property dinamically

[Former Member]

Dear Experts,

I write to you to ask if you can share your knowledge regarding how to solve a little problem in Script Logic. Let me state the case..

I have a model where two dimensions should be "linked" (COSTCENTER ENTITY). The relationship of each is somewhat as follows:

COSTCENTER n -- 1 ENTITY

Each COSTCENTER has a property (ENT) which states what ENTITY it is part of.

I have a situation where I need to allocate a set of COSTCENTER of a single SEGMENT into different COSTCENTERs and ENTITYs. Is there a way to obtain dinamicaly the ENTITY from a COSTCENTER when I write back into a REC statement? I had the following idea but it is not working...

*FOR %CC% = %COSTCENTERS%

*REC(FACTOR = SomeDriver, COSTCENTER = %CC%, SEGMENT = [COSTCENTER].[%CC%].Property("ENT"))

*NEXT

Is there a way to achieve this?

Thank you all for your time and help!

Best regards,

Joaquin.

[former_member186338 · Accepted solution]

And by the way, can you explain the business logic of what you want to achieve? Not the script logic code, but some sample with figures and result

Vadim

[Former Member · Accepted solution]

BPC 10.1 NW SP4, Java Engine

We have a "Gastos" model with the following dimensions:

- CATEGORY (C)

- CEBE (U)

- CUENTAS (A)

- G_AUDITTRAIL (D)

- INTERCO (I)

- RPTCURRENCY (R)

- SEGMENTO (U)

- SOCIEDAD (E)

- TIME (T)

In the model, three dimensions (CEBE, SEGMENTO and SOCIEDAD) are "linked".

The relationship of each is somewhat as follows:

CEBE n -- 1 SEGMENTO n -- 1 ENTITY

Each CEBE (7 dig) has a property (SEG) which states what SEGMENTO (4 dig) it is part of, and the same is done between SEGMENTO and SOCIEDAD (4 dig) through "SOC" property in SEGMENTO dimension. The CEBE ID is composed by a 7 digit code, where the first four are the same as the corresponding SEGMENTO and the last 3 are variable.

In the CUENTA dimension I have all the drivers required for each cycle (ZDISTR_C1, ZDISTR_C2, etc) and the data is registered in each destination SEGMENTO and TIME (CEBE = NA and SOCIEDAD = NA).

I want to perform a allocation through DM Package from a source from a set of CEBEs in SEGMENTO 8001 and SOCIEDAD 8000 to differents CEBEs in different SEGMENTOs and SOCIEDADs and into one CUENTA for each cycle of the allocation process.

For example, I have the following source data:

G_AUDITTRAIL	SOCIEDAD	SEGMENTO	CEBE	CUENTAS	TIME	VALUE
OFCInput	8000	8001	8000910	820010	2016.01	2000
OFCInput	8000	8001	8000911	820020	2016.01	2000
OFCInput	8000	8001	8000912	820030	2016.01	2000

And the following drivers:

G_AUDITTRAIL	SOCIEDAD	SEGMENTO	CEBE	CUENTAS	TIME	VALUE
Metricas	NA	1101	NA	ZDISTR_C1	2016.01	0.2
Metricas	NA	1102	NA	ZDISTR_C1	2016.01	0.3
Metricas	NA	1201	NA	ZDISTR_C1	2016.01	0.5

The logic I want to apply is to obtain the total amount to allocate from the source scope (6000 in this case) and allocate it in the target CEBES by SEGMENTO. The general idea is:

(Allocated Data by Segment) = (Total Source Data in Segment 8001) * (Allocation driver by segment)

That way I need to get the following results:

G_AUDITTRAIL	SOCIEDAD	SEGMENTO	CEBE	CUENTAS	TIME	VALUE
DistrPropio	1100	1101	1101651	8200101	2016.01	1200
DistrPropio	1100	1102	1102651	8200101	2016.01	1800
DistrPropio	1201	1201	1201651	8200101	2016.01	3000

To do that I developed the following code.

// ** Target SEGMENTOs
*SELECT(%SEGS_OP%, ID, CEBE, OP = "OP" AND ID <> "1001")

*FUNCTION C_FACTDISTR_OFC(%Factor%,%Segmento%) = ([CUENTAS].[%Factor%],[SEGMENTO].[%Segmento%],[SOCIEDAD].[NA],[RPTCURRENCY].[NA],[G_AUDITTRAIL].[Metricas],[CEBE].[NA])

*XDIM_MEMBERSET TIME = %TIME_SET%
*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%

*XDIM_MEMBERSET CEBE = BAS(BPC_OF8001)
*XDIM_MEMBERSET G_AUDITTRAIL = BAS(OFC) // Source Audittrails with data to allocate.
*XDIM_MEMBERSET INTERCO = I_NA
*XDIM_MEMBERSET RPTCURRENCY = UF

*WHEN CEBE.DISTR
*IS "C1"

*FOR %SEG_OP% = %SEGS_OP%

*REC(FACTOR = C_FACTDISTR_OFC(ZDISTR_C1,%SEG_OP%), SOCIEDAD = [SEGMENTO].[%SEG_OP%].Properties("SOC"), CEBE = %SEG_OP%651, CUENTAS = 8200101, G_AUDITTRAIL = DistrPropio)

*NEXT

*ENDWHEN

The UJKT log says that the code marked in red is incorrect so I'm looking how to solve the problem in another way...

In your repy you wrote that to do what I need I can call a property from the current member of any dimension, but the thing is that starting from the source data I don't have the target SEGMENTOs as a CURRENTMEMBER so I can't obtain SOCIEDAD by calling "SOCIEDAD = SEGMENTO.SOC". I've tried to solve it that way and I have a on-going thread for that matter with other problems.

Any ideas would be welcome! Surely there's a way to solve this in Script Logic (I hope so! As I'm not ABAP/BAdI proficient...).

Thanks again for your time.

Best regards,

Joaquin.

[Former Member · Accepted solution]

Just to let you know...

I've tried a different approach without good results...

CODE:

// ** Target SEGMENTO selection.
*SELECT(%SEGS_OP%, ID, SEGMENTO, OP = "OP" AND ID <> "1001")

// ** Source CEBE for each allocation cycle.
*SELECT(%CEBESOFC_C1%, ID, CEBE, DISTR = "C1")
*SELECT(%CEBESOFC_C2%, ID, CEBE, DISTR = "C2")
*SELECT(%CEBESOFC_C3%, ID, CEBE, DISTR = "C3")
*SELECT(%CEBESOFC_C4%, ID, CEBE, DISTR = "C4")
*SELECT(%CEBESOFC_C5%, ID, CEBE, DISTR = "C5")
*SELECT(%CEBESOFC_C6%, ID, CEBE, DISTR = "C6")

*XDIM_MEMBERSET TIME = %TIME_SET%
*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%

*XDIM_MEMBERSET CEBE = NA
*XDIM_MEMBERSET G_AUDITTRAIL = Metricas
*XDIM_MEMBERSET INTERCO = I_NA
*XDIM_MEMBERSET RPTCURRENCY = NA
*XDIM_MEMBERSET CUENTAS = BAS(ZDISTR_OFC)

*WHEN SEGMENTO
*IS %SEGS_OP%

*WHEN CUENTAS
*IS ZDISTR_C1

*FOR %CEBE_C1% = %CEBESOFC_C1%

*REC(FACTOR = ([SOCIEDAD].[8000],[SEGMENTO].[8001],[CEBE].[%CEBE_C1%],[CUENTAS].[BP_Utilidad],[G_AUDITTRAIL].[OFC],[RPTCURRENCY].[UF]), CEBE = SEGMENTO.ID"651", CUENTAS = 8200101, G_AUDITTRAIL = DistrPropio)

*NEXT

*ENDWHEN

*ENDWHEN

UJKT LOG:

LGX:


-------------------------------------------------------------------------------------------------------------------------------------
LOG:

LOG BEGIN TIME:2015-05-13 14:56:49
FILE:\ROOT\WEBFOLDERS\xxx \ADMINAPP\Gastos\TEST.LGF
USER:CONEXBPC2
APPSET:xxx
APPLICATION:Gastos
[INFO] GET_DIM_LIST(): I_APPL_ID="Gastos", #dimensions=10
CATEGORY,CEBE,CUENTAS,G_AUDITTRAIL,INTERCO,MEASURES,RPTCURRENCY,SEGMENTO,SOCIEDAD,TIME

#dim_memberset=7
TIME:2016.01,1 in total.
CATEGORY:Ppto_T,1 in total.
CEBE:NA,1 in total.
G_AUDITTRAIL:Metricas,1 in total.
INTERCO:I_NA,1 in total.
RPTCURRENCY:NA,1 in total.
CUENTAS:ZDISTR_C1,ZDISTR_C2,ZDISTR_C3,ZDISTR_C4,ZDISTR_C5,...10 in total.

ERROR LOG:
CLASS: CL_UJK_CALC_ENGINE
METHOD: PROCESS_FORMULA
TCODE: UJKT
ATTRIBUTE "ID651" NOT EXIST IN DIMENSION "SEGMENTO"
LOG END TIME:2015-05-13 14:56:49

UJK_VALIDATION_EXCEPTION:El atributo "ID651" no existe en la dimensión "SEGMENTO"

Any other idea?

Thanks again.

Best regards,

Joaquin.

[former_member186338 · Accepted solution]

But why do you think that this strange syntax will work:

CEBE = SEGMENTO.ID"651"

Please read help about script logic statements - the is no magic syntax, everything is very limited!!!

Vadim

[former_member186338 · Accepted solution]

Hi Joaquin,

I do not understand Spanish and I can't understand the business logic! Stop talking about model dimensions etc... Explain business value of figures.

Vadim

[Former Member · Accepted solution]

Hi Vadim,

I know that it's not working but I'm looking for ways to achieve my requirement.You've said that I can call a property from a current member into the target-rec statement and that's what I've tried. Calling SEGMENTO.ID worked well but I need to concatenate (is the word correct?) with the las three digits to form a correct ID for CEBE dimension. I've read a lot of documentation and still can't find a way to solve it... I tried another approach but I'll be opening an OSS note on that as I'm having a few dificulties with some FOR loops involved as you've noticed .

Best regards,

Joaquin.

PS: Yeah, it surely is limited! I'm very surprised how hard it is to achieve certain things that look simple on paper. It looks like I'll have to learn some ABAP/BAdI, but unfortunately I don't have a lot of time to learn and implement it properly without any experience in it and the involved due dates.

[Former Member · Accepted solution]

Hi Vadim,

Sorry, I tried to explain it as simple as I could. Looks like it wasn't good enough!

As I've said, I have a situation where certain expenses are registered at the central office and after allocated into the different offices (SEGMENTO) as a somewhat top-down approach using a percentage driver by office. This allocation process has 5 "cycles" where each one take a different set of cost centers (always from the central office "8001") and all the accounts involved as a source and then multiply it by the allocation driver for each office (a 4 code length ID) and register it in a particular cost center by office (the 4 id code from segment plus additional three digit code to form a 7 digit cost center ID, hence the "SEGMENT.ID+651" syntax that I incorrectly tried) and a single account.

For example, for a set of cost centers in office 8001 (Cycle 1) I have a Budget of USD 6,000 for JAN 2016. Then I have the following allocation drivers by office (cost center = NA):

	ENTITY 1100	ENTITY 1100	ENTITY 1200	...
TIME	OFFICE 1101	OFFICE 1102	OFFICE 1201	...
JAN 2016	20%	30%	50%	...
FEB 2016	15%	45%	40%	...
...	......		...	...

That way, the resulting expenses that I need to obtain for january 2016 is:

		ENTITY 1100	ENTITY 1100	ENTITY 1200	...
		OFFICE 1101	OFFICE 1102	OFFICE 1201	...
ACCOUNT	TIME	COSTCENTER 1101651	COSTCENTER 1102651	COSTCENTER 1201651	...
8200101	JAN 2016	1,200	1,800	3,000	...

OFFICE 1101 = 6,000 * 20% = 1,200

OFFICE 1102 = 6,000 * 30% = 1,800

OFFICE 1201 = 6,000 * 50% = 3,000

The same is done for the other cycles with a different set of source cost center and different target cost center (last 3 digits) and account.

I hope it's clearer this way. I'll gladly give more info if needed.

Thanks again! I really appreciate your help.

Best regards,

Joaquin.

[former_member200327 · Accepted solution]

Hi Joaquin,

It looks like everything you want to achieve is very easy to do using RUNALLOCATION instead of WHEN/ENDWHEN. The only problem will be to concatenate 651 in the Cost center.

Regards,

Gersh

[former_member186338 · Accepted solution]

"Calling SEGMENTO.ID worked well but I need to concatenate (is the word correct?) with the las three digits to form a correct ID for CEBE dimension." - Absolutely not possible to concatenate this way!

Vadim

[Former Member · Accepted solution]

Hi Gersh, thanks for your answer.

I've tried using a RUNALLOCATION before but I couldn't get the target cost center dinamically, as you said, so I started trying the approaches I explained here. Reading your answer, and after a lot of trial and error from the approaches I've tried, gave me an idea in how to solve it using the following code:

// ** Target SEGMENTO selection.
*SELECT(%SEGS_OP%, ID, SEGMENTO, OP = "OP" AND ID <> "1001")

// ** Source CEBE for each allocation cycle.
*SELECT(%CEBESOFC_C1%, ID, CEBE, DISTR = "C1")
*SELECT(%CEBESOFC_C2%, ID, CEBE, DISTR = "C2")
*SELECT(%CEBESOFC_C3%, ID, CEBE, DISTR = "C3")
*SELECT(%CEBESOFC_C4%, ID, CEBE, DISTR = "C4")
*SELECT(%CEBESOFC_C5%, ID, CEBE, DISTR = "C5")
*SELECT(%CEBESOFC_C6%, ID, CEBE, DISTR = "C6")

*XDIM_MEMBERSET TIME = %TIME_SET%
*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%

*FOR %SEG_OP% = %SEGS_OP%

*RUNALLOCATION
*FACTOR = USING
*DIM SOCIEDAD WHAT = 8000; WHERE = BAS(TotalSociedad); USING = <<<
*DIM SEGMENTO WHAT = 8001; WHERE = %SEG_OP%; USING = <<<
*DIM CEBE WHAT = %CEBESOFC_C1%; WHERE = %SEG_OP%651; USING = NA
*DIM CUENTAS WHAT = BAS(BP_Utilidad); WHERE = 8200101; USING = ZDISTR_C1
*DIM RPTCURRENCY WHAT = UF; WHERE = UF; USING = NA
*DIM G_AUDITTRAIL WHAT = BAS(OFC); WHERE = DistrPropio; USING = Metricas
*ENDALLOCATION

*NEXT

So far, at least for the first cycle, it's working like a charm! It was pretty simple and straight forward looking at it now. Thank you!

I just want to ask now for guidance about the optimal way of using the RUNALLOCATION sentence and the implication in performance for using it nested inside a FOR loop. Is there a way to achieve my requirement without a FOR loop? I ask this because, as far as my testing goes with my current (very little) set of data, I get the following execution times:

- Without FOR loop, can't get the target Cost Center dinamically: 1 second

- With FOR loop, I get desired results: 7 seconds

As you see, the execution time improves greatly if don't use the FOR loop but I'm still looking for a way to get the target cost center. Any other idea is welcomed!

Thanks a lot! I really appreciate the help from all of you.

Best regards,

Joaquin.

[former_member186338 · Accepted solution]

"Is there a way to achieve my requirement without a FOR loop?" - no, you can't! If you are not satisfied with the resulting performance - then you can always go to badi...

Vadim

P.S. "With FOR loop, I get desired results: 7 seconds" - not bad!

[Former Member · Accepted solution]

Vadim, thanks again for your reply.

7 seconds looks good but the thing is that it's just the first cycle for a very small test data set. I'll have to benchmark it later when I have a more robust scenario to see if some tune up needs to be done. I'm just looking ahead 😃

Going back to my problem (sorry to bother you guys so much), I got most of the allocations cycles covered with the solution posted above but a few are causing some trouble as there are two drivers involved:

- One driver will allocate in offices (SEGMENTO) just as the others

- The second driver will then multiply it again to store the final value in two different cost centers and accounts.

Having my previous example in mind, and adding the following driver, what I need to get is

ENTITY = NA

OFFICE = NA

COSTCENTER = NA

TIME	ACCOUNT D1	ACCOUNT D2
2016.01	40%	60%
2016.02	35%	65%
...	...	...

D1 : OFFICE 1101 = 6,000 * 20% * 40% = 480

D2 : OFFICE 1101 = 6,000 * 20% * 60% = 720

D1 : OFFICE 1102 = 6,000 * 30% * 40% = 720

D2 : OFFICE 1102 = 6,000 * 30% * 60% = 1,080

D1 : OFFICE 1201 = 6,000 * 50% * 40% = 1,200

D2 : OFFICE 1201 = 6,000 * 50% * 60% = 1,800

Having this in consideration, I think that RUNALLOCATION statement can't cover this logic and this would force me to go back to WHEN/ENDWHEN/REC logic again with all the problems I've mentioned in this thread... Maybe doing a 2 step allocation, first on ACCOUNT = and CostCenter = NA and then a second ALLOCATION using the second driver and zeroing the middle step. Do you guys have other idea that can be better than mine? It would be very helpful.

Once again, thank you! I hope this is the last thing I ask regarding this issue.

Best regards,

Joaquin.

[former_member200327 · Accepted solution]

Hi Joaquin,

Usually the best way to to avoid FOR/NEXT loop in RUNALLOCATION is to use DIM_NOAGGR. Not sure if this will work in your case because WHAT and WHERE should have same number of members.

If this doesn't work, use RINLOGIC_PH with parallelization on Dimension in FOR/NEXT loop, in your case it's CEBE.

Regards,

Gersh

[former_member186338 · Accepted solution]

"Maybe doing a 2 step allocation, first on ACCOUNT = and CostCenter = NA and then a second ALLOCATION using the second driver and zeroing the middle step." - something like this!

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Could you please give solution to my post. its urgent i have client demo and i am struck with the code form past 2 days.

http://scn.sap.com/thread/3743476

Regards,

Tej

[former_member186338]

Hi Joaquin,

If you want to write the result of rec into some member contained in the property of another member then:

The current member has to contain the required property value!

*REC(FACTOR=SOMETHING,SEGMENT=COSTCENTER.ENT)

In this sample for current COSTCENTER the ENT property will be used as destination SEGMENT.

Current - means current record  during when/endwhen loop execution!

Vadim