Skip to Content

script logic condition based on summarized source values

Hi,

I'm trying to move balances to 2 destination accounts based on whether "total balance in source acccount" is + or -. But, i'm unable to summarize totals and check value for + or -, incase source has multiple records with differnet Interco. for example, I've below data in my cube.

Entity	Interco	Account	Balance
B	I_C	A_111	100
B	I_D	A_111	-40
F	I_G	A_111	-70
F	I_H	A_111	-20

I'm trying to generate below records

comments

* if total account balance is +, move into A_112 in it's parent entity and swap INTCO (replace I_C, I_D with I_B (record entity is B so I_B))

* if total account balance is -, move into A_113 in it's parent entity and swap INTCO (replace I_G, I_H with I_F (record entity is F so I_F))

below is my entity dimension with PARENT and INTCO properties

Entity	Interco	PARENT
AA	I_AA	
B	I_B	AA
C	I_C	AA
D	I_D	AA
EE	I_EE	
F	I_F	EE
G	I_G	EE
H	I_H	EE

below is my script logic.

*FOR %ENT% = ENTITY
  *WHEN %ENT%.PARENT 
   *IS <> ""   		   
    *REC(EXPRESSION=(%VALUE%>0)? %VALUE% : 0, ENTITY = %ENT%.PARENT, INTERCO = %ENT%.INTCO, ACCOUNT = "A_112")
    *REC(EXPRESSION=(%VALUE%<0)? %VALUE% : 0, ENTITY = %ENT%.PARENT, INTERCO = %ENT%.INTCO, ACCOUNT = "A_113")					
    *REC(FACTOR=0)			
  *ENDWHEN
*NEXT

Currently, i'm getting below results as expression is checking "each record account value" instead of "total of account value".

AA	I_B	A_112	100
AA	I_B	A_113	-40
EE	I_F	A_113	-90

While I'm expecting to generate below records.

Entity	Interco	Account	Balance		
AA	I_B	A_112	 60		
EE	I_F	A_113	-90

Appreciate any help.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Oct 10, 2017 at 07:26 AM

    Please correct the format of your post - it's unreadable!

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

    and provide required info.

    Not clear what parent you are talking about? Entity? What is the hierarchy?

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 11, 2017 at 12:57 PM

    Please read the note:

    https://launchpad.support.sap.com/#/notes/2358637

    Looks like your SP11 is supported by this note!

    Then you can scope parent member for Interco:

    *XDIM_MEMBERSET Interco = TOTALINTERCO // %VALUE% will be total interco
    *WHEN ENTITY.PARENT
    *IS <>""
    *REC(EXPRESSION=(%VALUE%>0)?%VALUE%:0, ENTITY=ENTITY.PARENT, INTERCO=ENTITY.INTCO, ACCOUNT=A_112)
    ...

    Useless variable %ENT% and FOR/NEXT loop.

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 10, 2017 at 08:59 PM

    Thanks Vadim for your reply. pardon my formatting as i'm new here and getting used to changes. I've corrected and hopefully it's readable now and also added entity dimension.

    Add comment
    10|10000 characters needed characters exceeded

    • Vadim,

      sorry my bad. I've not included these earlier. Here is the info.

      SAP_BW	740	0016	SAPKW74016	SAP Business Warehouse
      CPMBPC	810	0011	SAPK-81011INCPMBPC	SAP Business Planning and Consolidation