MEDITECH Data Repository Implementation Best Practices

Learn about MEDITECH Data Repository Best Practices on 8/5/2014 and 8/14/2014 in these educational webcasts hosted by Iatric Systems.

Whether you’re in the midst of the installation process now, already implemented but experiencing issues like system slowness, or simply aren’t yet leveraging Data Repository reporting benefits, this best practices educational webcast is for you.

Please select a date below to read all of the specific topics that we’ll cover and register to attend:

You’ll learn how to properly implement and maximize the benefits of MEDITECH’s Data Repository. We hope you can join us!

NPR Tip (MAGIC or Client/Server) – When Standard Graphics Attributes Fall Short

Joe Cocuzzo, Senior Vice President – Report Writing Services

One limitation of NPR report graphical attributes is that you can only start vertical lines at the top of a box. Here is an example of a page header for a discharge order form:

Many preprinted forms have slightly different graphics for a page header, looking like this, leaving a space in the upper right for an “address-o-graph” stamping:

How could we reproduce this on an NPR report?

You can produce a vertical line, horizontal line, or grey or white box anywhere on the page in a C/S or MAGIC NPR report by using the utility Z.graphics, which has the following arguments:

Arguments:       A = Box type   0 – black border

1 – grey box

2 – grey box and black border

B = length of box

C = ht of box (DFT=1)  (can be 0 for just a horizontal line)

D = number of chars to move right (+) or left (-) before starting box

E = number of chars to move down (+) or (-) before starting box.

F = gray scale (default = 10%)

G = width of lines (DFT=5)

So if we remove the BX command from the page header of the report, and use the HL= (horizontal line) command to draw a line across the bottom of the page header, we can just add a call to Z.graphics to draw the vertical line down from the top of the page to the vertical line:

Let’s review how we are using the Z.graphics command.  To draw our box, we pass the following arguments:

A = 0, this means we want a black border box

B = 0, length of box is zero which means we are drawing a vertical line.

C = 6, make the box 6 lines high (decimals are accepted as values)

D = 65 move to the right 65 spaces before starting the “box” (really a line here)

E = 0-5.5  We need to move up 5.5 lines so we subtract 5.5 from zero for the “move up” argument.  We could also pass “-5.5” as the E argument.

F = not relevant, no grey shading

G = width of line (0-9 accepted)

(Note that to draw horizontal lines, you pass a non-zero length and a zero height).

We could also use an LC attribute to hold our Z.graphics code:

A LC expression becomes the condition of an IF statement that is wrapped around the line. So if you use an LC for other purposes, you need to decide if you need your line to print or be suppressed.  To control what happens, add a nil or a value at the end of your LC expression, and that will control the value of the IF condition and will control whether the line prints.

FINAL IMPORTANT POINT:

If you have Boxes anywhere on your report, MEDITECH will automatically create a box around the entire page, so unless I add a special footnote to this report, I will still get this:

To prevent the default boxing add the following footnote to your report. (This footnote also will suppress the boxing of the standard page banner if your report has one).

Once we have done this, the automatic boxing of the page no longer occurs:

This article originally appeared in the July 2014 issue of Iatric Systems Updates! newsletter.

SQL Tip –  Find Queries, Document Sections and Interventions (MEDITECH 6.x Only)

Thomas Harlan, Technical Team Lead – Data Repository at Iatric Systems

A report request arrives on your desk – complete with screenshots of screens from MEDITECH complete with circles and arrows. But the fields are unfamiliar… a query to the requestor for a Shift-F9 and screenshot of the resulting pop-up yields nothing. “Ah,” you think, “custom queries on a CDS, document section or intervention. Why oh why didn’t MEDITECH code Shift-F9 so it would tell us what query mnemonic was in play? Why?

But fear not, thanks to the DR xfer process also covering dictionaries, you can use SQL to query the build of screens, assessments, and interventions. Attached to this tip is a stored procedure that searches the dictionaries for usage of queries and lets you know not only which screens they are used on, but which collector tables contain the responses!

As with all of our example SQL code, you will need to run the script against your DR to compile a named query (or stored procedure) that you can then call with a simple SQL command like:

EXEC zcus.dbo.MIS_zcus_iatric_find_query_config ‘CAN.OX’ — Query mnemonic to search for or ALL

,’ALL’ — Query text to search for or ALL

,’ALL’ — Document Section Mnemonic or ALL

,’ALL’ — Document Section text or ALL

,’ALL’ — Intervention Mnemonic or ALL

,’ALL’ — Intervention text to search for or ALL

,’N’   — Show Row Counts from storage locations

Note that each parameter drives a LIKE predicate in the code, so you can search for partial strings (or use wildcards like % and _).

