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, <ianstuartjone...@gmail.com> 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: trac-...@googlegroups.com [mailto:trac-...@googlegroups.com]
>> > 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 trac-users+unsubscr...@googlegroups.com.
> To post to this group, send email to trac-users@googlegroups.com.
> 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 trac-users+unsubscr...@googlegroups.com.
To post to this group, send email to trac-users@googlegroups.com.
Visit this group at https://groups.google.com/group/trac-users.
For more options, visit https://groups.google.com/d/optout.
