Version 20.5

Overview

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. 

Prepare Your Report's SQL Job

Path: Tools menu > Database Viewer

Let's 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 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 button  at the bottom of the Job pane. A new row is displayed at the top of the grid. 
2. On the new row, create a name for your job, a short description, and the folder name where you would like your report to be placed. Avoid spaces and special characters (* , / ; and other punctuation) in your Job Names and Folder Names. Use an underscore (_) when a space is required. If you need to omit headers for the job, select the Omit Headers checkbox.
3. After the information has been entered, click the Save button  to save the information. 
4. 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 button  at the bottom of the Query pane (shown below). This will add a drop-down line to the query list grid.

1. Select the created query from the Stored Query Name drop-down. In the image below, we have selected Dashboard: Schedule: No-Show and Cancel.
2. Enter the Override filename for the job.  This name contains three parts:

1. The file name stem: The filename 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, and RUNDATE must be all capital letters. RUNDATE may be formatted in any of the following ways so that the date in the filename is clear: :RUNDATE formats the date as YYYYMMDD, :RUN-DATE formats the date as YYYY-MM-DD, and :RUN_DATE formats the date as YYYY_MM_DD.
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 Save button  at the bottom of the Query pane to save your work. 

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.

Test 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 displays 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.

Schedule 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 is displayed.
2. Click Create Task in the Actions pane (right pane). The Create Task window is displayed.

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 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 is displayed.

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 20.4

Overview

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. 

Prepare Your Report's SQL Job

Path: Tools menu > Database Viewer

Let's 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 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 button  at the bottom of the Job pane. A new row is displayed at the top of the grid. 
2. On the new row, 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 Save 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 button  at the bottom of the Query pane (shown below). This will add a drop-down line to the query list grid.

1. Select the created query from the Stored Query Name drop-down. In the image below, we have selected Dashboard: Schedule: No-Show and Cancel.
2. Enter the Override filename for the job.  This name contains three parts:

1. The file name stem: The filename 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, and RUNDATE must be all capital letters. RUNDATE may be formatted in any of the following ways so that the date in the filename is clear: :RUNDATE formats the date as YYYYMMDD, :RUN-DATE formats the date as YYYY-MM-DD, and :RUN_DATE formats the date as YYYY_MM_DD.
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 Save button  at the bottom of the Query pane to save your work. 

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.

Test 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 displays 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.

Schedule 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 is displayed.
2. Click Create Task in the Actions pane (right pane). The Create Task window is displayed.

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 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 is displayed.

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.