OutlookSoft 4.2 SP3: Copy Package on DataSrc dimension

[Former Member]

Greetings,

I'm trying to use Copy package to copy previous month's balances for balance sheet accounts into a open balance member of data source dimension. My data source dimension has following members: Total adjustments has two children: input and opening balance.

I am not able to copy total adjustments ( calculated member) into next months opening balance member. I can only copy non calculated data source members. Is there a workaround?

Thanks,

Roman

[Former Member · Accepted solution]

Not with the standard DM package. These work in the SQL tables so you can only reference base level records. The options would be to either setup a specific package for the balance roll-forward that lets you take multiple datasrc and map them into one or use modeling logic to perform the task. Modeling (aka scripting) would be the simpler approach and would not be much coding.

[Former Member · Accepted solution]

Thanks for the reply James.

Where can I learn more about modeling/scripting in outlooksoft?

Roman

[Former Member · Accepted solution]

The commands are documented in the BPC Administration on-line help guide in the product. I'll put together a short example of the basic code and post it later this evening.

[Former Member · Accepted solution]

Thanks, and example would help a lot!

[JHattendorf · Accepted solution]

Roman - one of the ways I've addressed this issue is to add a step in the DM package to execute an advanced logic package. Then, use the CALC_DUMMY_ORG function to capture the parent values in your script logic.

Here is a sample from the logic best practices document published last April.

// make sure you have all entities in memory

*XDIM_MEMBERSET ENTITY=<ALL>

// generate all parent values

*CALC_DUMMY_ORG ENTITY=PARENTH1

// use them as appropriate (note the # sign)

*WHEN ENTITY

*IS #SALESEUROPE

*REC(FACTOR=1/GET(ENTITY=#WORLDWIDE1), ACCOUNT=”SomeRatio”)

*ENDWHEN

Hope that helps!

Jeff

[Former Member · Accepted solution]

Here's what i found in Logic help for the CALC_EACH_PERIOD instruction.

Example:

//----------------------------------------------------

*CALC_EACH_PERIOD

*WHEN ACCOUNT

*IS OPEN_BALANCE, MOVEMENTS

*REC(ACCOUNT=CLOSING_BALANCE)

*REC(ACCOUNT=OPEN_BALANCE,TIME=NEXT)

*ENDWHEN

//----------------------------------------------------

The above sample logic performs a carry-forward of the closing balance of each period into the opening balance of next period.

I think this is exactly what I need. Is the sample above assumes separate open balance and closing balance account for each GL account? For example, GL account Cash has two children: Cash_open and Cash_closing.

[Former Member · Accepted solution]

As with any coding problem, there are many ways to do it and get the same results. The difficult part in writing code in a forum like this is not knowing the full requirements and design of the application. This is the basic way I would code this:

*CALC_EACH_PERIOD

*SELECT(%BS_ACCTS%,"[ID]","ACCOUNT","[ACCTYPE]='AST' OR [ACCTYPE]='LEQ'")

*XDIM_MEMBERSET ACCOUNT=%BS_ACCT%

*XDIM_MEMBERSET DATASRC=INPUT,Opening_Balance

*WHEN CATEGORY

*IS "ACTUALS"

*REC(FACTOR=1,DATASRC="Opening_Balance",TIME=NEXT)

*ENDWHEN

*COMMIT

The Calc_each_period orders the time ID's and moves through them in the order of the hierarchy from first to last. The select statement gets all accounts that have a balance sheet account type. Then it sets the scope to those accounts and the two data sources that how (what is assumed) to be the opening balance and the activity. The when statement only processes actuals (another assumption but ALL SQL logic requires the REC statement to be contained within a WHEN clause. For records that meet the above criteria, the value is copied into the next time period and the data source Opening_Balance.

So, if these are the input records:

Opening_Balance, Jan.2007, $1000

Input,Jan.2007,$50

Input,Jan.207, $25

The output would be

Opening_Balance, Feb.2007, $1000

Opening_Balance,Feb.2007,$50

Opening_Balance,Feb.207, $25

which would be summarized to:

Opening_Balance, Feb.2007, $175

[Former Member · Accepted solution]

I adopted the example to my application and it worked perfectly:

*CALC_EACH_PERIOD

*WHEN ACCOUNT.ACCTYPE

*IS "AST"

*REC (ACCOUNT=ACCOUNT.ID,DATASRC=OPENBAL,TIME=NEXT)

*ENDWHEN

Thanks for the help!

[Former Member · Accepted solution]

Your example is even better, James, as it limits the member set to actuals and balance sheet accounts.

Great help, thanks.