Notifications
Clear all
Topic starter
Hi All,
I'm having some difficulty writing a report that displays the observations associated to flowsheets in the EMR, has anyone done this? Basically all I want to do is HEIROBJS name be my targeted flowsheet and then have it display each OBSHEAD.NAME that makes that report up (I don't care about the values recorded just the content that makes up the flowsheet). For some reason I cannot seem to do this no matter what combination of HEIROBJS/HEIRGRPS/OBSHEAD/FLOWSHEETLABELS I use (or even just the Flowsheet View).
I'm trying to set up a "not" list of lab result (observations) not to pull into the CCD-A with the Qvera interface but when I use it I'm getting an error. I think this is because my flowsheet is not classified as they want it to be but I want to be able to pull all the relevant fields to help troubleshoot; without reproducing it in crystal I'm having trouble diagnosing the gap.
Thanks
Mike
Posted : July 22, 2015 4:40 am
This took me forever to figure out but I think I got it. This works in Oracle/EMR 9.8.9. You would replace "Diagnostic Results" with any Flowsheet name and it would return all of the obs from that flowsheet. Your query can only match one flowsheet at a time.
SELECT obshead.name From obshead where
hdid in (SELECT * from Table(ML.F_NUMBER_TABLE((SELECT OBJECTID from hierobjs where name like 'Diagnostic Results'))))
Posted : July 22, 2015 10:30 am
Here is one more for you that will join HIEROBJS to OBSHEAD. You could make this a view to create a report on it or you could possibly make it into a Crystal reports SQL Command.
SELECT HIEROBJS.OBJECTID, HIEROBJS.NAME, OBSHEAD.* FROM ML.HIEROBJS, TABLE(ML.F_NUMBER_TABLE(HIEROBJS.OBJECTID)) t
INNER JOIN ML.OBSHEAD ON OBSHEAD.HDID IN t.COLUMN_VALUE WHERE HIEROBJS.PRTATTRID = 1000 ORDER BY HIEROBJS.NAME
Posted : July 22, 2015 11:35 am
Topic starter
Awesome, thank you very much! I ended up figuring out that for the Qvera "Not" lists they can deliver as part of the TOC Outbound channel the flow sheets have to be built under the "Interfaces" folder and then (inside this folder) under a folder called CCDA. So effectively:
Chart -> Flowsheet views -> Enterprise -> Interfaces -> CCDA -> "Flowsheet"
For it to work, that is how they set it up. However what you did will help us immensely, being able to report on the Observations associated to flowsheets (for building/maintenance purposes) will be huge.
Thanks
Mike
Posted : July 23, 2015 3:49 am
Here is the view I ended up creating in our system with it. I figured I would need it in the future so I better put it in there while it is fresh in my memory: (IMA is the name of our practice so all of my customizations start with IMA_)
CREATE OR REPLACE FORCE VIEW "ML"."IMA_FLOWSHEET"
("OBJECTID", "FLOWSHEET_NAME", "HDID", "GROUPID", "XID", "NAME",
"DESCRIPTION","UNIT", "OBSTYPE", "MLCODE", "CPTCODE", "ICDCODE",
"LOINCCODE", "SNOMEDCODE", "OTHERCODE", "FACTORID", "ENCODEFMT", "DECODEFMT", "FMTLIB", "POSTEXPR", "PREEXPR", "ACTIVE", "KEYWORD", "DB_CREATE_DATE", "DB_UPDATED_DATE")
AS
SELECT HIEROBJS.OBJECTID, HIEROBJS.NAME FLOWSHEET_NAME,
OBSHEAD."HDID",OBSHEAD."GROUPID",OBSHEAD."XID",OBSHEAD."NAME",
OBSHEAD."DESCRIPTION",OBSHEAD."UNIT",OBSHEAD."OBSTYPE",
OBSHEAD."MLCODE",OBSHEAD."CPTCODE",OBSHEAD."ICDCODE",
OBSHEAD."LOINCCODE",OBSHEAD."SNOMEDCODE",OBSHEAD."OTHERCODE",
OBSHEAD."FACTORID",OBSHEAD."ENCODEFMT",OBSHEAD."DECODEFMT",
OBSHEAD."FMTLIB",OBSHEAD."POSTEXPR",OBSHEAD."PREEXPR",
OBSHEAD."ACTIVE",OBSHEAD."KEYWORD",OBSHEAD."DB_CREATE_DATE",
OBSHEAD."DB_UPDATED_DATE"
FROM ML.HIEROBJS, TABLE(ML.F_NUMBER_TABLE(HIEROBJS.OBJECTID)) t
INNER JOIN ML.OBSHEAD ON OBSHEAD.HDID IN t.COLUMN_VALUE
WHERE
HIEROBJS.PRTATTRID = 1000 ORDER BY HIEROBJS.NAME;
GRANT SELECT ON "ML"."IMA_FLOWSHEET" TO "REPORT";
EDIT: I switched it to an exported version from SQL Developer instead of the initial query I wrote which makes it more explicit on which rows and also adds the grant statement.
Posted : July 23, 2015 4:45 am
Topic starter
Thank you very much!
Posted : July 23, 2015 8:45 am
