Microsoft SQL Reporting Services – Enabling Service Desk Express Print Preview

If you are using Microsoft SQL Reporting Services as your enterprise reporting tool you will probably have already developed a bunch of reports that query the Service Desk Express database. Developing reports for use with Service Desk Express and even completely migrating away from Crystal is easy enough until you come to enabling the print preview type functionality.

Sure you can generate an Incident Report that takes a parameter of IncidentNo and returns all the data about that incident in a perfect format, but how do your support staff launch that report from a given incident ticket?

In a previous post I discussed the use of a System Parameters module, and in this post I am going to use that module in conjunction with a calculated field to enable the print-preview functionality without the use of the Program Launcher application.

Solution

Assumptions:

• We have already created an appropriate incident print preview report called “HDPrintPreview” that accepts a parameter of IncidentNo.
• That report is published in a directory called “SDE” on your report server.
• We have a System Parameters module as discussed in the previous post. If you don’t have this module this technique will still work but it means that if you need to change the name of the report or report server then you need modify the calculated field which can only be done out of hours.

Having made these assumptions we need to create a new system parameter called “Incident Print Preview Report URL” with a parameter value of:

http://<ReportServer>/ReportServer?%2f<Report Directory>%2f<Report>&<Parameters>=

where <ReportServer> is server where Microsoft SQL Reporting Services is installed, <Report Directory> is the directory on the report server where the report is located, <Report> is the name of the report, and <Parameters> are the parameters that need to be passed to the report.

So, let’s say that we have a report server called GANDALF (sorry all mine are named after Lord Of The Rings characters), your parameter value would be:

http://gandalf/ReportServer?%2fSDE%2fHDPrintPreview&IncidentNo=

So far so good…

Now using DB Admin we need to add a calculated field to the Incident module called ReportURL with a formula of:

(SELECT "PARAM_VALUE" + CAST("BASE"."SEQUENCE" AS NVARCHAR(10)) FROM "_SMDBA_"."TBL_PARAMS" WHERE "PARAM_NAME" = 'Incident Report URL')

as shown below:

Report URL Field

Basically, all this calculated field does is append the Incident No to the end of the URL/Parameter Value we specified earlier so that when, for example, you open Incident 1234, the ReportURL field will show:

http://gandalf/ReportServer?%2fSDE%2fHDPrintPreview&IncidentNo=1234

So now for the final bit to make it all work.

Add the ReportURL field to your selected Incident form using form customisation and also add the Display Link button to the form as your new print preview. The Display Link button is available via the buttons menu in form customisation.

Save the form and open an incident using the saved form and click the Display Link button you added. You should find that the SQL Reporting Services report should open automatically, showing the appropriate incident details.

As always feedback welcome – hope it proves useful.