Skip to main content

SQL Server Reporting Service – Few Important Concepts And Overview

SQL Server Reporting Services, due to its robust but user friendly architecture, is an obvious choice for Enterprise or in-house reporting, for product management, sales, and human resource and finance departments. Its flexibility makes it an ideal for usage in applications as well (Deliver User-Friendly Reports from Your Application with SQL Server Reporting Services - MSDN Magazine August 2004). Reporting Services offer various delivery methods, from ftp to email and it provides various rendering formats therefore it makes adds easiness to business to business reporting. Similarly because of its flexibility and industry standard security model, it makes extranet and secure internet reporting easily achievable.

The reporting system comprises for following main components.

Main Components of SSRS

Report Server & Report Server Database

Report Server is an integrated web service which controls report generation and management. Report Server database is a SQL server database which is used as data dictionary about reports (catalogue, groups) and caching. SQL server agent is used for scheduling the reports.

Report Manager 
An ASP.NET based Web interface for managing the reports, setting security and user permissions.

Report Designer is considered as a part of reporting services in Microsoft documentation but because RDL (Report definition language) is an XML based open standard, any vendor can implement it and therefore a single tool can’t be categorized as report designer. However, Microsoft provides a graphical report authoring tool with Visual Studio.NET 2003 or later for creating reports which automatically creates RDL markup at the backend.

Reporting services were designed with concept of disparate data sources in mind. A single report can retrieve data from multiple heterogeneous databases and render it to make it look like it’s from a single source. It provides built-in windows authentication security however one can write custom security wrapper to fit particular enterprise needs.

With SQL Server reporting services, multiple delivery methods and formats work like a charm. You design a generic report and reporting server takes care of exporting it into HTML, Excel, PDF, WAR(web archive), TIFF, CSV and XML format. As always, one can write his own custom format writer wrapper class for any custom format.

Reporting Services Delivery Formats

Reporting Services Delivery Formats

Reporting Services provide four distinct formats of report delivery also known as subscription in Reporting Services arena; Individual subscription, data driven subscription, SMTP delivery and file share directory (FTP) based subscriptions.

Reporting Services Architecture

Reporting services architecture

Above diagram schematically explains Reporting services architecture

Beside the code segments which can be written within a report in VB.NET, these API provides makes it more programmable. The application program interface can be classified into following categories.

  1. Data processing extension application programming interface (API)
  2. Delivery extension API
  3. Rendering extension API
  4. Security extension API
  5. Web service API
  6. Windows Management Instrumentation (WMI) configuration API

Interactive interfaces are another salient feature of SQL Server Reporting Services. Reports designed in SQL server reporting services supports charts, document map, freeform, cross tab matrix, sub reports and tables. Reports can also be parameterized and event driven (supports actions). Management is one of the most important parts in any reporting system; SQL server reporting services has it all planned. It manages jobs from a user friendly console, provides personalization "my reports", tracks report history, manage shared data sources, provide search, subscription and snapshot features with shared subscription from one stop shop, the management console. Reporting Services supports report caching and stores reports execution data in execution logs,

Report Generation and Publishing

Report Generation and Publishing

As defined in the diagram above, the process of report generation and publishing consists of the following main steps.

  1. Reporting server engine (Report Processor) receives the request for a particular report. A request includes parameters and formatting instructions.
  2. Report Processor retrieves the report definition on the basis of request.
  3. For the corresponding RDL, the report processor then retrieves the report data for specified data sources.
  4. Report Processor performs transformation on reporting data and sends the document data along with schema to rendering engine (rendering extension).
  5. The extension publishes the final rendered report.
  6. The following steps are basics of how reporting services work. The extensions (data processing extensions, rendering extensions etc) can be custom built and wrap around the existing set of API to provide extended functionality.

SSRS Integration with SharePoint 2007

SSRS Integration with SharePoint 2007

Configuration:

  • SQL Server 2005 SP2 is installed on report server in Native mode along with WSS Object Model (farm install)
  • SSRS Configuration Tool creates a new Report Server database in “SharePoint Integration mode”
  • SSRS Add-In is installed on WSS 2007
  • WSS Central Admin web pages register SSRS web service and windows service with WSS farm

Database Integration Points

  • WSS Content Database stores the master copy of SSRS items
  • Schedules, caching, and subscriptions are stored in SSRS database only
Labels:

Comments

Post a Comment

Popular posts from this blog

Comma separated list of values of single Database table field

Many times you need to create a comma seperated list of values in a table. Here is a line of T-SQL solution to get comma separated list of values of single field of a database table. DECLARE @commaSeparatedVal AS VARCHAR(MAX); SELECT @commaSeparatedVal = ISNULL(@commaSeparatedVal +',','') + CONVERT(VARCHAR,[SKU]) FROM PRODUCT PRINT @commaSeparatedVal
1 comment
Read more

Why SharePoint 2007?

It is rare for a technology product to attract as much attention as SharePoint has in recent years. The industry has historically paid little attention to new product suites, particularly those related to web design. SharePoint products and technologies, however, have managed to excite and rejuvenate industry followers, causing them to take notice of the ease of use, scalability, flexibility, and powerful document management capabilities within the product. A number of organizational needs have spurred the adoption of SharePoint technologies. Some of the most commonly mentioned requirements include the following: A need for better document management than the file system can offer —This includes document versioning, check-out and check-in features, adding metadata to documents, and better control of document access (by using groups and granular security). The high-level need is simply to make it easier for users to find the latest version of the document or documents they need to do th
1 comment
Read more

Creating Custom SharePoint Timer Jobs

In previous versions of SharePoint (or other platforms), if you had some task you wanted to perform on a scheduled basis, you'd have to either create a console EXE and schedule it to run via Windows Task Scheduler (ala AT.EXE) or create a Windows Service that went to sleep for a period of time. In order to install (and maintain) these tasks, you had to have console access to your production SharePoint (or other app) servers... something IT or admins wouldn't easily hand out. Addressing this issue, Microsoft has added something called timer jobs to Microsoft Office SharePoint Server (MOSS) 2007. Microsoft uses timer jobs to do things like dead web cleanup (purging unused sites from site collections) among others. To see what other timer jobs are out there, from Central Administration , click Operations and then Timer Job Definitions . Not only does Microsoft use timer jobs in MOSS, but you can create your own custom timer jobs to do your own scheduled tasks. What's nice a
Post a Comment
Read more