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.

Designing Reports

Version 14.19
If You Choose to Customize Reports Yourself:
Please proceed with caution, as changes to reports may be difficult to undo. It is recommended that you always make a copy of a report prior to making modifications. Once you have successfully changed the copy, you can rename it and replace the original report.

You can use the OP Reports program to run and modify existing reports, and to design new ones. Below is a guide describing how to use OP Reports.

Brief Explanation of Databases

The Office Practicum database stores all data in tables. For example, patient register information is stored in the REGISTER table, and all payments and charges are stored in the ARCHIVE_TRANSACTIONS table. In OP Reports, a report is based primarily on a single table. These tables are made up of fields. Each field contains a specific piece of data. For example, in the REGISTER table, the first name of the patient is stored in the FNAME field. When information from other tables is needed, they can be linked or joined to the first table by common fields. A query defines the conditions for what data is selected from the database. The information displayed in the report is generated by selected fields, based on conditions defined by the query.

The following instructions describe how to create a report displaying all patients that have visited the office within a specified date range.

Creating a new Report

First, open and log into OP Reports.  Your log in information is the same as for Office Practicum.  The main window of OP Reports opens to show all installed reports organized into folders.  To create a new report, click on the main menu and select File > New > Report.  You can also create a new report by clicking the new report icon button on the  toolbar.  A new report will open in a Report Builder window.  Saving the newly created report at this time is recommended.  Select File > Save As... from the menu to open the save window. Select which folder the report needs to be saved in, title the report relating to what the report is about, and click 'Save'.  Now you are ready to begin customizing your very own report.

Selecting Tables

