QuickReports is a powerful tool that can generate customisable reports to assist you with your practice analysis. It allows you to retrieve records from Genie including patients, procedures and billing and report on various aspects of them. Reports can be printed or exported to Microsoft Excel for further analysis.
How do QuickReports work?
Genie stores records in tables, similar to the tables you would find in a spreadsheet. There is one table in your data file for each type of record - patients, procedures, etc. Each table has several lines, or records, and each record has different fields containing different pieces of information. For example, procedure records have date and description fields, while patient records have first name, DOB and phone number fields.
When running a QuickReport, you will start by selecting a table to indicate which kind of record you would like to report on. Then, you’ll tell Genie which records in the table you are interested in using a search window. Finally, you will specify which columns from the table you would like to see in the report.
There are four steps to creating a QuickReport:
- Create your Query, to specify which records to include in the report (e.g. patients born within a date range).
- Add columns, indicating what information you want to know about those records (e.g. patient name, DOB).
- Sort and format (e.g. list patients alphabetically).
- Generate the report
Before you begin
To ensure Genie is able to find the right records for your report, you will need to determine the best way for Genie to distinguish these records in your data. The best starting point for this is to think is about how you would find these records.
For example, if you would like to report on your new patients, consider how you would identify them. It may be those patients with a particular ‘New Patient’ appointment type. Or, you might use the CreationDate field in the patient record. As a second example, if you were going to report on all breech births, consider how you would identify a pregnancy record as breech - perhaps with a keyword in a particular field of the pregnancy record.
Genie isn’t able to guess which records you are looking for – it can only search on the information entered in your data file. Making sure you know how to identify the records you are looking for from your data will help you search for them appropriately.
In addition to finding the right records, consider how you want them to appear in the report. For example:
- What information do you want to know about them? Do you want some basic information on these records (e.g. patient contact details, or procedure outcomes)? Or do you just want the total number of records in the list?
- Would you like the records to be sorted? e.g. alphabetically by patient name, or chronologically by procedure date.
- Do you need any totals or subtotals? e.g. number of procedures per provider.
To start a new report, go to File > QuickReports – this will bring you to the ‘Table’ tab of the Report Builder window. This tab allows you to choose which table you would like to report on, and then search for particular records within that table.
Note: A table is the group of all records of a particular type. E.g. the ‘Patient’ table contains all patients in the database.
To choose a table, highlight it in the ‘Tables’ list. Keep in mind that this is the table of records you are interested in, not the table of records you will be searching on. For example, to report on patients with a particular pregnancy outcome, you would highlight the ‘[Patient]’ table. But if you would like to know the details of those pregnancies with a particular outcome, you would highlight the ‘[Pregnancy]’ table instead.
If you would like to report on every record in the table, click ‘Load All Records’. Usually, however, you will want to narrow down the list to include only those records you are interested in. To do this, click the ‘Query’ button – this will open a search window, allowing you to specify a field to search on, a comparison, and a value to search with. The selected field, comparison and value will be displayed in the ‘Query Editor’ box at the top of the window.
Note: A field is a particular piece of information that Genie has for each record in a table. E.g. FirstName, Surname and DOB are all fields in the Patient table.
The selected field, comparison and value tells Genie which records you are looking for. For example, the following selection will search the Patient DOB field for all dates ‘greater than’ (more recent than) the 1st January 2007.
After entering the field, comparison and value, click ‘Query’ to run the search. You should see the number of records that met the search requirements displayed in bold in the Report Builder window:
Query Example 1 – all patients born 1970 or earlier
- Go to File > QuickReports.
- Highlight the ‘[Patient]’ table on the left.
- Click ‘Query’.
- Under ‘Available Fields’ on the left, click the arrow next to ‘[Patient]’ to expand out the patient fields.
- Highlight the ‘DOB’ field.
- Under ‘Comparisons’ on the right, highlight ‘is less than or equal to’.
- In the ‘Value’ field, enter the date ‘31/12/1970’.
- Click ‘Query’ to run the search.
If you would like to run a search with multiple criteria (e.g. patients on several medications), select your first field, comparison and value, then click ‘Add Line’ – this will add a second line to the Query Editor, which you can populate with a new field, comparison and value.
For example, selecting the Patient table, then running this two-line query will find all patients who have been prescribed Crestor and Pradaxa:
You can also change the relationship between criteria by clicking the black down arrow next to the query line. For example, to find all patients prescribed either Crestor or Pradaxa (not necessarily both), select the Patient table, construct the above query, then click the down arrow on the second line of the Query Editor and choose the ‘Or’ relationship.
Note: If you have three or more criteria in a query, the relationship should be the same for all of them – do not attempt to have both ‘and’ and ‘or’ in the same query.
Query Example 2 - all appointments in the last month for DVA patients
- In the Report Builder window, highlight the ‘[Appt]’ table.
- Click ‘Query’.
- Under ‘Available Fields’ on the left, double click on ‘[Appt]’ to expand out the fields for this table.
- Highlight ‘StartDate’.
- Under ‘Comparisons’, highlight ‘is greater than or equal to’.
- In the ‘Value’ field, enter the date for the start of the month.
- Click ‘Add Line’.
- Under ‘Comparisons’, highlight ‘is less than or equal to’.
- In the ‘Value’ field, enter the date for the end of the month.
- Click ‘Add Line’.
- Under ‘Available Fields’, double click on ‘PT_Id_Fk’ (it will have ‘[Patient]’ next to it) – this displays the fields in the Patient table.
- In the expanded list, highlight ‘DVANum’.
- Under ‘Comparisons’, highlight ‘is greater than’.
- In the ‘Value’ field, type the number 0.
After finding your records, the next step is to tell Genie what information you would like to know about these records. You can do this by going to the ‘Columns’ tab of the Report Builder window and adding fields to the report.
To add a field, double click on it in the ‘Database Fields’ list – you should see it appear in the ‘Report Columns’ box.
To remove a field, click and drag it from ‘Report Columns’ to ‘Database Fields’.
To reorder a field, click and drag it to its new location in the ‘Report Columns’ box. Genie will generate the report using the column order in this box.
The ‘Database Fields’ list will automatically show all fields related to the table you have selected, but you can also include fields from linked tables. To do this, first find the linked table in the list. Linked tables have a little arrow next to them and their record type is shown in brackets on the right. Click on the arrow to view the fields for this linked table. You can add any of these fields in the same way, by double clicking on them or dragging from the list on the left hand side over to the right.
For example, the following will insert the patient’s account holder’s balance:
Each field has a corresponding icon, indicating the type of information that field contains. The field types are:
Yes/No or radio box
Long text field (e.g. Family History)
The last four tabs in the Report Builder window are optional, but they allow you to format your report in the following ways:
- Sort the report (e.g. alphabetically by patient name).
- View totals/subtotals (e.g. billing totals for each site of service)
- Change the font or column widths
- Change the column headings
- Add a watermark
To sort a report:
- In the ‘Columns’ tab, add the fields you would like to be included in the report. Make sure the column you want the report sorted by is at the top of the list (if it isn’t, you can move it by clicking and dragging it in the ‘Report Columns’ box on the right).
- Go to the ‘Groups’ tab.
- Highlight the column on the left you would like to sort.
- Tick ‘Group Column Data’.
- Choose either ‘Ascending’ or ‘Descending’ order.
To include totals:
- Follow the above steps to sort the report.
- With the first field still highlighted in the ‘Groups’ tab, tick ‘Show Group Subtotals’ and/or ‘Show Grand Totals’.
- Under the ‘Formats’ tab, highlight the field you would like to be subtotalled.
- Tick the appropriate box on the right – usually ‘Count’ (to count the number of records) or ‘Sum’ (only applicable for number fields).
Formatting Example – total billed & receipted for each site of service
Find the items to report on:
- Go to File > QuickReports.
- Under 'Tables' on the left, highlight '[Sale]'.
- Click 'Query'.
- Under 'Available Fields' on the left, double click on '[Payment]'.
- In the expanded list, highlight 'PaymentDate'.
- Under 'Comparisons', highlight 'is greater than or equal to'.
- In the 'Value' field, enter the oldest date you wish to search for.
- Click 'Add Line'.
- Under 'Comparisons', highlight 'is less than or equal to'.
- In the 'Value' field, enter the most recent date you wish to search for.
- Click 'Query'.
Include the billing and receipting totals for each site of service:
- Go to the 'Columns' tab.
- Add the following fields to the report:
- ServiceSite (click the arrow next to 'InvoiceNum [Transaction]' – you should see the field in the expanded list)
- Go to the 'Groups' tab.
- Highlight 'ServiceSite'.
- Tick 'Group Column Data' and 'Show Group Subtotals'
- Go to the 'Formats' tab.
- Highlight 'TotalCharge' and tick 'Sum'.
- Highlight 'Payment' and tick 'Sum'.
You can then click 'Preview' or 'Print' to generate the report. Alternatively, click 'Export', 'EXCEL Spreadsheet', 'Export' to export the report to Microsoft Excel.
You can then generate the report in one of three ways, using the corresponding buttons at the bottom of the Report Builder window:
- Export: if you would like to manipulate the data further (e.g. additional sorting, calculations, etc), or would just like your data in a different format, you can export it as an Excel spreadsheet by clicking this button, choosing the ‘EXCEL Spreadsheet’ radio button and clicking ‘Export’ again. If you have Microsoft Excel installed, Genie will open the spreadsheet automatically (if you receive a prompt advising the file format and extension don’t match, click ‘Yes’ to open it anyway). The Export button also gives you the option to export in ASCII format (a tab delimited text file) or HTML.
- Preview: This will open the report directly on your computer, rather than physically printing it. It is a good idea to preview reports before printing them, particularly if they are large, to make sure you have constructed them correctly.
- Print: Physically prints the report. If you are using a Macintosh computer or have a PDF printer installed, you can also print to PDF and save the report to your computer.
Note: If your report has a lot of columns, it may not fit a portrait sheet of paper. In this case, you can either click ‘Page Setup’ and change the orientation to ‘Landscape’, or export the report in a different format.
If you plan to run a search or report often, you may like to save them to use again later.
To save a report template, go to File > QuickReports and construct the report, then click ‘Save’, enter a name for the template and click ‘OK’. The next time you would like to run this report, go again to File > QuickReports and immediately click ‘Load’. Select your template and click ‘Open’.
The report template saves all report information, including the selected table, but will not save the contents of the Query window.
To save a Query, select the appropriate table in the Report Builder window, click ‘Query’ and create the query. Then, click ‘Save’ and save the file somewhere on your computer (you may like to create a ‘Queries’ folder). The next time you would like to use this query, ensure the appropriate table is selected in the Report Builder window, click the ‘Query’ button, and click ‘Load’. Select the template you saved and click ‘Open’ – you should see all lines of the Query appear in the Query Editor box.
Note: Saved queries are only stored on your physical computer, so you will not be able to access them from another computer unless you manually copy the saved file across.
1. Accurate data entry
Your searches and reports will only be as accurate as your data entry. If health funds, current problems or procedure keywords are entered with multiple spellings or spacing, your reports may miss some information if you do not include all variations. To ensure your reports are an accurate reflection of your practice, you should endeavour to keep data entry consistent.
2. Pre-formatted reports
Genie has a number of pre-formatted searches and reports that may give you what you need in fewer steps. Before creating your own QuickReport, check whether you can find this information elsewhere in Genie. Some places to look include:
Accessed through Open > Patients, this page has a whole host of different ways to search for patients. Searches are available using:
- The buttons along the top of the window: Prescriptions, ICPC/ICD10 coding, consults and clinical attachments searches.
- The Patients menu: Find Birthdays (patients with an upcoming birthday), Today’s Appointments (patients with an appointment in a specific date range), Find Inactive Patients (patients not seen recently) or Flagged Patients.
File > Maintenance and Reports window
Reports are available in the ‘Practice Analysis’, ‘Administrator’ and ‘Other Reports’ tabs. Further reports can also be generated by Alt/Option + clicking or Ctrl/Cmd + clicking on some of these buttons.
Reports you can run from this window include:
- ‘Practice Analysis’ tab: Item Report, Referrals Report, Assistants Report, Patient Type, Account Holders Report.
- ‘Administrator’ tab: Payments and Sales reports.
- ‘Other Reports’ tab: New Referrals, Appointments Report.
The Query window can also be used in most of these windows by going to Records > Search. Clicking the printer icon in these windows will open the Report Builder window, allowing you to specify the fields to print.
If you think somebody else would appreciate reading this article feel free to email them a link.