Report Query Builder - Report on clinic utilisation using the Alias Manager

What is the purpose of the article?

This article will walk you through the process of building a report to show the percentage of time in clinic that each doctor spends seeing patients. You'll include filters to show utilisation at a particular site and within a particular date range.

• You'd like the report to look something like this:

In order to create this, you'll need to extract the first three columns of data from a report, then add the fourth column in Excel.

The complexity and time required here are high, as this is an advanced report that includes filters, parameters and alias tables.

Click here for other report examples.

Building a report on clinic utilisation

The process of building a report can be broken down into the following steps:

1. From the Start Page navigate to Admin > Report management.*

2. Click New report query to open the Query Builder.

3. Select Medical Person Demographics from the list of tables as the Root Table.**

*Building reports requires access to the Admin section.

**Selecting a Root Table determines what will be displayed in each Row of your report e.g. selecting Invoices as the root table will result in a report displaying an invoice in each row.

Click here for a detailed article on selecting a root table.

4. Click Next to open the Column Builder.

5. In the upper left corner, click New Column.

6. In the Select Column dialogue that appears, expand the Column folder to add columns to the report.*

For this report you want to include columns that return the following information**:

*Holding down the Ctrl key on your keyboard allows adding multiple columns to a report in a sequence.

** Some of the desired information will be obtained from columns that do not belong to our root table. They are available in linked tables.

• Doctor name
• Session duration
• Appointment duration

7. From the Columns folder, add the following:

• Full Name

Setting up alias tables

Aliases are used when you have a column with multiple potential values, such as those found in one-to-many relations. (Click here for more information on table relations).

In order to get the session duration, you'll need to set up an alias table. The alias table will allow us to pick a site and a date range before calculating the total duration of each doctor's sessions.

To begin creating an alias:-

1. Click Alias Manager in the top bar.
2. Click New Alias, then give the alias a name, e.g. Sessions at site within data range.
3. Click New Reference.
4. In the Select Reference dialogue that appears, click on Site Sessions table.
5. Then click on Select to reference.*

*Please note: An alias needs a root table on which to filter. An alias can be thought of as a sub-query or a custom table.

Adding Filters to an Alias Table

In this example, you'll need to add filters to your alias table to restrict the sessions to a particular site and date range. First, you'll add a condition to list sessions at one site only. To do this:-

1. Click Edit under Filters to open the Filter Builder.
2. Click Add Condition.
3. In the new row that appears, click on Select column or parameter...
4. In the Select column or parameter dialogue that appears, click Select Other Column.
5. In the Select Other Column dialogue that appears, expand the columns folder, choose Site Id and click Select.
6. Back in the Filter Builder, change value to parameter (so that the user can choose a particular site).

Next, you'll add conditions to restrict the sessions to a particular date range. You'll need two conditions - one that restricts them to those that start after a certain date, then another to restrict them to those that end before a certain date.

For the first condition:

7a. In the Filter Builder dialogue, click Add Condition.

7b. In the row that appears, click Select column or parameter...

7c. In the Select column or parameter dialogue that appears, click Select Other Column.

7d. In the Select Other Column dialogue that appears, expand the Columns folder, choose the Start column and click Select.

For the second condition:

8a. In the Filter Builder dialogue, click Add Condition.

8b. In the row that appears, click Select column or parameter...

8c. In the Select column or parameter dialogue that appears, click Select Other Column.

8d. In the Select Other Column dialogue that appears, expand the Columns folder, choose the Finish column and click Select.

Adding Parameters to an Alias Table

Ideally, you'd want the user to be able to input a site and date range to filter by, so that they can re-run the report for different criteria.

To do this, you’ll need to parameterise your existing filters. Using parameters lets you choose which values to include in your filter conditions.

In the Filter Builder, for each condition:

1. Change Value to Parameter.
2. Select the field that now says Select Parameter.
3. In the Parameter window that appears, click New Parameter.
4. In the New Data Parameter dialogue that appears, provide a Parameter Name*.
   Click OK.
5. In the Parameter window, click on the newly created parameter and click on Select at the bottom of the screen.
   
   The parameter is applied to the condition. Repeat the above steps for further conditions.

