# Database Discussions > Reporting Services >  Need help creating pie and line charts

## collie

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 




```
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

----------


## Island1

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

----------


## Island1

Did you ever get anywhere with this?  If you still need help, drop us a line!

Bill

----------


## collie

Hi,

I would appreciate if someone can please help me.

I need to create a line chart in reporting services 2005.

I have the results in sql_results.gif which i plot in line.png.

As can be seen in line.png then only 3 issue names are plotted.These 3 issues are the only issues that appear more than once in the results in fig1. I assume this is the reason only the 3 are shown.

However, isn't it more accurate to display all other lines also (one straight line for each issue)?

If yes then how can I accomplish this?

The line chart doesn't seem right to me as it is now.

Thanks

----------


## Island1

As the images are pretty hard to read, due to size, could you please tell me what you have placed in the following  settings of the  Chart Properties - Data tab:

1.  Values
2.  Category groups
3.  Series groups

Thanks!

Bill

----------

