0
129

Written by

Colin Brough
Integrations Developer at NHS Tayside
Follow
Question Colin Brough · Oct 3

Can we save Message Viewer Query output to file (eg CSV)

#Ensemble #CSV #HL7 #Key Question #Management Portal #Message Search

Is there any way of saving a representation of the results of a query created in the Message Viewer to a file - most obviously CSV.

We are reasonably adept at creating queries. We'd like to be able to send the output to a file, rather than resorting to cut'n'pasting from the message viewer window...

Is this possible? (on any version of Ensemble/Iris?)

Desired output to file something like:

ID,TimeCreated,Session,Status,Error,Source,Target,Body_MSH_MessageControlId,.....
1,8888888,2025-08-20 05:03:14.324,8438123,Completed,OK,ICE ADT Validator,ICE ADT TCP,1z123456,20220822......
Product version: Ensemble 2018.1
$ZV: Cache for Windows (x86-64) 2018.1 (Build 184U) Wed Sep 19 2018 09:09:22 EDT
Discussion (0)0
Log in or sign up to continue

Comments

Colin Brough  Oct 3 to David.M

Yeah, should have said we've used that - but we've not figured out how to use VDoc specifications or their equivalent to output specific fields from the HL7 message as you can in the message viewer. In the SQL text shown by "Show Query" you get something like:

null As FieldName

where the column specified in the message viewer via a VDoc display specification would be.

Query in Message Viewer includes display item: 

This produces SQL query below:

0
Jeffrey Drumm  Oct 3 to Colin Brough

Here's an example of the generated query, updated with calls to HICG.GetMsg():
 

SELECT TOP 100 head.ID AsID, 
    {fn RIGHT(%EXTERNAL(head.TimeCreated),999 )} As TimeCreated,
    head.SessionId AsSession, 
    head.Status AsStatus,
    CASE head.IsError WHEN1THEN'Error'ELSE'OK'ENDAsError,
    head.SourceConfigName AsSource,
    head.TargetConfigName As Target,
    head.SourceConfigName, 
    head.TargetConfigName, 
    head.MessageBodyClassName As BodyClassname, 
    head.MessageBodyId As BodyId, 
    EnsLib_HL7.Message.%IDAs Body_ID, 
    HICG.GetMsg(head.MessageBodyId,'PID:3') As Body_PID_PatientID,
    HICG.GetMsg(head.MessageBodyId,'PID:18') As Body_PID_PatientAccountNumber 
    FROM %IGNOREINDEX Ens.MessageHeader.SessionId Ens.MessageHeader head, 
        EnsLib_HL7.Message 
    WHERE (((head.SourceConfigName = 'From_Some_Router'AND head.TargetConfigName = 'To_Some_Operation')) 
        AND head.MessageBodyClassName='EnsLib.HL7.Message'AND head.MessageBodyId=EnsLib_HL7.Message.%ID) 
    ORDERBY head.ID Desc

I just replaced the null values with calls to HICG.GetMsg(). The 2nd argument is the schema-specific path to the values you're extracting, using the same syntax as found in EnsLib.HL7.Message's GetValueAt() method. You can download the class from the HL7 Spy website: https://hl7spy.ca/downloads/HICG_HL7.xml.

0
Jeffrey Drumm · Oct 3

While @David.M's response will provide a query, it will likely not include the HL7 message-derived values. For example, a search that includes the HL7 field values you've listed will return null for their values when executed via the SQL menu or SQL shell.

That said, I created a tool (originally for use with HL7 Spy to extract HL7 messages from Ensemble/Health Connect) that has some features that may help get you what you want. See this thread for an example of its use.

0
Colin Brough  Oct 7 to Jeffrey Drumm

This is fantastic - exactly what we were looking for. Thanks!

0
Evgeny Shvarov  Oct 7 to Jeffrey Drumm

Hi Jeff! If you could share it on OEX too one day that'd be amazing!

0
Jeffrey Drumm  Oct 7 to Evgeny Shvarov

I've never created an IPM module ... it seems like a fair amount of effort to set it up for a single small class and based  on my current status it's unlikely that I'd do it more than this one time.

That said, if some streamlined mechanism for building and publishing it exists, I'll give it a shot ...

0
Raef Youssef · Oct 3

You can compose a sql query that has the same conditions and query the Ens.MessageHeader table. You can export to whatever format from the SQL explorer.

0
Colin Brough  Oct 7 to Scott Roth

Interesting, will maybe pursue at a later date - Jeffrey's suggestion above works for us, and we've also got organisational constraints on what browser extensions we are allowed to install, which makes trying this non-trivial.

0
Scott Roth  Oct 7 to Colin Brough

I have selected the results in the past and pasted them into Excel but then you have to update the formatting.

0
Colin Brough  Oct 7 to Scott Roth

Yeah, this is what we were doing (and wanting to avoid). So being able to do the queries in SQL, including the HL7 content, and exporting the output to CSV (which Excel can import) means we have the tools we need for future diagnostics/issue resolution.

0