Dynamic Date Columns Formula???

[RustySunset]

I am trying to create a report that has amounts in columns by month. I will have twelve columns of data (no running total or grand total column) and need to be able to have the user enter an ending date which would control the date range of data selected. If they entered 06/30/07 they would get data for Jul-06 through Jun-07.

The transaction data is stored by date and there is several years of data (as in the example above. I have created the twelve formula fields for the 12 columns and am using the following formula with VERY limited success. It doesn't work when the year changes...

NumberVar Age_m1 := Month(DateSerial (Year ({?Ending Date}), Month({?Ending Date}) , 1));
NumberVar Age_y1 := Year(DateSerial (Year ({?Ending Date}), Month({?Ending Date}) -1, 1));

If Year ({tapVoucher.TranDate}) = Age_y1 And (Month({tapVoucher.TranDate}) = Age_m1)
Then {tapVoucherDetl.ExtAmt}
Else 0.00

Any help is appreciated. I am open to changes etc.

 

[Madawc]

Have you looked at Crosstabs? They do much the same thing, automatically. The only drawback is that they'd show nothing for a month that had no data.

For dates, you need a formula field something like

DateAdd("m", -12, @ParamDate)

First display and then use it for selection

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.


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

 

[BobHartung]

In your record selection formula you could have:

{Date_Field} >= ParamDate AND {Date_Field} <= DateAdd("m", 12, @ParamDate)

Then group by {Date_Field} for each month. You could then do a crosstab or a chart displaying your amounts on the change of {Date_Field}.

 

[RustySunset]

Madawc - I am using Crystal 8.5. I looked at cross tab reports but I have 4 columns of data that don;t have amounts associated with them and I was unable to get those and the vendor name on one line using a cross tab report. And you're tight, I'd need the month to show even if it didn;t have data.

I was thinking I would use a parameter field to set the ending date, or the date range, and then use that/those field(s) in my date formula in each column with a slight change so each column would pull one less than the previous column - starting from the right. I hope that makes sense...Thanks for the input.

 

[lbass]

I would just set up one conditional formula for each month like:

//{@month12}:
if {table.date} in dateserial(year({?endate},month({?enddate},1) to dateserial(year({?endate},month({?enddate}+1,1)-1 then {table.amt}

//{@month11}:
if {table.date} in dateserial(year({?endate},month({?enddate}-1,1) to dateserial(year({?endate},month({?enddate},1)-1 then {table.amt}

//{@month10}:
if {table.date} in dateserial(year({?endate},month({?enddate}-2,1) to dateserial(year({?endate},month({?enddate}-1,1)-1 then {table.amt}

//etc.

Place these in the detail section and right click on each and insert summaries on them at the group level. Then suppress the detail section.

-LB

 

[RustySunset]

Let me try that LB. THat seems like it would work just fine. I'll let you know.

 

[RustySunset]

LB - I tried the code and am getting an error of "The ) is missing" at the spot located below with the asterisk (*).

if {tapVoucher.TranDate} in dateserial(year({?Ending Date},month({?Ending Date},1) to dateserial(year({?Ending Date},month({?Ending Date}+1,1)-1 *then {tapVoucherDetl.ExtAmt}

Any ideas?

 

[lbass]

Sorry, let me try again. There were lots of missing close parens:

//{@month12}:
if {tapVoucher.TranDate} in dateserial(year({?Ending Date}), month({?Ending Date}),1) to
dateserial(year({?Ending Date}),month({?Ending Date})+1,1)-1 then {tapVoucherDetl.ExtAmt}

//{@month11}:
if {tapVoucher.TranDate} in dateserial(year({?Ending Date}), month({?Ending Date})-1,1) to
dateserial(year({?Ending Date}),month({?Ending Date}),1)-1 then {tapVoucherDetl.ExtAmt}

//{@month10}:
if {tapVoucher.TranDate} in dateserial(year({?Ending Date}), month({?Ending Date})-2,1) to
dateserial(year({?Ending Date}),month({?Ending Date})-1,1)-1 then {tapVoucherDetl.ExtAmt}

-LB

 

[RustySunset]

LB - That did the trick. Thank you very much for your advice. Here's the dn product for each of the 12 monthly columns that pull the data into the correct column based on the transaction date month.

//{@month12}:
if {tapVoucher.TranDate} in dateserial(year({?Ending Date}), month({?Ending Date}),1) to
dateserial(year({?Ending Date}),month({?Ending Date})+1,1)-1 then {tapVoucherDetl.ExtAmt}

//{@month11}:
if {tapVoucher.TranDate} in dateserial(year({?Ending Date}), month({?Ending Date})-1,1) to
dateserial(year({?Ending Date}),month({?Ending Date}),1)-1 then {tapVoucherDetl.ExtAmt}

//{@month10}:
if {tapVoucher.TranDate} in dateserial(year({?Ending Date}), month({?Ending Date})-2,1) to
dateserial(year({?Ending Date}),month({?Ending Date})-1,1)-1 then {tapVoucherDetl.ExtAmt}

//{@month9}:
if {tapVoucher.TranDate} in dateserial(year({?Ending Date}), month({?Ending Date})-3,1) to
dateserial(year({?Ending Date}),month({?Ending Date})-2,1)-1 then {tapVoucherDetl.ExtAmt}

//{@month8}:
if {tapVoucher.TranDate} in dateserial(year({?Ending Date}), month({?Ending Date})-4,1) to
dateserial(year({?Ending Date}),month({?Ending Date})-3,1)-1 then {tapVoucherDetl.ExtAmt}

//{@month7}:
if {tapVoucher.TranDate} in dateserial(year({?Ending Date}), month({?Ending Date})-5,1) to
dateserial(year({?Ending Date}),month({?Ending Date})-4,1)-1 then {tapVoucherDetl.ExtAmt}

//{@month6}:
if {tapVoucher.TranDate} in dateserial(year({?Ending Date}), month({?Ending Date})-6,1) to
dateserial(year({?Ending Date}),month({?Ending Date})-5,1)-1 then {tapVoucherDetl.ExtAmt}

//{@month5}:
if {tapVoucher.TranDate} in dateserial(year({?Ending Date}), month({?Ending Date})-7,1) to
dateserial(year({?Ending Date}),month({?Ending Date})-6,1)-1 then {tapVoucherDetl.ExtAmt}

//{@month4}:
if {tapVoucher.TranDate} in dateserial(year({?Ending Date}), month({?Ending Date})-8,1) to
dateserial(year({?Ending Date}),month({?Ending Date})-7,1)-1 then {tapVoucherDetl.ExtAmt}

//{@month3}:
if {tapVoucher.TranDate} in dateserial(year({?Ending Date}), month({?Ending Date})-9,1) to
dateserial(year({?Ending Date}),month({?Ending Date})-8,1)-1 then {tapVoucherDetl.ExtAmt}

//{@month2}:
if {tapVoucher.TranDate} in dateserial(year({?Ending Date}), month({?Ending Date})-10,1) to
dateserial(year({?Ending Date}),month({?Ending Date})-9,1)-1 then {tapVoucherDetl.ExtAmt}

//{@month1}:
if {tapVoucher.TranDate} in dateserial(year({?Ending Date}), month({?Ending Date})-11,1) to
dateserial(year({?Ending Date}),month({?Ending Date})-10,1)-1 then {tapVoucherDetl.ExtAmt}

 

[RustySunset]

Is there a way to get the date columns to read the date range parameter for each month from a database table? Here's the table layout:

Year Period Start Date Ending Date
2006 1 12/26/05 01/29/06
2006 2 01/30/06 02/26/06
2006 3 02/27/06 03/26/06
2006 4 03/27/06 04/30/06
2006 5 05/01/06 05/28/06
2006 6 05/29/06 06/25/06
2006 7 06/26/06 07/30/06
2006 8 07/31/06 08/27/06
2006 9 08/28/06 09/24/06
2006 10 09/25/06 10/29/06
2006 11 10/30/06 11/26/06
2006 12 11/27/06 12/24/06

The table will have multiple years and the months of each period will change each year. It is NOT a calendar month end.

I was thinking I could have each column pull 12 periods dynamically beased on either the ending period number and year combination or just by the ending date. Any ideas???

I am using the following sample code in each column:

//{@month1}:
if {tapVoucher.TranDate} in dateserial(year({?Ending Date}), month({?Ending Date})-11,1) to
dateserial(year({?Ending Date}),month({?Ending Date})-10,1)-1 then {tapVoucherDetl.ExtAmt}