How to get 0 if we don't have data

[Sarayucog]

Hi All,

I have report Month in Columns and 3 measures in Rows. For this Cross tab report I have data from Mar 2011 to Dec 2011. So its displaying data from Mar 2011 to Dec 2011, but here my requirement is I have to display Jan 2011 and Feb 2011 month values as 0s (Zeros) also.

It means in which month I won't have the data those months values should display with zeros, but in database we don't have any values for those months.

Can anyone suggest me on this.

[HalfBloodPrince]

Try to set format as Date and then set properties Missing Value Character and Zero Value Character to 0.

[Sarayucog]

Thanks for your reply Prince.

I have tried the solution which you have given, but its not working.

Any ohter solution for this.

[charon]

so the requierement is to display two more columsn with no data in it?
2 suggestions...work in FM on the package/ etl and add columns to the table..
or edit 2 static columns with no entries, and edit in cognos the way he works with no data fields.

[mmcalli]

Left outer join between your time dimension table and your fact table. That way you'll get all months from your time dimension, and if there are no facts, you'll get NULL for the measures for those rows.

[Grim]

Not sure this applies, but here it is anyway...
http://www-01.ibm.com/support/docview.wss?uid=swg21341663

[saumil287]

Hi
You can also use if then else construct to display 0 in place of blank.

[blom0344]

This is not about trying to substitute non existing data with zeroes (or any other formatted output) For a relational package a crosstab will simply only show the grid of existing intersections.

If you fetch no data for 2 months (i.e. no data exists for these months based on query def.)
, these will automatically not appear in the crosstab. This is a bit confusing for those brought up with dimensional models.

In some cases the left outer join strategy will work. In other cases you may need to build the crosstab against a union of  'real data'  and a set based on a cross-join of the time dimension and fact related attributes where the measure is defined as a dummy 0  (zero)