OP Database Viewer Basics

Version 20.5
Path: Tools tab > Database Viewer button

About

Database Viewer (or DB Viewer) functions as a database editor and SQL query tool. It allows you to run, save, and export data quickly based on a query. Practices that utilize PMX also have the ability to send a message to all patients listed as a result of a query. Only users with permission Tools_DBViewer have access to the Database Viewer.

NoteThe Database Viewer accesses the same database as OP.  If you run a query that filters a lot of data, your database could become sluggish and affect your users. It is recommended to run large queries during hours when users are not running OP.

Database Viewer Layout

  • Table List pane (left pane): Contains all the available database tables found in OP.
  • Table Data pane (right pane): Displays all the data for the selected database table.
  • Fields tab: Lists the fields contained within each selected database table.
  • Data Grid tab: Lists the data contained within each selected database table.
  • Data Details tab: Lists the details for each selected line in the Data Grid tab.
  • Find by Name/Description tab: Searches for a particular data element.
  • SQL tab: Groups particular elements from the database table based on the selected query. You can create, run, send messages (if using PMX), and export queries in this tab. Query results are exported as a .csv file, which can be opened in Excel. Created queries can also be selected in the SQL Jobs tab.
  • SQL Jobs tab: Groups multiple queries (created in the SQL tab) into jobs. Jobs create multiple .csv files in a particular folder. You can create, run, export, and import SQL Jobs using this tab.

Query vs. Job

  • A query groups particular elements from the database table based on query code entered in the SQL tab.
  • A job groups and runs selected SQL queries that were created in the SQL tab.

Queries

The SQL tab enables you to enter and run SQL Queries. Quick clip-and-save SQL queries can be found in our Clip-and-Save SQL Library.

Run a Query

  1. Navigate to Database Viewer following the path above.
  2. Click the SQL tab.
  3. Enter or paste the SQL query code into the text box below the Results filename field.
  4. Click the Run button. The query results are displayed in the lower section of the window.

Save, Select, Export, and Send Messages from a Query

NoteSome exported reports take a long time to be created. Do not attempt to open the file while the file is downloading or it could become corrupted. You can verify the .csv file has been downloaded by verifying the file size is not changing and that the icon changed from a blank page to a program icon that can read a .csv file (like Excel). However, the icon may remain a blank page if you do not have a local program on your computer that can read the .csv file.


  • Save a Query: After a query is initially created, click the SaveSQL Query Text Under a New Name. This will save the query.
  • Select a Saved or Imported Query: Select a saved or imported query that will run in the Query Name dropdown list.
  • Export a Query without Running the Query: Click the Extract button. The query will run and appear as a .csv file. The filename is created in the Results in Filename field.
  • Export a Query after it Runs: Click the Save CSV button.
  • Send Message(s): For practices who use PMX, messages can be sent to the list of patients displayed after running a query. Only queries that include PATNO can generate messages. Click the Send button and confirm that you want to send a message to all patients displayed in the grid by clicking OK. The Patient Message eXchange window opens where you will proceed with composing and sending the message. 

Jobs

The SQL Jobs tab enables you to group and run sets of queries.

Create a Job

  1. Click the Add button in the Job List panel located in the upper panel of the window. A new line is displayed at the top of the grid.
  2. Enter a name for the job in the Job Name field.
  3. Enter the description in the Job Description field.
  4. Enter the folder name where the query results for the job will be stored in the Output to Folder field. The folder will be created after the Run Job button is clicked.
  • Local server: The folder location when operating on local server is C:\Users[Your User Name]\Documents\Office Practicum\export
  • Cloud-based environment: See Saving Files Exported from the Cloud below.
  1. (Optional) Select the Omit Header checkbox if the file is being created for a vendor that does not accept the report header row.
  2. Click the Save button in the upper pane.
  3. Select the queries in the lower pane from the Stored Query by Name dropdown list.
  4. Enter the filename in the Override filename field with the proper extension for each query. Always enter .dot or .csv at the end of the filename. These are the files that appear in the output folder after the job is run.
  5. Click the Save button in the lower pane.

Saving Files Exported from the Cloud

Follow these steps to save files that you export from the cloud:

  1. Create a folder on your local computer where you wish to save your exported files. 
  2. Run the report/file you want to export, and click the Export button.
  3. Navigate to the Network drive in the Save As window.
  4. Double-click tsclient.
  5. Double-click the \\tsclient\C Share or your local folder.
  6. Select the folder you created in step 1. In the example below, we created the My OP Cloud Exports folder on the local C drive before we exported the file.
  7. Select the desired file extension in the Save as type dropdown. In this example below, we chose Excel files (*.xls).
  8. Click Save.

Run, Export, and Import a Job

  • Run a Job: After a job is created, click the Run Job button to run the job. The query files are stored in the output folder location.
  • Export a Job: If a job needs to be saved to an external location outside of OP, select the job and click the Export Job button. This will export the job and any queries associated with that job as an XML file to a specified folder on the computer.
  • Import a Job: If an exported job (or another job created from a different source) needs to be brought into OP on your computer, click the Import Jobs button and select the job .xml file. The job and queries for that job will be brought into the OP Database Viewer.

