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