Adding Fields to existing Reports

[Mark Ratcliffe]

Hi,

I want to add an email field to the existing Payment list report.

How do I go about doing this?

[William]

Hi Mark

Thanks for the post. In order to add this, you will need to update the SQL Source of the report. By default the source for the payment list is just “select * from view_payments” but in order to add the email address, we will need to expand open the SQL and then add the value per.email which will add the email address to this report. The full SQL code is below for this.

 

select

pay.id, paynum, datetime, pay.amount, pay.reference,
pay.per_id as receivedfrom_per_id, trim(both ‘ ‘ from per.lastname || ‘, ‘ || coalesce(per.firstname,”) || ‘ ‘ || coalesce(per.title,”)) as _receivedfrom,
per.lastname, per.firstname, per.title,

paymethod_id, paymethod.name as _paymentmethod,
pay.clinic_id, clinic.name as _clinicname, clinic.prefix as _clinicprefix,
(coalesce(clinic.prefix, ”) || cast(pay.paynum as varchar(20))) as _payref,

coalesce(view_payamtalloc.amtalloc, 0) as amtallocated,
(pay.amount – coalesce(view_payamtalloc.amtalloc, 0)) as amtunallocated,

cast(case
when (pay.amount = 0) or (view_payamtalloc.amtalloc >= pay.amount) then
‘Fully Allocated’
when (view_payamtalloc.amtalloc > 0) and (view_payamtalloc.amtalloc < pay.amount) then
‘Part Allocated’
else
‘Unallocated’
end as varchar(20)) as _status,

pay.createdby_staff_id as takenby_staff_id, cr_staff.knownas as _takenby,

pay.createdon, pay.createdby_staff_id, cr_staff.knownas as _createdby,
pay.updatedon, pay.updatedby_staff_id, up_staff.knownas as _lastupdatedby,
per.email

from pay
inner join clinic on (pay.clinic_id = clinic.id)
inner join per on (pay.per_id = per.id)
inner join paymethod on (pay.paymethod_id = paymethod.id)
left join view_payamtalloc on (pay.id = view_payamtalloc.pay_id)
left join staff as cr_staff on (pay.createdby_staff_id = cr_staff.id)
left join staff as up_staff on (pay.updatedby_staff_id = up_staff.id)

 

Please copy the above and then follow the steps below.

• Go into the Reports and Templates in ClinicOffice
• Right-click on the Payment List report and select Report Source
• Delete all the text in the SQL Source window
• Copy the above text into the SQL Source window in ClinicOffice
• Click OK

When you run the report next the patient’s email will now be present in the grid as well.

[Author]

Posts