Reporting and Analytics for Service

Get Involved. Join the Conversation.

Topic

    Pavol Procka
    Cross-Tab
    Topic posted June 20, 2017 by Pavol ProckaSilver Crown: 22,500+ Points, last edited June 20, 2017 
    190 Views, 8 Comments
    Title:
    Cross-Tab
    Content:

    Hi,

    I am trying to find some documentation for the Cross-Tab function but am only finding rather general info (e.g. https://documentation.custhelp.com/euf/assets/docs/may2016/olh/wwhelp/wwhimpl/js/html/wwhelp.htm#href=t_Define_a_cross_tab_report_ae1133085.html)

    Do I understand right that the Cross-Tab can only return totals? Is there a way to have it displayed Averages for example?

    Can anyone help with some more detailed documentation?

    Many thanks

    Pavol

    Version:
    Service Cloud

    Comment

     

    • Mike Griffiths

      Hi Pavol,

      Cross Tabs can output a variety of things, not just totals, though whether the output is actually meaningful is another thing! Cross Tabs can also be quite fickle to work with and sometimes difficult to output data in a suitable manner.

      What sort of reports are you looking to create with Cross Tabs? Perhaps we can assist in creating one for you that you can use as an example for others.

      Regards,

      Mike

    • Pavol Procka

      Hi Mike,

      Basically, I have the attached report and am trying to get the following information on one page / report. Currently I am exporting the report results to Excel and using a Pivot table to get the following details:

      Row labels (Incident Queues) Count of reference Average of time in Queue (HH:MM)
      Queue A 123 10:00
      Queue B 456 20:03
      Queue C 789 30:00
      Grand Total 1368 20:01

      I was hoping I could use the Crosstab on the existing report to pull the info similarly as with the Pivot.

      Your help would be greatly appreciated.

      Many thanks

      Pavol

    • Mike Griffiths

      Hi Pavol,

      I think it may be difficult getting the results you want using that report as a basis, largely due to its complexity. There are also other problems as well, such as the Time in Queue variable, which won't allow you to use the avg() function as you cannot next aggregate functions (and the variable contains max()). Similarly, you cannot use max() as a table join filter so filtering the records returned by that table is also not an option.

      That said, as you appear to be filtering for historical records, have you considered using the Inc_Performance table to get the queue duration? The "Queued to De-Queued" interval will give you a duration in each queue that a given Incident has been in, and you can average this also. I've attached a definition for a basic report that uses Cross Tabs with the Inc_Performance table to show the number of Incidents and average time in each Queue. Hopefully this is the sort of thing you're looking for. You can add and remove columns to this as necessary, just remember to adjust the columns in the Cross Tab column chooser otherwise they will not display.

      Let me know if this helps at all.

      Regards,

      Mike

    • Pavol Procka

      Hi Mike,

      I had a chance to have a quick look on the report you attached, unfortunately not yet to adjust it to my specific requirements, but it already looks promising. I will have a chance to play around with it on Monday and will definitely let you know how it goes.

      Many thanks

      Pavol

    • Pavol Procka

      I do however have one issue with the Inc_performance table. If I understand it correctly, the Queued to De-Queued interval does not really represent the response time?

      The problem is that some of our agents (and some other employees like team leaders and shift managers too) have the ability to move the emails between Qs even without responding to it. With Queued to De-Queued, would I not be also counting incidents that have simply been moved from the Qs I am looking at without a response?

    • Mike Griffiths

      Hi Pavol,

      Ahh, I didn't realise you were looking for a response time. As per Answer article 2240, the Queued to De-Queued interval shows the time the Incident entered a queue (start time) and the time it either left the queue or was set to Solved status (end time). This will therefore be the duration the Incident was in a given queue, irrespective of any responses or assignment changes made in that time. Individual response times are harder to calculate and how it's done would depend on exactly what you consider the start and end points to be (i.e. customer message in is a start point, agent response is an end point). I do have a simple report that uses Custom Scripts to give the intervals between various messages added to the Threads table if that's any use to you?

      Regards,

      Mike

    • Pavol Procka

      Hi Mike,

      I am packing up for today, will be back at work on Monday. If you could send me the report xml and I will have a look whether I can use it.

      just to add, in my specs, a Response Sent is the interval between an incident was Queued in the specific Q and the agent sent a response (I believe this is in the Transactions_type = Response Sent).

      Many thanks

      Pavol

    • Mike Griffiths

      Hi Pavol,

      No worries, definition attached. As there is a custom script, you'll be asked whether to keep the script on import so be sure to check the message and select the right answer on import, otherwise the code will be removed.

      For your Response Sent interval, Response Sent is the correct Transaction Type. The difficulty will be in tying a given response to a given queue change, especially when there can be a wide variety in the number of both queue changes and responses sent for each Incident. I'll consider how this could be a achieved.

      Have a good weekend.

      Regards,

      Mike