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

A problem I faced with WSS 3.0 configuration on a Windows 2003 Server

I’d like to share a typical problem recently I faced with WSS 3.0 configuration on a Windows 2003 Server.

I created a virtual machine with Windows 2003 OS and named it “myvpc” in a hypothetical workgroup viz. “WKGP” (I initially planned to use it in stand-alone mode). Then I installed all available updates to the Windows Server and .Net framework 3.5 with IIS followed by Stand-alone installation of Latest WSS 3.0. The default team site and central administration site were working smooth. I even created another application using central administration.

Later I changed my mind and thought that it would be better if I include this virtual machine into our company’s local intranet Domain. At this point of time I had to change the PC name from “myvpc” to “SUDEV-VPC” due to some or other reasons.

Now, suddenly none of the SharePoint sites were accessible even from the “localhost“! I opened IIS console and noticed that “SharePoint Admin” site was stopped!!! I checked the systems log and found many errors stating “Cannot connect to SQL Server. MYVPC\Microsoft##SSEE not found“. I was surprised to see this, why SharePoint did not understand the change in name of computer.

I just thought lets run the “Configuration Wizard” again for the WSS. I did it but it failed too at the step-5 saying “Could not access search service configuration database“.

I uninstalled the WSS 3.0 completely and restarted the OS. I noticed that IIS don’t have the sites I created using WSS anymore.

I installed the WSS again, ran the configuration wizard but just to get a disappointment. The error continues to occur. This time I checked IIS and found that the sites I created with previous installation have been created again on the same port (sounds strange again, how new installation of SP came to know about it?)

The WSS 3.0 was using same database (Windows Internal Database aka SQL Server Embedded Edition). But at some location it was using previous computer name and at other it was using new name. (Surprised?)

