SQL Tip — Multi-value Parameters for SSRS

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

Continuing on from last month, when you start building DR + SSRS reports to replace or supplement NPR or RD reporting, you lose some conveniences from direct MEDITECH integration.

One of them is the ability to provide the user with a lookup of values for a parameter field and the ALL keyword. But this functionality can be replaced, with a bit of preparatory work:

  1. We create a simple stored procedure to get the list of values to present to the user.
  2. We create the report detail stored procedure to break apart the list of values sent by the report into a little table, that we then use to control selecting data.
  3. We attach both stored procedures to the SSRS report.
  4. We configure the SSRS report to pass the values the user selects down into the main data stored procedure for the report.

Create the Location List Stored Procedure

See the attached IatricLocationList.sql code (a version is provided for the NPR-sourced dictionary and one for the M/AT-sourced dictionary) for an example of how to create a list of values query that goes against a dictionary table. The core of that stored procedure is:

SELECT  LOC.LocationID

,LOC.[Name]+ ‘ [‘+LOC.LocationID+’]’ AS LocationNameAndID

FROM    livedb.dbo.DMisLocationFacility FAC

JOIN livedb.dbo.DMisLocation LOC ON ( LOC.LocationID = FAC.LocationID AND LOC.SourceID = FAC.SourceID )

WHERE   FAC.FacilityID = @cFacilityID

AND

LOC.Active = ‘Y’

The key points in this code are that:

  1. We only see Active Locations.
  2. We return, in the LocationNameAndID field both the mnemonic and the name. We always do this in case you have two locations with the same name, but different mnemonics, or very similar (and easy to confuse) mnemonics with very different names.
  3. We add (in the attached code, not the example above) an extra line for ALL Locations. We’ll write our data query to respect that keyword coming across in the locations list.

The MAT version of the code also filters out non-clinical departments, since there is a differentiating field in that version of the dictionary.

Create Data Stored Procedure

Now we need a matching stored procedure (IatricDischargesByDateAndLocation.sql) that accepts some criteria, including a list of locations. Something simple will do for this example – Patients discharged from a list of locations in a range of days. The key part of the stored procedure is the WHERE:

WHERE   DIS.DischargeDateTime BETWEEN @dStartDate AND @dEndDate

AND

ADV.FacilityID = @cFacilityID

AND

(

@cLocations = ‘ALL’

OR

ADV.LocationID IN ( SELECT ITM.ITEM FROM IatricSplit(‘|’,@cLocations) ITM ) — This function turns a delimited list into a table

)

Here we see that not only are we selecting by Facility, and Discharge Date, but also by either the @cLocations parameter being ‘ALL’, or that the last ADM.PAT location is in the list of Location mnemonics we passed in to the stored procedure.

Note that in SQL, when an OR is processed, the work stops after the first clause which is True. So if the @cLocations parameter is indeed ‘ALL’, then the SELECT… Split code never executes. Which is good – it’s fast in that scenario – and we don’t do extra, unnecessary work.

Attach Both Stored Procedures to Report

Now that IatricDischargesByDateAndLocation and IatricLocationList exist, we can add them to an existing or template report (zcus_iatric_template.rdl is a good place to start) as DataSets.

First add IatricLocationList:

Datasets > Right-click > Add Dataset

SQL Image

Set the cFacilityID parameter to use the already existing FacilityID parameter from the template:

SQL Image

Then add IatricDischargesByDateAndLocation

Datasets > Right-click > Add Dataset

SQL image

Click OK to save. This automatically creates dStartDate, dEndDate and cLocations parameters. Note that cFacilityID in the new stored procedure does not create a new parameter, but uses the existing one automatically.

Now we need to attach the IatricLocationList dataset to the cLocations parameter.

Right-click on the cLocations parameter in the Parameters group in Report Explorer. Then checkmark “Select Multiple Values.”

SQL image

Switch to the Available Values pane. Select the LocationDs dataset and pick the appropriate fields:

SQL image

Switch to the Default Values pane, and set the default value to “ALL:”

SQL image

Right-click on the IatricDischargesByDateAndLocationDs dataset > Dataset Properties.

Click the Parameters entry on the left, then click the f(x) button beside the @cLocations field to change the expression that controls the value passed to the discharges stored procedure.

Change it from this:

=Parameters.cLocations.Value

To this:

=Join(Parameters.cLocations.Value,”|”)

Note that the pipe (|) delimiter here needs to match the delimiter used in the stored procedure when we break the delimited list apart into a table.

Click OK to save this. Now drag a Table object from the Toolbox onto the report design surface and populate it with some fields.

Preview the report, and you’ll be prompted first for a Facility, then for a list of Locations at that facility, then a starting/ending date range. Select more than one Location by checkmarking them in the list, then View Report.

You should see only discharges from the selected locations, in the date range you entered.

Extra Credit

Go back to the IatricLocationList stored procedure, review the output and suppress Location IDs from the list that are not useful to your customers.

(end)

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.

Read Joe’s blog posts at MEDI-Talk.

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 December 2014 issue of Iatric Systems Updates! newsletter.

Iatric Systems: New Report Designer Course Offering

A new Report Designer class has been scheduled:

Site:    Georgetown Memorial Hospital
Location:    Georgetown, SC 29440
Dates:    2/16/2015-2/19/2015
Class:    Beg/Int
Status:    Open
Instructor:    Philip Sherry

The class has also been added to our website, http://www.iatric.com/Information/Classes.aspx.

SQL Tip — Facility Prompt by User Access

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

One of the challenges of moving from inside MEDITECH-integrated reporting (NPR or RD) outside into the realm of DR (SQL + SQL Server Reporting Services or Crystal Reports)- based reporting is that you lose the benefits of being integrated.

A key capability lost is the ability to restrict a user to a specific facility’s set of data when reporting. Whereas in NPR you might be prompted, for example, to pick a BAR or AD database, which was facility-specific, in the world of the DR you need to be mindful of multiple facilities and filter accordingly.

Which brings us to the question: which facility (or facilities) does the user running this SSRS report have access to? And then – how do we get the report to know which facility to look at? And how does the report know who we are?

Let’s start with which user is running the report first. Inside SSRS, you can declare a parameter (cUserID) and set that parameter to an expression (calculated value):

=User!UserID

Which gets us the currently logged-in Active Directory user on the workstation:

IATRIC\thomas.harlan

When you are setting up this SSRS parameter, you want to make it hidden:

SQL Tip

And set the Available Values to code to get the current AD user. Click on each of the Expression buttons:

SQL Tip

And set the code in each expression to:

=StrReverse(Left(StrReverse(User!UserID),InStr(StrReverse(User!UserID),”\”)-1))

Then do the same on the Default Values screen:

SQL Tip

We do this because the format of the AD user ID is not what is stored in MEDITECH. The AD username returned by SSRS looks like:

IATRIC\thomas.harlan

But all we have in MEDITECH is the part after the “\” character. So the expression code returns us everything after the “\” to the end of the string:

thomas.harlan

With that shortened value, we can now call a stored procedure from the next report parameter (cFacilityID) to get all of the facilities where that AD user has access.

In that stored procedure, we need to look at a variety of different tables, to see if (A) we find the ID, and (B) which facility they are attached to:

Livefdb.dbo.MisPerson_JobFacilities
Livefdb.dbo.MisPerson_Jobs
Livefdb.dbo.MisPerson_ProviderFacilityMain
Livendb.dbo.DMisUserAdmMriFacility

Attached to this tip is a stored procedure (IatricFacilityIDFromUserID) which will take that AD user ID as a parameter, then check this constellation of tables and return the list of facility ID(s), SourceID(s) and User ID/Name (for verification purposes).

