NPR Tip: “Echo Name” feature for Customer Report Screens (MAGIC Only)

Joe Cocuzzo, Senior Vice President – Report Writing Services

You may have noticed that MEDITECH standard NPR report screens can have “display only” fields, but in customer reports, there is no attribute or standard method to provide the same feature. In a CDS, you could just set the “Echo Name” flag to Y when building the screen.

This month we will show you how to add a “name echo” feature to a MAGIC NPR report. Our example will be a simple doctor dictionary report where we will show the name on the selection screen after a doctor is selected:

Instead of this:

NPR Tip Image

We want to have the selected doctor’s name display like this:

NPR Tip Image

Here is how to add a “display only” field to your MAGIC report:

Step 1

Add a computed field where the name will display, make it the appropriate length (for doctor name this is 30 characters) and use the “IG” (Ignore) selection operator.

NPR Tip Image

In Process Reports you have two options that allow you to modify the screen generated by the screen built automatically by the report translator. The “Edit Picture” option allows you to move or edit prompts, to add additional text, or to increase the number of entries that show for an LI or RL field. The “Edit Elements” option allows you to add or modify FCL, REQ, DFT, and IFE attributes for the screen fields.

NPR Tip Image

First we want to move the 2nd “xx.name” field to the right of the doctor mnemonic field, we can remove the “:” prompt entirely.

In the Edit Picture routine do this:

NPR Tip Image

NPR Tip Image

Then we use the Edit Elements routine to add an IFE=”” expression that shows the doctor name in 30 characters at the correct row and column and has the cursor skip the field.

The attributes that default for the “xx.name” field with the IG selection operator look like this:

NPR Tip Image

We need to make two changes. We need to change the REQ=1 to REQ=”” so the field is not required, and we need to add code to the IFE so that the field is skipped but the doctor name is displayed in the spot where me moved the field (to the right on the mnemonic).

It would be nice if the Report Screen program kept the row and column in some nice “R” and “C” variables for us to use in the IFE, but then we do not have that luxury.  We could hardcode values and experiment, but we’d need to adjust for the 3.x vs the 4.x workstation and code like this:

NPR Tip Image

This code uses the P() command to print the name in 30 characters truncated, left justified to row 0 (if 4.x Workstation) or 2 (if 3.x Workstation) and column 29.

Since you have a ruler line in the “Edit Screen” routine, it isn’t too difficult to figure out the row and column to use, you just need to check the @.gui flag to figure out if the screen starts on row 0 or row 2. 3.x screens use up two lines for the title and the horizontal line, and in the 4.0 workstation the title moves up to the Windows menu bar so your screen starts at line 0.

With this code, we have a report that works for both versions of the Workstation:

NPR Tip Image

NPR Tip Image

For extra credit, you could invert the name and display the doctor group mnemonic and name as well, like this: Try your own variation as appropriate to add relevant information to your NPR Report selection screens.

NPR Tip Image

NPR Tip Image

But what about C/S sites?

Unfortunately this trick won’t work in C/S because starting in version 5.6 all screen output is handled by a set of NPR.UI programs and calling them is blocked by the syntax checker. If MEDITECH added a DIS=N attribute feature to the C/S screen builder, you could do this the same way they do in Programmers NPR. Don’t hold your breath.

The MAGIC version of this report MIS.DOC.DICT.zcus.is.echo.name has been uploaded to our report library:

http://new.iatric.com/report-library-search

NPR Tip Image

You can find additional Report Writing Tips on our website at http://new.iatric.com/report-writing-tips, 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://new.iatric.com/schedule-of-classes.

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

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

 

DR+SQL Tip: Using UNION to Best Effect

by Thomas Harlan, Iatric Reporting Services Team

MEDITECH migrations platform bring many challenges; one of them being that you may find yourself with a new LIVExDB in the picture and once you’re past the go-live you find that data is flowing only into the new LIVExDB and not into the old one anymore.

This is particularly noticeable when you have a migration from (for example) MAGIC to MT 6.1. This gets you a database scenario like:

            livemdb                      (the old MAGIC environment data)
livefdb                        (data from the new MAT modules, post go-live)
livendb                       (data from the new C/S NPR modules, post go-live)

If you have DR-based reports in play already, when this change occurs (say on June 1st, 2015, as an example), then they are going to stop having new data in them on the magical migration day.