Also note that you can mix the fields, using some or all of them.

Those screen shots you got? With the circles and arrows? Use the text labels from the fields to search by Query Text and you will find what you’re looking for.

In this example our search finds us five records:

Query ID

Query Text

Query Type

Group Response ID

MIS Doc Sect ID

MIS Doc Sect Name

Pcs Intervention ID

Pcs Intervention Name

CAN.OX

O2 Delivery Method

Group

CAN.OX

RAD.D/C

Radiology D/C documentation

(None)

(None)

CAN.OX

O2 Delivery Method

Group

CAN.OX

GEN.CANCER

Cancer Center Documentation

CANCER.CENTER

Cancer Center Documentation

CAN.OX

O2 Delivery Method

Group

CAN.OX

GEN.CANCER

Cancer Center Documentation

RN.CANCERCENTER

Documentation for Ambulatory Services

CAN.OX

O2 Delivery Method

Group

CAN.OX

GEN.RADRN

Radiology RN Documentation

RN.RAD

Radiology RN Documentation

CAN.OX

O2 Delivery Method

Group

CAN.OX

RAD.DC

Radiology D/C documentation

RN.RAD

Radiology RN Documentation

Here we see that the CAN.OX query can be find on four different document sections in three different interventions – or as a standalone.

Particularly when you are trying to retrieve PCS assessment query data, your speediest way to get at the response is to first identify potential visits, then search those visit’s interventions, then document sections and then finally the query/response data. Knowing which interventions and document sections hold the queries you are looking for will speed up that process enormously.

A Warning Note: But wait, you say, what about RegAcctQuery_Result? Doesn’t it hold all of the PCS query responses? Isn’t it designed to be reported against so you don’t have to plunge into the hellish complexity of the PCS tables?

Yes, but… a query needs to have its AccountQuery flag set to Y before the responses will flow over to RegAcctQuery_Result and the background job that populates the table from the PCS tables needs to be working flawlessly.

Unfortunately, for the data that you need for this latest report… that probably won’t be true. So back to the PCS mines for you!

Bonus! Attached to this tip, in addition to the SQL code, is a Microsoft Reporting Services report to package it up all pretty-like and make it easy to distribute to your analysts.

Extra Credit: When you examine the code, you’ll find that the @cRowCountYN parameter does not search every single possible response storage location. Add some more! Particularly if you know there are custom screens attached to modules like MM or BAR at your organization.

This article originally appeared in the July 2014 issue of Iatric Systems Updates! newsletter.

Iatric Systems Webcast: Report Designer and NPR Tips ‘n’ Tricks

“An Old Dog is Still Learning New Tricks!” Joe Cocuzzo will present a variety of Report Designer and NPR tips and tricks, including (but not limited to):

• A Report Designer “search report for fields” equivalent
• Gray bars where you want them in RD
• “Two and a half tools” – a Rosetta stone of sorts for report writers using Magic NPR, C/S NPR, and RD
• Turn an NPR report into a procedure
• Exploit user activity data in a BAR report to track comments efficiently

Learn more and  register for this July 8 webcast, from 2-3PM ET.

 

NPR Tip (MAGIC or Client/Server) – Report to Download with “CSV” Extension Opens Immediately in Excel 

NPR Tip (MAGIC or Client/Server) – Report to Download with “CSV” Extension Opens Immediately in Excel

At MUSE 2014, the most popular “tip” was probably the demonstration of an NPR report that allows the user to send output to “DOWNLOAD” with a CSV extension on the file and two footnotes on the report allow the just created file to open automatically in Excel. No “End of Report” message to close. No need to find the report flat file and open with Excel.

All you need is a START footnote that suppresses the “END OF REPORT” message and a CLOSE.UP macro that closes the download file “early” and then runs Excel for the file just made by the report.

In MAGIC:

NPR Tip

NPR Tip

In Client/Server:

NPR Tip

If the user runs the report to a download file with the “.CSV” extension, assuming that file type is associated with Excel (almost always true), Excel will run automatically and open the file in a spreadsheet.

Notice that in both sets of footnotes, the name of the file is saved to a variable and then passed to either the %Z.link.to.shell program (MAGIC) or @Shell.execute macro (C/S).  That is because Z.rw.close.up(0) closes data file and dictionary prefixes and cleans up the temp file and if you don’t save the file name from slash to a variable, it won’t be there to pass to the program or macro after the “close up.”

The complete Powerpoint for our Tips and Tricks Session, all report examples (including this one) and some DR documentation of the DR example tip/trick are available here: http://www.iatric.com/MUSE2014

Try it. You’ll like it.

