Viewing 1 post (of 1 total)
  • Author
    Posts
  • #37652
    William
    Keymaster

    The “Patient Retention by Staff” report will indicate if a patient has been retained or not. This is based on whether the patient has had a subsequent appointment after their initial appointment. You can specify the date range of that initial appointment and the staff member they had. The report by default is grouped by that staff member and it automatically excludes cancelled appointments. This will give you an idea of which staff are retaining their patients and the number of new patients they have seen within a given time.

    Before importing this report, you will need to create two custom views for the report to work. Please follow these steps:-

    1) Go to the Tools menu (tab) and click Advanced DB Operation

    2) Make sure the “As Administrator” checkbox is ticked

    3) Delete any code present in this window and copy & paste the code below into the “Advanced Database Operation” window

     

    create view xview_first_appt as

    select

    app.per_id, min(app.start) as datetime

    from app

    left join appstatus on (app.appstatus_id=appstatus.id)

    where (appstatus.cancelled=false or app.appstatus_id is null)

    group by app.per_id;

    ALTER TABLE xview_first_appt

      OWNER TO admin;

    GRANT ALL ON TABLE xview_first_appt TO admin;

    GRANT ALL ON TABLE xview_first_appt TO std_user;

     

    4) Click Execute, OK

    5) Delete the code and copy & paste the code below

     

    create view xview_count_after_exam as

    select

    (cast(count(app.id) as int) -1) as appt_count, app.per_id

    from app

    left join appstatus on (app.appstatus_id=appstatus.id)

    where (appstatus.cancelled=false or app.appstatus_id is null)

    group by app.per_id;

    ALTER TABLE xview_count_after_exam

      OWNER TO admin;

    GRANT ALL ON TABLE xview_count_after_exam TO admin;

    GRANT ALL ON TABLE xview_count_after_exam TO std_user;

     

    6) Click Execute, OK and then Close

    Now that you’ve created the required database VIEWS, please download and install the attached report file.

    For instructions on how to import the downloaded report, please see the article via the following link:-
    https://pioneersoftware.co.uk/forums/topic/how-do-i-import-a-report-file-into-my-database

     

    Attachments:
    You must be logged in to view attached files.
Viewing 1 post (of 1 total)
  • You must be logged in to reply to this topic.