SQL Tip -- Multi-value Parameters for SSRS

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:

1. We create a simple stored procedure to get the list of values to present to the user.
2. 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.
3. We attach both stored procedures to the SSRS report.
4. 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:

SELECT LOC.LocationID

,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

AND

LOC.Active = 'Y'

The key points in this code are that:

1. We only see Active Locations.
2. 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.
3. 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

AND

ADV.FacilityID = @cFacilityID

AND

(

@cLocations = 'ALL'

OR

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:

=Parameters.cLocations.Value

To this:

=Join(Parameters.cLocations.Value,"|")

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.

Extra Credit

Go back to the IatricLocationList stored procedure, review the output and suppress Location IDs from the list that are not useful to your customers.

(end)

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.

To subscribe for email notifications for new Report Writing classes, please follow this link:

http://www.iatric.com/Information/Classes.aspx.

For more information, please contact our NPR report writing team at reportwriting@iatric.com.