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

For tips on using the DR Data Def Tables in CS/MAGIC 5.67 or MT 6.07/6.15, please see this updated post.

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:

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:

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.

Notes

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

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.