SQL Reporting Services: A radically new approach
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.
- 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
- 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
- The report designer eliminates the traditional bands very effectively.
It provides three types of elementsTable, 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!
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 thats 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. Whats 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
- 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
- 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 dont 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
- 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
- Finally, you have many ways of rendering the reports:
- HTML (MHTML)
- Tiff (image)
- 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.
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
dont 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.
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
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
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 firstname.lastname@example.org