Unattended SSRS generation of PDF files

[Kristen]

I have is an SSRS report which produces a "report" for a single record (by ID) and some related child-table records.

What I now want to do is have this report produce a PDF file (and store it on a fileserver/folder somewhere).

The Header Record has a column for Status which would allow a scheduled task to loop around anything where Status="UNPrinted", generate a PDF file, and then change the Status="Printed" - it needs to be unattended.

I'm hoping all I need is some String and Gum, or an APP that doesn't require a rocket scientist to deploy it! nor cost-a-plenty

My Googling turned up stuff about the Reporting Services Execution Web service, but all the examples had enough DotNet type code to put me off - I don't have much knowledge of the newer C# etc. type languages, and I'm sure it will need plenty of "assembly modules" and "error catching code" and stuff that only experience brings. Hence was hoping for something more "oven ready" than that

Only commercial thing I turned up was licensed based on number of PDF file per day, and I probably need hundreds per day, but only for one day of the month!, so that product looked expensive.

[James]

If memory serves, you should be able to create a data driven subscription (enterprise only I think) where you can tell it to write to a file share, specific file name, export file type (PDF), etc. You can set it to run every X minutes or another schedule. You can even trigger it to run using SQL by grabbing the subscription GUID and then kicking off the job. I have not done this for years but I recall that is how I did it. I also built the C# stuff and it's a pain involving something that has to be deployed to run and then you get into the permissions garbage associated with that.

The data driven subscription thing seems like it would work for your needs.

Again, I have not done this for years and I don't remember what I had for breakfast so take it for what it's worth.

[robert_volk]

Your memory is correct, data-driven subscription is available in Enterprise Edition SSRS only, and is the only supported way to have full control over the file name.

The SSRS web GUI limits your schedule frequency to 1 minute granularity, anything more frequent would need a manual SQL Agent schedule to be applied to the job. Not hard to do, but will get deleted if you modify it through SSRS web GUI.

There's no way I'm aware of for SSRS to process multiple rows or report pages into separate PDF files in a single scheduled run. Your procedure would have to be able to return the next item in sequence so that the scheduled subscription can process it on the next run.

You can however split a PDF into separate files with a subset of pages:

PDFtk Server

There's a documentation link in there for the command line arguments. (The free GUI tool DOES NOT split, and it's not scriptable either). Unless your report generates a fixed number of pages per ID then this will probably not be useful.

Another option you can try with PDFTK is to use a static PDF form with fields that can be merged, and generate an FDF data file to merge with it. I've never done this but have been looking at it as a possibility. I'm pretty sure the page layout won't be too flexible though.

If some or all of this is not feasible to you, you can use some command-line utilities to generate PDFs from HTML or XML sources. I've done HTML-To-PDF with this:

https://wkhtmltopdf.org/

I mentioned my difficulties with it before:

App / Report Builder

I have had some experience converting a very basic HTML table to PDF via https://wkhtmltopdf.org/, which was one of the better utilities I've found. You mentioned in your earlier thread "that leveraging the CSS in the current web pages may convert readily to PDF". My experience does not bear that out, and my layout was VASTLY simpler that what you've described here. HTML is simply not a page-based layout engine, and CSS is not up to the task either. Any @print directives you place are at the m…

I have since then had some better success with it, CSS worked a little bit better this time around. The HTML was a bit simpler too, and I used a different method to generate it.

[robert_volk]

And I just learned of this today:

alvarcarto/url-to-pdf-api

Probably doesn't help you from the SSRS side but may be a better alternative than the HTML-to-PDF options I described.

[Kristen]

@James

You can even trigger it to run using SQL by grabbing the subscription GUID and then kicking off the job

That would do me ...

@James

I also built the C# stuff and it’s a pain involving something that has to be deployed to run and then you get into the permissions garbage associated with that.

Thanks for confirming that. I have zero skills in that area, and could waste a huge amount of time figuring out the "how" part.

