IBM Support

Columns or rows missing from crosstab if they contain no data



If a crosstab row or column contains no data, it does not show up in the crosstab. This document describes a method of forcing all columns and rows to appear, whether they contain data or not.


Column and Row headings in Crosstab reports are determined by the result set of the query.


Relational Data Source.

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.

Steps:The following steps assume that both rows or columns could be missing. If you are concerned about rows-only or columns-only, you may skip steps 1-2, and create just the row or column data in step 3.

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.

[{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU053","label":"Cloud \u0026 Data Platform"},"Component":"Query Studio","Platform":[{"code":"PF010","label":"HP-UX"},{"code":"PF033","label":"Windows"}],"Version":"1.1;8.1;8.2","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU053","label":"Cloud \u0026 Data Platform"},"Component":"Report Studio","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SUNSET","label":"PRODUCT REMOVED"},"Business Unit":{"code":"BU053","label":"Cloud \u0026 Data Platform"},"Component":"ReportNet","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}}]

Historical Number


Document Information

Modified date:
28 November 2022