|
Introduction
Your corporate client is overjoyed with the Intranet application you’ve just built. However, the client would like you to touch up the HTML in order to print a standardized set of reports. We know this just can’t happen in HTML (yet!) but let’s find out how you can say “yes” to this client.
We want a professional set of reports to be printable from any client’s browser. To enable this we must abandon the browser’s print engine and adopt a dedicated reporting engine. We have a number to choose from: Crystal Reports, DataDynamic’s ActiveReports, Chili!soft’s Chili!Reports and Microsoft Access will all perform handily. Some will enable you to ship data to the client where it will be formatted and printed there. Others require you to manage the formatting process server-side, sending the report client-side for final printing. Here I present code and observations that may allow you to comfortably answer affirmative the next time you’re asked for polished Web reports.
Microsoft Access
Pros: One of the best report engines, Crosstab queries, complex serial queries, an excellent event model for reports, strong VBA encapsulation within reports, Dual usability – Web and internal Access generation, save as Excel, CSV, print to RTF, Internal Web Formats – IGC/HTM, ASP, static.
Cons: Limited performance, threading issues, complex, Web output poor.
Let’s set the stage. For the last six months your client has been building some great looking reports in one or more Access databases. Hand crafting each report with code and fine field placement, they’ve built 40-plus data presentation masterpieces. You and four others can quickly pump out these reports and distribute them to those who need them. Well, you used to. Now more reports and more demand begin to tax your team’s capability to deliver. So, you decide to publish these reports through the Web.
What a great idea! We all know by now that built into Microsoft’s premier user database exists the ability to Web publish all of Access’ intrinsic display objects. Why not just use the built in HTML/IDC/HTX/ASP wizard and get those reports out on your clients intranet, pronto. The idea truly tingles with potential. But as most of us can attest, the results lack severely. The balance of Access’s HTML Wizards offer a truly unique set of functionality, with macros and batch files added one can get almost 75% of the way there. But for reports? Macros and batches – no thank you.
What to do now? How about running Access directly, either sending commands to a report runner form or manually executing the report via the Access.Application?
The underlying concept here is to execute a report based upon one or more queries, outputting the results to a Web-viewable file. Let’s start at the end, the output file, and work backwards.
Using the built in report formats found in Access and testing each for quality, most of us will conclude that although the resulting HTML, RTF or XLS is adequate for information presentation, we wouldn’t want to completely rely upon Access’s Web output for our corporate board presentations. To get that you would want to use a method/product found elsewhere in this article. But we still want to get these 40-plus reports out to our needy, clients so we’ll use these formats regardless. Generating the report file inside Access requires nothing more than a simple one-line command:
DoCmd.OutputTo acOutputReport, strReportName, acFormatRTF, txtExportFileName & ".rtf"
Here we show a rich text output format being generated. Access will execute the report saving the results to the file specified. Sure, this is easy, but what about parameter queries? Ah yes, parameters. Well, still working to the beginning, we need to focus on the query the report is based on. In Access, a report’s query may be the final in a series of queries, any one of which may have had input parameters specified. This obviously poses a problem. We cannot have modal popup boxes from intermediate queries prompting us for report dates or maximum revenue values as we run this report from our ASP page. We must provide a way to automatically populate these values prior to the report’s execution. An initial way, stated briefly, is to run a report runner form. On an Access form one would create text boxes, combo boxes, check boxes to be populated prior to running a report. The query (or queries) the report is based on would pull parameters, not from prompt parameter forms, but from the controls on this form. You could execute Access from a command line containing all required parameters, or programmatically control Access and populate these form fields interactively. In the end you still issue a DoCmd.OutputTo command to print the report.It’s just now your report’s queries don’t have to ask you for dynamic information.
A second way to interact with a reports query, (via the Access.Application, and this requires that only the last query contains parameters), is to open the QueryDef the report is based upon and dynamically fill the parameters directly. An additional caveat for this method is that the report must actually be based upon a work table which is generated by a “same name” query. In my example, report “HobbyCost” has a RecordSource of table w_HobbyCost which in turn is created by a make table query “q_HobbyCost”. The query q_HobbyCost contains two parameters [MinCost] and [MaxCost] which, as you can see in Listing MSA1, are both dynamically located and filled.
Continuing back to the beginning we now need a way to initially select a report, select an output type, and run said report.
The best way to accomplish this would be to build a table of report names. Our ASP would display the contents of this table. We could even assign rights to each report in this table to make sure only those with the appropriate level may run and view our classified reports. Then, within the same ASP, have code which performs what I have outlined above: instantiate Access, open the report selected, identify the “RecordSource” query, open this query, iterate through the parameters collection, build a dynamic form associated with the parameters (and parameter types) and allow the user to input dynamic report parameters. Upon the user’s selection we execute the named query, pre-supplying the Web-filled parameters, which then creates our report’s required work table. We then DoCmd.OutputTo and presto, here’s your report!
I’ll admit that this technique leaves much to be desired. Access being slow, cumbersome and multithread sensitive I wouldn’t expose my 10,000 reports-a-day clients to such a hack. But, I will say the following, when you build and release such a beast to those who have been clumsily reporting along, they will definitely love you for it!
More Microsoft Access
I like MS Access. Many department level reporting people like it too. For this reason my consultant team has come up with a great way to enable our clients to publish their previously created Access reports via their intranet. Unfortunately MS Access’s built-in Web functions cannot be considered usable for quality reports, in lieu of this we built a COM/ASP interface which will run Access using its superior formatting engine to generate reports.
DoCmd.OutputTo acOutputReport, strReportName, acFormatRTF, txtExportFileName & ".rtf"
This simple command, which can be executed from an ASP, will create a rich text report of the desired name and location. Other output formats are supported as well: XLS, HTML, CSV. This works by instantiating a copy of Access:
Set objAccess = Server.CreateObject(“Access.Application”)
Set objDoCmd = objAccess.DoCmd
and also instantiating a copy of Access’s DoCmd object. This process holds no surprises and is easily demonstrated. We haven’t, however, accomplished entirely what we want, which is to generate an Access report just like it comes out of the printer. To achieve this we must solicit the services of another printing oriented application – Acrobat. I’ve dedicated a portion of this piece to Acrobat and so I’ll leave final explanation 'til then.
In addition to the use of Acrobat there are a few more issues yet to be discussed. One is the fact of determining which reports are to be made available for dynamic generation. Another is parameter query based reports. Both of these concerns we’ve solved by adding a table to the database – a reports table. The reports table contains the fields report_name, display_name, run_as_batch, parm_display1, parm_name1, parm_value1, (other parameters extended here). When a report is ready for Web distribution it is added to this table. If the report’s query contains parameters they are copied into the parameter fields (they must be a perfect match). Also, all the reports are based off of work tables. That is, tables which are dynamically created at run time by “make table” queries. And two final points, to simplify management a naming triangle was assumed – query name = “q_” + report name, work table name = “w_” + report name. And if your queries do contain parameters and you have cascading queries (q_3 calls q_2 which calls q_1) the parameters must be contained in the last query (q_3 in our example - we cannot have modal popup boxes from intermediate queries, prompting us for report dates or maximum revenue values as we run this report from our ASP page).
Perhaps these tricks are too much for you and your clients. Understood. But just imagine allowing any Access literate reporting person to create their own reports which you (or they) merely add to the reports table., Done!
To interact with the query’s parameters we must open the QueryDef and dynamically fill the parameters directly. By doing this we can iterate through the parameters collection of the querydef matching them up with return values from our ASP Request object.
Listing MSA1
Sub PrintReportParmsInput()
dim mobjAccess, qry, rpt, param
dim blnDoNotRunReport
dim strOrigQrySQL
dim strReportName
strReportName = Request.Form(“ReportName”)
blnRunReport = True
'--- create an instance of Access --
set mobjAccess = CreateObject("Access.Application")
mobjAccess.OpenCurrentDatabase _
request.ServerVariables("APPL_PHYSICAL_PATH") & "\data\ASPReport.mdb"
mobjAccess.Visible = False
'--- the DoCmd object is the easiest way to open a report --
Set MyDoCmd = mobjAccess.DoCmd
MyDoCmd.OpenReport strReportName, 1 '1=design, 0=normal, 2=preview
'--- create a reference to the now open report --
Set rpt = mobjAccess.Reports(0)
Text "Report Name:" & rpt.Name
'--- Open the Query the report is based upon –
Set qry = mobjAccess.CurrentDb.QueryDefs("q_" &_
Mid(rpt.RecordSource, InStr(rpt.RecordSource, "_") + 1))
'--- open up a table cell to put the input boxes in --
Text " <tr>"
Text " <td>"
'--- loop through the queries parameters --
If qry.Parameters.Count > 0 then
For Each param In qry.Parameters
'--- to print the report make sure we have all parameters filled in --
If request(param.name) <> empty then
param.Value = request(param.name)
'--- echo the supplied value --
Text "Printing For " & param.name & ":" & request(param.name) & BR Else
'--- we have an empty parameter do not run report –-
blnRunReport = False
End if
'--- provide the text fields for parameter input --
text param.name & " <input type=text name=""" &_
param.name & """ value=""" & request(param.name) & """>" & BR
Next
Else
Text "No Parameters Found for this Query:" & qry.Name & BR
End if
'--- provide an report type output selection --
Text "<p>OutPut Type: "
Text "<Input Type=Radio Name=""ff_OutPutType"" value=""1"" Checked>HTML"
Text "<Input Type=Radio Name=""ff_OutPutType"" value=""2"">RTF"
Text "<Input Type=Radio Name=""ff_OutPutType"" value=""3"">TXT"
Text "<Input Type=Radio Name=""ff_OutPutType"" value=""4"">XLS" & P
text "<input type=submit></Form>"
'--- close up our cell --
Text " </td>"
Text " </tr>"
'--- Run the report if the parameters have been
'--- filled or no parameters were found --
If blnRunReport then
on error resume next
'--- run the make table query --
qry.Execute
If Err.Number <> 0 Then
'--- delete the old work table if necessary --
mobjAccess.CurrentDb.TableDefs.Delete rpt.RecordSource
qry.Execute
End If
'--- get the right output format and the file suffix for the report --
DecodeOutPutFormat Cint(Request("ff_OutPutType")), _
strType, strFileSuffix
'--- export the report to the selected format --
MyDoCmd.OutputTo 3, strReportName, strType, _
request.ServerVariables("APPL_PHYSICAL_PATH") & "MSAccess\" &_
strReportName & strFileSuffix
Else
Text "Please fill all parameters in prior to Printing" & BR
End If
'--- Close the Query --
qry.Close
'--- close the report --
MyDoCmd.Close 3, strReportName, 2 '2=saveno 3=Report
'--- clean up objects used --
Set qry = nothing
set rpt = nothing
set myDoCmd = nothing
mobjAccess.Quit 2 '2=savenone, 1=saveall
set mobjAccess = nothing
'--- provide a link to the just exported report --
If blnRunReport then
text "<a href=/aspreport/msAccess/HobbyCost" & strFileSuffix &_
">HobbyCost</a>"
End If
End Sub
Sub DecodeOutPutFormat(byval intType, strType, strFileSuffix)
Select Case IntType
Case 1
strType = "HTML (*.html)"
strFileSuffix = ".HTM"
Case 2
strType = "Rich Text Format (*.rtf)"
strFileSuffix = ".RTF"
Case 3
strType = "MS-DOS Text (*.txt)"
strFileSuffix = ".TXT"
Case 4
strType = "Microsoft Excel (*.xls)"
strFileSuffix = ".XLS"
End Select
End Sub
Acrobat Exchange
Pros: Easy, quick, versatile, supports HTML forms, ubiquitous
Cons: Data is limited to non-existent, initial and reformatting complex
Adobe’s Acrobat suite of PDF (portable document format) creation and processing tools has become the defacto standard for generating and distributing intricately formatted electronic documents. Adobe’s claims of high saturation can be substantiated by countless testimonials and by the ease of use and overall quality of this product. Aside from SGML/HTML I would place bets that PDF ties with Word’s DOC for the most Web pervasive format.
Acrobat’s Web functionality boils down to essentially two techniques: dynamic PDF generation and FDF/PDF. FDF (forms data format), which is inching its way into Mac and Unix enclaves everywhere, purports to be a brother to the HTTP Post protocol (RFC 1866). And indeed a form designed with Acrobat Exchange’s Form tool can actually submit to your Web server HTTP form variables.
Dynamic generation of PDF documents, on the other hand, extols no interactivity per se. But because of the ubiquitous coverage of this format, the ability to perform such a spontaneous document creation will handily set your ASP applications apart from the pack. I’ve often coded such functionality to great success.
How, you might ask, can I lump PDF generation in with report printing? By telling you that if I can get an application to print to a PostScript driver, I can get that same report into PDF and distributed to the world. If your application prints reports, your application can make a PDF.
For FDF/PDF I’ll be including examples and comments. But suffice it to say, if you find yourself or your client in need of in-depth Acrobat Forms functionality – hit the resources: Adobe and PDFZone top the list.
Figure ACR1
Listing ACR2
<%
Dim FdfAcX, objFdf
Dim strEntireReturn, strValue, strField
On Error Resume Next
Set FdfAcX = Server.CreateObject("FdfApp.FdfApp")
Set objFdf = FdfAcX.FDFOpenFromBuf(Request.BinaryRead(Request.TotalBytes))
Text "The following fields were returned from your PDF file:<br>"
strField = objFdf.FDFNextFieldName("")
Do While strField <> BLANK
i = i + 1
strValue = objFdf.FDFGetValue(strField)
If Err.Number = 0 Then
Text "Field " & i & " Name:" & strField & " = " & strValue & br
Err.Clear
Else
Text "Field " & i & " Name:" & strField & " = " & "Error" & br
End If
strField = objFdf.FDFNextFieldName(strField )
loop
'strEntireReturn = objFdf.FDFSaveToStr
'Text "FDFSaveToStr:" & strEntireReturn & br
objFdf.FDFClose
set objFdf = nothing
set FdfAcX = nothing
%>
Listing ACR3 : Results from the ASP FDF Processed file
The following fields were returned from your PDF file:
Field 1 Name: FavRiver = Madison
Field 2 Name: FavFly = Stone Nymph
Field 3 Name:Fish = Sturgeon
Field 4 Name:Lie = I never lie
Field 5 Name:Submit = Error
Field 6 Name:YearStarted = 1960
Listing ACR4
%FDF-1.2
%âãÏÓ
1 0 obj
<<
/FDF << /Fields [ << /V (Stone Fly)/T
(FavFly)>> << /V (Madison)/T
(FavRiver)>> << /V (Sturgeon)/T (Fish)>>
<< /V (I never lie)/T (Lie)>>
<< /V (1960)/T (YearStarted)>>
]
/F (FDFTest.pdf)/ID [ (+\213o\320\316\2146IK\365\233\246c\202o\313)
(OO\350\253Q\253:\250\254\375.?\034c\235J)
] >>
>>
endobj
trailer
<<
/Root 1 0 R
>>
%%EOF
In Listings ACR1, 2, 3, 4 you’ll find a screen shot of a PDF in Internet Explorer, the results of the FDFTest.Asp, the FDFTest.Asp itself and the FDF which was returned from the browser/Acrobat Exchange pair. The process goes as follows. You must first create a PDF, from the program of your choice, by any means at your disposal, i.e. print to Distiller, or print to file using PDFWriter. Next you must modify the resulting PDF within Acrobat Exchange’s Form tool to include form objects, text, checkbox, radio etc. Thirdly you must Web link to the PDF (or open it in ASP and feed the correct headers along with the file) to have it show up in your browser. Lastly you then should process the return from the submitted PDF form. A fifth option you might explore is processing the returned FDF file, massaging it with new or cleared data and return it as an FDF to the calling PDF file within your browser. It is possible to dynamically create a FDF file from fields in a database and send this file off to your browser. You’ll notice the FDF file contains a reference to the master PDF file. You can link to or dynamically send an FDF file which contains the reference to the parent PDF file. In order for IE to respond correctly to this action you need to have the file type, FDF registered in your Windows Explorer - View - Options - File Types tab. Make sure the following exist there:
- Registered File Type : Adobe Acrobat Form Document
- Extension : FDF
- Content Type (MIME) : application/vnd.fdf
- Opens With : Acrobat Reader (pointing to your copy of Exchange or Reader)
Adobe documentation also states that within your PDF form the submit URL should have appended a “#FDF” prior to returning to the server.
This PDF/FDF document handling can get extremely complicated with functions like uploaded images, PDFMark commands to actually manipulate the PDF file, data saving and fetching and PDF highlighting. Way too much to get into here. For beginners using Forms Data Format, the FDF ActiveX toolkit can be found here: http://beta1.adobe.com/ada/acrosdk/forms.html. Charlie Fulnecky (caf@programmmer.net) provided instrumental insight into how FDFs work and has built some amazing interactive forms using this technology, my thanks to him. Charlie would want me to mention that you needn’t return a PDF form’s contents in FDF format. PDF forms support the HTTP Post protocol directly. It’s just like receiving data from an HTML form.
The second Acrobat/ASP marriage revolves around the dynamic generation of Portable Document Format files. As I stated previously, if your report writer can print to a Postscript driver, you can have your cake and PDF it too. In Listing ACR5 I’ve included code which combines some interesting technologies. Excel, Word, Distiller and PDFMark commands team up to deliver some amazing results. But before we get into the code we must clear the air of a number of setup/configuration issues. First of all Acrobat Distiller converts Adobe’s Postscript files into PDF files. One sets up Distiller (a TSR or service-like executable) to continuously monitor a directory for Postscript files. If found, it converts them. If you’re running the NT version of Distiller be sure to get the 3.02 patch as the previous versions had a nasty memory leak.
But I’m still ahead of myself here. We need to generate a Postscript file first. So please follow the instructions below to accomplish this.
Printer setup
Running IIS as a system memory space service requires that any "user" oriented facilities required to operate this application be made available to this service. This means registry settings such as Printers must be copied over to a common registry location.
Please refer to the article Q152451 below for instructions on accomplishing this task. (Included here for convenience.)
- In Print Manager, create a new port -- Server Properties/Ports/ --Add Port -- choose LocalPort -- click New Port -- type in the following "c:\PSout.ps" -- this will create a file port which will contain the results of Postscript file contents.
Create a local printer--by choosing Create Printer from the Printer menu, using a PostScript printer driver appropriate for a printer which requires PostScript. Configure the printer to print to this new port just created, and set it as the default printer.
- Start and maximize Registry Editor (Regedt32.exe).
WARNING: Using Registry Editor incorrectly can cause serious, system- wide problems that may require you to reinstall Windows NT to correct them. Microsoft cannot guarantee that any problems resulting from the use of Registry Editor can be solved. Use this tool at your own risk.
Minimize the windows titled "HKEY_LOCAL_MACHINE on Local Machine" and "HKEY_CLASSES_ROOT on Local Machine" and position the remaining two windows, titled "HKEY_CURRENT_USER on Local Machine" and "HKEY_USERS on Local Machine," such that they are tiled vertically on the screen with each filling about one half of the available window space in Registry Editor. This step is provided for convenience in completing the remaining steps, in which you will review and edit certain registry keys.
- Select the key
HKEY_CURRENT_USER
\Software\Microsoft\Windows NT\Current Version\Devices
and note the information for the value of the key that represents the default printer created in step 1 above (for example, "LocalPrinter : REG_SZ : winspool,LPT1:").
- Select the key
HKEY_USERS
\.DEFAULT\Software\Microsoft\Windows NT\Current Version\Devices
and choose Add Value from the Edit menu to create the same value you noted in step 3 (using the above example, "LocalPrinter" is the value name, "REG_SZ" is the data type, and "winspool,LPT1:" is the string).
- Select the key
HKEY_CURRENT_USER
\Software\Microsoft\Windows NT\Current Version\PrinterPorts
and note the information for the value of the key that represents the default printer created in step 1 above (for example, "LocalPrinter : REG_SZ : winspool,LPT1:,14,45").
- Select the key
HKEY_USERS
\.DEFAULT\Software\Microsoft\Windows NT\Current Version\PrinterPorts
and choose Add Value from the Edit menu to create the same value you noted in step 5 (in the example from step 5, "LocalPrinter" is the value name, "REG_SZ" is the data type, and "winspool,LPT1:,14,45" is the string).
- Select the key
HKEY_CURRENT_USER
\Software\Microsoft\Windows NT\Current Version\Windows
and note the information for the device value of the key (for example, "LocalPrinter,winspool,LPT1:").
- Double-click the device value of the key
HKEY_USERS
\.DEFAULT\Software\Microsoft\Windows NT\Current Version\Windows
and edit it to reflect the same information you noted in step 7 (in the example from step 7, you would enter "LocalPrinter,winspool,LPT1:" for the string).
- Close Registry Editor.
And we’re still not done. From your program, in this case Excel and Word, you can either set this new printer as the system default printer( as noted above) or using the “ActivePrinter” for a VBA-enabled app you can dynamically set the printer to which the program will output.
ObjWord.Application.ActivePrinter = strPostScriptPrinter
ObjExcel.ActiveWorksheet.PrintOut ActivePrinter:=strPostScriptPrinter
(Excel requires this last method as the set ActivePrinter only works if the sheet is active and visible, and it will not be in our ASP code!)
Now we can review the code in Listing ACR5
- In it we have a FileSystem object deleting the previous Postscript file.
- Word then is printing a pre-made document to the default printer (PostScript).
- A hack for waiting for Word and the system to finish printing.
- Move the Postscript file to a processing holding directory.
- Print a pre-made Excel file to the same default printer.
- Second hack for waiting for Excel and the system to finish printing.
- Move that file to the same holding directory.
- Create and load a new Postscript file with Postscript and PDFMark commands.
- Write out to the Response object a link to the new PDF file.
- Clean up our objects used.
What is PDFMark? PDFMark commands provide the programmer with a vast set of PDF modification capabilities. What is illustrated here is just a taste. I wouldn’t lightly step into an elaborate usage of PDFMark but when needed for dynamically manipulating a PDF file via Distiller you’ve no choice but to use it.
Those of you familiar with Acrobat may be wondering why I don’t use the PDFWriter. Although you can directly set the output file PDFWriter generates by hitting the Registry (actually a PDF.ini file) PDFWriter cannot optimize a PDF (no page byte serving or compression) and it cannot modify document info (PDF metadata) using PDFMark. Also, PDFWriter does not translate passed Postscript, the PDF is generated via interpretation through the GDI (Graphic Device Interface) resulting in unreliable font reproduction/inclusion and problematic EPS graphic situations.
The below code assumes the following:
Acrobat Distiller has been setup to watch the “/ASPReport/Acrobat/Process” folder.
Listing ACR5
<%
On Error Resume Next
Response.Buffer = True
Dim objFileSys, objF, i, blnWait
Dim objWord, objExcel, objDoc, objWkb, objRng
Const PATH_POSTSCRIPT_OUTPUT = "c:/win95/desktop/PSout.ps"
'--- File System --------------------------------------
Set objFileSys = Server.CreateObject("Scripting.FileSystemObject")
objFileSys.DeleteFile PATH_POSTSCRIPT_OUTPUT, True
'--- Word ---------------------------------------------
Set objWord = Server.CreateObject("Word.Application")
Set objDoc = objWord.Documents.Open( _
Server.MapPath("/ASPReport/Acrobat/WordPS.Doc"))
objWord.PrintOut
'--- Delay while Word finishes printing ---------------
For i = 0 To 50
blnWait = objWord.IsObjectValid(objDoc)
Next
'--- move output --------------------------------------
objFileSys.CopyFile PATH_POSTSCRIPT_OUTPUT, _
Server.MapPath("/ASPReport/Acrobat/PostScript/" & "File1.ps"), True
objFileSys.DeleteFile PATH_POSTSCRIPT_OUTPUT, True
'-- Excel ---------------------------------------------
Set objExcel = Server.CreateObject("Excel.Application")
Set objWkb = objExcel.Workbooks.Open _
(Server.MapPath("/ASPReport/Acrobat/ExcelPS.xls"))
Set objRng = objWkb.Names("Print_Area").RefersToRange
objRng.PrintOut
'--- Delay while Excel finishes printing ---------------
For i = 0 To 50
blnWait = objWord.IsObjectValid(objDoc)
Next
'--- move output --------------------------------------
objFileSys.CopyFile PATH_POSTSCRIPT_OUTPUT, _
Server.MapPath("/ASPReport/Acrobat/PostScript/" & "File2.ps"), True
Set objF = objFileSys.CreateTextFile( _
Server.MapPath("/ASPReport/Acrobat/Process/In/New.ps"), True)
objF.WriteLine "%!"
objF.WriteLine "% DOCUMENT INFO"
objF.WriteLine " [ /Title (ASP Reporting)"
objF.WriteLine " /Author (Dave Cline)"
objF.WriteLine " /Subject (Generate Reports From ASP)"
objF.WriteLine " /Keywords (ASP Reports dynamic realtime)"
objF.WriteLine " /Creator (ASP byhand)"
objF.WriteLine " /ModificationDate (D:19980902073001)"
objF.WriteLine " /MyKey (development only)"
objF.WriteLine "/DOCINFO"
objF.WriteLine "pdfmark"
objF.WriteLine "/prun { /mysave save " &_
"def dup = flush RunFile clear cleardictstack mysave restore } def"
objF.WriteLine "(" & Replace( _
Server.MapPath("/ASPReport/Acrobat/PostScript/file1.ps"), _
"\", "/") & ") prun"
objF.WriteLine "(" & Replace( _
Server.MapPath("/ASPReport/Acrobat/PostScript/file2.ps"), _
"\", "/") & ") prun"
Response.Write "<a href=/ASPReport/Acrobat/Process/Out/New.pdf>New " &_
"PDF</a><br>"
'--- Close up Objects --------------------------------
objDoc.Close False
Set objDoc = Nothing
objWord.Quit False
Set objWord = Nothing
Set objRng = Nothing
objWkb.Close False
Set objWkb = Nothing
objExcel.Quit
Set objExcel = Nothing
Set objF = Nothing
Set objFileSys = Nothing
If Err.Number <> 0 Then
Response.Write "Err:" & Err & " :" & Err.Description
End If
%>
DataDynamic’s ActiveReports
Pros: Full integration w/ VB, multiple/nested Subreports, compression, PDF output.
Cons: New and still unpredictable, requires VB knowledge, complex ASP integration.
For a version 1.0 product, Datadynamic’s ActiveReports performs exceptionally. Crystal has long since needed a competitor and this one may just be the ticket. Because ActiveReports is a newcomer to this field, I’ve decided to just review this product “as is” without delving deeply into the coding possibilities which will eventually arise with usage and broad programmer exploration.
For starters, I generally like this product. I had few difficulties generating my generic test report and what it produced for me met my expectations. I hit the news groups dedicated to the product finding considerable traffic, most of which dealt with questions about enhancements, bugs, and general comments (in that order). With general comments often citing the excellent support received from DataDynamics, who continues to release revisions.Their latest, I’m sure, fixes the couple of problems I encountered such aspage footer not printing and RTF export filename “Object not set” errors.
In contrast to Crystal’s dedicated design executable, ActiveReports reveals its design environment as a VB5 design time control (an ActiveX Designer actually). The interface and functionality appeared simplistic but effective, reminiscent of a worked over Access report designer. Each field and group header/footer contained its own format event which I found useful. Many VB-like methods and events are associated with a report, initially being created by inserting into a VB5 project an ActiveX Designer of the type “Data Dynamic Active Report”. Complete VB-like formatting options are available for all report objects and a variety of data sources make data linking a snap. With databases like DAO, RDO and OLEDB (which is using the ADO dlls) just clicks away, I had my first report built in 15 minutes. This was easy.
It began to get sticky when I wanted to dynamically generate reports from ASP. First, I had to understand that these reports are not like Crystal’s. Crystal’s reports are entities unto themselves. They are self contained while ActiveReport’s are not. They are components of a VB5 project, i.e. part of a VB5 compiled executable. Well, I thought, “That’s okay. I’ll just provide an interface into a couple of reports through a class I included in the project.” Listing AR1.
Listing AR1
Public Function PrintReport(ByVal strReportName As String, _
ByVal strReportNameKey As String, _
ByVal intExportType As String, _
ByVal strReportPath As String, _
ByVal strNewSQL As String) As Variant
On Error GoTo PrintReportError
Dim strErrMsg As String
PrintReport = FAILURE
Select Case strReportName
Case "BloodFamily"
Dim rpt As BloodFamily
Set rpt = New BloodFamily
Case "InLawFamily"
Dim rpt As InlawFamily
Set rpt = New InlawFamily
End Select
Select Case intExportType
Case 1 'PDF
Dim pdf As New ActiveReportsPDFExport.ARExportPDF
pdf.filename = strReportPath & strReportName & strReportNameKey & ".pdf"
If strNewSQL <> "" Then rpt.DAODataControl1.RecordSource = strNewSQL
rpt.DisplayErrors = False
rpt.Run
pdf.Export rpt.Pages
Set pdf = Nothing
Case 2 'RTF
Dim rtf As ActiveReportsRTFExport.ARExportRTF
rtf.filename = strReportPath & strReportName & strReportNameKey & ".rtf"
If strNewSQL <> "" Then rpt.DAODataControl1.RecordSource = strNewSQL
rpt.Run
rtf.Export rpt.Pages
Set rtf = Nothing
Case 3 'RDF Report definition format
If strNewSQL <> "" Then rpt.DAODataControl1.RecordSource = strNewSQL
rpt.Run
rpt.Pages.Save strReportPath & strReportName & strReportNameKey & ".rdf"
End Select
PrintReport = SUCCESS
PrintReportExit:
Set rpt = Nothing
PrintReport = PrintReport & strErrMsg
Exit Function
PrintReportError:
strErrMsg = "PrintReport" & ":" & Err & ":" & Err.Description
Resume PrintReportExit
End Function
Externally, the interface to the COM object containing the ActiveReport code looks like Listing AR2
Listing AR2
<html>
<head>
<%
If Request("ReportName") <> "" Then
DisplayReport Request("ReportName")
Else
%>
</Head>
<body bgcolor="#CCEEBB">
<div align="center"><center>
<table border="0" cellpadding="3" cellspacing="0" width="550">
<tr>
<td valign="bottom"><br>
<br>
</td>
<td align="right" valign="top">
<a href="../index.asp"><font size="4" face="Arial"><em><strong>
ASPReporting</strong></em></font></a></td>
</tr>
<tr>
<td bgcolor="#008000">
<font color="#FFFFFF" size="3" face="Arial"><strong>
DataDynamic's ActiveReports</strong></font></td>
<td bgcolor="#008000"></td>
</tr>
<tr>
<td colspan=2>
<form action="dynamicreport.asp">
<input type=hidden name=ReportName value=BloodFamily>
Dynamic SQL for a ActiveReport
</td>
</tr>
<tr>
<td colspan=2>
<input type=radio name=blnSelf checked=True value=1>
Include Member as relation
<input type=radio name=blnSelf value=0>
Do not include self as a relation
</td>
</tr>
<tr>
<td colspan=2>
<input type=submit value="Generate Report and display">
</form>
</td>
</tr>
</table>
</center></div>
<%
End If
Sub DisplayReport(byval strReportName)
'--- generate the report
set objAR = Server.CreateObject("Test.ARPrintReport")
If not IsObject(objAR) then Txt "Error creating Print Object"
Dim strSQL
strSQL = "SELECT person.name_proper, family.relation, " &_
"q_person.first, q_person.proper, q_person.dob"
strSQL = strSQL & " FROM q_person INNER JOIN (person INNER " &_
"JOIN (relation INNER JOIN family ON " &_
"relation.relation = family.relation) ON " &_
"person.person_id = family.person_id) ON " &_
"q_person.person_id = family.family_id"
strSQL = strSQL & " Where relation.blood_direct = Yes"
If Clng(Request("blnSelf")) = 0 Then
strSQL = strSQL & " And relation.distance <> 0"
End If
strSQL = strSQL & " ORDER BY person.name_proper"
Txt strReportName & "<br>"
Txt Session.SessionID & "<br>"
Txt 3 & "<br>"
Txt Server.MapPath("/ASPReport/ActiveReports/") & "\" & "<br>"
Txt strSQL & "<br>"
vntRC = objAR.PrintReport(strReportName, Session.SessionID, 3, _
Server.MapPath("/ASPReport/ActiveReports/") & "\", "")
set objAR = Nothing
If vntRC = 0 Then
Set browType = Server.CreateObject("MSWC.BrowserType")
If browType.browser = "IE" then
Txt "<script LANGUAGE=""VBScript"">"
Txt "<!--"
Txt "Sub window_onload()"
Txt " arv.DataPath=""" & strReportName & Session.SessionID & ".rdf"""
Txt "End Sub"
Txt "-->"
Txt "</script>"
Txt "</Head><body bgcolor=""#CCEEBB"">"
Txt "<object ID=""arv"" classid=""clsid:00C7C2A0-8B82-11D1-8B57-00A0C98CD92B"""
Txt "codebase=""arviewer.cab"" width=95% height=70% >"
Txt "</object>"
Else
Txt "</Head><body bgcolor=""#CCEEBB"">"
Txt "<APPLET CODE=""DDARJV.class"" WIDTH=600 HEIGHT=400>"
Txt "<param name=""report-path"" value=""" & strReportName &_
Session.SessionID & ".rdf"">"
Txt "</APPLET>"
End If
Set browType = nothing
Else
Txt "Error generating report:" & strReportName
End If
End Sub
Sub Txt(byval s)
Response.Write s & vbLf
End Sub
%>
</body>
</html>
Chili!soft’s Chili!Reports
Pros:Uses Excel as print/calculation engine, auto ASP generation, feature rich(Excel)
Cons: Expensive, VBA interactive w/Excel(slow)
Chili!Soft’s Chili!Reports, a recent addition to ASP reporting, takes an excellent approach (literally) to providing professional looking reports from a browser. “Literally”,in the sense that it uses MS Excel as its reporting engine, sending template based, real-time data filled Excel spreadsheets to the browser. The browser must, of course, know what to do with such as beast when received but the free Excel reader, Excel itself or even Lotus 123 will swallow and display what Chili!Reports sends it.
Internally, a Chili!Report ASP relies upon an interpreter which will blend the data, any HTML form variables received from the previous form view, and the Excel spreadsheet into one cohesive, polished report. This is handled by chili_asp.dll. This dll is apartment threaded so ASP Application level caveats apply. The general processing flow goeslike this: retrieve your HTML dynamic variables, assemble your SQL (including the aforementioned Web form parameters), setup up the Chili!Report processor with all required parameters then activate the generation procedure. The resulting report contents (Excel) can be handled in one of three ways. The first is to redirect the requesting browser to the generated and saved Excel spreadsheet. The browser will identify the file as an .xls and respond accordingly. The second (and default) method is to return our report actively by streaming it back to the browser by modifying the HTTP headers and feeding the .xls out through the server. This method allows you to hide the .xls report as the URL returned to the “location” window in the browser is the name of the processing ASP. Lastly the report can be created and stored for later delivery via FTP or SMTP (externally). All three methods are handled by the chili_asp.dll by flipping the parameter passed to the “objChili.Report” method.
The internal structure of the Chili!Report ASP page is as follows.
Listing CHILI1
Dim lngCostMin, lngCostMax
lngCostMin = Request(“Parm1”)
lngCostMax = Request(“Parm2”)
Set objChili = Server.CreateObject("Chili.ChiliReport")
strSQL = “SELECT person.name_proper, person.email_1, hobby.hobby, “
strSQL = strSQL & “Sum([hobby_cost_monthly]*12) AS YearlyCost, “
strSQL = strSQL & lngCostMin & “ AS CostMin, “ & lngCostMax & “ AS CostMax “
strSQL = strSQL & “FROM person INNER JOIN (hobby INNER JOIN person_hobby ON “
strSQL = strSQL & “hobby.hobby = person_hobby.hobby) " &_
" ON person.person_id = person_hobby.person_id ”
strSQL = strSQL & “GROUP BY person.name_proper, " &_
"person.email_1, hobby.hobby ”
strSQL = strSQL & “HAVING (((Sum([hobby_cost_monthly]*12))>” & lngCostMin & “ And “
strSQL = strSQL & “(Sum([hobby_cost_monthly]*12))<” & lngCostMax & ”));”
XLSFile = Server.MapPath("/ASPReport/Code/ChiliReports/ASPReport.xls")
Data_DSN = "ASPReport"
Data_UID = ""
Data_PASS = ""
objChili.SetSQLCount 1
objChili.SetSQL 1, strSQL
objChili.SetSQLInfo 1, 1
objChili.SetDataSource data_DSN, data_UID, data_PASS
objChili.SetVBAParams XLSFile
objChili.Report 1 '0=redirect 1=stream blank=store
Response.Redirect objChili.VirtualOutputPath
Chili!Reports comes with a design time control for building reporting ASPs in Vinterdev and a standalone executable for quickly designing your report particulars. The Report Designer essentially wraps the chili_asp.dll in a tabbed interface for generating the initial ASP file. It apparently uses ADODB to execute the SQL statements specified (you can run up to 100 SQL statements per report). This Designer provides for easy configuration of the Chili.ChiliReport object and also allows you to actually run your report. Of course any ASP Request parameters won’t be available but you can hard code dummies in for testing.
The concept has definite merit as Excel proper and even the Excel Reader can do right by the printed page. Reports, no matter what they happen to be designed in, remain tedious and persnickety. Getting that line, column, date, margin aligned just right takes hours sometimes. For Chili!Reports the detailed tweaking of an Excel spreadsheet grates, though not as bad as Crystal’s design environment, but grates nonetheless on my nerves. Then again, I can get touchy every so often. The price of $3000 is a bit steep, however. Cough, cough.
Crystal Reports 6.0
Pros: Extensive ODBC support, a substantial design environment, their own web report system, their own plug-in (ActiveX or Netscape), versatile.
Cons: Large memory footprint, complex, ASP integration problematic, proprietary, quirky UI
Seagate Software’s Crystal Reports 6.0 clearly offers the most complete set of Web reporting tools known to exist in the universe. Its size and complexity shows it: ActiveX – got it; Java viewer – got that too; Frames based HTML viewer – yup; dynamic generation via ASP – you bet; in depth design environment – check; design-time ActiveX control – here we go; connectivity for every data repository ever invented (including my grandma’s recipe card catalog) – in the bag. If you want your report data on the Web, Crystal can probably get it there. I don’t mean to be uppity with regards to Crystal, if you’ve been coding in VB for a few years you’ve probably tussled with it at some time or another, but now, version 6.0 proves to be fairly tussle proof.
As just noted, Crystal, in the past, has been a product best accepted as is and developed within its own light. Striving for unusual functionality typically proved frustrating. Crystal 6.0’s Web offering continues this tradition. Although highly interactive, attractive and robust, Crystal Web Report Server and Crystal Report Engine Automation Server are best understood and used through the code and examples provided by Seagate.
Crystal divides its Web efforts into two camps. Web Report Server and Report Engine Automation Server, CRWEB.dll and CRPE.dll, respectively. The Web Report Server is an ISAPI dll which captures and interprets calls to .rpt files. Crystal reports are called directly via Web URL links with proprietary querystring codes tacked on for report customization. This method boasts simplicity and elegance, but requires that a Crystal Smart Viewer be available within the browser to interpret the returned report data stream. The viewers, like most now, are available free for distribution and currently work quite well. A typical Web Report Server querystring might be as follows:
www.hello.com/rweb/bloodfamily.rpt?INIT=actx&SPROC#=
The Web Report Server provides several report parameters GF, INIT, LOGDIR, LOGS, PASSWORD#, PROMPT#, SF, SPROC#, USER# which, when appended to an URL, can customize the returned report. For detailed descriptions of each, dive into Crystal’s Developr.hlp file. A few notes on the queries your reports are based upon, and this is paraphrased straight from the help file, that if your report requires SQL Server (or other database) logon or is based upon a parameterized query, CRWEB.dll will return a prompt HTML form asking for the appropriate information. I’ll admit, a pretty clean and handy way of getting your reports up on the Web.
A second way, and I’ll detail a third later, to get your report published is the direct use of the Crystal Report Engine Automation Server.
Set objCrystal = CreateObject(“Crystal.CRPE.Application”)
Figure ACR1
Our old friend CRPE.dll creeps back into our lives yet again. The code in Listing CR1 shows a condensed version of the required objects and “set” commands required to play interactive ASP report with the Crystal Report Print Engine. And it’s nowhere near complete. Fortunately, Crystal engineers have wrapped the macabre details for you in a set of ASP files they suggest (and I can see why) that you include with reports that you are attempting to publish using Active Server Page interactivity. Figure CR2
Listing CR1
<%
strReportName = Request.Form("FF_ReportName")
intHobbyCost = Request.Form("FF_HobbyCost")
'--- reference the Crystal application --
Set objCrystal = Server.CreateObject("Crystal.CRPE.Application")
'--- NO modal error messages --
objCrystal.Options.MorePrintEngineErrorMessages = 0
'--- find an load the selected report --
Set objReport = objCrystal.OpenReport(strReportName)
'--- load the Reports SQL -
objReport.SQLQueryString = "SELECT hobby_id, hobby_name, " &_
"hobby_cost FROM t_hobby WHERE hobby_cost > " & intHobbyCost
'--- make sure dialogs have been disabled
Set oRptOptions = session("oRpt").Options
oRptOptions.MorePrintEngineErrorMessages = 0
'--- execute the report ---
objReport.ReadRecords
If Err.Number <> 0 Then
Text "Error reading database for Report:" & strReportName &_
err.Number & ":" & Error.Description
Response.End
End if
'--- Create the Page Engine
Set objCrystalPageEngine = objCrystal.PageEngine
'--- initialize the Crystal engine(loads data)
Set objPageGenerator = objCrystalPageEngine.CreatePageGenerator(gvGroupPath)
Set objPageCollection = objPageGenerator.Pages
%>
If you choose to code your own variation of this given ASP file set, you do so at your own volition (and possible peril).
A third mechanism for interacting with Crystal is a fallback to the days when if you needed processing functionality, you built it yourself – with Visual Basic. Well, this last method merely wraps a set of methods of a COM object around either the CRPE object itself or, more easily, around the interface on a CRY VB form object. Drop the Crystal OCX onto a VB form, set the form’s Visible property to False, write a few procedures to interface with the standard properties and methods of the CRY1 object and voilà. But we’re trying to get this report’s output into a browser, right? Yes. So let’s either use the Export features of Crystal or, as I have done, combine a couple of technologies found in this article, PDF and Crystal. I’ve built a COM object which prints dynamic reports based on the SQL I send them ,which I print to PDF via Acrobat Distiller. The obvious lesson here is “innovate”. (ASP developers have never been short on innovation.)
OmniForm Internet Publisher
Pros: Excellent output quality, good input validation, good design environment, multiple creation choices
Cons: Lack of by row output, lack of database connectivity, moderately expensive
Duane Benson at www.DynaWebDev.com performed all the leg work for providing a review of Caere’s OmniForm Internet Publisher. He researched OmniForm and came up with his own methodology for using this product in the ASP environment. My thanks to him also.
Caere developed OFML (Open Form Markup Language) for use as a detailed forms description language. Their product, Omniform Internet Publisher, which takes advantage of this standard, allows one to scan existing or create afresh, elaborate, multi-page form reports. At heart this bears a resemblance to Acrobat FDF – a highly formatted page with fields, check boxes and dropdowns added for interactivity. I will admit, when Duane physically sent me a form original and a Web-printed form and I tried out the Web plugins myself, OmniForm’s Web quality and functionality showed me that they have a definite place in a corporation’s Web/paper arsenal.
OmniForm reproduces formatted forms. It does not generate true reports, that is, row by row reports. One could, through ASP programming, carefully loop through an ADO data set and build such a row report. However, there are better tools with which to accomplish this (for instance, Crystal, Access, and ActiveReports). The following code exposes you to a bit of OFML and ASP intermixed.
Listing Omni1
<%
Response.buffer=True
Response.AddHeader "expires", "Fri, 13 May 1996 23:59:59 EST"
Response.ContentType="application/fml"
Txt "<OFML VERSION=1.0 LEVEL=1>"
%>
<HEAD>
<DOCUMENT PAGES=1 W=177800 H=51600 TM=0 LM=0 RM=0 BM=0>
<FONTDESC NAME="Arial" PITCH=VARIABLE
FAMILY=SWISS CHARSET=ANSI INDEX=1>
<FONTDESC NAME="Times New Roman" PITCH=VARIABLE
FAMILY=ROMAN CHARSET=ANSI INDEX=2>
<TEMPLATE>
<TEXT FONTINDEX=1>
<FILLTEXT FONTINDEX=2>
<CE FONTINDEX=2>
</TEMPLATE>
</HEAD>
<BODY>
<PAGE>
<TEXT X=19050 Y=3175 W=149225 H=9525 FONT
SIZE=16 ALIGN=CENTER VALIGN=MIDDLE TM=330 LM=330 BM=330 RM=330>
ASP Reporting on <%=date%>
</TEXT>
<TEXT X=6350 Y=22225 W=12700 H=6350 ALIGN=RIGHT
VALIGN=BOTTOM TM=330 LM=330 BM=330 RM=330>
Name:
</TEXT>
<FILLTEXT NAME="Name" X=19050 Y=22225 W=149225
H=6350 FIELDTYPE=NAME OVERRIDE=1 VALIGN=MIDDLE
TM=330 LM=330 BM=330 RM=330>
</PAGE>
</BODY>
</OFML>
<%
Sub Txt(byval s)
Response.Write s
End Sub
%>
The code details the addition of an “expires” header to guarantee file freshness and the setting of the returned document’s “content_type” to MIME type of “application/fml. These are required to inform your browser of the need to load the appropriate plugin or ActiveX control. These .FML file type plugins (the OmniForm Internet Filler) are free from Caere and can be downloaded from www.caere.com. The body of the code represents OFML describing a single text box “name” and the supporting parameters required by the OmniForm interpreter.
The OmniForm Internet Publisher editor application generates .fml files. Mr. Benson deduced that if he added .fml to IIS’s script map for the ASP.dll he could get IIS to process .fml files through the ASP script interpreter. As it turns out, one can merely rename an OmniForm file to .asp, add the content-type header, Response.Write the tag and call yourself done. Well, not quite done. You would, as Duane suggests, probably want to dynamically fill the form with default text where appropriate (using the CALC tag) and extensively use the validation rules and auto calculations available in the OmniForm document.
An OmniForm form is downloaded into the helper program running in your browser. As in an HTML form the users data can be submitted via standard HTTP POST to a server process (ASP) which can then act upon the data.
The actual functionality of an .fml document far surpasses this explanation’s ability to do it justice. I suggest your give it a test drive. It definitely deserves one.
During Duane’s research he identified the following, “There is one serious drawback to using OmniForm. A bug in Internet Explorer prevents the use of the POST method and submitting more than 2k of data using the GET method. MS has acknowledged this problem and is planning to release a fix in version 4.1 of Internet Explorer. Netscape apparently works without any problems, although I have not confirmed this.”
Summary
|
MS Access |
Acrobat/PDF |
Crystal Reports |
Chili!Reports |
ActiveReport |
OmniForm |
|
ASP
usability |
4 |
6 |
7 |
7 |
6 |
5 |
|
Ease of ASP coding |
2 |
2 |
7 |
7 |
7 |
4 |
|
Ease of report creation |
9 |
9 |
4 |
6 |
3 |
8 |
|
General ease of use |
4 |
5 |
2 |
3 |
4 |
7 |
|
Scalability/
performance |
2 |
4 |
7 |
5 |
7 |
7 |
|
Output
Quality |
3 |
10 |
10 |
8 |
7 |
10 |
|
Plugin |
No |
Yes |
Yes |
No |
Yes |
Yes |
|
ActiveX |
No |
Yes |
Yes |
Yes(COM) |
Yes |
Yes |
|
Sub reports |
Yes |
No |
Yes |
No |
Yes |
No |
|
Multi-row |
Yes |
No |
Yes |
Yes |
Yes |
No |
|
Native ASP support |
No |
No |
Yes |
Yes |
No |
No |
|
Interpreted/
compiled |
Interpret |
Interpret |
Compiled |
Interpret |
Compiled |
Interpret |
|
Output FileTypes |
.htm, .xls, .rtf, .txt, .csv |
.pdf |
.rpt, .htm, .xls, .rtf |
.xls |
.rtf, .pdf, .xls, .txt |
.ofml, .html, .pdf |
|
Data support |
ODBC, SQL, DAO, XLS |
None |
ODBC,DAO |
ODBC |
DAO, ADO, RDO, Unbound |
None |
|
Features |
7 |
5 |
8 |
3 |
3 |
7 |
|
Download Time |
6 |
7 |
5 |
5 |
5 |
7 |
|
Compression |
No |
Yes |
No |
No |
Yes |
No |
|
Cost (street) |
$300 (Office97) |
$170 |
$420 |
$3000
(ouch) |
$400 |
$895.00 |
|
URL |
Microsoft |
Adobe |
Seagate |
Chili!Soft |
DataDynamics |
http://www.caere.com/ |
Key: 1 is bad, 10 is good
About the Author
David Cline is an Internet application consultant in San Francisco, Dave Cline develops highly interactive, data heavy Intranet and Extranet applications for companies of all sizes through out the Bay Area. Although his two children run him ragged he tries to find time to read up and explore all of the great stuff coming out for Web development these days - usually in the wee hours of the morning.
|
