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

How to get 0 if we don't have data

Started by Sarayucog, 03 Feb 2012 03:48:18 AM

Previous topic - Next topic

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

"Honorary Master of IBM Links"- MFGF
Certified IBM C8 & C10 Admin, Gamer, Geek and all around nice guy.
<-Applaud if my rant helped! 8)

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)