BPC 10- Sum members conditional logic

[Former Member]

Hello Guys,

I have  a requirement where i should record values based on this condition:

IE:  if an account1 + account  2  is less than "0", we should record its value, else record "0"

I am using the following code:

*SELECT ( %PER_ATUA% , [DATABASE],VERSAO , ID = %VERSAO_SET%)

*XDIM_MEMBERSET PERIODO = %PER_ATUA%

*XDIM_MEMBERSET CONTA = BAS(ACCOUNT_1,ACCOUNT_2)

*WHEN CONTA

  *IS *

  *REC(EXPRESSION = %VALUE%, CONTA = ACCOUNT_3)

*ENDWHEN

*COMMIT

*XDIM_MEMBERSET CONTA = ACCOUNT_3

*WHEN CONTA

  *IS *

  *REC(EXPRESSION = (%VALUE% <0) ? %VALUE% : 0,CONTA = "ACCOUNT_4")

*ENDWHEN

Logs:

*XDIM_MEMBERSET PERIODO = 201508

*XDIM_MEMBERSET CONTA= BAS(CR_COMP_DED_IRPJ,CR_COMP_DED_CSLL)

*WHEN CONTA

*IS *

*REC(EXPRESSION=%VALUE%, CONTA = CR_COMP_DED_IRPJ_CSLL)

*ENDWHEN

*COMMIT

*XDIM_MEMBERSET CONTA= CR_COMP_DED_IRPJ_CSLL

*WHEN CONTA

*IS *

*REC(EXPRESSION=(%VALUE% <0) ? %VALUE% : 0,CONTA =DB.DEBITO_MENSAL)

*ENDWHEN

-------------------------------------------------------------------------------------------------------------------------------------

LOG:

LOG BEGIN TIME:2015-12-15 15:57:32

FILE:\ROOT\WEBFOLDERS\ZMAGGI_FISCAL \ADMINAPP\FISCAL\TEST.LGF

USER:ABAP

APPSET:ZMAGGI_FISCAL

APPLICATION:FISCAL

[INFO] GET_DIM_LIST(): I_APPL_ID="FISCAL", #dimensions=13

ATIVIDADE,CCUSTO,CONTA,CONTRAPARTIDA,DIVISAO,EMPRESA,FONTE,INDICADOR,MEASURES,MOEDA,PERIODO,PRODUTO,VERSAO

#dim_memberset=4

EMPRESA:0001,1 in total.

VERSAO:PLAN201508,1 in total.

PERIODO:201508,1 in total.

CONTA:CR_COMP_DED_CSLL,CR_COMP_DED_IRPJ,2 in total.

REC :%VALUE%

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 0.00 ms. 4  RECORDS QUERIED OUT.

QUERY REFERENCE DATA

CALCULATION TIME IN TOTAL :0.00 ms.

2  RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 2  RECORDS ARE GENERATED.

DATA TO WRITE BACK:

ATIVIDADE CCUSTO CONTA CONTRAPARTIDA DIVISAO EMPRESA FONTE INDICADOR MOEDA PERIODO PRODUTO VERSAO SIGNEDDATA

NAATIVIDADE NACCUSTO CR_COMP_DED_IRPJ_CSLL NACONTRAPARTIDA 0101 0001 SAPFI FIS0000001 BRL 201508 NAPRODUTO PLAN201508 89928380.64

NAATIVIDADE NACCUSTO CR_COMP_DED_IRPJ_CSLL NACONTRAPARTIDA 0101 0001 SAPFI FIS0000003 BRL 201508 NAPRODUTO PLAN201508 - 82034548.02

2  RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :0.00  ms.

[INFO] GET_DIM_LIST(): I_APPL_ID="FISCAL", #dimensions=13

ATIVIDADE,CCUSTO,CONTA,CONTRAPARTIDA,DIVISAO,EMPRESA,FONTE,INDICADOR,MEASURES,MOEDA,PERIODO,PRODUTO,VERSAO

#dim_memberset=3

EMPRESA:0001,1 in total.

VERSAO:PLAN201508,1 in total.

CONTA:CR_COMP_DED_IRPJ_CSLL,1 in total.

REC :(%VALUE% <0) ? %VALUE% : 0

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 1.00 ms. 2  RECORDS QUERIED OUT.

QUERY REFERENCE DATA

CALCULATION TIME IN TOTAL :0.00 ms.

2  RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 2  RECORDS ARE GENERATED.

DATA TO WRITE BACK:

ATIVIDADE CCUSTO CONTA CONTRAPARTIDA DIVISAO EMPRESA FONTE INDICADOR MOEDA PERIODO PRODUTO VERSAO SIGNEDDATA

NAATIVIDADE NACCUSTO DB.DEBITO_MENSAL NACONTRAPARTIDA 0101 0001 SAPFI FIS0000001 BRL 201508 NAPRODUTO PLAN201508 0.00

NAATIVIDADE NACCUSTO DB.DEBITO_MENSAL NACONTRAPARTIDA 0101 0001 SAPFI FIS0000003 BRL 201508 NAPRODUTO PLAN201508 - 82034548.02

2  RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :0.00  ms.

SCRIPT RUNNING TIME IN TOTAL:1.00 s.

LOG END TIME:2015-12-15 15:57:33

Note that ACCOUNT 1 and 2 are the source accounts, ACCOUNT 3 is the auxiliary account and ACCOUNT 4 is the destination account.

Unfortunately the code is not respecting the sum of the member, it´s applying the rec rule by member.

Thanks in advance for your help.

[Former Member]

Hello Vadim,

Thanks for your response.

