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