During the design stage of any software project, regarding any business/finance activity,  it’s often visible from the very beginning that some kind of reporting component will be needed for providing user friendly output data. It becomes especially important when it comes to developing a website or a web application, where the Quality of Service and resource consuming are the most important factors in the application’s life cycle. If the mentioned project is an ASP .NET project using Microsoft’s SQL Server 2000 or later, then the choice of Microsoft Reporting Services for this task is rather obvious.

The Microsoft Reporting Services come with a SQL Server database as a royalty free solution, that can be used anywhere. There are however two different “levels” of Microsoft Reporting Services. These are:

• Server reports
• Local reports.

Below I will describe them both.

Server reports (*.rdl files)
Server Reports are Reporting Services reports created with a direct interaction with MS SQL Server. It means the report can retrieve data directly from MS SQL Server from tables/views with queries/executing stored procedures. It’s especially handy in big projects with big databases consisting of vast amounts of data.The software architect of such a system is often tempted by features of one of the ORM systems available on the market (like NHibernate/Active Record) and designs the system’s reporting layer or component to use the data model provided by the ORM or even designs the reporting component to be tightly bound with the Data Access Layer provided by the ORM. This often proves to be fatal. Very big projects with great amounts of incoming and outgoing database information are simply to heavy to be handled by such a solution. It happens very often that all webpage’s reporting business and data logic has to be rewritten because it becomes unusable. The business and data layer threads of the application begin to consume more and more server’s resources and webpages produce timeouts instead of the expected result. Please also be aware that ORM’s have a tendency to generate a really BIG and ineffective queries, that take a lot of time to execute (I have seen a query that could be a simple SELECT, but resulted in 14 JOINs after being generated by Active Record… Imagine how it can affect the system that gets 1 or 2 MB of new data on a daily basis, that has to be rendered to reports and provided to users later on…This can be avoided by a perfect data model design sometimes).

When it comes to report a big amount of data to the user I would advise the usage of the Server Reports. There are 5 important reasons for such an approach:

• The report doesn’t use the business layer / data layer of the application. It retrieves data directly form SQL Server. It provides a several security options (like transport’s SSL security layer and DB security) so it’s quite safe,the report can be generated on the server or client sides, depending of the way of implementing and configuring this solution. It’s important when we have low resources to use on one of the sides.
• It’s really, really easy to create and view a report – Microsoft Reporting Services also provide a designer that can be used in Visual Studio or in SQL Server’s Enterprise Manager and a control called ReportViewer, available in Web or Win forms, for displaying data in the application(more about the Report Viewer can be found on http://www.gotreportviewer.com/). In opposition – to create a report without an advanced designer , it takes a lot of time and coding to implement a sufficient solution. Additionally, ReportViewer can export data to several formats: PDF, Excel, HTML, JPG. The Server report version is richer in export possibilities than Local report described below.
• Data aggregation / calculation features can be implemented as a SQL queries / stored procedures on the server or as Expressions (with mathematic/logical/aggregation/formatting functions) or even in Visual Basic .NET / C# .NET.
• Passing parameters to reports is easy as well, and results with auto-generation of drop down controls or textboxes in the ReportViewer control.

Report Designer in Visual Studio

Local reports (*.rdlc files)
Local report is a Reporting Services report, that can be used indirectly. It means Local report can use a dataset or an object as a data source. It doesn’t use queries and cannot execute stored procedures, so it’s slower than a server solution. The datasources provided have to be filled with data before consuming them by the report. It may sound rather obscure in comparison to server reports but in fact it allows architects to move whole reporting features to controllers if needed. I wouldn’t advise this approiach in big projects, however this solution seems to be very useful in smaller ones or in different tasks (like ORM-based data access layer and business layer application mentioned above – the data model objects can be provided as a data source). This often proves very useful during development of non-ASP .NET application (but still in .NET framework).

Report Viewer control can be encapsulated by another class and used as a “file thrower”. It can be used as follows:

• Project doesn’t have the possibility of placing the Report Viewer control on a webpage, but uses .NET framework.(e.g. Castle/Monorail/NVelocity project).
• Developer encapsulates the ReportViewer control in a DLL library or project, that can be used from within theapplication, and creates an RDLC report the normal way (he cannot forget about an interface for providing a data source for the report).
• Report Viewer has the export feature mentioned earlier. Therefore the ReportViewers Render() method can be called , report rendered with data from the provided data source, exported to one of the possible formats, converted to a binary stream, and sent to the user with Response.Write() method.

It’s a very efficient way of creating extensive business data reports. The ReportViewer’s exporting capabilities can be extended with various libraries available on the market to be able to export to even more formats (those are expensive …). However, the architect has to have in mind, it’s more efficient to use the *.rdl Server Reports instead.

No matter of the selected technique, server or local, the architecs and developers should have in mind, that it’s a lot better to create a “flat” data structure instead of complex one as a report data source. When saying “flat” I mean a one view or stored procedure producing a view (server reports) or one object/dataset without properties of type implementing IEnumerable interface (local reports). It may be tempting to use a complex object (Microsoft often encourages to do so…) but I wouldn’t go that way. The local reports have a real problem with digesting collection/array properties of a data source object. In fact, I wasn’t succesful in direct usage of such a “nested” collection. And it’s easier to group/sort data on one table or view and within a collection of “flat” objects.

Best practises are:

• Server Reports- creating views from tables, creating stored procedures using data from previously created views, and executing those procedures from within the report.
• Local Reports – creating a simple dataset with tables populated in “code-behind” (a table is always flat…) or creating an IEnumerable list of objects. Those objects can have properties only of the simple types (int, double, string, decimal, date…etc.). Such data sources can be easily consumed and digested.

Conclusion
As you probably see, there is a lot of possible usage opportunities for MS reporting Services in .NET applications. There is no faster way of retrieving/rendering data, it’s supported by Microsoft and …free to use.