Thursday, June 19, 2014 5:35 PM

DR Tip - Iatric DR SQL and Microsoft Reporting Services Tip ˜ Date Mnemonics

Written by Thomas Harlan, Jim McGrath; Reporting Services Team - iatricSystems

You’ve built a great query, wrapped it up in a beautiful Microsoft Reporting Services report and published it to the SSRS web portal, all ready for your end users to run anytime they want, with the parameters they want… perfection awaits!

Then the first user emails and says "how do I enter TODAY-1 in this report?"
And then the second user emails and says "how do I schedule this report to run for the last calendar month?"

And then you realize, to your horror, that Microsoft Reporting Services has a nice date-picker, but no equivalent of the MEDITECH Date Mnemonics, which make entering date parameters in NPR easy-peasy… and to schedule a report to run monthly, you have to set up a schedule for each month, with hard-coded dates!

That is when the Bill Gates dartboard comes out… but do not despair, we have a solution for you!

Note that this solution is specific to Microsoft Reporting Services. You can do the same in Crystal Reports/Business Objects but you can’t display the results of the date mnemonic like we do here.

First, we need a way to convert a date mnemonic string (like MB-1) into an actual date. Second, we need to pass that real date into your stored procedure that executes the query to get data for the report.

We wrote two versions of the date conversion code:

MIS_iatric_DateInterpreter_fn [code]
MIS_iatric_DateInterpreter_sp [code]

One is a function you can call inside your own queries, if needed, and the other is a stored procedure to call from the Parameter setup screen in Reporting Services. Both are available on the Iatric Systems Tip page. Both scripts assume you have a custom database container on your DR server(s) called zcus. If you have a different database container for custom code, you’ll need to update the script accordingly.

The function and stored procedure support these mnemonic patterns:

Mnemonic	Pattern Notes
T	Today (t=today or datadate if passed, t+n, t-n)
W	Week (w=today or datadate if passed, w+n, w-n)
WB	Week Begin starting Sunday (wb, wb+n, wb-n)
WE	Week End ending Saturday (we, we+n, we-n)
M	Month (m=today or datadate if passed, m+n, m-n)
Y	Year (y=today or datadate if passed, y+n, y-n)
MB	Month Begin (mb, mb-n, mb+n), relative to current date
ME	Month End (me, me+n, me-n), relative to current date
YB	Year Begin (yb, yb+n, yb-n), relative to current date
YE	Year End (ye, ye+n, ye-n), relative to current date
FYB	Fiscal Year Begin (fyb, fyb+n, fyb-n), relative to current date
FYE	Fiscal Year End (fye, fye+n, fye-n), relative to current date
WM	(wm,wm+n,wm-n, +/- n is months) Week of the Month (3rd Tue, 2nd Fri, 4th Thu, 5th Wed) returns date for @WeekNo for @sDayOfWeek in the month of current date

Once the function and stored procedure are in place, you can update your MS Reporting Services report like so:

Step	Description	Screen Shot(s)
	Update the report (RDL) to include two new parameters: cFromDate and cThruDate with a data type of Text (right-click Parameters > Add Parameter).
	In the Parameter listing use the Arrow icon on the Report Data header to move the two new parameters up before dStartDate and dEndDate. You want your date mnemonic fields to be active before the actual date parameters are.
	Add a new Dataset to the report (Right click Datasets > Add Dataset) to convert cFromDate into dStartDate. On the Query screen, name it ConvertFromDateDs. Set the Data Source to reports DR connection. Query Type is Stored Procedure. Then select or pick MIS_iatric_DateInterpreter_sp. On the Parameters screen, set the Parameter Value for the @cDateIn parameter to [@cFromDate]. Click OK when done to save this dataset.
	Add a second Dataset to the report (Right click Datasets > Add Dataset) to convert cThruDate into dEndDate. On the Query screen, name it ConvertThruDateDs. Set the Data Source to reports DR connection. Query Type is Stored Procedure. Then select or pick MIS_iatric_DateInterpreter_sp. On the Parameters screen, set the Parameter Value for the @cDateIn parameter to [@cThruDate]. Click OK when done to save this dataset.
	Now change the parameter properties for dStartDate (right-click dStartDate > Edit Parameter). On the Available Values screen, select Get values from a query. Pick ConvertFromDateDs as the Dataset. Set the Value and Label fields to Result. Switch to the Default Values screen and select Get values from a query. Pick ConvertFromDateDs as the Dataset. Set the Value field to Result. Click OK to save your changes.
	Make the same changes for dEndDate (right-click dEndDate > Edit Parameter). On the Available Values screen, select Get values from a query. Pick ConvertThruDateDs as the Dataset. Set the Value and Label fields to Result. Switch to the Default Values screen and select Get values from a query. Pick ConvertThruDateDs as the Dataset. Set the Value field to Result. Click OK to save your changes.
	You’re done!

Now when you run your report, the cFromDate and cThruDate fields — and the mnemonics entered there — will control what values appear in the dStartDate and dEndDate fields. Those two fields are then passed to the query when it executes.

DR Tip

Note that this approach does not require changing your query/stored procedure at all (assuming it has parameters for the date range) and leverages the pre-built function and stored procedure at the report level.

Extra Credit!

Try out hiding the dStartDate and dEndDate parameters (each parameter has a Visibility setting).
Expand the function to use additional mnemonic patterns of use to your site!

Cheers, Jen Kelly and Thomas Harlan