cancel
Showing results for 
Search instead for 
Did you mean: 
whiteleydp
Canine III

report help please

I have been asked to compile a report with two main queries:

Firstly, and one I can see on your pre prepared reports, I need to list all schemes of patients seen between two dates, (which will be variable as I run the repost week to week) who have completed a course of treatment, then, FROM THAT list, I need to see who has booked a future appointment.

Can you help please?

Labels (1)
0 Kudos
Reply
7 Replies
Alison_Daniels
CS Dental Employee
CS Dental Employee

Re: report help please

Good morning

This can be achieved by creating 2 reports

The first report will find all the completed courses of treatment in a certain period of time

REPORT 1

To create a report to find the number of completed CoTs in a given time.

  1. Open Report Manager
  2. Select the Folder called Reports not in a Category
  3. Click ‘New Report’ then ‘Start’
  4. Click on the ‘Ellipsis’ to select a query.
  5. Click on ‘New Query’ then ‘Start’
  6. Select the item ‘Treatment Plans’ then ‘Next’
  7. Tick the box ‘that have certain attributes’ then click ‘Specify Attributes’
  8. In the next window select the ‘Completion Date’ field and add it to the Criteria window twice
  9. In the first date line change the ‘Operator’ column option to ‘later than or equal to’ and tick the ‘Ask’ column, click ‘OK’ then change the question to ask for the START DATE
  10. In the second date line change the ‘Operator’ column option to ‘earlier than or equal to’ and tick the ‘Ask’ column, click ‘OK’ then change the question to ask for the END DATE
  11. Click ‘OK’ then ‘Finish’ and save the query.
  12. Now select the query just made and click ‘Next’ then select ‘Report Layout’
  13. select AcceptanceDate, Completion Date, PatientCode and Surname
  14. Click ‘OK’ then ‘Finish’ and save the report

REPORT 2

To create a report to find the number of completed CoTs in a given time with a PENDING APPOINTMENT

  1. Open Report Manager
  2. Select the Folder called Reports not in a Category
  3. Click ‘New Report’ then ‘Start’
  4. Click on the ‘Ellipsis’ to select a query.
  5. Click on ‘New Query’ then ‘Start’
  6. Select the item ‘Treatment Plans’ then ‘Next’
  7. Tick the box ‘that have certain attributes’ then click ‘Specify Attributes’
  8. In the next window select the ‘Completion Date’ field and add it to the Criteria window twice
  9. In the first date line change the ‘Operator’ column option to ‘later than or equal to’ and tick the ‘Ask’ column, click ‘OK’ then change the question to ask for the START DATE
  10. In the second date line change the ‘Operator’ column option to ‘earlier than or equal to’ and tick the ‘Ask’ column, click ‘OK’ then change the question to ask for the END DATE
  11. Click ‘OK’ then ‘Finish’ and save the query.
  12. Now select the query just made and click ‘Next’
  13. On the next window select the ‘Yes’ option and click the ellipsis
  14. Select the ‘Query’ option and click ‘OK’
  15. Create a second query by clicking on ‘New Query’ then ‘Start’
  16. Select the entity ‘Patients’ and click ‘Next’
  17. Scroll down and select the line ‘that have at least [Number of Appointments] appointments with a status of [Appointment Status] and click ‘Specify Criteria’
  18. In the value column select ‘Pending’ for the Appointment Status and ‘1’ for the Number of Appointments then click ‘OK’
  19. Click ‘Next’ then ‘Finished’ and save the query.
  20. Select the query just created and click ‘Next’ then select ‘Report Layout’
  21. From the first table (Patients) select the PatientCode and Surname
  22. From the second table select AcceptanceDate, Completion Date, PatientCode and Surname
  23. Click ‘OK’ then ‘Finish’ and save the report

However, the second report will find the patients who have completed a CoT within the time but the pending appointment they have may be one from the past which wasn't cancelled or completed! 

It is easier to find the patients that have not got a booked appointment and that can be done by editing the second report at steps 17 & 18 and choosing the option as follows

17. That don't have an appointment with a status of [Appointment Status]

18. Select 'Specify Criteria' and select 'Pending' in the Value Column

I hope this helps you to get the information you require, if you need any further advice do please get to me.

Kind regards

Alison

0 Kudos
Reply
whiteleydp
Canine III

Re: report help please

Thank you Alison - I will have a play
0 Kudos
Reply
whiteleydp
Canine III

Re: report help please

A little help Alison please - 1st report stages 9 and 10, i can't see where to change the question?

0 Kudos
Reply
liamrushton
CS Dental Employee
CS Dental Employee

Re: report help please

Good Afternoon,

Double click the area under "question prompted to user"

liamrushton_0-1627989558125.png

 

Kind Regards
Liam Rushton
0 Kudos
Reply
Alison_Daniels
CS Dental Employee
CS Dental Employee

Re: report help please

Click in the operator column and a little 'down arrow' appears, change the option to 'later than' and tick the 'Ask' box, then do as Liam says.
0 Kudos
Reply
whiteleydp
Canine III

Re: report help please

Sorry, I've seen where to enter question, but the report (2) generated is messy and difficult to read - can you help further?

 

0 Kudos
Reply
Alison_Daniels
CS Dental Employee
CS Dental Employee

Re: report help please

That is the only way to get the filtered details out - if you export it into Excel instead of using Web Page it is then easy to sort the columns by either PatientCode or Surname.
Regards
Alison
0 Kudos
Reply