DR Tip –  Iatric DR SQL and Microsoft Reporting Services Tip ˜ Date Mnemonics

Thomas Harlan, Technical Team Lead – Data Repository at Iatric Systems

You’ve built a great query, wrapped it up in a beautiful Microsoft Reporting Services report and published it to the SSRS web portal, all ready for your end users to run anytime they want, with the parameters they want… perfection awaits!

  • Then the first user emails and says “how do I enter TODAY-1 in this report?”
  • And then the second user emails and says “how do I schedule this report to run for the last calendar month?”

And then you realize, to your horror, that Microsoft Reporting Services has a nice date-picker, but no equivalent of the MEDITECH Date Mnemonics, which make entering date parameters in NPR easy-peasy… and to schedule a report to run monthly, you have to set up a schedule for each month, with hard-coded dates!

That is when the Bill Gates dartboard comes out… but do not despair, we have a solution for you!

Note that this solution is specific to Microsoft Reporting Services. You can do the same in Crystal Reports/Business Objects but you can’t display the results of the date mnemonic like we do here.

First, we need a way to convert a date mnemonic string (like MB-1) into an actual date. Second, we need to pass that real date into your stored procedure that executes the query to get data for the report.

We wrote two versions of the date conversion code:

  • MIS_iatric_DateInterpreter_fn [code]
  • MIS_iatric_DateInterpreter_sp [code]

One is a function you can call inside your own queries, if needed, and the other is a stored procedure to call from the Parameter setup screen in Reporting Services. Both are available on the Iatric Systems Tip page. Both scripts assume you have a custom database container on your DR server(s) called zcus. If you have a different database container for custom code, you’ll need to update the script accordingly.

The function and stored procedure support these mnemonic patterns:

Mnemonic Pattern Notes

T

Today (t=today or datadate if passed, t+n, t-n)

W

Week (w=today or datadate if passed, w+n, w-n)

WB

Week Begin starting Sunday (wb, wb+n, wb-n)

WE

Week End ending Saturday (we, we+n, we-n)

M

Month (m=today or datadate if passed, m+n, m-n)

Y

Year  (y=today or datadate if passed, y+n, y-n)

MB

Month Begin (mb, mb-n, mb+n), relative to current date

ME

Month End (me, me+n, me-n), relative to current date

YB

Year Begin (yb, yb+n, yb-n), relative to current date

YE

Year End (ye, ye+n, ye-n), relative to current date

FYB

Fiscal Year Begin (fyb, fyb+n, fyb-n), relative to current date

FYE

Fiscal Year End (fye, fye+n, fye-n), relative to current date

WM

(wm,wm+n,wm-n, +/- n is months) Week of the Month (3rd Tue, 2nd Fri, 4th Thu, 5th Wed) returns date for @WeekNo for @sDayOfWeek in the month of current date

Once the function and stored procedure are in place, you can update your MS Reporting Services report like so:

Step Description

Screen Shot(s)

Update the report (RDL) to include two new parameters: cFromDate and cThruDate with a data type of Text (right-click Parameters > Add Parameter). DR Tip

DR Tip

In the Parameter listing use the Arrow icon on the Report Data header to move the two new parameters up before dStartDate and dEndDate. You want your date mnemonic fields to be active before the actual date parameters are. DR Tip
Add a new Dataset to the report (Right click Datasets > Add Dataset) to convert cFromDate into dStartDate.

On the Query screen, name it ConvertFromDateDs.

Set the Data Source to reports DR connection. Query Type is Stored Procedure. Then select or pick MIS_iatric_DateInterpreter_sp.

On the Parameters screen, set the Parameter Value for the @cDateIn parameter to [@cFromDate].

Click OK when done to save this dataset.

DR Tip

DR Tip

Add a second Dataset to the report (Right click Datasets > Add Dataset) to convert cThruDate into dEndDate.

On the Query screen, name it ConvertThruDateDs.

Set the Data Source to reports DR connection. Query Type is Stored Procedure. Then select or pick MIS_iatric_DateInterpreter_sp.

On the Parameters screen, set the Parameter Value for the @cDateIn parameter to [@cThruDate].

Click OK when done to save this dataset.

DR Tip

DR Tip

Now change the parameter properties for dStartDate (right-click dStartDate > Edit Parameter).

On the Available Values screen, select Get values from a query.

Pick ConvertFromDateDs as the Dataset.

Set the Value and Label fields to Result.

Switch to the Default Values screen and select Get values from a query.

Pick ConvertFromDateDs as the Dataset.

Set the Value field to Result.

Click OK to save your changes.

DR Tip

DR Tip

Make the same changes for dEndDate (right-click dEndDate > Edit Parameter).

