SQL Tip – Managing Diagnosis Codes

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

A common challenge in reporting (or extracting data, in this case) that we see over and over again is the need to transform one data structure into another. The best arrangement of data for the programmer writing the on-line application is not necessarily the best arrangement for the person writing a report or an extract.

The diagnosis (or procedure) codes in ABS are a perfect example of this. For the programmer, you want a list of codes (and attendant extra fields) per abstracted case, and that looks like this:

sql image

Where we see that the list of diagnosis codes is stored, and sorted, in dx.seq.no order and that is a number field that can take up to 99 codes. If we turn around and look at the matching DR table (AbsDrgDiagnoses) with a query like this:

SELECT  ADX.AbstractID                                AS AbstractID

,ADX.DiagnosisSeqID                           AS DiagnosisSeqID

,COALESCE(ADX.Diagnosis,'(?)’)            AS Diagnosis

,COALESCE(ADI.[Name],'(No Diagnosis Entered)’) AS DiagnosisName

FROM    AbsDrgDiagnoses ADX

LEFT JOIN DAbsDiagnoses ADI ON ( ADI.DiagnosisCodeID = ADX.Diagnosis

AND ADI.SourceID    = ADX.SourceID )

We see…

AbstractID

Seq No

Diagnosis

Diagnosis Name
100002

1

276.1

HYPOSMOLALITY
100002

2

331.9

CEREB DEGENERATION NOS
100002

7

442.83

SPLENIC ARTERY ANEURYSM
100002

5

562.10

DIVERTICULOSIS COLON (W/O MENT OF HEMORRHAGE)
100002

4

573.8

LIVER DISORDERS NEC
100002

3

793.0

NOSP (ABN) FINDINGS ON RADIOLOGICAL &

OTH EXAM SKULL & HEAD

100002

6

737.30

IDIOPATHIC SCOLIOSIS

Which looks great… until the report writer building an extract gets a request to show the data like this instead

dx1|dx2|dx3|dx4 … |dx24

In this view of the data we want one row per abstracted case, with the diagnosis codes “flattened” up into that single row, for up to 24 codes. Which is perfectly possible in SQL, like this:

SELECT  ASD.AbstractID  AS AbstractID

,DX1.Diagnosis  AS Dx1

,DX2.Diagnosis  AS Dx2

,DX3.Diagnosis  AS Dx3

,DX4.Diagnosis  AS Dx4

FROM    AbstractData ASD

LEFT JOIN AbsDrgDiagnoses DX1 ON ( DX1.AbstractID = ASD.AbstractID

AND DX1.SourceID = ASD.SourceID

AND DX1.DiagnosisSeqID = 1 )

LEFT JOIN AbsDrgDiagnoses DX2 ON ( DX2.AbstractID = ASD.AbstractID

AND DX2.SourceID = ASD.SourceID

AND DX2.DiagnosisSeqID = 2 )

LEFT JOIN AbsDrgDiagnoses DX3 ON ( DX3.AbstractID = ASD.AbstractID

AND DX3.SourceID = ASD.SourceID

AND DX3.DiagnosisSeqID = 3 )

LEFT JOIN AbsDrgDiagnoses DX4 ON ( DX4.AbstractID = ASD.AbstractID

AND DX4.SourceID = ASD.SourceID

AND DX4.DiagnosisSeqID = 4 )

Which produces what we want:

AbstractID

Dx1

Dx2

Dx3

Dx4

100002

276.1

331.9

793.0

573.8

We get one row per abstracted case, and the DX codes folded up into discrete columns, because we JOIN’ed in the AbsDrgDiagnoses table four times… and if we wanted 24 discrete columns, we would have to join that table in 24 times! And if we needed all possible DX codes – up to that 99 we mentioned before – that is 99 luftballoons – oh, sorry, 99 JOIN’s that we have to write out.

When you hit this problem you start thinking about … a CURSOR, or a TABLE-VALUED FUNCTION, or a PIVOT … but the easiest and fastest way to address this problem is to build a VIEW.

In the past we’ve talked about code reuse and LEGO® building blocks. This is the perfect place to build a VIEW that flattens down all 99 possible diagnosis codes into one line per abstracted case. “But,” you say… “that is a lot of work!”

Yes… but you only have to do it once. And even better, attached to this tip is a VIEW that does it for you! Well, actually, it brings in the first 50 diagnosis codes. Which should cover you for 99% of all cases. But if you need the other 49… they are easy to add.

So you can just install that view in your zcus database instance (after updating the database references for your DR databases) and then you can bring in the data from the VIEW all day long, like this:

SELECT  ADF.Dx01Code,ADF.Dx02Code,ADF.Dx03Code,ADF.Dx04Code,ADF.Dx05Code,ADF.Dx06Code

FROM    dbo.IatricAbsDxCodesFlattened ADF

Which gets you:

Dx 01 Code

Dx 02 Code

Dx 03 Code

Dx 04 Code

Dx 05 Code

Dx 06 Code

V57.89

555.9

276.1

263.9

599.0

799.3

You can also just JOIN to the VIEW as well, as part of the code for your export:

SELECT  ASD.AccountNumber, ADF.Dx01Code, ADF.Dx02Code, ADF.Dx03Code, ADF.Dx04Code,

ADF.Dx05Code, ADF.Dx06Code

FROM    AbstractData ASD

LEFT JOIN zcus.dbo.IatricAbsDxCodesFlattened ADF ON ( ADF.AbstractID = ASD.AbstractID AND ADF.SourceID = ASD.SourceID )

(Note that in MT 6.1 the AbstractID field goes away, and is replaced by VisitID)

Account Number

Dx 01 Code

Dx 02 Code

Dx 03 Code

Dx 04 Code

Dx 05 Code

Dx 06 Code

A0914869722

774.6

A0203879655

648.93

789.00

784.0

A0970887105

789.00

496

250.00

401.9

A0150873503

V57.89

555.9

276.1

263.9

599.0

799.3

A0303888598

174.9

A0104865878

847.0

338.29

784.0

E826.1

E006.4

What about performance using this VIEW? The key for good performance is to JOIN to the VIEW via a primary key, or other indexed field, in the top table in the VIEW, which in this case is AbstractData. If you try doing a JOIN or a WHERE to a specific DX code in one of the Dx?? Code fields – then performance will be slower.

With this in your toolkit, you can easily pull in up to 50 (or more) DX codes flattened down to the abstracted case level with as little effort as possible!

(end)

Visit our report library at http://www.iatric.com/Information/NPRReportLibrarySearch.aspx.

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

NPR Tip:  What Happened on March 1st 1980?

Joe Cocuzzo, Senior Vice President – Report Writing Services

MAGIC and Client/Server Time Stamps and how to use them.

Example Reports

Report with “elapsed time” information

Export of Doctor Dictionary Entries created or edited on/after selected date.

List of NPR Reports “run since” elected date

FIRST – The Geeky Background Information…

Most dates in an NPR application are stored internally in YYYYMMDD format and most times are stored in HHMM format. This is convenient for sorting. When a YYYYMM date is printed or displayed, the system country value is checked to determine whether you get MM/DD (US) or DD/MM (everywhere else) format.

Sometimes, MEDITECH uses a different value for a combined date and time, using the number of seconds since March 1, 1980 or, for a date in PCI or RAD, the number of days since March 1, 1980.  Typically uses of this value are:

Building a unique subscript for a transaction queue. Here is the Admissions Transfer Queue from a MAGIC test system (the system appends a tie-breaker when two transactions file in the same second).

NPR Tip Image

Storing a date and time in a user or report activity log. Here is the MIS activity log from the same MAGIC test system:

NPR Tip Image

Here is report activity for a custom NPR report (a face sheet):

NPR Tip Image

Creating a “last update” stamp in a dictionary.

NPR Tip Image

Creating/converting “number of seconds” values.

To produce the “number of seconds” value, use the @.sd NPR macro.

To convert the “seconds” to a YYYYMMDD date use %Z.date.in.magic(@.sd)

To convert the “seconds” to a HHMM time use %Z.time.out(@.sd)

To convert an NPR date and a HHMM time to a seconds value do this:

%Z.date.clinical(YYYYMMDD)*86400+(%Z.time.in(HHMM))

First Report Example – show elapsed time for report in trailer field.

It is sometimes necessary or useful to know how long it takes a scheduled or spooled report to compile and complete. One example is when you are designing a large data export and want to run a portion of the range to project how much time the full export might take.

You can do this in two easy steps:

Store the starting time in seconds, you can do this right in an “AL START” footnote as follows:

NPR Tip Image

Compute the elapsed time in seconds in a field in the report trailer, convert to minutes.

NPR Tip Image

Running the report through a year of transactions, elapsed time 20 minutes:

NPR Tip Image

The Client/Server approach is identical, here is some sample output for one month of transactions:

NPR Tip Image

Second Report Example, all records edited on/after selected date:

Sometimes it is useful to have a report you can run for all records, or only records updated on or after a selected date. For example, rather than sending the entire doctor dictionary to some other-vendor system, you could send an initial export of all doctors and a weekly “update file” of new or edited entries.

Just create a simple dictionary report and add a computed selection field. If you add the BEGINNING keyword, you can run from “BEGINNING” to do a full export of all records.

NPR Tip Image

The xx.last.updated field converts the last update time stamp from “seconds” format to YYYYMMDD

NPR Tip Image

Note that the “last.update” field will not be visible in the field lookups, but all dictionaries have this field, and if you enter it in your computed field logic, it will work.

Here is sample output from a Client Server System (again, C/S and MAGIC approach is identical):

NPR Tip Image

Third Report Example:  List of all NPR Reports run on/after a selected date.

A MEDITECH MAGIC hospital doing a migration to another platform asked me for a list of NPR reports run in the past year. MEDITECH keeps a log of the last 10 runs of a report in a child segment, with a subscript @run.time which is a “seconds” value. To select only reports with the most recent run, we need to somehow get the “last” (largest) value of @run.time and convert to an NPR date (YYYYMMDD) to check to see if it is on or after T-365 (or some user supplied date).

Step one is identical to the dictionary export example above, create a report in the “main” segment, and add a computed select field:

NPR Tip Image

The code for the xx.date field is slightly more complicated because we want to get the largest value of run.date and convert to YYYYMMDD format.

We can either use the @Prev operator or the @Last operator to get the largest value of @run.time for each report. The @Prev operator does a “logical previous” or a “minus” on the subscript, which “backs up” one entry in the list and assigns the previous value back to the subscript. The @Last operator does a “logical previous” but does not assign the value back to the subscript.

Looking at the object code may help you understand the difference:

Using @Prev

NPR Tip Image

So our xx.date field should look like this:

NPR Tip Image

Because we have an IF statement around the code, the value of the field is the condition of the IF.  If there was no activity for a report, the @Prev(run.time) would return nil and the IF would return nil, so the report would not be listed.

Using @Last

NPR Tip Image

The code for xx.date using @Last is shorter and because we can write everything in one expression, no IF if needed.

NPR Tip Image

@Last approach has the advantage that you are not affecting the value of run.time, so if you used multiple fields on your report to show all activity, the code in the xx.date field would not cause problems. @Prev has the advantage of leaving run.time equal to the most recent run so you could put xx.date field on the picture (establishing run.time) and then use the field @run.user and it would be populated with the most recent run user.

Here is example output from a MAGIC site:

NPR Tip Image

If we use the @Last approach and add some multiple fields, we can add activity listing to our report:

NPR Tip Image

The xx.act.date field converts run.time with %Z.date.in.magic. We would not want to use the xx.date in the set of multiple fields because that would give us the most recent run date, not each run date.

NPR Tip Image

For Client Server, you need to add FNC=LST for the field to work.

NPR Tip Image

Here is sample output:

NPR Tip Image

Three example reports (both C/S and MAGIC formats) have been placed in our report library:

Show Elapsed Time for report run: BAR.PAT.zcus.is.eupdate.et

Doctors created/edited on/after selected date:

C/S: MIS.DOC.zcus.is.last.updated.since

MAGIC: MIS.DOC.DICT.zcus.is.last.update.since

NPR reports last run run on/after selected date:  NPR.REP.zcus.is.eupdate.last.run.since

Visit our report library at http://www.iatric.com/Information/NPRReportLibrarySearch.aspx.

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

SQL Tip —The Data Request Number

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

As we’ve discussed before in webinars, white papers and in our SQL training classes, we recommend that all DR-based reporting and extracting be driven by stored procedures. Those stored procedures be used or consumed by either SQL Server Reporting Services reports, or SQL Server Integration Services packages, or Crystal Reports.

This splits the work into two focused areas – data retrieval and then data presentation – and lets you use each “tool” in an optimized way — which is good.

But unlike a classic NPR, your work is now split into two different sets of code, using two (or more) sets of tools, and physically stored in at least three places! And that can get confusing.

One excellent solution to manage this new environment is to implement a Data Request Number (DRN) and some structure around the process of building and deploying reports and extracts.

In the Data Request Number approach, every single data request you receive is given a unique number. This number is tracked, along with key data about who made the request in a centralized way. (For some of our customers, that is a custom web app they built, or an Access database, or an Excel spreadsheet… an example layout of the tracking tool – in Excel – is attached to this tip).

Note that every request is tracked, from every system, even if we don’t build a report or extract in response to the request. And the Data Request Number is just an identifier to let us identify things were created in response to a request – it is not necessarily structured in such a way as to identify a specific module or application – though you could do so.

The DRN Workflow

In the specific case of a data request that is met by building a stored procedure and then an SSIS extract and a matching SSRS report, we would get something like:

    • Data Request Number assigned is: M0241 (in this implementation, “M” stands for a MEDITECH report, “K” for Kronos reports, etc.)
    • The request is for a BAR-based text-file extract of UB-04 data to be submitted to the state.
    • The request includes an error report, showing issues with the data that should be corrected before the final file is created and sent to the state.
    • We create one Stored Procedure to get the data from the DR and it gets the DRN embedded in the stored procedure name like so:
     zcus.dbo.M0241_BAR_UB4_CLAIMS_SP
    • And the stored procedure code you’re keeping in an offline copy of the database object has exactly the same name, with a .sql added:
     M0241_BAR_UB4_CLAIMS_SP.sql
    • Then we use that stored procedure to drive an SSRS report, where the report has multiple sections, pulling out records that meet specific error criteria. We can embed the error criteria either in the sp (adding an Error Flags field) or in the report. The key idea here, however, is that the report .RDL name also includes the DRN:
     M0241_BAR_UB4_CLAIMS.sql
    • When you set the description property of that report, you also embed the DRN, so that it flows over into the report title the end-user sees in Report Manager or Sharepoint:
     M0241-State UB4 Error Listing
    • Finally, when you create the SQL Server Integration Services package to automate the creation of the formatted text-file, the .dtsx file also embeds the number:
     M0241_BAR_UB4_CLAIMS.dtsx

We can now track all of the parts of the response to the request just by the DRN. This makes maintenance vastly easier for the programmer; gives the end-user an easy way to refer to a specific report (“I need a change to the M0241 report”); and lets you positively identify each request and what happened to it in response.

Ah, but wait!, you say. What about database objects that are generic? What if we create a VIEW in the database, for example, to ease reporting? Should they have DRN’s? And we say… no, they do not. They have names like:

zcus.dbo.BAR_CLAIMS_VIEW

And…

BAR_CLAIMS_VIEW.sql

With the exception of a non-generic supporting object. Say you need a SQL function which is specific to one data request, then you might have something like:

M0241_BAR_UB4_CPT_CODE_FN.dtsx

A real-life example of a request-specific function would be a multiple-file/multiple-stored procedure extract set where state-specific CPT Codes needed to be suppressed in the files sent to a specific vendor. Then you could properly build a DRN-number function because it is intimately linked to that request.

Organization of Code

We noted above that you’re keeping an off-line copy of each database object, (because you are, aren’t you?) for the day that something happens to the database and you have to recreate a single object (stored procedure, custom index, custom table, function, view, etc.) without a database restore.

This happens. It’s painful if you haven’t taken some care beforehand…

What we suggest is that you build, in a network share visible to your report developers and DBA’s, a structure like:

\DR
\Live
\Procedures
\Functions
\Views
\Indexes
\Reports

And everything that you put into the database you do via a SQL script, which you save into the offline folder first, then run to create (or ALTER, later) the object(s).

When you need to change an object, you change the offline .sql script first, then run it to change the object in the database. Work diligently to keep these two structures in sync! Someday it will save your… well, you will be happy you have it.

Extracts are a bit more complicated, because SSIS wants to create its own folder structure, and that tends to get deep — so deep, in fact, that SSIS can create a folder path you can’t access! Because of that, you want to shorten the path as much as possible.

\Extracts
\M0241-Bar-Claims
\Extract
\SQL

The \Extract folder contains both the .sln, .dtproj and .dtsx files all in one bucket. The \SQL folder, in this case, contains all of the extract’s supporting code. Conceptually, this takes us away from the structure above, where we have more generic folders, but is much easier to manage.

RD and NPR

Nothing prevents you from using the same tracking system for new NPR(s) and RD reports, as you can set their internal name and title. You won’t be storing them in the offline storage area, however, unless you have some scripted process to dump the object versions of those report formats out to disk on a schedule.

Finally…

If you’re just starting with the DR, that’s a great time to implement an organizational system like this.

If you’re migrating to MT6, that’s a great time to implement an organizational system like this.

If you have disorganized folders full of a mass of code, it’s… well, you get the idea!

(end)

Visit our report library at http://www.iatric.com/Information/NPRReportLibrarySearch.aspx.

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

NPR Tip: Every Module Can Have Room and Bed Index (MAGIC or Client/Server)

Joe Cocuzzo, Senior Vice President – Report Writing Services

ALSO:– Physical vs Logic “Nexting”

It is quite common to write reports for current inpatients, and a logical assumption by many NPR report writers is that such reports are best built in ADM, even when the data required is in some other application.  A very typical approach is to build a “main report” in ADM and then use (shudder) fragments or programming in a macro to get the data from OE, PHA, LAB, NUR, etc.

A better technique is to write the report in the DPM with the data, and take advantage of the fact that most clinical modules keep a prefix or all prefixes “open to” ADM.

In MAGIC, EDM, OE, PHA, LAB, NUR, and SCH keep the * prefix open to the ADM data file, (ABS, BAR, RXM, and RADRW do not).

In C/S, EDM, OE, PHA,LAB, NUR, and SCH keep all prefixes open to ADM data, dictionaries, and programs, and it is easy to open ADM from any C/S report by calling: %Z.rw.fragment(“”,”ADM.XYZ”) in a “start macro”.

This month, we will show how to effectively “give” EDM, OE, PHA, LAB, NUR, SCH a room and bed index.  Our example will be a Pharmacy report for find all current inpatients on a particular medication.  We will use the ADM room bed index to make a list of all current inpatients in slash, and use that list in a selection for speedy access to just those patients for our report.

The first step is to pick an index for your PHA.RX report that contains ADM.PAT.urn as far to the left in the subscripts as possible, ideally as the first subscript  In EDM, OE, PHA, LAB, NUR and SCH this field is called “patient” (ABS and BAR are odd ducks with @adm.urn and @int.number just because).

In MAGIC, you have these indexes to choose from:

NPR Tip Image

In Client/Server the indexes are identical, you just don’t have to deal with the “customer name/programmer name” duality, as the C/S development team lacked a department of redundancy department.

NPR Tip Image
So our PHA report looks like this:

NPR Tip Image

The selections on the report are setup like this:

NPR Tip Image

Three points about the selections:
Selection #1
We are going to load a list of admission urns from the ADM room bed index in a “start” macro, the LI will use that list against the index file to efficiently get to all the medications of each current inpatient.

Selection #2
The index file we picked includes “status” (order status) as its second subscript.  We can restrict the report to “AC” (active) orders to make it even more efficient by hardcoding that selection as #2

Selection#3
It is bad practice to write a PHA report looking for particular drug mnemonics.  Nobody is going to check every formulary load to see if some new variations on a drug have appeared.  Checking the med’s.generic.id for “INSULIN” with a “CO (Contains) makes it less likely that new forms of that medication will be missed.

Now we need to use the ADM room and bed index to get our list of current inpatients (and outpatients in a bed if you use that MEDITECH feature).

The room and bed index looks like this:

@room.bed.index[facility,room.bed] = urn

To use it from a Pharmacy report we need to add the DPM so the translator can find it:

@ADM.PAT.room.bed.index[ADM.PAT.facility,ADM.PAT.room,ADM.PAT.bed]

Since we want to loop through the whole thing, we can use “physical next (>) rather than nesting three DO loops with “logical next” (+ or @Next).   It just amounts to a bit less typing:

Remember that (for MAGIC), only the * prefix is open to ADM in the PHA (and OE, NUR, and EDM applications).   This means you have a problem using any field where the data definition expects to use a different prefix, such as :.

The way to tell which prefix will be used is either, stick the field or segment in a test macro and watch the translation or look at the data definition for ADM.PAT.   If you look at the data definition you will see that data fields use the * prefix, but index segments translate with : instead.

NPR Tip Image

Fortunately, there is a “@Chg.prefix” translator macro you can include in your code to force the translator to use a substitute a different prefix for the one defined in the data definition:

The syntax is:
@Chg.prefix(DPM,regular prefix,prefix to use instead)
So if we do @Chg.prefix(ADM.PAT,:,*) the translator will use * instead of : for anything with a : in the data definition.  Instead of :AARB the translator will do *AARB so your code will work.

PS – using one prefix for indexes and a different prefixes for data increases the chance that data fetched from the file will be in a memory based buffer and a report will run faster, with fewer trips to the disk for more data.  This is the reason MEDITECH typically uses a different prefix for data than indexes, and opens multiple (two or more) prefixes to the application data file.

NPR Tip Image

After the start macro runs, there is a list in /PAT of all the patients currently in beds:

NPR Tip Image

This list in /PAT behaves like your own RBI index of patients for the report in PHA.

In C/S you do not need to use the @Chg.prefix, because the ADM prefixes are open in PHA exactly as the translator expects.

NPR Tip Image

Here is output from a MAGIC test system:

NPR Tip Image
Here is output from a C/S (actually 6.0) system:

NPR Tip Image

This sample PHA report in both C/S and MAGIC format: %PHA.RX.zcus.update.pha.rbi has been posted in our report library.

Visit our report library at http://www.iatric.com/Information/NPRReportLibrarySearch.aspx.

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

NPR Tip: Keep Users Entertained During Download or Printing (MAGIC or Client/Server)

Joe Cocuzzo, Senior Vice President – Report Writing Services

NPR Tip:  Keep Users Entertained During Download or Printing (MAGIC or Client/Server)

If you have a report that users are going to run and wait for, it can be nice to print some kind of progress message to the screen to keep them entertained.

The easiest way to do this is to use the @W.display macro, which will show a message in a window while the report runs.

Some caveats:

Don’t use @W.display when the user (in MAGIC) sends report output to “S.”  (Your call will pop a small window and the report output will end up in that little box.)

Your attempt to display a progress message (in MAGIC) will not do anything when the user prints to VIEW. This is because output to VIEW occurs in the background and the screen is not available to your report for messages.

You can decide to send a message per each detail record, or at some less frequent point. If there are relatively few detail records, you might want to show a progress message per each detail record. If there could be a lot of detail records, you should show the progress message at some sort of break.

Our demonstration report is set up as a DOWNLOAD format, so we have Chars/Line at 200 and Lines/Page = Page Size. The progress message technique could be used in a report designed for printed output as well. Because even a few days of output will have many records, we are going to show a progress message per each day of the range, and those records are output.

NPR image

Because we selected the bar.acct.discharge.date.index (in C/S it is called: “discharge.x”) the sorts are automatically set up as follows:

NPR image

Given that this is a download, we do not want a header line to print, so we suppress that region with an LC attribute:

NPR image

Here are the footnotes on the example report. The highlighted one calls the macro to display the progress note. The other footnotes change the output into a tab delimited download file.

NPR image

The hk1 macro uses the @W.display translator macro to show a message per each date as the sort changes:

NPR image

Notice that we do not use an @ sign in front of the field bar.dis.ser. That is because we want to use the temporary sort value from the local variable and not have the report try to find the field for particular record. In the HK1 region, the subscript of the detail segment will be nil and @bar.dis.ser will fail to return anything, but bar.dis.ser will.

Using the @ sign on @bar.dis.ser will translate to:

NPR image

Omitting the @ sign gives us:

NPR image

If we look at the object code of the “.R” part of the report, we can see that the report loops on the local variable bzds (which is what bar.dis.ser translates to), so we will have a date in this local variable in the HK1 macro

NPR image

So, when we run the report to a printer or a download, we will see a progress message as follows:

NPR image

The message update per each day of the selected run date range, then you see the usual message about Downloading… or Printing…

Visit our report library at http://www.iatric.com/Information/NPRReportLibrarySearch.aspx.

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

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.