Reports

SQL support

23R1

Network reporting now supports the USING syntax in the FROM clause in SQL queries.

Syntax example 

table1 INNER JOIN table2 [ ON table1_column = table2_column | USING (join_column [, ...] ) ]

This enhancement is enabled by default in your Network instance.

Supported SQL commands

The USING syntax can be used only when the join_column from the two tables are identical.

It is supported in the following commands:

  • INNER JOIN (or just JOIN)

  • LEFT (OUTER) JOIN

  • RIGHT (OUTER) JOIN

Example

The USING syntax can make a query more readable for users.

Previous query 

select hcp_revision.vid__v
  from hcp_revision
  join revision on hcp_revision.revision_id = revision.revision_id

New query using the USING syntax 

select hcp_revision.vid__v
  from hcp_revision
  join revision using(revision_id)

Reporting on OpenData

Availability

23R1

This feature will be available in all customer Sandbox and Production instances for the NA and EMEA OpenData regions. You can access OpenData entity tables for a country if you subscribe to all records for an OpenData country.

Flat hierarchy tables

22R3.1

The Reporting on OpenData feature was introduced in Network version 22R3.0 to allow advanced reporting users to report on country data within OpenData instances. In this release, the following enhancements are available:

  • Entity links in results

  • Access to OpenData flat hierarchy tables

Note: This feature is not enabled by default; it is being deployed to Network instances in stages. After the feature is deployed to the NA and EMEA regions in Network 23R1.0, it will be deployed to the LatAm and APAC regions.

For more information about the feature, see Reporting on OpenData in the Veeva Network Online Help.

Entity links in results

The report results now include a link to the entity's Profile page. If the entity has been downloaded to your Network instance, the Profile page opens when you click the link.

If the entity has not been downloaded to your Network instance, an error displays when you click the link.

Access to OpenData flat hierarchy tables

You can now access a flat hierarchy table for OpenData instances that you have access to through this feature. Flat hierarchy tables display all of the relationships and levels in hierarchies so you can see how HCPs and HCOs rollup to HCOs.

To access the table, expand the OpenData category in the SQL Query editor and expand the OpenData instance folder; for example, OpenData NA. The table has the following naming convention: <OpenData_instance>.flat_hierarchy; for example, opendata_na.flat_hierarchy.

Example flat hierarchy table

All OpenData regions (APAC, China, EMEA, LatAm, and NA) have their own flat_hierarchy table. The OpenData NA region has a second table called opendata_na.flat_ownership_hierarchy, which contains US records only.

Table updates

Flat hierarchy tables are updated daily. The tables are read-only; OpenData teams cannot edit or change the contents of the tables. This ensures that you will always have access to the full ownership hierarchy for each OpenData region.

More information

For information and examples for using the flat hierarchy tables, see Reporting on hierarchies in the Veeva Network Online Help.