On the Available Values screen, select Get values from a query.

Pick ConvertThruDateDs as the Dataset.

Set the Value and Label fields to Result.

Switch to the Default Values screen and select Get values from a query.

Pick ConvertThruDateDs as the Dataset.

Set the Value field to Result.

Click OK to save your changes.

DR Tip

DR Tip

You’re done!

Now when you run your report, the cFromDate and cThruDate fields — and the mnemonics entered there — will control what values appear in the dStartDate and dEndDate fields. Those two fields are then passed to the query when it executes.

DR Tip

Note that this approach does not require changing your query/stored procedure at all (assuming it has parameters for the date range) and leverages the pre-built function and stored procedure at the report level.

Extra Credit!

  • Try out hiding the dStartDate and dEndDate parameters (each parameter has a Visibility setting).
  • Expand the function to use additional mnemonic patterns of use to your site!

Cheers, Jen Kelly and Thomas Harlan

Visit our report library at http://www.iatric.com/Information/NPRReportLibrarySearch.aspx to look them up.

You can find additional Report Writing Tips on our website at http://www.iatric.com/Information/NPRTips.aspx, as well as information about our on-site Report Writer Training and Report Writing Services.

To subscribe for email notifications for new Report Writing classes, please follow this link:

http://www.iatric.com/Information/Classes.aspx.

For more information, please contact Karen Roemer at 978.805.3142 or email
karen.roemer@iatric.com
.

This article originally appeared in the June 2014 issue of Iatric Systems Updates! newsletter.

DR Tip – Finding fields across MAGIC/M-AT/DR Using the DR Data Def Tables

Thomas Harlan, Technical Team Lead – Data Repository at Iatric Systems

One of the challenges of DR-based report development is the same as for NPR or RD… where is the data you want stored? One way to find out is to go into MEDITECH and find the field, and – if you are lucky – you can do:

Platform Hotkey
MAGIC Shift-F8, down-arrow, down-arrow
Client/Server Shift-F9
MT6 Shift-F9

Which will get you a pop-up like this:

The top section shows you were the data element is in NPR or RD, and the bottom shows the Table (MisLoc_Main) and Column (Phone) in the DR.

This requires that you know what module/screen/field you want in the front-end, and that the field you cursor into is editable and it has a DR table/column attached. There is, however, another, more flexible, way to get at this information with SQL. In each DR database (livedb, livendb, and so on) there are two tables that hold the data definitions:

SysDrColumns
SysDrTables

Note that if you have more than one DR database (a MAGIC one, a C/S one, a M/AT one…) then you will have multiple versions of these tables. It would be convenient, therefore to have a single query (saved as a handy stored procedure) to query all of these tables for what you want.

So we’ve built: MIS_dd_query_sp to do just that. (A copy is in the library, set up for an MT6 site with a zcus database container to store the query in). If your custom database container is not named zcus, you’ll need to edit the script appropriately. You will also need to update the code to reflect your database container name(s) for LIVE. The example uses Livendb and Livefdb, as they are common, but your specific implementation may vary.

Once the script has been run and the query translated and saved into the database, you call the query like this:

EXEC [zcus].[dbo].[MIS_dd_query_sp] ‘%MisLoc_Main%’ — Table Name
,’%Phone%’ — Column name
,’%’ — DPM Name
,’%’ — Segment Name
,’%’ — Element Name
,’%MIS%’ — Application Name
,’Livendb’ — NPR Schema
,’Livefdb’ — Focus Schema
,0 — 1 is only table name, 0 is all columns

The first set of parameters:

@cTableName
@cColumnName
@cDpmName
@cSegmentName
@cElementName
@cApplication

…are set up in the query to do wildcard search, where you can use % for any number of characters and _ for a single character.

The next two parameters:

@cNPRSchema
@cFocusSchema

…are a convenience – they let the query build a fully-qualified table name (container.owner.tablename) in the query results, so that you can copy and paste that field into your code. A time saver!

The last parameter:

@nMode

Changes the output arrangement of the query. If you set this to 1, then you get a listing of only the Table Name and the Module the table(s) were found in:

DR_TABLE_NAME

MT_APP

Livefdb.dbo.MisLoc_Main

MIS

If a zero (0) then you get a listing of each column that matches your criteria, as well as supporting detail.

DR_
SOURCE
_ID

DR_TABLE
_NAME

DR_
COLUMN
_NUMBER

DR_
COLUMN
_NAME

DR_
COLUMN
_TYPE

DR_
COLUMN
_LENGTH

MT_
APP

NPR_
DPM

NPR
_SEGMENT

NPR
_ELEMENT

