Report based on crosstab query (2003 sp2)

Topic

New Reply

I have a report based on a crosstab query with ‘month’ as a column heading in the query. The query displays as many months as the data reports. The report has fields for all twelve months, but gives me errors when all the months are not in the data. Do I have to create the report with code rather than designing it? And if so how would I do that? Or is there another solution?

Reply | Quote

Replies

Does the attached database do what you want, i sourced this fro Hans a while back.

Reply | Quote

Actually Pat, I am going to use both of Hans solutions. The columns headings are sufficient to prevent the error when the report is opened and that’s really all I need, but the dynamic report I have got to do because it facinates me. I will be retiring next year so I will never get past scratching the surface of this stuff, but I have so thoroughly enjoyed it, and you guys are the best.

Pat, Hans, thanks again.

Reply | Quote

The database Pat attached shows how to create a dynamic crosstab report. If you simply want 12 columns, whether there are data or not, there is a simpler way: specify all 12 months in the Column Headings property of the crosstab query.

Reply | Quote

I found this in the search (wonders will never cease!) as it fits my needs exactly. The only problem I’ve got is that the properties for the Column Headings brings up the Qurey properties. The other fields show their properties though?

Ideas please.

Reply | Quote

Sorry, I don’t understand. What do you mean by “the properties for the Column Headings brings up the Query properties”?

Reply | Quote

When the properties box is open, & I select the field for Column Headings, the properties box only shows the properties for the query, not the field. All other fields are OK though.

Reply | Quote

Column Headings is a property of the query as a whole, not of an individual field. You can set this property to a comma-separated list of the column headings you want to include in the crosstab query, in the desired order. For example, if the column field for the crosstab query contains regions, you could enter the following in the Column Headings property:

“North”, “East”, “South”, “West”

The crosstab query will always use these four column headings, whether there are data for each value or not, and in the specified order instead of the default alphabetic order.

Reply | Quote

Doh!

Thank you for your patience.

Reply | Quote