Date Columns that read from a table of periods

[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}

 

[IanWaterman]

It would be better if you had a table which replicated the data you described. Trying to do it with parameters will be very difficult. You would need 24 parameters or formulae to set up the period limits.

Ian

 

[RustySunset]

That was my concern. Also, the parameteres would have to be maintained as future years/periods are added to the table...

For some reason I thought I could include (link) the table in the report and reference the dates by period and year.

 

[lbass]

You could link the table to your main table where you use a >= join between {main.date} and {period.startdate} and a <= join between {main.date} and {period.enddate}. You could then directly reference the periods, as in:

{period.period} <= {?period} and
{period.year} = {?year}

The complication is when the dataset crosses years and your formulas have to select records from, e.g.,
period 4 of 2005 to period 3 of 2006, based on your parameter end period.

-LB

 

[RustySunset]

LB -
One thought though is that I don't have to reference the year or period and all of the starting and ending dates are in one table so couldn't I just link the table and somehow use the columns to look at the dates (starting and ending) to create a column period/date range dynamically?

I could put in 36 periods as defined dates and then just modify those once a year...It's a lot of work but not too bad if I only have to modify it once every 12 months.

Thanks for the brainstorming help.

 

[lbass]

What would be the point of using the table if you're not going to reference the periods and years?

-LB

 

[RustySunset]

LB - sorry about that. Those were two thoughts. One os use the table and the second and define the years and dates in each column.