Skip to Content
0

NULL values in a Rec statement with When/EndWhen

Mar 17, 2017 at 09:13 AM

453

avatar image

Hi all.

I've been through the whole internet trying to find ANYTHING that can explain exactly how to use XDIM_SKIPNULLCHECK, FOR/NEXT with ISEMPTY or ANYWAY to be able to use script logic to skip the null check.

I've tried

XDIM_SKIPNULLCHECK with ALL, CURRENT, with and without [] and also ISEMPTY.

But the code still skips all blanks.

We are not allowed to use ABAP at the client - Policy issues.

And the scope is VERY specific so I can't use IIF.

What we are trying to achieve is to lookup a value, then XDIM specific value and then in the rec we have to %VALUE%-LOOKUP and save to specific Audittrail member. But if %VALUE% is NULL, it skips. We need it to then do 0-LOOKUP.

How should I use the above options?

Is there ANY documentation other than Note 2146749 that explains how and where to use those 2?

I've tried:

*Select(...

*XDIM_SKIPNULLCHECK [ALL/CURRENT]

*XDIM_MEMBERSET...

*WHEN....

I've even tried it just before the WHEN and even just before the rec.

The *FOR %I% = %ISEMPTY& if tried in about 23 different ways.

I might be missing something or I'm just and "id10t" but at this stage it does not seem like those SAP solutions work.

Oh and REF_MASTERDATA takes WAY to long...Takes about 45min for one month and we will need to run this code almost daily for different areas and for 5 years at a time in 10+ different script logics.

Any help would be AWESOME!

Thanks!

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Best Answer
Vadim Kalinin Mar 18, 2017 at 05:34 PM
0

If both are parents on the same level then the script will be:

*WHEN ACCOUNT.ACCTYPE
*IS=INC //strange - if you scope accounts why do you need to check for INC?
*WHEN AUDITTRAIL
*IS BAS(AUD_TOT)
*REC(EXPRESSION=%VALUE%,AUDITTRAIL=AUD_9300)
*IS BAS(AUD_GT)
*REC(EXPRESSION=-%VALUE%,AUDITTRAIL=AUD_9300)
*ENDWHEN
*ENDWHEN

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Hi

That script still does not work.

Here is the info you requested:

  1. BPC 10.1 Classic NW, BW
    1. SAP_BW7400015SAPKW74015SAP Business Warehouse
  2. Current engine is ABAP
  3. ACCOUNT

    ANALYSIS

    AUDITTRAIL

    CATEGORY

    ENTITY

    FLOW

    INTERCO

    RPTCURRENCY

    SCOPE

    TIME

  4. It is one of about 5 packages that runs every month to either do spread on LC or other currencies – thus the lookup(s)
  5. We HAVE to lookup FTPRV.There MUST be a value on FTPRV and then the %VALUE%, if its empty or not MUST be %VALUE%-LOOKUP.Obviously if %VALUE% and Lookup is empty or zero, then nothing will happen.But if there is a value on Entity, Account (as scripted) and AUD_GT, then it must be deducted from same Entity, Account AUD_TOT and saved to AUD_9300.
  6. As ablove
  7. For Actuals, every month will be selected, for Budget, up to 5 years at a time
Thus we need to find an alternative to WHEN_REF_DATA = MASTER_DATA. There is multiple dimensions with more than 5000 members which cause the REF MASTERDATA to run for hours.

Thanks for the help

dcm0d.png (24.1 kB)
0

For sure it will not work because AUD_TOT is a children of AUD_GT!

But I do not understand the calculation logic based on your script:

For each member in BAS(AUD_TOT) =%VALUE%

You want to subtract total:

%VALUE%-[AUDITTRAIL].[AUD_GT]

And store the result in AUD_9300

It will be equal to:

Let's N = number of base members in BAS(AUD_TOT)

[AUDITTRAIL].[AUD_TOT] - N * [AUDITTRAIL].[AUD_GT] = [AUDITTRAIL].[AUD_TOT] - N * ([AUDITTRAIL].[AUD_TOT]+[AUDITTRAIL].[AUD_9200]+[AUDITTRAIL].[AUD_8400])

Is it correct? What do you want to achieve????

0
Vadim Kalinin Mar 17, 2017 at 09:29 AM
0

"What we are trying to achieve is to lookup a value, then XDIM specific value and then in the rec we have to %VALUE%-LOOKUP and save to specific Audittrail member. But if %VALUE% is NULL, it skips." not 100% clear what do you want to achieve?? Can you explain a business case?

WHEN/ENDWHEN loop will scope only not empty records... (by default *WHEN_REF_DATA = TRANS_DATA)

To my mind in your case instead of LOOKUP pull method it's better to use push!

If you lookup a different model use *DESTINATION_APP

And you can combine push and pull - anyway it will be faster then *WHEN_REF_DATA = MASTER_DATA

P.S. *XDIM_SKIPNULLCHECK has no relation to transaction data values! https://launchpad.support.sap.com/#/notes/2146749/E

Show 2 Share
10 |10000 characters needed characters left characters exceeded

P.P.S. In order to use push from the different model please read about RUNLOGIC_PH badi...

"We are not allowed to use ABAP at the client - Policy issues." - absolutely unacceptable!

1

Hi Vadim

We've tried push but then the results are not correct. Not sure why...

What we try to achieve is this:

We have AUDITTRAIL dimension:

Total

Group1

Group 1.1

...Base of 1.1

Group 1.2

...Base of 1.2

Group 1.3

...Base of 1.3

Group 2

Group 2.1

...Base of 2.1

Member X (Base members under Total)

We lookup Group 1 of members then we scope XDIM_MEMBERSET BAS(Group2) (this will be %VALUE%)

Then we do the WHEN etc and REC looks like this:

REC(EXPRESSION=%VALUE%-LOOKUP(Group1),AUDITTRAIL=Member X)

BUT there is a few intersections that has value in Group1 but is BLANK(Null) in Group 2.

This must also go to member X but then obviously as blank - group 2 = -group2 in member

Group 1 is the "driving" group. Everywhere that Group 1 has values, Member x MUST be Group2 - Group1

So this works for all members that has values in group 2 as well as group 1.

If we try a push there is other values and following scripts that fail.

So I just need some way to read masterdata other than REF_MASTERDATA because that takes to long (as mentioned)

I hope this explanation makes sense.

0
Vadim Kalinin Mar 17, 2017 at 12:11 PM
0

"We've tried push but then the results are not correct. Not sure why" - Sorry, but you have to clearly explain the case, including full info about models, dimensions, members etc...

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

With the current description I can't help you!

"So I just need some way to read masterdata other than REF_MASTERDATA because that takes to long (as mentioned)" - There is NO way!

Use push!

Show 5 Share
10 |10000 characters needed characters left characters exceeded

HI.

With this statement:

"So I just need some way to read masterdata other than REF_MASTERDATA because that takes to long (as mentioned)" - There is NO way!

Are you saying that there is no other way than REF_MASTERDATA?

I will try and create a better explanation.

But the people who wrote the original script logic told me that they weren't able to successfully to a push. I inherreted this code to try and fix asap but the proposed statements I've found - SKIPNULLCHECK and FOR %I% = %ISEMPTY% I can't get to work so I came here.

Thanks for the replies. Will try and explain.

This is BPC 10.1 Classic on NW

Here is the script in the meantime this is in the GROUP_DIM. So no Destination App etc:

*LOOKUP GROUP_DIM

*DIM FTPRV: ACCOUNT =ACCOUNT.ID
*DIM FTPRV: ANALYSIS = NO_ANALYSIS
*DIM FTPRV: AUDITTRAIL=AUD_GT
*DIM FTPRV: CATEGORY = %CATEGORY_SET%
*DIM FTPRV: ENTITY=ENTITY.ID
*DIM FTPRV: FLOW = F_NOFLOW
*DIM FTPRV: INTERCO = NO_INTC
*DIM FTPRV: RPTCURRENCY = RPTCURRENCY.ID
*DIM FTPRV: SCOPE = SCOPE.ID
*DIM FTPRV: TIME =%TIME_SET%

*ENDLOOKUP

*XDIM_SKIPNULLCHECK CURRENT
//Scoping the Value
*XDIM_MEMBERSET ACCOUNT =,ACCOUN1, ACCOUNT2,ACCOUNT3,ACCOUNT4,ACCOUNT5,ACCOUNT6,ACCOUNT7
*XDIM_MEMBERSET AUDITTRAIL = BAS(AUD_TOT)
*XDIM_MEMBERSET FLOW = F_NOFLOW
*XDIM_MEMBERSET ANALYSIS = NO_ANALYSIS
*XDIM_MEMBERSET INTERCO = NO_INTC
*XDIM_MEMBERSET SCOPE = NO_SCOPE,NBL,NGL
*XDIM_MEMBERSET RPTCURRENCY = LC,ZAR
*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%
*XDIM_MEMBERSET TIME = %TIME_SET%
*XDIM_MEMBERSET ENTITY = <all>


*WHEN ENTITY.CALC
*IS=N
*WHEN ACCOUNT.ACCTYPE
*IS=INC

*REC(EXPRESSION=%VALUE%-LOOKUP(FTPRV),AUDITTRAIL=AUD_9300)

*ENDWHEN
*ENDWHEN

*COMMIT


// Scoping the Value

*XDIM_MEMBERSET ACCOUNT =,ACCOUN1, ACCOUNT2,ACCOUNT3,ACCOUNT4,ACCOUNT5,ACCOUNT6,ACCOUNT7
*XDIM_MEMBERSET AUDITTRAIL = AUD_9300
*XDIM_MEMBERSET FLOW = F_NOFLOW
*XDIM_MEMBERSET ANALYSIS = NO_ANALYSIS
*XDIM_MEMBERSET INTERCO = NO_INTC
*XDIM_MEMBERSET SCOPE = NO_SCOPE,NBL,NGL
*XDIM_MEMBERSET RPTCURRENCY = LC,ZAR
*XDIM_MEMBERSET CATEGORY = ACT
*XDIM_MEMBERSET TIME = %TIME_SET%
*XDIM_MEMBERSET ENTITY = <all>

*WHEN ENTITY.CALC
*IS=N
*WHEN ACCOUNT.ACCTYPE
*IS=INC
*REC(EXPRESSION=%VALUE%-LOOKUP(FTPRV),AUDITTRAIL=AUD_9300)
*ENDWHEN
*ENDWHEN
*COMMIT

0

Can you provide info about AUD_GT

Is it a base member or parent?

BAS(AUD_TOT) include AUD_GT???

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


0

Hi

Both those are parents yes. It is groups that is in same H-level.

0

Not clear:

SOME_PARENT

...AUD_TOT

......SOMECHILDREN1

......SOMECHILDREN2

...AUD_GT

......SOMECHILDREN3

......SOMECHILDREN4

?

Screenshot of the dimension hierarchy will be better..

0

"But the people who wrote the original script logic told me that they weren't able to successfully to a push" - wrong people :)

