Solved: Reverse an entry

Hi,

Is there a way using datapackage I can reverse an entry?

because end of period my total in one account is doubled, I want to reverse it so end of period it shows correct number.

two accounts

account one from BI

account two entered in BPC

want to reverse account two.

Thanks

SAP Managed Tags:
SAP Business Planning and Consolidation, version for the Microsoft platform

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (1)

Hi,

The amount can be reversed any time, by using a script logic to multiply the amount by -1.

However, it would be better if you analyze why is it getting doubled.

My understanding is Account1 is getting value from BI and Account2 is getting value from BPC. Now, when you report, the values are displayed as doubled (which is the sum of account1 and account2). Am I right till here?

This means that in the report you are not using the distinguishing factor.

Lets say, that region BI data is stored is like:

Account1    Cat1    2009.JAN    ENT1    2000

The BPC data is like:

Account2    Cat2    2009.JAN    ENT1    2000

Now, in the report, if you want to see the data for

All Account    All Cat    2009.JAN    ENT1

Then you will get the doubled figure of 4000

You need to use the differentiating factor (in this case Cat1 and Cat2). This was just an example.

I hope you got the idea.

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hi Nilanjan,

Thanks for your reply.

This is the code I am trying to use .not sure how to make the totals same and execute this logic when ?

at period end i want the input from BPC which is Manual to be equal to BI data COST

*INCLUDE MdxLib.LGL

*INCLUDE System_Library.LGL

*INCLUDE FXTRANS.LGL

*LOCAL_CURRENCY = LCLCUR

//SPECIFY DIMENSIONS. WE WANT TO REVERSE THE ENTRY FOR MANUAL AT PERIOD END.

*XDIM_MEMBERSET DATASRC = <ALL>

*XDIM_MEMBERSET ACCOUNT= COST,MANUAL

*XDIM_MEMBERSET TIME = 2011.FP1,2011.FP2,2011.FP3,2011.FP4,2011.FP5,2011.FP6,2011.FP7,2011.FP8,2011.FP9,2011.FP10,2011.FP11,2011.FP12

*XDIM_MEMBERSET SCENARIO= ACTUAL

// LOGIC BEGINS

*WHEN ACCOUNT

*IS COST,MANUAL

*REC(ACCOUNT=MANUAL

*ENDWHEN

*GO

*WHEN ACCOUNT

*IS COST.VAR

//OPTION 1

//*REC(FACTOR=GET(COST- MANUAL)

//OR

*REC(FACTOR=GET(COST=MANUAL)

//OR

//OPTION 3

//*REC(FACTOR=(COST =MANUAL)

*ENDWHEN

*COMMIT

Hi,

The figures are getting doubled because of the below part:

*WHEN ACCOUNT
*IS COST,MANUAL
   *REC(ACCOUNT=MANUAL)
*ENDWHEN

You are writing both COST and MANUAL into MANUAL. So, the figures will be doubled. If you want to copy the BI into BPC, then your code should be:

*WHEN ACCOUNT
*IS COST
   *REC(ACCOUNT=MANUAL)
*ENDWHEN

However, there should not be any value already existing in MANUAL.

So, to ensure that you may want to delete the MANUAL data by the below code:

*WHEN ACCOUNT
*IS MANUAL
   *REC(EXPRESSION=0)
*ENDWHEN

Since you are copying from COST to MANUAL, the values will be always the same.

Hope this helps.

That helps Nilanjan,

If I change the statement as you suggested , is it going to affect the data which is already in BPC ?

now I don't have this code in script logic.

COST and Manual are members of Account dimension.

But data which goes into Manual is from Input schedule in BPC and COST is from BI.

I just want the numbers to be correct at period end

i.e total from BI for cost = total of BPC from MANUAL +COST.

Hi,

Just a moment. I am little confused by the statement:

total from BI for cost = total of BPC from MANUAL +COST

Where are you storing all these 3 data (BI cost, BPC cost, BPC manual)?

Sorry Nilanjan,

I will try to explain you better.

from BI - total value is 100000

in BPC it is 100000 + value entered thru input schedules before period end.

at period end we need to match the values because it can be doubled.

does that make sense.

and the accounts which i use to show values from BI is cost and

BPC is Manual

Hi,

If that is the case, then can you have:

total from BI for cost = total of BPC from MANUAL +COST

I guess BI for cost is 100000 and even BPC from manual is 100000. Am I right?

no because how should we handle the data entered through input schedules in BPC . which stays in BPC

either there should be a way to show the offset entry or make it same total .

cost total - BI is 100

Manual total in BPC is 120 at month end

all i need is make BPC total 120 same as 100 at month end how to reverse it with logic.

Edited by: sapgirl on Apr 28, 2011 11:08 AM

Hi Nilanjan,

Can you please share the code to reverse the entry as mentioned in your post

*-1 , can you give an example of the script ?

Thanks

Hi,

You can try something like

*WHEN dimensionname
*IS member
   *FACTOR(-1)
*ENDWHEN

The above code will multiply the existing value with -1 and store it in the same data region. So, in your reports, you will see a value of 0.

Hope this helps.

Thank Nilanjan,

I am getting error on the factor line .

Is something missing ?

Can you please share the code?

*INCLUDE MdxLib.LGL//

*INCLUDE System_Library.LGL

//*INCLUDE FXTRANS.LGL

*LOCAL_CURRENCY = LCLCUR

//SPECIFY DIMENSIONS.

*XDIM_MEMBERSET DATASRC = MANUAL

*XDIM_MEMBERSET ACCOUNT= Variable

*XDIM_MEMBERSET TIME = 2011.FP1,2011.FP2,2011.FP3,2011.FP4,2011.FP5,2011.FP6,2011.FP7,2011.FP8,2011.FP9,2011.FP10,2011.FP11,2011.FP12

*XDIM_MEMBERSET SCENARIO= ACTUAL

//WE WANT TO REVERSE THE ENTRY FOR Variable AT PERIOD END.

// LOGIC BEGINS

//----

//**************

*WHEN ACCOUNT

*IS VARIABLE

*REC(EXPRESSION=0)

*ENDWHEN

//----

*WHEN ACCOUNT

*IS VARIABLE

*FACTOR(-1)

*ENDWHEN

//----

Tried both above codes but doesnot give correct results for expression and for factor getting error while validating.

Thanks

Hi,

I am sorry....its my mistake.

Please try it as:

*WHEN ACCOUNT

*IS VARIABLE

*REC(FACTOR=-1)

*ENDWHEN

and remove the first when and endwhen statement. Because it is first writing 0 and then multiplying it with -1. So, the result is going to be 0 again.

Hope this helps.

Nope it doesnot work , first time when i open report the values in variable are empty but after i expand it shows up again also

the total doesnot change .

this is how my report looks.

TOTAL -18179456

BI-18186956

MANUAL-6500

I need two things to happen

1) The value in Manual should be zero or reversed.

2) TOTAL value should be equal to BI

