Techforum

SQL Reporting Services: A radically new approach

We have been used to traditional report writers for many years now. In this era of thin client, multi-tier, object oriented, service based architecture, the good old report writers have remained more or less similar. For example, most of these provide header, footer, detail, and group based bands where you insert database or calculated fields to manage things.

The most common ones in use are Crystal and VB report writers. There are many other tools also available. With the advent of ActiveX controls, many third-party components have been available, which provide spreadsheet, graphics and ad-hoc report rendering capability. Microsoft has also released and enriched the set of tools called Office Web components, which include spreadsheet, Pivot Table and Chart controls.

Now finally Microsoft has introduced a very different and extremely powerful reporting engine called SQL Reporting Services.

In this article I am only going to enlist and highlight some great features of the Reporting Services. We will cover it in greater detail in some future articles.

Functionality

• This tool is currently in final beta stages but it is already becoming very popular with developers and architects, simply due to its elegant design.
• Needless to say, this tool requires SQL Server (not MSDE) to work. This does not mean that your application data should be in SQL Server. You can have your data in any ODBC or OLEDB compliant data source. In fact, you can even write your own custom data source just for reporting purposes, based upon your proprietary data. Most report writers used to store the metadata of the report itself in proprietary files and storage locations. This was a reason for reduced scalability and difficulty in maintaining report files. By using an RDBMS, these issues are effectively managed.
• The beauty is that the entire report and data source definition is stored as a simple XML file. This is the file the reporting engine uses to render reports. The elements and attributes required for defining a report format are fully documented. Further, you can even add your custom elements if you want to enrich available functionality. Most report writers available today never provided this functionality.
• XML based report definition allows you to directly design reports programmatically and render them. This was very difficult to achieve in currently available report writers.
• The default report designer is integrated with Visual Studio .NET so that you can create application and its reports in the same environment.
• The report designer eliminates the traditional bands very effectively. It provides three types of elements—Table, Matrix and List. Table is equivalent to the traditional report with header, footer, detail and groups. You can have multiple tables rendering different data arranged side by side!
• For each type of reporting element, you have to attach a dataset to it. Dataset is based upon data source.
• The matrix is like a pivot table. It has rows, columns and cells containing computed data. Very useful and very easy. I am sure all of you remember how much we have to struggle today to create a simple cross-tab report. Write complex queries, struggle with table formatting and so on. With this new tool, just drag the matrix on the report, specify row, column and data fields and that’s it.
• The list is free form data. It can be descriptive and cannot be represented as a structured table, somewhat like a data repeater. Whatever data elements you put in the list are repeated for each row in the base dataset. This allows you to create free form layout reports that still repeat for each data item.
• The report items can be recursive. For example, one list can contain another list. What’s more one report can be defined as a sub-report of the base report. This provides more than just drill down. The subreport also need not be hard coded. Parameters can be passed online to it based upon the area of base report clicked.
• Now, about rendering. This is the most sophisticated part. By default rendering is in HTML. But while you are viewing the report, you can simply click on the toolbar and render it in many different ways.
• The most important part is that all the reports are stored on the central SQL Server database. Usually, we have reports for each application stored and managed separately. This leads to a lot of confusion and administrative headaches.
• The reports are viewed and administered by using a Web-based implementation of the entire reporting engine. The default website provides a base structure which is folder based. Typically you will have folders created for each application or user functionality.
• How do you access reports usually? By instancing the report writer runtime. Here you don’t have to do that. Because all reports are accessible in one of the two ways:

  - By specifying the URL identifying the report on the reportserver or
  - By calling the Web service.

• The best part of the reporting server is that the entire functionality is exposed as a single Web service! You can create, manage, and view reports using various methods provided by the web service.
• The security is managed in a role-based manner and can be applied to folders as well as reports.
• User can manage their own reporting needs by accessing reports ad-hoc or by subscribing to the reports. Subscription based reports are automatically sent by mail to the users.
• All reports require parameters to be accepted from users. Here once parameters are defined, the UI for these parameters is automatically generated.
• Finally, you have many ways of rendering the reports:
• HTML (MHTML)
• Excel
• Acrobat
• Tiff (image)
• XML
• CSV
• And of course, you can write your own rendering mechanism, if you like!
• Depending upon the type of output you want, some features are disabled. For example, HTML reports do not support pagination. Whereas, XML and CSV will not support drilldown. This is primarily due to the nature of the output format and not due to inadequate rendering capabilities of the reporting services.

Scripting

Another nice feature available here is scripting. There is a command-line tool called RS.EXE. You can create scripts to manage batch processing of reports or programmatically manipulate any exposed functionality of reporting service. The script is written in a simple text editor and the file is labeled as .RSS. The script is not written in VB SCRIPT. It is written in VB.NET syntax. You don’t have to declare any object, import any namespaces.

Just assume all that is already done and a reference to the entire functionality of the reporting server web service is available to you through a pre-created object called RS. Now you simply write VB.NET code, save the file and run it with RS.EXE with relevant parameters.

No compilation, no assemblies, no deployment, it simply works. Another brilliant way to ensure that in the excitement of great UI, services based architecture and web based functionality, the good old scripting was also supported. Something to learn here about application design and enrichment.

Caching

The entire code of the reporting engine is written in .NET. Therefore, for reports based upon data that changes infrequently, it supports caching. Further it also supports snapshot based pre-execution of reports. For example, there are some reports that are periodically generated by default and viewed by users whenever required. End of day, month, quarter type of reports are typically time consuming to create. Therefore, reporting services supports pre-execution of these reports and saves them as snapshots. These snapshots can be seen by users and quickly rendered. Thus you will typically have snapshots for each month stored throughout the year for quick reference.

Here is a simplistic representation of the reporting services architecture.

Next steps

I am sure you have enough reasons now to explore this great new tool. This is a great reporting engine. It allows you to manage the entire reporting needs of not just a single application, but of the entire organisation in a structured, secure and easy to use manner. Further, it provides end-user features which minimise administrative overheads.

Another very important thing about reporting service is to look at it as one of the most sophisticated but easy to use implementations of the so called ‘services based architecture’. Use the concepts used here while designing your applications.

(SQL Reporting Services can be used for OLTP as well as OLAP based reporting and Business Intelligence solutions. Business Intelligence means going beyond traditional reporting. Succeeding in BI initiatives requires a combination of technology and many other related best practices. In this column Dr. Nitin has covered the challenges surrounding BI projects ("Cutting through the hype and facing reality") as well as the solutions and best practices ("Business Intelligence without the hype"). If you are planning a BI project, it would be a good idea to read through these articles so that you can proactively prevent common mistakes.)

About the Author:Dr Nitin Paranjape is the Chairman and MD of Maestros (Mediline). He is a consultant with many organisations, covering appropriate technology utilisation, business application of relevant technology, application architecture and audit as well as knowledge transfer. He has authored more than 650 articles on various technology-related subjects. He can be contacted at nitin@mediline.co.in