11 Replies Latest reply: Mar 16, 2012 3:05 AM by Mark Moran RSS

Graph To Show Number Of Incidents

Mark Moran

Hi all,

I have been asked to create a graph that will show the number of Incidents\Calls that we have taken over the past year. Additionally I would need to show how many per month and have the option to show how many per group. Is Reporting Services the best way to do this do you know or is there another way?

 

Cheers,

Mark

  • 1. Re: Graph To Show Number Of Incidents
    Rahul NameToUpdate

    Crystal report will be the best way to do this.  you will need to create custom fields in report to get the data you are looking for.

    Quickviews chart will not render the details you are looking for.

  • 2. Graph To Show Number Of Incidents
    Mark Moran

    Hi Rahul,

    I would rather not have to use Crystal Reports if at all possible. Would any of the built in reports give me the information that I require?

     

    Cheers,

    M

  • 3. Re: Graph To Show Number Of Incidents
    Rahul NameToUpdate

    you can start with Reports--> Incident Management --> “Incident Volume” build-in report and modify as per your needs.

    You certainly can use Reporting Services rather to go with Crystal report.

  • 4. Graph To Show Number Of Incidents
    Mark Moran

    Thanks Rahul,

    Would any kind soul out there already have an SQL Reporting Services report setup for what I want that they could send me?

     

    Many thanks,

    M

  • 5. Re: Graph To Show Number Of Incidents
    Julie Wynne

    Hi Mark

    I’ve only just started playing with SQL reporting, however, could you possibly grab the sql query from the Crystal Report and use that as the basis for your SQL reporting version ?

     

    Julie

  • 6. Graph To Show Number Of Incidents
    Mark Moran

    That is a good idea Julie but where does one see the actual SQL used? I have very little knowledge of either Crystal Reports or SQL Reporting Services.....they are sending me on a course here on Reporting Services after Easter thankfully.

     

    Cheers,

    M

  • 7. Re: Graph To Show Number Of Incidents
    Julie Wynne

    Open the report in Crystal Reports , select Database->Show SQL query

     

    Select the text and copy it to a text file (or just to the clipboard if you prefer).

     

    Then open sql report builder tool, add a new report with your SDE database as the datasource, and the query from the sql report as the dataset query.

     

    Have fun  !

     

    Julie

  • 8. Graph To Show Number Of Incidents
    Mark Moran

    Great, will give that a go tomorrow.

     

    All the best,

    M

  • 9. Graph To Show Number Of Incidents
    Mark Moran

    Hey Julie,

    When you say "Open the report in Crystal Reports , select Database->Show SQL query" do you mean via the SDE or in a separate Crystal Reports application? I do not have any separate Crystal Reports application installed and when I open it via the SDE I don't see Database->Show SQL query anywhere?

     

    All the best,

    M

  • 10. Re: Graph To Show Number Of Incidents
    Julie Wynne

    Hi Mark

    Yes, I’m talking about the Crystal Report Designer.   You can install it from your SDE install media, and Support will give you the licence key if you don’t already have it, however, you may not want to do that if you’re not planning on using Crystal for anything else.    I’ve installed it on my Business Rules app server and generally do all my report building there.

     

    To save you having to install it, here’s the query for the report IncidentVolume.rpt mentioned earlier in the thread (with some parameter values filled in for the dates)

     

    SELECT "Incident"."Incident #", "Incident"."Open Date & Time"

    FROM   "RW319"."_SMDBA_"."Incident" "Incident"

    WHERE  ("Incident"."Open Date & Time">={ts '2012-03-01 00:00:00'} AND "Incident"."Open Date & Time"<{ts '2012-03-16 00:00:00'})

     

    The RW319 is just the name of the database the report was developed on so you can substitute your db name for that.   The two {ts ‘datetimestring’} values probably need to be replaced with  @FromDate and @ToDate , making those parameters to your new report in the SQL Report Builder tool.

     

    Of course, that’s a very simple query, and the real work will be in creating all the totals, grouping etc – I haven’t got that far with my SQL RS reports yet so I can’t really help you with that

     

    Julie

  • 11. Graph To Show Number Of Incidents
    Mark Moran

    Many thanks Julie for all your help