How to Automate Summary of Care/Transitions of Care Reporting for MEDITECH Customers
Data Repository Talk

How to Automate MEDITECH Summary of Care/Transition of Care Reporting and Eliminate Manual Reconciliation

This article will show MEDITECH Healthcare Organizations how to eliminate manual reconciliation and fully automate calculating their numerator for the Stage 2 Meaningful Use Summary of Care (Core Measure 12, Measure 2).


This article will show MEDITECH healthcare organizations how to eliminate manual reconciliation and fully automate calculating their numerator for the Stage 2 Meaningful Use Summary of Care (Core Measure 12, Measure 2). You will learn how to work with your HISP to capture an MDN report, import that MDN into your Data Repository and integrate with the MEDITECH standard Meaningful Use SQL reports. This article is intended for an intermediate level SQL User with some Data Repository experience. 

Now that you have set up and configured your MEDITECH PHS/CCD interfaces and the CCD Summary of Care document is being sent to your HISP, it's time to start reporting Summary of Care Core Measure 12 Measure 2. 

Almost all HISPs are using the industry standard message delivery notification (MDN) capabilities. We have worked with over a dozen HISPs, but for this article we used a sample extract from a popular HISP to demonstrate these capabilities.


The MEDITECH Best Practice document for Transitions of Care provides an overview for organizations to follow for using MEDITECH's SQL report to provide a list of patients that had a Summary of Care transmitted and manually reconcile this list with a summary report/audit trail provided by the HISP.

The MEDITECH SQL report will generate a denominator value but will always display a zero (0) numerator on the MEDITECH SQL report output. In order to generate a numerator and performance rate, reconciliation between the report and the HISP is required.

At this time, MEDITECH has not implemented SOAP/XDR Message Delivery Notification processing (MDN) for the CCD interface. I will show you how to calculate the numerator and performance rate while continuing to take into account Meaningful Use audit compliance and certification standards criteria.

Using the Code:

There are a several methods for integrating with MEDITECH.  I will introduce you to one common method which uses SQL Server Integration Services (SSIS) to import a CSV (Comma Separated Values) file into a table. A CSV file has a comma between each item of information it contains making it easy to import into a SQL table. We will calculate the numerator from the MEDITECH Summary of Care stored procedure based on the MEDITECH supplied denominator.

Step 1:

We will need a CSV file from the HISP that contains a list of all Summary of Care messages with the message disposition results.  The CSV file below contains all of the necessary information for calculating the numerator for audit compliance; you want to be sure you are prepared in case of an audit.

Example File:

 HISP Extract

When Summary of Care messages are sent from the XDR source (MEDITECH) to the XDR destination (HISP), there will be an identifier associated with the source message called the XDR Message ID. The XDR Message ID is used to link to the MEDITECH denominator data so we can calculate the numerator. This XDR Message ID will be included in the file from the HISP.


Step 2:

We will need to create an SSIS Project to get the data from the CSV file and insert it into a SQL table. This table will store the raw HISP data. We’ll name this table tbMU_SummaryofCareMessageDisposition_HispRawData. When importing data from an external system, it is always important to store the raw data in a transient table where the data is then processed and moved to the permanent table.

The SSIS project should contain one package with 3 steps that handles all the import, transformation and processing.

 Summary of Care SSIS Process

Screenshot 1

The first step in the SSIS package is called “Truncate Raw Data Table.” This step will delete all of the existing records in the tbMU_SummaryofCareMessageDisposition_HispRawData table.

Summary of Care Script

Screenshot 2

The second SSIS package step is called “Process HISP CSV File.” This step imports a csv file called Message_Status_Direct_Sample.csv and inserts its records into the tbMU_SummaryofCareMessageDisposition_HispRawData table.

Transitions of Care Logic


Transitions of Care Insert Table

Screeshot 3 & Screenshot_ 4

The third step in the SSIS package is called “Insert into Live Table.” This step reads all of the records in the tbMU_SummaryofCareMessageDisposition_HispRawData table and inserts them into the tbMU_SummaryofCareMessageDisposition table. 

Summary of Care code

Screenshot 5

This task executes the stored procedure spMU_SetSummaryofCareMessageDisposition. This stored procedure was created to take the HISP data from the SQL table and move to a table called tbCoreMeasure_SummaryOfCare. Importing the HISP data into a separate table allows us to separate the HISP data from the actual reporting data. Because the raw data from the HISP may contain information that is not well formed, we create a stored procedure that performs the validation prior to inserting it into the table that is used for measure calculation.

Transition of Care Table Creation

SQL Screenshot 1

Step 3:

We need to update the MEDITECH stored procedure that is used to calculate the Denominator and Numerator to include a link to the newly added HISP table.

Below is the code used to join to the new Summary of Care Message Disposition table. Records from these two tables match if the OriginalMessageID and TransExternalId column values are equal, the MessageReceived date is within the reporting period(or prior to Attestation Submission)and the DispositionActionMode is equal to “Delivered*” (Delivered* for this HISP means received).

 Transition of Care Delivery Stored Procedure

SQL Screenshot 2

Once we join to the table, we need to include one line of code in the CASE statement to determine whether the record meets the numerator condition. If the OrginalMessageID is not NULL then we have a match on the TransExternalId.

Transition of Care Confirmation 

SQL Screenshot 3

Below is the result when you run the Summary of Care report without code modifications.


SQL_Screenshot_OutputWithoutChange Screenshot

The following screenshot is the result when you modify the Summary of Care stored procedure to join to the new table.

Acmeware TOC SQL

SQL_Screenshot_WithChange Screenshot

Downloadable materials:

The downloadable materials are made of a Visual Studio 2008 BIDs solution including:

  •          The SSIS Package project
  •          The HISP Extract file
  •          The SQL database creation scripts



In summary, we have provided documentation and associated code that describes how to fully automate Core Measure 12 - Summary of Care/Transition of Care Reporting in MEDITECH Data Repository, eliminating the need for manual reconciliation.  You learned how to work with your HISP message disposition (to capture an MDN) report, import that MDN into your Data Repository and interface with the MEDITECH standard Meaningful Use SQL reports.

We used Visual Studio Business Intelligence Studio to create an SSIS package to import a CSV file, insert the data from that CSV file to a SQL table and joint to that table in the MEDITECH Summary of Care stored procedure. 


Created by Acmeware, Inc., All Rights Reserved. 


By Acmeware at 24 Mar 2015, 15:05 PM



Post a comment

Please correct the following:
Glen D'Abate
Ian Proffer
Jamie Gerardo
Geoff Grouten
Ken MacPherson
Alexis Donnaruma
Taylor Solari
Howard Minor
Misha Shatskiy
Company Information

rss feedRSS