Hello!
I'm quite new to Analysis Services.
I have a problem i can't solve.

Scenario:
A car rental company registers the history of each car. for each record there are two date fields, StartDate and ReturnDate.
A record in the historical table is:

Car Mercedes C200
Customer Company1
StartDate 12/12/2003
ReturnDate 01/12/2004
Quantity 3

This means that the customer rented 3 mercedes c200 from 12/12/2003 to 01/12/2004

Then, If I want to know at the end of Q4-2003 how many cars where rented in T-SQL I should write

SELECT….. FROM ……
WHERE startdate<=12/31/2003 and (returndate >=31/12/2003 or returndate is null)

Now I built a cube based on that table with the following dimensions and measures:

Dimension:
CarModel
Customer
StartDate
ReturnDate

Measures
Quantity

The question is: if I use as point of my analysis Q4-2003 and I want to see at that time how many car I rented, how can I filter or select the subset of the cars rented before Q4-2003 and returned after Q4-2003 (or not returned yet)?


Carmodel quantity start return
Mer c200 2 may-2003 feb-2004
Mer c200 6 dec-2003 mar-2004


If in the cube browser I try to filter the startime selecting q4-2003 my result will be just 6 but I had other to cars out rented before (then it should be 8).

I should be able to have the possibility to dynamically select the analysis point because the cube is browsed by crystal reports, so I have to insert something in the cube structure, like….calculated member?

Thanks in advance.