-
AuthorPosts
-
February 17, 2012 at 10:28 am #31299
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:-
selectinvline.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 _clinicnamefrom 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|datetimeFebruary 17, 2012 at 1:50 pm #32995Support
KeymasterHi 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.nameNow 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!
February 24, 2012 at 10:30 am #32996I 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 savedI attach a sceen capture.
We are running COv4 Server edition build 1068
February 25, 2012 at 9:07 am #32997Support
KeymasterApologies – 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!
February 27, 2012 at 2:42 pm #32998Thanks 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
NoneSo choosing Sum I get the missing total!
As per attachement ESO 3Seems 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]
February 29, 2012 at 8:35 am #32999Support
KeymasterHi 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?
February 29, 2012 at 10:57 am #33000Sorry 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 -
AuthorPosts
- You must be logged in to reply to this topic.