Then you need to update your reports – but how?

You could build completely new reports pointing at the new livefdb and livendb databases, but then your end-users will have to run two separate reports to get data about any patients admitted before the go-live and discharged after… and any kind of historical record-of-service reports will also have to be run separately.

Not so very convenient for your end-users!

SQL provides two solutions, however, and we will look at one of them here: using the UNION ALL keyword.

All In One

UNION ALL lets us run two (or more) separate queries against the DR server and combine the results; if and only if, the number of columns in the two queries matches and the datatypes of each column match. This lets us do something like:

— First from Magic

SELECT  MAV.UnitNumber
,MAV.VisitID
,MAV.AccountNumber
,COALESCE(MAV.ServiceDateTime,MAD.AdmitDateTime) AS ArrivalDateTime
,MAV.Status
FROM    livemdb.dbo.AdmVisits MAV

LEFT JOIN livemdb.dbo.AdmittingData MAD ON ( MAD.VisitID = MAV.VisitID AND MAD.SourceID = MAV.SourceID )

WHERE   COALESCE(MAV.ServiceDateTime,MAD.AdmitDateTime)
BETWEEN CONVERT(DATETIME,’2015-01-01 00:00:00′,120)
AND CONVERT(DATETIME,’2015-12-31′,120)
UNION ALL

— Then from C/S

SELECT  CAV.UnitNumber
,CAV.VisitID
,CAV.AccountNumber
,COALESCE(CAV.ServiceDateTime,CAD.AdmitDateTime) AS ArrivalDateTime
,CAV.Status
FROM    livendb.dbo.AdmVisits CAV

LEFT JOIN livendb.dbo.AdmittingData CAD ON ( CAD.VisitID = CAV.VisitID AND CAD.SourceID = CAV.SourceID )

WHERE   COALESCE(CAV.ServiceDateTime,CAD.AdmitDateTime)
BETWEEN CONVERT(DATETIME,’2015-01-01 00:00:00′,120)
AND CONVERT(DATETIME,’2015-12-31′,120)

When we launch this query the SQL engine splits the work into two parallel queries and executes them simultaneously. So performance can be quite good. MEDITECH’s data structure is challenging to speed, however, because we have to look at two (or more) different fields in different tables to get the ArrivalDateTime.

However, this will get us visits on either side of the gap at go-live and since the fields line up in number and type, the report we build on top of this won’t know the difference. And neither will the user!

Dangers of Union

One gotcha to watch out for with UNION, however, is that since each section runs in parallel with one another; if each part is hitting the same tables in the same database, sometimes they block each other – and then performance falls off a cliff while each waits for the other to release database page locks. And this is bad.

So our rule of thumb is to avoid using UNION with queries accessing the same tables in the same database. In our example, we don’t have this issue – the table names may be the same, but they are in entirely different databases.

In the version of UNION that we’ve looked at so far, we have the “ALL” keyword added – this just combines the results of the two queries into a single result-set.

Union to Unique

But there is also just plain old UNION, which compares the two result-sets and discards any duplicates, producing a single set of unique rows. That is sometimes useful, but that requires more overhead to compare two sets and produce a third to return to you.

Alternatives

If you find that you need to run two or more sets of queries against the same sets of tables and combine the results; you don’t want a UNION at all. That will get you the performance issues we’ve just mentioned. In this scenario you:

  1. Create a temp table with a common structure.
  2. Run each query in sequence, INSERT-ing the results into the temp table.
  3. Return the whole contents of the temp table to your report or extract.

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

 

DR+NPR Tip: Running Web-based Reports from a MEDITECH Menu

by Thomas Harlan, Technical Team Lead – Data Repository at Iatric Systems & Mitchell Lawrence, HIS Programmer Analyst at Iatric Systems

Overview

Many sites using SQL Server Reporting Services will use the External Links feature in MEDITECH to route a user to the “top” of the reporting portal. However, you can also set up a structure in NPR that lets you invoke the user’s web browser and provide a URL to go to the reporting portal and open a specific report directly from a MEDITECH menu.

We do this by creating two NPRs (which are attached to this tip in both MAGIC (PDF) and C/S (PDF) versions):

First, there we’ll call the control report:

MIS.USER.zcus.is.rs

