Columns or rows missing from crosstab if they contain no data

Resolving The Problem

Create separate queries for the column/row headings, and the data. Join these two queries with a 1..1 -> 0..n relationship so that even Columns and Rows with no data will be represented in the result set.

See the attached example written for the GO Sales and Retailers sample package. It is a simple crosstab filtered for 2004 data. There is no data for Mountaineering Equipment in 2004. The crosstab uses a joined query as described, and does contain a blank row for Mountaineering Equipment.

1) Create a "Column Query", containing only the column information and a dummy data item with a value of 1. In the attached example, this is named "Years"

2) Create a "Row Query", containing only the row information and a dummy data item with a value of 1. In the attached example, this is named "Product Lines"

3) Create a "Dimension Query" query that joins the queries from steps 1 and 2 on dummy. This requires that the Outer Join Allowed property of the query be set to Allowed. This creates a crossjoin that includes all possible combinations of rows and columns

4) Create a fourth query that contains the data for the crosstab. This is the same as a normal crosstab report.

5) Join the queries from steps 3 and 4, using cardinality of 1..1 and 0..n respectively. When dragging data items into this new query, ensure that you are dragging in the row and column headings from the "Dimension Query". This ensures that all possible rows and columns will be returned, even if there is no data associated with them.