The Custom Reports module is the central location for creating, running, and maintaining your custom report queries.

Accessing Custom Reports

To navigate to the Custom Reports screen, click the Admin link in the top navigation bar, and then click on the Custom Reports item in the left-hand menu. Users in the External Admin, External Chat Auditor, and Organization Owner roles can access the Custom Reports screen.

Module Overview

The Custom Reports module consists of several panels of tools to assist you when working with your custom queries and reports.

Left Hand Panel

The left-hand panel contains a set of tools that will come in handy when working with custom report queries. Let's examine these tabs one by one.

Saved

The Saved tab displays all of your saved queries, as well as any saved queries that have been shared by your teammates.

You can search your saved queries by typing into the search queries box, and the list will filter down the queries according to your search terms as you type, searching for matching queries by both the query name and query text.

The queries displayed show the query author, the query name and description, and a "Shared" indicator if the query has been shared with the organization.

Select a query in the list to open the query in the editor and run it.

To remove a query or toggle team-sharing, click the ellipses to the right of the query. These options are only available for queries that you have created.

📘

Shared queries

Shared queries are available to all users in the organization with access to the custom reports module. When you share a query, others are able to run the query or save a copy, but only the original author is able to update the shared query.

History

The History tab displays all of the queries that you have run in the Custom Reports module.

As on the Saved tab, you can search your query history by typing into the search history box, and MedChat will filter your query history as you type.

The queries listed show the name of the query, when it was run, a preview of the first couple lines of the query, and the number of rows returned. Clicking on the history item brings the query back into the query editor so that it can be re-run or saved.

📘

Query history to the rescue

Every query that you run, whether it's a saved query or an ad-hoc query that you ran but never saved, is saved to your query history. This can be helpful if you accidentally leave the custom reports screen before saving your work, or you need to re-run a query from the other day that you never saved.

Schema

The Schema tab contains a list of tables and their columns that are available to query in Custom Reports.

The tables are expandable to display the columns and their datatypes, and as with the other tabs, the Schema tab is searchable.

Check out the MedChat Data Model for a more descriptive listing of the MedChat tables and fields available to query.

Templates

The Templates tab contains MedChat-provided queries that answer common questions and demonstrate some of the different kinds of queries that can be run in custom reports.

As with the other tabs, templates are searchable and they can be selected and run in the Custom Reports editor.

New Query

At the bottom of the left-hand panel is the New Query button. Hit this to start a new query.

Top Panel

The top panel of the screen allows you to control some high-level settings for your query.

Click on the title and description fields and start typing to set a name and description for your query. These will be saved with your query if you choose to save it. These will also be displayed on the History tab even if you don't save the query.

In the middle of the top panel are a couple of the dropdowns that allow you to control some aspects of the query execution.

• The first dropdown has the values "Local Time" and "UTC", and it controls how datetime values are handled in evaluating your query. It also controls whether datetimes in your query's result set are returned in your local time or UTC.
• The second dropdown has different timeframes representing how far back in time the query will go when considering what data to query. It effectively sandboxes the query to a specific timeframe of data (30 minutes, 1 hour, 12 hours, 24 hours, 48 hours, 3 days, 7 days, or a Custom timeframe).

📘

Using Custom Time Range

Selecting 'Custom' allows you to select a start and end date duration. You can now go as far back as April of 2021 for your start date.

To the right of the top panel are the Save Report and Run buttons. Both do what they say - they save the report and they run the query, respectively. In some cases, the "Save Report" button will say "Save Copy" if you don't have permission to update the query you're viewing (if, for example, you're viewing a shared query that someone else created or a template query).

Query Editor

The query editor is where you build your KQL queries.

The editor offers context-aware assistance as you type your query, offering suggestions based on its knowledge of the MedChat schema.

The query editor also suggests KQL functions that are available to use in your query.

The editor will also identify and highlight errors in your query, underlining any problems with a red squiggly line.

You can read more about writing KQL queries in the Writing Queries section.

Results Panel

Once you've written your query and clicked Run, the results panel will display the query results.

The results are displayed in table format. Each column can be sorted by clicking on the column heading, and each column is resizable by click on and dragging the column heading separator.

The footer displays the number of rows returned and the run date.

Export Query Results

The query results can be downloaded as a comma-separated values (.csv) file or as an Excel spreadsheet (.xlsx) by clicking on the Download Report button.