If you are unable to create a new account, please email support@bspsoftware.com

Author Topic: How to calculate percentage in a crosstab from Cognos Workspace Advanced 10.2.2  (Read 8018 times)


  • Guest

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

Offline hespora

  • Statesman
  • ******
  • Join Date: Nov 2015
  • Posts: 394
  • Forum Citizenship: +23/-0
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.
« Last Edit: 17 Oct 2017 05:26:32 am by hespora »