[rt-users] Custom Charting and Tables

[Jeffrey Pilant]

Anton Panetta wrote:
>Hi There
>I'm curious if anyone else has attempted or found a way to achieve this.
>What I am
>Able to make is this (sort for the lack of formatting)
>
>                          Ticket
>Queue           Status    count
>                new       11
>                open      5
>                rejected  1
>BPA             resolved  7
>                new       2
>                open      3
>                rejected  2
>COO Projects    resolved  6
>                new       8
>                open      3
>                resolved  1
>DI              stalled   1
>                new       6
>Finance         resolved  5
>                new       12
>IT BI Requests  stalled   1
>                new       8
>                open      8
>IT Helpdesk     resolved  42
>                new       18
>                open      1
>                resolved  2
>IT Projects     stalled   1
>Total           0         154
>
>What id like to make is closer to this
>
>Queue    Open  New  Resolved  Stalled  Rejected  Total
>IT       1     5    10        2        0         18
>DI       2     5    10        3        0         20
>BI       3     5    10        2        0         20
>Project  4     5    10        3        1         23
>Total    10    20   40        10       1
>
>The Idea is to include it in a dashboard.
>I am aware that using the chat tool to make a table is a bit of a side way, I could possibly make do if I could make the tables run horizontal.
>Regards
>Anton

You need to make a query that returns 6 values per queue.
Pseudo SQL:
  select queue.name as queue, sumOpen, sumNew, sumResolved, sumStalled, sumRejected, sumTotal
    from queue
   where queue.disable = 0
    join (select count(*) as sumOpen, queue.id
            from tickets
           where tickets.status  = 'open'
             and tickers.queueid = queue.id)
    join (select count(*) as sumNew, queue.id
            from tickets
           where tickets.status  = 'new'
             and tickers.queueid = queue.id)
    join (select count(*) as sumResolved, queue.id
            from tickets
           where tickets.status  = 'resolved'
             and tickers.queueid = queue.id)
    join (select count(*) as sumStalled, queue.id
            from tickets
           where tickets.status  = 'stalled'
             and tickers.queueid = queue.id)
    join (select count(*) as sumRejected, queue.id
            from tickets
           where tickets.status  = 'rejected'
             and tickers.queueid = queue.id)
    join (select count(*) as sumTotal, queue.id
            from tickets
           where tickets.status  in ('open','new','resolved','stalled','rejected')
             and tickers.queueid = queue.id)

As I don't have easy access to the database, I am not sure I have the syntax or table names correct.  But this should give you an idea of what you need.

Basically, you need to gather the sum of tickets for each type by queue, and then list them all at once.  That needs multiple joins to achieve.

/jeff

________________________________________________________________________
The information contained in this e-mail is for the exclusive use of the 
intended recipient(s) and may be confidential, proprietary, and/or 
legally privileged.  Inadvertent disclosure of this message does not 
constitute a waiver of any privilege.  If you receive this message in 
error, please do not directly or indirectly use, print, copy, forward,
or disclose any part of this message.  Please also delete this e-mail 
and all copies and notify the sender.  Thank you. 

For alternate languages please go to http://bayerdisclaimer.bayerweb.com
________________________________________________________________________
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20150831/f58f5fd7/attachment.htm>