Important Content Update Message
We are currently updating the OP Help Center content for the release of OP 20. OP 20 (official version 20.0.x) is the certified, 2015 Edition, version of the Office Practicum software. This is displayed in your software (Help tab > About) and in the Help Center tab labeled Version 20.0. We appreciate your patience as we continue to update all of our content.

Scheduling Database Viewer Reports to Run Automatically (Local)

Version 14.19

Introduction

So you have this great set of  Database Viewer queries, and you plan to run them every day, but sometimes you forget to run them. Other reports you have also need to run once a week or once a month.  It can be hard to keep all these schedules straight.  Wouldn’t it be great if you could set your reports to run automatically on a schedule that you decide?  This article addresses how to automatically run your reports at the times you want without needing to worry about if they are completed.  This process is for an on-premise OP local server. 

Preparing Your Report's SQL Job

Path: Tools menu > Database Viewer
The report used in the images of this article is nothing special.  It simply shows the Completed, No-Show, and Cancelled appointments from today that are not appointment type “NURSE”. Its simplicity just makes it a good example for this topic.

Lets say you want to run an appointment report at the end of the day every day. To do this you would first need to prepare your report's SQL job.  To prepare your report's SQL job:

  1. Before setting up the job, make a note of the query name (the image example below indicates the Dashboard: Schedule: No-Show and Cancel query).  Queries are created in the SQL tab of the OP Database Viewer.  See OP Database Viewer Basics for more information on how to create an SQL query.

  1. Click on the SQL Jobs tab.   We want to put this query into an SQL Job, which we will then schedule to run at the end of the day every day.  The list of existing jobs will appear in the Jobs pane grid of the SQL Jobs tab.   Many practices don’t have anything here (or just a few jobs).  

  1. To create a new job, click on the Add Job button () at the bottom of the Job pane. A new row will appear at the top of the Job pane grid.  This line will be highlighted in blue.
  2. On the newly created line, create a name for your job, a short description, and the folder name where you would like your report to be placed.  In our example, we are using the no-show report.  Avoid spaces and special characters (* , / ; and other punctuation) in your Job Names and Folder Names.  Use an underscore (_) when a space is required.

  1. After the information has been entered, click the Apply Changes button () to save the information.  In the image below we call the job "My_Report_Set."  The job is placed in the subfolder called "Report_Files."

  1. Once the job is created, you will need to attach the queries (created in the SQL tab) that you wish to run.  Queries can be applied to the Job in the lower pane of the SQL Jobs tab.  Begin this process by clicking the Add Query button () at the bottom of the Query pane (shown below).  This will add a dropdown line to the query list grid.  This line will be highlighted in blue.

  1. Select the created query in the Stored Query Name dropdown.  In the image below, we have selected Dashboard: Schedule: No-Show and Cancel.
  2. Enter the Override file name for the job.  The override filename contains three parts:
  1. The file name stem:  The file name stem is the main name of the file.  It helps to make the Override Filename unique, and is used at the beginning of the Override filename.
  2. The variable: Add the :RUNDATE variable at the end of the file name stem.  :RUNDATE is a variable for the date that the report is created.  Because we are going to be running this report every day, we will want to put the date in the filename in order to store files with unique file names.  Otherwise, the previous day's file will be overwritten each day.  The colon before RUNDATE must be applied.  Also, RUNDATE must be all capital letters.
  3. The file extension: The .csv file extension is required for the file name.  This tells Windows to expect a comma-separated values file (CSV format file), which can be opened in Excel or a text editor.  
  1. After you select the Stored Query and enter the Override Filename, click the Apply button () to save your work. 

  1. If you want to add more reports to this job (if you want to run more than one report each day), you can add additional queries to the query list in the Stored Queries Attached to Selected Job pane (lower pane of the SQL Jobs tab).  Each row should have a selected Stored Query and Override Filename.  In the example below, we now have a SQL job called My_Report_Set with the Dashboard: Schedule: No-Show and Cancel query applied.

Testing Your SQL Job

