CrossTab Queries and Reports

[DeutchBose]

I have a CrossTab Query that has Month/Year Values as Columns...the number of columns is determined by a user selecting a range on a form...i.e. All the data from january 04 - june 04.

Of course, depending on what the user selects, the number of columns change and the names of the columns change. How would I create a report that will adapt to the changing columns and column names of this cross tab query?

 

[Andrew Fergus]

You can get the names of the columns to conform to a format that you can pass through to a report but I don't think the report will handle a varying number of columns - the number and names of the columns are usually hard coded in the report.

The way to get the columns names consistent in the query (for use on the report) is by including the first date selected by the user as a row on the crosstab query and then coverting all of the dates in the column heading into a datediff value (from 0 to x) based on that first date. The columns of 0 through x are hard coded into the report but you can then reconvert the column heading labels back into real dates on the report using a dateadd function, again based on the first date passed through the query.

Would your users accept reports that are always a fixed number of months?

HTH, Andrew.