Absolutely incorrect script!

0
Vadim Kalinin Mar 17, 2017 at 03:11 PM
0

The correct script will something like:

*WHEN ACCOUNT.ACCTYPE
*IS=INC
*WHEN AUDITTRAIL
*IS BAS(AUD_TOT)
*REC(EXPRESSION=%VALUE%,AUDITTRAIL=AUD_9300)
*IS AUD_GT //AUD_GT is not in BAS(AUD_TOT)
*REC(EXPRESSION=-%VALUE%,AUDITTRAIL=AUD_9300)
*ENDWHEN
*ENDWHEN

Or... I need more info about AUDITTRAIL dimension and the calculation logic you want to achieve!

Share
10 |10000 characters needed characters left characters exceeded
Johan Fourie Mar 20, 2017 at 08:56 AM
0

Hi. This is what was given to me just now to explain:

Hope this makes sense.

Scenario1 is where the script "skips" the calc


slsco.png (146.8 kB)
x6vbo.png (150.1 kB)
Show 7 Share
10 |10000 characters needed characters left characters exceeded

OK, not 100% clear but looks like by AUD_1...AUD_30 you mean BAS(AUD_TOT)?

Are you absolutely sure that -19261 is the required result???

[AUDITTRAIL].[AUD_TOT] - N * [AUDITTRAIL].[AUD_GT]=1087 - 4 * 5087 = -19261

