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.