Overview of a SharePoint Report Repository and Crystal Reports
The decision to use Crystal Reports or another technology, such as SQL Server Reporting Services, depends on the resources available, the time and cost of development, maintainability, training, and many other factors. Crystal Reports has been supported by Microsoft Visual Studio for many years. As a result, there are developers already familiar with Crystal and there might be many existing reports already built on Crystal Reports. In addition, these reports can be exported in many formats including PDF, Excel, Word, and HTML.
The report generator runs once a night and performs the following steps:
- Connect to the database with the scheduling information and determines whether any reports need to be generated based on the scheduling information.
- For those reports that should be generated, gather the necessary data and parameters.
- Connect to the database with the data that should be reported and create the report.
- Export the report from memory to the local file system in the desired format.
- Upload the report to SharePoint.
using CrystalDecisions.CrystalReports.Engine; ... ReportDocument reportDoc = new ReportDocument(); // where reportPath is the file path to the Crystal .rpt file. reportDoc.Load(reportPath); // Pass in each report parameter reportDoc.SetParameterValue(parameterName, parameterValue); ... // Export the report to the local filesystem in PDF format reportDoc.ExportToDisk(ExportFormatType.PortableDocFormat, fileName);
The application can access SQL Server directly or use Stored Procedures. Once it has the necessary information, the application can load a Crystal Report report and pass in the required parameters, as shown in the code sample above. After exporting the report, it can call a web service to upload the report to the appropriate document library and sub-folder.
The documents can be uploaded to a SharePoint site through a number of options. There are many built-in SharePoint web services that allow access to the document libraries, user alerts, lists, or anything else in SharePoint. The FrontPage Remote Procedure Calls are still available in WSS, but it can be cumbersome to use and not offer access to all the features of SharePoint. If there is a need for more customization, a custom SharePoint web service can be created. As with any web service, the report generator does not need to be on the same server as SharePoint. In addition, the custom web service can take advantage of the SharePoint object model. In a Web Service Web Method, given a byte array called fileContents:
SPSite site = new SPSite(siteURL); SPWeb web = site.OpenWeb(); SPFolder folder = web.GetFolder(folderURL) // Set to true for overwriting SPFile file = folder.Files.Add(fileName, fileContents, true);
The preceding code sample uses the SharePoint object model to upload a document to the SharePoint directory and overwrite any existing files at that location. This can be extended further to dynamically create folders or some other custom logic. The report generator application is able to call the methods exposed by the web service to upload reports to the desired document library.
This article presents an overview of a reporting system that uses SharePoint as the basis for a report repository. The reporting system would offer users a central interface for accessing reports and scheduling the creation of new ones. Once report schedules have been defined, the system can leverage a .NET application to generate and upload the reports themselves. Although Crystal Reports is used in this article, it is not the only solution for generating reports. New report generators that generate different types of reports or use new sources of data can be added to the system. With such changes abstracted away from the user, users can focus on managing their reports and scheduling new reports when necessary.
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 Urbana-Champaign.
Page 3 of 3