This single NPR holds a list of all web-based report URLs you want to invoke from a MEDITECH menu, and each one of them is assigned a unique identifying number. (If you’re using Data Request Numbers (tip from 2/19/2015), then you can use the same number here).

Example Report URLs

Business Objects http://boe.hospital.org:8080/BOE/OpenDocument/opendoc
/openDocument.jsp?sIDType=CUID&iDocID=ASiVWPaLWtJNmPHJIu87i2Y
MS Reporting Services http://rs.hospital.org/Reports/Pages/Report.aspx?
ItemPath=%2fEDM_zcus_iatric_ed_dashboard

Second, we have a stub or template NPR that exists only to be copied into a version that you’ll update to point to the web-portal-based report:

MIS.USER.zcus.is.rs.template

This gets copied to something like:

EDM.PAT.zcus.is.rs.ed.dashboard

Within that copy of the template (what we’ll call the link report) all we need to do is update the Title field (on Page 1) to reference the number we assigned to the URL that we want to kick off:

“_MIS.USER.zcus.is.rs.M.launch(1)_”

The launch macro over in zcus.is.rs then builds out the URL we want and launches the default browser on the workstation and goes to that URL, viewing the report.

Once that is set up, then you can attach EDM.PAT.zcus.is.rs.ed.dashboard to any MT menu that will accept an NPR as a menu option, and then launch the web-portal-based report from inside a MEDITECH workflow.

All of the report URLs are centrally managed in MIS.USER.zcus.is.rs so they are easy to find, update and add to.

Requirements

  1. Each MEDITECH (MT) workstation needs a web-browser installed and set to handle http requests by default.
  2. Preferably, access to your web-reporting portal will be handled by Single Sign On, using the end user’s Active Directory credentials – so that they open the reporting portal web-page seamlessly without being prompted for a username and password.
  3. Users launching the reports from a MEDITECH menu will need at least * access to the appropriate application in the MIS user dictionary where the link report is created.
  4. Users will of course need appropriate access to the reporting server and the specified report they are running.

Creating MIS.USER.zcus.is.rs.template from scratch

This is the template report that the end user copies and then modifies the title field to call the proper report. This is also provided via an attachment to this tip (MAGIC (PDF) or Client/Server (PDF)).

  1. Start NPR
  2. Create a new NPR in MIS.USER called zcus.is.rs.template
  3. Edit the report
  4. Set the title to “_%MIS.USER.zcus.is.rs.M.launch(1)_”
  5. Set Detail to N
  6. File and Translate the report

Identify the URL to run an SSRS report

  1. Go to the MS Reporting Services portal and right-click on a report name in Report Manager. Copy the URL to clipboard, and you should get a URL like this:
    http://reportsvr/Reports/Pages/Report.aspx?
    ItemPath=%2fEDM_zcus_iatric_ed_dashboard
  2. For long URLs, it is best to break up the url at a punctuation into smaller parts to avoid character limits:

    http://                                        PREFIX
    reportsvr                                      RSSERVER
    /Reports/Pages/Report.aspx?ItemPath=%2f        PATHTOREPORT(1)
    EDM_zcus_iatric_ed_dashboard                                  REPORT(1)

Create the Launch report from scratch

