crosstabwebi

How to display data in webi cross table?

I have the below data

F1  F2  F3
1   V1  11
2   V1  21
2   V2  22
3   V1  31
3   V3  33
4   V2  42
4   V3  43
5   V1  51
5   V2  52
5   V3  53

And I would like to display it as below:

    V1  V2  V3
1   11      
2   21  22  
3   31      33
4       42  43
5   51  52  53

However, the cross table in Webi displays as below:

    V1  V2  V3
1   11  11  11
2   21  21  21
2   22  22  22
3   31  31  31
3   33  33  33
4   42  42  42
4   43  43  43
5   51  51  51
5   52  52  52
5   53  53  53

Anyone could show me how to do that. Many thanks.

Andre

Solution

  • The object in the field of your crosstab (not the columns or row headers) needs to be a measure. Initially, I thought you could get around this by checking the "Avoid duplicate row aggregation" within the properties of your table, but that does not quite meet the objective.

    enter image description here

    To test this out I created a report based on FHSQL (free-hand SQL). Here is my SQL to generate your dataset...

    SELECT '1' AS [F1], 'V1' AS [F2], '11' AS [F3]
UNION
SELECT '2', 'V1', '21'
UNION
SELECT '2', 'V2', '22'
UNION
SELECT '3', 'V1', '31'
UNION
SELECT '3', 'V3', '33'
UNION
SELECT '4', 'V2', '42'
UNION
SELECT '4', 'V3', '43'
UNION
SELECT '5', 'V1', '51'
UNION
SELECT '5', 'V2', '52'
UNION
SELECT '5', 'V3', '53';

    Now that I actually had the exact data to work with I could experiment more fully. The first step is to create a variable that is the maximum of F3.

    [Max F3]=Max([F3])

    Place Max F3 in the field of your crosstab replacing F3. Now instead of checking "Avoid duplicate row aggregation" on the properties of your crosstab you need to check "Show rows/columns with empty dimension values".

    The table on the left shows the raw data. The table in the middle is the crosstab with the duplicated data that you get by default which you do not want. Finally, the table on the right is the crosstab using the Max F3 defined above and the "Show rows/columns with empty dimension values" checked.

    enter image description here