what's a good enterprise document generation solution? (SQL Server, .NET)

Question

I wonder what "the big guys" use to generate documents. Like credit card statements, insurance documentation, etc. We're investigating changing solutions.

Here are the two that I know of:

• SSRS
• Crystal Reports

Neither of these seem to be able to handle widow/orphan control well. Crystal certainly can't deal with dynamically sized page footers (think page annotations). I've used InDesign many years ago, but seeing as how it's an Adobe product, I suspect it won't be .NET friendly. I think it has a .NET API, but it'd have to be really awesome to get over the adoption resistance. How about Adobe Acrobat? MS Word?

Requirements:

1. 1k documents per day, all with different values in fields
2. data driven from dataset or direct from SQL Server
3. dynamically sized footers
4. good widow/orphan control to minimize large blank spaces and single lines at top of page
5. save to PDF easily, ideally with no 3rd party tool in the middle
6. image files from blob or file link (not embedded in doc)

Answer 1

SQL Reporting services is the best!!!!

http://www.microsoft.com/sqlserver/2008/en/us/reporting.aspx

I have several deployments within the enterprise scale and my users LOVE it, especially since the release of report builder:

http://www.microsoft.com/sqlserver/2008/en/us/report-builder.aspx

Best of all it is free with SQL server!!

It fits everything you need and it is soooooooo easy to use and ties right into sharepoint.

Take a look at Crystal and Reporting Services comparison:

http://www.devx.com/codemag/Article/36397/1954?pf=true

Priority Order Due to page size limitations, conflicts can arise between the rules for keeping report items together. When conflicts occur, the following priority order is used to keep items together when rendering:

Lines, charts, and images.

Widow and orphan control.

Repeated column headers and row headers. Headers take precedence over footers. Inner repeated groups have priority over outer groups. Items where the RepeatWith property is set that are closer to the target data region have priority over items that are farther away from the data region.

Small report items, such as text boxes or rectangles, with an explicit KeepTogether property set to true.

Large report items, such as subreports or a non-innermost Tablix member, with an explicit KeepTogether property set to true.

Tablix data regions with an explicit KeepTogether property set to true.

taken from : http://msdn.microsoft.com/en-us/library/dd255248.aspx

Answer 2

If it's mass PDF's you're really after, you might check out http://www.dynamicpdf.com/, for projects like this we have been pleased with the outcome. They have a PDF WYSIWYG designer tool that you can use to build out the layout template of your PDF, along with the tool to merge the template with your dataset based on field to column mapping.

This tool saves hours of hand coding each element of a given PDF. It supports dynamic or static page headers and footers and deals with the complexities of page breaks and many other things. It has an API that can get into the guts of the document in case you need to make some very specific adjustments to the layout or detail at runtime.

Answer 3

I worked for a large Insurance Provider that deployed thousands of statements and what is commonly called collateral to print and our website monthly. We also used it to generate 5498 and 1099 documents annually. We used ABCpdf and it worked well. If you are using the MSFT stack and you have access to SQL Server 2005 or SQL Server 2008 then the best way to do it, in my opinion, is as follows.

1. Get a PDF generating package CSharp is free but doesn't give you a WYSIWYG. You really want to use one with a WYSIWYG capability though if you can.
2. Get the Adobe suite of products that manage PDFs. This product set will allow you to easily create templates for your PDF generating software. For example, we would download the 1099 PDF tax document from the IRS site and create a them based off of it using Adobe's software suite in about 2 hours.
3. Write a program that uses your PDF generating software and your PDF template to generate your PDF documents. We could generate 100,000 plus PDFs in around 2 hours.
4. Take the PDF documents and load them into your SQL Database in a table. For PDFs this seems to be the best way to store them as SQL Server can search them and they are easy to manage and maintain.