Missing Values when downloading to Excel

[tlynnm4591]

I have developed a report in Report Studio 10. The report is rendering to HTML with all values displaying fine in IE 9:



However when downloaded to excel (2002 or 2007) it doesn't download the values below in red which is throwing off the totals:



The missing values are always the zeros under the AMEX AT Mode column. The code rendering these fields is:

CASE
WHEN [qAMEXATM].[American Express AT Mode] > 0 THEN [qAMEXATM].[American Express AT Mode]
ELSE (0)
END

This works fine to add in a zero to prevent the summary from trying to add a missing value in html. However, it appears to be outputting a missing value to excel  in some cases.

Any ideas why this would be working in html and not in excel?

I can not attach the specification for customer privacy issues.

Thanks,

[Lynn]

How about using the missing values data format property instead?

[CognosPaul]

The missing zeros are less worrying to me than the missing 16.75 and 34.9 in the upper right cells. Do they remain missing in other formats, such as PDF or CSV?

Can you trace the SQL to see if there is any difference between the Excel version and the HTML?

The case statement looks like it's a report expression. Is that correct? Do you have any other report expressions or variables in the list?

As Lynn said, for the zeros, try setting the data format to show missing values as $0.00. I don't remember off the top of my head if that will actually insert a 0 in the excel output, or just set the formatting the same way.

[twister]

A null value could spoil things. Please use COALESCE statement in the first condition.
Try using the following code and see whether it works,

CASE
WHEN [qAMEXATM].[American Express AT Mode] > 0 THEN COALESCE([qAMEXATM].[American Express AT Mode],0)
ELSE (0)
END

[tlynnm4591]

Thanks for your help everyone.....

Lynn - we tried the missing data value first. This causes more problems as Cognos will display the 0 if we set that as the missing data value but it will not add a zero resulting in all the totals missing on the html report.

CognosPaul - i would agree, but customers specification requires zeros, not blanks. Also, once the zero in the data is resolved, this will resolve the totals (see above to Lynn)

RESOLUTION: Twister - Coalesce instead of just using an IF 0 worked like a charm! The IF works fine for html, but the coalesce statement works for both html & excel. I will remember that for the future.

Thanks Twister & everyone!