SQL Server 2000 Reporting Services were officially released from Microsoft on Jan 27th '04. And this is the first step from Microsoft to enter into the reporting services sector of BI. The tool is amazing to see the capabilities of the tool if fantastic. The first release itself is full of promise and in this article I will look at some of the key features and salient points of this tool.

Introduction

For clarification and benifit of all, SQL Server 2000 Reporting Services is an add-on to the available SQL Server functionality. This is availbale to all the SQL Server licenced users. You can always download the same from the MSDN Download site. This is similiar to the add-on tools like SQL Server Notification Services which was released last year. The concept is the same. Having said that let me also confirm and make another assumption clear that the SQL Server Reporting Services and the Notification Services will also be a part of the next version of SQL Server named SQL Server 2005 (codenamed Yukon). Microsoft will bring in the same capabilities available in the current 2000 version available in theor next version also but ofcourse those being in the managed world.

Pre-Requisites

The configuration for installing SQL Server is very minimum. Basically below are the softwares you would require to install the same.

Component	Prerequisites
Report server	Web server with ASP.NET and Internet Information Services (IIS) 5.0 or higher. ·         MDAC 2.6 or higher. ·         Connection to SQL Server 2000 SP3 instance. ·         Default website accessible through http://<servername>.
Report Manager	Web server with ASP.NET. ·         IIS 5.0 or later installed and configured. ·         Default website accessible through http://<servername>.
Report server database	Server with SQL Server 2000 SP3.
Report Designer	Client workstation with Microsoft Visual Studio® .NET 2003 and MDAC 2.6 or higher.
Administrative tools and utilities	Client workstation with access to a report server.
Reporting Services Books Online	Client workstation, no prerequisites.
Sample reports , applications, and sample database	Client workstation, no prerequisites.

Fundamentally let me drill into some of the finer details here. The first two components are server side components. And these are required for the Reporting services to run.The server is the component that hosts the Reporting server service. Once you install SQL Server Reporting services you can get two websites that you can view using:

1. http://localhost/reportserver - This is the Reports URL that allows you to deploy your Reports. (Image 1 Below)

2. http://localhost/reports - Often viewed as the Reports Manager, this URL lets you to View the reports that are published on your local server. (Image 2 below)

Find below a typical URL would like below.

The hardware requirement for the same is also minimal. You will need ard 50 MB for the Reports server services and 30 MB for Reports designer requirement (integrated with VS.NET 2003). Samples if required would mandate another 150MB. These are some of the HDD requirements. When you install SQL RS on Win2K machines you would need a minimum service pack level pf SP4.

Types of SQL Reporting Services

RS Edition	Comments
Developer Edition	·         Targeted for developers who want to explore and extend the capabilities of SQL RS. ·         Not applicable for production environment
Standard Edition	·         Designed for a production server in a single-computer configuration, which centralizes server-side report processing, storage, and tools. ·         Suitable for small reporting services system.
Enterprise Edition	Suitable for large volume production system. Available with loads of extra features like data-driven reporting, subscriptions, web-farm deployment etc.
Evaluation Edition	This is a 120 days evaluation edition and features same as developer edition.

FAQ Section on SQL RS

There are a host of other information I thought I will share in this section.

1. SQL Server 2000 RS will not get installed on Home Edition.

2. The client for designer needs VS.NET 2003 to integrate.

3. VS.NET 2003 is NOT mandatory for designing. But having said that the ONLY tool available presently for designing effectively RDL's for SQL RS.

4. Since RDL is a open flat XML structure. You need to understand the format to effectively write RDL's on the fly. This is surely possible.

5. All the server side components of SQL Server 2000 Reporting are extensible. You can also access reports using WebServices.

6. You can also design Sub-reports. Maximum level of sub-reports nesting officially reported is 20.

7. Renders in almost all known formats like PDF, XML, Word, Excel, TIFF, HTML, CSV etc. You can also define your custom format and export the data.

8. Present version DONOT support adhoc query in the current version.

9. MS-Access reports can be upgraded to SQL Server RS. There is a wizard to convert them. MS doesnt have any solution for converting Crystal Reports to SQL Server RS. There can be third party solutions for doing the same.

10. The Reports store cannot be installed on MSDE version. This is not supported. This needs to be on any version including Standard or Edition.

11. SQL Server 7.0 can be used as a input datasource for reports. Any OleDB/ODBC/ADO provider can be used as a input datasource. But SQL Server 7.0 cannot be used as the repository

12. You can use two different datasources for in a single reports itself. You need to define two different datasets for the same.

13. You can program and call a report from a Windows forms Applicaiton and display the output result into a browser instance.

14. You cannot connect dynamically to different datasource on the fly while generating reports. This is not supported in this initial version.

15. Apart from English SQL Reporting Services is available in Spanish, Traditional Chinese, Simplified Chinese, German, Italian, Japanese, Korean and French.

16. SQL Server 2000 RS DONOT come with a VS license.

17. Even the SQL Server 2000 Developer edition have the license for SQL Server 2000 RS add-on.

18. Conditional formating is supported and all these are to be done using the VB syntax.

19. Multi-valued parameters are not supported. But there are third party solutions that have extended to give this functionality.

20. Custom authentication is possible in SQL Server RS. But this is supported and available in the Enterprise Edition of SQL Server RS only.

21. Reports designing using the VS.NET IDE is more of a WYSIWYG designer.

22. Preview pane is one of the key feature I feel. You can preview the reports you designed from a single IDE.

23. Multiple formatting support exists. This is a key feature I feel, to generate different currencies/date formats etc from a single report itself.

24. RS reports can be cached and invalidated with a predefined period using a schedule. Reports can also be configured to be sent as email format.

25. We can also push reports down the wire as a email at scheduled time intervals.

Conclusion

This is a sneak preview to undersanding this amazing technology. Be locked to the site for some interesting articles on SQL Server RS. You can also write your comments down to us anytime. We will add more to the FAQ section.