Reports

Merged HCO and HCP reports

The following Saved Reports will be discontinued in a future release:

• Merged HCO Report

• Merged HCP Report

These are legacy reports that do not use the current Network reporting warehouse.

For best results, begin using the provided SQL queries for merged HCOs and HCPs. You can save the queries as saved reports.

Merged HCOs query

SELECT
		hco_surviving.vid__v AS "Veeva ID (Surviving)",
		hco_non_surviving.vid__v AS "Veeva ID (Non-Surviving)",
		hco_revision.created_at AS "Timestamp"
		
    FROM
        hco_revision
		INNER JOIN hco AS hco_non_surviving ON hco_revision.vid__v = hco_non_surviving.vid__v
		INNER JOIN hco AS hco_surviving ON hco_non_surviving.record_merged_vid__v = hco_surviving.vid__v
    WHERE
	    hco_revision.record_state__v = 'MERGED_INTO'
        and hco_revision.created_at >= 'insert_date_here' -- example '2022-01-30'

Merged HCPs query

SELECT
		hcp_surviving.vid__v AS "Veeva ID (Surviving)",
		hcp_non_surviving.vid__v AS "Veeva ID (Non-Surviving)",
        hcp_revision.created_at AS "Timestamp"
		
    FROM
        hcp_revision
		INNER JOIN hcp AS hcp_non_surviving ON hcp_revision.vid__v = hcp_non_surviving.vid__v
		INNER JOIN hcp AS hcp_surviving ON hcp_non_surviving.record_merged_vid__v = hcp_surviving.vid__v
    WHERE
	    hcp_revision.record_state__v = 'MERGED_INTO'
        and hcp_revision.created_at >= 'insert_date_here' -- example '2022-01-30'

Save the reports

Save these queries as saved reports. You can schedule saved reports to run or you can run them manually.

To save a report:

1. On the Network menu bar, click Reports > SQL Query Editor.

2. Paste the query in the query box.

   Customize the query by defining the date parameter and include any additional columns.

   

3. Clear the Include only VALID and UNDER_REVIEW records in results option.

   This ensures that records with the record_state__v field set to MERGED_INTO display in the results.

4. Click Save Query. The button is available only if the query is valid.

5. In the Save Report As dialog, type a name and description.

6. On the report configuration page, you can choose to schedule the report, share it with other users, define download options, and make changes to the SQL query.

   For more details , see the Saved report options topic in the Veeva Network Online Help.

Reporting on hierarchies

A new table called flat_hierarchy is added to Network reports to help you report on relationship hierarchies. The table updates hierarchies once a day and displays all of the relationships and levels so you can see how HCPs and HCOs rollup to HCOs.

Existing hierarchy reporting

Using the existing parenthco table, you can view each entity and its direct parent HCO. You can create a SQL query and join the table with other tables, but this can be complex.

Flattened hierarchy reporting

Using the new flat_hierarchy table, you can view all the relationships and understand how HCPs and HCOs roll up to direct and indirect HCOs.

Example

For the HCP, Adam, a row is populated for every HCO that he's connected to so you can see all the relationships and the levels to understand how he rolls up to each HCO.

This feature is available by default. Administrators must enable the flat_hierarchy table by setting a schedule to populate the data for their Network instance.

Note: You do not need to have the Hierarchy Explorer widget enabled to use this feature.

Benefits

• Explore targets - Find the HCPs in a specific health system or understand the health systems you should target for specific therapeutic areas. For example, you can produce a report for health systems that have a large number of HCPs that are gastroenterologists.

• Maintain hierarchies and tracking changes - Compare old versions of your custom hierarchy to the new version to find breakages. You can also use it to understand how data source updates impact the hierarchy. For example, if a data source inactivates an HCO, you can see how that change impacts a hierarchy.

• Roll up sales and interactions to the health system - Allow analytics teams to easily roll up the interactions and sales data to the health system or hospital.

• Skip levels of the hierarchy when reporting - Data Managers and analytics teams can skip levels of the hierarchy using SQL.

• Export - Export the report to your data warehouse so you can use it for business purposes like incentive compensation. This can be done using transformation queries.

Enable the flat hierarchy table

The flat_hierarchy reporting table is not enabled until Administrators set the schedule to update the data for their Network instance. Until the schedule is set, there is no data for the reporting database to return.

1. In the Admin console, click Settings > General Settings.

2. In the Flattened Hierarchy Reporting Daily Update Schedule section, expand the Update Table list and choose the time.