After you’ve compiled that sp in your DR, you can create a new DataSet in your report for this new stored procedure:

SQL Tip

Which will have three fields (we only need two, really, but the UserNameAndID is useful to making sure you’ve gotten it working properly):

SQL Tip

…and feed it the cUserID value as a parameter:

SQL Tip

Note that the Parameter Value field has the parameter name we created earlier
(cUserID) wrapped in brackets with a leading @. This chains our retrieved cUserID to the stored procedure to get a list of facilities. Now we connect that to the cFacilityID parameter:

SQL Tip

We connect to the same stored procedure on the Default Values screen (which, if you have more than one facility assigned to you, will only use the first one in the list):

SQL Tip

Now we can carry on with the rest of the report…

Bonus!

Attached to this tip is a template report which implements this code. It’s an excellent idea to start from a reusable template, so you don’t have to go through all of the setup for standard features over and over again…

The stored procedure to get the list of MIS Locations from the Facility ID is also attached, as it’s used in the template report.

Extra Credit

If you’re not at an MT6 site, you don’t need all of the code in the stored procedure. Just the last section where it looks at DMisUserAdmMriFacility will get you the clinical users. The Business/Financial users you’ll need to get from the Client/Server or MEDITECH versions of the Job Table(s).

(end)

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 November 2014 issue of Iatric Systems Updates! newsletter.

Stop at Query if Patient is “on Coumadin” (MAGIC Only)

Joe Cocuzzo, Senior Vice President – Report Writing Services

Someone from Mid Columbia Medical Center in Oregon asked for a way to stop at a query only if a patient was “on coumadin.” I am going to interpret that to mean any active PHA order for Warfarin (administration of the drug not checked, although you could add this if needed).

Since it is tip time, I thought I would show how you can write a generic macro that will go from any application to Pharmacy and check for an active order for a particular medication and return a flag if such an order is found. We will use the MEDITECH MAGIC “Z.link” utility to “open” PHA.

First we need to write a fake “holder” report in “Z” or “MIS” so our programming can be available from any segment. If we wrote our report in NUR, we would only be safe using the utility from NUR CDSs.

If you have ever noticed the “copying files to:” message you get when you file and translate a report in MIS or Z, that is an indication that the translator is making the object code available on every segment and therefore from every application.

NPR Tip

NPR Tip

Next we write a macro called “check”

NPR Tip

We can make our code modular by breaking the macro into “submacros.” By ending the code with a semi colon, we make it a program. When you call a program, you use %, then the name of the program, then one or more arguments in parenthesis, for example:

%Z.name.inversion(“SMITH,JOE”)

In our example, the program is %Z.zcus.is.warfarin.M.check and we are passing in the patient’s admission urn.

The program then returns a value from the last expression in the code, which in our example is the value of the variable OK. We put a 1 into OK if we find an ACTIVE (status = AC) order for Warfarin for the patient we are checking.

Inside the program the first argument (in this example it is the only argument) becomes the variable A.

NPR Tip

We test for A before having the program do anything. Since the CDS filer will check the code by running it with no A argument, we can avoid bogus CDS “syntax error – file as draft” issues by testing for A and skipping all the code in the program when A is nil.

MEDITECH MAGIC has a utility program called %Z.link that will open any prefix to any data or dictionary file.

The general way you use the program is:

  • Save the prefixes you plan to use
  • Call the Z.link program to open the prefix or prefixes to the data or dictionary file of the other application database. Our example calls it once to open PHA data, then again to open PHA dictionaries.
  • Get the data you need – typically by looping on some index and then checking or getting some data.
  • Restore the prefixes the way you started.

When your are working with prefixes “yourself,” it is a very good idea to test your code in the TEST system first, before using it in the LIVE directory. Bad programming can crash and leave records locked when your macro runs from a CDS.

STEP ONE – “OPEN PHA”

First we need to “open PHA.” When we are in Nursing, prefixes are “open to” NUR, ADM, and MIS, but not PHA.

NPR Tip

Z.link goes over to the PHA segment and directory and opens the prefixes and then comes back to the segment it started from. This means that you can then get to PHA fields and dictionaries, but you need to be careful not to try to use any PHA fields that call PHA programs or you will likely crash. This is because the PHA programs are only available in the PHA segment and directory (and the NPR master segment) and you might call the Z program from some other segment.

PHA.combo.dose.and.unit is an example of a field that calls some PHA program, see the %PHA.RX program call in the VAL column that is your tip off:

NPR Tip

STEP TWO “CHECK ORDERS”

To check orders, we need to make sure that all the PHA fields we use translate using the one prefix (:) that we opened to the PHA data file with the Z.link program. Looking at the date definition we can see that indexes translate with :, which is fine, but data fields use ? which won’t work. The @Chg.prefix translator macro will fix this.

NPR Tip

This code uses an index by patient ADM urn, status, and order type, so we can put the patient’s admission urn into PHA.RX.patient, and “AC” (that is the status of PHA active orders) into PHA.RX.status and then loop through just the active orders for just the one patient and then check the generic of each @med to see if we have any active orders with Warfarin. Just change the generic mnemonic to the value used at your hospital.

I try very hard to avoid writing any reports in PHA with hardcoded drug mnemonics, as these change quite often and your report will “break” because you won’t add new ones. This is why I opened the PHA dictionary file as well, so I could go from the @med field on each order over to the generic to select based on a value which changes far less often. There is usually just one value, although nothing prevents a site from creating multiple different generic mnemonics for what is actually the same medication.

If the medication you are looking for might be a compound, split, or IV with additives, the @med field based selection would fail. Since Warfarin is not that kind of medication, we can just check @med’s.generic and be fine.

STEP THREE “CLOSE PHA” (put everything back or else!)

After we check PHA, we need to be very careful to restore : and &, otherwise we will likely crash back in NUR. The “CLOSE.PHA” macro does this as follows:

NPR Tip

We used the C(:S) “close and stack” syntax to save the value of our prefixes to “the stack”.
If we use C(:U) we restore the prefixes to their previous state (typically open to the previous file).
It may seem funny to use a “Close” command and have a prefix be Open, but these two comands do just the same thing C(:U) O(:U). The advantage of using C is that the syntax checker does not complain.

To use our utility macro from an IFE, we attach it like this:

NPR Tip

If we select a patient with an Active PHA order for Warfarin, the cursor will stop at the “IAT.WARED” field, because our utility program returns a value if such an order is found.

NPR Tip

A copy of the Z report has been added to our MAGIC report library. Z.zcus.is.warfarin
http://www.iatric.com/Information/NPRReportLibrarySearch.aspx

Make sure you change the PHA database in the “check” macro to match YOUR HOSPITAL, and the generic mnemonic to match what you use. Otherwise the utility is generic and will work at any MAGIC site.

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 November 2014 issue of Iatric Systems Updates! newsletter.

SQL Tip — Check Tables for Data

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

One of the challenges of the Data Repository (DR) is that we often need sets of tables populated with data and, despite everyone’s best efforts, when the rubber hits the road – one of them is empty. The MT task to get it turned on missed it, or you were doing it yourself with a self-service Special IL (if you’re on C/S or MAGIC), and you skipped one yourself.

This is painful to find when you’re deep in the project. There should be a good way to check in advance which tables you need are populated or not. And you can certainly do that by doing a SELECT * FROM… per table. But how about a way to check a bunch of tables, in a batch operation, to see if they have data in them – and how much – in one go?

To this useful end, we’re going to use the capability of T-SQL to execute SQL commands created by other SQL commands in code. This is generally termed “dynamic SQL” and revolves around using the sp_executesql command. The attached code shows a simple and (hopefully) straightforward example of executing a stored procedure like this:

