Adding a Total to a Report

[ttaylor]

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

[SupportKeymaster]

Hi ttaylor,

Thanks for your post. We recommend taking a COPY of the report and making the following changes to your copy. This will preserve the original report. (You can of course rename the copy to something more descriptive.)

Firstly, there isn’t actually an “invoice category” – there is an “appointment category” which comes over from the appointment on which the invoice is based. Please update the SQL SOURCE to the following :-

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,

inv.apptype_id, apptype.name as _appointment_type



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)

left join apptype on (inv.apptype_id=apptype.id)



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



order by invline.description, apptype.name

Now you simply need to add an extra level of grouping on the “Appointment Type” column – you can do this by right clicking on the column header and select GROUP BY.

Please let us know how you get on with this – many thanks!

[ttaylor]

I have taken a copy of the existing report so that I could make the changes.

I then removed the old SQL and copied in the revised SQL provided.

Unfortunately I get the error message

“column invapptype does not exist”
and so the change cannot be saved

I attach a sceen capture.

We are running COv4 Server edition build 1068

[SupportKeymaster]

Apologies – I have corrected the SQL in the original post (rather than re-posting it here which would get confusing). Please copy/paste it again and let us know if it works now.

Thanks!

[ttaylor]

Thanks for the SQL change the error message has gone away.

Unfortunately I could not get the sub total on “QTY” to work even with the extra Grouping by Appointment Type.

Attachment ESO 1 shows an extract of the report without the extra Grouping

Attachment ESO 2 shows an extract of the report hving right clicked on the header and adding a Grouping by Appointment Type

Whilst playing around to try and see what was going on, I stumbled on something that seems to give the right answer!

So going back to the original report (as per ESO 1) without the extra Grouping
I then right clicked on the cell with the missing sub total and got the options:-

Sum
Min
Max
Count
Average
None

So choosing Sum I get the missing total!
As per attachement ESO 3

Seems to show on the printed format report as well as the Grid Report and on the extracts to Excel[attachment=2:obspmjqy]ESO 1.PNG[/attachment:obspmjqy]

[SupportKeymaster]

Hi Tony,

Sorry – I’m a bit confused by your last post. You can always right-click on any column footer (either the REPORT footer or a GROUP footer) and then select a summary function. Is that what you wanted to do from the outset?

[ttaylor]

Sorry for the confusion

At its simplest that is all I needed to do. I had long forgotten or indeed never realised that the option existed.

The need was to make a total visible on the printed reports and exports to Excel (and eventually Pivottables) without the need to add formulas in Excel.

So I think we can close this item.

Regards
tony Taylor

[Author]

Posts