Adding a Total to a Report

Help with reports & document templates
Post a reply

Adding a Total to a Report

Postby ttaylor » February 17th, 2012, 10:28 am

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
Attachments
Screenprint extract of report
ESO Report.PNG (41.61 KiB) Viewed 2810 times
ttaylor
 
Posts: 72
Joined: April 26th, 2010, 8:13 am
Location: ESO Maidstone
Top

Re: Adding a Total to a Report

Postby Support » February 17th, 2012, 1:50 pm

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 :-

Code: Select all
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!
ClinicOffice Support Team
User avatar
Support
Site Admin
 
Posts: 874
Joined: August 25th, 2005, 6:37 pm
Top

Re: Adding a Total to a Report

Postby ttaylor » February 24th, 2012, 10:30 am

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
Attachments
ESO Error Message.PNG (27.56 KiB) Viewed 2793 times
ttaylor
 
Posts: 72
Joined: April 26th, 2010, 8:13 am
Location: ESO Maidstone
Top

Re: Adding a Total to a Report

Postby Support » February 25th, 2012, 9:07 am

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!
ClinicOffice Support Team
User avatar
Support
Site Admin
 
Posts: 874
Joined: August 25th, 2005, 6:37 pm
Top

Re: Adding a Total to a Report

Postby ttaylor » February 27th, 2012, 2:42 pm

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
Extract Without Grouping
ESO 1.PNG (61.34 KiB) Viewed 2782 times
Attachments
sub Total without Grouping
ESO 3.PNG (59.27 KiB) Viewed 2782 times
Extract with Grouping
ESO 2.PNG (55.29 KiB) Viewed 2782 times
ttaylor
 
Posts: 72
Joined: April 26th, 2010, 8:13 am
Location: ESO Maidstone
Top

Re: Adding a Total to a Report

Postby Support » February 29th, 2012, 8:35 am

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?
ClinicOffice Support Team
User avatar
Support
Site Admin
 
Posts: 874
Joined: August 25th, 2005, 6:37 pm
Top

Re: Adding a Total to a Report

Postby ttaylor » February 29th, 2012, 10:57 am

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
ttaylor
 
Posts: 72
Joined: April 26th, 2010, 8:13 am
Location: ESO Maidstone
Top


Post a reply

Return to Reports & Templates

Who is online

Users browsing this forum: No registered users and 2 guests

cron