SQL Reporting Services and Sharepoint

http://brianecooper.com/coop/archive/2004/10/19/237.aspx

Reporting Services, Sharepoint, DSP Panel, and RSWebParts Overview



From an article by K.S.Ganesh on C3 Corner



INTRODUCTION

SQL Server Reporting Services is a comprehensive, server-based platform for creating, managing, and delivering reports that contain data from relational and multidimensional data sources.

This article explains in brief about the Features that are supported and about Designing a report.

Features

1. Graphical Report Authoring
Reporting Services has a powerful graphical report designer that lets users create report definitions by dragging and dropping design and data elements.

2. Flexible Report Delivery
With support for both push- and pull-style report delivery, Reporting Services provides a flexible mechanism for generating reports. In pull-style report delivery, users can run previously created reports on demand. Users can also automate push-style report delivery so that the reporting server generates reports based on predetermined events.

3. Multiple Data-Source Options
Reporting Services isn't limited to using data from Analysis Services and SQL Server databases. The new product is compatible with other databases, including Oracle. Data-source options include ODBC, OLE DB, XML for Analysis, and Web services. Reporting Services can use multiple data sources for a single report and can even use reports as sources for other reports.

4. Multiple Reporting Options
Reporting Services supports a variety of reporting formats, including Web-based, XML, email, and standard printed reports. In addition to these basic options, Reporting Services supports several more sophisticated formats including Excel, PDF, and custom formats.

5. Support for SQL Server and Analysis Services
Reporting Services can create reports for relational databases such as SQL Server and for Analysis Services. This flexibility lets users generate reports from SQL Server databases or Analysis Services cubes without using Office or other external applications.

6. Support for ActiveX controls
Reporting Services provides support for embedded ActiveX controls helps to add many custom capabilities i.e. such as a calendar or multimedia elements to the reports.

Designing a Report

Two templates are provided in Visual Studio.Net 2003. Report Project Wizard which helps to build the report from scratch and Report Project which invokes Report Designer.

First step is to define a Data source. Data Source can either be defined for each report or in a report project so that multiple reports share the same Data source.
In addition, a report can contain multiple data sources if it needs to retrieve information from more than one location.

The Report Life Cycle consists of the following phases:

Authoring

The Authoring phase consists of :

1. Defining the Data area where the Query is constructed to retrieve the data. Reporting Services executes the query defined for the data set against the data source. The rows and columns that result from this query
become the data set. It is possible to define the Input Parameters for the
Query.

2. The Layout Area where the report is constructed by dragging controls from the Toolbox and fields from the Fields window and dropping them in the Report design. Only four Report controls can be bound to a Data set which are :the Table, Matrix, List, and Chart controls.

The Table control produces a column-and-row report with optional gGrouping and subtotaling.
The Matrix control outputs a Crosstab report.
The List control produces repeating blocks of free-form data.
The Chart control also interacts directly with a data set.
The definition of the report is stored in a Report Definition Language(RDL)
which is an XML.

Visual Studio deploys the RDL to a Report Server, which stores it in a SQL Server database. When Reporting Services delivers a report to a user, it processes the RDL report definition and renders it into a more familiar format such as an HTML page or an Adobe PDF document.


Management

After a report definition is complete, the report designer publishes it to a report server and it becomes a managed report. Report Manager is used to manage the reports and the reporting environment.

Report definitions, folders, and resources are published and managed as a Web service.

The Security, Properties and Scheduled operations applicable are defined for the items like reports,folders,Data source connections etc. It mainly consists of organizing folders to store various reports, Securing access rights to folders and reports and setting up shared schedules and shared data sources that are available for general use.


Deployment

The report can be deployed by uploading its RDL file directly from the Report
Manager. To use this method, you must locate the RDL file for the report you
want to deploy. This will also help to deploy a singe report from a Project which
might contain several reports.


Delivery

The reports can be distributed in the Report Catalog through the Report Manager, Microsoft's SharePoint Portal Server, or a custom application. The Report Manager lets you organize reports into folders and lets users access the reports on demand or subscribe to them by email. Reports can be live reports in which the data is requeried each time they run, or they can be snapshots of standard reports that are updated regularly.


_______________________________________________________________________________________

Q&A


Q. How is SQL Server 2000 Reporting Services licensed?

A. Reporting Services is part of the SQL Server 2000 license. There is not a separate license for Reporting Services. If you have a licensed copy of SQL Server 2000, you may run Reporting Services on the same server for no additional license fee. If you want to run Reporting Services on a server that is not licensed for SQL Server 2000, you will need to obtain a SQL Server 2000 license for that additional server. This is the same licensing model used for SQL Server Analysis Services. For more information about licensing scenarios, see the

Q. What are the main components that come with Reporting Services?

A. Reporting Services is comprised of four main components:

· Report Server. A Web service that handles report management and processing. It connects to a SQL Server database for report metadata and caching.

· Report Server Database. A SQL Server database used for report metadata and caching.

· Report Manager. A Web-based tool for managing Report Server.

· Report Designer. A graphical report-authoring tool that is integrated with Microsoft Visual Studio® .NET 2003.

Q. Is Reporting Services free?

A. No. Reporting Services is part of the SQL Server 2000 license. If you have a licensed copy of SQL Server 2000, you may run Reporting Services on the same server for no additional license fee. If you want to run Reporting Services on a server that is not licensed for SQL Server 2000, you will need to obtain a SQL Server 2000 license for that additional server. This is the same licensing model used for SQL Server Analysis Services


Q. Can Users make reports

A. Active Views

Active Views is a system for report authoring and data browsing that anyone can use. Its intuitive interface puts business intelligence within reach of ordinary business users, leading to faster, better decision-making at lower cost. Active Views will be renamed Reporting Services Report Builder and will be included in beta 3 of SQL Server 2005.

Active View Tour

Q. Does Reporting Services Integrate into Sharepoint?

A. Yes, by using a tool named RSWebparts by Bryant Likes