July 25, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

A Database Driven Reporting Application

  • June 16, 2008
  • By Chi-Wei Wang
  • Send Email »
  • More Articles »

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<string, object> reportParameters =
   new Dictionary<string, object>();
...
// Determine whether application should generate a report:
// 1. If this is checking for Scheduled Reports, then check the
//    ScheduledReport table.
// 2. If this application 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<string, object>
         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.


Tags: .NET



Page 2 of 2



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel