Software

Ask something
Discussion Locked

Database/Reporting Tool Selection

0 helpful
7 replies

by twells

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I need assistance picking out a database that will match my reporting needs. I have designed a product for my client using an online questionnaire to obtain answers from her customers. These answers are stored in a database and they need to be subsequently retrieved into a static Word document to produce a compliance manual. The customers need to be able to download the merged document as a Word document.

What I need essentially is a glorified Merge document to be created. There are approximately 150 questions. The static document into which the answers need to be merged contains 150 pages of static text prior to being populated with the answers. Considering the answers are of various lengths, the overall document could grow to 200 pages or more. I guess the question is really how to build report with Word's merge functionality built into it. We anticipate that 100-200 users will be accessing these reports daily.

Here is my question....if I use something like SQL Server 2000, will the built-in reporting functionality allow me to produce this type of merged document if I use a stored procedure on the server side to merge the data? Or, will I need to buy a separate reporting tool such as Crystal Reports or some other report writer and do the formatting in Crystal Reports? Will SQL Server's functionality allow for the creation of a report that can be converted to a Word document and maintain the formatting? Would a database like mySQL combined with Crystal Reports for report writing be a more appropriate choice?

7 replies

by Lamont Adams

How many users do you expect your application to handle at one time? How will you build your word document, with VBA code run from inside an instance of Word, or in some other fashion?

by twells

Thanks for your reply, Lamont.

I expect the most simultaneous users we will ever get is 100 at most.

Your second question is one that is at the heart of why I posted my question. I'm not quite sure what the best solution is to populate the Word document. When I worked on a similar project before, we tried to use PowerBuilder to merge data from a table into a one-page Word document in the form of a letter. It took ten minutes just to process 100 rows (and make 100 letters). Later they had to chunk the whole thing and start again from scratch. I think they tried a stored procedure to process all of the data but I don't know how they eventually got the data into a Word document. I know our performance hangup, though, was using OLE in Word. I think my best bet is to do all of the processing that I can on the server side before it gets into Word. The stored procedure could get my data from each table into the final merged document, but I'd be stuck without any kind of formatting.

I'm open to any suggestions as to middleware that I could use instead of forcing the database to do the majority of the work.

Tonya

by Lamont Adams

I agree, you'll need to get everything that you can done on the server, though I don't know that doing it in an SP would be required. However, with the number of concurrent users you're talking about, I'd look at using SQL Server or something comparable. I'm not terribly familiar with PostGRE or MySQL so I can't tell you how they'd work in an application like this, but they might be options for a back end DB.

I also agree that your big bottleneck will likely be automating Word to do a merge. So, I'd leave word out of the equation completely. Here's what I'd do based on what you've explained so far: Build an ASP front end that gathered all the info needed to generate your doc, then build a server-side ActiveX component (even an ASP pagewould work here) that generated an HTML document as a final result. HTML is probably more portable than Word's DOC format, would be much faster to produce programmatically, and wouldn't require the user to actually download anything or have any software installed. You could also do something similar with PDF, although you'd have to purchase some software from Adobe.

The only drawback to not generating the final document in Word is that it couldn't be easily edited, but, assuming you saved their previous selections, the user could easily come back and use your app to create a new, modified document.

by aaron_kempf

i know im gonna get laughed at--

but i am right.

include the access runtime with your application and do reporting with Access Data Projects.

you can pass an RST as an argument to the Docmd.OpenReport argument and its like music to my ears..

Its __fast__, its prebuilt-- it royalty free.

Deployment is a little bit of a pain-- i use it in a dozen different companies; and i always make a habit of overwriting the current Access files instead of trying to deal with a 2nd instance of Access on the machine.

it works like a charm; i just havent spent enough time trying to figure out how to make a package to install it..

but people love it.

just do it.

by aaron_kempf

dude dont even think about doing this in a webpage..

i swear some people are absolute losers; and assume that everything _Must_ be done in a webformat.

these losers are the kinds of people that are unemployed right now.

use the quickest, best performing, most flexible platform--

ACCESS!!!

by rwblaas

I don't think it's necessary to use ASP. I think it may be possible to use the OLE Automation SP's in SQL server to perform the task you seek. I however haven't got any experience with them so I'm not sure what the performance consequences are on using server side OLE automation objects (check out the doc's in BOL on OLE). If there is a performance issue you may want to consider replicating the DB onto a seperate DOC generating server. As far as using ACCESS, you may run into performance bottleneck, it's another link in the chain. Although Acces is a cheaper solution it's certainly not a performance cruncher and if not coded properly you are going to run into resource contention and locking on SQL.

by mschmalz

I don't know how you have your system set up, but using form fields in Word is very easy. Here is what I have done for several clients. I have a query/table/stored procedure that has information in it and I have a word document that I want to populate with it. If all of my data is in one row, I set the names of the form fields equal to the names of the columns in the database. I then use a for each loop on the form fields and populate them with the database information that is in the current row. If the information is in multiple rows, I use a field as a key that tells me where it goes in the word document and then I use a while not rs.eof to go through the entire recordset and use that to populate the word document. You can then save the document via code as well and then batch print at the end, or you can have it print the documents as they go.

I realize that the Access report writer is fairly easy to use and might be a little less cumbersome on the development side, but for the user it would be all but impossible for them to just make changes if they were not familiar with Access. Using word with form fields lets anyone go into the template and make changes. It also allows you to use the same query to serve multiple purposes. If you have three different documents that use different information from the database, you use the same procedure since it is only going to fill what fields are in the word document.

You can contact me if you have any questions.

This discussion is closed to new replies.