Sql Server Reporting Service -- Sql Server

Reporting service architecture

Reporting Services has a quite a few components that work together seamlessly to provide a complete reporting solution. The full Reporting Services architecture includes development tools, administration tools, and report viewers. There are a number of ways to get to Reporting Services programmatically, including URL, SOAP and WMI interfaces.

simplified diagram of the main Reporting Services components

 

Using Report Designer:

Reporting Services includes two tools for creating reports:

• Report Designer can create reports of any complexity that Reporting Services supports, but requires you to understand the structure of your data and to be able to navigate the Visual Studio user interface.
• Report Builder provides a simpler user interface for creating ad hoc reports, directed primarily at business users rather than developers. Report Builder requires a developer or administrator to set up a data model before end users can create reports.

We'll start our tour of Reporting Services with Report Designer. Report Designer runs inside the Business Intelligence Development Studio shell, and offers several ways to create reports. You can either use the Report Wizard to quickly create a report, or you can use a set of design tools to build a report from scratch. You can also use the design tools to modify a report created with the wizard.

 

Using the Report Wizard:

The easiest way to create a report in Report Designer is to use the Report Wizard. Like all wizards, the Report Wizard walks you through the process in step-by-step fashion. You can make the following choices in the wizard:

• Select data source to be used
• The query to use to retrieve data
• Whether to use a tabular or matrix layout for the report
• How to group the retrieved data
• Select Visual Style to to display the data
• Deploy the created report

Steps to Create the sample report using report wizard

Modifying a Report:

Now that you've created a report with the Report Wizard, you can modify it with the Report Designer. If you've used any sort of visual report design tool in the past, you should have no problem making changes here. Among the possibilities here:

• You can change  data source or the sort order for the report by modifying the query on the Data tab.
• You can resize or rearrange controls on the Layout tab.
• You can use the Properties window to change properties of individual controls including their font, alignment, colors, and so on. 

Steps to Modify Report 

Designing a Report From Scratch

You can also use Report Designer to build your own reports starting from scratch. In general, you'll follow these steps to create a report:

• Create a Report project in Business Intelligence Design Studio or open an existing Report project.
• Add a report to the project.
• Create one or more data sets for the report.
• Build the report layout.

Steps to design report from scratch 



Publishing a Report

Creating reports in Business Intelligence Development Studio is good for developers, but it doesn't help users at all. In order for the reports you build to be available to others, you must publish them to your Reporting Services server. To publish a report, you can use the Build and Deploy menu items in Business Intelligence Development Studio. Before you do this, you need to check the project's configuration to make sure that you've selected an appropriate server for the deployment. 

Steps to Publishing Report  

Using Report Builder

Report Designer gives you one way to create reports for Reporting Services, but it's not the only way. SQL Server 2008 also includes a tool directed at end users named Report Builder. Unlike Report Designer, which is aimed at Developers, Report Builder presents a simplified view of the report-building process and is intended for business analysts and other end users.

Building a Data Model:

Report Builder doesn't let end users explore all of a SQL Server database. Instead, it depends on a data model: a preselected group of tables and relationships that a developer has identified as suitable for end-user reporting.

To build a data model, you use Business Intelligence Development Studio. Data models contain three things:

• Data Sources connect the data model to actual data.
• Data Source Views draw data from data sources.
• Report Models contain entities that end users can use on reports.

Steps to create report using Data Model

 Building a Report:

 

Report Builder itself is a ClickOnce Windows Forms application. That means that it's a Windows application that end users launch from their web browser, but it never gets installed on their computer, so they don't need any local administrator rights on their computer to run it. 

To get started with Report Builder, browse to your Reporting Services home page

Typically, this will have a URL such as http://ServerName/Reports (or http://localhost/Reports if you're running the browser on the same box with SQL Server 2008 itself).
Below Figure  shows the Reporting Services home page.. 

To run Report Builder, click the Report Builder link in the home page menu bar. Report Builder will automatically load up all of the available report models and wait for you to choose one to build a report from.

Steps to Build a report 



Using Report Manager

The Web home page for Reporting Services provides a complete interface for managing reports (as well as other objects such as data sources and models) after they are created. This interface, known as Report Manager, is intended primarily for database administrators, but as a developer you should know about its capabilities for managing and modifying reports. 

When you click on a report in Report Manager it will display the report’s data 

  

Note that reports in Report Manager open in a tabbed interface. The four tabs allow you to perform various functions:

• View allows you to see the current data in the report. 
•  Properties lets you adjust such things as the report's name, data source, security credentials, caching, and end-user security. 
•  History shows you saved snapshots of the report.
•  Subscriptions lets you create subscriptions to the report. Subscriptions allow you to set up periodic delivery of reports to end users by e-mail or file share.