Efficient methodology to exclude NULL values from Multi Column crosstab report.

[vincydza@gmail.com]

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

[vincydza@gmail.com]

is there a solution to my request.?

[bi4u2]

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

[vincydza@gmail.com]

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

[bdbits]

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.