add search criteria

[dee.duncanson]

How do we add patient category {private or insurance} in to a financial report?

[SupportKeymaster]

Hi Dee – thanks for your post. The answer is it depends on which financial report you have in mind – please let us know and we’ll provide instructions.

[dee.duncanson]

Hi
I am trying to run a report detailing private vs insurance patients on the invoice list report template unless there is an easier way of obtaining the information.
Thanks for your help
Dee

[WilliamKeymaster]

Hi Dee

If it is just a list of patients that have the patient category of Private or Insurance you could always use the main Patient grid in ClinicOffice or the Patient List and then search on Patient Category.

With this you can search on both categories and under Grid Options select the “By Category” layout. This will group the grid by Private and Insurance and will give you a sub total of the number of records under each group. Alternatively you could just search on each category separately and view the total count of records at the bottom of the grid.

Would this be sufficient or would you prefer to be able to see the amount of money each group have been invoiced/paid for?

[dee.duncanson]

Thanks for your answer we do need to know the financial side and need to put in a time span, we just want to add the insurnace/private criteria into the search items on the invoices list!
Thanks Dee

[WilliamKeymaster]

That will be no problem Dee. To add the Patient Category to the Invoice List please follow the steps below.

[1] Right click on the Invoice List report and select Copy
[2] Right click on this copied report and select Rename
[3] Enter a new name like “Invoice List with Patient Categories”
[4] Click OK
[5] Right click on the report and select Report Source
[6] Click on the SQL Source tab
[7] Delete any text present in this window
[8] Copy all of the the SQL below

select inv.id, invoicenum, datetime, duedate, inv.discpercent, discamount, nettotal, taxtotal, total, printed,



inv.per_id as recipient_per_id, trim(recipper.lastname || ', ' || coalesce(recipper.firstname,'') || ' ' || coalesce(recipper.title,'')) as _recipient,

inv.clinic_id, clinic.name as _clinicname,

inv.staff_id,

(case

when inv.staff_id is null then ''

else staff.knownas

end) as _staffmember,

inv.room_id, room.name as _roomname,

inv.apptype_id, apptype.name as _apptype,

inv.patient_per_id, trim(patper.lastname || ', ' || coalesce(patper.firstname,'') || ' ' || coalesce(patper.title,'')) as _patient,

patper.code, patper.insuranceref,

(coalesce(clinic.prefix, '') || cast(inv.invoicenum as varchar(20))) as _invoiceref,



coalesce(view_invamtalloc.amtalloc, 0) as amtpaid,

(inv.total-coalesce(view_invamtalloc.amtalloc, 0)) as outstanding,



cast(

case

when (inv.total = 0) or (view_invamtalloc.amtalloc >= inv.total) then

'Paid In Full'

when (view_invamtalloc.amtalloc > 0) and (view_invamtalloc.amtalloc < inv.total) then

'Part Paid'

else

'Unpaid'

end as varchar(20)) as _status,



(case when (inv.total > 0) and (coalesce(view_invamtalloc.amtalloc, 0) < inv.total)

then (cast((current_date - cast(inv.datetime as date)) as integer))

else cast(null as integer) end) as _ageing,



(case when (inv.total > 0) and (current_date > duedate) and (coalesce(view_invamtalloc.amtalloc, 0) < inv.total)

then true

else false end) as _overdue,



inv.createdon, inv.createdby_staff_id, cr_staff.knownas as _createdby,

inv.updatedon, inv.updatedby_staff_id, up_staff.knownas as _lastupdatedby,

patper.patcat_id, patcat.name as _patient_category



from inv



inner join per as recipper on (inv.per_id = recipper.id)

inner join clinic on (inv.clinic_id = clinic.id)

left join staff on (inv.staff_id = staff.id)

left join room on (inv.room_id = room.id)

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

left join per as patper on (inv.patient_per_id = patper.id)

left join staff as cr_staff on (inv.createdby_staff_id = cr_staff.id)

left join staff as up_staff on (inv.updatedby_staff_id = up_staff.id)

left join view_invamtalloc on (inv.id = view_invamtalloc.inv_id)

left join patcat on (patper.patcat_id=patcat.id)

[9] Paste this into the SQL Source window
[10] Click OK

When you run this report you will see whether a patient is of the private or insurance categories. Please let us know if you have any problems with the above instructions.

[dee.duncanson]

Thats excellent – Many thanks for your help =D>
Dee

[Author]

Posts