currently the BI total includes Manual and I dont want that to be included.

Thanks

Hi,

TOTAL -18179456

BI-18186956

MANUAL-6500

Is TOTAL parent of BI and MANUAL?

So, you need 0 in MANUAL?

Then you need:

*WHEN dimensionname
*IS MANUAL
   *REC(EXPRESSION=0)
*ENDWHEN

Hope this helps.

Yes total is parent of BI and MANUAL.

but since its a data source I am using the account variable for that which holds value of Manual.

I tried the above code before it didnot work.

Please advise.

Thanks.

Hi,

I would request you to please explain it clearly. What is the region of the different data. It will be easier to help.

TOTAL -18179456

BI-18186956

MANUAL-6500

The above are members of datasrc

and the accounts which hold the values for the above are

so members of account are COST and VARIABLE

BI - COST

Manual - Variable.

I am trying to match the values of BI to total at period end as it is adding up the value from Manual also.

Hi,

So, this means that 6500 is stored in datasrc=manual and account=variable.

This means if you want to make this value 0, then you need both the conditions in your script. So your logic should be

*WHEN DATASRC

*IS MANUAL

*WHEN ACCOUNT

*IS VARIABLE

*REC(EXPRESSION=0)

*ENDWHEN

The above code will make the manual entry 0. However, when you report, you need to make sure that you look at the correct region.

Hope this helps.

Thanks Nilanjan for your prompt reply.

I had tried this code yesterday , It didnot work.

Hi,

The code is definitely fine, provided that it is used appropriately. This is the max I could go with the information you provided.

The only thing I can suggest you is that you need to figure out what is the data region you want to work on and write the code accordingly. Secondly, you need to ensure that you are running the report on the correct data region. Otherwise, you wont get the correct values and it will give the impression that the script is not working.

I wont be able to help you more on this since I dont have the access to your system.

The code examples, I have given earlier, will help you to understand how they work. Now, you need to customize them to make them work as you need.

I am sure you will figure it out.

Nilanjan,

I have this code in default logic , Is that causing problem? I thought it will trigger as soon as I open the report as i have the opens selected in report expand when open.

strange thing is even if i use factor option i get exact same result so i am wondering if its not looking at the code ??

Hi,

Default logic will not be executed as long as you send some data to the database. So, this might be causing the trouble. Once the default logic is written and validated, you need to send some data to the database to run the default logic. Alternatively, you can run it through the DM package also. There is one package to run default logic.

Hope this helps.

Can you please tell me the package name ?

Default Formulas - Thats the name of the package.

Sorry Nilanjan for bothering you.

I did run the datapackage . It ran without errors and completed. then I looked at the report the totals didnot change .

can you please help ?

Hi,

Lets try to do some tests to see whether our code is working or not.

Our code is:

*WHEN DATASRC
*IS MANUAL
   *WHEN ACCOUNT
   *IS VARIABLE
      *REC(EXPRESSION=0)
   *ENDWHEN
*ENDWHEN

First optimize your application to clear the fac2 and wb tables. Now, send some data to datasrc=manual and account=variable (may be 100) through input schedule. Now go to the wb table and you should be able to see 2 records. One with 100 and another with -100. If yes, then our code is working. Please confirm.

Next thing is let me know ho you are running the report. I mean on what data region you are reporting on.

Followed the steps , and reviewed I do see the value I entered 100 after optimizing the app

Hi,

Did you see 100 in the wb table? Did you see any other record in the wb or fac2 table?

I saw the value in the report . I dont have access to sql admin side.

Hi,

Do you see 100 or 0?

Did you post 100 to the intersection of Manual and Variable? If yes, you should see 0, because as soon as you send 100 to this intersection, the default logic should run and post 0.

Yes i posted 100 on intersection . But it didnot change to zero. Ran default formula again to check .

Hi,

This is a full proof logic and will definitely work. Can you please share the logic once again.

I agree Nilanjan this should work . I really cant figure out whats going on. Logic is same from above post tried factor -1 as well as expression both did not work they actually dont change numbers.

Hi,

Can you please share the exact code, you used in the default logic.

Goodmorning Nilanjan,

Here is the code and the result . The value in manual should have changed to zero but it didnot.

2011.FP12 2011.FW49 2011.FW50 2011.FW51 2011.FW52

2011 APR Fiscal Week 49 2011 Fiscal Week 50 2011 Fiscal Week 51 2011 Fiscal Week 52 2011

TOTAL 18,187 (600.00) 9,859 8,331 (2,901.00)

BI 18,186 (1,000.00) 9,858 8,333 (4,000.00)

MANUAL 1,500.00 500.00 1,000.00 (1,500.00) 1,500.00

*INCLUDE MdxLib.LGL

*INCLUDE System_Library.LGL

//*INCLUDE FXTRANS.LGL

*LOCAL_CURRENCY = LCLCUR

//SPECIFY DIMENSIONS.

*XDIM_MEMBERSET DATASRC = INPUTSCHEDULE,TOTAL,BI

*XDIM_MEMBERSET ACCOUNT=MANUAL

*XDIM_MEMBERSET TIME = 2011.FP1,2011.FP2,2011.FP3,2011.FP4,2011.FP5,2011.FP6,2011.FP7,2011.FP8,2011.FP9,2011.FP10,2011.FP11,2011.FP12

*XDIM_MEMBERSET SCENARIO= ACTUAL

// REVERSE THE ENTRY MANUAL AT PERIOD END.

// LOGIC BEGINS

*WHEN DATASRC

*IS INPUTSCHEDULE

*WHEN ACCOUNT

*IS MANUAL

*REC(EXPRESSION=0)

*ENDWHEN

Hi,

Can you please share the result enclosed within the triangular bracket, so that it is easier to understand. Please preview the data before posting them. I cant make out anything about the data from what you have posted.

One more thing, when you sent the data, what was the DataSrc member?

<2011.FP12 2011.FW49 2011.FW50 2011.FW51 2011.FW52>

datasource name for input is INputsched

account - variable

Edited by: sapgirl on May 4, 2011 9:32 AM

<*INCLUDE MdxLib.LGL>

<*INCLUDE System_Library.LGL>

<*LOCAL_CURRENCY = LCLCUR>

//SPECIFY DIMENSIONS.

<*XDIM_MEMBERSET DATASRC = INPUTSCHEDULE>

<*XDIM_MEMBERSET ACCOUNT= MANUAL>

<*XDIM_MEMBERSET TIME = 2011.FP1,2011.FP2,2011.FP3,2011.FP4,2011.FP5,2011.FP6,2011.FP7,2011.FP8,2011.FP9,2011.FP10,2011.FP11,2011.FP12>

<*XDIM_MEMBERSET SCENARIO= ACTUAL>

//WE WANT TO REVERSE THE ENTRY FOR COST.VAR.MANUAL AT PERIOD END.

// LOGIC BEGINS

<*WHEN DATASRC >

<*IS INPUTSCHEDULE>

<*WHEN ACCOUNT>

<*IS MANUAL>

<*REC(EXPRESSION=0)>

<*ENDWHEN>

datasource name for input is INputsched

account - variable

Edited by: sapgirl on May 4, 2011 9:28 AM

Edited by: sapgirl on May 4, 2011 9:38 AM

Hi Sapgirl,

try this script:

//WE WANT TO REVERSE THE ENTRY FOR COST.VAR.MANUAL AT PERIOD END.

// LOGIC BEGINS

*WHEN DATASRC

*IS INPUTSCHEDULE

*WHEN ACCOUNT

*IS MANUAL

*REC(FACTOR=0)

*ENDWHEN

*COMMIT

cheers.

Hi,

You need to have a *COMMIT statement at the end of your script so that the result is written back to the database.

Hope this helps.

Goodmorning Nilanjan,

I did try this code . What it does is when I enter 100 it changes to (100) which is -100 but it doesnot change it to zero.

If it doesn't change to zero my balances are still going to be off.

Please advise.

Hi,

Thats strange. You are using expression=0. Right?

Try to optimize your application.

Yes used expression=0 and

Factor=-1

Hi,

I am not sure what are you trying to do. But, you need to understand the code before using it, instead of doing it blindly. It wont take you anywhere, if you dont understand what you are doing.

expression=0 will post a value of 0. Whereas factor =-1 is going to multiply the original value by -1. And that's the reason you got -100 after sending 100.

Please share the entire code once again.

I totally understand the code and I am using either of the two to see if i get anything different . but get exact same result.

All I want it to do is no matter what the value is in Manual it should change it to zero.

so factor is no good.

but expression is not bringing correct results.

I am able to get 0 by using a datapackage, I can run that on period end and am using same accounts, but wanted to put this logic in default.lgl

<*WHEN DATASRC >

<*IS INPUTSCHEDULE>

<*WHEN ACCOUNT>

<*IS MANUAL>

<*REC(EXPRESSION=0)>

<*ENDWHEN>

<*COMMIT>

Hi,

To run it in default logic, try this:

*XDIM_MEMBERSET ACCOUNT = MANUAL
*XDIM_MEMBERSET DATASRC = INPUTSCHEDULE
*WHEN ACCOUNT
*IS *
   *REC(EXPRESSION=0)
*ENDWHEN
*COMMIT

Hope this helps.

Nilanjan,

Thanks for helping me so much.

It is so strange so far I have tried all the options you gave me. but result doesn't seem to change at all.

It stays the same if its negetive value stays negetive if its positive says positive.

I tried your code and optimized again and checked the report nothing changed.

*XDIM_MEMBERSET ACCOUNT = MANUAL

*XDIM_MEMBERSET DATASRC = INPUTSCHEDULE

*WHEN ACCOUNT

*IS *

*REC(EXPRESSION=0)

*ENDWHEN

*COMMIT

Hi Nilanjan Just wanted to Thankyou for all the help.you deserve double points for not giving up trying...

My problem is finally resolved . It was a small mistake.

I had a parent member <2011.FP12 > along with all basemembers <2011.FW49,2011.FW50> in the TIME dimension .

NOTE: To all my forum friends the logic below works.Please note if you are using it in DEFAULT.LGL make sure you run default formulas datapackage . otherwise the changes wont take affect.

How do I automate this nilanjan instead of running it should i just schedule the default formula at period end ??

//WE WANT TO REVERSE THE ENTRY FOR MANUAL INPUT AT PERIOD END.

// LOGIC BEGINS

*XDIM_MEMBERSET DATASRC = INPUTSCHEDULE

*XDIM_MEMBERSET ACCOUNT= MANUAL

*XDIM_MEMBERSET SCENARIO= ACTUAL

*XDIM_MEMBERSET CALWEEK = 2011.FW49,2011.FW50,2011.FW51,2011.FW52

*WHEN DATASRC

*IS INPUTSCHEDULE

*WHEN ACCOUNT

*IS MANUAL

*REC(EXPRESSION=0)

*ENDWHEN

*COMMIT

Hi,

I am glad that your issue was finally resolved. This was my longest thread of answers till now.

You can give me double points by giving points to my other replies. .....Just kidding.

Take care

Answers (0)

Ask a Question

Top Q&A Solution Author

User

Count

Reverse an entry

Accepted Solutions (1)

Accepted Solutions (1)

Answers (0)

Re: Tables on purchase invoice

Re: Error while specifying intervals and posting r...

Re: Approaches for deploy from sandbox to producti...

Approaches for deploy from sandbox to production e...

Group Reporting Rule Based COI - TP calculation