Before you can add any fields to a report, you need to select which tables the query is going to pull information from.  To select the table, click on the Data tab below the menu bar on the design window.  This tab will display a visual representation of the selected data.  To add a table, select File > New... from the menu toolbar.  This will open a window with two options for creating a query.  Select Query Wizard and click  the 'OK' button.  The first step of the wizard is to select the tables that you want to query.  For this demonstration, we need to select the REGISTER table because most of the information that will be displayed on the report is information stored in the register.  The next step in the wizard is to select the fields you would like to use in the report.  (ie PATNO, FNAME, LNAME, BIRTHDAT, SEX, and BILLTWO)  Most of the fields in the OP Database are descriptive of the information stored in then (PATNO), but there are certain cases that deviate from this (BILLTWO, which is the patient's most recent visit date).  Click the NEXT button.  The next page on the query is for calculations, which we do not need for this demonstration.  At this moment, we also don't need to add groups, search criteria or an order, so we can click the Finish button.

The above window should now be visible on the Data tab.  Note the button toolbar on the new window:  these can be used to edit the query that you just created using the wizard.

Report Layout

To begin designing the layout of the report, click on the Design tab. You'll see that there are three sections on the current report; Header (which prints at the top of every page), Detail (which will print once for each row in the table), and Footer (which prints at the bottom of every page).  You can also add a Title and a Summary by selecting Report >Title and Report >Summary from the menu toolbar.  Title prints at the beginning of the report, and Summary prints at the end.  These sections can be resized by dragging the gray divider bars up and down.  To add components to the report, click the icon for the component you would like to add, then click on the area of the report that you would like to add it too.  The components most often used are as follows:

Labels are used to add text to a report. System Variables can be used to display the current date and time or page numbers.   Variables display user defined calculations. DBText components display the data in the selected field.

Labels

Labels are most frequently used to create titles, and to label rows and columns.  Click the label icon button and place it in either the Title or Header section.  Using the formatting toolbar,  set the font to an appropriate size for a page title.  Change the text of the label by typing into the 'Set Value' textbox located at the top of the window below the Design tab.  We also need to add column titles, which should be placed towards the bottom of the header section.  For this report, we need a label for Patient, D.O.B, Sex, and Last Visit.

System Variables

It is a good idea to display the page count, and the date the report was printed.  This is done via System Variables.  Click the system variable button icon, and place it in the header or title.  It will default to display the current date.  Click the system variable button icon again and place it in the footer section.  This will serve as a page count.  To change the text of a system variable, select the appropriate option from the 'Variable Type' drop down box located at the top of the screen.  Select PageSet from the drop down menu to display the page number in a '# of #' format.

Variables

Variables are used to display calculated information on a report.  You can also use them to format data in a particular way.  Because the patient's first and last name are stored in two separate fields, we will need to use a variable to combine them into one line.  To do this, click on the Variable button icon and place it in the Details section below the 'Patient' label you created earlier.  To set the calculation for a variable, right click and select 'Calculations...' from the pop-up menu.  This will open a Calculations window, where you will enter the formula.  For this report, we want to display the patient's name in 'Last, First' form.  To do this,  enter the following text into the 'Calculations' window.

The variable will display whatever the 'Value' is set to.  This line of code pieces together the LNAME field, a comma and space, and the FNAME field.  To make sure there are no errors in the calculation, it is a good idea to right click and select Compile before you click the OK button to close the calculation window.  If there are no errors, a textbox will appear at the bottom of the window displaying 'Compile Completed: 0 Errors'.

DB Text

DB Text components display the information in the fields of the database as it is stored.  They display the majority of the information seen in a report.  To add a DB Text component, click the button icon and place it in the Detail section below one of the column labels you created earlier.  We need a total of 4 DBText components for this report.  After the component is placed on the report, you need to select which information will be displayed.  Select the component, then select the appropriate field from the 'Data Field' drop down box at the top of the screen.  For DBText components, you will need to right click and check AutoSize so that the size of the text field will display all of the information stored in the field.

Lines and Shapes

At this point, you may add lines and shapes to the report that will make it more aesthetically pleasing and easy to read once it is printed.  To add a line, click the line button icon and place the line where it is needed on the report.  It is a good idea to place a line under the column labels in the header.  Lines can also be used in the detail section as a divider between each entry in the database.  There are formatting options on the toolbar for changing the width, style, and color of the line.  Shapes can also be added to the report, and are most commonly used to set a background for text in the title and headers.  For this report, we're going to add a rounded rectangle to the title.  Place a shape on the report, then drag the corners to an appropriate size.  Options to change the fill color, line color, and shape type can be changed on the toolbar.

At this point, the report should look like this in design view:

Sorting and Searching

To preview what the report will look like once printed, click the Preview tab at the top of the window.  You will notice that this report prints one line for each patient in the register, and they are currently listed in no particular order.  To change this, we are going to add a search and a sort to the query we created earlier.  Click on the Data tab, then click on the sort icon on the REGISTER table.  A query designer window will pop up.  Here, you can choose how you would like the report to be sorted by double clicking on that field.  For reports based on the register, you will most often want to sort by LNAME or by PATNO.  After you've added a sort, click on the Search tab on the query design window.  This will restrict the data that will display when the report is printed.  For this report, we want to search by BILLTWO, so that the report only prints out patients with a 'Last Visit' between selected dates.  Double click BILLTWO to add it to the criteria.  Then set the Operator to Between.  You have the option to set the Value, or to choose 'AutoSearch' so it can be set by the user when he or she prints the report.  For this report, we are going to check the AutoSearch checkbox.  Click 'OK' to save the changes made to the query.  Go back to the Design tab, File->Save, and exit the report builder.

Running your Report

Now that your report is finished being designed, it is ready to be executed and printed.  Locate the folder where you saved your report, and double click on it to run it.  If you added the search correctly, a Search pop-up will appear prompting you to set the date range the report is going to print.  Set the date range, and click OK.  Your finished report will run as a Print Preview.  From here you can print the final report.

Hints and tips

Joining Tables

There are times when you may need to pull information from more than one table.  You can do this by joining an additional table or tables to your query.  For example, you can join the DRLABEL table to the REGISTER to get the initials of the patient's primary provider.  (The register only stores the provider's ID.)   Go to the Data tab, and click the tables icon button to open the Query Designer.  Double click the DRLABEL table to open the Join Table window.  From here, you set the information that these two tables have in common.  In the 'Joined Fields' section of the window, you will see that Report Builder has auto selected fields with the same names from each table.  We only want these tables to be joined by the ADDR ID field, so remove all others by selecting and clicking the 'Remove' button.  Click OK to confirm the join.  Next, click on the Fields tab on the query designer to add fields from the DRLABEL table to Selected Fields.  Double click to add DRINIT, the field that stores the provider's initials.  We also want to search by provider, so click on the Search tab, and double click to add ADDR ID to the search criteria.  Set the operator to In List, and select the AutoSearch checkbox.  Click OK to save changes to the query.  Add a label and a DBText component to display the DRINIT field that we just added to the query in the report.

