RustySunset
Technical User
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}
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}