provided by: 
Originally published at Internet.comThe 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.
Overview
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.
Loading Reports in C#
The application now has all the report information necessary to generate the report. As described in Figure 1, the application then loads the information into the report to generate the report, as shown in the following code sample. using CrystalDecisions.CrystalReports.Engine; ... // Crystal Report Document ReportDocument reportDoc = new ReportDocument(); // Path to Crystal Report .RPT file String reportPath; // Collection for Report Parameters and values Dictionary reportParameters = new Dictionary(); ... // Determine whether application should generate a report: // 1. If this is checking for Scheduled Reports, then check the // ScheduledReport table. // 2. If this aapplication has a user interface, wait for user // inputs. ... // Populate the reportPath, and reportParameters collection // based on the information provided in the database and/or // the user. ... // where reportPath is the file path to the Crystal .rpt file. reportDoc.Load(reportPath); // Pass in each parameter and value from the parameter collection. foreach (KeyValuePair paramEntry in reportParameters) { // SetParameterValue(string, object) reportDoc.SetParameterValue(paramEntry.Key.ToString(), paramEntry.Value); } // Export the report in PDF format reportDoc.ExportToDisk(ExportFormatType.PortableDocFormat, fileName); Conclusion
This article shows how report-related knowledge can be shifted from an application's code to a database. This allows the application itself to remain unchanged because updates to the database will enable the application to handle new and modified reports.
Additional customization and enhancements can be handled by extending the database tables to hold additional information or to support new functionality such as emailing reports to a set of users. Although the article describes a reporting application that checks scheduled reports, the design also can be integrated with a user interface to allow the user to select a report to generate and then input the desired parameter values.
About the Author
Chi-Wei Wang is a senior software consultant for Crowe Chizek and Company LLC, focusing on Windows application development, Crystal Reports, SQL Server, and SharePoint. Crowe Chizek and Company LLC is a Microsoft Gold Certified Partner.
Chi-Wei graduated with a Master's Degree in Computer Engineering from the University of Illinois at Urbana-Champaign.
Author: Chi-Wei Wang
Read article at Internet.com site