value doesnt display if there is a null in calculation

[philelmousse]

Hello,

I create some calculations but the values doesnt display if an item is null. The only solution is to make nullable test in the calculation ? Thats crappy :/

[CognosPaul]

Not crappy, but standard. Every database behaves this way.

1 + 1 == 2
1 + 0 == 1
1 + null == null
if ( null == null) then (1) == null

[philelmousse]

yes but is there a function to do it the best way ?

[CognosPaul]

Depending on where you're trying to do it.

If you're trying to do it in a layout calculation, you need to do something like:

'Country: ' + case when paramDisplayValue('Country') is null then 'Not Selected' else paramDisplayValue('Country') end

Try using the following if it's in a query.

Code Select Expand

coalesce([NS].[Table].[ID],[NS].[Table].[ID2],0) will return the first field which is not null.

Depending on how tight and optimized your SQL needs to be you could also use NVL, or ISNULL depending on the database you're using. COALESCE should work for 95% of cases though.


EDIT:

It's also worth mentioning that this behaviour can be exploited when building reports. For instance, instead of showing Country: Not Selected, you could simply not show that label at all. I have several analytical screens with many many possible options. As users select parameters the labels show up on the bottom of the screen.

[Lynn]

Another option is to set the missing values character as part of the data format property. This is reasonable if you just need to the display something rather than the null. It doesn't change the underlying value so wouldn't be appropriate if you want a null transformed to a number for further calculations.

In a list, for example, click on the column and then select the data format property. Set it to number and scroll down to the Missing Value Characters property and put in whatever you want, such as zero.