Losing the effect of a Left Outer Join when grouping

[btodag]

Dataset:
I have standalone CR11 running against multiple spreadsheet sources via ADO linked on date columns.
The target data is basically all sales volumes to customers by date and product ID. In many cases, a customer doesn't order every sku every month.
I've created a supplemental spreadsheet that has a date list including all possible interesting dates for years past a over the next year. Each date in that spreadsheet has an accounting period ID in the column beside it.

Target
I hope to have a report grouped by customer then sku with charts at each level showing volumes by accounting period and show all accounting periods on the charts, even those that have 0 corresponding volumes.

Successes so far:
I left outer joined the date list to the target data on a date field in each. Date List.Date ---> Target Data.Sales Date
Verifying the join, I ran a basic report grouping by accounting period and summed the sales volume. It shows all accounting periods including those with 0 volume, and shows a 0 beside that AP.

Failures
I added a group above the AP that was customer. I expected every customer to have every AP, but they only showed AP's where the customer had sales.

How do I get these to show all AP's for every customer, specifically on a stacked bar chart by date by SKU.
I worry that I need to make my left join reference table include all Customer x Skus x AP x dates and join on all of those columns to get it to show up correctly. There has to be the "right" way that I'm not thinking of.

Thanks for any help you can offer.
Brian

 

[ianoctdec]

Hi Brian,
<I worry that I need to make my left join reference table include all Customer x Skus x AP x dates and join on all of those columns to get it to show up correctly. There has to be the "right" way that I'm not thinking of.>
You are correct, that's how you have to do it. Maybe you don't need the AP on the driving table though.

Dana