Need help creating pie and line charts
I need to display the results in a pie and line charts. I have looked at many examples and have wrecked my brains out trying to get a decent pie/line chart. I am doing something wrong. Maybe my sp is not written correctly or I am not dragging the correct values to the graph.
1. I have to show how many times the values were reported between dates selected by the user eg issue1was reported 2 times on the 19/02/2009 by Sarah Black and kb user and once on the 18/02/2009 by Support Agent.
2. In the line chart the user may want to compare how many times each DisplayName (i.e. agent) reported values between certain dates.
3. There are more options that the user might want to see such as how many times a certain value was reported between certain dates and the fluctuations.
Can someone please guide me on how to create a pie/line chart?
I am at a complete loss.
Thanks
Code:
ALTER PROCEDURE[dbo].[IM_ChartReport]
@app_id int
, @subject_id int
,@issue_id int
,@date_from datetime,
@date_to datetime
,@agent int
AS
BEGIN
if @subject_id=0
set @subject_id=null
if @app_id=0
set @app_id=null
if @issue_id=0
set @issue_id=null
if @agent=0
set @agent=null
select
count(r.subject) as 'total'
,s.subject as 'Value'
,convert(nvarchar(10),[Date_Reported],103) as date_reported
,users.FirstName +' '+ LastName as DisplayName
from
dbo.IM_Msg_Issue_Reported r
left join dbo.IM_Bugs b on b.bug_id=r.issue
left join dbo.IM_Application a on a.App_id=r.application
left join dbo.IM_Subject s on s.Subject_id=r.subject
left outer join users on users.id=r.agent_id
where (r.date_reported BETWEEN CONVERT(varchar(10), @date_from, 103)
AND CONVERT(varchar(10), @date_to, 103))
AND
(R.SUBJECT=@subject_id or @subject_id is null)
and (r.application=@app_id or @app_id is null)
and (r.issue=@issue_id or @issue_id is null)
and (r.agent_id=@agent or @agent is null)
group by
s.subject
,r.date_reported
,users.FirstName
,LastName
union all
select
count (r.application) as 'total'
,a.application as 'Value'
,convert(nvarchar(10),[Date_Reported],103) as date_reported
,users.FirstName +' '+ LastName as DisplayName
from
dbo.IM_Msg_Issue_Reported r
left join dbo.IM_Bugs b on b.bug_id=r.issue
left join dbo.IM_Application a on a.App_id=r.application
left join dbo.IM_Subject s on s.Subject_id=r.subject
left outer join users on users.id=r.agent_id
where (r.date_reported BETWEEN CONVERT(varchar(10), @date_from, 103)
AND CONVERT(varchar(10), @date_to, 103))
AND
(R.SUBJECT=@subject_id or @subject_id is null)
and (r.application=@app_id or @app_id is null)
and (r.issue=@issue_id or @issue_id is null)
and (r.agent_id=@agent or @agent is null)
group by
a.application
,r.date_reported
,users.FirstName
,LastName
union all
select
count(r.issue) as 'total'
,b.bug as 'Value'
,convert(nvarchar(10),r.[Date_Reported],103) as date_reported
,users.FirstName +' '+ LastName as DisplayName
from
dbo.IM_Msg_Issue_Reported r
left join dbo.IM_Bugs b on b.bug_id=r.issue
left join dbo.IM_Application a on a.App_id=r.application
left join dbo.IM_Subject s on s.Subject_id=r.subject
left outer join users on users.id=r.agent_id
where (r.date_reported BETWEEN CONVERT(varchar(10), @date_from, 103)
AND CONVERT(varchar(10), @date_to, 103))
AND
(R.SUBJECT=@subject_id or @subject_id is null)
and (r.application=@app_id or @app_id is null)
and (r.issue=@issue_id or @issue_id is null)
and (r.agent_id=@agent or @agent is null)
group by
b.bug
,r.date_reported
,users.FirstName
,LastName
order by Date_Reported
end
The result of this if the values are null and date_from=’02/02/2009’ and date_to=’03/03/2009’
Total Value Date_reported Agent
1 subject1 02/02/2009 kb user
1 Subject5A02/02/2009 Tom Agent
1 Subject5A02/02/2009 kb user
1 product1 02/02/2009 kb user
1 Product3 02/02/2009 Tom Agent
1 Product5 02/02/2009 kb user
1 issue 5 02/02/2009 kb user
1 issue2 02/02/2009 kb user
1 issue3aaa 02/02/2009 Tom Agent
1 issue2 13/02/2009 Tom Agent
1 product2 13/02/2009 Tom Agent
1 subject2 13/02/2009 Tom Agent
1 subject1 18/02/2009 Support Agent
1 subject3 18/02/2009 Support Agent
1 product1 18/02/2009 Support Agent
1 Product3 18/02/2009 Support Agent
1 issue3aaa 18/02/2009 Support Agent
1 issue1 18/02/2009 Support Agent
1 issue1 19/02/2009 Sarah Black
1 issue1 19/02/2009 kb user
1 issue3aaa 19/02/2009 Sarah Black
1 ISSUE4 19/02/2009 kb user
1 Product4 19/02/2009 Sarah Black
1 Product4 19/02/2009 kb user
1 product2 19/02/2009 Tom Agent
1 product2 19/02/2009 Sarah Black
1 product2 19/02/2009 kb user
2 product2 19/02/2009 Support Agent
1 issue2 19/02/2009 Tom Agent
1 issue2 19/02/2009 Sarah Black
1 issue2 19/02/2009 kb user
2 issue2 19/02/2009 Support Agent
1 issue 5 19/02/2009 Sarah Black
1 Product5 19/02/2009 Sarah Black
1 product1 19/02/2009 Sarah Black
1 product1 19/02/2009 kb user
1 Subject5A 19/02/2009 Tom Agent
1 Subject5A 19/02/2009 Sarah Black
1 subject3 19/02/2009 Sarah Black
1 Subject4 19/02/2009 kb user
1 Subject4a 19/02/2009 Sarah Black
2 Subject4a 19/02/2009 kb user
1 subject2 19/02/2009 Sarah Black
2 subject2 19/02/2009 Support Agent
Take a Look at My Chart Articles ...
Take a look at the continuing list of chart articles I publish within my Reporting Services series here at Database Journal.
Let us know if we can answer specific questions, once you understand the basics.
Good Luck.
Bill
