14

I know this is a little subjective, but I'm looking into the following situation:

I need to produce a number of documents automatically from data in a SQL Server database. There will be an MVC3 app sat on the database to allow data entry etc. and (probably) a "Go" button to produce the documents.

There needs to be some business logic about how these documents are created, named and stored (e.g. "Parent" documents get one name and go in one folder, "Child" documents get a computed name and go in a sub-folder.

The documents can either be PDF or Doc(x) (or even both), as long as the output can be in EN-US and AR-QA (RTL text)

I know there are a number of options from SSRS, Crystal Reports, VSTO, "manual" PDF in code, word mail merge, etc... and we already have an HTML to PDF tool if thats any use?

Does anyone have any real world advice on how to go about this and what the "best" (most pragmatic) approach would be? The less "extras" I need to install and configure on a server the better - the faster the development the better (as always!!)

Findings so far:

Word Mail Merge (or VSTO) Simply doesn't offer the simplicity, control and flexibility I require - shame really. Would be nice to define a dotx and be able to pass in the data to it on an individual basis to generate the docx. Only way I could acheive this (and I may be wrong here) was to loop through controls/bookmarks by name and replace the values...messy.

OpenXML Creating documents based on dotx templates, even using OpenXML is not as simple as (IMHO) it should be. You have to replace each Content control by name, so maintenance isn't the simplest task.

SSRS On the face of it this is a good solution (although it needs SQL Enterprise), however it gets more complicated if you want to dynamically produce the folders and documents. Data driven subscription gets very close to what I want though.

Winnovative HTML to PDF Convertor* This is the tool we already have (albeit a .Net 2.0 version). This allows me to generate the HTML pages and convert those to PDF. A good option for me since I can run this on an MVC3 website adn pass the parameters into the controllers to generate the PDF's. This gives me much finer-grained control over the folder and naming structures - the issue with this method is simply generating the pages in the correct way. A bonus is that it automatically gives me a "preview"...basiclly just the HTML page!

BlueChippy
  • 5,935
  • 16
  • 81
  • 131
  • Is it really a [Data-Driven subscription](http://msdn.microsoft.com/en-us/library/ms159150(v=sql.100).aspx) in SSRS (which necessitates the need for Enterprise Edition) or just an on-demand report? I *think* you can use the embedded RS client to render directly to PDF or Word or just present the report to the user in your web page and allow them to direct the save actions. – billinkc May 09 '12 at 15:40
  • For a single report this would be great, however the end result (in this case) is a hierarchy of multiple levels of reports. Basically, we need to produce specific reports for all our clients, their shareholders, board members and subsidiaraies - including THEIR shareholders, board members etc. for a regulatory body. There are rules on at what level each one is reported etc. – BlueChippy May 10 '12 at 06:10
  • Nice you edit the original post as a reference. Did you happen to check out the capabilities of technologies like Silverlight and WPF? – Steffe Dec 11 '12 at 10:18
  • No, didn't go beyond the list above...basically went with what we already had, but upgraded to .Net 4.0 version. – BlueChippy Dec 11 '12 at 10:25

3 Answers3

4

Office OpenXML is a nice and simple way of generating office files. XSLT's can be strong tool to format your content. This technology will not let you create pdf's.

Fast development without using any third party components will be difficult. But if you do consider using a report server, make sure to check out BIRT or Jasper.

To generate pdf's I have been using the deprecated Report.net. It has many ports to different languages and is still sufficient to make simple pdf's. Report.net on sourceforge

Steffe
  • 348
  • 3
  • 16
2

I dont think SQL Server itself can produce pdf files. What you can do is, as you mentioned, install an instance of SSRS and create a report that produces the information you need. Then you can create a subscription to deliver your report to where you want, when you want.

Here is an example of a simple subscription: enter image description here

Diego
  • 34,802
  • 21
  • 91
  • 134
  • Can SSRS create a dynamic folder structure/filename as well? – BlueChippy May 07 '12 at 06:44
  • yes, on a regular subscription, you can create dynamic filenames (see the overwrite options) on the print screen. If you need something more elaborate, like dynamic folders, you need a data_driven subscription – Diego May 07 '12 at 12:12
0

Go for SSRS only if you are OK with setting it up on a server and there is a definite need for schedule reporting and complex reports.

If you have code for manual PDF/docx generation, I would suggest to go ahead with it. Hopefully the complexity of its code is not a matter to you.

I have used both in separate scenarios. We used excel classes and objects from .NET for a minimal reporting from a web application.

But went for an elaborate reporting scheme for a system which required 1000s of reports to be generated in a scheduled manner and delivered to selected set of people.

Vivek
  • 259
  • 2
  • 16
  • Thanks - no scheduling is requried, its more a case of being able to replace stubs with data in a "template" of some kind and then save to specific locations/names. – BlueChippy May 03 '12 at 12:42