Automated monthly reporting on health systems

AD
DM

Health systems can change over time. It's important to understand when HCOs and HCPs are dropped and added because it can affect your sales reporting and incentive compensation processes.

To track these changes, you can choose which hierarchy you want to track. In our examples, we will use the Flattened Ownership Hierarchy to report on the health system changes each month. The ownership hierarchy looks at relationships that are Ownership, Affiliation, or Kaiser Affiliation for the US. If you have other hierarchies you are managing, such as a Neurology hierarchy or an Oncology hierarchy, a similar approach can be followed for those hierarchies.

This monthly reporting is an automated process that requires a few prerequisite tasks to prepare for the reports.

Prerequisites

To prepare for monthly reporting, complete the following tasks:

Create a snapshot- Use a transformation query to create a custom table of the ownership hierarchy data at the beginning of each month.
Create a report - This report will be run at the end of each month. It compares the snapshot to the live hierarchy data.

This is a one-time setup. It is important to plan and schedule the custom table creation and report to ensure that the data is reported as expected.

Example

Create a snapshot of the hierarchy data at the beginning of the month. At the end of the month, run a report that compares the data from the snapshot to the live hierarchy data so you can see the number of HCOs and HCPs that were dropped and added in each health system during that month.

Create a snapshot of the hierarchy data

The snapshot freezes the data that is available at the beginning of the month so you can compare it with the live hierarchy data at the end of the month.

To take a snapshot of the data, create a transformation query using the ownership hierarchy and link the query to a target subscription. The transformation query will create a custom table when the target subscription runs.

Transformation query

To create a transformation query, click System Interfaces > Transformation Queries and click Add Query.

Details section

In this section, define a meaningful name and description and choose the Outbound query type.

In this example, we'll refer to the snapshot taken during the first of the month as the last snapshot. Each time a snapshot is taken at the first of the month, it replaces the last snapshot.

Example

Query Output section

In the Query Output section, define the following settings:

Output type- Choose Custom Table.
Table Name and Description
Save to Folder - Save the custom table to a specific folder or to the root of the Shared Custom Tables folder in SQL Query Editor (default).
Table Name Options - Choose Static table name. This table will be overwritten each month.

Example

Transformation Query section

Paste the following query into the query box.

select * from flat_ownership_hierarchy

Save the query.

Create a target subscription

Link the transformation query to the target subscription. When the target subscription runs, the transformation query creates the custom table; no data will be exported.

To create a target subscription, click System Interfaces > Target subscriptions and click Add Subscription.

Details section

Define the following settings:

Name and Description.
Type - Choose Data.
System - Choose any source system. This is a standard requirement for target subscription configurations; no data will be exported.

Tip: Add a system (System Interfaces > System) that you can use for these subscriptions; for example, create a system called CreateCustomTables.
Status - The subscription is Enabled by default.

Example

Note that the Data Flow View and Code do not display until the subscription is saved.

General Export Options section

Accept the default settings in this section. They do not apply to the subscription because no data will be exported.

File & Field Selection section

Choose Export None for the Export Options. All objects will be set to Do Not Export.

Example

Transformation Queries section

In this section, click Add Query to add the transformation query that you created for the snapshot of last month's hierarchy data.

Example

Job Schedule & Triggers section

In this section, schedule the target subscription to run at the beginning of each month; for example, at 12:00 am on the first day of each month.

Important: It must be scheduled to run after the monthly report is run.

Example

No job triggers are required.

Save the subscription.

Create a monthly report

Create a report that runs at the end of each month. This will compare the live hierarchy data to the snapshot of the data that was taken at the beginning of the month.

To create the report:

In the Network menu bar, click Report > SQL Query Editor.

Paste the following query into the query box.

SELECT
        (
            CASE
                WHEN flat_hierarchy_current.ancestor_vid__v IS NOT NULL
                THEN CAST(flat_hierarchy_current.ancestor_vid__v AS char(20))
                ELSE  CAST(flat_hierarchy_old.ancestor_vid__v AS char(20))
            END
        ) AS "HCO_VID",
        (
            CASE
                WHEN flat_hierarchy_current.ancestor_vid__v  IS NOT NULL
                THEN flat_hierarchy_current.ancestor_name__v
                ELSE flat_hierarchy_old.ancestor_name__v
            END
        ) AS "hco_name",
        SUM (
            CASE
                WHEN flat_hierarchy_current.entity_vid__v IS NULL
                AND flat_hierarchy_old.entity_type__v = 'HCP'
                THEN 1
                ELSE 0
            END
        ) AS "hcp_drops",
        SUM (
            CASE
                WHEN flat_hierarchy_old.entity_vid__v IS NULL
                AND flat_hierarchy_current.entity_type__v = 'HCP'
                THEN 1
                ELSE 0
            END
        ) AS "hcp_adds",
        SUM (
            CASE
                WHEN flat_hierarchy_current.entity_vid__v IS NULL
                AND flat_hierarchy_old.entity_type__v = 'HCO'
                THEN 1
                ELSE 0
            END
        ) AS "hco_drops",
        SUM (
            CASE
                WHEN flat_hierarchy_old.entity_vid__v IS NULL
                AND flat_hierarchy_current.entity_type__v = 'HCO'
                THEN 1
                ELSE 0
            END
        ) AS "hco_adds"
    FROM
        flat_ownership_hierarchy_last_snapshot__ct flat_hierarchy_old 
		FULL OUTER JOIN flat_ownership_hierarchy flat_hierarchy_current
            ON flat_hierarchy_old.entity_vid__v = flat_hierarchy_current.entity_vid__v
        	AND flat_hierarchy_old.ancestor_vid__v = flat_hierarchy_current.ancestor_vid__v 
    WHERE
        flat_hierarchy_current.hco_type__v_ancestor = '4:37'
        OR flat_hierarchy_old.hco_type__v_ancestor = '4:37'
    GROUP BY
        hco_name,
        HCO_VID
    ORDER BY
        hcp_adds DESC,
        hcp_drops DESC