This NPR holds the code to produce our report based on the parameter fed to M.launch.

  1. Start NPR
  2. Create a new NPR in MIS.USER called zcus.is.rs
  3. Edit the report
  4. Set the title to Reporting Services Utilities
  5. Set Detail to N
  6. File and Translate the report
  7. Create a new macro called “launch”
  8. Enter the following into the macro code
    For C/S:

    ;C/S title code: “_%(MIS)USER..zcus.is.rs.M.launch(URLSEQ)_”
    IF{‘/DONE @SET.VARIABLES,
    @GET.URL,
    @CALL.URL,
    1^/DONE},
    /R.TITLE;

    SET.VARIABLES
    ;Set the URLSEQ
    A^URLSEQ,
    ;Suppress Print On Prompt and End of Report messages
    1^/Z.SCHED.LOG^/R.NO.PRT.MSGS,
    END

    CALL.URL
    @CS.CALL.URL,
    END

    GET.URL
    %MIS.USER.zcus.is.rs.M.url(URLSEQ),
    END

    CS.CALL.URL
    @Shell.execute(/FULLURL)

    For MAGIC:

    ;MAGIC title code: “_%MIS.USER.zcus.is.rs.M.launch(URLSEQ)_”
    IF{‘/DONE @SET.VARIABLES,
    @GET.URL,
    @CALL.URL,
    1^/DONE},
    /R.TITLE;

    SET.VARIABLES
    ;Set the URLSEQ
    A^URLSEQ,
    ;Suppress Print On Prompt and End of Report messages
    1^/Z.SCHED.LOG^/R.NO.PRT.MSGS,
    END

    CALL.URL
    @MAGIC.CALL.URL,
    END

    GET.URL
    %MIS.USER.zcus.is.rs.M.url(URLSEQ),
    END

    MAGIC.CALL.URL
    %Z.link.to.shell(“”,””,^/URL)

  9. File and translate your macro
  10. Create a new macro called “url”
  11. Enter the following into the macro code for C/S and MAGIC both:

    ;%MIS.USER.zcus.is.rs.M.url(URLSEQ)
    @SET.VARIABLES,
    @BUILD.URL,
    END;

    SET.VARIABLES
    A^URLSEQ,
    “”^SEQ,
    ;Get Global Variables
    %MIS.USER.zcus.is.rs.M.config(“”),
    /PREFIX_/RSSERVER^/URL[@Add(1,SEQ)],
    “”^RSEQ,
    DO{+/PATHTOREPORT[RSEQ]^RSEQ /PATHTOREPORT[RSEQ]^/URL[@Add(1,SEQ)]},
    /REPORT[URLSEQ]^/URL[@Add(1,SEQ)],
    END

    BUILD.URL
    ;Build Final URL from Sequenced Path
    “”^SEQ,
    DO{+(/URL[SEQ],DATA)^SEQ @Add(L(DATA),TOT)},
    TOT^/URL|0,
    END

  12. File and translate your macro
  13. Create a new macro called “config”
  14. Enter the following into the macro code for C/S and MAGIC both (NOTE: this is where the customer will need to do the most customization, see comment lines in green):

    ;– %MIS.USER.zcus.is.rs.M.config(“”)
    @SET.VARIABLES,
    @BUILD.REPORT.ARRAY,
    END;

    SET.VARIABLES
    ;– Set /RSSERVER to the DNS name for your reporting server. This will be used to
    ;– dynamically build the URL to launch the reports.
    ;
    “REPORTSVR”^/RSSERVER,
    ;
    ;
    ;– Set /PREFIX to “https://” or “http://”, depending on your report server environment
    ;
    “http://”^/PREFIX,
    ;
    ;
    ;– Set the /PATHTOREPORT, Note, if this is exceedingly long, you may need to use more
    ;– than one array element
    ;
    “”^SEQ,
    “/Reports/Pages/Report.aspx?ItemPath=%2f”^/PATHTOREPORT[@Add(1,SEQ)],
    END

    BUILD.REPORT.ARRAY
    ;– ED Dashboard
    ;
    “ED Dashboard”^/R.TITLE[1],
    “EDM_zcus_iatric_ed_dashboard”^/REPORT[1],
    ;– Pledge Form Download
    ;
    “Pledge Form Download”^/R.TITLE[220],
    “PledgeFormDownload”^/REPORT[220],
    END

  15. File and translate your macro

NOTE: Customization by the customer for their environment is mostly done in the M.config macro, with the exception of copying and modifying the stub report for each individual SSRS report being ran.

Using template NPR to create stub reports for your SSRS reports

  1. Copy MIS.USER.zcus.is.rs.template to a unique name that identifies the report you are wanting to run out of SSRS, for example: MIS.USER.zcus.is.rs.ed.dashboard.
  2. Identify the ID number of the report to invoke. “1” in our example.
  3. In the new NPR, modify the title field to read
    1. For C/S: “_%(MIS)USER..zcus.is.rs.M..launch(1)_”
    2. For MAGIC: “_%MIS.USER.zcus.is.rs.M.launch(1)_”
  4. File and translate the new NPR
  5. Attach new NPR to user’s menu, which will allow them to run the SSRS report EDM_zcus_iatric_ed_dashboard from the Reporting Services server in their default web browser.
  6. Attach EDM.PAT.zcus.is.rs.ed.dashboard to user’s menus and they will now be able to execute the ED dashboard SSRS report from within MEDITECH.

