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.

NPR Tip (MAGIC only) – Printing Radiology ($T RAD) Text and how to use ECB/ECE loop in a report

Joe Cocuzzo, Senior Vice President – Report Writing Services

In October 2002, we published a tip on how to print Radiology report text in an NPR report. At the time, there was no field or utility to do this (or possibly I just liked to do things the hard way), so our code showed how to open to the “saf” (sequential access file) or “raf” (random access file) containing the report text and send the text out to the printer or output file.

There is a utility program that will print the entire report body for you, and you can just put a program call in an LC or ECB and call this:

ECB=%RADRW.REPORT.print.report.lines(report),

Or for Addenda

ECB=%RADRW.ADDENDA.print.addendum.lines(addendum)

There can be a problem however, if you need to send the lines of text out with some data at the beginning of each line, or without the CR/LF at the end. It turns out if you pass a “B” argument to either of these utilities, they build the report text in /RPT.TEXT[report]T[n] = line of text and the addenda text in /ADD.TEXT[addendum]T[n] = line of addendum text.

So if we call %RADRW.REPORT.print.report.lines(report,1) the utility creates /RPT.TEXT[report]T[1] = first line of text
/RPT.TEXT[report]T[2] = second line of text

This month I will show you a report that selects reports and addenda for a signed date range, and then prints out the report and addenda text with some leading data (we will show line #) at the start of each line of text. We will use the two utilities to create the temp structures holding the report and addendum text and an ECB/ECE loop to print out the lines with a line counter at the beginning of each line.

The report example is a bit more complex because it also allows you to export a report if it was signed during the selected date range, or if one of its addenda was signed during the date range. This is useful for a billing service export because they typically want to get a report “again” if an addenda is attached and signed.

Step 1 is to build a report in RADRW.REPORT so it loops through the “radiology.report.exams” segment.  Note that we do not use an index.  Because we loop on the indexes in our own “start” macro (called at the beginning of the report) and make a list of the report urns in a slash structure, when we do an LI selection, the report goes directly to the list of reports we want and an index would only make the report slower (much slower).

NPR Image

Then on page two we prompt for a date range with an “IG” (ignore) operator and do an LI selection on /RPT.  That is a structure we will build in a “start” macro to make a list of all the reports signed in the date range and all the reports which had an addenda signed in the date range.

NPR Image

NPR Image

Next we call the two utilities in a “detail” macro (per each report and exam) to build /RPT.TEXT and /ADD.TEXT

NPR Image

Then in the picture, we “wrap a loop” around a line with an ECB and ECE and loop thru /RPT.TEXT, get the line of text out into the variable “DATA” and then put a line counter at the beginning of each line in five spaces left justified.

A computed field prints the line. We set TRUNC =NO so that even if the line exceeds the width we have defined, the entire value prints.

NPR Image

The output of the report is shown below:

NPR Image

A copy of the report RADRW.REPORT.zcus.is.report.addendum.export has been loaded to our MAGIC 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 August 2014 issue of Iatric Systems Updates! newsletter.

SQL Tip – Parsing Patient and Provider Names

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

Tis but patient name tis my enemy;
Thou art five parts, though not in MEDITECH.
What is MEDITECH? It is nor first, nor last,
nor suffix, nor prefix, nor any other part
belonging to a name.
Oh! Be some other structure!
— Jules Capulet, Report Developer, Verona University Healthcare

If you are lucky enough to be up on MEDITECH 6, you can find the patient name broken out into discrete components in this table:

Livefdb.dbo.HimRec_Main

Where the HIM module has helpfully separated out NameLast, NameFirst, NameMiddle, NamePrefix and NameSuffix. But if you are dealing with a provider name – your stars are crossed there – or if you’re on MAGIC or Client/Server – you also have to break down the formatted string into its constituent parts.

In very general terms, that is:

LastName, FirstName MiddleInitial

If your site has been scrupulous about following the convention for patient names. For provider names, however, all bets are off… there are often suffixes or sets of suffixes; additional slashes or semi-colons and additional information at either the front of the provider name or at the end.

In SQL there is no built-in “piece of” function, as you would find in NPR with the # operator. If we want to break up a string into pieces, we need to use either an assortment of functions:

SELECT LEFT(ADV.[Name],CHARINDEX(‘,’,ADV.[Name])-1) AS LastName
RIGHT(ADV.Name,CHARINDEX(‘,’,REVERSE(ADV.Name))-1) AS FirstName
FROM Livendb.dbo.AdmVisits ADV

But this example is too simple… our FirstName field also includes the Middle Initial, if any. Or anything trailing the middle initial.

Or we use a custom function like IatricPiece() to pick out the parts of the string that we want:

SELECT  zcus.dbo.IatricPiece(‘,’,1,’BERMAN,JOEL F MD’) AS LastName
,zcus.dbo.IatricPiece(‘ ‘,1,zcus.dbo.IatricPiece(‘,’,2,’BERMAN,JOEL F MD’)) AS FirstName
,zcus.dbo.IatricPiece(‘ ‘,2,’BERMAN,JOEL F MD’) AS MiddleName ;

This gets us:

LastName

FirstName

MiddleName

BERMAN

JOEL

F

Which works perfectly for names in the standard format. You could also do the same with a more elaborate nested use of CHARINDEX. But creating the IatricPiece() function in your database gives you an endlessly useful tool with little or no overhead.

But what about those thrice-damned Montagues? I mean, provider names? Ones that have wound up looking like this (due to dictionary maintenance):

** DO NOT USE ** SMITH,JOHN P JR NP/JONES

When you see that, sir, I dare say you will bite your thumb, sir! At me, even.

(Note! If you are on MT6, check your DMisProvider table… you might have LastName, FirstName, MiddleInitial already broken out for you!)

To tackle this we need a function of more parts – and more sophistication than the simple IatricPiece(). We need Jim McGrath’s IatricParseProviderName() which breaks a more complicated string down into a one-row table containing the following fields:

full_name
last_name
middle_name
prefix
suffix
degree
mail_name
associated_doctor
other

Which looks like this:

SELECT * FROM zcus.dbo.IatricParseProviderName(‘** DO NOT USE ** SMITH,JOHN P JR NP/JONES’)

And produces:

full_name

last_name

first_name

middle_name

prefix

suffix

degree

mail_name

associated_doctor

other

SMITH,JOHN P JR NP/JONES

SMITH

JOHN

P

JR

JOHN P SMITH JR

JONES

The code for IatricParseProviderName() is attached to the tip for your edification, along with supporting code for IatricStripString() and IatricStripPuncuation() which help out along the way.

Once you have that in your zcus database, you can call the function via an OUTER APPLY (which acts like a SQL join, but is used to invoke a table-valued function once per row of your result set) and then use the name components in your SELECT field list:

SELECT DMP.ProviderID
,DMP.[Name]   AS ProviderName
,DMP.Active   AS ProviderActiveYN
,PPN.*
FROM Livendb.dbo.DMisProvider DMP
OUTER APPLY zcus.dbo.IatricParseProviderName(DMP.[Name]) AS PPN
;

But note that this only works if the code matches the name pattern in the dictionary…

(end)

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

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

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.