I have a problem combining a joined query with a Page set in Cognos Report Studio.
Let's say I have monthly sales for August for different departements, listed out by day. When no data is available for a particular day this results in a NULL value. When plotting these sales in a figure, this has the undesired result that the figure does not plot all 31 days but only those where sales took place (this is undesirable because I want these non-sale days to grap my attention in the first place).
To force the figure to plot all 31 days in that month I created a joined query (with in one query the sales by department and in the other query the dates). This results in a nice graph plotting the 31 days and pulling attention to the reader to these non-sale days. Until here everything works fine.
Say I then want to add a Page Set to this set-up so that for each department a page is made showing these monthy sales. To this purpose I added the pageset and included a master-detail relationship between the figure with the joined query and the page set query based on departement_ID).
This is where things fale. The report then shows the different pages with a graph for each department, but the figure no longer plots the 31 days anymore (although the figure is tied to the joined query, and the figure also has a master-detail relationship with the Page Set based on department_ID).
I need the Page set and thus also the master-detail relationship (this because these page sets are then used for bursting reports to the heads of the departements) . So, I know how to get the join working without the page set, and I also know how to get a page set working, but for some mysterious reason combining the two doesn't work.
Anybody who knows what I do wrong here? Or are there easier solutions to force a figure plotting all days?
Any advice is welcome! Thanx
If you run tabular data for your query and look closely, you will see that every day is present due to your join. However, not every combination of day and department will be present, and that is why your different instances of the graph do not render all days.
You will need to run a) a query for all days, b) a query for all departments, c) crossjoin them, d) outer join that result to your actual data query.
Thanx for the fast reply! Can you be a little bit more specific on how to accomplish this? I would be really grateful.