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
Related Information
- 1019839_3.zipHistorical Number
1019839
Was this topic helpful?
Document Information
Modified date:
28 November 2022
UID
swg21339689