cancel
Showing results for 
Search instead for 
Did you mean: 

Logic Script - calculating Accounts with BAS()

Former Member
0 Kudos


Hello experts,

I am trying to write a script that includes a BAS formula to gather the sum of all the descendants.  I want to pull the value of all, less a few accounts.  This is the line of code I have, and it gives me an error around the BAS formula.  Is there a better way to do this?

*WHEN ACCOUNT

*IS BAS(P_EA3700)

*REC(EXPRESSION = ([ACCOUNT].[BAS(P_EA3700)] - ([ACCOUNT].[A_51800020])), ACCOUNT = "A_FCST_3700", AUDITTRAIL = "CC")

When I put an actual Account in place of the BAS forumla, it works.  The EA3700 has a lot of accounts within it, and i need the value of all, less this 51800020 account, and a couple others I will add when I figure out this code.

Thanks!

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member186338
Active Contributor
0 Kudos

Hi Sean,

The code you are trying to run is absolutely incorrect

If this code is for DM package (not for default.lgf) then:

*XDIM_MEMBERSET ACCOUNT= BAS(P_EA3700)

*WHEN ACCOUNT

*IS<>A_51800020 //assuming that A_51800020 is in BAS(P_EA3700)

*REC(EXPRESSION=%VALUE%,ACCOUNT = "A_FCST_3700", AUDITTRAIL = "CC")

*ENDWHEN

Vadim

Former Member
0 Kudos

Thank you Vadim.  This line is one of many that currently work with a different *XDIM_MEMBERSET.  I will try to seperate the two based on your suggestion.

former_member186338
Active Contributor
0 Kudos

Sorry, not clear...

Vadim

Former Member
0 Kudos

Thank you for the input, however the proposed solution did not work.

former_member186338
Active Contributor
0 Kudos

??? Why?

Can your clearly specify what do you want to achieve?

Vadim

Former Member
0 Kudos

Here is the code I used, based on your input.

*FUNCTION Target_Cat = %CATEGORY_SET%

*XDIM_MEMBERSET AUDITTRAIL = ECC
*XDIM_MEMBERSET CATEGORY = Actual
*XDIM_MEMBERSET RPTCURRENCY = LC
*XDIM_MEMBERSET RCOST_CENTER = RCCA_NONE
*XDIM_MEMBERSET TIME = %TIME_SET%
*XDIM_MEMBERSET COST_CENTER = %COST_CENTER_SET%
*XDIM_MEMBERSET ACCOUNT = BAS(P_EA3700)

*WHEN ACCOUNT

*IS <> A_51800020

*REC(EXPRESSION=%VALUE%,ACCOUNT = "A_FCST_3700", AUDITTRAIL = "CC", CATEGORY = "Target_Cat")

*ENDWHEN
*COMMIT

When I run the code, a value does appear in the correct location but it is not the correct amount.

Below is a picture of the report I am using for testing.  You can see the value in both the P_EA3700 and the A_51800020.  When the data moves, it should be the total P_EA3700 - A_51800020, which should be $7,957 in this example.

Thanks

former_member186338
Active Contributor
0 Kudos

Sorry, but in REC you have target CATEGORY = "Target_Cat" And in report I see Forecast_1....

Please do some tests in UJKT and post logs....

Vadim

former_member186338
Active Contributor
0 Kudos

P.S. Stop using COMMIT - useless with WHEN/ENDWHEN, just reset the scope....

Former Member
0 Kudos

The CATEGORY is a variable, that is selected in the DM Prompt.  In this example, Forecast_1 was selected in the prompt for Category. The top line in the code for FUNCTION displays this.

Thanks for your help,

former_member186338
Active Contributor
0 Kudos

I see, but what for to use function - to make script unclear? And I am not sure it will work correctly in all cases...

Test this in UJKT with Data area set to required members:

*XDIM_MEMBERSET AUDITTRAIL = ECC
*XDIM_MEMBERSET CATEGORY = Actual
*XDIM_MEMBERSET RPTCURRENCY = LC
*XDIM_MEMBERSET RCOST_CENTER = RCCA_NONE
*XDIM_MEMBERSET TIME = %TIME_SET%
*XDIM_MEMBERSET COST_CENTER = %COST_CENTER_SET%
*XDIM_MEMBERSET ACCOUNT = BAS(P_EA3700)

*WHEN ACCOUNT

*IS <> A_51800020

*REC(EXPRESSION=%VALUE%,ACCOUNT = "A_FCST_3700", AUDITTRAIL = "CC", CATEGORY = %CATEGORY_SET%)

*ENDWHEN

Post UJKT LOG of Execute.

Vadim

Former Member
0 Kudos

I apologize if it does not make sense. I am not an expert and it did work as expected, but I can make the proposed change. I will perform the testing in the UJKT.

former_member186338
Active Contributor
0 Kudos

Just to confirm:

you have have some parent account P_EA3700

one of base members under P_EA3700 is A_51800020

you want to sum all base members under P_EA3700 except A_51800020 and write result to A_FCST_3700, AUDITTRAIL=CC, CATEGORY=Forecast_1

Vadim

Former Member
0 Kudos

Yes, that is correct.

former_member186338
Active Contributor
0 Kudos

Then my code is correct (or if it's BPC 7.5 and the ACCTYPE is INC then you have to add "-" before %VALUE%)

Vadim

Former Member
0 Kudos

We have BPC 10.  The Parent Account does have both EXP and INC ACCTYPEs within it.  Would that affect it?  When I run your code, the value does not add up correctly.

former_member186338
Active Contributor
0 Kudos

Let's check the following code:

*XDIM_MEMBERSET AUDITTRAIL = ECC
*XDIM_MEMBERSET CATEGORY = Actual
*XDIM_MEMBERSET RPTCURRENCY = LC
*XDIM_MEMBERSET RCOST_CENTER = RCCA_NONE
*XDIM_MEMBERSET TIME = %TIME_SET%
*XDIM_MEMBERSET COST_CENTER = %COST_CENTER_SET%
*XDIM_MEMBERSET ACCOUNT = BAS(P_EA3700)

*WHEN ACCOUNT.ACCTYPE

*IS INC

*REC(EXPRESSION=%VALUE%,ACCOUNT = "A_FCST_3700", AUDITTRAIL = "CC", CATEGORY = %CATEGORY_SET%)

*ENDWHEN

What will be the result?

The same but with *IS EXP - what will be the result?

Vadim

former_member186338
Active Contributor
0 Kudos

Anyway, it's EXTREMELY strange from the finance point of view to have parent with the name OTHER EXPENSES and to have some base members under this parent with INC account type...

B.R. Vadim

Former Member
0 Kudos

Thank you, I will perform those test.  I do agree with you that it is strange.

former_member186338
Active Contributor
0 Kudos

I just want to see the numbers...

The total amount is 8027

The first test will sum and copy all INC accounts, the second will do this for EXP accounts!

By the way what is the ACCTYPE of A_51800020?

Vadim

Former Member
0 Kudos

So the test had interesting results.

You are correct, the Total is 8,027

When I used INC - I got 8,027

When I used EXP - I got (83,856)

The 51800020 is an EXP accout type.

former_member186338
Active Contributor
0 Kudos

That's something absolutely strange...

Can you show the EPM report with all base members of P_EA3700 with ACCTYPE for each member and the amount for one month and:

AUDITTRAIL = ECC

CATEGORY = Actual

RPTCURRENCY = LC

RCOST_CENTER = RCCA_NONE

TIME = some single month

COST_CENTER = some cost center

Can you check that aggregation on parent node is OK? (INC have to be reversed sign for aggregation).

Show parent P_EA3700 on this report also.

B.R. Vadim

Former Member
0 Kudos

Here it is, although there are INC accounts within the hierarchy, appears none of them have a value for this example I am using. This report removes empty/zero values, as there are many accounts within the EA3700.  I agree, based on what I see, I think your code should work.

former_member186338
Active Contributor
0 Kudos

Ok, If you have no records for INC accounts, then the code:

*WHEN ACCOUNT.ACCTYPE

*IS INC

...

will not write anything to target and what you see is the previous value!

No records - no writes!

But the code for EXP accounts:

*WHEN ACCOUNT.ACCTYPE

*IS EXP

have to provide the same amount as for total P_EA3700!

And if we remove some specific account using *IS <> XXXX then we will have the sum of all accounts without this particular one.

Vadim

JohnL
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Sean, Can you try adding:

*XDIM_MEMBERSET MEASURES = Periodic

Thx,

John

Former Member
0 Kudos

Ok, I simplified it.  I hard coded the Variables for TIME, COST_CENTER and CATEGORY.

I Cleared all the Forecast_1 data in Category we we can start clean.  Here is the EPM Report Before running the script.

Here is My script.  I added the Measures as John mentioned.

*XDIM_MEMBERSET AUDITTRAIL = ECC
*XDIM_MEMBERSET MEASURES = PERIODIC
*XDIM_MEMBERSET CATEGORY = Actual
*XDIM_MEMBERSET RPTCURRENCY = LC
*XDIM_MEMBERSET RCOST_CENTER = RCCA_NONE
*XDIM_MEMBERSET TIME = 2013.01
*XDIM_MEMBERSET COST_CENTER = CCA_7340016
*XDIM_MEMBERSET ACCOUNT = BAS(P_EA3700)


*WHEN ACCOUNT.ACCTYPE

*IS EXP

*REC(EXPRESSION=%VALUE%,ACCOUNT = "A_FCST_3700", AUDITTRAIL = "CC", CATEGORY = "Forecast_1")                        

*ENDWHEN

After I ran the script, I got the same result as I mentioned before.

I did perform a couple other tests.  When I used WHEN ACCOUNT Is BAS(P_EA3700) I got the correct  8027 to appear in the A_FCST_3700 account.  I also did When Account is A_51800020 then REC it, and I successfully got the 70 into the A_FCST_3700 Account. I have been tring a few other options, none have worked so far.

I will not be in the office much longer.  I really appreciate your time and input today.

Thanks,

former_member186338
Active Contributor
0 Kudos

Ups, I think I understand the issue...

A_FCST_3700 is somehow a base member of P_EA3700? Then we have to exclude it from the scope of the loop!

Test this:

*XDIM_MEMBERSET AUDITTRAIL = ECC
*XDIM_MEMBERSET MEASURES = PERIODIC
*XDIM_MEMBERSET CATEGORY = Actual
*XDIM_MEMBERSET RPTCURRENCY = LC
*XDIM_MEMBERSET RCOST_CENTER = RCCA_NONE
*XDIM_MEMBERSET TIME = 2013.01
*XDIM_MEMBERSET COST_CENTER = CCA_7340016
*XDIM_MEMBERSET ACCOUNT = BAS(P_EA3700)


*WHEN ACCOUNT

*IS A_FCST_3700 //exclude A_FCST_3700

*IS A_51800020 //exclude A_51800020

*ELSE // for all other

*REC(EXPRESSION=%VALUE%,ACCOUNT = "A_FCST_3700", AUDITTRAIL = "CC", CATEGORY = "Forecast_1")                        

*ENDWHEN

Vadim

former_member186338
Active Contributor
0 Kudos

Hi John,

Setting the cube default measure in XDIM_MEMBERSET has no effect on script execution...

Vadim

former_member186338
Active Contributor
0 Kudos

P.S. And please, provide the results of script test in UJKT!

I have tested the script from my last post in my system and get correct results!

Scenario:

Some parent account having multiple base members.

I want to write the total of all base members excluding some particular base member and excluding the target base member to the target base member (target is a base member of parent account) to the different CATEGORY.

B.R. Vadim

Former Member
0 Kudos

Hey Vadim,

I tested the new code and the results were the same as the prior unfortunatly.  I used the UJKT and below are the results.  Let me know if this is what you were looking for.

LGX:



*WHEN ACCOUNT


*IS A_FCST_3700


*IS A_51800020


*ELSE


*REC(EXPRESSION=%VALUE%,ACCOUNT = A_FCST_3700, AUDITTRAIL = CC, CATEGORY = Forecast_1)


*ENDWHEN



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


LOG:



FILE:\ROOT\WEBFOLDERS\NAM_PRIMARY \ADMINAPP\Planning\TEST.LGF


USER:USSEDYA


APPSET:NAM_PRIMARY


APPLICATION:Planning


[INFO] GET_DIM_LIST(): I_APPL_ID="Planning", #dimensions=8


ACCOUNT,AUDITTRAIL,CATEGORY,COST_CENTER,MEASURES,RCOST_CENTER,RPTCURRENCY,TIME



#dim_memberset=8


*XDIM_MEMBERSETAUDIT:ECC,1 in total.


*XDIM_MEMBERSETMEASU:PERIODIC,1 in total.


*XDIM_MEMBERSETCATEG:Actual,1 in total.


*XDIM_MEMBERSETRPTCU:LC,1 in total.


*XDIM_MEMBERSETRCOST:RCCA_NONE,1 in total.


*XDIM_MEMBERSETTIME:2013.01,1 in total.


*XDIM_MEMBERSETCOST_:CCA_7340016,1 in total.


*XDIM_MEMBERSETACCOU:BAS(P_EA3700),1 in total.



REC :%VALUE%



CALCULATION BEGIN:


QUERY PROCESSING DATA


QUERY TIME : 289033.98 ms. 3338538  RECORDS QUERIED OUT.


QUERY REFERENCE DATA


CALCULATION TIME IN TOTAL :263299.02 ms.


112725  RECORDS ARE GENERATED.


CALCULATION END.



SCRIPT RUNNING TIME IN TOTAL:559.95 s.

former_member186338
Active Contributor
0 Kudos

Hi Sean,

Sorry, but it's not a real UJKT log...

#dim_memberset=8

*XDIM_MEMBERSETAUDIT:ECC,1 in total.

*XDIM_MEMBERSETMEASU:PERIODIC,1 in total.

*XDIM_MEMBERSETCATEG:Actual,1 in total.

*XDIM_MEMBERSETRPTCU:LC,1 in total.

*XDIM_MEMBERSETRCOST:RCCA_NONE,1 in total.

*XDIM_MEMBERSETTIME:2013.01,1 in total.

*XDIM_MEMBERSETCOST_:CCA_7340016,1 in total.

*XDIM_MEMBERSETACCOU:BAS(P_EA3700),1 in total.

bold is not from the log!

And 3338538  RECORDS - close to impossible

Vadim

Former Member
0 Kudos

Hey Vadim,

Could you please advise on how to get the actual Log?  Those were the results when I used the Execute Simulate on the code.  I also noteced the number of records as well. I have attached a couple screenshots from the UJKT.I added the scope below, reduced the record cout to 40.

former_member186338
Active Contributor
0 Kudos

Ups! I see!

In the Data Region you can't use XDIM, you have to specify like this:

AUDITTRAIL=ECC

...

Look here:

Vadim

former_member186338
Active Contributor
0 Kudos

Now, please post the correct UJKT log with:

1. Correctly filled Data Region

2. Or with EMPTY Data Region, but with *XDIM_MEMBERSET in the script code.

Press Execute - I want to see the record result.

Vadim

Former Member
0 Kudos

I emptied the Data Region, and uses the *XDIM_MEMBERSET in the script code.  Here were the results

LGX:



*XDIM_MEMBERSET AUDITTRAIL = ECC


*XDIM_MEMBERSET MEASURES = PERIODIC


*XDIM_MEMBERSET CATEGORY = Actual


*XDIM_MEMBERSET RPTCURRENCY = LC


*XDIM_MEMBERSET RCOST_CENTER = RCCA_NONE


*XDIM_MEMBERSET TIME = 2013.01


*XDIM_MEMBERSET COST_CENTER = CCA_7340016


*XDIM_MEMBERSET ACCOUNT = BAS(P_EA3700)


*WHEN ACCOUNT


*IS A_FCST_3700


*IS A_51800020


*ELSE


*REC(EXPRESSION=%VALUE%,ACCOUNT = A_FCST_3700, AUDITTRAIL = CC, CATEGORY = Forecast_1)


*ENDWHEN



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


LOG:



LOG BEGIN TIME:2014-03-03 10:48:45


FILE:\ROOT\WEBFOLDERS\NAM_PRIMARY \ADMINAPP\Planning\TEST.LGF


USER:USSEDYA


APPSET:NAM_PRIMARY


APPLICATION:Planning


[INFO] GET_DIM_LIST(): I_APPL_ID="Planning", #dimensions=8


ACCOUNT,AUDITTRAIL,CATEGORY,COST_CENTER,MEASURES,RCOST_CENTER,RPTCURRENCY,TIME



#dim_memberset=8


AUDITTRAIL:ECC,1 in total.


MEASURES:PERIODIC,1 in total.


CATEGORY:Actual,1 in total.


RPTCURRENCY:LC,1 in total.


RCOST_CENTER:RCCA_NONE,1 in total.


TIME:2013.01,1 in total.


COST_CENTER:CCA_7340016,1 in total.


ACCOUNT:A_51110000,A_51111000,A_51113000,A_51114000,A_51114001,...787 in total.



REC :%VALUE%



CALCULATION BEGIN:


QUERY PROCESSING DATA


QUERY TIME : 4172.80 ms. 40  RECORDS QUERIED OUT.


QUERY REFERENCE DATA


CALCULATION TIME IN TOTAL :561.27 ms.


1  RECORDS ARE GENERATED.


CALCULATION END.



ENDWHEN ACCUMULATION: 1  RECORDS ARE GENERATED.



DATA TO WRITE BACK:


ACCOUNT AUDITTRAIL CATEGORY COST_CENTER RCOST_CENTER RPTCURRENCY TIME SIGNEDDATA


A_FCST_3700 CC Forecast_1 CCA_7340016 RCCA_NONE LC 2013.01 - 83856.06


1  RECORDS HAVE BEEN WRITTEN BACK.


WRITING TIME :927.43  ms.



SCRIPT RUNNING TIME IN TOTAL:5.82 s.


LOG END TIME:2014-03-03 10:48:50

former_member186338
Active Contributor
0 Kudos

Fine, now at least the results are consistent

Then, the same test (same *XDIM_MEMBERSET's), but

1. Write total

*WHEN ACCOUNT

*IS *

*REC(EXPRESSION=%VALUE%,ACCOUNT = A_FCST_3700, AUDITTRAIL = CC, CATEGORY = Forecast_1)

*ENDWHEN

2. Write only A_FCST_3700

*WHEN ACCOUNT

*IS A_FCST_3700

*REC(EXPRESSION=%VALUE%,ACCOUNT = A_FCST_3700, AUDITTRAIL = CC, CATEGORY = Forecast_1)

*ENDWHEN

3. Write only A_51800020

*WHEN ACCOUNT

*IS A_51800020

*REC(EXPRESSION=%VALUE%,ACCOUNT = A_FCST_3700, AUDITTRAIL = CC, CATEGORY = Forecast_1)

*ENDWHEN

Vadim

Former Member
0 Kudos

Ok, I ran those three scenarios.  The results are as I would expect. Result one had the correct value of 8027.  Result 2, had no results and result three had the value of 70, which is accurate.

Result 1

Result 2

Result 3

former_member186338
Active Contributor
0 Kudos

I see something strange:

In the prev test you have:

*XDIM_MEMBERSET ACCOUNT = BAS(P_EA3700)

*WHEN ACCOUNT

*IS A_FCST_3700

*IS A_51800020

*ELSE

*REC(...

QUERY PROCESSING DATA

40  RECORDS QUERIED OUT.

in the full account sum you have:

*XDIM_MEMBERSET ACCOUNT = BAS(P_EA3700)

*WHEN ACCOUNT

*IS *

*REC(...

QUERY PROCESSING DATA

9  RECORDS QUERIED OUT.

I will come back a bit later!

Vadim

former_member186338
Active Contributor
0 Kudos

Additional tests required to understand why we have 40-9=31 extra records queried:

1. Write all base members to the same intersections

*WHEN ACCOUNT

*IS *

*REC(EXPRESSION=%VALUE%)

*ENDWHEN

List of written records from UJKT log? Have to be 9 records!

2. Write base members except A_FCST_3700 and A_51800020 to the same intersections

*WHEN ACCOUNT

*IS A_FCST_3700

*IS A_51800020

*ELSE

*REC(EXPRESSION=%VALUE%)

*ENDWHEN

List of written records from UJKT log? Have to be 40 records - 9 normal + 31 extra??

Vadim

Former Member
0 Kudos

Ok I did the tests, ont he first one with all the base members, I did get the 9 records.

LGX:



*XDIM_MEMBERSET AUDITTRAIL = ECC


*XDIM_MEMBERSET MEASURES = PERIODIC


*XDIM_MEMBERSET CATEGORY = Actual


*XDIM_MEMBERSET RPTCURRENCY = LC


*XDIM_MEMBERSET RCOST_CENTER = RCCA_NONE


*XDIM_MEMBERSET TIME = 2013.01


*XDIM_MEMBERSET COST_CENTER = CCA_7340016


*XDIM_MEMBERSET ACCOUNT = BAS(P_EA3700)


*WHEN ACCOUNT


*IS *


*REC(EXPRESSION=%VALUE%)


*ENDWHEN



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


LOG:



FILE:\ROOT\WEBFOLDERS\NAM_PRIMARY \ADMINAPP\Planning\TEST.LGF


USER:USSEDYA


APPSET:NAM_PRIMARY


APPLICATION:Planning


[INFO] GET_DIM_LIST(): I_APPL_ID="Planning", #dimensions=8


ACCOUNT,AUDITTRAIL,CATEGORY,COST_CENTER,MEASURES,RCOST_CENTER,RPTCURRENCY,TIME



#dim_memberset=8


AUDITTRAIL:ECC,1 in total.


MEASURES:PERIODIC,1 in total.


CATEGORY:Actual,1 in total.


RPTCURRENCY:LC,1 in total.


RCOST_CENTER:RCCA_NONE,1 in total.


TIME:2013.01,1 in total.


COST_CENTER:CCA_7340016,1 in total.


ACCOUNT:A_51110000,A_51111000,A_51113000,A_51114000,A_51114001,...787 in total.



REC :%VALUE%



CALCULATION BEGIN:


QUERY PROCESSING DATA


QUERY TIME : 2996.79 ms. 9  RECORDS QUERIED OUT.


QUERY REFERENCE DATA


CALCULATION TIME IN TOTAL :156.05 ms.


9  RECORDS ARE GENERATED.


CALCULATION END.



SCRIPT RUNNING TIME IN TOTAL:3.26 s.

2nd Test: It ran 40 records.

LGX:



*XDIM_MEMBERSET AUDITTRAIL = ECC


*XDIM_MEMBERSET MEASURES = PERIODIC


*XDIM_MEMBERSET CATEGORY = Actual


*XDIM_MEMBERSET RPTCURRENCY = LC


*XDIM_MEMBERSET RCOST_CENTER = RCCA_NONE


*XDIM_MEMBERSET TIME = 2013.01


*XDIM_MEMBERSET COST_CENTER = CCA_7340016


*XDIM_MEMBERSET ACCOUNT = BAS(P_EA3700)


*WHEN ACCOUNT


*IS A_FCST_3700


*IS A_51800020


*ELSE


*REC(EXPRESSION=%VALUE%)


*ENDWHEN



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


LOG:



FILE:\ROOT\WEBFOLDERS\NAM_PRIMARY \ADMINAPP\Planning\TEST.LGF


USER:USSEDYA


APPSET:NAM_PRIMARY


APPLICATION:Planning


[INFO] GET_DIM_LIST(): I_APPL_ID="Planning", #dimensions=8


ACCOUNT,AUDITTRAIL,CATEGORY,COST_CENTER,MEASURES,RCOST_CENTER,RPTCURRENCY,TIME



#dim_memberset=8


AUDITTRAIL:ECC,1 in total.


MEASURES:PERIODIC,1 in total.


CATEGORY:Actual,1 in total.


RPTCURRENCY:LC,1 in total.


RCOST_CENTER:RCCA_NONE,1 in total.


TIME:2013.01,1 in total.


COST_CENTER:CCA_7340016,1 in total.


ACCOUNT:A_51110000,A_51111000,A_51113000,A_51114000,A_51114001,...787 in total.



REC :%VALUE%



CALCULATION BEGIN:


QUERY PROCESSING DATA


QUERY TIME : 4478.54 ms. 40  RECORDS QUERIED OUT.


QUERY REFERENCE DATA

former_member186338
Active Contributor
0 Kudos

You don't understand me!

Please, show the records in UJKT log for both cases, like

ACCOUNT AUDITTRAIL CATEGORY COST_CENTER RCOST_CENTER RPTCURRENCY TIME SIGNEDDATA

A_FCST_3700 CC Forecast_1 CCA_7340016 RCCA_NONE LC 2013.01 - 83856.06

...

Vadim

former_member186338
Active Contributor
0 Kudos

By the way, at least in core BPC SP12 the following trick can be used to get a list of base members of some parent and then exclude some members:

*XDIM_MEMBERSET ACCOUNT AS %AC%=BAS(P_EA3700)

*SELECT(%AC1%,"[ID]",ACCOUNT,"[ID]=%AC% AND [ID]<>A_51800020 AND [ID]<>A_FCST_3700")

*XDIM_MEMBERSET ACCOUNT=%AC1%

Vadim

Former Member
0 Kudos

As I mentioned before, I would like your assistance on how to view the Logs in the UJKT as I have never done that before.  I can run a report in Excel to show the values, or please guide me to the record logs within the UJKT.  Thanks. 

former_member186338
Active Contributor
0 Kudos

Hi Sean,

Hope it's not a joke

Records log in red!

former_member186338
Active Contributor
0 Kudos

Just copy here the records log for 9 records and for 40 records! Text, not picture!

Former Member
0 Kudos

It Appears I did not copy all the way down the log, that is what caused the confusion.  Below are the records.  After reviewing this, I did some digging.  It appears the pull with the 40 records is pulling ALL the accounts with data for this entity, except those two we outlined in the script.  Including accounts that are not within the P_EA3700.  For Example, the first record, the account 46001000, is a personel expense account.  That appears to be the difference, when using the ELSE code, it appears to not keep the scope within the BAS(P_EA3700)

DATA TO WRITE BACK:


ACCOUNT AUDITTRAIL CATEGORY COST_CENTER RCOST_CENTER RPTCURRENCY TIME SIGNEDDATA


A_51800020 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 70.22


A_51800028 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 3738.52


A_55200000 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 100.00


A_55205000 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 5288.00


A_55508000 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 1364.07


A_56230000 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 - 9718.73


A_98420599 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 315.24


A_98420601 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 6587.39


A_98420625 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 282.44


9  RECORDS HAVE BEEN WRITTEN BACK.

40 Record Log

DATA TO WRITE BACK:


ACCOUNT AUDITTRAIL CATEGORY COST_CENTER RCOST_CENTER RPTCURRENCY TIME SIGNEDDATA


A_46001000 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 35119.82


A_46006000 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 3503.76


A_47100000 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 2134.17


A_47100001 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 499.12


A_47100200 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 185.63


A_47100300 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 784.83


A_47100900 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 278.45


A_47105000 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 - 4982.17


A_47200000 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 3846.25


A_47203000 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 886.96


A_47203001 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 2072.07


A_47206000 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 867.61


A_47208500 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 2168.67


A_47209500 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 5000.00


A_47304000 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 3.39


A_51300000 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 2089.09


A_51500000 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 17761.33


A_51800020 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 70.22


A_51800028 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 3738.52


A_52105001 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 41.47


A_52207000 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 8.26


A_52208500 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 2199.86


A_53310000 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 549.95


A_53320000 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 205.20


A_55200000 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 100.00


A_55205000 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 5288.00


A_55508000 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 1364.07


A_56230000 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 - 9718.73


A_92001102 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 5.00


A_92001199 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 - 5.00


A_97420599 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 315.24


A_97420601 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 6587.39


A_97420625 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 282.44


A_97431010 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 - 74024.00


A_97431030 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 - 13122.00


A_98420599 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 315.24


A_98420601 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 6587.39


A_98420625 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 282.44


A_98431010 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 - 74024.00


A_98431030 ECC Actual CCA_7340016 RCCA_NONE LC 2013.01 - 13122.00


40  RECORDS HAVE BEEN WRITTEN BACK.

former_member186338
Active Contributor
0 Kudos

Solution:

1. Upgrade for the latest core SP of BPC - I don't have this issue in my system. It's a bug!

2. Or, use the code I proposed to select accounts:

*XDIM_MEMBERSET ACCOUNT AS %AC%=BAS(P_EA3700)

*SELECT(%AC1%,"[ID]",ACCOUNT,"[ID]=%AC% AND [ID]<>A_51800020 AND [ID]<>A_FCST_3700")

*XDIM_MEMBERSET ACCOUNT=%AC1% //only necessary accounts will be selected in the scope

*WHEN ACCOUNT

*IS *

*REC(EXPRESSION=%VALUE%,ACCOUNT = A_FCST_3700, AUDITTRAIL = CC, CATEGORY = Forecast_1)

*ENDWHEN

Vadim

Message was edited by: Vadim Kalinin - WHEN/ENDWHEN Added

Former Member
0 Kudos

Thank you Vadim, I will look into upgrading our SP.  I appreciate your help, I know this was a bit painful.  Thank you.

former_member186338
Active Contributor
0 Kudos

By the way, the code with SELECT is even better then original - only accounts to be added are selected in the scope for WHEN/ENDWHEN.

Have you tested it?

former_member186338
Active Contributor
0 Kudos

P.S. But to use this code you need at least SP09 for core BPC on 7.30 (or equivalent)

http://service.sap.com/sap/support/notes/1764086