New article
Recently updated
Reporting on Inform data using external BI tools
Who is this article for?
Administrators and IT teams at organisations that use a BI tool, SQL queries, or a database connection to extract or report on InForm data from EHS Core.
This article explains a change to how InForm form response data is stored in EHS Core, what it means for external reports and BI connections, and what to update to keep your reports running correctly.
Are you affected?
This change affects you if your organisation does any of the following:
- Connects a BI tool such as Power BI, Tableau, or similar to the EHS Core database
- Runs SQL queries against the EHS Core database to extract InForm form response data
- Has an automated pipeline or integration that reads from the EHS Core database
If you are unsure, check with whoever manages your reporting or IT integrations.
What changed and why
To improve InForm performance, the way form response data is stored internally has changed. Previously, each answer to every question was stored as an individual row in the database. Responses are now stored together as a single record in a new table called cs_inform_form_record_history, with all answers bundled into a single JSON column.
This makes InForm significantly faster, but it means the data layout is different. Reports querying the original cs_inform_response table directly will appear to stop receiving new data once this change is in place. No data is deleted — all existing data remains intact.
What to update
Your BI or ETL pipeline needs to read from two sources and handle JSON decoding on the receiving end:
-
Query
cs_inform_form_record_history(filtered byis_latest = 1for the most recent state of each form) for all new submissions -
Query
cs_inform_response(filtered bymigrated_to_history = 0) for any older records not yet snapshotted into the history table -
Decode
responses_jsonin your BI tool, ETL job, or warehouse to produce row-per-answer output as needed
Most modern BI tools — including Power BI, Tableau, Looker, Snowflake, BigQuery, and Databricks — can parse and expand JSON natively. Full instructions, including schema reference, recommended queries, and worked examples for common tools, are in the technical guide attached to this article.
We recommend making these updates before your site updates to avoid any interruption to your reports.
Want to choose when your site updates?
If your organisation is affected by this change and you'd like to select your preferred update date, complete our scheduling form and our Support Team will be in touch to confirm the details with you directly.
Need help?
If you have questions about the change or need assistance reviewing your queries, contact our support team or reach out to your Account Manager directly.