We are currently updating the OP Help Center content for the release of OP 14.19 or OP 19. OP 19 is a member of the certified OP 14 family of products (official version is 14.19.1), which you may see in your software (such as in Help > About) and in the Help Center tabs labeled 14.19. You may also notice that the version number in content and videos may not match the version of your software, and some procedural content may not match the workflow in your software. We appreciate your patience and understanding as we make these enhancements.

OP Database Viewer Basics

Version 14.19
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. Only users with permission Tools_DBViewer have access to the Database Viewer.

Note: The 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, 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.

Run, Save, Select, and Export a Query

Note: Some 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.

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 14, 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 14 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 14 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 14.10

OP DB Viewer Basics

Overview

Office Practicum Version 14 (OP 14) includes the OP DB Viewer, which functions as a database editor and SQL query tool. The OP Database Viewer allows you to run, save, and export data within the database viewer to quickly provide all the OP 14 data necessary based on a query.

Note: The 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 queries during hours when users are not running OP. 

Providing Access

Only users with SystemAdmin rights have access to the Database Viewer. To provide SystemAdmin rights:

  1. Select: Utilities / System Admin / Security Administration in the main menu.
  2. Select the Groups tab.
  3. Locate SystemAdmin under the Groups column. Verify Tools_DBExporter and Tools_DBViewer are listed under SystemAdmin.
  4. Click the Membership tab.
  5. Select SystemAdmin in the Groups column.
  6. Select the user to have SystemAdmin rights in the Users column.
  7. Click the left Red Arrow () button. The user in the Membership column will be added to the SystemAdmin group in the Groups column.

Opening the Database Viewer

To access the OP DB Viewer from the main menu, select: Tools / Database Viewer.

Database Viewer Layout Overview

  • Table List pane (left pane): Contains all the available database tables found in OP 14.
  • 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, 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.

Create a Query

  1. Create SQL code. Copy and paste code into the text box beneath the Results filename field.
  2. Enter query details in the Description field.
  3. Click the SaveSQL Query Text Under a New Name button.


  1. Enter the query name in the popup window and click OK. The query is created.

Run, Save, Select, and Export a Query

Note: Some 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.
  • Run a Query: After a query is created, click the Run button to run the query. The results are displayed in the lower SQL pane.
  • 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.

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 14, 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 14 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 14 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.