IBM Support

How to suppress zeroes on OLAP data source in Report Studio.

Troubleshooting


Problem

How to suppress zeroes on OLAP data source in Report Studio?

Resolving The Problem

With OLAP data source, do not apple an SQL relational filter like [Quantity sold]<> 0, instead, use OLAP (MDX) filters:

filter([great_outdoors_company].[Staff].[Staff].[Staff name],tuple([Total Quantity sold])<>0)

If the report contains nested measures then a more complex filter expression is necessary:

filter (set ( [Row members] ), tuple (currentMember ( [ Row Hierarchy] ), [Measure1], [Column Parent] <> 0 and
tuple (currentMember ( [ Rows Hierarchy] ), [Measure2], [Column Parent] <> 0 and tuple (currentMember ( [ Rows Hierarchy ] ), [Measure3], [Column
Parent] <> 0)

Test Case Report Spec:

Please see the steps below:

1. Load the attached 'Zero_suppression.txt' report specification in Report Studio.
2. Double click the staff name in the Rows zone of the crosstab
3. In the Definition Expression box, instead of using
[great_outdoors_company].[Staff].[Staff].[Staff name]
use
filter([great_outdoors_company].[Staff].[Staff].[Staff name],tuple([Total Quantity sold])<>0)

The 'Nested Measures Sample.txt' report specification provides an example of the more complex filter() function necessary when using nested sets and multiple measures. Open this report in Report Studio and double click the 'Product Name' on the crosstab rows and examine this expression.

Related Documents:

Writing Efficient Queries on OLAP Data Sources : http://support.cognos.com/supported/tti/public/docs/c ognos_pp_report_design_writing_efficient_olap_queries.pdf?lid=cognos_pp_report_d esign_writing_efficient_olap_queries

[{"Product":{"code":"SS9RTN","label":"Cognos 8 Business Intelligence"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Report Studio","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"1.1;8.1;8.2","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SS9RTN","label":"Cognos 8 Business Intelligence"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Analysis Studio","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SS9RTN","label":"Cognos 8 Business Intelligence"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Cognos Connection","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SS9RTN","label":"Cognos 8 Business Intelligence"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Report Studio","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SSQJXN","label":"Cognos ReportNet"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"ReportNet","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}}]

Historical Number

1019839

Document Information

Modified date:
15 June 2018

UID

swg21339689