EXEC zcus.dbo.IatricDynamicTableVerify ‘Livendb.dbo.DMisUserStatistics|Livefdb.dbo.MisAuditNprPatAccess_Main|Livefdb.dbo.HimRecAuditNpr_Main|Live

fdb.dbo.HimRecAudit_Main’ ;

To check those four tables to see if they have data, how much in total, the first and last time they were updated and some very simple statistics about them. You’ll get result roughly like so:

TableID

DataRowCount

FirstUpdated

LastUpdated

DataMonths

MonthlyVolume

Comment

Livefdb.dbo.HimRecAudit_Main

35,056,210

2012-11-02 21:02:09

2014-10-08 22:17:45

23

1,524,183

Livefdb.dbo.HimRecAuditNpr_Main

15,223,878

2012-11-02 21:08:39

2014-10-08 22:07:40

23

661,907

Livefdb.dbo.MisAuditNprPatAccess_Main

9,575,012

2013-12-17 10:19:53

2014-10-08 22:05:19

10

957,501

Livendb.dbo.DMisUserStatistics

5,039,040

2012-11-02 14:53:00

2014-10-08 22:04:00

23

219,088

When you dig into the code you’ll find that we create some tables to manage the “buckets” we want to check – that we use the IatricSplit() function (also attached to this tip) to break up the pipe(|)-delimited list of table names into a little table of its own – and then we loop (using WHILE instead of cursor, because cursors are evil…) through the list of table names passed in.

Within each loop we compose a SELECT statement in a text string, bringing in the name of the table to check, and then we EXEC the “created” statement to query the table for how many rows of data it has, when it was first and last updated and the average monthly volume of rows added to the table.

   SET @cSQL = ‘SELECT MAX(‘+CHAR(39)+@cTableID+CHAR(39)+’) AS TableID’+

‘,COUNT(*) AS DataRowCount’+

‘,MIN(TBL.RowUpdateDateTime) AS FirstUpdated ‘+

‘,MAX(TBL.RowUpdateDateTime) AS LastUpdated ‘+

‘FROM ‘+@cTableID+’ TBL ;’

Putting this text string together is pretty simple, save perhaps for the use of CHAR(39) to embed single quotes inside of a single-quoted string. We do that because sometimes you do need to embed quotes and the CHAR() function is a straightforward way to do that.

Note also the use of the WHILE/@nIndex structure – this is a good, lightweight way to loop through a table of “things to do” while avoiding the use of a cursor.

Dynamic SQL can be used for lots of things; from this framework however you can expand the code to do batch operations on lists of tables which can be tremendously useful.

Extra Credit

Make a faster version that just gets the DataRowCount and use it to check that all of the tables you need for your Meaningful Use Stage 2 queries are populated. This gives you a counter-check to the MT process in mt_pr_2014_TableHealthCheck.

More Extra Credit

Review the attached code for the IatricSplit stored procedure as well. It may look a little strange, but it will parse out a very long string of delimited-by-something codes into a table of with each value in the ITEM field. So that this…

SELECT ITEMS.* FROM zcus.dbo.IatricSplit( ‘|’,’2WA|2N|2S’) ITEMS

Gets you:

ITEM

2WA

2N

2S

When used with Microsoft Reporting Services, you can easily build a delimited list of items picked in a parameter in the report, pass the string into the stored procedure and then break the list apart again into a table. And then… use that table in a JOIN or WHEN to limit results.

(end)

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 October 2014 issue of Iatric Systems Updates! newsletter.

NPR Tip: Getting Most Recent BAR Comment for Account (MAGIC, Client/Server, or 6.0)

Joe Cocuzzo, Senior Vice President – Report Writing Services

Getting Most Recent BAR Comment for Account (MAGIC, Client/Server, or 6.0)

A BAR analyst from a C/S hospital posted a request to the “Meditech-L” mailing list, asking for help in including the last billing comment and last billing comment date on a BAR.PAT report.

