Crosstab Report - Variable Columns

[Ron Bajda]

This is a request for further information on creating a
crosstab report with variable column headings.

What I want is the ability to create a report that has the
week of the year as column headings.

Further, I want to be able to use the underlying query to
limit the specific weeks that appear on the report.

(I have the query developed that asks for user input:
"Between [First Week] and [Last Week]" - and it works!)

For example: I might want only weeks 15 to 25.

But the next time that I run the report, I might want
weeks 18 to 33

Any thoughts

 

[Duane Hookom]

First of all, I would always display the same number of weeks in the report.
Creating a report with a variable number of columns increases your
complexity many fold.
Then, have a control on a form for the user to enter the ending date
(Forms!frmA!txtEndDate).
Set up your column heading in the crosstab as
ColHead:"Wk" & DateDiff("ww", [YourDateField], Forms!frmA!txtEndDate)
Add this control reference to your Query|Parameters
Forms!frmA!txtEndDate Date/Time
Set your Column Headings property to:
"Wk0", "Wk1", "Wk2",...
Your report can now be based on a moving window of weeks yet will always
have the same columns/fields.