Nulls and Charts

[qberta01]

I created a crosstab report where product sales are displayed per day. Some products do not have any sales for some days. I set the crosstab to display missing characters as 0. However, I cannot get my line chart to do the same. What happens is the line ends where there is no data for a particualr day for the product. Then the line starts again for the next day where there are sales. Is there anyway I can set the line to be continuous?

I am using Cognos report studio v10.

Thx in advance!

Q

 

[blom0344]

This is not really a Cognos specific issue. It is basically a 'habit' of a typical relational model where only combinations are returned based on the actual fact. No fact for a given day means missing out on data (meaning not zero but non-existant: null)

There are typically 2 strategies:

1. Outer joins

Change the model and introduce outer joins where they are needed. This is feeble as it may influence other reports and adding filters may compromise the outer join strategy

2. Use unions

Use a union where you fetch a dummy record in the second set with a fact value of 0 (zero) for ALL possible dates.
Since the report uses the aggregate on the fact, you will have simply more combinations , but with the right factual data.

The problem with the union construction is the amount of combinations for a report that uses more than one dimension to display data. For just dates this is perfectly possible, but make sure to fetch dummy values for other dataitems as well (proper datatype, proper number of dataitems between union sets

Ties Blom