How to add "missing" columns in a column group in reporting services?

2
votes

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

2
sql-serversql-server-2008reporting-services

2 Answers

3
votes
SELECT Seller.Id,
SUM(CASE WHEN SellingHistory.Month = 'Jan' THEN SellingHistory.QuantitySold END ) AS Jan,
SUM(CASE WHEN SellingHistory.Month = 'Feb' THEN SellingHistory.QuantitySold END ) AS Feb,
...
GROUP BY Seller.Id

You can also use PIVOT(double check syntax, I think the following query is ok, but I haven't worked with transact sql for a while) :

SELECT Seller.Id, Jan, Feb, ...
FROM ...
PIVOT (SUM(SellingHistory.QuantitySold) FOR SellingHistory.Month IN (
  [Jan],[Feb],....)) AS t;
2
votes

To do this in T-SQL you want a PIVOT, there is an example using months midway down the page here.