Produce MEDITECH Data Repository (DR) reports quickly, today

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

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

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

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

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

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

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

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

Joe Cocuzzo, Senior Vice President – Report Writing Services

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

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

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

Or for Addenda

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

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

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

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

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

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

NPR Image

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

NPR Image

NPR Image

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

NPR Image

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

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

NPR Image

The output of the report is shown below:

NPR Image

A copy of the report RADRW.REPORT.zcus.is.report.addendum.export has been loaded to our MAGIC report library.

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

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

To subscribe for email notifications for new Report Writing classes, please follow this link:
http://www.iatric.com/Information/Classes.aspx.

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

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

SQL Tip – Parsing Patient and Provider Names

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

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

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

Livefdb.dbo.HimRec_Main

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

In very general terms, that is:

LastName, FirstName MiddleInitial

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

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

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

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

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

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

This gets us:

LastName

FirstName

MiddleName

BERMAN

JOEL

F

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

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

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

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

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

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

full_name
last_name
middle_name
prefix
suffix
degree
mail_name
associated_doctor
other

Which looks like this:

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

And produces:

full_name

last_name

first_name

middle_name

prefix

suffix

degree

mail_name

associated_doctor

other

SMITH,JOHN P JR NP/JONES

SMITH

JOHN

P

JR

JOHN P SMITH JR

JONES

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

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

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

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

(end)

EXEC zcus.dbo.MIS_zcus_iatric_find_query_config ‘CAN.OX’ — Query mnemonic to search for or ALL
,’ALL’ — Query text to search for or ALL
,’ALL’ — Document Section Mnemonic or ALL
,’ALL’ — Document Section text or ALL
,’ALL’ — Intervention Mnemonic or ALL
,’ALL’ — Intervention text to search for or ALL
,’N’ — Show Row Counts from storage locations

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

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

To subscribe for email notifications for new Report Writing classes, please follow this link:
http://www.iatric.com/Information/Classes.aspx.

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

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

MEDITECH Data Repository Implementation Best Practices

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

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

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

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

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

Joe Cocuzzo, Senior Vice President – Report Writing Services

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

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

How could we reproduce this on an NPR report?

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

Arguments:       A = Box type   0 – black border

1 – grey box

2 – grey box and black border

B = length of box

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

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

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

F = gray scale (default = 10%)

G = width of lines (DFT=5)

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

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

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

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

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

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

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

F = not relevant, no grey shading

G = width of line (0-9 accepted)

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

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

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

FINAL IMPORTANT POINT:

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

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

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

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

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

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

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

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

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

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

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

,’ALL’ — Document Section Mnemonic or ALL

,’ALL’ — Document Section text or ALL

,’ALL’ — Intervention Mnemonic or ALL

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

,’N’   — Show Row Counts from storage locations

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

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

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

In this example our search finds us five records:

Query ID

Query Text

Query Type

Group Response ID

MIS Doc Sect ID

MIS Doc Sect Name

Pcs Intervention ID

Pcs Intervention Name

CAN.OX

O2 Delivery Method

Group

CAN.OX

RAD.D/C

Radiology D/C documentation

(None)

(None)

CAN.OX

O2 Delivery Method

Group

CAN.OX

GEN.CANCER

Cancer Center Documentation

CANCER.CENTER

Cancer Center Documentation

CAN.OX

O2 Delivery Method

Group

CAN.OX

GEN.CANCER

Cancer Center Documentation

RN.CANCERCENTER

Documentation for Ambulatory Services

CAN.OX

O2 Delivery Method

Group

CAN.OX

GEN.RADRN

Radiology RN Documentation

RN.RAD

Radiology RN Documentation

CAN.OX

O2 Delivery Method

Group

CAN.OX

RAD.DC

Radiology D/C documentation

RN.RAD

Radiology RN Documentation

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

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

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

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

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

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

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

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

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

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

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

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

 

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

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

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

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

In MAGIC:

NPR Tip

NPR Tip

In Client/Server:

NPR Tip

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

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

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

Try it. You’ll like it.

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

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

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

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

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

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

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

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

We wrote two versions of the date conversion code:

  • MIS_iatric_DateInterpreter_fn [code]
  • MIS_iatric_DateInterpreter_sp [code]

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

The function and stored procedure support these mnemonic patterns:

Mnemonic Pattern Notes

T

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

W

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

WB

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

WE

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

M

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

Y

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

MB

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

ME

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

YB

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

YE

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

FYB

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

FYE

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

WM

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

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

Step Description

Screen Shot(s)

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

DR Tip

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

On the Query screen, name it ConvertFromDateDs.

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

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

Click OK when done to save this dataset.

DR Tip

DR Tip

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

On the Query screen, name it ConvertThruDateDs.

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

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

Click OK when done to save this dataset.

DR Tip

DR Tip

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

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

Pick ConvertFromDateDs as the Dataset.

Set the Value and Label fields to Result.

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

Pick ConvertFromDateDs as the Dataset.

Set the Value field to Result.

Click OK to save your changes.

DR Tip

DR Tip

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

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

Pick ConvertThruDateDs as the Dataset.

Set the Value and Label fields to Result.

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

Pick ConvertThruDateDs as the Dataset.

Set the Value field to Result.

Click OK to save your changes.

DR Tip

DR Tip

You’re done!

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

DR Tip

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

Extra Credit!

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

Cheers, Jen Kelly and Thomas Harlan

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

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

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

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

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

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

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

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

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

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

Which will get you a pop-up like this:

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

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

SysDrColumns
SysDrTables

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

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

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

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

The first set of parameters:

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

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

The next two parameters:

@cNPRSchema
@cFocusSchema

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

The last parameter:

@nMode

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

DR_TABLE_NAME

MT_APP

Livefdb.dbo.MisLoc_Main

MIS

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

DR_
SOURCE
_ID

DR_TABLE
_NAME

DR_
COLUMN
_NUMBER

DR_
COLUMN
_NAME

DR_
COLUMN
_TYPE

DR_
COLUMN
_LENGTH

MT_
APP

NPR_
DPM

NPR
_SEGMENT

NPR
_ELEMENT

IAT

Livefdb.dbo.MisLoc_Main

7

Phone

varchar

40

MIS

MisLoc

Main

3

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

Notes

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

NPR

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

MT 6 DR

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

CS DR

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

MAGIC DR

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

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

Extra Credit

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

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

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

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

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

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

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

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