3. Save your changes.

Supported hierarchy paths

The flattened hierarchies support HCPs, HCOs, and ParentHCO relationships.

The report displays only active and valid relationship paths. Paths that do not meet this criteria are removed from the hierarchy.

• Inactive paths - Paths are considered inactive for the following reasons:

  • HCP or HCP - Record status is not Active, they are candidate records, or HCPs have been opted out.

  • Relationships - The relationship status is not Active.

• Invalid paths - Paths are marked as invalid for the following reasons:

  • HCP/HCP - Record state is not Valid or Under Review (Invalid/Merged_Into/Deleted) or the record is unsubscribed (record state is Deleted).

  • Relationships - The record state is not Valid or Under_Review or the parent of the relationship is not in your Network instance.

Shortest path

The shortest path between two entities displays in the report. Duplicate short paths do not display to prevent duplicate entities in roll up counts.

Example 1

Between Health System and Clinic A, there are two paths with a distance value of 2; however, the table displays only one row.

Example 2

Between Health System and Clinic A there are two paths:

• Health System → Hospital A → Clinic A

• Health System → Clinic A)

The flat hierarchy table displays only the shortest path (Health System → Clinic A) to prevent duplicate counts when rolling up the data.

View the hierarchy table

The flat_hierarchy table is in the Customer Master section in the SQL Query Editor (Reports).

The table contains the following fields:

Field Name	Field Label	Field Type	Description
entity_vid__v	Entity VID	VID	Veeva ID of the child record.
entity_type__v	Entity Type	Reference	Type of the child record.
ancestor__vid__v	Ancestor Veeva ID	VID	Veeva ID of the ancestor record.
ancestor_type__v	Ancestor Entity Type	Reference	Type of the ancestor record. This value is always HCO.
path_distance	Path Distance	Number	Number of hops from the child to the ancestor. Direct relationships have 1 as the length.
record_state__v	Record State	Reference	State of the path. The value is always Valid. Paths that are not valid do not display in the reporting table.
path_status	Path Status	Reference	Status of the path. The value is always Active. Paths that are inactive do not display in the reporting table.
modified_date	Modified Date	Date Time	The last modified date for any entity or relationship in the path.
path_info	Path Info	Text	Path from the child record to the ancestor using names. The names are separated by the pipe (|) character.
path_info_vid	Path Info with Veeva IDs	Text	Path from the child record to the ancestor HCO using Veeva IDs. The VIDs are separated by the pipe (|) character.

Flattened hierarchy report examples

Review the following examples to see how you can use the flat_hierarchy table.

Example 1 - flat_hierarchy table structure

Use this basic query to view the structure of the table.

Query

select * from flat_hierarchy

Results

Notes about path columns

• Path Distance - Indicates the level that the entity is from the parent HCO. For example, a value of 1 means that the entity is directly connected to the parent HCO. A value of 2 means that there is another HCO between the entity and the parent HCO.

• Path Info with Veeva IDs - Displays the Veeva IDs of the entities in the path separated by the pipe (|) character. The entity's ID displays first and then the parent HCO ID displays.

• Path Info - Displays the names of the entities in the path separated by the pipe (|) character.

Example 2 - Query an entire health system

Report on all of the HCPs and HCOs in a specific health system.

Note: Previously, this data would require a SQL query that joined an average of five different tables.

Query

In this example, we are reporting on all of the HCPs and HCOs in the Ascension Health health system.

SELECT
        *
    FROM
        flat_hierarchy
    WHERE
        ancestor_vid__v = 242979566124008448

Results

Example 3 - Hierarchy levels and counts for a health system

Use this query to report on the levels and counts of HCOs in a health system.

Query

This query joins the flat_hierarchy table to the hco table.

SELECT
        path_distance AS "Level",
        hco_type__v,
        COUNT (*)
    FROM
        flat_hierarchy LEFT JOIN hco
            ON entity_vid__v = hco.vid__v
    WHERE
        ancestor_vid__v = 242979566124008448
    GROUP BY
        hco_type__v,
        "Level"
    ORDER BY
        "Level" ASC

Results

Example 4 - Health system for HCPs

Use this query to find the health system where HCPs are affiliated.

Query

This query joins the flat_hierarchy table to the hco table.

Note: This query is difficult to create without the flat_hierarchy table; it requires joining on several tables.

