1 Reply Latest reply on Jun 29, 2009 4:55 AM by elizabethcombrink

    Crosstab report on Open Incidents by group & status

    Rookie

      I’m attempting to write a cross tab report to show Open Incidents per Support Group by Status.

      We used to have something similar in HelpDesk and I need to recreate this in Service Desk but I’m struggling.

       

      I’m looking for something similar to the table below

       

      Awaiting 2nd line responseOpenWith 1st LineWith 2nd lineTotal
      Comms

      2

      1

      0

      2

      5

      Desktops

      3

      3

      0

      3

      9

      Operations

      3

      3

      3

      0

      9

      Total

      8

      7

      3

      5

      23

       

      I can retrieve the Support Groups and Statuses but the counts of Incidents in the table do not match the data that is retrieved by our ‘Workload List’ queries

       

      LC_STATUS.LC_TITLE

      TPS_GROUP.TPS_TITLE

      What should go in here to give count of Incidents per support group for each status ?

       

      Should this be count of PM_PROCESS.PM_ID or IM_INCIDENT.IM_LATEST_ASSIGNMENT_GUID

      Or IM_INCIDENT.IM_CURRENT_ASSIGNMENT_GUID or something else ny assistance would be?

       

      Any assistance would be rgatefully received!

        • 1. Re: Crosstab report on Open Incidents by group & status
          elizabethcombrink Employee

          Hi Mandy,

           

          I often use crosstab reports in Crystal - and its so easy to get the criteria wrong and not realise because you don't actually see the data behind each total. Here are my steps where I first build the totals myself through grouping and count / sum /etc to validate, and then put the crosstab in.

           

          1 - Start off with a normal, blank report with  basic select statement to make sure I'm pulling the correct data back from the database.

           

          2 - Then put in grouping and counts to count the records you return.  (Remember to that you may sometimes want to use Distinct Count and not Count). Validate / reconcile the data to make sure you're getting what you want.

           

          3 - Put in a crosstab in the header, put the summaries in, and make sure that they correspond to the groupings and totals you calculated in the details.

           

          4 - Clean up the crystal report by deleting all the Groupings and things in the details sections, and minimise the different .

           

          PS Difference between current assignment and latest assignment becomes apparent at resolution / closure.  Every time the Current Assignment changes to a new assignment, the Latest Assignment is updated to be Current Assignment.  When ticket is resolved/closed the Current Assignment is null, but the Latest Assignment remains as the assignment which was active when the ticket was resolved/closed.