Dollar breakout by month depending on duration

[edison1135]

I'm using Crystal Reports 7.0 to pull data from an Access database. The fields involved are:
Lead Number
Start Date
Dollar Total
Years Duration

The user would like a report that allocates the total amount evenly accross the months included as determined by considering the start date and the years duration.

For example, lets say a contract has a start date of 1 June 02 and a years duration of 1 yr, 2 months (14 months total), and has a total amount of $1400. If I run the report for calendar year 2002, Jan through May would show $0 for each month next to the lead number, but months June through December would show $100 for each month next to the lead number.

Then if I run the report for calendar year 2003, Jan through Aug would show $100 for each month next to the lead number, and Sept through Dec would show $0 for each month next to the lead number.

Sample of report for 2002

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
$0 $0 $0 $0 $0 $100 $100 $100 $100 $100 $100 $100

Sample of report for 2003

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
$100 $100 $100 $100 $100 $100 $100 $100 $0 $0 $0 $0

Thanks in advance.

Edison1135

 

[synapsevampire]

Crystal doesn't fabricate data, so you'd need to code all of this out by hand.

I always suggest that folk construct a Periods Table, which is helpful for reporting on dates that are non-existent.

I'll assume that you have a parameter for the year, and reference it as:
{?MyYear}

Build the formula to learn the monthly amount:
@MonthlyAmt:
{MyTable.Dollar Total}/ ({Years Duration}*12)

It's probably quickest to just build 12 formulas, though you might also build an array to accomplish storing this.

@Jan:
if month({Start Date}) = 1 and year({Start Date}) = {?MyYear} then
@MonthlyAmt

@Feb:
if month({Start Date}) <= 2 and year({Start Date}) = {?MyYear} then
@MonthlyAmt

etc.

That should get you there, I could do it more elegantly, but this works.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[synapsevampire]

Ooops, you might want and Else, as in:

if month({Start Date}) <= 2 and year({Start Date}) = {?MyYear} then
@MonthlyAmt
else
0

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[edison1135]

Thanks - I'll definitely take a look at your solution and let you know how I made out.

Edison1135