SELECT
        entity_vid__v,
        LISTAGG (
            DISTINCT corporate_name__v,
            '| '
        ) AS "Health Systems"
    FROM
        flat_hierarchy JOIN hco
            ON ancestor_vid__v = hco.vid__v
    WHERE
        hco_type__v = '4:37'
        AND entity_type__v = 'HCP'
    GROUP BY
        entity_vid__v

Results

Example 5 - Rollup counts for HCPs and HCOs

Use this query to display roll up counts for health systems for a specific place and therapeutic area.

Query

In this example, we want to return the rollup counts for health systems in Texas that have HCPs that are MDs and that specialize in oncology.

This query joins the flat_hierarchy table to the hco and hcp tables. The query is more complex but it is much easier to do now than it previously was using the parenthco table.

SELECT
        hco_anc.corporate_name__v,
        hco_anc.hco_type__v,
        locality__v || ' ' || administrative_area__v AS "location",
        SUM (
            CASE
                WHEN hco_ent.major_class_of_trade__v = '32'
                THEN 1
                ELSE 0
            END
        ) AS "Hospital Count",
        SUM (
            CASE
                WHEN hcp.primary_specialty_group__v = 'G-ON'
                AND medical_degrees IN (
                    'MD',
                    'DO'
                )
                THEN 1
                ELSE 0
            END
        ) AS "Oncologist Count",
        MAX( path_distance ) AS "Levels"
    FROM
        flat_hierarchy JOIN hco hco_anc
            ON hco_anc.vid__v = ancestor_vid__v JOIN address
            ON ancestor_vid__v = address.entity_vid__v
        AND address_ordinal__v = 1 LEFT JOIN hcp
            ON hcp.vid__v = flat_hierarchy.entity_vid__v LEFT JOIN hco hco_ent
            ON hco_ent.vid__v = flat_hierarchy.entity_vid__v
    WHERE
        hco_anc.hco_type__v = '4:37'
        AND administrative_area__v = 'US-TX'
    GROUP BY
        hco_anc.corporate_name__v,
        "location",
        hco_anc.hco_type__v
    HAVING
        "Oncologist Count" > 5
        AND "Hospital Count" > 1
    ORDER BY
        hco_anc.corporate_name__v

Results

Example 6 - Skip hierarchy levels

Analytics teams may want to roll up sales and interactions to the closest hospital an HCP is affiliated to and then to the Health System the hospital is affiliated with.

We can leverage the flat hierarchy to skip levels from the HCP to the hospital to the health system.

Query

This query involves a number of joins because we're pulling the names of the HCP, hospital, and health system. However, the number of joins on the relationships is reduced significantly.

SELECT
        flat_hospital.entity_vid__v as "HCP VID",
        formatted_name__v as "HCP Name",
        flat_hospital.ancestor_vid__v as "Hospital VID",
        hospital.corporate_name__v as "Hospital",
        flat_hospital.path_distance as "HCP to Hospital Distance",
        flat_healthsys.ancestor_vid__v as "Health System VID",
        healthsystem.corporate_name__v as "Health System",
        flat_healthsys.path_distance as "Hospital to Health System Distance"
    FROM
        flat_hierarchy flat_hospital JOIN hco hospital
            ON hospital.vid__v = flat_hospital.ancestor_vid__v JOIN hcp
            ON flat_hospital.entity_vid__v = hcp.vid__v LEFT JOIN flat_hierarchy flat_healthsys
            ON flat_healthsys.entity_vid__v = flat_hospital.ancestor_vid__v LEFT JOIN hco healthsystem
            ON healthsystem.vid__v = flat_healthsys.ancestor_vid__v
    WHERE
        flat_hospital.entity_type__v = 'HCP'
        AND hospital.hco_type__v IN (
            '4:6',
            '4:35',
            '32_23',
            '32_22',
            '1_7',
            '1_3',
            '32_11',
            '4_56',
            '1_21',
            '1_10',
            '1_1'
        )
        and healthsystem.hco_type__v = '4:37'

Results

Customizations

Flattened hierarchies can be customized to flatten certain relationships only. For example, instead of flattening all active parentHCO relationships, you can flatten the “Ownership Hierarchy” (relationships where the relationship type is either Affiliation or Ownership).

To enable a customization on the flattened hierarchy, contact Veeva Support.

In the support ticket, provide the filter you want applied on the parentHCO. For example, for the "Ownership Hierarchy", submit the following filter:

relationship_type__v in ('7356','2')