Hi Cognos Experts,
We are using Cognos 10.2 and the report is based on dimensional cube designed in Cognos Transformer. The format of the crosstab report with revenue as a default measure is as follows.
Year 2014
Customer Product Line Revenue
When filter criteria was applied on ' Product Line' column to exclude NULL values I found the performance of the report to be acceptable.
Now I inserted 'Retailer Name' column between ' Customer' & ' Product Line ' and found the performance of the report to be deplorable. It took a long time for the report to finish. However when I applied filter criteria on 'Retailer Name' column to exclude NULL values, the performance of the report was acceptable.
Assuming this report had 5 columns, do I have to apply a filter criteria on each column to exclude null values.
If this report was based on a relational model then a filter criteria applied in the detail filter pane would ensure that NULL values are excluded from the report.
Is there a similar methodology for dimensional model reports so that the filter criteria can be applied once instead of repeating filter criteria on every column of the report.?
Please advise.
Regards
Vince
is there a solution to my request.?
Will using the suppression options on either rows, columns or both help you to achieve this? I do not find that applying suppression impacts performance at least with my data source(TM1).
Hi bi4u2,
Thanks !!! that's the most convenient method to suppress NULL values from a report however there are performance issues with this Methodology when compared to Filter() function. I compared two identical report and found that the report with the filter() function completed under 5 seconds whereas the report with missing values and zero suppressing completed in 25 seconds.
Solution accepted however if there is a better Methodology it would be gladly accepted.
Vince
I've not had filter() performance problems on Transformer cubes, in fact for me it performs better than the built-in suppression options. But of course it is somewhat slower than not having a filter() or using suppression.
To filter out the nulls, no you should not need it on every nested row item. I am assuming your expression looks something like filter([Product Line],[Revenue] is not null) based on your description. What is your column on the crosstab? Is it Year? (Formatting text is difficult in a post.) You might try putting your filter() expression on that instead of the rows, so your Year data item would be filter([Year],[Revenue] is not null). Not saying that will fix it, but it might have an effect.