Viewing 7 posts - 1 through 7 (of 7 total)
  • Author
    Posts
  • #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:-


    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

    #32995

    Support
    Keymaster

    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!

    #32996

    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

    #32997

    Support
    Keymaster

    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!

    #32998

    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]

    #32999

    Support
    Keymaster

    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?

    #33000

    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

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic.