Adding Selection Choices to Existing Report

[clinicoffice_user]

We have a nice simple report that shows the appointment count by staff and appointment type. Basically we just choose a date range for the appointments.

The SQL Source from the grid report editor is:-
select staff.id, staff.knownas, app.apptype_id, apptype.name as _apptype, cast(coalesce(count(app.id), 0) as int4) as appcount
from staff
left join app on (app.staff_id=staff.id)
left join apptype on (app.apptype_id=apptype.id)
left join appstatus on (app.appstatus_id=appstatus.id)
where (staff.deleted<>true) and (appstatus.name not ilike ‘%cancel%’)
group by staff.id, staff.knownas, apptype_id, apptype.name
order by staff.knownas, _apptype

SQL Source 2 is blank

Options show:-
Favourites=app.start;
ForcedSearchFields=App Date|app.start|DateTime

The report runs for all the current staff. We would like to be able to-

1) Select particular staff based on their staff.knownas data field
2) To have the option to include deleted staff on the selection list

(I have some standard reports that do this but cannot see the SQL that does the job)

3) Select by apptype_id

4) Select by appointment status
(in order to remove DNAs)

Regards
Tony Taylor
ESO Maidstone

[Author]

Posts