IAT

Livefdb.dbo.MisLoc_Main

7

Phone

varchar

40

MIS

MisLoc

Main

3

Note that this listing includes both the DR location and the RD location (MisLoc.Main.3). This stored procedure does triple duty, letting you do wildcard searches against all three data-definitions (DR, NPR and RD). But beware! These data-defs only include elements that are in the DR.

Notes

MT also publishes the NPR and RD datadefs on-line:

NPR

https://customer.meditech.com/en/d/prwrw/pages/rw6basdatadef.htm

MT 6 DR

https://www.meditech.com/prdr/Pages/DRxbASTables.htm

CS DR

https://customer.meditech.com/en/d/prwdr/pages/drcbasdbmodels.htm

MAGIC DR

https://customer.meditech.com/en/d/prwdr/pages/drmbasdbmodels.htm

Often you can find what you’re looking for by browsing the models. If you find the data element is in NPR, then you can use the search query to find the matching DR table/column.

Extra Credit

This version of the query only handles two database containers – maybe you only have one! Or maybe you have three, or more… You can easily copy/paste the code inside of the example SP to handle multiple databases.

If you have SQL Server Reporting Services or Crystal Reports you can then easily built a report to manage the presentation of the data and prompt your analysts for what they are searching for.

Visit our report library athttp://www.iatric.com/Information/NPRReportLibrarySearch.aspx to look them up.

You can find additional Report Writing Tips on our website at http://www.iatric.com/Information/NPRTips.aspx, as well as information about our on-site Report Writer Training and Report Writing Services.

To subscribe for email notifications for new Report Writing classes, please follow this link:

http://www.iatric.com/Information/Classes.aspx.

For more information, please contact Karen Roemer at 978.805.3142 or email karen.roemer@iatric.com.

This article originally appeared in the May 2014 issue of Iatric Systems Updates! newsletter.

NPR Tip (MAGIC) – Default Segment and Directory in “MOVE REPORTS”

Joe Cocuzzo, Senior Vice President – Report Writing Services

Last month we had a Client/Server only tip about a handy Page 1 of N utility. At MUSE our Tips and Tricks session will include an updated version of an approach that will do this for MAGIC NPR reports as well.  For this May tip, I will show you how to solve the minor annoyance of having to guess about the NPR master segment and directory for the MOVE REPORTS routine.

Anyone who works at multiple hospitals, or who forgets the exact combinations of dots and numbers cooked up in the naming of the latest test directory, has experienced the fun of guessing for the NPR Master Segment and NPR Directory in the MOVE routine. If you are in an HCA site, you have the added fun of dealing with segments with long cryptic names, instead of the typical A..B..C – you might have 50 choices from TNNANVA  to TNNANVZX.

This month, we will show you a report you can place on a Hot Key menu for NPR that you can use to quickly find out the name of the NPR master segment and directory you need to use for the MOVE routine.

The first problem is how to know about segments and directories you aren’t in. What if we take a look at MIS.signon and see how that program does it? After all, when you manage a device to that program, the user sees a list of both LIVE and TEST directories.

Just hit F4 while editing any macro of any report and look at the MEDITECH source code of MIS.signon, like so:

We see in this program that MIS.signon loops through NPR.MIS.DB.npr.mis.remote.index to show the user a list of “npr.mis.remote.title” fields to pick from.

Even though we cannot use the DPM NPR.MIS.DB on page 1 of a report, we can see its segments and fields in a macro with F9, and we can write code to loop through the entries and build an MV array for our report.

(Note to C/S report writers, the C/S field lookup program will not show you DPMs unless they are flagged Customer Report Writer access = Y. You can still use them in macros, but you have to figure out the names from listing MEDITECH reports or getting to the data definitions by other means.)

So, if we write a report in NPR.REP in the npr.rep.main segment and set up the selects to just print one record like this:

This is just a “trick” to force the report to select exactly one record by selecting the last report urn, regardless of what that is.

Then build an MV array in the picture:

Then write a macro “detail” to be called from an AL D footnote:

The macro does the same looping as MIS.signon, but show the user segment and directory rather than MIS title information:

Here is the report output. Notice the current segment/directory is flagged in the third MV column.

The report NPR.REP.zcus.is.move has been loaded to our MAGIC NPR report library. So you can just grab it “as is” and use it on your custom hot key menu attached to the NPR application.

Visit our report library at http://www.iatric.com/Information/NPRReportLibrarySearch.aspx to look them up.

You can find additional Report Writing Tips on our website at http://www.iatric.com/Information/NPRTips.aspx, as well as information about our on-site Report Writer Training and Report Writing Services.

