BPC 10.1 script logic reclass

[srikanth_ramachandran]

Hello,

I'm trying to create a same account reclass (across line of business) and attempting to do it all via script logic.

Here is the text of my script -

*WHEN ACCOUNT
*IS *
*WHEN LINEOFBUSINESS
*IS <> ABAGF
*REC(FACTOR=-1)
*REC(FACTOR=1, LINEOFBUSINESS=ABAGF)

*ENDWHEN
*ENDWHEN

Where ACCOUNT and LINEOFBUSINESS are the two elements. However, what seems to be happening is the reversal (first REC line) is being done for twice the amount of that record. I'm still ascertaining how accurate the offset line is. (since it is being summed up by LINEOFBUSINESS ABAGF, it is harder to validate)

Any pointers on what is wrong with my lgf? Thanks.

Sri

[former_member186338 · Accepted solution]

Some minor changes:

*XDIM_MEMBERSET CATEGORY=Actual
*SELECT (%ACCT%,ID,ACCOUNT,ACCTYPE=AST AND ACCTYPE=LEQ AND FLOAT_ACCT=Y AND CALC=N) //Changed
*XDIM_MEMBERSET ACCOUNT = %ACCT%
*SELECT (%LOBNOT%,ID,LOB,ID<>ABAGF AND CALC=N) //Added
*XDIM_MEMBERSET LOB = %LOBNOT% //Added
*XDIM_MEMBERSET FLOW=F_CLO
*XDIM_MEMBERSET RPTCURRENCY=USD
*XDIM_MEMBERSET TP_LOB=<ALL>
*XDIM_MEMBERSET INTERCO=<ALL>
*XDIM_MEMBERSET AUDITTRAIL=<ALL>
//*XDIM_MEMBERSET TIME=%TIME_SET% - useless
//*XDIM_MEMBERSET SCOPE=%SCOPE_SET% - useless

*WHEN LOB 
*IS * //Changed
*REC(FACTOR=-1)
*REC(FACTOR=1, LOB=ABAGF)
*ENDWHEN

This line

*SELECT (%LOBNOT%,ID,LOB,ID<>ABAGF AND CALC=N) //Added

is critical

[srikanth_ramachandran · Accepted solution]

Thank you Vadim - this is helpful. For my understanding - does the LOB select statement filter the result set from the first select or does it simply work as another selection parameter and run a common query on the BPC cube?

Also, I should have mentioned that the SCOPE and TIME are selection values entered by the user, hence those statements. So I guess I would still need those?

[former_member186338]

Sorry, but may be next time the customer will ask you to make: 2*2=5??? Using special audittrail is the correct and recommended approach. And it's your job to explain him.

Badi is also a wrong approach.

In general, if you want to add - you have to use audittrail to be able to perform reclass multiple times.

[srikanth_ramachandran]

Ha ha - but yeah I get it. I have explained my head off 🙂 You would not believe how unintelligent some powerful folks are. Not going to write a BADI for this one.

[former_member186338]

I can believe! I have seen a lot of absolutely stupid requests... And was able to to explain why 🙂

[former_member186338]

If you want to add, not overwrite the target value simply use special audittrail id for target result.

[srikanth_ramachandran]

Thanks Vadim - that was my first pass but have a stubborn customer who wants to use the original audittrail ! Hence my question. I was trying to avoid a BADI but if I need to use the original audittrail it looks like I may need one.

[former_member186338]

"Just curious - was the amount getting doubled with the incorrect script due to the ACCOUNT loop?" - no, due to parent members of LOB were written to target ABAGF

"Also, if I needed to introduce another dimension, say TP_LOB - into the reclass logic; for example I need to do the reclass only when LOB <> ABAGF and TP_LOB <> TP_ABAGF" - use same approach, SELECT required TP_LOB! And use single WHEN/ENDWHEN loop!

[srikanth_ramachandran]

Hi Vadim,

I did exactly that and code is fine - except there is some doubling/inconsistency with the result postings. I figured out the problem:

*REC(FACTOR=-1)
*REC(FACTOR=1, LOB=EUKAC, TP_LOB=TP_EUKAC)

The first line above I need to replace with FACTOR = 0. That is straightforward.

The issue with second line is more complex. Basically there already exists a signeddata value for that record combination in the cube; hence if the amount arrived at in the above statement is $400 (for example), and if there is already a value in the cube for that combination (say $1000), then the write is for -$600. Which is causing incorrect results obviously.