Grouping

You can group data to add further organization to your report.  For this report, we can group by primary provider, so that patients will show up under the group and we don't have to print the provider's name for every patients.  If you have not done so already, join the DRLABEL table to the REGISTER using the instructions above.  However, add SIGNAME and ADDR ID to selected fields instead of DRINIT.  SIGNAME stores the full name of the provider.  In order for the group to work correctly, we also need to sort by provider.  Click the Sort tab on the Query Designer window and double click to add ADDR ID.  Then, using the arrows on the right side of the window, move ADDR ID above LNAME.  This sets ADDR ID to the primary search.  Click OK to save changes to the query.  Click the Design tab on the Report Builder window.  To add a group, select Report -> Groups... from the main menu.  A Groups window will pop up, where you will select 'REGISTER.ADDR ID' from the drop down list, and click the Add button.  Uncheck 'Keep group together'.  Click OK to add the group.  You will notice new 'Group Header' and 'Group Footer' sections have been added to the design mode window.  Drag 'Group Header' to an appropriate size, and add the 'SIGNAME' field using a DBText component.  The information in the group header will only print out at the top of the group, and the top of the page if the group expands to more than one page in length.  You can view the final result on the Preview tab.

Common Tables

  • REGISTER: Patient's demographic information
  • DRLABEL: Provider Information
  • SCHEDULE: Appointments
  • PHYSICAL: Patient Well Visits
  • ENC_NOTE: Patient Sick Visits
  • INS_CARRIER: Insurance carrier information
  • STAFF1: All staff members
Version 14.10
If You Choose to Customize Reports Yourself:
Please proceed with caution, as changes to reports may be difficult to undo. It is recommended that you always make a copy of a report prior to making modifications. Once you have successfully changed the copy, you can rename it and replace the original report.

You can use the OP Reports program to run and modify existing reports, and to design new ones. Below is a guide describing how to use OP Reports.

Brief Explanation of Databases

The Office Practicum database stores all data in tables. For example, patient register information is stored in the REGISTER table, and all payments and charges are stored in the ARCHIVE_TRANSACTIONS table. In OP Reports, a report is based primarily on a single table. These tables are made up of fields. Each field contains a specific piece of data. For example, in the REGISTER table, the first name of the patient is stored in the FNAME field. When information from other tables is needed, they can be linked or joined to the first table by common fields. A query defines the conditions for what data is selected from the database. The information displayed in the report is generated by selected fields, based on conditions defined by the query.

The following instructions describe how to create a report displaying all patients that have visited the office within a specified date range.

Creating a new Report

First, open and log into OP Reports.  Your log in information is the same as for Office Practicum.  The main window of OP Reports opens to show all installed reports organized into folders.  To create a new report, click on the main menu and select File > New > Report.  You can also create a new report by clicking the new report icon button on the  toolbar.  A new report will open in a Report Builder window.  Saving the newly created report at this time is recommended.  Select File > Save As... from the menu to open the save window. Select which folder the report needs to be saved in, title the report relating to what the report is about, and click 'Save'.  Now you are ready to begin customizing your very own report.

Selecting Tables

