IBM Support

How to Hide Summary Rows when there is only 1 detail row in Crosstab based on a Cognos Powercube or DMR pacakge.

Technote (troubleshooting)


A Crosstab report that returns 1 detail row for most records resulting is the detail and summary row displaying the same value. Want to hide the summary row and display only the detail row when there is only 1.

Resolving the problem

Create a Query Calculation to serve as the Summary that counts the number of detail rows and returns NULL is only 1 is found.

Working with the following Sample report based on the Cognos Sample Package: Sales and Marketing (cube).


1. From the Toolbox, add a Query Calculation to the Rows of the report.

2. Select option 'Other expression'

3. Provide a Data Item name and define the Data Item Expression:
if (
(count(tuple([Revenue],[Prior QTD]) within set [Order method type])>1) or
(count(tuple([Revenue],[QTD]) within set [Order method type])>1) or
(count(tuple([Revenue],[QTD Change]) within set [Order method type])>1) or
(count(tuple([Revenue],[QTD Growth]) within set [Order method type])>1)
) then ( aggregate([Revenue] within set [Order method type]) )
else (Null)

Document information

More support for: Cognos Business Intelligence
Report Studio

Software version: 10.1, 10.1.1, 10.2, 10.2.1

Operating system(s): AIX, HP-UX, Linux, Solaris, Windows

Software edition: All Editions

Reference #: 1665844

Modified date: 12 September 2014

Translate this page: