Adding a Total to a Report

We have an existing report which analysis invoices by invoice category (full price, or by various offers). This sub totals by invoice category the invoice value, tax, total invoice.

We would like to add a sub total by invoice category to the Qty column.

It is a “standard” Clinic Office Report “A summary of your item/service sales. Based on the date that the invoice was raised”.

The SQL for the report is:-

---

select

invline.description,
cast(sum(invline.quantity) as float) as qty,
sum(invline.net) as nettotal,
sum(invline.tax) as taxtotal,
sum(invline.total) as total,

itemcat.id as itemcat_id,
itemcat.name as itemcategory,
inv.clinic_id, clinic.name as _clinicname

from invline
inner join inv on (inv.id=invline.inv_id)
inner join clinic on (inv.clinic_id=clinic.id)
left join item on (invline.item_id=item.id)
left join itemcat on (item.itemcat_id=itemcat.id)

group by invline.description, itemcat.name, itemcat.id, clinic.name, inv.clinic_id

order by invline.description

---

The options tab shows:-
Favourites=description;inv.datetime;
ForcedSearchFields=Invoice Date/Time|inv.datetime|datetime