I can't help thinking that I should just outsource this job, but little enough time to Spec it, let alone find someone suitable

@robert_volk

data-driven subscription is available in Enterprise Edition SSRS only

That's a bind then, not worth me spending the money on Enterprise just because this one feature would be handy ...

@robert_volk

Your procedure would have to be able to return the next item in sequence so that the scheduled subscription can process it on the next run.

That's fine, in practice I don't suppose Accounts can generate more than one invoice within a minute, so a scheduled task that says "Anything available" and then "Print the first one, and change its status to DONE" would be just fine.

@robert_volk

I just learned of this today

Thanks for the thoughts on HTML-to-PDF routes. They are still very appealing to me, as it plays to my core skills. Not too bothered about "HTML" to PDF specifically, any markup to PDF would do me - so maybe there are better starting points? (I'll elaborate below)

I've spent some time trying Google Searches - along the lines of "Dynamically Generate Invoice as PDF file" and pretty much all the results start from HTML, or PHP-generating-HTML, for conversion to PDF.

So I might spend a bit more time trying the HTML->PDF route before I bite the bullet on SSRS (this just comes back to my skills being SQL and HTML-generation, not SSRS).

Re: Alvar Carto url-to-pdf-api

It kicks off with stuff like:

To get this thing running, you have two options: run it in Heroku, or locally.
The code requires Node 8+ (async, await).

being an old fart I have no idea what that means ... I under standing things like "Run this XXX.msi" - but I can see a whole lot of time spent just figuring out HOW to do the install, before I get anywhere near discovering whether it is fit for purpose. Anything I've ever come across that targets Unix starts off like that "Only rocket scientists need apply" type thingie.

However, the Example description talks about the fact that it uses Headless Chrome - can't I just do that, without installing anything else?

This bug report seems to be suggesting that that is possible (albeit with some "coming soon" / "Wishlist" features) [using Headless Chrome]
https://bugs.chromium.org/p/chromium/issues/detail?id=603559

I can definitely render a nice looking Invoice, with all the sections and Brouhaha that are needed, so that's a start. However, that is just as a single, scroll-able, page, and I think I'm likely to need:

• No regular Browser Physical-page Header/Footer stuff (as can be included on printouts, depending on current browser settings)
• Page Breaks
• Page numbers (and Total Pages would be handy)
• Sub totals and Carried forward totals is fairly important (but not essential)
• Widow and Orphan control is fairly important (but not essential)
• Column heading repeat, on additional pages, would be nice

If all those things are NOT possible with SSRS then I might as well go ahead with Browser-route and HTML-to-PDF, even if they are not solvable there either!

I read someone saying (and I think I mentioned in a thread here) that they achieved Sub Totals by including a hidden sub-total on every row [i.e. in a Table/Grid] and then using Javascript to "show" the relevant ones at just the page break(s). Most of the HTML-to-PDF solutions support Javascript (certainly the "headless Browser" ones), so that might be a solution to that problem, and quite possibly to the Widows and Orphans one too.

That would leave me with the issue

Alternative Document Generation Routes:

I could generate XML or maybe RTF / Postscript (provided that they don't need integral layout-calculations for word-wrap etc.) or perhaps a publishing markup language like TEX which would then (Batch?) convert to a PDF document

[Kristen]

P.S. I stumbled over this very useful Stack Exchange post. The Author has put together a short review of each of the tools he has tried. Its got a recent date on that thread too.

https://softwarerecs.stackexchange.com/questions/45903/how-can-i-convert-html-with-css-to-pdf

[robert_volk]

If you're already able to generate HTML more-or-less the way you want it, I don't think PostScript or RTF are worth the hassle, they don't add anything to the process of ultimately turning it into a PDF. I don't know anything about TeX to say if it's better, I've never heard that it can do running totals though.

All of the page breaks, number, subtotals etc. are available in SSRS, and will be much easier to do there than in HTML.

I mentioned in my earlier thread on PDF/HTML that SSRS is not hard to learn and is likely the best route if you need well formatted PDFs with the features you mentioned. If you're concerned that it will take too long to learn for just this project then you're selling the time investment short, you can do a lot more with it than just this one thing.

Regarding the node.js stuff, it's just a module to install in IIS or whatever web server you're using, documentation should be on the Node website, take you 1-2 minutes to install. The headless chrome part just renders the PDF, you still need a web server to generate the HTML. Otherwise it's just an HTML file, and you can use wkhtmltopdf on that.

[Kristen]

@robert_volk

PostScript or RTF [and TeX] ... don’t add anything to the process of ultimately turning it into a PDF

Thanks, something else I will stop considering.

@robert_volk

SSRS is not hard to learn ... If you’re concerned that it will take too long to learn for just this project then you’re selling the time investment short.

Very valid point. However

In the last 10 years I have never needed anything other than HTML. In the 10 years before that I rarely needed anything other than HTML, although we did do some stuff in Access and e.g. Crystal Reports. In the 10 years before that!! everything was "specify the size" and the layout was much more prescriptive. That said, everything was in fixed-spacing fonts, so I could make the field 10 characters wide and not have to worry about "What if the operator enters All Capital W's"

Where I am at now is that I save DEV time by offloading all the formatting (to the browser). We have almost nothing that is end-user facing, and absolutely nothing where Marketing is allowed to say "This has to be pixel perfect" (along with "we don't care if flow of the APP is unusable by the user, only that it looks beautiful" <Sigh!>)

So all this "place a field on the page, and size it, and set all sorts of attributes about it" is a development time & budget killer for me. If I create an HTML table with some columns I (mostly) don't care how it is formatted, how wide the columns are, what word wrap occurs, which rows spill onto multiple lines. The Browser's best-guess for all that is fine by me. Where it matters enough (or the default is goofy - e.g. because sometimes a column is blank, and then the Browser makes the column ridiculously narrow) I will set a Minimum Width. That's it.

{HeadingSection}
<table class="MyTableDisplayClassName">

{BodyRowRepeatSection}
<tr>
<td>{MyColumnName1}</td>
<td>{MyColumnName2}</td>
...
</tr>

{FootingSection}
<table>

is basically all I need.

If my Column changes from VARCHAR(10) to VARCHAR(200) [facetious example, of course] I don't have to change any of the presentation side at all, and I rarely have to change the SProcs either (unless that column is an @Parameter - and even then all the straightforward CRUD SQL is mechanically generated). I haven't got the problem of a 10-char field being all Capital-W's [no whitespace], the Browser will just make it fit. I have a cascading style sheet, so I don't have the problem that I accidentally set a Font Face on something and then spend ages trying to figure out what went wrong - or change my mind and then have to Find & Fix loads of individual stuff case-by-case, In our HTML I have a very narrow pallet of CSS Classes that are available for "additional window dressing" on the data. Seems to me that in SSRS I can have any Font, Size, Colour, Adornment that I like ... might well be that I can streamline that? but if that is the case its one more thing that I have to learn, and based on previous experience I'll find that out half way through and then have to go back and "improve" everything that went before.

You are right, SSRS can do a lot more than jsut this one Invoice PDF but I'm really sceptical that it is going to solve other, future, problems for me.

For sure if I want to get my data onto a sheet of paper, or equivalent in PDF, I do need some help. Right now I'm doing an Invoice, so that qualifies but I just can't think of other documents where that will be the case (otherwise I would have been having that problem repeatedly for the last 10 years )

The other side, which I know you know, is that leaning any new tool is a horrendous commitment of time. I've knocked up an Invoice in SSRS without too much difficultly, and it looks OK-ish, but I have no idea whatsoever how much time I should budget to get it working "nicely", so the impact on other "promises" made to users is very uncertain - its why I've sat on the fence for so long about "Can we leverage HTML for this job".

So what I probably should be doing is outsourcing this (if anyone has time & skill by all means PM me )