5 Replies Latest reply on Dec 10, 2010 12:18 PM by karenpeacock

    Report to show count of incidents at the Inprogress Status per Week against new

    Rookie

      I have been asked to produce a report which will show both the total amount of In Progress incidents (not at the resolved or closed status) against the total amount of newly created incidents by week over a 3 month period. Im looking to produce this within Crystal if this is possible? and how to go about creating the report?

       

      I can create a graph to show new Incidents per a week but im stuggling to find out how to show how many incidents were In Progres for that week, Ive attached a mockup of how i would expect the report to roughly look. Any assistance would greatly be appreciated.

       

       

       

       

      untitled.bmp

        • 1. Re: Report to show count of incidents at the Inprogress Status per Week against new
          afrogers Apprentice

          I would group the incidents by week then by status.  You can then insert summaries for each grouping and in selection expert, create a formula on the suppress option to only display the In Progress incidents.  Where it might get tricky is getting your chart to look right as I always find Crystal charts a real pain, good luck!

          • 2. Re: Report to show count of incidents at the Inprogress Status per Week against new
            Rookie

            That's exactly how i was going to approach the report initially, however the problem i am coming up against is that i need to look back at a particular week and see how many incidents at that time were in-progress like a snapshot, all i am able to show is if an incident is currently In progress rather than whether it would of been historically on a particular date if that makes sense?

            • 3. Re: Report to show count of incidents at the Inprogress Status per Week against new
              afrogers Apprentice

              I see, so you need to snapshot the amount of In Progress incidents at the end of each week against the amount created?  Retrospectively I don't see how you can do this, unless you resort to backups of the database from the end of week to get your figures.

              • 4. Re: Report to show count of incidents at the Inprogress Status per Week against new
                karenpeacock SupportEmployee

                Hi

                 

                I believe that by looking at the data in the audit trail for each incident you would be able to achieve this.  Here is some information on this table.  I would think that you would need to be fairly comfortable with Crystal / your reporting tool to write a formula to get this.  Alternatively, going forward, you could look at collecting this total on a perhaps daily basis and then storing it in a separate table for easy historic reporting.

                 

                If you need assistance with the above then your Account Manager would be able to give you some further details on the options available.

                 

                Best wishes

                Karen

                • 5. Re: Report to show count of incidents at the Inprogress Status per Week against new
                  karenpeacock SupportEmployee

                  Actually, thinking about this I am not sure that you will need to use the audit trail.  As long as your In Progress status straight after the incident is opened which is what it sounds like from your description.  If so, as you don't want to pick a particular status your incident was at you should just need the pm_process, im_incident, im_incident_resolution and im_incident_closure tables. 

                   

                  You can then write a formula to say if ?datexyz (the parameter on your report) is greater than the incident creation date but less than the resolution creation date or closure creation date then return a 1 else 0.  Then you could do a sum on this formula.  You would need to account for incidents that have not yet been resolved or closed when the report is run by using the right type of outer join and a formula to return something like 'not yet closed' when the incident is open then amend your other formula to ignore those records.  Then lastly you would need to know whether you sometimes might unresolve or reopen an incident and ensure that some logic is built into the report to deal with this.

                   

                  Best wishes

                  Karen