The parameter name is what the user will see next to the field where they need to input a value, so it should be descriptive. You would choose Site for the first condition, then From Date and To Date for the next two conditions. Parameters are mandatory by default, which means the user has to include a value before they can run the report. You can make parameters optional, which means that the report will run without those filters unless specified, or provide a default value

For this example, set up the parameters as shown below:

6. When Conditions have been set up, click Close in the Filter Builder.

7. Click Proceed to exit the Alias Manager.

Testing the Alias

You can check if our alias is working as expected by adding a column from it to our report and previewing the results. The alias table is available at the bottom of the list of tables:

1. Click New Column in the upper left corner.
2. Scroll down to the bottom of the list of tables and expand the Alias table.
3. Expand the Column folder.
4. Choose the Duration column from the alias table and click Select.
5. Switch the aggregate to Sum
   (This means our report will return the total duration of each doctor's sessions over our date range).
6. Click Next in the bottom right-hand corner of the screen to preview the results
   (You should be prompted to pick a site and enter a date range)
7. Enter some dates and click OK.
   (The report should list each doctor with the total duration of their sessions in minutes).
8. Click Previous in the bottom left-hand corner to return to the report query builder.

Building subsequent Aliases

Next, you'll need to set up a second alias table to get the total duration of each doctor's appointments.

Using the previous alias table as a guide, set up a second alias table as follows:

Then, add 3 filter conditions to only return appointments at a particular site, within a particular date range (You can reuse the Site, From date and To date parameters that were set up earlier). Furthermore, include 2 more conditions to filter out cancelled and not arrived appointments (this ensures that only time spent actually seeing patients is included). To do this:-

1. Click Edit under Filters to open the Filter Builder:

For the first condition:

2a. Click Add Condition.

2b. Click Select column or parameter...

2c. Click Select Other Column.

2d. Expand the Site table and open the Column folder.

2e. Choose the ID column and click Select.

2f. Switch Value to Parameter and click Select parameter...

2g. Choose the Site parameter and click Select.

For the second condition:

3a. Click Add Condition.

3b. Click Select column or parameter...

3c. Click Select Other Column.

3d. Expand the Column folder.

3e. Choose the Start column and click Select.

3f. Switch Is on to Is on or after.

3g. Switch Value to Parameter and click Select parameter...

3h. Choose the From date parameter and click Select.

For the third condition:

4a. Click Add Condition.

4b. Click Select column or parameter...

4c. Click Select Other Column.

4d. Expand the Column folder.

4e. Choose the Finish column and click Select.

4f. Switch Is on to Is on or after.

4g. Switch Value to Parameter and click Select parameter...

4h. Choose the To date parameter and click Select.

For the fourth condition:

5a. Click Add Condition.

5b. Click Select column or parameter...

5c. Click Select Other Column.

5d. Expand the Column folder.

5e. Choose the Cancelled column and click Select. (leave the tick box unchecked, which means cancelled appointments will not be included).

For the fifth condition:

6a. Click Add Condition.

6b. Click Select column or parameter...

6c. Click Select Other Column.

6d. Expand the Column folder.

6e. Choose the Did Not Arrive column and click Select. (leave the tick box unchecked, which means DNA appointments will not be included)

The parameters are now applied to the conditions.

Now that's all set up, close the filter builder and click Proceed to exit the alias manager.

Using Alias Tables in the report query and exporting results

Having set up both required alias tables, you will use the 2nd one in the report query, in the manner described in the previous chapter.

The 2nd alias table is available at the bottom of the list of tables. To use it:-

1. Click New Column in the upper left corner.
2. Scroll down to the bottom of the list of tables and expand the Appointments at site within date range alias table.
3. Choose the Duration* column from the alias table and click Select. 

4. Switch the aggregate to Sum (This means our report will return the total duration of each doctor's appointments over our date range; cancelled and DNA appointments will not be included)

5. Enter some dates and click OK. (The report should list each doctor with the total duration of their sessions in minutes).

6. Click Previous in the bottom left-hand corner to return to the report query builder.

7. Click Next in the bottom right-hand corner to preview the results (You should be prompted to select a site and a date range; your report should include a new column for the appointment duration)

To calculate the percentage of time spent seeing patients, you will need to export the results to Excel. To do this:-

1. Click the  button in the lower left-hand corner of the preview screen to export the results
2. Add a column in Excel to calculate the percentage

Review date

This article was last updated on 2nd December 2024