Hi there, I'm using Crystal 2008 with SQL. I'm basically stuck, in that I can't think how I can go about creating a report that will show me what I need. I've been using Crystal for a number of years but this has stumped me.

I have line items like below....

Item     Start Date      End Date     Value

1        01/01/2007      31/12/2008   £100

2        01/01/2007      31/12/2008   £500

3        01/01/2007      31/06/2008   £100

All the above data is on one table, and basically shows items on one contract. The items on contract can be for different time periods, so some can end before others. I'm trying to get a monthly total that basically shows for each month, but doesn't count an items value if it's dropped off. I can simply take each line item, and divide the total by the number of months, so line 1 is £100/12, 2 is £500/12 and line 3 is £100/3

The report we'd like to see at the end of it is something like this.....

Item    Jan 08   Feb 08  March 08 April 08 May 08 June 08

1       8.33     8.33    8.33     8.33     8.33   8.33 

2       41.6     41.6    41.6     41.6     41.6   41.6

3       33.3     33.3    33.3     0          0        0 

Total 3.23    83.23   83.23  49.93   49.93  49.93

I'm just not sure how to go about this. Ultimately what I want is for the total at the bottom to basically show the grand total for many contracts (with many line items having different ending dates within them).

So as the months could stretch over a few months or a few years, I'm guessing a cross tab is the way to go. I just have no idea how I can start this to make it separate the values into each column.

Any help is very much appreciated,

Regards

Adam