[Trac] Re: custom fields in reports

[Jim Cheetham]

On Tue, 2005-02-08 at 16:50 +1300, Felix Collins wrote:
> James Whitwell wrote:
> > Thanks everyone for your help and advice on this problem (and my 
> > apologies for not saying thank you earlier!)

> So you got it working?  Can you post an example to help others who might 
> try this?

I've added a number of fields to keep track of the type of work involved
in a ticket (for external customer support work, not code bug-fixing)

In trac.ini :-

> [ticket-custom]
> charge = text
> charge.label = Elapsed chargeable time
> 
> noncharge = text
> noncharge.label = Elapsed non-chargeable time
> 
> sitevisit = checkbox
> sitevisit.label = Site visit
> 
> oohours = checkbox
> oohours.label = Out Of Hours work
> 

The report I currently use for this has multiple LEFT OUTER JOIN
statements ... it's going to dump a list of all closed calls (ordered by
milestone, which I'm re-purposing as the customer name), along with
their custom fields.

I'll be extending the report eventually to deal with time ranges, and
then requesting it as CSV so we can dump it straight into the billing
system ... sweet :-)


> SELECT DISTINCT 
> 
>    id AS ticket, c.value AS chargeable, n.value AS nonchargeable,
>    s.value AS sitevisit, o.value AS outofhours,
>    milestone AS customer, summary, component, status 
> 
>   FROM ticket t,enum p
> 
>   LEFT OUTER JOIN ticket_custom c ON
>        (t.id=c.ticket AND c.name='charge')
> 
>   LEFT OUTER JOIN ticket_custom n ON
>        (t.id=n.ticket AND n.name='noncharge')
> 
>   LEFT OUTER JOIN ticket_custom s ON
>        (t.id=s.ticket AND s.name='sitevisit')
> 
>   LEFT OUTER JOIN ticket_custom o ON
>        (t.id=o.ticket AND o.name='oohours')
> 
>   WHERE (p.name=t.priority AND p.type='priority' AND status='closed')
>  
>   ORDER BY milestone

The LEFT OUTER JOINS get around the fact that not all of my tickets have
the custom fields referenced (i.e. old tickets created before the custom
field was declared, and not subsequently updated)

I have a remaining minor niggle : the unpopulated custom fields render
as the word "None" in the output, and I'd like them to be blank. I tried
a CASE clause, but that didn't seem to trigger for 'None' or NULL ...

>  (CASE c.value WHEN '' THEN ' ' ELSE c.value END) AS chargeable,

Any clues on this last point (plus better suggestions for my verbose
LEFT OUTER JOINs) would be welcome!

-jim