It´s working now, I needed the balance of these two accounts CR_COMP_DED_IRPJ and CR_COMP_DED_CSLL and execute a basic logic <0.

*SELECT ( %PER_ATUA% , [DATABASE],VERSAO , ID=%VERSAO_SET%)

*XDIM_MEMBERSET PERIODO = %PER_ATUA%

*XDIM_MEMBERSET CONTA= BAS(CR_COMP_DED_IRPJ,CR_COMP_DED_CSLL)

*WHEN CONTA

     *IS *

          *REC(EXPRESSION=%VALUE%, CONTA = CR_COMP_DED_IRPJ_CSLL, INDICADOR = FIS0000011)

*ENDWHEN

*WHEN CONTA

     *IS "CR_COMP_DED_IRPJ_CSLL"

          *REC(EXPRESSION=%VALUE%<0 ? %VALUE%*1:0,CONTA=DB.DEBITO_MENSAL)

*ENDWHEN

[former_member186338]

Sorry, but is CR_COMP_DED_IRPJ_CSLL inside BAS(CR_COMP_DED_IRPJ,CR_COMP_DED_CSLL)?

[Former Member]

Hello Vadim,

Thanks for your response.

CR_COMP_DED_IRPJ_CSLL is the account that receives the net value of CR_COMP_DED_IRPJ and CR_COMP_DED_CSSL aacounts.

Rgs,

[former_member186338]

No answer to my question... please show the hierarchy of the account dimension!

Vadim

[Former Member]

Hello Vadim,

It´s very simple.

There are 3 accounts:

ACCOUNT A

ACCOUNT B

ACCOUNT C

ACCOUNT A = should contain the ACCOUNT B and C net value.

There´s no account hierarchy, it´s a simple sum opereation, but in this case I would to have not all base members records, but only one record of the net value.

Rgs

[former_member186338]

*XDIM_MEMBERSET ACCOUNT=B,C

*WHEN ACCOUNT

*IS *

*REC(EXPRESSION=%VALUE%,ACCOUNT=A)

*ENDWHEN

[Former Member]

Hello Vadim,

This code will copy all values from account B, C to account A with the same number of records.

What I am trying to achieve is only one record (account B and C sum) to account A.

If i have just one record I could use the conditional expression below:

*WHEN ACCOUNT

*IS A

*BEGIN

*REC(EXPRESSION=%VALUE%> 0 ? %VALUE%*1 : %VALUE%*0, ACCOUNT= "E")

*END

*ENDWHEN

With multiple value the expression above only get the positive numbers, the condition should consider only the account net alue.

The MDX statement works the same way.

ACCOUNT].[#E]  =( [ACCOUNT].[A]>0? [ACCOUNT].[E] : 0 )

Rgs,

[former_member186338]

I am unable to understand your requirements...

"What I am trying to achieve is only one record (account B and C sum) to account A." - absolutely not clear!

Please show the table with some test values and the desired result!

If you want to sum positive values in A and B:

*XDIM_MEMBERSET ACCOUNT=A,B

*WHEN ACCOUNT

*IS A

*REC(EXPRESSION=%VALUE%>0 ? %VALUE% : 0, ACCOUNT=E)

*IS B

*REC(EXPRESSION=%VALUE%>0 ? %VALUE% : 0, ACCOUNT=E)

*ENDWHEN

All positive amounts of A and B will be accumulated in E...

Vadim

[Former Member]

CASE  1
	ACOUNT	VALUE
	A	10
	A	-20
	A	15
TOTAL  A		5
	B	-20
	B	-5
	B	10
TOTAL  B		-15
TOTAL A, B		-10

The code below will copy all values from B,C to account A

*XDIM_MEMBERSET ACCOUNT=B,C

*WHEN ACCOUNT

*IS *

*REC(EXPRESSION=%VALUE%,ACCOUNT=A)

*ENDWHEN

CASE  1
	ACOUNT	VALUE
	A	10
	A	-20
	A	15
	A	-20
	A	-5
	A	10
TOTAL  A		-10

The conditional code below is copying only the postive values.

*WHEN ACCOUNT

*IS A

*BEGIN

*REC(EXPRESSION=%VALUE%> 0 ? %VALUE%*1 : %VALUE%*0, ACCOUNT= "E")

*END

*ENDWHEN

CASE  1
	ACOUNT	VALUE
	E	10
	E	15
	E	10
TOTAL  E		35

The code is applying the REC rule by record, not by total account value

If the total A account value is greater than zero it should record it´s value to ACCOUNT E, otherwise record zero, should work like that:

TOTAL  A

-10

CASE  1
	ACOUNT	VALUE
	E	0
TOTAL  E		0

I don´t want only the positive records, I want the total of the account and apply the conditional statement.

[former_member186338]

In order to apply condition to the total of account A you have to calculate total in first loop and store the result in some temporary account Atotal. Then you have another loop for A total in scope - in this loop you will test the condition and write result to destination account. There is no way to achieve your goal with a single when/endwhen loop.

Vadim

[former_member186338]

P.S. In your explanation with tables it's not clear:

"The code below will copy all values from B,C to account A"

but in the table there is no account C?

And account A is not the target...

Vadim

[former_member186338]

What accounts you want to sum? Not clear from your post! All base members of BAS(CR_COMP_DED_IRPJ,CR_COMP_DED_CSLL)?

And please read:

Vadim

[former_member186338]

P.S. Remove *COMMIT - it will reset scope!

And explain in details what do you have in the property DB.DEBITO_MENSAL

[Former Member]

Hello Vadim,

I just want to record only the net value of each account.

Rgs,