To subscribe for email notifications for new Report Writing classes, please follow this link:

http://www.iatric.com/Information/Classes.aspx.

For more information, please contact Karen Roemer at 978.805.3142 or email 
karen.roemer@iatric.com
.

This article originally appeared in the May 2014 issue of Iatric Systems Updates! newsletter.

C/S NPR Tip & DR/SQL Tip

Joe Cocuzzo, Senior Vice President – Report Writing Services

This month we have a quick tip for NPR fans (Client/Server only) and a SQL /DR tip written by Thomas Harlan my DR Technical Team Lead.  If only we had a RD tip, we’d have a trifecta.

C/S NPR Tip: Page 1 of N via MEDITECH utility program

We recently found out that MEDITECH has a utility you can call from the footnote of a report and print a Page X of Y message on your NPR reports. The utility is called:

%Z.rw.page.count(XPOS,YPOS,STRING) where

XPOS = KB article and program say this is X dot position @300DPI, but I think it is COL

YPOS = KB article and program say this is Y dot position, but I find it to be line of page header with first line =0

STRING is an option string to use for message, default is Page XX of YY, but you could send “Pg XX with exciting conclusion on YY” if you wanted a different message.

MEDITECH has a KB article on the utility: 47296. This utility is very handy, although it is not sufficient compensation for other sins of the Print Manager in C/S (no trays, no legal size paper, no print direction, no overlay macros, and so on).

Neither MAGIC nor RD has this feature. Years ago, we showed a way to do this for MAGIC that involved diverting print output to a file and doing a search/replace and then sending the data out to the printer. Back then (before print on VIEW), this worked even when spooling or downloading, but the technique needs updating for MAGIC to cope with spooling’s multiple SAFs (one per page to support VIEW).  Stay tuned for an updated MAGIC report showing how to do Page X of Y including when spooling.

If you just stick a footnote on your report like this:

You’ll get this on your page header:

DR/SQL Revamping ADM Patient Events into Patient Flow

One of the challenges in working with data structures developed for NPR (or M/AT) in the DR is that both NPR and M/AT retrieve data via a loop, while DR expects things to be arranged so they can be fetched in a set. A good example of this is AdmVisitEvents.

AdmVisitEvents has multiple kinds of data embedded within one table, including when the patient is bedded, and then moved from room to room, and then discharged.

Inevitably you’ll need to pull an inpatient location at a point in time and there won’t be a handy field with the Location/Room/Bed where the patient was when something occurred. And when you turn to AdmVisitEvents to find that situational information… you realize that the structure of the table is just not what you want.

The immediate way to get the patient Location is to embed a sub-query into your main SQL SELECT to fetch the patient Room:

SELECT TOP 1 AVE.LocationID

FROM   Livedb.dbo.AdmVisitEvents AVE

WHERE RX.SourceID = AVE.SourceID

AND RX.VisitID = AVE.VisitID

AND  AVE.UndoneSeqID IS NULL

AND  AVE.EffectiveDateTime <= RXA.AdministrationDateTime

AND  AVE.LocationID IS NOT NULL

ORDER BY AVE.EffectiveDateTime DESC, AVE.EventSeqID DESC

(In this snippet, we’re starting from PhaRx (RX) / PhaRxAdministrations RXA) to find the patient unit at the time of a medication administration.)

This answers the immediate question — where was the patient at the time of the administration. But it has issues… first, with a sub-query structured this way, we can only get one (1) field back (LocationID), and second, we have to create a loop in SQL (which SQL does not perform terribly well) with that ORDER BY and TOP 1 structure.

If we wanted to get LocationID, RoomID, and Bed in three separate fields – we would have to do three sub-queries, each returning one field. And as we add more sub-queries, the performance of our report begins to drop off precipitously!

What we want instead is a way to get at the AdmVisitEvents data so that we can JOIN to a table where each patient flow event (a stay in a location/room/bed) is in a single row, so we can do something like:

LEFT JOIN #tFlow ADT ON ( ADT.VisitID = RX.VisitID

AND ADT.SourceID = RX.SourceID

AND RXA.AdministrationDateTime >= ADT.FromDateTime

AND RXA.AdministrationDateTime <= ADT.ThruDateTime )

That is very clean! And now any field we need from #tFlow, we can reference in our query via the ADT alias — and since no sub-queries are involved, it’s fast.

The challenge now is creating this #tFlow table when we need it. And we only want to write that code once, in a reusable way. We need either a stored procedure or a table-valued function to go process AdmVisitEvents for us and create a nice flattened temporary table we can use.

