SQL Tip: Auto-printing from SSRS to a Networked Printer

This is probably our second-most requested tip – how do I emulate, from SSRS, the standard MEDITECH scheduled auto-print to a spooled printer functionality?

A Bad Idea

Before we show you how to auto-print to a spooled printer, you're going to get a talking to! Why? Because auto-printing to a networked printer is a bad idea: 

1. Unless you have locking output trays on your networked printers, scheduled printing to a device is a HIPAA violation waiting to happen. Almost any printout you have in modern healthcare contains PHI, and if someone can pick up the wrong report from a shared printer, your organization may find itself in violation without even realizing it.
2. The printout is stale as soon as it’s printed. Unless the end-user reviews the report footer or header carefully to see what date/time it was printed, the user may attempt to take action using data that is hours or even days old.
3. Auto-printing wastes paper and electricity, and most auto-printed reports are never used.
4. Auto-printing requires more management — of the schedules controlling the auto-printing, of the print queues used to drive it, and of the printers themselves (particularly if they move, or change IP address, or are decommissioned).

It is optimal to set up all of your SSRS reports to be run on-demand by your end-users, whenever they need them. Then they only get the data when needed, it’s immediate and up-to-date, and they can act on it appropriately and at the right time. Additionally, you waste a lot less user-time, paper, electricity, and management overhead.

If a user does need a report on a schedule, it is far better to set up an SSRS Subscription that generates an Excel spreadsheet (but not recommended because Excel is insecure, corrupts data, and has row limitations that might inadvertently truncate your dataset) or PDF (better option) and delivers via email. Or, at most, to an AD-security controlled file share.

However, if none of that matters, and you still must have your scheduled, spooled printing, here’s how you can set up SSRS to auto-print to a network printer… 

You Will Need: 

• A Windows Server on which you can install software, set up shares, configure security, and manage the Scheduled Task Agent
• One or more printers that can handle directly printing a PDF
• SSRS Reports that can be scheduled
• Access in SSRS Report Manager to set up Subscriptions 

Then… 

1. Confirm that the printers in question support Print-Ready PDF (or direct PDF) printing:
   • Go into the printer’s control panel, print the Configuration report. Look for a Personalities section (on an HP printer) that shows PDF.
2. On a Windows Server, set up a file share for each printer to which you’re going to send scheduled reports. Make sure this share has read/write permissions for the AD service account running your SSRS instance. We’ll call this the “printer control share.”
3. IMPORTANT NOTE: Put these printer control shares in a folder structure that is easy to maintain with short names. No embedded spaces or parentheses, please! Something like:

D:\Printers\(PrinterName)

Which would be shared like this: 

\\(Servername)\Printers\(PrinterName) 

4. On the same Windows Server, confirm that it has lpr installed by reviewing this article: https://technet.microsoft.com/en-us/library/cc731857.aspx.
5. Document the IP address/DNS name of each printer and the file share from which they’ll be getting files. A handy Excel spreadsheet will do for this. Or log them in your CMDB.
6. If the DNS host names of the printers are not short (8 characters at most), this is a good time to make them short and sweet.
7. In each printer control share folder, create this very simple batch file as (printername).cmd 

FOR %%f IN (*.pdf) DO (lpr -S printerdnsname -P auto %%f & del %%f) 

8. Where you replace printerdnsname with the printer DNS name you documented. So if my printer was named “Scribe” on the network, I might have this printer control share:

\\QueueServer\Printers\Scribe 

Which is this folder on the QueueServer: 

D:\Printers\Scribe 

Where that folder contains this command file: 

scribe.cmd 

Containing this text: 

FOR %%f IN (*.pdf) DO (lpr -S scribe -P auto %%f & del %%f) 

9. Now find some short PDF files and put them into your printer control share.
10. Run the .cmd file from the command prompt, in the printer control share.
11. If your printer supports Direct-PDF/Print-Ready printing, all of the PDFs should print out, in order, on the printer. 

