Getting CR9 to display months with no data 1

[Furious00]

I've done some searching around but I can't seem to find any recent discussion on this. I'm trying to build a bar chart that displays every month regardless of whether or not there is data for that month. I do not have a table that I can join to that has every month in it (for an outter join).

Does anyone know of an effective way to create this chart?

TIA!

 

[vidru]

Since you're using CR9, you might be able to fake this using a SQL command, but it would be database dependent. Here's a simple example that works for SQL Server.

Single table -> Sales
Want to show total sales by month for the year 2003.

I'd enter this for my SQL command:

select Cal.MonthNum, Cal.MonthName, Sales = ISNULL(SUM(S.DollarsSold), 0)
from
(select MonthNum = 1, MonthName = 'January'
     union
     select 2,'February'
     union
     select 3,'March'
     union
     select 4,'April'
     union
     select 5,'May'
     union
     select 6,'June'
     union
     select 7,'July'
     union
     select 8,'August'
     union
     select 9,'September'
     union
     select 10,'October'
     union
     select 11,'November'
     union
     select 12,'December') Cal 
LEFT OUTER JOIN Sales S on(datepart(month,invoicedate) = cal.MonthNum)
  AND S.InvoiceDate BETWEEN '1/1/2003' AND '12/31/2003'
group by Cal.MonthNum, Cal.MonthName
order by Cal.MonthNum

which returns:

MonthNum    MonthName Sales 
----------- --------- -------- 
1           January   31968.96
2           February  186.25
3           March     .00
4           April     .00
5           May       .00
6           June      123.85
7           July      195.86
8           August    1135.81
9           September .00
10          October   .00
11          November  .00
12          December  .00

Note the exclusion of a WHERE clause, as that would cause it to only filter out months with sales. By including my condition as part of the join, it returns all months.

Hope that gives you some ideas...

-dave

 

[Furious00]

Thanks!

Someone from my office came up with the same solution and I just wanted to let others who may search on this that it works brilliantly!

 

[synapsevampire]

Consider building a Period table:

faq767-4532

This handles date concerns without code, and solves many other problems (standard data warehouse fare).

-k