Before creating the auto schedule, you will want to test your SQL Job.  This will ensure that the SQL job runs as expected.  To run the report:

  1. Select the created SQL job.  Continuing with the example above, we would select My_Report_Set in the SQL Jobs pane.
  2. Click the Run Job button.  A Confirm prompt will appear indicating that it will run all the queries listed in the lower pane and then save them in the specified output folder.  In our example, we would only be running one small report in the SQL Job, so it shouldn't take too long.

  1. Click the OK button to continue.  After a moment, you will receive a confirmation box that lists the total number of files written along with the complete Windows path where the report file(s) are located.

  1. Navigate the complete file path (shown in the dialog box) to locate the newly-created report file(s).

  1. Double-click the file and open it in Excel to make sure it appears with the data you want to see.

Scheduling Your Report's SQL Job

Once you determine that your job works as expected, you can apply the Windows Task Scheduler to automatically run the reports every day.  To do this:

  1. Click the Windows icon to open the Windows menu.
  2. Click on the Search for Programs and Files field and type "Task Scheduler." The task scheduler will appear in the top of the Window menu.

  1. Click on the Task Scheduler menu item to launch it.  The Task Scheduler window opens.
  2. Click Create Task in the Actions pane (right pane).  The Create Task window appears.

  1. Enter a name in the Name field that describes that task you want to perform.

  1. In this window you can also set other options like whether or not you want your report to run only when you are logged into your computer.
  2. Once the fields are completed, click the OK button.
  3. Click the Triggers tab.  This is where you set how often you want to run the report.

  1. Click the New button in the Triggers tab.  The New Trigger window appears.  Here is where you define the schedule that you want use in order to run your report(s).

  1. In the New Trigger window, you’ll see a variety of options for scheduling your report runs.  Select Daily in the settings field to have your report run every day.
  2. Select the start date and Enter the time to run the report.  In our example, we want the report to run in the evening (after all the patients have been seen), so we would set the report to run at 7:30 PM.

  1. After setting the schedule parameters, click the OK button.
  2. Select the Actions tab.This tab is where you want to tell the computer the report that you want to run every day.  

  1. Click the New button in the Actions tab.  The New Action window appears.

  1. Select Start a Program in the Action dropdown.
  2. Enter the path of your OP folder.  Keep going deeper in the OP folder until you can select the dbviewer.exe program.  For many installations, this will be C:\OP\DBViewer.exe.  Alternatively, you can click the Browse button and navigate to the database viewer executable program (dbviewer.exe).

  1. Enter -j [name of job] in the Add Arguments (optional) field.  In our example, we would enter -j My_Report_Set.

  1. Click the OK button.  After clicking the OK button, you can return to the list of tasks in the Task Schedule.  There, you can see your new scheduled task.

Version 14.10

Introduction

So you have this great set of  Database Viewer queries, and you plan to run them every day, but sometimes you forget to run them. Other reports you have also need to run once a week or once a month.  It can be hard to keep all these schedules straight.  Wouldn’t it be great if you could set your reports to run automatically on a schedule that you decide?  This article addresses how to automatically run your reports at the times you want without needing to worry about if they are completed.  This process is for an on-premise OP local server. 

Preparing Your Report's SQL Job

Path: Tools menu > Database Viewer
The report used in the images of this article is nothing special.  It simply shows the Completed, No-Show, and Cancelled appointments from today that are not appointment type “NURSE”. Its simplicity just makes it a good example for this topic.