So I thought lets re-install the Windows Internal Database itself. There is no direct way to uninstall it. So I uninstall the WSS 3.0 first. And then with help of MSDN link (http://technet.microsoft.com/en-us/library/cc708610.aspx) I uninstalled the Windows Internal Database as well. I removed the data files from “%windir%\SYSMSI\SSEE” as per their suggestion followed by a system restart.

Now I installed the WSS 3.0 in stand-alone mode, ran the configuration wizard and guess what? The configuration wizard ran successfully and I was able to access the default team site and admin site again.

It took quite some time to figure out this solution but it worked for me. In your case you may need to retain the backup of the content database and configuration database before un-install and install of WSS.

Microsoft Announces Visual Studio 2010

Microsoft is offering a first look at the next version of its Visual Studio integrated development environment (IDE) and platform, which will be named Visual Studio 2010 and the .Net Framework 4.0.

http://msdn.microsoft.com/en-us/vstudio/products/cc948977.aspx

There’s a lot promised in the new release (expected to ship, duh, in 2010), from improved software testing tools to software engineering modeling capabilities to integrated development and database functions for the application lifecycle management (ALM).

Microsoft is putting its attention on improving Visual Studio for the benefit of every one of its users—from the CIO to the software architect to the enterprise developer to the software testing team.

A key goal in VSTS 2010, says Microsoft, is to help democratize ALM by bringing all members of a development organization into the application development lifecycle, and remove many of the existing barriers to integration.

One way that Visual Studio 2010 will do this is to break down the ALM roles, from the business decision maker (who needs a project overview but doesn’t want to be bogged down in details) to the lead developer or system architect (who enables the software infrastructure and draws the blueprint), to the developer who writes the code and the database administrator (DBA) who integrates it with the company database to the testers (who make sure the software is of high quality).

For the IT manager or CIO, says Mendlen, VSTS will give clarity and visibility into the state of the project throughout the lifecycle, using Team Foundation Server-enabled dashboards customized for her role. The dashboard can answer high level questions such as ongoing project cost or project status.

Agile Tools, Built-In

Visual Studio 2010 also will sport features to integrate Agile methodologies into the tech stack using Team Foundation Server. Skinner explains, “We’ll include in the [VSTS] box an Excel workbook for teams that are leveraging, say, the Scrum process so they can get burndown from their project.” These features, he says, will let Agile teams track daily progress, see projects broken down into iterations and use sprints.

Putting Quality Earlier in the Development Lifecycle

One sometimes-stressful interaction in the application development lifecycle is the tension between developers and testers. Developers have to do a better job of testing their code before they send it off to the software testers. Developers don’t always know which unit tests they have to run, and often they don’t have the time or inclination (your own cynicism-meter can determine which) to run the tests anyway.

Merging of Developer, DBA Roles

Most of these changes are a ways off, though you can expect to see some of this functionality demonstrated at the upcoming Microsoft Professional Developers’ Conference. One item, however, takes effect immediately.

As Microsoft sees it, the roles of the database-centric developer and “regular” developer are less distinct than they once were, so the company is merging its VSTS database and development products. As of October 1, those who belong to the Microsoft Developer Network (MSDN) and currently own Visual Studio Team System 2008 Development Edition or Visual Studio Team System 2008 Database Edition will receive for free the Visual Studio Team System 2008 Development Edition, Visual Studio Team System 2008 Database Edition, Visual Studio 2005 Team System for Software Developers and Visual Studio 2005 Team System for Database Professionals.

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 their jobs, and ultimately to make them more efficient in those jobs.

Improved collaboration between users with a minimal learning curve— Although virtually everyone has a different definition of what comprises collaboration, a functional definition is a technology solution that allows users to interact efficiently with each other using software products to share documents and information in a user friendly environment. In regard to SharePoint, this typically refers to document and meeting workspaces, site collections, discussion lists, integration of instant messaging and presence information, and integration with the Office suite of applications. Integration with Office applications is a key component: Most organizations do not want to force users to learn a new set of tools to collaborate more effectively because users generally resist such requirements.

A better intranet—Although most companies have an intranet in place, the consensus is that it is too static, that it is not user friendly, and that every change has to go through IT or the “web guy.” This level of request generally comes from a departmental manager, team lead, or project manager frustrated with their inability to publish information to a select group of users and regularly update resources their team needs to do their jobs.

A centralized way to search for information—Rather than using the “word of mouth” search engine (that is, asking coworkers via email for a specific document), there should be an engine in place that allows the user to quickly and efficiently find particular documents. The user can search for documents that contain certain words; documents created or modified during a certain timeframe; documents authored by a specific person; or documents that meet other criteria, such as file type.

Creation of a portal—Many definitions exist for the term portal, but a general definition that a portal is a web-enabled environment that allows Internet and, potentially, external users to access company intellectual resources and software applications. A portal typically extends standard intranet functionality by providing features such as single sign-on, powerful search tools, and access to other core company applications such as help desk, human resources software, educational resources, and other corporate information and applications.

SharePoint is a set of products and technologies with informative and collaborative web-based capabilities that help people create, organize, distribute, and maintain stored knowledge. SharePoint web sites and pages are commonly used to build intranet and extranet portals and team sites, as well as public-facing Internet sites.

SharePoint is a great platform upon which to build applications and provides many key services in the greater story of the Microsoft Application Platform. SharePoint shows great maturity in terms of its user interface, database design, and workflow and communication features. It provides a standard interface with standard navigation, enabling users to focus on tools and information, not on learning how to navigate new menu controls.

SharePoint helps resolve database and business logic issues by providing a powerful complement to structured data – that is, a managed environment to store unstructured data that gets the information and business rules onto the network and out of local Excel files. SharePoint takes advantage of the first workflow platform built into an operating system and makes it easy for developers and power users to use Windows Workflow Foundation (WF) to automate business processes.

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 about this is once your timer job has been installed (something you can easily do with a solution & a feature), you can view it’s status through Central Administration and even disable/enable it… all without console access to your production servers! Another cool thing is that when your job runs, MOSS passes it the GUID of the content database for the site the job is registered with. You can use this GUID to obtain a reference to a content database, then a site collection, and finally a site within the collection (SPWeb).

How do you build one? Well, unfortunately the documentation is lacking here… there isn’t a single article in the SDK talking about creating custom timer jobs and the necessary objects aren’t well documented either.

MVP Andrew Connel has described the Custom Timer Jobs on SharePoint well in depth on his blog http://www.andrewconnell.com/blog/articles/CreatingCustomSharePointTimerJobs.aspx. Awsome functionality. Thanks Andrew.