Setting in Cognos 10 Analysis Studio to set null = 0 in a crosstab?

[ChrisF79]

If a particular cell intersection is zero (imagine a sales rep hasn't sold a particular item), Cognos defaults to just an empty cell.  I'd like for that to be a zero instead of an empty cell merely for aesthetic reasons.  Is there a setting to tell Cognos to show zero for empty cells?

[MFGF]

Hi,

On the menubar, select Settings > Data Format and in the "Missing Value Characters" entry, put 0. This will show all null cells as zeros in the analysis.

MF.

[ChrisF79]

Good tip but that's just for each individual user.  How would I make that a global setting?  I want all users to default to that.

[MFGF]

Good tip but that's just for each individual user.  How would I make that a global setting?  I want all users to default to that.

Not aware of a global setting to do this, sorry. Is there an option to put zeros into the underlying source cube?

MF.

[ChrisF79]

In case someone stumbles upon this post later and wants to know the answer, I ended up putting in a ticket with IBM Support and found out the answer.  Unfortunately, this is a feature that is not supported by IBM so use at your own risk:

1. Open the <Cognos_BI_install_location>/configuration/qfs_config.xml file in a text editor.
2. If you are using the compatible query mode, perform the following steps:
a. Locate the following section in the qfs_config.xml file: <provider name="TM1OlapODP" libraryName="tm1odp" connectionCode="TM">
... <parameters>
<parameter name="ConvertNullCellsToUndefVals" value="true"/>
</parameters>
... </provider>

b. If the ConvertNullCellsToUndefVals line is not present, add it to the section.
c. The default value is true. To force missing data to be treated consistently as 0, keep this value as TRUE. This will affect all packages running in the compatible query mode.

3. If you are using the dynamic query mode, perform the following steps:
a. Locate the following section in the qfs_config.xml file: <provider name="TM1OlapODPXQE" libraryName="tm1odpxqe" connectionCode="TM">
... <parameters>
<parameter name="ConvertNullCellsToUndefVals" value="false"/>
</parameters>
... </provider>
b. If the ConvertNullCellsToUndefVals line is not present, add it to the section.
c. The default value is false. To force missing data to be treated as it was in versions prior to 10.1.0.1 and display a '0' character, change false to true. This will affect all packages running in the dynamic query mode.

If you have a configuration with multiple installations of Cognos BI sharing the same content store, make sure that all have the same setting. This parameter setting affects only Cognos BI. It does not affect Cognos TM1 applications such as Architect.