MEDITECH had provided code to loop back through the transactions and get the last “c” type transaction and then get the date, but this would stop on system comments also. MEDITECH also tried to provide code to get the comment itself, but the logic provided was not quite correct.

There are two issues to overcome for this report:

  1. You need to loop backward to the most recent “c” type transaction, but also skip any system transactions.
  2. You need to get the transaction text from a child segment under the transaction, as MEDITECH stores billing comments in multiple lines.

Step one is to take a look in a billing system and see what system comments look like, so we can figure out which transactions to exclude from our logic that will “back up” to the most recent comment made by a human, vs. the comments from automatic stuff in B/AR.

Even those of us that write NPR reports all day don’t exactly recall what system vs. user comments look like, so we write a quick report at the transaction text level to see what things look like; we need to write our quick report here:

NPR Tip

NPR Tip

Our quick report just gets some accounts and shows us all the “c” type transactions:

NPR Tip

NPR Tip

NPR Tip

So, how do we write some computed fields or attributes or macro code to get the last “real” comment date, and the comment text from that comment?

First, the date, we go “backward” (@Prev goes from largest to smallest transaction urn) until we hit a “c” type transaction where the user is anybody but BKG DAEMON.  Hang on to the txn urn in the variable FND, which also serves as a flag to stop our backward looping.

[MAGIC Users Please Note:

In MAGIC, it appears that comments from automatic processes have the user that happened to start the BAR background job, so you’d need to do some more sophisticated screening of system stuff than you need to do in C/S.  Possibly looking at @txn.comment for “NSD” and other automatic comments would work.]

Note that at the end of this loop, FND holds the @txn.urn we want and txn.urn is one less than that transaction, because the @Prev happens one more time before we check FND and stop looping because it has a value (i.e. NOT FND no longer true).

NPR Tip

Now we need to write some code to show all the transaction text for the comment. We could build an MV array and load it with the transaction text, but then you either have to use a utility to keep the field number subscript dynamic and unbreakable (highly recommended) or hardcode a field number and take the risk that an edit could break your report. Instead, lets write one computed field for the first text line, and wrap a DO loop around a second computed field to handle the rest of the text lines.

The first comment line is a simple computed field that just uses the txn.urn from FND and has a hardcoded 1 to print the first line

NPR Tip

For the rest of the lines, we wrap a loop around a second “D” line as follows:

NPR Tip

The field to print line 2 through N is:

NPR Tip

So our report written at the “top” (main) level is now smart enough to go through the transactions in the child segment and find the last comment, then go through the text in the grandchild segment and print the lines out.

I have added a horizontal line between accounts to make output clearer:

NPR Tip

A sample report, in MAGIC and C/S BAR.PAT.zcus.is.last.comment.and.text has been placed in our report library.

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 October 2014 issue of Iatric Systems Updates! newsletter.

Epic Reporting Tools for MEDITECH NPR Developers

Learn about Epic data and reporting tools. Whether you simply extract data outbound to Epic, your hospital is planning to migrate to an Epic platform, or you’ve already switched from MEDITECH to Epic, this informative webcast will benefit you.

Attend this webcast to learn about:

• All of the data layers in the Epic environment
• The reporting tools available in each layer
• What kind of reporting output each tool is intended to address / good at handling
• A triage model for handling data requests, and how to map requests to the appropriate reporting tool
• Recommended business intelligence model for Epic sites (beyond Epic’s initial recommendations)

Register for the October 28 2PM ET webcast.

SQL Tip — Making Your Own Legos

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

Today we play with Lego, or rather we make our own Lego. No, not this way, but in SQL. One of the tremendous things about SQL and the Data Repository is that you can roll your own building blocks and use them over and over again, easily, saving yourself a lot of work down the road — and also making your reports more consistent.

Our goal is to make a SQL function that returns a “stacked” data item — one that reflects the hierarchy of the patient’s progress through the facility, to discharge, and then abstracting. In this case we’re looking at the Primary Diagnosis of the visit.

