Reporting

Reporting on OpenData

Advanced reporting users can now report on country data within OpenData instances.

New reporting tables for OpenData are available in the SQL Query Editor. Users with access to Network Reports and the data can view results directly from the OpenData instance.

Enable the feature

This feature is not enabled by default; it will be deployed to Network instances in stages.

If you are interested in having the feature enabled soon, contact your Network representative.

Supported country subscriptions

OpenData reporting tables are available for countries where you subscribe to all records. They are not available for pay-per-record country subscriptions.

Using OpenData tables

OpenData tables are unique because they report on data that may not be in your Network instance. They can, however, be used in the same way that other reporting tables can be used.

Example use cases:

• Join OpenData tables with Network tables or custom tables.

• Create inbound or outbound data transformation queries.

• Create custom tables with the query results.

• Download report results.

• Save queries as Saved Reports.

Access the reporting tables

The OpenData tables are available in the SQL Query Editor (Reports). The OpenData category displays below the data domains in the tree view.

• Expand the OpenData category to view folders for each OpenData instance that you have access to.

  If your Network instance is connected to an OpenData instance because you subscribe to all records for one or more countries, that instance displays.

  Example

  If you subscribe to all OpenData records for the United States (US), the OpenData NA instance displays in the list. If your subscription does not include all records, or you do not subscribe to the US or Canada at all, the OpenData NA instance does not display.

• Expand each instance to see the OpenData reporting tables.

  Available tables All Veeva object and sub-object tables: HCP HCO Address License ParentHCO

  Tables that are not included Any table that is not an entity table is not included: Custom key Reference Data Revision History Data Loading Data Stewardship Lookup tables flat_hierarchy tables

Table data

Country considerations

The tables contain data for all countries in that instance where you subscribe to all records. For example, if you subscribe to all records for Canada and the US, the tables in the OpenData NA instance returns results for both countries.

Tip: If you want to return results for a specific country, remember to specify the country in your query.

If you subscribe to all records for France but your subscription for Italy is pay-per-record, the OpenData EMEA reporting tables will only return results for France. Data for Italy will not be included in the tables.

Supported records

The following records and fields are available to report on in the OpenData instance:

• Records with Valid and Merged_Into record states

  Note: Record state applies to the HCOs and HCPs only. Sub-objects of any record state will be returned. For example, any invalid addresses on a valid HCP will be included in the tables.

• All record statuses

• All Veeva standard fields (__v)

  Not all Veeva standard fields are used in every country.

  Note: The fields must be enabled in your Network instance to be available for OpenData reporting.

• All Veeva objects and sub-objects

• OpenData subscription fields that you subscribe to (HIN, CIP, Geo Subdivision, NCPDP, and so on)

• Unsubscribed records

Excluded records

The following records and fields are not included in OpenData reporting results:

• Records with Invalid and Deleted record states

• Opted out records

• Candidate records

• Custom keys

• Custom fields

Instance-level access

This feature is available only if your Network instance is configured for the following:

• OpenData country subscriptions

  • You subscribe to all records for a country; it is not available for pay-per-record subscriptions.

    Note: (US only) This applies to the OpenData Email subscriptions also. Emails are only available in reporting if all emails are included in the subscription.

  • The country subscription is enabled and configured in your Network instance (System Interfaces > Veeva OpenData Subscriptions)

• Network features - The following features must be enabled in Settings > General Settings:

  • Search and Query OpenData (previously called Search OpenData) - This setting enables you to view records in the OpenData instance that have not been downloaded to your Network instance.

  • Reporting View - This setting enables the reporting feature in your Network instance.

User-level access

Administrators must set the following permissions on individual user pages and on data visibility profiles to give users access to OpenData data in Network reports. Field restrictions are also considered for data returned in the report results.

User page

Required Permissions	Required Permission Value	Description	Impacted Users
Reports	Display Tab	Allow users to see the Reports tab in the Network menu bar.	All users
SQL Query Editor	Allow	Allow users access to the SQL Query Editor feature in the Reports menu.	Standard users and Data Managers
Report Results	Restricted - Limited by user's data permissions	Query results observe the user's data permissions (data visibility profiles, inbox task groups)	Administrators, Data Managers, System and Data Admins
	Unrestricted	Query results have no restrictions applied.

Data visibility profiles

Required Permissions	Required Permission Value	Description	Impacted Users
Can search and query OpenData instance	True	Users with this DVP can access OpenData records for this country.	All users
Health Care Professional Visibility	All, or Include (for specific HCPs)	All - User can view all HCP records for the country in the query results Include - User can see only the HCP records for the country that they have visibility to.	All users
Health Care Organization Visibility	All, or Include (for specific HCOs)	All - User can view all HCO records for the country in the query results Include - User can see only the HCO records for the country that they have visibility to.	All users
Can download/sync records from OpenData Instance	True or False	Does not restrict users from reporting on OpenData records. True - Users can download records from OpenData instance. False - Users cannot download records. The download icon is not active.	All users

Field restrictions

If there are restrictions on OpenData fields, it will impact the data that users see in query results.

• No restrictions - Users have access to all fields in the query results.

• Restricted field - Queries run on OpenData tables for that field will not return results.

  • If the field is used in the where clause, no results are returned at all.

  • If the field is simply listed in the query, the query will run, but no data displays for that field in the column.

Sample queries

OpenData tables can be used to query data in the OpenData instance, or you can JOIN the tables with other Network reporting tables.

Sample query 1 - Count of records to be downloaded (filter subscription)

If you add Specialty or Type filters in the OpenData country subscription, you can report on how many new records will be downloaded to your Network instance.

Query

This query uses two values for Specialty fields and two values for the HCP Type field.

SELECT
        us_opendata_hcps.vid__v
    FROM
        (
            SELECT
        vid__v, EXPLODE specialties AS specialty
    FROM
        opendata_na.hcp
    WHERE
        specialty IS NOT NULL
        AND specialty IN (
            'CHP',
            'CPP'
        )
        AND hcp_type__v IN (
            'D',
            'P'
        )
        AND primary_country__v = 'US'                    
        ) AS us_opendata_hcps 
        LEFT OUTER JOIN 
        (
SELECT
        vid__v, EXPLODE specialties AS specialty
    FROM
        hcp
    WHERE
        specialty IS NOT NULL
        AND specialty IN (
            'CHP',
            'CPP'
        )
        AND hcp_type__v IN (
            'D',
            'P'
        )
        AND primary_country__v = 'US'                    
        AND record_owner_type__v = 'VOD'
        ) AS downloaded_hcps
            ON us_opendata_hcps.vid__v = downloaded_hcps.vid__v
    WHERE
        downloaded_hcps.vid__v IS NULL

Sample results

Sample query 2 - Count of prescribers in a country

Find the count of all HCPs that are prescribers (HCP type = Prescriber) for a country in an OpenData instance.

Query

This query returns a count for prescribers in the US for the OpenDataNA instance

SELECT
        COUNT (vid__v)
    FROM
        opendata_na.hcp
    WHERE
        hcp_type__v = 'P'
        AND primary_country__v = 'US'

Sample results

Sample query 3 - List of unsubscribed HCPs

See a list of HCPs that have been unsubscribed from OpenData for your Network instance for a specific country.

Note: The Include only Valid and Under_Review checkbox must be cleared for this report; otherwise, no records will be returned in the results.

Query

This query specifies HCPs in the US.

SELECT
        vid__v
    FROM
        opendata_na.hcp
    WHERE
        primary_country__v = 'US'
        AND record_state__v = 'VALID'
        AND vid__v IN (
            SELECT
                    vid__v
                FROM
                    hcp
                WHERE
                    primary_country__v = 'US'
                    AND record_owner_type__v = 'VOD'
                    AND record_state__v = 'DELETED'
        )

Results

Sample query 4 - List of HCOs related to already downloaded HCPs or HCOs

This query finds not-yet-downloaded HCOs (one level up) that are related to already downloaded active HCPs or active HCOs. The relationship to the HCO must also be active. This helps you to find missing parents in your hierarchy.

Query

This query returns a list HCO Veeva IDs (VIDs) for the US.

SELECT
        distinct us_opendata_hcos.parent_hco_vid__v
    FROM
        (
            SELECT
                    parenthco.parent_hco_vid__v
                FROM
                    hcp INNER JOIN opendata_na.parenthco
                        ON hcp.vid__v = parenthco.entity_vid__v
        ) us_opendata_hcos LEFT OUTER JOIN (
            SELECT
                    hco.vid__v
                FROM
                    hco
                WHERE
                    hco_status__v = 'A'
                    AND primary_country__v = 'US'
                    AND record_owner_type__v = 'VOD'
        ) downloaded_us_hcos
            ON downloaded_us_hcos.vid__v = us_opendata_hcos.parent_hco_vid__v
    WHERE
        downloaded_us_hcos.vid__v IS NULL

Results

Data transformation queries

OpenData reporting tables can be used in queries for inbound and outbound data transformations.

Sandbox considerations

The OpenData data results in your Sandbox inbox might be different from the data in your Production instance for the following reasons:

• Sandbox instances are not updated daily.

• Veeva OpenData subscriptions are often set for all records for the country in Sandboxes. If you do not subscribe to all records for a country, you might have access to the feature for that country in your Sandbox, but not in your Production instance.

Sample queries

The following queries have been added as Sample Queries in the SQL Query Editor. These queries were previously available as Saved Reports but they have been discontinued. Use these sample queries to report on merged HCOs or HCPs.

These queries are available by default if Reporting is enabled in your Network instance.

Merged HCO Report

Entity: HCO

Country: All

Description: Report of HCOs merged starting from a given date.

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' -- i.e. '2022-01-01'

Merged HCP Report

Entity: HCP

Country: All

Description: Report of HCPs merged starting from a given date.

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' -- i.e. '2022-01-01'

Use a sample query

To report on merged HCOs and HCPs:

1. Select Reports > SQL Query Editor and click Sample Queries.

2. In the Sample Queries dialog, use the search bar to find the query or find the query in the HCO or HCP entity section.

3. Select the query and click Insert Selected Query.

4. In the query editor box, replace the INSERT_DATE_HERE placeholder with a date. The report will display all of the merges of that object from the date that you specify.

5. Click Run Query to view the results.