2 Replies Latest reply: Nov 13, 2012 3:25 AM by user6741070 RSS

    How to populate member values to Year and Period Dimensions

    user6741070
      Hi,

      I have set up the following members in account dimension:
      Lease From (Date Type)
      Lease To (Date Type)
      Lease Amount (Currency Type)
      If I input Jan-2012 in Lease From, Nov-2013 in Lease To and 1,000 in Lease Amount, all in BegBalance->Fy12, how can I populate the 1,000 to the correponding Period and Year dimensions using business rule. I need 1,000 in Lease Amount->Fy12->Jan all way through to Lease Amount->Fy13->Nov. I do not want to create a input form with all the years and periods for monthly input as I may have over 100 lease information with different from and to dates.

      Or is there any alternate method I can get the expected results.

      Thanks.
        • 1. Re: How to populate member values to Year and Period Dimensions
          Stuart Game
          Hi

          This is a really complex problem! It may help you to look at the documentation around the CapEx planning module as this is Oracle's take on calculating asset depreciation etc on a similar basis (create asset in BegBalance -> NoYear and calculate which periods to poulate depreciation data in using dates, variables and rules). The business rules are presented in full in the doc below but I warn you the code is complex and not easy to understand, although you may get the principles

          http://docs.oracle.com/cd/E17236_01/epm.1112/cpx_admin_11122.pdf


          You ought to be able to do something using dates and period / year indicies (remember dates are stored in Essbase in something like the format YYYYMMDD, e.g. 20121107 so should be easy to compare whether the date for the period is within range of the dates entered).

          I have also tried to do something similar using smartlists in a prototype before but this proved a bit more complicated and not necessarily any more reliable than using dates

          Hope this helps
          Stuart
          • 2. Re: How to populate member values to Year and Period Dimensions
            user6741070
            Thanks, Stuart, I have got idea from your suggestion and resolved my problem by using the formula expression TP_Index and YR_Index.