Creating Custom Reports

DrCloudEHR includes a robust custom report generator that enables you to create custom reports that suit your needs. You can include any system and user-defined data, which is stored in the DrCloudEHR database, in reports.

Data in the database is stored in tables, which in turn contain fields that each contain a single piece of data. For example, patient information is stored in a table named "patient_data". The table in turn contains a field named "pid" that contains patient IDs. In the custom report generator, the table and field are displayed as patient_data.pid.

You should be familiar with how data is stored in your system in order to map a form field to a database field, and have some familiarity with database concepts, including schema structure and SQL queries. For details on the DrCloudEHR database schema design see DrCloudEHR Database Design.

When you create a report, you create a SQL query that retrieves the data you want to include from the database. You can click the Preview Query button to see the query run to build your report.

You can also create your own SQL query directly, instead of using the report generator. For more information, see Create Your Own SQL Query.

This topic contains the following sections:

Create a New Report

1. Click the Practice tab.
2. Click the Custom Reports tab in the left hand navigation bar, and then click New Report.

Enter a Name and Description

1. Enter a name and optional description for the report. When you save the report, the name is displayed under Custom Reports in the Reports module.
2. Click Next to open the Topics/Tables page.

Select Topics and Tables Containing the Data to Include

The tables containing data are organized into categories, or topics. When you select a topic, the application displays the corresponding tables. You then select the tables you want to include in your report.

For example, assume you want to generate a report the shows patient discharge data. You select the Patient Data table and the ADT Data table, which contains admit and discharge data, as shown in the image below:

1. Select each category you want to include from the Topics field. Tables within the category load in the Tables field.
2. Select the tables that contain the data you want to include in your report, and then click the arrow button to move them to the Selected Tables field.
3. After you move the tables containing the data to the Selected Tables field, click Show Fields to load the fields in the selected tables. The fields do not load in the current page; you must select Next to see the fields.
4. Click Next to see the fields in the Matching page.

Match or Join Fields in Tables

Next, identify the connection between data elements in the report. Because you want to include data from multiple tables, you specify the relationship between tables that you can use to render data.

The report is based on patients, so you match fields that contain common data in both tables. Because both tables contain patient ID, you match the field containing the patient ID in the Patients table (patient_data.pid) with the patient ID field in the ADT table (adt_data.pid), as shown in the image below:

In database terminology, you are matching the primary key in one table (Patients) with a foreign key in the other table (ADT).

When you match fields, you specify how they relate to one another. In SQL terminology, you are performing a JOIN operation to combine data from two or more tables based on a common field. 

Select the Fields to Include

Select the fields you want to include in your report. The data is displayed in the report data based on the order of fields in the right column.
The label for a field is shown as the column header in the report. You can change the column header to a more readable name. For example, you can change pid to Patient ID, as shown in the image below:

1. Select the fields you want to include in your report. Use the arrow keys to move fields into the right column.
2. Select a field, and then use the Up and Down keys to set the order.
3. Double click a field to add a display name to use as column header in report.
4. Enter the column name in Display Text dialog, and then click Change.
5. Click Next.

Add Predefined Filters

The report generator provides you with predefined filters that you can apply to table fields to refine results. For example, you might decide to use the Date Range filter, which adds a To and From filter to the report, to filter results retrieved from the adt_data.discharge field based on dates, as shown in the image below:

Filters you add appear at the top of the report. For best performance, do not include more than three filters in your report.

1. Select the field to apply the filter to in the left column.
2. Select the filter in the right column. 
3. Click Add.
4. Click Next.

Add Custom Filters

You can also create custom filters to limit results returned from the database. You use the following operators in filters:

You can use AND and OR operators to use multiple filters in a report. All of the filters you specify affect the results returned from the database.

For example, assume you want to include the Standard insurance company in your report, but exclude the Mutual insurance company. You would create a filter similar to the example below:

1. Select a field to filter on.
2. Select the operator to apply. 
3. Specify the text or number string to match, or click the calendar icon to specify a date.
4. Click Add.
5. Select AND or OR to include another filter.
6. Click Next.

The below image illustrates the example:

Group Data

You can group common data together to limit data included in your report. For example, assume that you want to group all of the data for a single facility together in the report, rather than show multiple entries for the facility.

1. Select the field containing the data to group.
2. Click the arrow key to move the data into the right column.
3. Click Next.

Set the Sort Order the Data Displayed

Specify how to sort or order data in the report. You sort results by field.

1. Select a field.
2. Select Ascending or Descending to set the field's position in the list.
3. Click Next.

Totals and Averages

You can further refine the data by performing calculations on the values in a column in the report. For example, you might want to calculate the average number of encounters per patient. 

You can perform the following calculations:

1. Select the field, which is displayed in the report as a column, to perform a calculation on.
2. Select the operator to use.
3. Enter the name of the column that will contain the results of the calculation. The application adds the column to the report.
4. Click Add. 
5. Click Next when finished.

Show Total Number of Occurrences of a Field

You can include the total count of occurrences of one or more fields in the report. The results are shown at the bottom of the report.

1. Select the field to calculate the total count for.
2. Click the arrow key to move the field into the right column.
3. Click Next.

Secure the Report

Specify the access control list (ACL) groups who can view the report.

For more information, see Manage Access Control Lists.

Create Your Own SQL Query

When you create a report using the DrCloudEHR report generator, you are actually creating a SQL query that retrieves the data you want to include from the DrCloudEHR database. However, if you have the skills and have knowledge of the database schema, you can create your own SELECT query, instead of using the report generator.

1. Enter a name and optional description for the report.
2. Click Next.
3. Click the 12: Custom Query tab.
4. Click Next after you enter the query string.

Save the Report

Click Save Report to save your new report. Note that you must specify the access control list (ACL) groups who can view the report before you can save it. 

After you save the report, it appears under Custom Reports in the Reports module.