If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Displaying NULL values as Zero

Started by bdjohnson1973, 28 Jun 2010 11:15:06 AM

Previous topic - Next topic

bdjohnson1973

I have a chart that pulls the previous 10 fiscal years.  If a year doesn't have a value it is left off of the chart.  Is there a way to tell the chart to display that missing year?

example

2001 - 10
2002 - 11
2004 - 3
2005 - 6
2007 - 10

I want to see 2003 as well.

thank you for your help

brian

tupac_rd

will this help, for the measure being used in the chart, if you change the expression as

IF ([data_item] is null) THEN (0.01) ELSE ([data_item])

not sure if using 0 instead of 0.01 would work too...

mvjcognos

check this once.......

by using variable it is possible like take a boolean variable....
case when (quantity is null) then (0) else quantity end

or

click on the measure check in the data format number missing or null values as 0

blom0344

This is a recurring issue that should take a little time to sink in. There is a distinct difference between null values (record exists, but certain value is null) and the dataset not returning each and every occurence of - for instance - year. In the last case evaluating null values does not work, since the required row/record is not returned in the first place.

The solution should lie in the model, making sure your query returns the year dimension, even if no fact exists for a given year.

A second solution involves working with a union that returns all years in the second part of the set with zeroes as dummy measures.

Sreeni P

Hi,

try to use 'colesce' function and add the value which is you want to show in the chart using IF