Dear All,

I am using AS2005.

I have time hierarchy dimension like this one:
Time
[-] Year
[-] Semester
[-] Quarter
[-] Month
[-] Week
[-] Day

the Day attribute is using a full date key, ex. 1/1/2006.

I also have a [Measures].[ReceivableAmount].

I want to display a report like this (an aging/overdue receivable
report) :

1-15 | 16-30 | 31-60 | 61-90| > 90
------------|--------------------------------------------------------
Time | | | | |
---------------------------------------------------------------------


Would you help me, please?? May be using MDX or in designing the
fact/dimension table??


Thanks in advance,


Irwansyah

Hi there.

I am assuming that there is some way to determine whether a fact table
record falls into the different aging categories? Something like, if
16 days from a certain date it gets flagged as being in the 16-30 Aging
Range?

If this is the case, then its actually very easy to do this in your
ETL. Create a column in your fact table called 'AgingDimID' or however
you are naming your keys. Then during your ETL run your formula that
determines what category the record falls into (1-15, 16-30, etc.).
Populate the field with that key ID (you could use 1 = 1-15, 2 = 16-30,
etc).

Then create a dimension table with your ranges. It would look
something like this:

AgingDim
------------------------
ID | Group Name
1 | 1-15
2 | 16-30
3 | 31-60
4 | 61-90
5 | > 90

Then just create a dimension called Aging and hook it up to your cube
on the AgingDimID column. Then you should be all set. Post again if
none of this makes sense and I will try to explain further.

V

Thanks Voorshwa for the solution.


But what I want to do is this:
1. The report display the Period dimension hierarchy on the left, the
Aging dimension on the top and the A/R cummulative (based on the period
dimension) on the data columns. Like the illustration below:

2005 | 100 | 50 | 10 | 20
2006 | 110 | 60 | 20 | 10

2. If the user drilldown the Period dimension then the report should
change the A/R cummulative and place it on the appropriate aging
dimension:

2005 | | | |
Semester 1 | 80 | 20 | 5 | 10
Semester 2 | 20 | 30 | 5 | 10

The point is, the AgingDimID must be calculated/defined on the fly
based on the Period levet that get selected by the user.

My current solution is not different with the one you proposed except
mine duplicate the record from the previous date to the current date
and define where the record should fall in the aging category. But the
number of data growth exponentially, approximately 65 times the
original data.

I am a newbie in business intelligence business. I have tried to search
the solution for this problem on many web sites and news group but I
can't find one. Therefore I need help for this one.

Please forgive my bad English and my bad explanation.