RPT Software - Batch Reporting Module Examples

Our software exposes two functions:

RPT_CreateSingleFile(ReportName, ReportFile, ReportFormat, QueryText, QueryName)
This function creates a single file and returns a string indicating either "Success" or the error that occurred trying to create the file

 Function arguments include:

• ReportName is the name of the report in Access
• ReportFile is the complete path for the result file (ex: "c:\reports\myrpt.pdf")
• ReportType is the format ("PDF", "RTF", "XLS", "RAW" or "SNP")
   "PDF" = Adobe PDF Format
   "RTF" = Rich Text Format
   "XLS" = Microsoft Excel
   "RAW" = Microsoft Excel from the results of the query
   "SNP" = Microsoft Snapshot
• QueryText is the SQL to be used for a WHERE clause or the query(s) which drive the report
• QueryName is the name of the query(s)

RPT_CreateFiles()
This function creates multiple files by using a table that holds report requests.

Details of how it works:
This function uses the table tblReportQueue and runs through every record in which the field "Complete" is False. For each record it uses the various fields to call RPT_CreateSingleFile(...) and then fills in the fields: "Complete", "TimeStamp" and "ErrorMessage" appropriately. This allows you to use the tblReportQueue as an audit trail of exactly what happened with every report.

The best way to see examples is to download the evaluation and look at the examples provided, however here are a few code samples:

--------------------------------
Example #1
--------------------------------
This example is a simple call to a create a report and save it in PDF format:

Result = RPT_CreateSingleFile("rptExample", "C:\Reports\Report1.pdf","PDF") 

--------------------------------
Example #2
--------------------------------
This example is a simple call to a create a report and save it in PDF format while including a WHERE clause to filter the report results to only records where the Salary is greater than $50,000:

Result = RPT_CreateSingleFile("rptExample", "C:\Reports\Report1.pdf","PDF","WHERE Salary > 50000") 

--------------------------------
Example #3
--------------------------------
This example is a simple call to a create a report and save it in PDF format while including SQL for all the queries that drive this report (For some reason we want to just see customers that make more than $35,000 and products with product code 'CG').  This report happens to have a sub report so we provide SQL for both the main report and the sub report (notice the "|" character is used as a delimiter between the multiple query strings and the multiple query names):

Result = RPT_CreateSingleFile("rptExample", "C:\Reports\Report1.pdf","PDF","Select tblExample.* WHERE Salary > 35000 | Select tblProducts.* WHERE ProductCode = 'CG'","qryMainReport | qrySubReport") 

--------------------------------
Example #4
--------------------------------
This example creates 10,000 PDF files by appending records to our tblReportQueue table and calling our function: 

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryAppendDailyFixedReportsToQueue"
DoCmd.SetWarnings True

Result = RPT_CreateFiles()

--------------------------------
Example #5
--------------------------------
Scheduled batch report creation, reports emailed out to customers

Possible Tools Involved
Scheduling: Windows NT Scheduler, SQL Server Scheduler, or your favorite scheduler  
Report Creation: Our product, your Access database, Adobe PDFWriter or Win2PDF
Email Distribution: Access/Outlook, SQL Server/SQL Mail, ASPMail, or any email system or email component.

New example added to evaluations 10/28/2002 to demonstrate how to do batch reporting and emailing using ASPMail email component.  See email.zip file found in Access2000 and AccessXP evaluation versions.

--------------------------------
Example #6
--------------------------------
Scheduled batch report creation, ftp reports to web site, web/database interface used to drive report selection (Example: a customer comes to your web site, logs in and is presented with a web interface that allows them to access all the possible reports they are allowed to see).

Possible Tools Involved
Scheduling: Windows NT Scheduler, SQL Server Scheduler, or your favorite scheduler  
Report Creation: Our product, your Access database, Adobe PDFWriter or Win2PDF
Transfer Reports to Web site: Automated FTP or automated copy command
Web Site: ASP/Access, or ASP/SQL Server, or your favorite web tool and your favorite database (ASP, COM/COM+, .NET, Cold Fusion, Perl, CGI, etc...) (SQL Server, Access, Oracle, DB2, Sybase etc...).