Edit 'no value' in crosstabs - Cognos Analytics dashboard

Question

Is there a way to edit the 'no value' in the cells of a Cognos Analytics (11.0.13) dashboard crosstab visualization? I would like to change it into 0.

Thanks in advance.

Answer 1

My answer consists of two parts.

The first is of some techniques to do what you want to do. The second is of a caution about you really really need to understand the data to know if the values should be reported as zero rather than null.

Part the first

It depends on your data source and the provenance of the object.

If you are using a data module you can set a setting for the query item to replace the null with a zero.

If you are using an expression you could use the coalesce function to substitute a value then the source value is null. This could be used in place of whatever measure you are trying to use in your chart.

For example coalesce ( {some query item}, 0)

Coalesce takes any number of parameters and will return the value of those in the leftmost position unless it is null and then return the value for the next parameter, unless it is also null etc.

I'm assuming that you are using a file directly in the dashboard. My advice is that it might be a good idea to put it into a module, do your modelling there so that it is available in all your dashboards and reports and you can use it in conjunction with other data sources. Also, if you have a multi-sheet or multi-file source you have the ability to know how the relationships between the sheets were defined and correct them if need be. I have not had entirely good experiences with the soi disant smart relationship generation in multi-sheet files but you're using 11.0.13 and I don't think it's in there. (I'm using 11.1.5)

Part the second

For purists out there: A null is not a zero. There is a difference between a null and a zero. It's difficult for people without maths degrees (such as me, who trained as an accountant) to understand that but I have a fairly simple example which I think can show where a value is null and where it could be truly zero.

Assume that you are doing a month by month sales report for a car dealer. This time last year (i.e. in April 2019) you could be selling 2019 model year cars and some 2018 model year cars. You were not selling any 2020 model year cars because they were not released yet. In that case, the value ought to be null. Over time the number of sales of 2018 year cars would possibly be 0 as you would no longer have them in inventory to sell. In this pretend report I have a sale of 1 2018 car in February of this year.

Now, your report for 2019 will have nulls for the sales of 2020 year cars before the autumn (I think that's when they usually release new cars).

It is possible that the number of sale for April this year will be zero.

Answer 2

@E. van Dongen,

There is a way to set 'Missing value characters' property to '0' on Col Format option in CA 11.1.5 version. This option may be available in CA 11.0.13 as well.