How to calculate percentage in a crosstab from Cognos Workspace Advanced 10.2.2

[LinaH]

Hi,

I am making a crosstab in the Cognos workspace advanced 10.2.2 representing the number of issues per category and Business Unit.
I want to calculate how much percentage the number of issues represent per Issues category (See the row "%")

Columns: Issues category
Rows: Business Unit

                          Issues category 1        Issues category 2          Issues category 3        Total

Business Unit 1            2                                 6                                  10                      18
Business Unit 2            10                               1                                   30                     41
Business Unit 3            5                                 20                                 2                       27

Total                           17                               27                                 42                      86
%                               20%                            31%                              49%                  100%


How can you get the calculation in percentage on the row % ? Is there any SQL query we can enter to get the calculation in %?

Thank you  in advanced

[hespora]

i've built this in report studio (just so I could replicate your values), but I don't see any reason why you should not be able to do this in WSA:

1. pivot your IC into columns
2. make sure the resulting crosstab has no default measure. you want your measure sitting as a crosstab node member in the rows. (refer to screenshot attached)
3. drag a copy of your measure into the crosstab, below the existing node members. this will be your total.
4. drag a query calculation into the crosstab, again below the existing node members, for the percentage. define the calculation as

Code: [Select]

total ( [ct] for [ic] )
/
total ( [ct] for report )

/edit: I just realized I skipped over the total column. For that, just select the column crosstab node member, and in the menubar select summarize -> total. Make sure you select total rather than automatic summary, as the percentage calculation otherwise will not roll up correctly, and to the best of my knowledge, the aggregate functions of a query item cannot be manipulated in WSA.