Version 14.19

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

1. Click the Tools tab.
2. Click the OP Reports button.
3. Log in to OP Reports. Your log in information is the same as for Office Practicum. The main window of OP Reports displays all installed reports organized into folders. 
4. Click File and select New > Report or click the New Report button on the  toolbar.  A new report is displayed in the Report Builder window. 
5. Select File > Save As (recommended).
6. Select the Folder the report needs to be saved in, enter a Report Title, and click the Save button.

Now, you are ready to begin customizing your 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.  

1. To select the table, click the Data tab below the menu bar on the design window. This tab displays a visual representation of the selected data.  
2. To add a table, select File > New... from the menu toolbar. A window is displayed with two options for creating a query.  
3. Select Query Wizard and click  the OK button. The first step of the wizard is to select the tables that you want to query.  
4. Select the REGISTER table, because most of the information that will be displayed on the report is information stored in the register.  
5. 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).  
6. Click the NEXT button. The next page on the query is for calculations. At this moment, we do not need to add groups, search criteria or an order.
7.  Click the Finish button.

The above window is 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:

1. Click  the Design tab. 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).  
2. To add a Title and a Summary, click Report and select Title and/or Summary from the menu toolbar. The Title prints at the beginning of the report, and the Summary prints at the end. These sections can be resized by dragging the gray divider bars up and down.  
3. 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.  

1. Click the Label button and place it in either the Title or Header section.  
2. From the Formatting toolbar, select the Font Size from the drop-down menu.  
3. Enter new Text into Set Value textbox to change the Label text. This field is located at the top of the window below the Design tab.  
4. Add Column Titles and place them towards the bottom of the header section.  
5. 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 by using System Variables.  

1. Click the System Variable button and place it in the header or title. It defaults to display the current date.  
2. Click the System Variable button again and place it in the footer section. This serves as a page count.  
3. Select an option from the Variable Type drop-down menu at the top of the screen to change the text of a system variable.
4. 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, you need to use a variable to combine them into one line.  

1. Click the Variable button and place it in the Details section below the Patient label you created earlier.  
2. To set the calculation for a variable, right-click and select Calculations... from the pop-up menu. The Calculations window is displayed , where you will enter the formula. 
3. For this report, you want to display the patient's name in Last, First form. Enter the following text into the Calculations window.

The variable is displayed whatever the Value is set to. This line of code pieces together the LNAME field, a comma and space, and the FNAME field. To ensure there are no errors in the calculation, right-click and select Compile before you click the OK button to close the calculation window.  If there are no errors, a textbox is displayed at the bottom of the window that says 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:

1. 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.  
2. Select the Component, then select the appropriate Field from the Data Field drop-down menu at the top of the screen.  
3. For DBText components, right-click and select AutoSize so the size of the text field displays 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:

1. Click the Line button 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.  
2. 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:

1. Click the Preview tab at the top of the window. This report prints one line for each patient in the register, and they are currently listed in no particular order. To change this, you need to add a search and a sort to the query created earlier. 
2. Click the Data tab, then click the Sort icon on the REGISTER table. A Query Designer window is displayed. 
3. Double-click the Sort field to determine how you want the report to be sorted. For reports based on the register, you will most often want to sort by LNAME or by PATNO.  
4. After adding a sort, click the Search tab. This restricts the data that is displayed when the report is printed. 
5. 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.  
6. Select Between for the Operator.  
7. You can set the Value, or select AutoSearch so it can be set by the user when he or she prints the report. Select the AutoSearch checkbox.  
8. Click the OK button to save the changes made to the query.  
9. Click the Design tab and select File > Save.
10. Click the X to 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 the Report Name to run it. If you added the search correctly, a Search pop-up is displayed prompting you to set the date range the report is going to print. Select the Date Range, and click the OK button. The finished Report runs 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.). 

1. Navigate to the Data tab, and click the Tables button to open the Query Designer. 
2. Double-click the DRLABEL table to open the Join Table window. From here, set the information that these two tables have in common. In the Joined Fields section of the window, the Report Builder has auto-selected fields with the same names from each table. These tables should be joined by the ADDR ID field.
3. Click the Remove button to remove all other Fields. 
4. Click the OK button to confirm the join.  
5. Click the Fields tab on the query designer to add fields from the DRLABEL table to Selected Fields.  
6. Double-click to add DRINIT, the field that stores the provider's initials.  
7. Click the Search tab to search by provider.
8. Double-click to add ADDR ID to the search criteria.  
9. Set the operator to In List, and select the AutoSearch checkbox.
10. Click the OK button to save changes to the query.  
11. Add a Label and a DBText component to display the DRINIT field that was just added to the query in the report.

Grouping

You can group data to add further organization to your report. For this report, you can group by primary provider, so that patients are displayed under the group and we do not have to print the provider's name for every patients. 

1. 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.  
2. In order for the group to work correctly, we need to sort by provider. Click the Sort tab on the Query Designer window and Double-click to add ADDR ID. 
3. Using the arrows on the right side of the window, move ADDR ID above LNAME. This sets ADDR ID to the primary search. 
4. Click the OK button to save changes to the query. 
5. Click the Design tab on the Report Builder window. 
6. To add a group, select Report > Groups... from the main menu. The Groups window is displayed. 
7. Select REGISTER.ADDR ID from the drop-down menu.
8. Click the Add button.  
9. Deselect the Keep group together checkbox.
10.  Click the OK button to add the Group. The Group Header and Group Footer sections are added to the Design Mode window.  
11. Adjust the Group Header to an appropriate size, and add the SIGNAME field using a DBText component. The information in the group header only prints 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: 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 create a new report by clicking the New Report icon button on the  toolbar.  A new report is displayed in the Report Builder window. Save the newly created report. Select File > Save As... from the menu to open the save window. Select the Folder the report needs to be saved in, enter a Title for the report, and click the Save button. 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:

1. Click the Data tab below the menu bar on the design window. This tab displays a visual representation of the selected data. 
2. To add a table, select File > New... from the menu toolbar. This opens a window with two options for creating a query. 
3. Select Query Wizard.
4. Click the OK button. The first step of the wizard is to select the tables that you want to query. 
5. Select the REGISTER table because most of the information that will be displayed on the report is information stored in the register. 
6. 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).  
7. Click the NEXT button. We do not need to add calculations, groups, search criteria or an order.
8. Click the Finish button.

The window above is 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:

1. Click the Design tab. 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).
2. Add a Title and a Summary by selecting Report >Title and Report >Summary from the menu toolbar. The 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.
3.  To add components to the report, click the Icon for the component you would like to add, then click 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. 

1. Click the Label button 
2. Move it to the Title or Header section. 
3. Using the formatting toolbar,  select the appropriate Font Size for the page title.
4. Enter Text in the Set Value textbox to change the label text.
5. 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 through System Variables. 

1. Click the System Variable button.
2. Move it to the Title or Header section. It defaults to display the current date. 
3. Click the System Variable button again and place it in the footer section. This serves as a page count.  
4. To change the text of a System Variable, select the appropriate option from the Variable Type drop-down menu located at the top of the screen. 
5. 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, you need to use a variable to combine them into one line:

1. Click the Variable button.
2. Move it to  the Details section below the Patient label.  
3. To set the calculation for a variable, right-click and select Calculations... from the pop-up menu. This opens a Calculations window
4. Enter the Formula. For this report, we want to display the patient's name in Last, First form. Enter the following text into the Calculations window.

The variable displays 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 

5. Right-click and select Compile.

6. Click the OK button to close the calculation window. If there are no errors, a textbox is displayed at the bottom of the window that says: 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:

1. Click the Button icon 
2. Move it to the Detail section below one of the column labels you created earlier. There needs to be a total of 4 DBText components for this report. 
3. Select the Component.
4. Select an option from the Data Field drop-down menu at the top of the screen. 
5. For DBText components, right-click and select 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:

1. Click the Line button
2. Move the line to 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.  
3. For this report, 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. This report prints one line for each patient in the register, and they are currently listed in no particular order. To change this, add a search and a sort to the query we created earlier:

1. Click the Data tab
2. Click the Sort icon on the REGISTER table. The Query Designer window will is displayed. 
3. Double-click the Sort field to select how you would like the report to be sorted. For reports based on the register, you will most often want to sort by LNAME or by PATNO.  
4. Click  the Search tab. This restricts the data that is displayed when the report is printed. 
5. For this report, we want to search by BILLTWO, so  the report only prints out patients with a Last Visit between selected dates. Double-click BILLTWO to add it to the criteria. 
6. Set the Operator to Between. 
7. Set the Value, or select AutoSearch so it can be set by the user when he or she prints the report.  
8. Select the AutoSearch checkbox.  
9. Click the OK button to save the changes made to the query.  
10. Navigate 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 the Report Name to run it. If you added the search correctly, a Search pop-up box is displayed that prompts you to set the date range the report is going to print.  Set the Date Range, and click the OK button. Your finished report runs 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.). 

1. Navigate to the Data tab, and click the Tables button to open the Query Designer. 
2. Double-click the DRLABEL table to open the Join Table window. From here, set the information that these two tables have in common. In the Joined Fields section of the window, the Report Builder has auto-selected fields with the same names from each table. These tables should be joined by the ADDR ID field.
3. Click the Remove button to remove all other Fields. 
4. Click the OK button to confirm the join.  
5. Click the Fields tab on the query designer to add fields from the DRLABEL table to Selected Fields.  
6. Double-click to add DRINIT, the field that stores the provider's initials.  
7. Click the Search tab to search by provider.
8. Double-click to add ADDR ID to the search criteria.  
9. Set the operator to In List, and select the AutoSearch checkbox.
10. Click the OK button to save changes to the query.  
11. Add a Label and a DBText component to display the DRINIT field that was 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. 

1. 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.  
2. In order for the group to work correctly, we need to sort by provider. Click the Sort tab on the Query Designer window and Double-click to add ADDR ID. 
3. Using the arrows on the right side of the window, move ADDR ID above LNAME. This sets ADDR ID to the primary search. 
4. Click the OK button to save changes to the query. 
5. Click the Design tab on the Report Builder window. 
6. To add a group, select Report > Groups... from the main menu. The Groups window is displayed. 
7. Select REGISTER.ADDR ID from the drop-down menu.
8. Click the Add button.  
9. Deselect the Keep group together checkbox.
10.  Click the OK button to add the Group. The Group Header and Group Footer sections are added to the Design Mode window.  
11. Adjust the Group Header to an appropriate size, and add the SIGNAME field using a DBText component. The information in the group header only prints 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