IBM Support

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

Troubleshooting


Problem

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


Steps:

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)

[{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Report Studio","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"10.2.1;10.2;10.1.1;10.1","Edition":"All Editions","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 June 2018

UID

swg21665844