Report on Affiliation widget data

AD
DM

Advanced reporting users can report on the Affiliation widget data in their Network instance. Use the reports to summarize the data in your views. The reports can be downloaded and shared with other users or sent to your downstream systems.

The reporting tables are available if the Affiliation widget is enabled in your Network instance.

Widget reporting tables

Tables for Affiliation widget data are available in the reporting tree view in the SQL Query Editor.

Expand the Network Widgets Data section to view and use these tables:

  • aw_view - List of all the views created for the Affiliation widget in your Network instance.

  • aw_view_entity - List of entities that have existed in a view.

  • aw_view_relationship - List of relationships that have existed in a view.

The reporting tables are updated every five minutes.

Affiliation widget reporting schema

The tables help you investigate the data in your Affiliation widget views. Use the tables to help build your SQL query.

Affiliation Widget View Information (aw_view)

This table contains the list of views created for the affiliation widget.

Field Label Description
view_id View ID Unique ID for the view.
view_name View Name The name of the view.
view_type View Type Type of view (Default, Standard, or Custom)
product Product The product associated with the view.
therapeutic_area Therapeutic Area The therapeutic area associated with the view.
top_hco_vid Top HCO VID Top parent Veeva ID associated with the view. Typically, the health system VID.
view_status View Status Status of the view.
created_date Created Date

Created date of the view.

For views created before this release, the migration date for the release update is used.

modified_date Last Modified Date

Last modified date of the view.

For views created before this release, the migration date for the release update is used.

created_by Created By

User who created the view.

For Default and Standard views, the user is System.

If the view was created before the report was available, this might be empty.

modified by Last Modified By

User who modified the view.

For standard and default views, the user is System. Exception: When default views are removed, the user that removed the view displays.

If the view was created before the report was available, this might be empty.

Affiliation Widget View Entity (aw_view_entity)

This table contains the list of entities that have existed in a view.

Field Label Description
view_id View ID Unique ID for the view.
vid__v Entity ID Unique Veeva ID for the entity.
entity_type Entity Type Entity type.
displayed_in_view Displayed in View? Indicates whether the entity is currently displayed in the view.
created_date Created Date Created date of the entity in the view.
modified_date Last Modified Date Last modified date of the entity in the view.
created_by Created By

User who added the entity to the view.

The user is System whenever HCPs are added to the default view when load_to_default_view = Y.

modified by Last Modified By

User who last added or removed the entity from the view.

The user is System whenever invalid entities are removed, merges, and other system processes occur to cleanse the data.

Affiliation Widget View Relationship (aw_view_relationship)

This table contains the list of relationships that have existed in a view.

Field Label Description
view_id View ID Unique view ID for the Affiliation widget.
relationship_vid Relationship VID Unique Veeva ID for the ParentHCO and Affiliation relationships.
relationship_type Relationship Type Type of relationship (ParenHCO or Affiliation).
displayed_in_view Displayed in View? Indicates whether the relationship is currently displayed in the view.
created_date Created Date Created date of the relationship in the view.
modified_date Last Modified Date Last modified date of the relationship in the view.
created_by Created By

User who created the relationship in the view.

The user is System whenever relationships are added based on the widget configuration.
modified by Last Modified By

User who last added or removed the relationship from the view.

The user is System whenever invalid relationships are removed, merges, and other system processes occur to cleanse the data.

Sample query

A query called Aggregated Summary of Affiliation Widget Views is provided to help summarize the Affiliation widget data. The query is available by default if the Affiliation widget is enabled in your Network instance.

To use the query:

  1. In the SQL Query Editor, click Sample Queries.

  2. Scroll to the Network Widgets section, or type a search term (for example, widget) in the search bar to quickly find it.

  3. Select the query and click Insert Selected Query.

  4. Click Run Query.

The Report Results section displays the data for all of the Affiliation widgets enabled in your Network instance.

You can download the report to analyze the data or to share it.

Query examples

Example 1

Run this query to report on the entities in your views. The query joins the aw_view table and aw_view_entity table.

SELECT
         view_name,
         aw_view_entity.view_id,
         corporate_name__v,
 top_hco_vid,
         account_link_product__v,
         account_link_therapeutic_area__v,
         aw_view_entity.vid__v,
         entity_type,
         displayed_in_view,
         aw_view_entity.created_date,
         aw_view_entity.created_by,
         aw_view_entity.modified_date,
         aw_view_entity.modified_by
     FROM
         aw_view_entity JOIN aw_view
             ON aw_view.view_id = aw_view_entity.view_id JOIN hco
             ON top_hco_vid = hco.vid__v
     ORDER BY
         modified_date DESC

Results

The results will display details for the therapeutic area and product, the health system name, and so on.

Example 2

Use this query to extract all the data in the Affiliation widget. 

SELECT
        view_name,
        aw_view_entity.view_id,
        top_hco_vid,
        account_link_product__v,
        account_link_therapeutic_area__v,
        vid__v,
        entity_type,
        displayed_in_view
    FROM
        aw_view_entity JOIN aw_view
            ON aw_view.view_id = aw_view_entity.view_id

union

SELECT
        view_name,
        aw_view_relationship.view_id,
        top_hco_vid,
        account_link_product__v,
        account_link_therapeutic_area__v,
        relationship_vid,
        relationship_type,
        displayed_in_view
    FROM
        aw_view_relationship JOIN aw_view
            ON aw_view.view_id = aw_view_relationship.view_id

Results

The results will display details for the view, therapeutic area and product, and the health system.

Saved reports

You can save a report query so you can schedule the report or run it manually and export the results to your FTP folder.

For more details, see Saved report options.