IMPORTANT NOTE: This batch command file is super simple. For each PDF, send it via lpr to the printer, then delete the file afterwards. If you’re a DOS batch command language wizard (and yes, such people exist…), I am certain you can improve it. 

12. Alternatively, if you already have a Windows Server acting as a Print Manager, where each of your PDF-capable printers has a printer queue already set up, then you can use this for your batch command instead: 

FOR %%f IN (*.pdf) DO ( move %%f \\printserver\queue ) 

13. Which will have the same effect.
14. Now that we’ve confirmed the printer can handle a set of files dispatched to it via lpr or move, we need to set up a Scheduled Task to fire up this command file on a regular basis:
15. Go to Start > Administrative Tools > Task Scheduler (or whichever path works on your server).
16. Under Actions, click Create Task. (We don’t want a Basic Task because, well… it’s too basic!)
17. Provide a suitable Name, like “Scheduled printing on Scribe.”
18. Add the path to the Excel or CMDB or wiki entry that documents your scheduling printing setup to the Description.
19. The Security settings may be problematic for you, depending on your AD setup and any GPOs that may be in place. However, you do need to “Run whether user is logged in or not,” and you may need to run with “Highest Privileges.”
20. On the Triggers tab, click New.
21. Under “Settings,” choose Daily.
22. Set the “Start Time” to 12:00:00 AM.
23. Under Advanced Settings, set the Task to “Repeat Task” every 15 Minutes, for a duration of 15 minutes.
24. IMPORTANT NOTE: If you want the task to run more often than every 15 minutes, you can (after picking 15 Minutes from the drop-down list) change the number in the field to 10 or 5, as you please.
25. Also set the “Stop Task if runs longer than” to 15 minutes (by picking 30 Minutes, and then editing the entry).
26. Make sure the Enabled checkbox is checked.
27. Click OK to add the Trigger.
28. Now click on the Actions tab.
29. Click New.
30. Choose Start a Program for the Action field.
31. Browse to the .cmd file for this particular printer schedule (in our example, it would be the scribe.cmd in the \\QueueServer\Printers\Scribe share folder, whatever folder that is…).
32. Set the Start In field to be your printer control share folder, like this: D:\Printers\Scribe\
33. Click OK.
34. Click on the Settings tab.
35. You want to make sure the following settings are check marked:
1. Allow task to be run on demand.
2. Stop the task if it runs longer than 1 hour.
3. If the running task does not end when requested, force it to stop.
4. If the task is already running, then the following rule applies: Do not start a new instance.
36. Now – finally – you can set up a Subscription in SSRS to fire off a report as a PDF and drop it into your printer control share.
37. In Report Manager, switch to Detail View.
38. Find the Report you want to schedule. Then click on the Properties icon on the left side of the listing.
39. Once the report properties have opened, click on the Subscriptions tab.
40. Set the Delivered By field to Windows File Share.
41. Enter the Path for the file to drop to — this will be the printer control share (as the UNC path version) — that you documented above.
42. Enter the Credentials used to access the file share, if you have not already set the file share to be accessed (read/write) by the AD service account running SSRS on the Report Manager server.
43. Set the Overwrite option to Increment File Names as newer versions are added.
44. In the Subscription Processing Options section, set the schedule you want to use for report generation by clicking the Select Schedule button.
45. In the Report Parameter Values section, fill in the parameters you need. Note that if you’ve got Date parameters, you need to either (A) set up each and every date range as a separate subscription, or (B) set up MEDITECH-style date mnemonics in your Report. See this Iatric Systems RWS Blog tip for how to do that.
46. Click OK to save your subscription.
47. Test the subscription! Generate some PDFs into the printer control share; confirm they print!
48. Have two lattes. 

Notes 

• Getting security permissions correct for all of this will be the biggest hurdle.
• Second will be getting your Task Scheduler set up properly.
• Finally, dealing with the timing of how often the print job runs. 

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