> I'm too set in my ways which is why I need a kick in a different direction ;)
>
> http://lsces.co.uk/lsces/wiki/index.php?page=Rep-QueueServe
> For example
> Goes back quite a few years now, and still working, but it uses FastReports in
> Builder6 and I've moved most of the stuff to web based in PHP. Reports are the
> last area to sort out, and while I have the simple stuff these multi-level
> ones are more of a problem. The ORIGINAL designs were broken up to get fast
> response - with IB5.0 ....
>
> ( The image links on the website have been messed up by a 'software
> improvement' so only a couple of the reports have the right pictures :( )

I suggest the following, from my guessing at your table structures:

In the first pass (z) we get totals per area and queue, counting up detail records only in some
cases. (count(fieldname) only counts non-null entries, and I assume that case/when/end defaults to
null if no conditions are matched -- otherwise, modifiy.)
In the second pass (y) we calculate percentages (now that we have everything we need in one place)
and also figure out who the worst offender was. We avoid multiple sub-queries by grabbing the id
just once, rather than running several "first 1" queries to get each field we want.
In the third pass (outermost) we join by the precalculated worst-offender id to go and get the
details we wanted about it.

I have not tested this query, but maybe it'll save you some time or give someone some ideas for
solving their problems? It's certainly an interesting case.

It also means that your <, between, and > sections in the report would be just one band grabbing
three different fields and placing them vertically. The dataset has them horizontally. From your
design-view screenshots, it looks like that's already the case.

select a_name, q_name, enq_tot, enq_avg, enq_max, enq_low, enq_mid, enq_hi, pct_low, pct_mid,
pct_hi, enq.serv_time, enq.serv_date, enq.ticket_num
from (
select a_name, q_name, enq_tot, enq_avg, enq_max, enq_low, enq_mid, enq_hi,
case when enq_tot > 0 then 100 * enq_low / enq_tot else 0 end as pct_low,
case when enq_tot > 0 then 100 * enq_mid / enq_tot else 0 end as pct_mid,
case when enq_tot > 0 then 100 * enq_hi / enq_tot else 0 end as pct_hi,
(select first 1 badenq.id from enquiries badenq where badenq.queue_id = q_id order by serv_time
desc) as enq_bad_id
from (
select
area.id a_id,
area.name a_name,
queue.id q_id,
queue.name q_name,
count(enquiries.*) enq_tot,
avg(enquiries.serv_time) enq_avg,
max(enquiries.serv_time) enq_max,
count(case when enquiries.serv_time < area.lower_bound then 1 end) enq_low,
count(case when enquiries.serv_time between area.lower_bound and area.upper_bound then 1 end)
enq_mid,
count(case when enquiries.serv_time > area.upper_bound then 1 end) enq_hi
from
enquiries
left join queue on queue.id = enquiries.queue_id
left join area on area.id = queue.area_id
where
enquiries.serv_date between ... and ...
group by a_id, q_id, area.name, queue.name
) as z
) as y
left join enquiries enq on enq.id = y.enq_bad_id
;

-Philip