Results 1 to 5 of 5

Thread: Need help creating pie and line charts

  1. #1
    Join Date
    Oct 2003
    Posts
    3

    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

  2. #2
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    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

  3. #3
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    Following Up ...

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

    Bill

  4. #4
    Join Date
    Oct 2003
    Posts
    3

    Line chart problem

    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
    Attached Images Attached Images

  5. #5
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    Quick Question ...

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •