Joe Cocuzzo, Senior Vice President – Report Writing Services
A customer recently requested a billing report they could run for patients interested in knowing how much they had paid to the hospital during the calendar year. Trying to assemble this information using the application or a compiled report was proving tedious.
In the report I wrote for the customer, I opened prefixes so that I could have MPI lookup functionality in the C/S BAR report. For this tip I will accomplish the same thing using a (shudder) fragment.
For any of you who lack experience writing fragments, this tip provides a reasonably straightforward example. If you are familiar with fragments, this tip includes some other techniques that you might find useful.
What we need is a report to do a “unit number” or “mpi” style lookup, where you search by name or unit number and end up selecting a patient rather than a visit. MRI.PAT reports can be written to do this, so we will start by writing a “main” report in MRI PAT which will just prompt for the patient and the date range. Once the “main” report has run, we will have a medical records urn to pass to a BAR fragment report that can go get the self pay payment transactions across all the patient’s accounts (no matter how old), provided the payment occurred in the date range.
Here is the approach for the “main” report:
You’d think you could just do this in your report for a nice MPI lookup, just as you can select on “urn” in ADM.PAT:
Unfortunately you have to resort to this kind of trickery to get an MPI lookup in MRI:
There is no way to figure this out via the data definition, but we have a handy table of how to do lookups in common applications.
We set up the main report with a PAGE sort on medical.record and build the HP1 region with the hospital name and address and the patient unit number and name and the date range selected by the user at run time
The “REG” type banner seems a little inappropriate for a patient statement, so we will build some centered text of the hospital information in the header instead.
If we put our hospital info in computed fields, we can make it bold or larger or both, and if we set the JFY (justification) to C and make the length equal to the report width we can have nicely centered text without fussing.
The date range is prompted for with two computed DAT=DATE fields with an EQ operator. I use EQ because in C/S an IG field is not required, but an EQ is and I want to make sure I get a date range. Because the VAL=exactly what the user provided the EQ is always true.
So, our “main” report provides the page header including patient information and the column headers:
Now we need to write a BAR.PAT fragment report to list the self pay payments across all visits of the selected patient, provided the payment was posted in the selected date range. If you have patients who like to write checks on December 31st at 23:59, you might need to select an end date beyond the end of the calendar year to allow for travel.
We want our report to go through transactions for all accounts of the patient, so we pick a detail segment of “transactions” and use the “mpi.x.” (MAGIC report is the same, except the detail segment is called bar.acct.transactions and the index bar.acct.mri.x for the purposes of Page 1).
The main report controls the page header, page characteristics, and whether there is a banner or report trailer. The title of the report comes from the main report, so all the highlighted areas on the fragment have absolutely no effect.
We need to create a sort trailer on the fragment to total the amounts paid. Setting Report Trailer to Y in a fragment will create a TR in the fragment picture but that TR region (from the fragment) will never actually print. Rather than try to pass a total value back to the main report for a TR defined on the main report (which would print), it is easier to add a sort trailer (TK1) where your total will appear in the fragment just fine, simply by putting the txn.amount field in TK1.
We are going to pass three values in slash to our fragment report:
We will put @medical.record from the main report into /R.FRAG.ARG1
We use it to select against @master.pt.urn which is the SAME FIELD, apparently named by a renegade MEDITECH developer who says “top” when others say “main” and “int.number” when others say “adm.urn.”
We also need to “hardcode” three additional selections:
Setting txn.class to “O” will skip all the “C” (charge) transactions
Setting txn.type to “R” will get payments only R = payments and “P” equals Refunds, as renegade MEDITECH developer strikes again, because he says “Prefunds” and “Rayments.”
Note that we select against the “self pay insurance” using the MIS parameter value, this means the report will work for you whether you use “SP” or “SELF” or whatever.
The fragment picture looks like this:
To connect the reports we go back to the main report and add a call to Z.rw.fragment. We load /R.FRAG1, /R.FRAG2, and /R.FRAG3 and then call the fragment. The D line from the main report will not print because Z.rw.fragment returns nil and therefore our LC is nil.
Our patient statement now looks like this
Possibly you might want to flip the negatives for the “Rayments,” to do this you need to create a computed field in the BAR fragment as follows:
A copy of the two fragments MRI.PAT.zcus.is.self.pay.statement and BAR.PAT.zcus.is.self.pay.statement.fr has been uploaded to both our MAGIC and our C/S NPR report library. The reports will work fine in 6.0 also, but in 6.1 BAR goes off to MAT land, so you’d have to do something in RD or SQL instead.
Make sure you change the PHA database in the “check” macro to match YOUR HOSPITAL, and the generic mnemonic to match what you use. Otherwise the utility is generic and will work at any MAGIC site.
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:
- We create a simple stored procedure to get the list of values to present to the user.
- 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.
- We attach both stored procedures to the SSRS report.
- 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:
,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
LOC.Active = ‘Y’
The key points in this code are that:
- We only see Active Locations.
- 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.
- 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
ADV.FacilityID = @cFacilityID
@cLocations = ‘ALL’
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
Set the cFacilityID parameter to use the already existing FacilityID parameter from the template:
Then add IatricDischargesByDateAndLocation
Datasets > Right-click > Add Dataset
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.”
Switch to the Available Values pane. Select the LocationDs dataset and pick the appropriate fields:
Switch to the Default Values pane, and set the default value to “ALL:”
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:
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.
Go back to the IatricLocationList stored procedure, review the output and suppress Location IDs from the list that are not useful to your customers.
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:
For more information, please contact Karen Roemer at 978.805.3142 or email
This article originally appeared in the December 2014 issue of Iatric Systems Updates! newsletter.