Null Value Customers and Null Value Months

[cognovice]

Hi,
I am trying to create a cross tab report that has Year and Months in columns and Customer account code in rows with sales value of customer for each month displaying for months.  When I generate the report, it only shows all the customers that have a value in the year.  I need to also show the customers that do not have a value as well (0 value customers). Similarly, the report shows all months from where the first sale was done ignoring the first previous months (eg:- first sale was done in May and the report does not show from Jan to Apr).  I want to  show all months even if the first sale was not done in Jan.

Hope I explained my issue clearly.  Looking forward for some assistance.

Report attached.

Kind Regards,
R

[hespora]

- create a query from your time dimension that returns all months
- create a query from your customer dimension that returns all customers
- in both queries, include a data item "join item" that is defined by a static value. '1' will do.
- create a query join that joins both queries on the join item. you now have a query that returns *every* combination of customer and month.
- outer join this query to your original data query on customer and month. for the sales query item, use "coalesce([dataquery].[sales],0)"

you now have a query that returns every combination of customer and month, with their correct sales value, or zero value if no sales.