WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
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.
As shown in Figure 1, the application first needs to determine whether there is a report that needs to be generated and, if so, what type of report it is. In this scenario, the reporting application is scheduled to run daily and will check the ScheduledReport table, as shown in Figure 3.
Figure 3: ScheduledReport Table
Based on the ScheduledDate field, the application knows which reports need to be generated and their types. Using the ReportTypeID field, the application finds the corresponding row from the ReportTypes table, shown in Figure 4.
Figure 4: ReportType table
From the ReportType table, the application knows the location of the report's .RPT file by using the ReportRPTFilePath field, which may be a path to a local folder or a network drive. In addition, the table offers a location to store other report type-specific information. For example:
- ExportFileFormat: A particular type format that all reports of a particular type should be exported in.
- SharePointDocLib/SharePointFolder: SharePoint document library or folder that all reports of a particular type should be uploaded to.
- Description: Description of the report type that can be tied into a user interface, such as a combo box where the user can select which report to generate.
Note that such fields could be treated as defaults that can be over-ridden by corresponding fields added to the ScheduledReport table.
The application now has to determine what parameters are required by the report and their corresponding values. As shown in the code sample later in this article, the application needs to know both the parameter's name and the value when loading parameters into the report. Using the ReportTypeID, the application can determine the report's parameters and type from the ReportTypeParameter table, shown in Figure 5.
Figure 5: ReportTypeParameter
The ReportTypeParameter table, as shown in Figure 5, contains extra columns that would be useful if the application had a user interface for getting user input, such as a DataGridView.
- MinValue and MaxValue: Minimum and maximum values to be used with ParameterType for input validation.
- DataSourceTable: For a user interface, a database table from which potential parameter values could be used to populate a combo box.
In the scenario of scheduled reports, the parameter values are already available and are stored in the ScheduledReportParameter table shown in Figure 6.
Figure 6: ScheduledReportParameter Table
The application has a collection of parameters from the ReportTypeParameter table that can be mapped to values from the ScheduledReportParameter table by using the ScheduledReportID and ParameterID columns.