Faced this issue when one of the client wanted to display all the dates in the date range even though there was no data present for that particular date ‘Column’. This client was very particular about the format of the report and what they wanted. There was no compromise on anything at all.
Following are the steps we need to follow and we can solve the issue.
1) Create a "Column Query", containing only the column information and a dummy data item with a value of 1.
2) Create a "Row Query", containing only the row information and a dummy data item with a value of 1.
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 cross join 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. In the join create 2 links, for both the data items (row and column). 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. (You can also find this solution in the COGNOS knowledge bank.)
