Reporting on field sets
DM
In your reports, you can specify a field set so you don't have to list fields individually. For example, if you wanted to refine your query using HCP focus areas, you can specify All HCP Focus Areas in the basic report builder or hcp_focus_areas
in the SQL editor; this value includes all 36 fields (hcp_focus_area_1__v
to hcp_focus_area_36__v
) in that set.
The following table contains all of the groups you can specify, along with their corresponding SQL field names and the individual fields that are included.
Field Set | SQL Field Name | Data Model Fields |
---|---|---|
All Accreditations |
hco_accreditations | hco_accreditation_area_1__v to hco_accreditation_10__v (Note: These fields are not considered a set for Japan). |
All Address Lines | address_lines | address_line_1__v to address_line_3__v |
All Alternate Names | alternate_names | alternate_name_1__v to alternate_name_10__v |
All Credentials | credentials | credentials_1__v to credentials_5__v |
All Delivery Address Lines | delivery_addresses | delivery_address_line_1__v to delivery_address_line_3__v |
All Emails | emails | email_1__v to email_10__v |
All Faxes | faxes | fax_1__v to fax_10__v |
All HCO Focus Areas | hco_focus_areas | hco_focus_area_1__v to hco_focus_area_10__v |
All HCP Focus Areas | hcp_focus_areas | hcp_focus_area_1__v to hcp_focus_area_36__v |
All Medical Degrees | medical_degrees | medical_degree_1__v to medical_degree_5__v |
All National IDs | national_ids | national_id__v to national_id_5__v |
All Phones | phones | phone_1__v to phone_10__v |
All Specialties | specialties | specialty_1__v to specialty_10__v |
All URLs | URLs | URL_1__v to URL_2__v |
Simplified query using field sets
In basic reporting queries, you can select a single field to represent all fields in a group by selecting it in the Field drop-down list in the Filters section for the query. Each field set includes an item in the list prefixed by All to represent all fields in the group; for example, All Specialties, All Emails, and so on.
Using the SQL query builder, you can specify a single field (for example, specialties
) to represent multiple fields (for example, specialty_1__v, specialty_2__v, and so on).
When you specify a single field; for example, specialties
, the results will include values from all fields in the set.
select specialties from hcp where specialties in (‘FM’)
This example will return results that include FM (Family Medicine) in any field in the set (that is, from specialty_1__v, specialty_2__v, through specialty_10__v).
Use the EXPLODE operator
In the SQL query builder, the EXPLODE operator performs an aggregate function on sets of numbered fields.
The following example shows how to use the EXPLODE operator with the specialties fields. For more information about this query, see HCP number of specialties.
A similar query without using the EXPLODE operator has different results.