Simply put, what we know about the patient primary diagnosis (or complaint) advances through the following progression:

Visit Stage

Data Item

DR Field

Quality

ER Presentation

Complaint

EdmAcct_Main.StatedComplaint

Poor

Admitted

Reason for Visit

AdmVisits.ReasonForVisit

Better

Initial Abstraction

Reason for Visit

AbstractData.ReasonForVisit

Good

Final Coding

Primary Diagnosis

AbsDrgDiagnoses.DiagnosisName

Best

Our little chart makes it clear our understanding of the patient changes over time, and is refined as they progress through the care cycle. On our reports, we want to reflect that, and reflect it easily, in a consistent way.

Thus, a custom function! We’ll call it IatricPrimaryAdmitDiagnosis (which you will find attached to this tip). We call it from a query showing patient visits where we have access to the SourceID and VisitID fields, like so:

SELECT  ADV.SourceID
,ADV.VisitID
,ADV.ServiceDateTime
,AdmitDX = zcus.dbo.IatricPrimaryAdmitDiagnosis( ADV.SourceID,ADV.VisitID )
FROM    Livendb.dbo.AdmVisits ADV
WHERE   ADV.ServiceDateTime BETWEEN GETDATE()-30 AND GETDATE()
;

Executing that code gets us a wide variety of results… let’s look at the ones from today:

2014-09-05 21:33:00

TOOTH PAIN

2014-09-05 21:35:00

APNEA

2014-09-05 21:40:00

COUGH,RUNNY NOSE

2014-09-05 21:45:00

EXTREMITY PROBLEM

Some of these patients are in the ED right now – and no one has had a chance to do more than capture the patient’s impression of their issue. If we’re lucky, a triage nurse is taking down this documentation. Now if we look at the visits from 30 days back — patients who have likely been discharged, abstracted, and final coded:

2014-08-06 22:51:00

FX DISTAL RADIUS NEC-CL [813.42]

2014-08-06 23:36:00

TOXIC EFFECT VENOM [989.5]

2014-08-07 00:46:00

ACUTE CHOLECYSTITIS

2014-08-07 03:31:00

FX FEMUR, MIDCERVIC-CLOS [820.02]

Now we have something more solid! Real ICD-9 diagnosis codes and names. Except for one poor soul who is still an Inpatient… When you review the code for the function, you’ll see it uses a COALESCE() (a standard SQL function that returns the first non-NULL value it finds in the list of parameters) to represent this hierarchy:

COALESCE( ADX.DiagnosisName+’ [‘+ADX.Diagnosis+’]’ — Final Coded diagnosis #1
,ASD.ReasonForVisit  — Abstracted Reason for visit
,ADV.ReasonForVisit  — ADM.PAT.reason.for.visit (cleaned up from EDM complaint)
,EAM.StatedComplaint  — EDM Stated Complaint (pretty rough)
) AS AdmitDiagnosis

In this structure, we’ll get back the abstracted diagnosis first (if it exists), then the abstracted reason for visit (often filled in while the patient is still in-house), then the admitting reason for visit and finally the ED stated complaint if nothing else exists.

Warning!

This is a super-useful function for displaying reason for visit, on a report. But you should NOT try and use it as part of a WHERE clause to look for kinds of reasons of visit, or for primary diagnoses. Performance, in that scenario, will be quite poor.

Extra Credit

There are other data fields which might, depending on how your enterprise handles pre-abstracting documentation, have the admitting diagnosis as well:

AdmittingData.AdmitDiagnosis
AdmittingData.MedNecDiagnosis
AdmDischarge.ErDiagnosis

If these fields are populated in your MT environment – add them to your copy of the function!

(end)

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 September 2014 issue of Iatric Systems Updates! newsletter.

Writing files from NPR report to a shared folder from a scheduled or submerged report (MAGIC Only)

Joe Cocuzzo, Senior Vice President – Report Writing Services