Note: After you import a job, you can:

  • Run the complete job from the SQL Jobs tab.
  • Run the job’s individual queries from the SQL tab.
Version 20.4
Path: Tools tab > Database Viewer button

Overview

Database Viewer (or DB Viewer) functions as a database editor and SQL query tool. It allows you to run, save, and export data quickly based on a query. Practices who utilize PMX also have the ability to send a message to all patients listed as the result of a query. Only users with permission Tools_DBViewer have access to the Database Viewer.

NoteThe Database Viewer accesses the same database as OP.  If you run a query that filters a lot of data, your database could become sluggish and affect your users. It is recommended to run large queries during hours when users are not running OP.

Database Viewer Layout

  • Table List pane (left pane): Contains all the available database tables found in OP.
  • Table Data pane (right pane): Displays all the data for the selected database table.
  • Fields tab: Lists the fields contained within each selected database table.
  • Data Grid tab: Lists the data contained within each selected database table.
  • Data Details tab: Lists the details for each selected line in the Data Grid tab.
  • Find by Name/Description tab: Searches for a particular data element.
  • SQL tab: Groups particular elements from the database table based on the selected query. You can create, run, send messages (if using PMX), and export queries in this tab. Query results are exported as a .csv file, which can be opened in Excel. Created queries can also be selected in the SQL Jobs tab.
  • SQL Jobs tab: Groups multiple queries (created in the SQL tab) into jobs. Jobs create multiple .csv files in a particular folder. You can create, run, export, and import SQL Jobs using this tab.

Query vs. Job

  • A query groups particular elements from the database table based on query code entered in the SQL tab.
  • A job groups and runs selected SQL queries that were created in the SQL tab.

Queries

The SQL tab enables you to enter and run SQL Queries. Quick clip-and-save SQL queries can be found in our Clip-and-Save SQL Library.

Run a Query

  1. Navigate to Database Viewer following the path above.
  2. Click the SQL tab.
  3. Enter or paste the SQL query code into the text box below the Results filename field.
  4. Click the Run button. The query results are displayed in the lower section of the window.

Save, Select, Export, and Send Messages from a Query

NoteSome exported reports take a long time to be created. Do not attempt to open the file while the file is downloading or it could become corrupted. You can verify the .csv file has been downloaded by verifying the file size is not changing and that the icon changed from a blank page to a program icon that can read a .csv file (like Excel). However, the icon may remain a blank page if you do not have a local program on your computer that can read the .csv file.


  • Save a Query: After a query is initially created, click the SaveSQL Query Text Under a New Name. This will save the query.
  • Select a Saved or Imported Query: Select a saved or imported query that will run in the Query Name dropdown list.
  • Export a Query without Running the Query: Click the Extract button. The query will run and appear as a .csv file. The filename is created in the Results in Filename field.
  • Export a Query after it Runs: Click the Save CSV button.
  • Send Message(s): For practices who use PMX, messages can be sent to the list of patients displayed after running a query. Only queries that include PATNO can generate messages. Click the Send button and confirm that you want to send a message to all patients displayed in the grid by clicking OK. The Patient Message eXchange window opens where you will proceed with composing and sending the message. 

Jobs

The SQL Jobs tab enables you to group and run sets of queries.

Create a Job

  1. Click in the field labeled Click Here to Add a New Query or Selected Job. Enter the name of the job.
  2. Enter the description in the Job Description field.
  3. Enter the folder name where the query results for the job will be stored in the Output to Folder field. The folder will be created after the Run Job button is clicked.
  • Local server: The folder location when operating on local server is C:\Users[Your User Name]\Documents\Office Practicum\export
  • Cloud-based environment: See Saving Files Exported from the Cloud below.
  1. Click the Save button in the upper pane.
  2. Select the queries in the lower pane from the Stored Query by Name dropdown list.
  3. Enter the filename in the Override filename field with the proper extension for each query. Always enter .dot or .csv at the end of the filename. These are the files that appear in the output folder after the job is run.
  4. Click the Save button in the lower pane.

Saving Files Exported from the Cloud

Follow these steps to save files that you export from the cloud:

  1. Create a folder on your local computer where you wish to save your exported files. 
  2. Run the report/file you want to export, and click the Export button.
  3. Navigate to the Network drive in the Save As window.
  4. Double-click tsclient.
  5. Double-click the \\tsclient\C Share or your local folder.
  6. Select the folder you created in step 1. In the example below, we created the My OP Cloud Exports folder on the local C drive before we exported the file.
  7. Select the desired file extension in the Save as type dropdown. In this example below, we chose Excel files (*.xls).
  8. Click Save.

Run, Export, and Import a Job

  • Run a Job: After a job is created, click the Run Job button to run the job. The query files are stored in the output folder location.
  • Export a Job: If a job needs to be saved to an external location outside of OP, select the job and click the Export Job button. This will export the job and any queries associated with that job as an XML file to a specified folder on the computer.
  • Import a Job: If an exported job (or another job created from a different source) needs to be brought into OP on your computer, click the Import Jobs button and select the job .xml file. The job and queries for that job will be brought into the OP Database Viewer.

Note: After you import a job, you can:

  • Run the complete job from the SQL Jobs tab.
  • Run the job’s individual queries from the SQL tab.