Have Geek Brain

How do I add "missing" columns to a Column Group in Reporting Services?

I am trying to create a report that displays the number of items sold for each month of the year.

I have a query that returns a list of products 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 show a report that has a column for each month + a general 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 was able to do was use a matrix and column group (group per month) to display columns for existing data, if I have data from January to March, it will display the first 3 columns and the total. I would like to show all columns.

I've been thinking about this by adding the missing months to the SQL query, but I find it a little odd and I'm sure there must be some kind of "clean" solution, as it should be pretty common.

Thanks.

PS: I am using SQL Server Express 2008

+2


a source to share


2 answers 


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;
+3


a source


To do this in T-SQL you want PIVOT

, there is an example using mid-page months here .



+2


a source






More articles:


All Articles