Search code examples
listcognos-11

Cognos HTML Output Generates multiple rows for each column

I have report that has 6 Query Calculation Columns in a List. Each column feeds from the same field 'Sale Number':

IF( 'Metric 1' IN ?pCompany?) THEN ( CASE WHEN ( [Metric] = 'Metric 1' ) THEN ( count( distinct [Sale Number] FOR [Metric], [Category] )) END ) ELSE ( 0 )

Each of these columns have the Detail and Summary Aggregation set to None. Calculation is working fine. However, the report is generating each row for a column that has data in it. Like Below: enter image description here

I tried changing the Detail and Summary aggregations to Default, Total But those options are adding the entire columns value into single cells and not consistent. Tried to Section and Group the Category field, but that just groups the Category name still repeating the no.of rows for each category.

Any idea on how to bring the output into single row for each category? I'm using Cognos 11.0.11 version.

Solution

  • I think I just got lucky and figured out a solution. Updated the query to use running-count instead of count:

    IF( 'Metric 1' IN ?pCompany?)
THEN
(
CASE
WHEN ( [Metric] = 'Metric 1' ) THEN 
( running-count( distinct [Sale Number] FOR  [Metric], [Category] ))
END
)
ELSE
( 0 )

    And changed the Detail Aggregation to Count Distinct while Summary Aggregation set to None.

    Got the output in a single row for each category. It doesn't work when I tried with only Count Distinct or running-count. It has to be a combination of both.