What is the business value of this amount? Please ask again your customer!

You can see it's dramatically affected by number of base members under AUD_TOT

0

Yes. AUD_1 - AUD_30 are all base members for AUD_TOT.

This is what they gave me yes and that 19261 we get every time as that is when AUD_TOT is not blank.

The -4000 is what we don't get (BLANK - AUD_GT)

There is 4 or 5 different spreads. All of which has same logic but different lookups and currencies. This is just the one they gave me 1st but if this is sorted, same principles will be used. I'll try and get more info from customer.

Thanks for the help

0

To my mind the logic of this formula is incorrect!

I can't imaging the business value of the amount calculated using this formula depending on number of non empty base members under AUD_TOT. And you can also see that the impact of [AUD_TOT] is depending on number of not empty members:

AUD_TOT=100

AUD_GT=1000

first case:

Only AUD_1 =100

Result: 100-1*1000=-900

second case:

AUD_1...AUD_10=10 (10 members)

Result: 100-10*1000=-9900

May be instead of formulas you can ask the customer what he want to achieve at the end?

1

Hi.

I changed the script and the order and got it to work.

My script more or less looks like what you recommended:

LOOKUP(AUD_TOT)

...

LOOKUP(AUD_GT)

...

*XDIM_MEMBERSET ACCOUNT =,ACCOUN1, ACCOUNT2,ACCOUNT3,ACCOUNT4,ACCOUNT5,ACCOUNT6,ACCOUNT7

