CODEWITHSUNDEEP
oracle-databasebusiness-ruleshyperionessbase
Daniele
Daniele

Reputation: 1

PBCS: Custom rules to aggregate Period members

I have an input account (never share) in which the user types a parameter for each month, I want that into aggregate members of Period dimension, for example on YearTotal, the value will be the weighted average between two other accounts representing the cost and the quantity.

With the account properties I can rollup my account in addition or as simple average between months, obviously in this way I get wrong data in both cases.

Anyone know a solution to my question?

Thanks a lot, Daniele

Upvotes: 0

Views: 2092

Answers (1)

Arthur
Arthur

Reputation: 1704

Not sure exactly what you are asking. But I assume the following in my answer:

  • data entry for user on account Parameter (from the context, I think it is a price)
  • data entry for user on level0 Period, i.e. the months
  • you want Essbase to show the Parameter value as typed in at the month level (Jan .. Dec)
  • you want Essbase to show Costs / Quantity for Q1/2/3/4 and the YearTotal
  • the Account and Period dimension are of density: dense

You did not specify if you are also reporting on YTD values and how you have implemented this in Essbase. I assume you do, but the preferred solution depends on how you have implemented this, so I take the "safe" solution here:

solution 1 This is the most straightforward solution:

  1. Implement a "parameter_inp" account on which the user keys in the data. Set the account to "never consolidate".
  2. Create a new "parameter" account, dynamic calc, and give it the formula "Costs/Quantity;".
  3. Refer to "parameter" in your reports, and to "parameter_inp" for user entry

solution 2 - alternative If you have a lot of these parameters, you'll end up with a system making it unpleasant for data entry and reporting for the end-users. To solve it using data entry and reporting on the same "parameter" account, you need to tune your implementation for Quarter and YearTotal calculation, including the YTD calculation. I see no way of getting this correct if you are using DTS.

This is the way to go forward:

  1. Make use of a new dimension called "View", data entry on PER (= periodic), additional dynamic calc member "YTD", density: dense, place it after Period (so Account, Period, View)
  2. Add a UDA to the "parameter", for example "WA"
  3. Set custom dynamic calculations on Quarter and YearTotal level, something like: IF (@ISUDA("WA")) THEN ELSIF <check on FLOW/BALANCE> ... logic for regular aggregation of FLOW and BALANCE items hereby overriding Essbase's native time logic)
  4. Set custom dynamic calculations for YTD (overiding DTS), and make an exception for UDA "WA"

Upvotes: 0

Related Questions