Organizations are continuously faced with the need to organize information and present them as reports. Combining the ability to generate and manage reports into a single system addresses this need and is a powerful tool for users.
An automated report generation process has many benefits. It can quickly generate a complex report that would otherwise require a considerable amount of time to create by hand. People have more time to focus on other business needs rather than create reports. In addition, this means that the end users have quicker access to their reports as well. When timely decisions have to be made based on these reports, the time saved can be invaluable. Lastly, this reduces the potential for human error when creating the reports while increasing the report’s acceptance among its users.
As reports are generated, there is a need for them to be organized and accessible to its users. A centralized document repository makes it easier to keep track of different reports by minimizing the risk of inconsistent reports. In addition, it allows an organization to better manage who has access to which information.
A reporting system with these advantages can be created combining SharePoint’s document management features with a .NET application for generating reports.
Overview
The reporting system shown in Figure 1 supports the following process:
- Manage existing reports through SharePoint
- Schedule reports to be created
- Generate reports
- Upload new reports to SharePoint
Each component can sit on the same machine or on different machines, depending on how they are implemented.
Figure 1: Overall Architecture
Windows SharePoint Services
SharePoint is a Microsoft platform for collaboration and document management based on web-portals. Currently, there are two SharePoint offerings from Microsoft: Windows SharePoint Services (WSS) 3.0 and Microsoft Office SharePoint Server (MOSS) 2007. The content of this article focuses on WSS because it is a free add-on to Windows Server 2003 and provides the fundamental document management functionality. The same concepts can be easily translated to the more robust MOSS 2007 because it is built on top of WSS 3.0.
The SharePoint site allows documents to be stored in a central location and be accessible to many users. A basic WSS site is shown in Figure 2, with a document library named “Documents” that is linked via the quick-launch menu.
Figure 2: Basic Windows SharePoint Services site
Multiple document libraries can be created as deemed necessary. Each one can contain folders, files, and reports and can be individually customized by their settings and the views they present to the user.
There are many features that can be specified in a document library’s settings:
- Version History: Document Version History can be enabled to keep track of the different revisions for a particular file. Although this would consume more space because SharePoint now maintains a new copy for every modification, this is particularly useful when applied to reports. Using the version history, a user can compare previous reports and see who approved each one if content approval is enabled.
- Check In–Check Out: In some cases, users should not be able to modify reports. However, in the situations where they are allowed, the document library can require that the report be checked out first before editing. This ensures that multiple users are not updating the same report simultaneously.
- Alerts: Users can be kept up-to-date with the document libraries through user alerts. By using the SharePoint interface to create alerts, users can have SharePoint email them when changes to a document library occur, such as when a newly generated report is added to the document library of interest.
- Report Security: SharePoint users are organized by user groups. As many user groups can be created as necessary. One of the features of SharePoint is the ability to set the permissions of a Document Library or on the individual items within the Document Library. For example, this allows certain reports to only be viewable by specific users or groups.
Although most user groups might only have the permission to view documents, an Approval group should be created with the permission to view drafts and manage lists. This group will be responsible for content approval. Content Approval is a common requirement when managing documents. With Content Approval enabled for the report document libraries, any reports that have been modified or newly uploaded will be in a pending state until it is approved or rejected by a member of the approval team. While in a pending state, only members of the Approval team and Administrators will be able to view the documents.
When applied to reports, this is a powerful concept. A report may contain bad or incomplete results. In such a scenario, it should not be viewable by most users until the issues are resolved.
As a result, auto-generated reports initially will be in a pending state and invisible to users outside of the Approval group. When a member of the Approval group has confirmed that the report can be released, the member can set its state to approved; the report automatically becomes visible to the general users. If there is a problem with the report, it can be rejected and scheduled to be generated again; the rejected report remains invisible to the general users as the report generation cycle repeats.
Such an approval process can be enhanced further by being encapsulated in a workflow. SharePoint supports workflows that can be created through the SharePoint interface. For example, emails can be sent out along with a task that can be assigned from within SharePoint. Further customization can be accomplished via the SharePoint Designer or Visual Studio.
Report Scheduling
There often are reports that are generated on a weekly, monthly, or even annual schedule. To facilitate scheduling such reports, the scheduling information should be stored in a database. Because the scheduling information might be shared with applications outside of SharePoint, as shown in Figure 1, the SharePoint content database is not used. By using document libraries and web part pages, users are able to view and modify the report schedules through SharePoint. By doing so, the SharePoint site becomes the central interface for both managing and creating documents.
Within SharePoint, a document library that hosts web part pages should be created. A web part page is an ASP.NET page composed of controls called web parts. Although there is a set of default web parts that comes with SharePoint, custom web parts will be required to access the report schedules. These web parts can be built by using ASP.NET and Visual Studio or by using a wrapper web part such as a SmartPart to host ASP.NET user controls.
An alternative approach that does not require custom web parts is to have web part pages that use the built-in Page Viewer web part. The Page Viewer web part can display web pages and can be used in conjunction with an ASP.NET application, as shown in Figure 3. This allows support for a new or existing ASP.NET application to access the scheduling information.
Figure 3: Sample Report Schedule page
There can be one scheduling page or multiple scheduling pages, depending on the need. Through the report scheduling pages, the users should be able to specify all the necessary information to generate a particular report such as the type of report, how frequent the report should be generated, and the destination document library.
Once the report scheduling pages are created, links in the quick launch menu can be added for each one, as shown in Figure 4 under the Scheduling menu. This allows easy access to the scheduling pages. By setting the permissions for the Report Scheduling document library, only users belonging to the appropriate user groups will have the ability to modify the schedules.
Figure 4: Sample custom Quick Launch links.
Report Generation
Reports can come in many different file formats such as Adobe PDF files, Microsoft Word documents, or Excel Spreadsheets. There can be many different sources from which reports are generated and uploaded to SharePoint. For this article, the primary report file format will be a PDF generated from a .NET application using Crystal Reports.
PDF reports offer a common format that easily can be linked in emails and shared. At the same time, they are less likely to be modified. Because this is hosted in a Windows environment, a .NET application can be created to serve as the report generator. The application should not require user interaction and can be a simple console application running in Windows Task Scheduler.
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.
Uploading Reports
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.
Conclusion
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.