Click Save Query.
In the Save Report As dialog, type a meaningful name and description for the report.
In the saved report configuration, schedule the report. It must be scheduled to run before the comparison (last update) snapshot is created at the beginning of the month.

For example, if the comparison snapshot is created on the first day of each month, schedule the report to run on the last day of the month; ideally, January 31, February 28, March 31, and so on.

Example
Save the report.

The report will be available each month to monitor the drops and adds of HCPs and HCOs for health systems in your Network instance.

Example report results

Detailed breakdown reports

Use these reports to see a detailed breakdown of the drops and adds from the previous report's results.

Monthly drops to the health system

Run this report to see the number of HCOs and HCPs dropped from an Integrated Delivery Network (IDN) or health system over the past month.

Specify the Veeva ID of the health system in the query.

SELECT
        flat_hierarchy_old.entity_vid__v,
        flat_hierarchy_old.entity_name__v,
        flat_hierarchy_old.entity_type__v,
        flat_hierarchy_old.ancestor_vid__v,
        flat_hierarchy_old.ancestor_name__v,
        'DROP' AS "Action"
    FROM
        flat_ownership_hierarchy_last_snapshot__ct flat_hierarchy_old LEFT JOIN flat_ownership_hierarchy flat_hierarchy_current
            ON flat_hierarchy_old.entity_vid__v = flat_hierarchy_current.entity_vid__v
        AND flat_hierarchy_old.ancestor_vid__v = flat_hierarchy_current.ancestor_vid__v
    WHERE
        /* VID of the Health System or IDN */
        flat_hierarchy_old.ancestor_vid__v = 242979616908641280 /* If the current hierarchy does not have the entity/ancestor pair that exists in the snapshot, then the entity was dropped from the hierarchy */
        AND flat_hierarchy_current.entity_vid__v IS NULL /* Choose what Entity Type you want to look at*/
        AND flat_hierarchy_old.entity_type__v = 'HCO'

Example report results

Adds to the health system

Run this report to see the number of HCOs and HCPs added to an Integrated Delivery Network (IDN) or health system over the past month.

Specify the Veeva ID of the health system in the query.

SELECT
        flat_hierarchy_current.entity_vid__v,
		flat_hierarchy_current.entity_name__v,
		 flat_hierarchy_current.entity_type__v,
		flat_hierarchy_current.ancestor_vid__v,
		flat_hierarchy_current.ancestor_name__v,
		'ADD' as "Action"
    FROM
       flat_ownership_hierarchy_last_snapshot__ct flat_hierarchy_old 
		FULL OUTER JOIN flat_ownership_hierarchy flat_hierarchy_current
            ON flat_hierarchy_old.entity_vid__v = flat_hierarchy_current.entity_vid__v
        	AND flat_hierarchy_old.ancestor_vid__v = flat_hierarchy_current.ancestor_vid__v 
    WHERE
		/* VID of the Health System or IDN */
        flat_hierarchy_current.ancestor_vid__v = 242979616908641280 
		/* If the old hierarchy does not have the entity/ancestor pair that exists in the current hierarchy, then the entity was added to the hierarchy */
		AND flat_hierarchy_old.entity_vid__v IS NULL
		/* Choose what Entity Type you want to look at*/
		and flat_hierarchy_current.entity_type__v = 'HCP'

Example report results

These results display the HCPs and HCOs added to Trinity Health System in a month.

Filtering on health system targets

In addition to reporting on health systems and IDNs, you can also flag HCO and HCPs that are considered targets. Adjust the query so the results also consider these accounts.

Automated monthly reporting on health systems

Prerequisites

Create a snapshot of the hierarchy data

Transformation query

Details section

Query Output section

Transformation Query section

Create a target subscription

Details section

General Export Options section

File & Field Selection section

Transformation Queries section

Job Schedule & Triggers section

Create a monthly report

Detailed breakdown reports

Monthly drops to the health system

Adds to the health system

Filtering on health system targets

Was this topic helpful?

Tell us more

Additional feedback?

Thanks, we appreciate your feedback!

Thanks, we appreciate your feedback!