You can download a copy of IatricPatientFlow.sql from iWeb and implement it on your DR server to get a pre-packaged function to do just this. That table-valued function works like so:

  1. First we look for any patient in-house during the date range we are interested in.
  2. Then we take that list of patient visits and fetch all of their Events into a temp table. When we do that, we only look for Events with a Location, that are not un-done, are not ‘FIXED’ records, and there are some kind of Event codes that are excluded.
  3. We sweep that raw list of Events and look for events where there is a change of location or status, and we create yet another temp table that captures the start and end times of the patient being in a specific place.
  4. Finally we create our nice summary table and address some data quirks where the last patient status isn’t correct (unless we correct it… ) and extra discharge rows are discarded.

That process takes a set of Event data that looks like this:

EffectiveDateTime

NewLocationID

NewRoomID

NewBed

NewStatus

OldLocationID

OldRoomID

OldBed

OldStatus

2014-01-20 18:29:00

ED

REG ER

2014-01-21 03:22:00

IC

ICU01

1

ADM IN

ED

REG ER

2014-01-21 21:50:00

M/S

MS24

1

ADM IN

IC

ICU01

1

2014-01-22 05:17:00

IC

ICU01

1

ADM IN

M/S

MS24

1

2014-01-22 14:50:00

M/S

MS16

1

ADM IN

IC

ICU01

1

And makes it look like this:

FromDateTime

ThruDateTime

VisitStatus

LocationID

RoomID

Bed

StayInMinutes

2014-01-20 18:29:00

2014-01-21 03:21:59

REG ER

ED

MS16

1

533

2014-01-21 03:22:00

2014-01-21 21:49:59

ADM IN

IC

ICU01

1

1,108

2014-01-21 21:50:00

2014-01-22 05:16:59

ADM IN

M/S

MS24

1

447

2014-01-22 05:17:00

2014-01-22 14:49:59

ADM IN

IC

ICU01

1

573

2014-01-22 14:50:00

2014-01-26 12:15:00

DIS IN

M/S

MS16

1

5,605

At any given point in time during the stay, we can then immediately find which Location, Room, Bed, and Status. We also get a summary of the time spent in the location.

To use this, in your reporting stored procedure (you are using a stored procedure for each report, aren’t you?) you create a little on-the-fly temp table:

SELECT ADT.*

INTO   #tFlow

FROM   zcus.dbo.IatricPatientFlow( ‘IAT’      — Change to your site SourceID to test

,’IATRIC’  — Change to your facility ID to test

,’2014-01-01′

,’2014-01-31′ ) ADT

;

And then use the JOIN above to bring that data into your query.

Don’t forget to clean up afterwards at the end of your stored procedure!

DROP TABLE #tFlow ;

Performance Note: The patient flow function runs at a test site, with 1.3 million rows in AdmVisitEvents, for a month of visits, in three seconds. It could have a bit better performance after being recast as a stored procedure using temp tables (rather than variables) inside IatricPatientFlow, but then it would require more setup and cleanup code, and could not be nested as deeply as a function.

Extra Credit! You can adapt the code to break out the patient by the day, calculating their stay in each location by day, which then makes calculating Patient Day Equivalent metrics for Nursing Quality Measures very easy.

The SQL code has been uploaded to our report library as IatricPatientFlow.txt.

Visit our report library at http://www.iatric.com/Information/NPRReportLibrarySearch.aspx to look them up.

You can find additional Report Writing Tips on our website at http://www.iatric.com/Information/NPRTips.aspx, as well as information about our on-site Report Writer Training and Report Writing Services.

To subscribe for email notifications for new Report Writing classes, please follow this link:

http://www.iatric.com/Information/Classes.aspx.

For more information, please contact Karen Roemer at 978.805.3142 or email
karen.roemer@iatric.com
.

This article originally appeared in the April 2014 issue of Iatric Systems Updates! newsletter.)

Report Writing Tip: March 2014 – Run Report for Period without prompting for date range (Client/Server or MAGIC)

Joe Cocuzzo, Senior Vice President – Report Writing Services

Run Report for Period without prompting for date range (Client/Server or MAGIC)

It is common to have a report that should be run for a GL period (here we assume this is a calendar month) and the easiest way to program this is to prompt for a date range and let the user figure out how many days in the month. You can add some error checking to reject an ending date that is not the last day of the month with an “FCL” attribute, but another approach would be to prompt them for the GL period and write some code to get the start and end dates based on the period selected.

This month we will show you how to add this programming to your report in a “start” macro and, for extra credit, show how to have the report run automatically for the prior month for the case where it is scheduled to run after month end.

First we set up a report with a date based index, in this example we want to go through BAR transactions in BAR.BCH:

MAGIC:

NPT Tip

Client/Server:

NPT Tip

