cancel
Showing results for 
Search instead for 
Did you mean: 

How to carry over values differently (aggregated or not), or is it possible

Former Member
0 Kudos
59

Dear all:

In our Account Dimension, we have Beginning Inventory Qty, Ending Inventory Qty, Purchased Amt, Sold Amt, etc.

When we run reports with calculated time members, such as 2008.Total, 2008.H2, 2008.Q4, etc, most of our Account displays correctly with proper aggregation (like Purchased Qty, Sold Qty is summation of all child months'). But in particular for Beginning Inventory Qty and Beginning Inventory Amount is where I am having trouble.

For instance, for Ending Inventory Qty, we assigned ACCTYPE AST, so the report ignores aggregation and carries the last month's value to the calculated time member. However, for Beginning Inventory Qty and Amount, I wish to assign the values of the first month. If it is 2008.H2, then 2008.H2 Beginning Inventory Qty should be the same as in 2008.07. Another example would be 2008.Q4's Beginning Inventory Amount being the same as 2008.10's.

Is this doable in BPC?

Thank you!

Brian

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Brian,

It may be possible to design the opening balances in the way that you propose here. To get the correct results in your approach, you will be relying in large part on the cube and Analysis Services to figure out -- for certain intersections of accounts & time levels -- that it should look to the opening period rather than the standard time dim aggregation. I assume that would require some fancy MDX programming of a custom measure.

I would recommend a different approach -- in particular if you plan to use the consolidation engine business rules -- that you store the opening balances in each of the 12 months. This makes it easy for the time-dim aggregation: for Q3, it looks at Sep; for H2, it looks at Dec, and so on -- and this is exactly what the cube does naturally. Sure, it may feel like some data duplication for no valid business purposes, but in some cases we master the technology, and in other cases the technology masters us. This is one of the latter cases.

So then the question is, which opening balances? This can be tricky. It comes down to the question, are opening balances for each month = the closing balance from prior year December (or whatever your final period of the fiscal year is), or are they = the closing balance of the prior month?

BPC can handle it either way, and for a legal consol I would always recommend the first approach (prior year December). For a planning app it depends on the business requirements for B/S planning. The copy opening balances business rule is designed for the former. The account transformation rules can be used to handle the latter (and also support redirection across the flow-type dimension), with a source period = -1.

It also depends in part on your application's data storage type (YTD or periodic), particularly if you expect the system to create a cash flow statement for you, where the P&L data comes into play too.

As Petar mentioned, you should also carefully consider using a flow / movement / accdetail dimension. It's not always helpful, but if there are lots of accounts that you're tracking movements on, it can be worthwhile -- even if it means a lot of mapping of the ERP COA to the different movements. It's not intuitive for people who haven't spent a lot of time with their heads in the OLAP cube, and are used to a flat COA concept. It it can be very powerful, since the consolidation engine takes enormous advantage of this. It makes the setup of the "copy opening balances" logic rules very, very simple. And it could either complicate, or simplify, some report layouts.

Usually it's one of those dimensions (like datasrc) that customers only really start to appreciate after 6 months on the project.

Hope that helps.... and if you do succeed in your initial proposed approach, I'd be very curious to hear how you did it.

Regards,

Tim

Answers (1)

Answers (1)

Former Member
0 Kudos

I would suggest that you use a third dimension that is user defined and is the indicator for the FLOW. The process would then use the Account to aggregate correctly, but the flow dimension would store values for Opening, Additions, Removal, Ending Balance. That way, the components are tied to 1 account which will then aggregate correctly when reporting across time periods. This process works well for Cash flow accounts, and asset / liability accounts to avoid the aggregation issues you are observing. The addition of the dimension will change your current design and accounts would just be associated to a "No_Flow" member.

There is an example of the use of flow in the Legal Consolidation application in ApShell.

Former Member
0 Kudos

Peter:

Are you referring to the Legal Consolidation App Set under Administration Help? I read that section (including carrying over rules), but still am confused about how it is calculated. In other words, I can't picture how a new Dimension would help populate values differently based on the "flow" type "at the same time".

For instance, for my 2008.total, I have the followings:

Beginning Inv Qty --> carried over from 2008.Jan Beginning Inv Qty

Purchased Qty --> Sum of 2008.Jan-Dec Purchased Qty

Sold Qty --> Sum of 2008.Jan-Dec Sold Qty

Ending Inv Qty --> carried over from 2008.Dec Ending Inv Qty

Thank you!

Brian