# OData Queries
CSOD’s Reporting APIs use the OData protocol. For more information regarding the OData protocol, please check http://www.odata.org/. Cornerstone is currently using OData version 4.0. Please note that not every OData option is available.
TIP
For best performance, reduce the payload at its source.
The Reporting API allows you to retrieve data from your Cornerstone Real-time Data Warehouse using legacy reporting views in the report schema (report.vw_rpt_*), which are the data source for custom reporting in the Cornerstone application. These views produce denormalized representation of the relational tables used by the CSOD application. As a result, the views can become very large vertically (number of rows), but more importantly they can be very wide horizontally by the number of properties or fields they cover. You can think of the views as an aggregation of not only the records in each table but also of the columns they hold.
- It's important to review your use case and what you plan to do with the data, retrieve only what you need rather than getting everything.
- It is much more efficient to reduce your payload at the source, doing so will yield significant performance gains in response and processing time from the service.
- For this reason, it is always a best practice to utilize the $select option to eliminate any unnecessary fields.
TIP
Many browsers and applications have a max URL length of 2083 characters, which may limit your ability to pass large query parameters.
# Custom Fields
Whenever a custom field is created in the portal, it is assigned a database-generated encoded ID.
WARNING
Please note this encoded ID may not be the same between environments. The ID will never change within its respective environment except for copy downs.
The general rule for locating custom fields is by looking for _cf appended to a view name. Please note that
custom fields are categorized by type/entity and will not be found in a single view. You can find the custom
field type/entity in Custom Field Administration by navigating to: Admin > Tools > Core Functions > Custom Field Administration.
The custom field views include the encoded field ID and the value selected for that field. In order to get the mapping between the encoded field ID and the name of the custom field as seen in your CSOD portal, you will need to call OU API or Employee API v2. For any other custom field mappings, a ticket must be logged with Global Customer Support (GCS).
# Custom Field Types
For dropdown, radio button, multiple checkbox, and branched hierarchy custom fields, the transactional view may provide you with an ID in your return. To map the ID with the description of the record, you must map the ID to one of the local views.
For example, take a user custom field labeled Exempt that is stored as user_custom_field_00001 that returns a value of 1 or 2.
In the vw_rpt_custom_field_value_local view, $filter= cfvl_value_id eq 1 or $filter= cfvl_value_id eq 2 may return
Exempt or Nonexempt.
TIP
Please note that these specific views are localized, meaning you may see multiple values for a given ID. This allows us to report on certain culture ID’s or languages. If you are only looking for a single value, you will want to limit your query to a single culture ID (culture_id=1 is en-US).
Below is a listing of where to look up these values based on custom field type:
| Custom Field View Name | Value Lookup View | Value ID |
|---|---|---|
| vw_rpt_application_cf | vw_rpt_custom_field_value_local | cfvl_value_id |
| vw_rpt_jp_incumbent_smp_cf | vw_rpt_custom_field_value_local | cfvl_value_id |
| vw_rpt_jp_ou_cf | vw_rpt_custom_field_value_local | cfvl_value_id |
| vw_rpt_jp_position_smp_cf | vw_rpt_custom_field_value_local | cfvl_value_id |
| vw_rpt_jp_successor_smp_cf | vw_rpt_custom_field_value_local | cfvl_value_id |
| vw_rpt_offer_letter_cf | vw_rpt_custom_field_value_local | cfvl_value_id |
| vw_rpt_ou_cf2 | vw_rpt_custom_field_value_local | cfvl_value_id |
| vw_rpt_resume | vw_rpt_resume_section_attribute_value_local | rsal_attribute_id |
| vw_rpt_succession_incumbent_smp_cf | vw_rpt_custom_field_value_local | cfvl_value_id |
| vw_rpt_succession_successor_smp_cf | vw_rpt_custom_field_value_local | cfvl_value_id |
| vw_rpt_training_cf | vw_rpt_lo_form_cf_display_value_local | cfvl_field_id |
| vw_rpt_transaction_cf | vw_rpt_transaction_custom_field_option_local | tol_value_id |
| vw_rpt_transcript_cf | vw_rpt_lo_form_cf_display_value_local | cfvl_field_id |
| vw_rpt_user_cf | vw_rpt_custom_field_value_local | cfvl_value_id |
# Delta Conditions
For clients pulling information on a regular basis, it is recommended that you apply a $filter to your OData call. Depending on a given view, there may not be a last modified date for the information presented. Based on certain views, there may be other information or date filters that you can apply to limit the amount of information being returned. During the design phase of your reporting development, it is recommended to determine the best criteria that makes sense for your report specifications.
In most cases, the recommended delta condition is _last_touched_dt_utc > {date criteria}, except in views where _last_touched_dt_utc is not available.
# Unique IDs
The data returned via the Reporting API end points are from views in the CSOD data warehouse that are in turn created from CSOD’s transactional database. Some views may not have a single unique ID for the entire dataset. The unique keys are identified in the RTDW documentation, under the field labled Unique Key notated above each object.