Written by Thomas Harlan, Jim McGrath; Reporting Services Team - iatricSystems
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're lucky — you can do:
Which will get you a pop-up like this:
The top section shows you where 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, etc.) there are two tables that hold the data definitions:
DrTableColumns
DrTableMain
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 IatricDataDefQuery to do just that. (We’ve made MAGIC, CS and MT6 versions). 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].[ IatricDataDefQuery] 'MisLoc_Main' -- Table Name
,'%Phone%' -- Column name
,'%' -- DPM Name
,'%' -- Segment Name
,'%' -- Element Name
,'MIS' -- Application Name
,0 -- 1 is only table name, 0 is all columns
,0 – 1 shows row counts in tables, 0 does not
The first set of parameters:
@cTableName
@cColumnName
@cDpmName
@cSegmentName
@cElementName
@cApplication
… is set up in the query to do a wildcard search, where you can use % for any number of characters and _ for a single character.
The next parameter:
@nMode
Changes the output arrangement of the query. If you set @nMode to 1 then you get a listing of only the Table Name and the Module in which the table(s) were found:
If a zero (0) then you get a listing of each column that matches your criteria, as well as supporting detail.
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.
And finally the last parameter:
@nRowCounts
Controls whether the query should fetch estimated row counts per found table.
Convenience!
You can easily use this stored procedure to drive a MS Reporting Services report — so we’ve created one (as an SSRS 2008 .RDL), which prompts the user for all of the main parameters:
And returns a list of column details grouped by table name:
When you publish this report onto your MS Reporting Services server, make sure to update the Shared Data Source in the report to reference your site’s SRS, pointing to your own DR server and database. The report will only work if the stored procedure already exists.
Notes
MT also publishes the NPR and RD data defs online:
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 one database container (for CS/MAGIC) or two (for MT6). But maybe you have three, or more … You can easily copy/paste the code inside of the example SP to handle multiple databases.
If you need more help…
Our Report Writing team can help you fix reports, create new ones, and make old ones faster. Simply reach out to your Iatric Systems Account Executive or our NPR report writing team at reportwriting@iatric.com to discuss how we can help support your team!
