Reports

Reporting tree view

The SQL Query Editor now contains a tree view of the data domains and additional reporting tables available to you to query against in your Network instance. The tree view gives you the visibility to see your data model and reporting structure to help you create queries. Each data domain and reporting table category can be expanded so you can view its tables, fields, field metadata, and reference codes. Use the tree view to become familiar with the data model structure and to help you build your query by understanding the fields that can be joined. Some reporting users do not have access to the data model, so the tree view provides visibility to the data that is available to query.

This enhancement is available by default in your Network instance.

View a quick video about the Reporting tree view.

+

About the tree view

The left pane of the SQL Query Editor contains the tree view. Use this hierarchical structure to understand the tables, fields, field metadata, and reference codes that are available to query.

The view contains the following information:

  • Data domains - The Customer Master domain displays at the top by default. Custom domains are listed in alphabetical order after the Customer Master domain. Expand each data domain to view its tables, fields, and field metadata.

  • Tables - Tables are listed alphabetically in the respective domain or the appropriate reporting table category; for example, change request tables are located in the Data Stewardship Tables category.

  • Fields - Expand the table to see the fields. Only fields that are enabled in the data model display. Primary keys and foreign keys are listed first. The remaining fields are listed alphabetically.

    • Primary key - This field uniquely identifies the object.
    • Foreign key - The primary key field from a main object that is used to reference a main object on a sub-object or relationship object.

      The foreign key metadata includes the JOIN() description. This is helpful when you want to include multiple tables in the same SQL query.

      Example

      To understand how to join the Address table and HCP table in a query, use the metadata in the Address table's foreign key.

      from hcp INNER JOIN address ON hcp.vid__v = address.entity_vid__v

  • Field metadata - Expand each field to see the type and description. Reference type fields also contain the types and codes.

  • Reference Data - When you expand a reference type field, the reference type name and codes display.

    If there are more than ten reference codes for the reference type, click the link to display the full list in a pop-up. Each pop-up contains a search bar so you can quickly find a code.

    The following reporting category tables are only accessible through the SQL Query Editor.

  • Revision History - This category contains the revision history tables.
  • Data Stewardship Tables - This category contains change requests and suspect match tables.
  • Data Loading & Matching - This category contains the job tables from source subscriptions.
  • Lookup Tables - This category displays if you have uploaded one or more lookup tables in your Network instance.

Available actions

View ERD

Click the View ERD link beside each data domain to open a window that displays a relationship diagram. Click the line between two objects to see the fields that you can use to JOIN those object tables.

Copy to clipboard

Click the Copy to Clipboard button to copy the table or field name from the tree view to your clipboard. The button displays when you hover over any table or field name.

Add to query

Click the Add to Query button to insert the table, field, or reference code into your SQL query editor box. The text will be inserted wherever your cursor is located in the query box.

Search

Use the search bar at the top of the pane to find tables and fields. Reference codes do not display in these search results. If a reference type contains more than ten codes, a pop-up displays and includes a search bar.

As you type, the list automatically refines to display matches for any table and field name or label. The tables will automatically expand so you can quickly view all of the results.

Example - Create a SQL query

Create a query to find all of the doctors with active addresses in Austria.

To create this query, we need to find all of the addresses that meet the following criteria:

  • The address status is active.
  • The country is Austria.
  • The address belongs to an HCP with the HCP type Doctor.

In the report result, we want to display the following fields:

  • HCP Prefix
  • First Name
  • Last Name
  • HCP Type
  • All Specialties
  • Address Line 1
  • Address Line 2
  • City
  • Province
  • Postal code
  • Country (on the address object)

To create the query:

  1. Begin the query with a SELECT statement.

  2. Add the fields that you want to see in the report results.

    1. To find the HCP Prefix field, type prefix in the search box.

    2. Find the field in the search results and click the Add to Query button to insert the field into the SQL editor box.

      The prefix__v field is inserted after the SELECT statement.

      Tip: The text is inserted wherever your cursor is in the SQL editor box, so leave spaces and punctuation whenever applicable.

    3. Continue searching for the remaining fields and insert them into the query.

  3. Use a FROM statement to tell the query which tables to use. To find the addresses that belong to HCPs, we need to join the HCP and Address tables.

    1. To understand how to join the HCP and Address table, expand the foreign key on the Address table.

    2. Based on the foreign key description, we can see that the field entity_vid__v on the Address table (address.entity_vid__v) joins to the field vid__v on the HCP table (hcp.vid__v ).

    3. You can copy and paste the hcp.vid__v field into the SQL editor box.

    4. On the foreign key, click Add to Query to insert it into the query. Ensure that you preface the foreign key with address.; for example, address.entity_vid__v.

  4. Create a WHERE statement to insert the criteria for the query.

    1. The HCP type must be Doctor - Search for the HCP Type field and add it to the query. To find the reference code for Doctor, expand the Reference Codes for the hcp_type__v field and click the link to display the codes. Click Doctor (D) to add the code to the query.

    2. The Address status must be active - Search for the address status field in the tree view pane and click Add to Query. Expand the address_status__v field to find the reference code for Active. Add the reference code to the query.

    3. The country must be Austria - Search for the country field in the tree view pane and click Add to Query. Under the country__v field, expand Reference Codes and click the link to find the code for Austria. Add the reference code to the query.

  5. Ensure that the query is valid.
  6. Click Run Query to see the report results.