Showing multiple items value over a period of time

[lissa1974]

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 83.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

 

[Madawc]

Choose 'Crosstab' and it will guide you through. Though I'd suggest putting your dates as rows, not columns.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lissa1974]

Hi, sorry that doesn't really help, as when I go into cross-tab it asks the column and rows I want. If I use the start date it will only display the total under that start date. I want it to display the total evenly under each month for the duration of the line item.

Putting the dates as rows won't give the desired effect. I want to display the total for each month, and in columns looks a better way to display it.

Can anybody else offer advise?

Thanks

 

[lbass]

I think you'll have to do a manual crosstab, where you insert a group on item and then create one formula per month, as in:

//{@Jan}:
if {table.enddate} >= date({?year}, 1,1) and
{table.startdate} < date({?year},2,1) then
{table.amt}/(datediff("m",{table.startdate},{table.enddate})+1)

//{@Feb}:
if {table.enddate} >= date({?year}, 2,1) and
{table.startdate} < date({?year},3,1) then
{table.amt}/(datediff("m",{table.startdate},{table.enddate})+1)

Then insert sums on these formulas at the group (item) and report levels.

I added a year parameter, so you wouldn't need to update this yearly.

-LB

 

[lissa1974]

Hi there, that appears to be working in part, but the problem I have is that the items could span over different years (sorry, I forgot to mention this). So line item 1 above could be from 01/01/2007 to 31/12/2008, whereas line item 3 might be just for 01/01/2007 and end on 01/07/2007, meaning my totals for Jan-July 2007 would be higher, but when item 3 ends the total should decrease in July 2007 total to show.

So ultimatly, doing it in a cross tab I could have 36 months running across the top, but after the first 6 months line item 3 would be 0's for the remaining 24 months. I then need this totalling.

Thanks for your continued help

Regards

 

[lbass]

These formulas would work across years--you would just need more formulas and you would have to set up a base year for the parameter and then adjust the formulas like this:

//{@JanYr2}:
if {table.enddate} >= date({?baseyear}+1, 1,1) and
{table.startdate} < date({?baseyear}+1,2,1) then
{table.amt}/(datediff("m",{table.startdate},{table.enddate})+1)

An easier approach might be to create a table containing all potential dates, e.g., in Excel. Just add a date to a column, select hundreds of cells going down, and then use edit-fill to generate the dates. You can then link this new date field to the startdate using a >= join and to the end date using a <= join. Then reference only your Excel date in your record selection formula. Then you can insert a crosstab that uses the Excel date on change of month, item for the row, and the summary should be the sum of a formula:

{table.amt}/(datediff("m",{table.startdate},{table.enddate})+1)

-LB

 

[lissa1974]

Hi there, thanks for your response again. The excel option isn't easy as the dates in the table are stored as julian and so I convert them within Crystal to a normal date format, so I can't really link the excel date to the table date.

I've had some clarification on the report, and what they'd need is basically two years worth of data. The way you have done it above would give me 2 years worth, but I'd like it rolling if possible, so when you run the report it runs for the current month, and then 24 month forward. The titles would also need to change as they roll.

What you have suggested above works well, but for a static period, and I just need a little more flexibility.

I appriciate this is quite involved so once again thanks for your support.

 

[lbass]

Couldn't you create the dates in Excel as Julian and then convert that date also in CR (not sure--I'm not really familiar with Julian dates)?

Using the first method, instead of using a baseyear parameter, use a basedate parameter that defines the beginning month, use dateserial instead of date, and change your formulas to:

//{@Month3}:
if {table.enddate} >= dateserial(year({?basedate}), month({?basedate}+2,1) and
{table.startdate} < dateserial(year({?basedate}),month({?basedate}+3,1) then
{table.amt}/(datediff("m",{table.startdate},{table.enddate})+1)

Then create separate labels for each month:

//{@LabelMonth3}:
totext(dateserial(year({?basedate}), month({?basedate}+2,1),"MMM yy")

You would create formulas up to Month24, and adding 23 to the month in the last formula (dateserial will allow that).

-LB