Adding Selection Choices to Existing Report

[ttaylor]

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

[SupportKeymaster]

Hi Tony – thanks for your post.

>> 1) Select particular staff based on their staff.knownas data field
In your SQL source you starting with a “select staff.id”. In the datatable, this will result in a field called “id”, however ClinicOffice has no idea what a field called “id” refers to. If you change the start of the SQL to :-

select staff.id, staff.id as staff_id, app.apptype_id...

This will enable ClinicOffice to search on the STAFF member.

>> 2) To have the option to include deleted staff on the selection list
This will automatically be the case once you have made the above change, but you need to remove the “(staff.deleted<>true)” clause from your WHERE statement in the SQL.

>> 3) Select by apptype_id
It will already offer this based on your SQL.

>> 4) Select by appointment status
Add the following fields to your SQL :-

app.appstatus_id, appstatus.name as _appstatus,

Hope this helps!

[Author]

Posts