Consistent Monthly Headings on Crosstab Query
Posted on 2016-08-29
A user has requested a report that breaks down counts by month. They will be able to request the month span within a given year. Depending what they request there may or may not be totals for all months.
I have created a crosstab query for the data which has the counts for each month in the data pool. If they want the report for the months 01 - 06 of the year, my crosstab query will have those results for each month.
The issue is creating the report to show these totals. If they request a report for all months for a given year I need columns for every month 01 - 12. If I design the report based on the crosstab query with all months I get errors in the report when the user only requests data for month 01 - 03 because columns 04 - 12 don't exist.
I ran into this situation several years ago and requested help from EE. I'm pretty sure I was given a way to make sure the crosstab query creates column entries for all months, even if there is no data for a given month. I'm also pretty sure it had to do with setting up all possible column headings as properties in the query. Sadly that's all I remember and I don't have access to the application I created back then.
Does anyone know how to make sure a crosstab query generates a column for all possible entries, even if there is no data for the column? Once I have that I can build the report based on the crosstab query and I will not get errors for columns that have no data.