Challenge: Reporting census numbers when there are no entries on certain days

[Developer1]

I am creating a report for a store that deals in low volume.  That is, on certain days, they don't sell anything.  As it is now, when I create a report for sales, any date on which no sales were made simply doesn't appear.  Please see below- there were no sales on January 1,2,3,4,8,9 and 10 so those days don't even show up in the report.

Can anyone help me with a way to have the values for Pizzas, Hamburgers and Sodas show up all as '0' on days when none are sold?

Thank you.

Facility Name   Encounter Date   Pizzas   Hamburgers   Sodas
Office1            Jan 5, 2019     0             0                    1
Office1            Jan 6, 2019     0             0                    0
Office1            Jan 7, 2019     0             0                    0
Office1            Jan 11, 2019     0             0                    1

[adam_mc]

The way I typically do this is to effectively create the set of all combinations of Facility/Date and force in zeros as your sales for Pizzas, Hamburgers, and Sodas into every row.

Then, union this query with your query and you should end up with values (including all zeros) for every Facility/Date.

Hope this helps.
Adam.

[Developer1]

Hi Adam,

Thanks so much for the reply.  Sorry, I am relatively new to cognos.  How would I "effectively create the set of all combinations of Facility/Date and force in zeros as your sales for Pizzas, Hamburgers, and Sodas into every row"?  I really appreciate the suggestion.

[adam_mc]

Without knowing your model and database it's a little tricky to explain.
You want to generate a SQL statement that looks something like this:

select
    a.facility,
    b.date,
    0 as pizzas,
    0 as hamburgers,
    0 as sodas   
from
    DATES_TABLE a,
    FACILITY_TABLE b

So, a full outer join that gets you all the facilities you need combined with all the dates you need.
The Pizzas, Hamburgers, and Sodas will just be data items/query calculations with a default of zero - Make sure to set the aggregate on these to Total so that they will total correctly with the results you already have.

Again, hope this is something that can get you headed in the right direction.
Adam.

[Developer1]

Thanks, Adam.  That is very helpful.