How to add "missing" columns in a column group in reporting services?
- by Gimly
Hello,
I'm trying to create a report that displays for each months of the year the quantity of goods sold.
I have a query that returns the list of goods sold for each month, it looks something like this :
SELECT Seller.FirstName, Seller.LastName, SellingHistory.Month, SUM(SellingHistory.QuantitySold)
FROM SellingHistory JOIN Seller on SellingHistory.SellerId = Seller.SellerId
WHERE SellingHistory.Year = @Year
GOUP BY Seller.FirstName, Seller.LastName, SellingHistory.Month
What I want to do is display a report that has a column for each months + a total column that will display for each Seller the quantity sold in the selected month.
Seller Name | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Total
What I managed to do is using a matrix and a column group (group on Month) to display the columns for existing data, if I have data from January to March, it will display the 3 first columns and the total. What I would like to do is always display all the columns.
I thought about making that by adding the missing months in the SQL request, but I find that a bit weird and I'm sure there must be some "cleanest" solution as this is something that must be quite frequent.
Thanks.
PS: I'm using SQL Server Express 2008
