What is SSRS SQL Server 2005 Reporting Services is a server-based reporting platform that you can use to create and manage tabular, matrix, graphical, and free-form reports that contain data from relational and multidimensional data sources.

Advantages of SSRS The beauty is that the entire report and data source definition is stored as a simple XML file. This is the file the reporting engine uses to render reports. The elements and attributes required for defining a report format are fully documented.

Software Requirements Microsoft Visual Studio .NET 2003 (any edition) or the standard edition of Visual Basic .NET, Visual C# .NET, Visual C++ .NET Standard, or Visual J# .NET to use Report Designer. Report Designer can be installed on the same computer as Report Server components or on a separate computer.

Architecture SSRS is a comprehensive and extensible reporting platform, which includes an integrated set of processing components, programmatic interfaces, and tools. Processing components are the basis for the multilayered architecture of SSRS and interact with each other to retrieve data, process layout, render, and deliver a report to a target destination.

Creating a SSRS Project To create a report in Reporting Services, you must first create a report server project in SQL Server Business Intelligence Development Studio where you will save your report definition (.rdl) file and any other resource files that you need for your report.

Adding Data source Before we start the report, we are going to build a shared data source i.e. a data source that is common to, and can be used by, all of the reports on a reporting server. While each report can contain its own connection information, it is good practice to use shared data sources, as it will save you a lot time and headaches.

Creating Report A new report will be added to the project, and the Report Designer will open at the Data tab. At this point, let's take a closer look at the Report Designer tool. At the top of the Report Designer window are three tabs: Data, Layout and Preview.

Developing Report Next, switch to the Layout tab. This is where you actually build the report display. Start by dragging a table from the Toolbox, onto the Body section of the report. By default, the table shows a header row at the top, a detail row in the middle and a footer row at the bottom.

Report Items (Toolbox) The Textbox item can be used to display data from a data source, calculations or expressions, or static data, much like a label control in a Windows forms project. When you drag fields from the Fields list onto the Report Designer, bound Textbox items are created.

Grouping In a data region, you can group data by a single field, or create more complex expressions that identify the data on which to group. Complex expressions include references to multiple fields or parameters, conditional statements, or custom code.

Interactive Sorting It is incredibly easy to add sorting to the table. Open any report which contains the table. Navigate to the Layout tab of the report designer. Click in the cell labeled First Name in the header of the table. Right-click and select Properties. Switch to the Interactive Sort tab.

Formatting Text

IIF Function The IIF() statement has the following format: =IIF( Expression to evaluate, what-to-do when the expression is true, what-to-do when the expression is false )

Built-in Functions In Reporting Services, expressions are used to provide dynamic flexibility for controlling the content and appearance of a report. Some expressions are created for you automatically.

Custom Functions There are about 100 common functions in SSRS 2005, and they can handle most of what you will need to do. Occasionally, however, you will still need to set an expression with more control than the common functions can manage. So, to make SSRS even more flexible, you can write custom VB.NET or C# functions and use them in expressions.

Sub Reports A subreport is a report that is embedded into another report. Subreports can take parameters and execute their own datasets. A subreport in SSRS is actually just another. In fact, in SSRS you can execute a subreport on its own.

Navigating in same report(Book Marking) Abookmark is a text box or image in a report that can be used as a navigational link. If you want to allow the user to click an item and navigate to another item, assign a bookmark value to each of the target items.

Jump to Report This powerful feature enables a text box or image to be used as a link to another report by passing parameter values to the target report.

Jump to URL The Jump to URL option can be used to navigate to practically any report or document content on your report server, and files, folders, and applications in your intranet environment or on the World Wide Web.

Totals & Sub-totals

Report Header & Footer How to add a page footer that displays the user name for the report and page numbers.

Global Variables Let's go to the expression editor. Click on Globals in the first column of the expression editor. In the second column, you will see the list of the global variables that are provided to the report.

Bookmarking in reports Add bookmarks and bookmark links to a report when you want to provide a customized table of contents or to provide customized internal navigation links in the report.

Parameterized Reports A parameterized report uses input values to complete report or data processing. With a parameterized report, you can vary the output of a report based on values that are set when the report runs.

How to set Default Values and Available Values for Report Parameters in SSRS In SSRS, parameters are used to specify the data to use in a report, narrow down your report data for better analysis, connect related reports together, and vary report presentation.

Adding Images to a SSRS Report (Embedded & Database) An image is a report item that contains a reference to an image that is stored on the report server, embedded within the report, or stored in a database.

Drill Down By setting the Toggle Item property to State, when the report is run a little + sign appears next to it when the report is run and it works like a tree view.

Matrix report in SSRS Matrix Reports are a special report similar to a pivot table. Matrix reports are not uncommon, and are useful for measuring trends.

Listbox & Rectangle Arectangle is simply used to visually separate a region of the report. A list data region is a data-bound report item that contains a single column of multi-instance data on a report.

Cascading Parameters

Deploying Reports You can run your reports in the Visual Studio 2005 environment but, to make them useful, you must publish them to a report server. The easiest way to do this is to have Visual Studio publish your reports.

ReportViewer Control in Visual Studio ReportViewer is a freely redistributable control that enables embedding reports in applications developed using the .NET Framework. Reports are designed with drag-and-drop simplicity using Report Designer included in Visual Studio

Local & Server Reports To Load a server Report in web page directly is not possible. By taking a ReportViewer control we can show the Report in web page. The ReportViewer control is available in ToolBox under Data Controls Tab.

Integrating reports with Web

How to Configure a Report Server for Local Administration on windows7, Windows Vista and Windows Server 2008 This article explains how to configure the report server for local administration after setup is finished and the report server components are configured.

SSRS Common Functions Text List of Text Functions available in Common Functions to write expressions in Reporting Services.

SSRS Common Functions Date Time List of Date Time Functions available in Common Functions to write expressions in Reporting Services.

Types of Reports in SSRS With Reporting Services, you can create the following types of reports: Parameterized reports, Linked reports, Snapshot reports, Cached reports, Ad hoc reports, Clickthrough reports, Drilldown reports, Drillthrough reports, Subreports.

Generating PDF using SSRS Reports programmatically ReportViewer is a good tool to generate report file on fly in pdf / excel format in asp.net web application.