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

Chart question

Started by CognosAdmn, 12 Mar 2010 12:07:40 PM

Previous topic - Next topic

CognosAdmn

hello there,

I have a bar graph report that has
- x-axis as month (for rolling 12 months)
- y-axis as count of products sold
- series Products (only three products available)
- I have a prompt to choose the products and locations. There are 2 locations and 3 products to choose from.

if all products for all the locations are displayed. the graphs returns correct data for the rolling 12 months.

but if any or all products for a specific location is chosen and that product was not sold at this location for a particular month, then the graph does not show the month. It skips to the next month. for some products at some locations i have only 1 month displayed in the graph because that was the only month the products was sold.

the end users would like to seee all the months, regardless of the products sold or not. instead of dropping the month, they would like to see a '0' for that month.



is this possible? if so, could someone please advise?

thanks so much in advance.

blom0344

There is more than 1 solution, but in general terms you will want all combinations in the resulting set. One way to achieve this is, is a union. The first part of the union fetches the 'real' data, the second one fetches all combinations with a zero as measure.

This requires 2 queries which are unioned to give a resulting third one as output (again setting an aggregate against the measure)

If crossjoin are allowed, it is fairly easy to generate the 2nd query as you take all locations, products and required months :

2 locations x 3 products  x12 months  = 72 records

Adding a zero to the real data has no effect on it , but does generate  zeros for months with missing products sold..