Question
· Oct 3

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

#CSV #HL7 #Management Portal #Message Search #Tips & Tricks #Ensemble

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 (12)5
Log in or sign up to continue
Colin Brough · Oct 3

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 0
Jeffrey Drumm · Oct 3

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

SELECT TOP 100 head.ID As ID, 
    {fn RIGHT(%EXTERNAL(head.TimeCreated),999 )} As TimeCreated,
    head.SessionId As Session, 
    head.Status As Status,
    CASE head.IsError WHEN 1 THEN 'Error' ELSE 'OK' END As Error,
    head.SourceConfigName As Source,
    head.TargetConfigName As Target,
    head.SourceConfigName, 
    head.TargetConfigName, 
    head.MessageBodyClassName As BodyClassname, 
    head.MessageBodyId As BodyId, 
    EnsLib_HL7.Message.%ID As 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) 
    ORDER BY 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.

1 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.

1 0