Next we set up selections to prompt for a period, to restrict to “RCP” transactions, and to limit the report to a date range we will create from the GL period selected.

NPT Tip

We need to write a small “start” macro to use the period that the user has selected to put the start date of the period in /FROM and the end date in /THRU.

When you prompt for field, the value supplied by the user at run time is stored in the temp file and the value is stored in a variable which has a name based on a “b”, “c” or “e” based on the selection operator. “b” for GT and GE. “e” for “LT and LE” and “c” for EQ or IG. When you are prompting for a field that is in some other DPM (not the DPM of the report), the screen translator puts the “b”, “c”, or “e” after the DPM.

So we will have the user selected period in MIS.GL.PERIOD.c.period.

All we need to do is to put that value into MIS.GL.PERIOD.period (the subscript of the GL period dictionary) and then we have the ending date in @MIS.GL.PERIOD.ending.date to put into /FROM.

Then cut the YYYYMMDD off  that value (using $6) and add a “01” and you have the start date to put in /THRU.

NPT Tip

Then add a footnote to call the macro at “START”

The AL = ALGORITHM, the START is where the code gets called and “start” is the name of the macro on this report that is going to be incorporated into the report program by the translator at the “start” point (after printer and some report setup, but before any looping on indexes and record selection).

NPT Tip

You might think this footnote is from the Department of Redundancy Department, but it actually is far better (in my opinion) to name your macros based on where they are called (or included in report translation) than with some invented name about what they do.

Two advantages:

  1. If you look at a report written by someone else, you know that a macro called “detail” is called per record. If you see a macro called “guts” and another called “process” what does that tell you about where the code is executed?
  2. After you write the macro and are about to write the footnote reference, with my method you do not have to remember what you just called the macro, you just have to remember what you always would call it.

Extra Credit:

What if you schedule the report for the 5th day of the next month, allowing five days for any Month End snafus. How can we have the report run from the scheduler and automatically run for the prior month?

There is a scheduler convention to run for a calendar month, including a way to run for the prior calendar month, but I do not think there is a “prior period” convention, so we’d need to created a date range prompt version of the report to get that functionality.  Instead, we can schedule the report with no period selected and modify our macro code to automatically run for the prior month.

In keeping with the MEDITECH practice of creating small annoying differences in the report writer between platforms, if you use the IG operator in MAGIC, the fields remains required, but if you use IG in C/S the field is optional.  So, in MAGIC we need to go into the “Edit Elements” routine and change REQ=1 to REQ=N or REQ=”” to avoid:

NPT Tip

NPT Tip

NPT Tip

If we look at the C/S version of the report we see that the screen translator has already done things this way for us:

NPT Tip

Now we need to modify our “start” macro so we use the MIS.GL.PERIOD.c.period selected if the report was run by the user (and a period was selected) or we use the current date and figure out the prior month if no period is selected. This adds a bit of convenience for the user because if they typically run for the prior month, then they can just leave the period blank and run the report and it will automatically do this.

First we write an IF statement and call two “sub-macros” based on whether we detect a period in the temp field or not. The way to break your macro into sub-macros is to call the submacro using an @ sign followed by some label, which should be all upper case with no spaces. Then these submacros are in the macro code separated by a single blank line and the same label with no leading @. This makes your code more readable and easier to maintain. Also you can use the same sub-macro in multiple places. You can nest these calls and have a macro call a sub-macro which then calls another sub-macro.

Warning – do not create infinite recursion like this:

IF{CONDITION  @SUBMACRO}

SUBMACRO

@DO.STUFF

DO.STUFF

@SUBMACRO

If you do this, your translation will crash. If you submerge the translation, you can run your (MAGIC at least) system out of space.

Here is our new macro (C/S version, but MAGIC is identical) with the code to run for either the selected period or the prior month when no period is selected:

NPT Tip

So if we run the report for no period:

NPT Tip

Or if we run the report for a user-selected period:

NPT Tip

This example report BAR.BCH.zcus.is.eupdate.run.for.period has been uploaded to both our MAGIC and C/S report libraries.

Visit our report library at http://www.iatric.com/Information/NPRReportLibrarySearch.aspx to look them up.

You can find additional Report Writing Tips on our website at http://www.iatric.com/Information/NPRTips.aspx, as well as information about our on-site Report Writer Training and Report Writing Services.

To subscribe for email notifications for new Report Writing classes, please follow this link:

http://www.iatric.com/Information/Classes.aspx.

For more information, please contact Karen Roemer at 978.805.3142 or email
karen.roemer@iatric.com
.

This article originally appeared in the January 2014 issue of Iatric Systems Updates! newsletter.)