*XDIM_MEMBERSET FLOW = F_NOFLOW
*XDIM_MEMBERSET ANALYSIS = NO_ANALYSIS
*XDIM_MEMBERSET INTERCO = NO_INTC
*XDIM_MEMBERSET SCOPE = NO_SCOPE,NBL,NGL
*XDIM_MEMBERSET RPTCURRENCY = LC,ZAR
*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%
*XDIM_MEMBERSET TIME = %TIME_SET%
*XDIM_MEMBERSET ENTITY = <all>

*WHEN ENTITY.CALC
*IS = N
*WHEN ACCOUNT.ACCTYPE
*IS = INC
*WHEN AUDITTRAIL
*IS AUD_9200,AUD_8400//Only members that falls outside AUD_TOT which will solve the empty member issue

*REC(EXPRESSION = 0-%VALUE%,AUDITTRAIL = AUD_9300)

*WHEN AUDITTRAIL
*IS BAS(AUD_TOT)//Where the empty issue is. Above step eliminates that issue

*REC(EXPRESSION = %VALUE%-LOOKUP(AUD_GT),AUDITTRAIL = AUD_9300)

*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN

*COMMIT

Thanks

0

Still the logic is strange :)

And why do you use strange syntax:

*REC(EXPRESSION = 0-%VALUE%,AUDITTRAIL = AUD_9300) // "0-" what for???

Simply use:

*REC(EXPRESSION = -%VALUE%,AUDITTRAIL = AUD_9300)

Also absolutely meaningless:

*WHEN ENTITY.CALC
*IS = N

WHEH/ENDWHEN loops ONLY base members!

Also COMMIT is absolutely useless!

0
Yea I know :)

But as mentioned I got this from those (wrong) people. I didn't changed those sections (.CALC = N etc...)

They had the empty issue which I was asked to try and fix.

I will look at those strange CALC = N sections next when they are happy with the results of my change.

There is 8 scripts that is similar with similar issues so will make all changes at same time.

But thanks for the help and tips

0

If you are changing some script then it's your script :)

And the line:

*WHEN ENTITY.CALC
*IS = N

Means that the person responcible for the script understand NOTHING in script logic!

1