Notes

  1. In this example we used MIS.USER as the template report DPM because MIS reports exist on every MAGIC segment. However both the template and the shell reports can be created in any DPM.
  2. MAGIC has a maximum line size of 250 characters. In practice, lines should not exceed 200 characters to leave room for overhead. Very long SSRS report paths can be broken into shorter segments and loaded into additional lines in the /URL array, as shown.

Extra Credit

★ Modify the control NPR (zcus.is.rs) to list the contents of the URL array in the Detail section of that report– then you can run that report and get immediate documentation of everything you have set up.

★ Make a variant of the zcus.is.rs and zcus.is.rs.template structure to run from a specific DPM (like BAR.PAT) – prompt for an account number using the MT prompt screen(s) – and then invoke an SSRS or Crystal Report passing that account number as a parameter: (Passing Parameters by URL for SSRS | Passing Parameters by URL for Business Objects ).

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

NPR Tip: Save Trees and Charge for More ER Supplies

Joe Cocuzzo, Senior Vice President – Report Writing Services

MEDITECH MAGIC or Client/Server – Force a report to PREVIEW and show bar codes for scanning

A MEDITECH Magic customer recently asked us to produce a report that could show bar codes on the screen of all current ER Patients to allow Nurses to scan an account number code and then any supplies issued to the patient. Charging for the “patient issues” was being done by MM staff who would download the set of account number and item bar codes from the hand-held scanners a few times per day to create patient issue charges in MM.

The plan was to have a PC in the supply room setup to run an NPR report and produce output like this on all current ER patients, so bar codes could be scanned from the screen before item bar codes were scanned as chargeable supplies are picked up:

NPR Tip Image

We want a report that does the following:

  • Automatically outputs to PREVIEW (no print on prompt for user)
  • Creates a two column list of patients with bar codes

The first step is to build a report in EDM.PAT that gets current ER patients. We can just use the “tracker.patients.index”:

NPR Tip Image

We want to sort the patients by name and exclude the entries in the tracker index that represent empty rooms:

NPR Tip Image

Now we want to manipulate the report so that there is no “Print On” prompt for the user and the output automatically goes to PREVIEW.

Step #1 is to “imbed a program in the title.” To get the title for the report selection window, and to put the title of the report in the Page Header, the string in the report title is executed as a line of magic code. What this means is that we can imbed a program in the title, and that program will be executed as the selection screen is displayed to the user.

We just need to write a macro “as a program” (below we call the macro “d”), and imbed in the title surrounded by double quotes and underscores:

NPR Tip Image

In Client Server, the “internal” or “object code” name needs to be used, like this:

NPR Tip Image

The way to tell how the macro is named is to use the “List Object Code” option from process reports and check the name in the lookup box:

NPR Tip Image

Let me explain how you create the “d” macro so it is a program that both forces output to “PREVIEW” and gives your report a title to show for the selection screen window and to print in the page banner (if you have one).

The first thing to know is that a macro created to be a program needs to end with a semi-colon, and the last Magic expression before the semi-colon will be the value that the program returns.

So, if we want to have a “title macro” return “THIS IS THE TITLE OF THE REPORT,” we write it like this:

NPR Tip Image

With this macro imbedded in the title and no selections for the report, when we run the report we get this:

NPR Tip Image

To suppress the “PRINT on:” prompt and force the report to PREVIEW, we need to do two things in our macro; call the printer selection utility “Z.on.device” with a hardcoded printer selection of “PREVIEW” and then to set the /Z.SCHED.LOG flag, which will cause the report writer’s call to Z.on.device to do nothing.

While we are at it, we will change our report title to something a little less generic:

NPR Tip Image

Finally, we use a /DONE flag to set up the “d” macro so that the diversion to PREVIEW only happens once. Since the report title macro will run multiple times (when the selection screen displays, and then as each new page prints), we would have odd behavior if we tried to open to PREVIEW multiple times in the same report run.

The Client Server “d” macro is slightly different because the arguments to Z.on.device for C/S are different, with the device passed in as C vs E and using the D argument to suppress the dialog box to the user.

For reference when writing macros, it can be very helpful to use the F(4) “get” key in the macro editor to view the source code of a MEDITECH procedure.  The arguments are typically in comments at the top of the program.  In the example below, I copied these comments into my macro.

