OpenData advanced filters

Administrators and Data Managers can use SQL queries to define additional records to subscribe to for their OpenData country subscriptions.

Filter options

When your OpenData country subscription runs, it downloads and updates records defined in your working set file.

Use the options in the Health Care Professional and Health Care Organization sections of your OpenData country subscription to subscribe to additional records.

Filter Option Details
No additional records

Subscribe to records in the Working Set file only. (default)
Additional records via Basic Filters

Filter records by Specialty or Type.

For details, see Use basic filters.

Additional records via Advanced Filters

Use SQL queries to subscribe to additional records.

These queries are defined and tested in the SQL Query Editor using the OpenData reporting tables and then copied and used in your OpenData country subscriptions.

Queries run in your instance against OpenData reporting tables.

Additional records via Managed filters

Queries are created and managed by Veeva.

The queries display as read-only so you can easily understand what is currently in place. In some cases, you can convert a Managed filter to an Advanced Filter that you can self-manage.

Create an advanced filter

Prerequisite

The Reporting on OpenData feature must be enabled in your Network MDM instance. The queries are created using fields from the OpenData tables.

If the feature is enabled, the OpenData category displays in the SQL Query Editor.

For more information about this feature, see Reporting on OpenData.

Process

  • Write and test query in the SQL Query Editor.

  • Copy and paste the query to the country subscription and preview the count of additional records that will be subscribed to using the filter.

Add an advanced filter

  1. Open the SQL Query editor (Reports) and define a query using the OpenData reporting tables.

    Queries must filter on a primary country and include a SELECT statement, a WHERE clause, and reference the OpenData master instance tables.

    See the Query validation section for additional requirements and restrictions.

    Example query

    Use this HCP query to subscribe doctors in France that specialize in Oncology.

    SELECT
        vid__v
    FROM
        opendata_emea.hcp
    WHERE
        hcp_type__v = 'D'
        AND specialties = 'ON'
        AND primary_country__v = 'FR'

    The query will validate as you create it.

  2. Click Run Query to test the query and get a count of all OpenData records that meet your filter criteria.

  3. Open the OpenData country subscription (System Interfaces > OpenData Subscriptions).
  4. In the HCP or HCO section, click Additional records via Advanced Filters.
  5. Type a Query Description.

  6. Paste the validated query into the Query box.

    The query will validate again.

  7. Click Preview Additional Records. A count displays the number of additional records that will be added to your subscription. This is calculated by taking the records in OpenData that match the filter and subtracting any you already have in your instance.

  8. Save your query.

  9. In the Save Changes dialog, the option When this subscription runs, download all OpenData records that meet the defined criteria is selected by default. Click Yes, Save Changes.

Query validation

All queries must include a SELECT statement and be complete and valid.

In addition, the following rules are applied to ensure the queries are valid.

Validation Rule Validation Message Details
Network ID (vid__v) must be from the HCP or HCO OpenData table that you are querying Invalid Selection: The query must select only the vid__v field from the HCP or HCO table. Please remove any additional fields or aliases. Only Network IDs are added to subscriptions.
Use available reporting tables Unknown Table: Only OpenData tables as well as custom or lookup tables can be queried.

Any referenced tables must be one of the following:

  • OpenData master instance table ('opendata_<region>.hcp' or 'opendata_<region>.hco')

  • custom table in your instance (includes __ct suffix)

  • lookup table in your instance (includes __t suffix)
Include a WHERE clause Missing Filter Criteria: A WHERE clause is required. Please add specific filter criteria to your query. -
Cannot include a DISTINCT keyword Unsupported Clause: The DISTINCT keyword is not allowed. Records are unique by default within the OpenData subscription. -
Cannot include a LIMIT clause Unsupported Clause: The LIMIT clause is not allowed in this context. Please use the WHERE clause to manage the scope of your results. -
Cannot include a MIN, MAX, COUNT, and so on - Aggregate functions are not supported. The query supports only the vid__v field from the HCP or HCO table.

Managed filters

If you have existing filters that were requested through Veeva Support tickets, the Additional records via Managed Filters option displays. The query created and managed by Veeva displays as read-only so you can see what is currently in place to subscribe to additional records.

Switch a Managed filter to an Advanced filter

You can remove the Managed filter and create an Advanced filter so you can manage it for your instance.

Options

  • Write a new query

  • Copy the Managed filter and use it as starting point for your Advanced filter.

    Important considerations

    Because Advanced filters run against OpenData reporting tables in your Network MDM instance—rather than on the OpenData instance—existing Veeva-managed queries cannot simply be copied; they must be adjusted or rewritten.

    Example issues

    • Custom tables and custom fields - If the Managed filter uses Veeva custom tables or custom fields, the filter behavior must be changed because you cannot access those Veeva artifacts.

      Contact your OpenData representative before converting a Managed filter that uses Veeva tables or custom fields.

    • Candidate record fields - References to candidate record fields must be removed. They are not included in OpenData reporting tables.

    • Include the OpenData tables - The FROM statement must include the OpenData instance table for the entity; for example, opendata_emea.hcp.

The query must be fully tested in the SQL Query Editor before being added as an Advanced filter.

Create the advanced filter

Important: The switch from a Managed filter to an Advanced filter cannot be reversed.

To switch from using a Managed filter to an Advanced filter:

  1. If you are copying the Managed filter, click Copy to Clipboard.

  2. Open the SQL Query Editor (Reports) and either paste the copied Managed filter query and make the required changes or create a new query.

    Ensure that the query validates and then run it to view the results.

  3. Copy the query and open the OpenData country subscription.

  4. Select Additional Records via Advanced Filters.

  5. In the dialog, confirm that you want to switch to using an advanced filter. Click Yes, Proceed.

    The Additional records via Managed Filters option is removed.

  6. In the Additional records via Advanced Filters option, type a Query Description.

  7. Paste your tested query. When the query validates, click Preview Additional Records to test the query against the OpenData reporting tables. A count displays the records that will be added to your country subscription.

  8. Save your changes.