A Database Driven Reporting Application
The reporting needs for an organization often can change, requiring updates to existing reports as well as the addition of new reports. Having to update and re-deploy software each time reports are updated or added can quickly become time consuming. Rather than writing custom code for each type of report within the application, the report-specific information can be shifted outside of the core application. This allows an application to adapt to changing and new reports while minimizing the need for code updates. Instead, updates can be made to the database.
The article gives an overview of a how an application can retrieve report-related information from a database to generate reports. Crystal Reports is used for the reports since it is integrated with Visual Studio.
To minimize code changes, the application should not have any custom code tied to any specific report. All report-specific knowledge can be kept outside of the application. Even though such information can be stored in configuration files or flat files, leveraging a database has many advantages.
A database offers a convenient way to organize and store related information and can be accessed easily by applications. It can serve as a central location for storing all reporting information as well as offering secure access. In addition, it allows multiple applications to access the data and request reports through the same reporting application.
The basic process used by the reporting application is shown in Figure 1. Once the application finds a report to generate, it needs to load the report's .RPT file, and pass in the appropriate parameter values to the report. Afterwards, depending on the application requirements, the application might display the report to a user, upload it to a SharePoint site, or even email it to users.
Figure 1: Report Generation Process
Database Report Information
The sample application that will be discussed is a scheduled report generator used to generate reports scheduled for specific dates. The pending report requests are maintained in the database by other applications through which users schedule reports to be generated. The sample application requires the database tables shown in Figure 2.
Figure 2: Database Diagram
- ScheduledReport: A table populated with scheduled requests for reports to be generated on certain dates. Populated by another application.
- ScheduledReportParameter: A table populated with the parameter values required by each scheduled report. Populated by another application.
- ReportType: A table populated with all the possible type of reports and related information.
- ReportTypeParameter: A table populated with the parameters required by each report type and related information.
Page 1 of 2