NPR Tip Image

The first time the macro runs “not done” or ‘/DONE is TRUE and the code in the IF statement runs and puts a value of 1 into /DONE. The next time the macro runs, ‘/DONE is false so the condition of the IF is not executed.

The apostrophe is the “not” operator. When used on a slash or permanent data structure or a variable, it returns a value (true) if the structure does not exist or if the variable is nil, and nil (false) if the structure exists or the variable has a value.

Now we have a report that goes directly to PREVIEW.

The next step is to set up the report to store the set of patients selected in a list, and print them out in two columns in the trailer.

Our report has no detail region, just a TR. Per each detail record, we store our patients using a bit of code in a footnote:

NPR Tip Image

The “tr” macro is going to run before the TR region gets printed.  What we need to do in the tr macro is to split our list of patients into a left column and right column.   In this report I decided to keep the columns in alpha order like this:

A         F
B         G
C         H
D         I
E         J

Rather than like this:

A         B
C         D
E         F
G         H
I          J

The size of the ER at the hospital for which I was writing was such that the entire set of ER patients would likely fit on one page of output, and I thought snaking rather than zig-zag made a better sort order.

The tr macro takes a structure build by the d footnote:

/LIST[1] = urn of first patient
/LIST[2] = urn of second patient
/LIST[3] = urn of third patient
/LIST[4] = urn of fourth patient

And makes it a list like this:

/DATA[1]|0 = urn of first patient /DATA[1]|1 = urn of third patient
/DATA[2]|0 = urn of second patient /DATA[2]|1 = urn of fourth patient

The steps are:

  • Get the last value for the subscript of /LIST (that is total record count)
  • Divide by 2
  • Loop through the entire /LIST
  • If you are in the first half of the list, put the urn into |0 of DATA[n+1^n]
  • If you are in the second half of the list, reset n back to nil and put the urn in |1 of DATA[n+1^n]. We use the “r” variable to track whether we have done a “reset” to start loading the right hand values.
  • Set n back to 1 as you start loading the second half

The actual macro looks like this:

NPR Tip Image

Why didn’t we just load the data into a couple of /MV arrays and build a trailer region that looks like this?

xx.mv1__________________________                     xx.mv2____________________
xx.mv1__________________________                     xx.mv2____________________

The problem with this approach is that we are looking for output where each field in the column can have different formatting:

NPR Tip Image

It is quite a nuisance to persuade fields loaded into an MV array to have a variety of characteristics. It is easier (I know – easier for me) to wrap a loop around a set of lines and set up individual fields and use field characteristics to make 1 bold, another a bar code, etc.

The looping code goes through our /DATA[n] structure and puts the left hand column urn into the variable LEFT and the right hand column into the variable RIGHT and then we can set up computed fields like this:

DAT=FREE
LEN=30
SIZE=1.2
FONT=b
VAL=@name[LEFT]

Or for the bar codes:
DAT=BAR.CODE
LEN=12
VAL=@ADM.PAT.acct.number[LEFT]

Here is the ECB-ECE looping code:

NPR Tip Image

Some further explanation for the diehard programmer-wannabes:

The looping is “next get” syntax, which allows you to loop through a data structure and put the value of each node into a variable in a single + operation.

So this:
DO{+(/DATA[n],DATA)^n DATA^{LEFT,RIGHT},

Is the same as:
DO{+/DATA[n]^n /DATA[n]^DATA,DATA^{LEFT,RIGHT},

This expression:
DATA^{LEFT,RIGHT}

Takes the zeroth piece of DATA and puts it into the variable LEFT and the 1th piece of data and puts it into the variable RIGHT

It works like this:
Q(“FIRST”,”SECOND”)^XXX
Or:
{“FIRST”,”SECOND”}^XXX
Or even:
`FIRST,SECOND’^XXX

All give you this:
XXX|0 = “FIRST”
XXX|1 = “SECOND”
And you can do this:
XXX^{A,B}
And then A = “FIRST” and B= “SECOND”

So now we have a report that runs like this (Magic example shown, C/S is similar):

NPR Tip Image

And (here shown with a page banner) has output like this:

NPR Tip Image

Sample reports for C/S and Magic named EDM.PAT.zcus.is.bar.code.page have been loaded to 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 April 2015 issue of Iatric Systems Updates! newsletter.

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.