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.
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..