How to display all columns in crosstab with section

[Maxime Pavy]

hi,

First, I'm french so sorry for my bad english.

In report studio i use Crosstab with section but for each section, i want display all columns ( columns come from distinct values of the variable i use for my croostab).

I think a exemple will be better :

 

-----------------------Source

Var       A | var         B | var        C   | Number |

-----------------------------------------------------------

A1            |   B1           |   C1           |   120
A1            |   B1           |   C2           |   130
A1            |   B2           |   C1           |   10
A2            |   B1           |   C1           |   17
A2            |   B1           |   C2           |   16

 

I make crosstab :

  - Columns : Var B

  - Row : Var C

  - "Values" :  sum (Number)

-  Section : Var A

 

So I have :

 

Section: Var A = A1

       |   B1    |    B2

---------------------------

C1   |  120   |  10

C2   |  130   |   0

 

AND :

Section: Var A = A2

     |    B1   

------------------

C1 |    17

C2 |    16

 

BUT I WANT :

 

Section: Var A = A2

      |     B1   |    B2

---------------------------

C1 |    17   |     0

C2 |    16   |     0

 

I don't know how to do that properly ( i have found a method where it is necessary to isolate each variable and cross themselves but it is long, gredy and ugly)

   

Best regard

[Maxime Pavy]

I have found the solution in a other Forum (i search since a long time but i don't use right key word):


"http://www-01.ibm.com/support/docview.wss?uid=swg21341708

Title : Columns or rows missing from crosstab if they contain no data

Problem(Abstract)

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.

Cause


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

Environment


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."