Honeydew Helps: SQL Reporting on Lab Misc and Free-text Results

By Jack Hoblog, Honeydew Helps

Honeydew Helps is a series where we answer some of the real questions our clients have reached out to us for. They may dive deep into technical aspects of each topic, but we hope to share the insights with anyone else that may have a similar question or would like to learn more.

If you have questions on this article or would like to submit your own question, please reach out to [email protected].

Question: “Can I get a report for all the tests being ordered out as Lab Misc tests? More specifically, can I get a report for all the HLA tests being ordered and who is placing those orders?“

Background

Many tests like HLA testing are sendouts that may not be built as a specific sendout test due to the lower volume (or perhaps it is a new sendout test and the OVT has not been built yet). The Beaker solution for these workflows is typically the Lab Misc Test.

This comes into Beaker as a sendout test where the result components on the test contain the name of the test being ordered as a free text field. This comes from the LQL order question filled out by the ordering provider so the lab knows what test needs to be ordered and processed for the receiving lab. Another result component is typically used to document the sendout lab it is going to as well.

This creates an obvious obstacle in reporting since the test themselves are not discretely documented in Epic. Cogito tools like RWB and even SlicerDicer will have trouble handling the variance that comes with a free-text field. With SQL reporting, we can program in the string handling as well as any complicated logic when comparing result components for even more focused reports.

Here we use a short SQL script that can be used in a Clarity report to gather this data from a free-text field.

SQL (Oracle):


SELECT v_lab_results.result_id
	,upper(rtrim(res_components.component_value)) AS test_name
	,(
		SELECT rescomp2.component_value
		FROM res_components rescomp21
		WHERE rescomp2.result_id = v_lab_results.result_id
			AND rescomp2.component_id = <LRR ID storing sendout lab>
		) AS sent_to
	,v_lab_results.authorizing_prov_name2
	,v_lab_results.result_status_name
	,v_lab_results.ordered_datetime
	,v_lab_results.received_datetime
FROM v_lab_results
LEFT OUTER JOIN res_components ON res_components.result_id = v_lab_results.result_id
WHERE v_lab_results.test_id = <OVT ID OF the Lab Misc test>3
	AND res_components.component_id = <LRR ID OF test name>
	AND upper(rtrim(res_components.component_value)) LIKE '%HLA%'4
	AND v_lab_results.ordered_datetime > '1-JAN-2024'5

Notes:

  1. Setting an alias of “rescomp2” since we are referencing the RES_COMPONENTS table twice for different purposes. This grabs the value in the LRR where the value for the reference lab may be stored. ↩︎
  2. We add in columns for provider names, status, ordered and received time. In SQL you can easily pull in any other data field your interested in as well. Many useful columns are already on V_LAB_RESULTS, but many other tables can be directly joined to it. ↩︎
  3. We filter by the OVT ID to only include Lab Misc Test and further by the LRR ID that contains the requested test name ↩︎
  4. We apply some string handling to correct for mixed case and any extra unintended spaces. We are looking for “HLA” tests in this scenario so we used the LIKE operator and the wildcards on both sides ↩︎
  5. Additionally a time range filter can be added with a WHERE statement(s). In this case we just want all tests ordered since 1/1/24. Epic’s standard relative dates (M-1, Y-1, T-60, etc.) can be used as well. Reach out if you want to know how! ↩︎

Sample Output

All names randomly generated. Any similarity to actual persons, living or dead, is purely coincidental!

Here we see the data directly out of Clarity in a tabular format, which can easily be sorted and further manipulated in Excel. Note that one of the tests returned was actually for GC/CHLAMYDIA, which demonstrates the challenge of reporting over free-text fields. This however can easily be accounted for with another WHERE statement to exclude tests that either have characters left or right of “HLA” or we could specifically exclude tests that contain the entire string “CHLAMYDIA”. However in the beginning it is useful to cast a wider net, just to confirm that we are not accidentally excluding anything that we might have wanted to include.

If you have Clarity/SQL questions related to this or any other project please reach out and we would be happy to discuss them!