Custom Charting and Tables

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)

Queue

Status

Ticket count

BPA

new

11

open

5

rejected

1

resolved

7

COO Projects

new

2

open

3

rejected

2

resolved

6

DI

new

8

open

3

resolved

1

stalled

1

Finance

new

6

resolved

5

IT BI Requests

new

12

stalled

1

IT Helpdesk

new

8

open

8

resolved

42

IT Projects

new

18

open

1

resolved

2

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

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