Sales Totals for all months -- sql server reporting services

Josh
8/15/2006 10:38:51 AM

Ben,

There is a SQL trick... you need to do an OUTER JOIN on a reference
table that has all of the possible months.

Your table could look like this: (I will use CSV just to make it easy
to type)

Table: MonthReference
MonthNo,MonthName
1,Jan
2,Feb
3,Mar
4,Apr
5,May
6,Jun
7,Jul
8,Aug
9,Sep
10,Oct
11,Nov
12,Dec

Here is the table you are querying:

Table: Sales
MonthNo,SalesAmount
1,5000
3,7000
10,5500

(OK... So your sales aren't doing so good...)

SQL query should look like this:

SELECT
MonthReference.MonthNo,
MonthReference.MonthName,
ISNULL(Sales.SalesAmount, 0) AS SalesAmount
FROM
MonthReference
LEFT OUTER JOIN
Sales
ON
MonthReference.MonthNo = Sales.MonthNo
ORDER BY
MonthReference.MonthNo

The OUTER JOIN gets you 12 records instead of just 3. The ISNULL
obviously changes the NULLs to 0.

You could either have someone build you a reference table with all the
months, or you could create that from a query that has at least one
record from each month...

SELECT DISTINCT
MONTH(dateField) as MonthNo,
CASE
WHEN MONTH(dateField) = 1 THEN 'Jan'
WHEN MONTH(dateField) = 2 THEN 'Feb'
END AS MonthName
FROM
SomeTable
ORDER BY
MONTH(dateField)

Hope this helps!

-Josh

[quoted text, click to view]

blabore
8/17/2006 3:09:02 PM

Josh,

Thanks for the reply. Unfortunately, the sales totals don't exists in my
data, so I need to use an aggregate function. Based your comment, I've tried
the following (which doesn't seem to work):

CREATE TABLE #TempMonth
(
MonthValue int
)

INSERT INTO #TempMonth
SELECT DISTINCT MONTH(Lead.DAte) FROM Lead

SELECT #TempMonth.MonthValue, ISNULL(COUNT(Sales.SalesId), 0) AS SalesCount
FROM #TempMonth LEFT OUTER JOIN Sales ON #TempMonth.MonthValue =
MONTH(Sales.Date)
GROUP BY #TempMonth.MonthValue

DROP TABLE #TempMonth

Am I not allowed to Join on an aggregate function?

-Ben

[quoted text, click to view]

Josh
8/21/2006 5:26:05 AM

Use a subquery to summarize your sales, then join to the subquery.

Something like this:

SELECT
MonthReference.MonthNo,
MonthReference.MonthName,
ISNULL(Sales.SalesAmount, 0) AS SalesAmount
FROM
MonthReference
LEFT OUTER JOIN
(SELECT MONTH(saleDate) AS MonthNo, SUM(saleAmount) AS SalesAmount
FROM salesTable GROUP BY MONTH(saleDate)) AS Sales
ON
MonthReference.MonthNo = Sales.MonthNo
ORDER BY
MonthReference.MonthNo

Basically, you are going to create a query that outputs sales totals
per month as if that was a table.

-Josh

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server reporting services

sql server reporting services : Sales Totals for all months