The only “officially supported” mechanism to write a file to a non-MEDITECH machine is to use FTP. This means you need to set up an FTP as a service on the destination machine, which typically involves installing additional software.

It would be much more convenient if you could write files to any Windows PC in the network using a regular windows user ID and password.

It turns out you can. Microsoft has a protocol called “Server Message Block” that allows you to read and write files, created folders, and read directories and MEDITECH has a set of Z programs and OS utilities that support this protocol.

This month I will show you how to use Z.smb.put.raf to write a file from an NPR report to a share using a regular windows user ID and password.

First we need to have a place to keep our Windows user ID and password information.  The FTP remote host dictionary is a good place, and at most sites this will hide the password information a bit better than just putting it into our NPR report source code.

NPR Tip

Next, we write an NPR report where we do all output in a macro, which builds lines of data in a memory based RAF. This is similar to the way you can build a MOX message in a temporary RAF and then use MIS.OA.MSG.auto to send a MOX message.

Set up Page 1 as you would a report with no picture and put a program call to a macro in the title:

NPR Tip

The program call in the title is so you can set a flag to suppress the PRINT ON prompt and any report messages. The code in the “setup” macro looks like this:

NPR Tip

(It probably would be okay to set the /Z.SCHED.LOG and /R.NO.RPT.MSGS flag multiple times, but just in case we set the report up to just do it 1x. The title program is executed when the report first displays a selection screen and then as it prints each new page.)

The report has NO PICTURE, just a footnote to call a macro (“detail”) for each record. Our demo report just has one selection, where you identify the MIS.FTP remote host where the password and host information is kept:

NPR Tip

NPR Tip

 

In the “detail” macro we send data to a random access structure in memory:

/STUFF[n] = data (including CR/LF)

Note that this technique of writing the data to slash (memory based temp file) should not be used for extremely large records. In our case, each file is only two lines long. If you need to send very large files this way, you would write to an RAF as a disk file in your own directory.

The arguments to the Z.smb.put.raf program are:
A = HOST (ip or server name of the host to send to)
B = USER (windows user id of user with rights to the share)
C = PW (windows password of user in argument B)
D = Share name
E = Path and file name
F = “name of” the raf structure with the data to put in the file
(^/STUFF^F would put the “name of” the RAF into F)

Here is the “detail” macro showing how the lines of data are put into /STUFF[line number] and how the file is transferred to the shared folder.

NPR Tip

IMPORTANT NOTE: Make sure you enable the SHARE for your user on the server. Otherwise you will get an error when MEDITECH attempts to write the file to the share.

The report makes files very fast, far faster than FTP, especially if you have a lot of small files to create:

NPR Tip

A sample MAGIC report has been uploaded to our report library ADM.PAT.zcus.is.smb.transfer

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 September 2014 issue of Iatric Systems Updates! newsletter.

Produce MEDITECH Data Repository (DR) reports quickly, today

At a recent DR Implementation Best Practices educational webcast, we heard loud and clear that many hospitals still struggle with DR report writing. But you can produce MEDITECH DR reports quickly, today. Below are three ways to accomplish it.

First, let us take some — or all — of the report writing burden off of your staff, freeing them up for other important IT initiatives:

Data Repository Report Writing — Our DR report writers can create the necessary queries to retrieve the data you need, and they work with a variety of commercial report writing tools to produce the results you want.

You can also improve your staff’s DR report writing skills. These two resources can help them produce DR reports more easily:

DR Resource Center™ demonstration webcast — During this webcast, attendees will see a variety of SQL programming code, SQL Server® Reporting Services (SSRS) reports and other tools that can help them write accurate, efficient DR reports in less time. Click on one of the dates below to register to attend:

Tuesday, September 9, 2014, at 2:00 p.m. ET

Data Repository Report Training — Our DR report trainers provide custom, on-site training that is hands-on, using your own DR database to maximize the application of what your staff learn.