By Tim Landgrave

System users have two key touch points with the data processed by the systems we developers write. One is when the data goes in (data entry), and the other is when it comes out (reporting). But for most systems, reporting seems to be an afterthought instead of a carefully planned part of the overall architecture. Let’s look at some of the key factors you should consider when architecting reporting systems, with the focus on how those factors may be influenced by a design based on the .NET Framework.

How live is live?
The first key architectural decision for any new system involves the age of the reporting data. It’s very important to ask questions such as, “Will you ever need to report on transactional data before any end-of-day processes have been completed?” In most cases, you should design the system to use some kind of staged data rather than risk potential data contention or even possible corruption by trying to retrieve live data from a system. One of two things is bound to happen: Either the report won’t be correct because you were trying to access data that was incomplete, or someone’s update will fail because your report locked a critical section of a table when the update needed access to the same data.

Understanding these issues makes it easier to plan around them. For example, one of the systems that I’ve been working on uses SQL Server triggers to create a temporary reporting repository. As transaction records are added to key tables during the day, copies of those records are automatically maintained in corresponding copies of those tables using INSERT, UPDATE, and DELETE triggers on the key tables. All of the actions against the reporting tables are performed as SQL Transactions, so a SELECT statement won’t retrieve any of the records until the transaction completes. Any reporting that requires comparisons can be done with the temporary tables and the history table without fear of locking or deadlock issues. At the end of the day, all transaction records are rolled to history, and the reporting tables are cleared for use the next day.

Effective use of the DataSet is one of the key advantages of the .NET Framework in developing reporting mechanisms that minimize data contention. Some of the systems we’ve developed use the DataSet as the base of all system reports. Since the DataSet can be populated quickly, reused, and cached, and it stores a hierarchical representation of data, it makes a perfect data source for one or more reports. Creative use of the DataSet can greatly minimize your application’s need to access and potentially lock portions of the underlying database. But the tools you use to develop reports have to recognize the DataSet as a valid data source.

Picking the right tools
The next major decision you face when architecting a system is whether to develop the reports by hand or use a report writer. I find it hard to believe that companies still feel the need to create hand-coded reports with the breadth of report-writing tools available. But their decision to do so generally hinges on two major issues: report processing and licensing fees. In many cases, the system stores the data in one format for data entry and another for reporting. To create meaningful reports, the reporting system itself transforms the data in some way before displaying it. Because of this report-processing step, many developers prefer to just finish the job and generate the report in code. But this is a bad decision in most cases. Since all .NET report writers can take a DataSet as their data source, it’s simple for developers to create reporting objects whose output is a DataSet, and then pass this DataSet to the report writer for final preparation and display. Why would you want to develop, maintain, and enhance your own engine for the preparation and display of data when so many fine reporting engines already exist?

The main reason—and another major issue to consider when deciding whether to use a report writer—is the licensing cost. When choosing a report-writing product, you must consider the licensing implications for the developer’s report-writer environment, the report generation and distribution environment, and the end-user report-writer environment. You may have to pay licensing fees for each of these environments to use the product. However, I recommend ActiveReports for .NET from Data Dynamics for the vast majority of consultants’ reporting needs. It has a per-developer seat cost and no runtime or distribution costs for Web or Windows reports. This high-end product even allows distribution of a Windows-based report writer that enables your customers to modify existing reports or design their own reports for the system you provide.

Managing security
The last point to consider when architecting your reporting strategy is how to handle the security of the underlying data exposed by the reports. Given the .NET Framework’s robust support for Active Directory (AD), you can easily implement security across the reporting system by applying AD permissions to report files themselves and granting permission to report processing objects only to those groups that should have access to specific data. Then the administration of all security—including the reporting system—can be managed by administering group membership.

You generally design reporting systems so that the only reports displayed on a menu are those the user is allowed to run based on his or her group membership. Architecting the system from the ground up to support AD permissions and to use groups properly will help you implement a secure and easily manageable reporting system.