Before you can add any fields to a report, you need to select which tables the query is going to pull information from.  To select the table, click on the Data tab below the menu bar on the design window.  This tab will display a visual representation of the selected data.  To add a table, select File > New... from the menu toolbar.  This will open a window with two options for creating a query.  Select Query Wizard and click  the 'OK' button.  The first step of the wizard is to select the tables that you want to query.  For this demonstration, we need to select the REGISTER table because most of the information that will be displayed on the report is information stored in the register.  The next step in the wizard is to select the fields you would like to use in the report.  (ie PATNO, FNAME, LNAME, BIRTHDAT, SEX, and BILLTWO)  Most of the fields in the OP Database are descriptive of the information stored in then (PATNO), but there are certain cases that deviate from this (BILLTWO, which is the patient's most recent visit date).  Click the NEXT button.  The next page on the query is for calculations, which we do not need for this demonstration.  At this moment, we also don't need to add groups, search criteria or an order, so we can click the Finish button.

The above window should now be visible on the Data tab.  Note the button toolbar on the new window:  these can be used to edit the query that you just created using the wizard.

Report Layout

To begin designing the layout of the report, click on the Design tab. You'll see that there are three sections on the current report; Header (which prints at the top of every page), Detail (which will print once for each row in the table), and Footer (which prints at the bottom of every page).  You can also add a Title and a Summary by selecting Report >Title and Report >Summary from the menu toolbar.  Title prints at the beginning of the report, and Summary prints at the end.  These sections can be resized by dragging the gray divider bars up and down.  To add components to the report, click the icon for the component you would like to add, then click on the area of the report that you would like to add it too.  The components most often used are as follows:

Labels are used to add text to a report. System Variables can be used to display the current date and time or page numbers.   Variables display user defined calculations. DBText components display the data in the selected field.

Labels

Labels are most frequently used to create titles, and to label rows and columns.  Click the label icon button and place it in either the Title or Header section.  Using the formatting toolbar,  set the font to an appropriate size for a page title.  Change the text of the label by typing into the 'Set Value' textbox located at the top of the window below the Design tab.  We also need to add column titles, which should be placed towards the bottom of the header section.  For this report, we need a label for Patient, D.O.B, Sex, and Last Visit.

System Variables

It is a good idea to display the page count, and the date the report was printed.  This is done via System Variables.  Click the system variable button icon, and place it in the header or title.  It will default to display the current date.  Click the system variable button icon again and place it in the footer section.  This will serve as a page count.  To change the text of a system variable, select the appropriate option from the 'Variable Type' drop down box located at the top of the screen.  Select PageSet from the drop down menu to display the page number in a '# of #' format.

Variables

Variables are used to display calculated information on a report.  You can also use them to format data in a particular way.  Because the patient's first and last name are stored in two separate fields, we will need to use a variable to combine them into one line.  To do this, click on the Variable button icon and place it in the Details section below the 'Patient' label you created earlier.  To set the calculation for a variable, right click and select 'Calculations...' from the pop-up menu.  This will open a Calculations window, where you will enter the formula.  For this report, we want to display the patient's name in 'Last, First' form.  To do this,  enter the following text into the 'Calculations' window.

The variable will display whatever the 'Value' is set to.  This line of code pieces together the LNAME field, a comma and space, and the FNAME field.  To make sure there are no errors in the calculation, it is a good idea to right click and select Compile before you click the OK button to close the calculation window.  If there are no errors, a textbox will appear at the bottom of the window displaying 'Compile Completed: 0 Errors'.

DB Text

DB Text components display the information in the fields of the database as it is stored.  They display the majority of the information seen in a report.  To add a DB Text component, click the button icon and place it in the Detail section below one of the column labels you created earlier.  We need a total of 4 DBText components for this report.  After the component is placed on the report, you need to select which information will be displayed.  Select the component, then select the appropriate field from the 'Data Field' drop down box at the top of the screen.  For DBText components, you will need to right click and check AutoSize so that the size of the text field will display all of the information stored in the field.

Lines and Shapes

At this point, you may add lines and shapes to the report that will make it more aesthetically pleasing and easy to read once it is printed.  To add a line, click the line button icon and place the line where it is needed on the report.  It is a good idea to place a line under the column labels in the header.  Lines can also be used in the detail section as a divider between each entry in the database.  There are formatting options on the toolbar for changing the width, style, and color of the line.  Shapes can also be added to the report, and are most commonly used to set a background for text in the title and headers.  For this report, we're going to add a rounded rectangle to the title.  Place a shape on the report, then drag the corners to an appropriate size.  Options to change the fill color, line color, and shape type can be changed on the toolbar.

At this point, the report should look like this in design view:

Sorting and Searching

To preview what the report will look like once printed, click the Preview tab at the top of the window.  You will notice that this report prints one line for each patient in the register, and they are currently listed in no particular order.  To change this, we are going to add a search and a sort to the query we created earlier.  Click on the Data tab, then click on the sort icon on the REGISTER table.  A query designer window will pop up.  Here, you can choose how you would like the report to be sorted by double clicking on that field.  For reports based on the register, you will most often want to sort by LNAME or by PATNO.  After you've added a sort, click on the Search tab on the query design window.  This will restrict the data that will display when the report is printed.  For this report, we want to search by BILLTWO, so that the report only prints out patients with a 'Last Visit' between selected dates.  Double click BILLTWO to add it to the criteria.  Then set the Operator to Between.  You have the option to set the Value, or to choose 'AutoSearch' so it can be set by the user when he or she prints the report.  For this report, we are going to check the AutoSearch checkbox.  Click 'OK' to save the changes made to the query.  Go back to the Design tab, File->Save, and exit the report builder.

Running your Report

Now that your report is finished being designed, it is ready to be executed and printed.  Locate the folder where you saved your report, and double click on it to run it.  If you added the search correctly, a Search pop-up will appear prompting you to set the date range the report is going to print.  Set the date range, and click OK.  Your finished report will run as a Print Preview.  From here you can print the final report.

Hints and tips

Joining Tables

There are times when you may need to pull information from more than one table.  You can do this by joining an additional table or tables to your query.  For example, you can join the DRLABEL table to the REGISTER to get the initials of the patient's primary provider.  (The register only stores the provider's ID.)   Go to the Data tab, and click the tables icon button to open the Query Designer.  Double click the DRLABEL table to open the Join Table window.  From here, you set the information that these two tables have in common.  In the 'Joined Fields' section of the window, you will see that Report Builder has auto selected fields with the same names from each table.  We only want these tables to be joined by the ADDR ID field, so remove all others by selecting and clicking the 'Remove' button.  Click OK to confirm the join.  Next, click on the Fields tab on the query designer to add fields from the DRLABEL table to Selected Fields.  Double click to add DRINIT, the field that stores the provider's initials.  We also want to search by provider, so click on the Search tab, and double click to add ADDR ID to the search criteria.  Set the operator to In List, and select the AutoSearch checkbox.  Click OK to save changes to the query.  Add a label and a DBText component to display the DRINIT field that we just added to the query in the report.

Grouping

You can group data to add further organization to your report.  For this report, we can group by primary provider, so that patients will show up under the group and we don't have to print the provider's name for every patients.  If you have not done so already, join the DRLABEL table to the REGISTER using the instructions above.  However, add SIGNAME and ADDR ID to selected fields instead of DRINIT.  SIGNAME stores the full name of the provider.  In order for the group to work correctly, we also need to sort by provider.  Click the Sort tab on the Query Designer window and double click to add ADDR ID.  Then, using the arrows on the right side of the window, move ADDR ID above LNAME.  This sets ADDR ID to the primary search.  Click OK to save changes to the query.  Click the Design tab on the Report Builder window.  To add a group, select Report -> Groups... from the main menu.  A Groups window will pop up, where you will select 'REGISTER.ADDR ID' from the drop down list, and click the Add button.  Uncheck 'Keep group together'.  Click OK to add the group.  You will notice new 'Group Header' and 'Group Footer' sections have been added to the design mode window.  Drag 'Group Header' to an appropriate size, and add the 'SIGNAME' field using a DBText component.  The information in the group header will only print out at the top of the group, and the top of the page if the group expands to more than one page in length.  You can view the final result on the Preview tab.

Common Tables

  • REGISTER: Patient's demographic information
  • DRLABEL: Provider Information
  • SCHEDULE: Appointments
  • PHYSICAL: Patient Well Visits
  • ENC_NOTE: Patient Sick Visits
  • INS_CARRIER: Insurance carrier information
  • STAFF1: All staff members