Month Chart

Question

Month Chart

ofir shoval

Ten Centuries

Points: 1278
More actions
July 10, 2005 at 7:34 am

#152458

I want 2 Show a Month Chart of Alarms.
I want that even in days with no Alarm(so won't be on the dataset) will be seen on the chart.
i tried to insert a col with the month days and do LEFT OUTER JOIN 2 the dataset but the Chart wasn't clear
Ideas???

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

First OUTER JOIN default mathematically to a NULL so doing Time related calculations with OUTER JOIN is not a good idea. Try these links to use Excel to create the chart and render it. If you use Excel it takes just a few lines of code to render it. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_xl2003_ta/html/Office_Excel_XML_Toolbox.asp

http://blogs.msdn.com/vsto2/

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie

ofir shoval Ten Centuries Points: 1278 More actions · Answer 2

Thanks but is there a way 2 do it with reporting services???

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

Resporting Services in an Asp.net Application, if you create the charts in Excel and save it, you can use the code in the link below to call your Excel file. Hope this helps.

http://www.dotnetjohn.com/articles.aspx?articleid=54

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 4

What was the exact problem with your left outer join attempt? If you had two tables, one containing a claendar the other the transactional data, then your approach should work. You may want to coalesc or isnull the 'alarm count' field to ensure that nulls (days with no alarms) come out as zero rather than null. I didn't test the code below but it hould work with minor enhancements :S

SELECT

c.theDateField,

SUM(COALESCE(t.theAlarms, 0)) AS Alarm_Count

FROM

myCalendar c LEFT OUTER JOIN

myTxns t

ON c.myDateKey = t.txnDateKey

GROUP BY

theDateField

ORDER BY integerDateKey ASC

Steve.