This is very specific to our list of components and statuses, and relies on
the WikiTableMacro plugin from https://trac-hacks.org/wiki/WikiTableMacro,
but I wrote a query a few years ago that is close to what you are looking
for, I think. This may have some MySQL specific syntax, but should be
close enough to be helpful.
{{{
#!SQLTable
select
s.component as Functional_Area,
concat('[query:?status=new&component=', replace(s.component, ' ', '+'),
' ', s.new, ']' ) as New,
concat('[query:?status=assigned&component=', replace(s.component, ' ',
'+'),
' ', s.assigned, ']' ) as Assigned,
concat('[query:?status=in_work&component=', replace(s.component, ' ',
'+'),
' ', s.in_work, ']' ) as In_Work,
concat('[query:?status=in_QA&component=', replace(s.component, ' ', '+'),
' ', s.in_QA, ']' ) as In_QA,
concat('[query:?status=info_needed&component=', replace(s.component, ' ',
'+'),
' ', s.info_needed, ']' ) as Info_Needed,
concat('[query:?status=resolved&component=', replace(s.component, ' ',
'+'),
' ', s.resolved, ']' ) as Resolved,
concat('[query:?status=closed&component=', replace(s.component, ' ', '+'),
' ', s.closed, ']' ) as Closed,
concat('[query:?component=', replace(s.component, ' ', '+'),
' ', s.total, ']' ) as Total
from (
select
component,
count( case when status = 'new' then 1 end ) new,
count( case when status = 'assigned' then 1 end ) assigned,
count( case when status = 'in_work' then 1 end ) in_work,
count( case when status = 'in_QA' then 1 end ) in_QA,
count( case when status = 'info_needed' then 1 end ) info_needed,
count( case when status = 'resolved' then 1 end ) resolved,
count( case when status = 'closed' then 1 end ) closed,
count(*) as total
from
ticket
where
component = 'ar'
UNION
select
component,
count( case when status = 'new' then 1 end ) new,
count( case when status = 'assigned' then 1 end ) assigned,
count( case when status = 'in_work' then 1 end ) in_work,
count( case when status = 'in_QA' then 1 end ) in_QA,
count( case when status = 'info_needed' then 1 end ) info_needed,
count( case when status = 'resolved' then 1 end ) resolved,
count( case when status = 'closed' then 1 end ) closed,
count(*) as total
from
ticket
where
component = 'auxiliary'
UNION
select
component,
count( case when status = 'new' then 1 end ) new,
count( case when status = 'assigned' then 1 end ) assigned,
count( case when status = 'in_work' then 1 end ) in_work,
count( case when status = 'in_QA' then 1 end ) in_QA,
count( case when status = 'info_needed' then 1 end ) info_needed,
count( case when status = 'resolved' then 1 end ) resolved,
count( case when status = 'closed' then 1 end ) closed,
count(*) as total
from
ticket
where
component = 'bcr'
UNION
select
component,
count( case when status = 'new' then 1 end ) new,
count( case when status = 'assigned' then 1 end ) assigned,
count( case when status = 'in_work' then 1 end ) in_work,
count( case when status = 'in_QA' then 1 end ) in_QA,
count( case when status = 'info_needed' then 1 end ) info_needed,
count( case when status = 'resolved' then 1 end ) resolved,
count( case when status = 'closed' then 1 end ) closed,
count(*) as total
from
ticket
where
component = 'ca'
UNION
select
component,
count( case when status = 'new' then 1 end ) new,
count( case when status = 'assigned' then 1 end ) assigned,
count( case when status = 'in_work' then 1 end ) in_work,
count( case when status = 'in_QA' then 1 end ) in_QA,
count( case when status = 'info_needed' then 1 end ) info_needed,
count( case when status = 'resolved' then 1 end ) resolved,
count( case when status = 'closed' then 1 end ) closed,
count(*) as total
from
ticket
where
component = 'cm'
UNION
select
component,
count( case when status = 'new' then 1 end ) new,
count( case when status = 'assigned' then 1 end ) assigned,
count( case when status = 'in_work' then 1 end ) in_work,
count( case when status = 'in_QA' then 1 end ) in_QA,
count( case when status = 'info_needed' then 1 end ) info_needed,
count( case when status = 'resolved' then 1 end ) resolved,
count( case when status = 'closed' then 1 end ) closed,
count(*) as total
from
ticket
where
component = 'gl / ld / pdp'
UNION
select
component,
count( case when status = 'new' then 1 end ) new,
count( case when status = 'assigned' then 1 end ) assigned,
count( case when status = 'in_work' then 1 end ) in_work,
count( case when status = 'in_QA' then 1 end ) in_QA,
count( case when status = 'info_needed' then 1 end ) info_needed,
count( case when status = 'resolved' then 1 end ) resolved,
count( case when status = 'closed' then 1 end ) closed,
count(*) as total
from
ticket
where
component = 'pay'
UNION
select
component,
count( case when status = 'new' then 1 end ) new,
count( case when status = 'assigned' then 1 end ) assigned,
count( case when status = 'in_work' then 1 end ) in_work,
count( case when status = 'in_QA' then 1 end ) in_QA,
count( case when status = 'info_needed' then 1 end ) info_needed,
count( case when status = 'resolved' then 1 end ) resolved,
count( case when status = 'closed' then 1 end ) closed,
count(*) as total
from
ticket
where
component = 'pur / ap / tp'
UNION
select
component,
count( case when status = 'new' then 1 end ) new,
count( case when status = 'assigned' then 1 end ) assigned,
count( case when status = 'in_work' then 1 end ) in_work,
count( case when status = 'in_QA' then 1 end ) in_QA,
count( case when status = 'info_needed' then 1 end ) info_needed,
count( case when status = 'resolved' then 1 end ) resolved,
count( case when status = 'closed' then 1 end ) closed,
count(*) as total
from
ticket
where
component = 'tax'
UNION
select
component,
count( case when status = 'new' then 1 end ) new,
count( case when status = 'assigned' then 1 end ) assigned,
count( case when status = 'in_work' then 1 end ) in_work,
count( case when status = 'in_QA' then 1 end ) in_QA,
count( case when status = 'info_needed' then 1 end ) info_needed,
count( case when status = 'resolved' then 1 end ) resolved,
count( case when status = 'closed' then 1 end ) closed,
count(*) as total
from
ticket
where
component = 'tk'
) s
I hope this helps.
chris
On Sat, Sep 9, 2017 at 9:43 AM, <[email protected]> wrote:
> Hi Michael,
>
> Did you ever manage to figure this out? I'm essentially looking to build
> a table with ticket status vs owner and have the table show ticket count.
>
> Thanks,
> Ian
>
> On Monday, June 15, 2009 at 4:19:09 PM UTC-4, Noah Kantrowitz wrote:
>>
>> You would need to make a macro. Might want to check if there is one on
>> trac-hacks already that can render a report or something.
>>
>> --Noah
>>
>> > -----Original Message-----
>> > From: [email protected] [mailto:[email protected]]
>> > On Behalf Of Michael
>> > Sent: Monday, June 15, 2009 11:11 AM
>> > To: Trac Users
>> > Subject: [Trac] Ticket Status Table
>> >
>> >
>> > Hello,
>> >
>> > I'm looking to see if it is possible to put together a table showing
>> > the status of tickets in my Trac instance. This table would contain
>> > counts of currently open tickets, with the columns signifying
>> > components and the rows signifying priorities.
>> >
>> > The logical way to do this is construct an HTML table, with embedded
>> > TracQuery 's in it. However, I can't seem to do this - is there an
>> > easier/better way?
>> >
>> > For instance, this DOES NOT work, but you can probably see what I'm
>> > trying to do:
>> >
>> > {{{
>> > #!html
>> > <table>
>> > <tr>
>> > <td>Tickets</td>
>> > <td>High</td>
>> > <td>Medium</td>
>> > <td>Low</td>
>> > </tr>
>> > <tr>
>> > <td>Component 1</td>
>> > }}}
>> > [TracQuery(status=open,component=comp1,priority=high, count)]
>> > {{{
>> > #!html
>> > </td><td>
>> > }}}
>> > [TracQuery(status=open,component=comp1,priority=medium, count)]
>> > {{{
>> > #!html
>> > </td><td>
>> > }}}
>> > [TracQuery(status=open,component=comp1,priority=low, count)]
>> > {{{
>> > #!html
>> > </td><td>
>> > </tr>
>> > <tr>
>> > <td>Component 1</td>
>> > }}}
>> > [TracQuery(status=open,component=comp1,priority=high, count)]
>> > {{{
>> > #!html
>> > </td><td>
>> > }}}
>> > [TracQuery(status=open,component=comp1,priority=medium, count)]
>> > {{{
>> > #!html
>> > </td><td>
>> > }}}
>> > [TracQuery(status=open,component=comp1,priority=low, count)]
>> > {{{
>> > #!html
>> > </td><td>
>> > </tr>
>> > }}}
>> >
>> > ...and so on... you can see what I'm aiming for. Any thoughts?
>> >
>> > Thanks,
>> > Michael
>> >
>> >
>>
>> --
> You received this message because you are subscribed to the Google Groups
> "Trac Users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To post to this group, send email to [email protected].
> Visit this group at https://groups.google.com/group/trac-users.
> For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups "Trac
Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/trac-users.
For more options, visit https://groups.google.com/d/optout.