Lets say you want to run an appointment report at the end of the day every day. To do this you would first need to prepare your report's SQL job.  To prepare your report's SQL job:

  1. Before setting up the job, make a note of the query name (the image example below indicates the Dashboard: Schedule: No-Show and Cancel query).  Queries are created in the SQL tab of the OP Database Viewer.  See OP Database Viewer Basics for more information on how to create an SQL query.

  1. Click on the SQL Jobs tab.   We want to put this query into an SQL Job, which we will then schedule to run at the end of the day every day.  The list of existing jobs will appear in the Jobs pane grid of the SQL Jobs tab.   Many practices don’t have anything here (or just a few jobs).  

  1. To create a new job, click on the Add Job button () at the bottom of the Job pane. A new row will appear at the top of the Job pane grid.  This line will be highlighted in blue.
  2. On the newly created line, create a name for your job, a short description, and the folder name where you would like your report to be placed.  In our example, we are using the no-show report.  Avoid spaces and special characters (* , / ; and other punctuation) in your Job Names and Folder Names.  Use an underscore (_) when a space is required.

  1. After the information has been entered, click the Apply Changes button () to save the information.  In the image below we call the job "My_Report_Set."  The job is placed in the subfolder called "Report_Files."

  1. Once the job is created, you will need to attach the queries (created in the SQL tab) that you wish to run.  Queries can be applied to the Job in the lower pane of the SQL Jobs tab.  Begin this process by clicking the Add Query button () at the bottom of the Query pane (shown below).  This will add a dropdown line to the query list grid.  This line will be highlighted in blue.

  1. Select the created query in the Stored Query Name dropdown.  In the image below, we have selected Dashboard: Schedule: No-Show and Cancel.
  2. Enter the Override file name for the job.  The override filename contains three parts:
  1. The file name stem:  The file name stem is the main name of the file.  It helps to make the Override Filename unique, and is used at the beginning of the Override filename.
  2. The variable: Add the :RUNDATE variable at the end of the file name stem.  :RUNDATE is a variable for the date that the report is created.  Because we are going to be running this report every day, we will want to put the date in the filename in order to store files with unique file names.  Otherwise, the previous day's file will be overwritten each day.  The colon before RUNDATE must be applied.  Also, RUNDATE must be all capital letters.
  3. The file extension: The .csv file extension is required for the file name.  This tells Windows to expect a comma-separated values file (CSV format file), which can be opened in Excel or a text editor.  
  1. After you select the Stored Query and enter the Override Filename, click the Apply button () to save your work. 

  1. If you want to add more reports to this job (if you want to run more than one report each day), you can add additional queries to the query list in the Stored Queries Attached to Selected Job pane (lower pane of the SQL Jobs tab).  Each row should have a selected Stored Query and Override Filename.  In the example below, we now have a SQL job called My_Report_Set with the Dashboard: Schedule: No-Show and Cancel query applied.

Testing Your SQL Job

Before creating the auto schedule, you will want to test your SQL Job.  This will ensure that the SQL job runs as expected.  To run the report:

  1. Select the created SQL job.  Continuing with the example above, we would select My_Report_Set in the SQL Jobs pane.
  2. Click the Run Job button.  A Confirm prompt will appear indicating that it will run all the queries listed in the lower pane and then save them in the specified output folder.  In our example, we would only be running one small report in the SQL Job, so it shouldn't take too long.

  1. Click the OK button to continue.  After a moment, you will receive a confirmation box that lists the total number of files written along with the complete Windows path where the report file(s) are located.

  1. Navigate the complete file path (shown in the dialog box) to locate the newly-created report file(s).

  1. Double-click the file and open it in Excel to make sure it appears with the data you want to see.

Scheduling Your Report's SQL Job

Once you determine that your job works as expected, you can apply the Windows Task Scheduler to automatically run the reports every day.  To do this:

  1. Click the Windows icon to open the Windows menu.
  2. Click on the Search for Programs and Files field and type "Task Scheduler." The task scheduler will appear in the top of the Window menu.

  1. Click on the Task Scheduler menu item to launch it.  The Task Scheduler window opens.
  2. Click Create Task in the Actions pane (right pane).  The Create Task window appears.

  1. Enter a name in the Name field that describes that task you want to perform.

  1. In this window you can also set other options like whether or not you want your report to run only when you are logged into your computer.
  2. Once the fields are completed, click the OK button.
  3. Click the Triggers tab.  This is where you set how often you want to run the report.

  1. Click the New button in the Triggers tab.  The New Trigger window appears.  Here is where you define the schedule that you want use in order to run your report(s).

  1. In the New Trigger window, you’ll see a variety of options for scheduling your report runs.  Select Daily in the settings field to have your report run every day.
  2. Select the start date and Enter the time to run the report.  In our example, we want the report to run in the evening (after all the patients have been seen), so we would set the report to run at 7:30 PM.

  1. After setting the schedule parameters, click the OK button.
  2. Select the Actions tab.This tab is where you want to tell the computer the report that you want to run every day.  

  1. Click the New button in the Actions tab.  The New Action window appears.

  1. Select Start a Program in the Action dropdown.
  2. Enter the path of your OP folder.  Keep going deeper in the OP folder until you can select the dbviewer.exe program.  For many installations, this will be C:\OP\DBViewer.exe.  Alternatively, you can click the Browse button and navigate to the database viewer executable program (dbviewer.exe).

  1. Enter -j [name of job] in the Add Arguments (optional) field.  In our example, we would enter -j My_Report_Set.

  1. Click the OK button.  After clicking the OK button, you can return to the list of tasks in the Task Schedule.  There, you can see your new scheduled task.