Suppress Zero for rows and columns

Environment: Cognos Report Studio 8.2, SQL Server Cube 2005

Suppress zero rows and columns for crosstab reports built in report studio over SQL server cube can be quite a bit of pain, due to the fact that there is no in-built suppression facility.

You might be tempted to use the Suppression option from Query properties, but will be disappointed to know that it works only for SAP.

So, in order to suppress the rows or columns you can use following.

Solution:

Assume that you are showing LOCATIONS on rows, and MONTHS on columns. Your measure is UNIT SOLD. This is the easiest example I can think of.

You simply need to modify the rows and columns expression to below:

filter([myCube].[Location].[Location Names],([myCube].[Measures].[Units Sold])>0)

filter([myCube].[Date].[Months],([myCube].[Measures].[Units Sold])>0)

So Cognos will populate the rows and columns with only those members whose 'Units Sold' is not zero.

If your measure can have negative values too, for example: Revenue. Then you can use ABS() function which produces a positive value for all non zero values.

Suppressing can be more complex if you are displaying Multiple Measures. You want to suppress only those members for which all the measures are zero or null. In this case, you need to create a dummy measure which is an addition of absolutes of all measures.

For example, create a calculation as:ABS (measure1) + ABS (measure2) + ABS (measure3)

Now use this new calculation in your Rows and Columns expression to decide whether member should be hidden or not. This is faster than doing OR operation.

I hope this helps. Please leave comments or at least mark hit/miss. Cheers!!