I am trying to create a Balance Sheet snapshots at the end of each month.I have dimensions BS Jan,BS Feb, BS Mar, etc. I have years 1999-2015.
So to get the balance sheet as at the end of Nov, I want BS Nov 2008 to have Actuals from 1999 to Nov 2008 YTD.
Please help....anyone??
Need help with rules syntax
-
David Usherwood
- Site Admin
- Posts: 1454
- Joined: Wed May 28, 2008 9:09 am
Re: Need help with rules syntax
First, some assumptions/inferences from what you write:
a You have separate year and month dimensions
b The value against (eg) BS Mar is the _movement_ for the month
Based on that, I suggest the following:
a Create a 'sawtooth' hierarchy for YTD as
Feb YTD = Jan YTD + BS Feb
Mar YTD = Feb YTD + BS Mar
etc
b Add a Brought Forward element under Jan YTD
c Write a rule (and feeder) setting Brought Forward to be last year's Dec YTD
That should do the trick. Watch out though - this will create a feeder chain right through your cube and thus increase memory usage. There are ways of dealing with this if that's a problem - it may not be though.
a You have separate year and month dimensions
b The value against (eg) BS Mar is the _movement_ for the month
Based on that, I suggest the following:
a Create a 'sawtooth' hierarchy for YTD as
Feb YTD = Jan YTD + BS Feb
Mar YTD = Feb YTD + BS Mar
etc
b Add a Brought Forward element under Jan YTD
c Write a rule (and feeder) setting Brought Forward to be last year's Dec YTD
That should do the trick. Watch out though - this will create a feeder chain right through your cube and thus increase memory usage. There are ways of dealing with this if that's a problem - it may not be though.