Is there a simple way around this? I think I may be missing something. Or do we need to read the balance in that record first and add it to the value to be reclassed - e.g. FACTOR=%VALUE%+xxx where xxx is the signeddata from the cube record. This would need to be done for every record within the WHEN loop?

*WHEN LOB
*IS EUKAC
*WHEN TP_LOB
*IS TP_EUKAC
*ELSE
*REC(FACTOR=-1)
*REC(FACTOR=1, TP_LOB=TP_EUKAC)
*ENDWHEN
*ELSE
*REC(FACTOR=-1)
*REC(FACTOR=1, LOB=EUKAC, TP_LOB=TP_EUKAC)
*ENDWHEN

Any help is appreciated! Thanks.

Sri

[former_member186338]

I am talking about standard BPC help. Also search for SAP notes related to the subject.

And it's not a bad idea to read my blogs related to script logic 🙂

[srikanth_ramachandran]

Will do. I'm sure to have more questions after reading your blogs!

Just curious - was the amount getting doubled with the incorrect script due to the ACCOUNT loop?

Also, if I needed to introduce another dimension, say TP_LOB - into the reclass logic; for example I need to do the reclass only when LOB <> ABAGF and

TP_LOB <> TP_ABAGF

would I just not use the SELECT statement for LOB but use a nested WHEN instead?

[former_member186338]

"And I get that we are excluding parents but my question was does the LOB select happen after the query retrieves data based on first select or is it just an added condition on the only query that is executed?" - absolutely not clear, what are you talking about!

*SELECT statement is used to get a list of dimension members and place this list in variable. No relation to data. Please read help!

.

"The selection for SCOPE and TIME is because the reclass should only be done for a specific period and specific scope values, on demand .. so my question on that."

When user select some members in DM prompt (for variable %SELECTION%) then the initial scope of the script will be set to this selection automatically.

*XDIM_MEMBERSET TIME=%TIME_SET%- useless

already set to this scope!

[srikanth_ramachandran]

Okay got it. When you say read help - can you point me to any good help documentation? Will help you too to not have to answer anything and everything 🙂

Thanks Vadim.

[former_member186338]

First - please accept the correct answer!

Second: you need to exclude from scope LOB parent members - to avoid writing of parent to base ABAGF (Parent members are in scope since some BPC SP)

"Also, I should have mentioned that the SCOPE and TIME are selection values entered by the user, hence those statements. So I guess I would still need those?" - no, will be automatically scoped.

[srikanth_ramachandran]

OK, accepted ..

And I get that we are excluding parents but my question was does the LOB select happen after the query retrieves data based on first select or is it just an added condition on the only query that is executed?

The selection for SCOPE and TIME is because the reclass should only be done for a specific period and specific scope values, on demand .. so my question on that.

Thanks.

[former_member186338]

Please read my blog: https://blogs.sap.com/2014/01/31/how-to-ask-questions-about-script-logic-issues/

and provide missing info, required to answer your question.

P.S. The loop for ACCOUNT is useless!

[srikanth_ramachandran]

We are on BW 7.5 SP 8, BPC 10.1.

Calc engine is Javascript.

Execution via DM package.

I am new to script logic so if you can tell me where I am going wrong that will be useful. Thanks.

Here is the script: (logic is to filter all accounts that meet criteria as below and then create a reclass within each account across the LOB dim)

*XDIM_MEMBERSET CATEGORY=Actual
*SELECT (%ACCT%, ID, ACCOUNT, " ACCTYPE = 'AST' AND ACCTYPE = 'LEQ' AND FLOAT_ACCT = 'Y' " )
*XDIM_MEMBERSET ACCOUNT = %ACCT%
*XDIM_MEMBERSET FLOW=F_CLO
*XDIM_MEMBERSET RPTCURRENCY=USD
*XDIM_MEMBERSET TP_LOB=<ALL>
*XDIM_MEMBERSET INTERCO=<ALL>
*XDIM_MEMBERSET AUDITTRAIL=<ALL>
*XDIM_MEMBERSET TIME=%TIME_SET%
*XDIM_MEMBERSET SCOPE=%SCOPE_SET%

*WHEN ACCOUNT
*IS *
*WHEN LOB
*IS <> ABAGF
*REC(FACTOR=-1)
*REC(FACTOR=1, LOB=ABAGF)

*ENDWHEN
*ENDWHEN

*COMMIT