Report Server In SSRS

The report server is the central component of a Reporting Services installation. It consists of a pair of core processors plus a collection of special-purpose extensions that handle authentication, data processing, rendering, and delivery operations. Processors are the hub of the report server. The processors support the integrity of the reporting system and cannot be modified or extended. Extensions are also processors, but they perform very specific functions. Reporting Services includes one or more default extensions for every type of extension that is supported. You can add custom extensions to a report server. Doing so allows you to extend a report server to support features that are not supported out of the box; examples of custom functionality might include support for single sign-on technologies, report output in application formats that are not already handled by the default rendering extensions, and report delivery to a printer or application.

A single report server instance is defined by the complete collection of processors and extensions that provide end-to-end processing, from the handling of the initial request to the presentation of a finished report. Through its subcomponents, the report server processes report requests and makes reports available for on-demand access or scheduled distribution.

The following illustration shows a report server instance as it is installed by Setup. It includes the core processors and default extensions shown in the diagram:

The report server is implemented as a Microsoft Windows service, called the "Report Server service", that hosts a Web service, background processing, and other operations. In the Services console application, the service is listed as SQL Server Reporting Services (MSSQLSERVER). For more information about service implementation, see Service Architecture (Reporting Services).

Third-party developers can create additional extensions to replace or extend the processing capability of the report server. To learn more about the programmatic interfaces available to application developers, see the Developer's Guide and Technical Reference.

Report Processor and Scheduling and Delivery Processor
The report server includes two processors that perform preliminary and intermediate report processing, and scheduled and delivery operations. The Report Processor retrieves the report definition or model, combines layout information with data from the data processing extension, and renders it in the requested format. The Scheduling and Delivery Processor processes reports triggered from a schedule, and delivers reports to target destinations. For more information about each processor, see Report Processor and Scheduling and Delivery Processor.

Report Server Database The report server is a stateless server that stores all properties, objects, and metadata in a SQL Server database. Stored data includes published reports, report models, and the folder hierarchy that provides the addressing for all items managed by the report server. A report server database can provide internal storage for a single Reporting Services installation or for multiple report servers that are part of a scale-out deployment. If you configure a report server to run within a larger deployment of a SharePoint product or technology, the report server uses the SharePoint databases in addition to the report server database. For more information about data stores used in Reporting Services installation, see Report Server Database and Storing and Synchronizing Report Server Content With SharePoint Databases.

Authentication, Rendering, Data, and Delivery Extensions The report server supports the following types of extensions: authentication extensions, data processing extensions, report processing extensions, rendering extensions, and delivery extensions. A report server requires at least one authentication extension, data processing extension, and rendering extension. Delivery and custom report processing extensions are optional, but necessary if you want to support report distribution or custom controls.

Reporting Services provides default extensions so that you can use all of the server features without having to develop custom components. The following table describes the default extensions that contribute to a complete report server instance that provides ready-to-use functionality:

Type	Default
Authentication	A default report server instance supports Windows Authentication, including impersonation and delegation features if they are enabled in your domain.
Data processing	A default report server instance includes data processing extensions for SQL Server, Analysis Services, Oracle, Hyperion Essbase, SAPBW, OLE DB, and ODBC data sources.
Rendering	A default report server instance includes rendering extensions for HTML, Excel, CSV, XML, Image, Word, and PDF.
Delivery	A default report server instance includes an e-mail delivery extension and a file share delivery extension. If the report server is configured for SharePoint integration, you can use a delivery extension that place report deliveries in a SharePoint library.

Report Manager In SSRS

Report Manager is a Web-based report access and management tool that you use to administer a single report server instance from a remote location over an HTTP connection. You can also use Report Manager for its report viewer and navigation features. You can use Report Manager to perform the following tasks:

• View, search, print, and subscribe to reports.
  
• Create, secure, and maintain the folder hierarchy to organize items on the server.
  
• Configure role-based security that determines access to items and operations.
  
• Configure report execution properties, report history, and report parameters.
  
• Create report models that connect to and retrieve data from a Microsoft SQL Server Analysis Services data source or from a SQL Server relational data source.
  
• Set model item security to allow access to specific entities in the model, or map entities to predefined clickthrough reports that you create in advance.
  
• Create shared schedules and shared data sources to make schedules and data source connections more manageable.
  
• Create data-driven subscriptions that roll out reports to a large recipient list.
  
• Create linked reports to reuse and repurpose an existing report in different ways.
  
• Launch Report Builder 1.0 to create ad hoc reports that you can save and run on the report server. For more information, see How to: Start Report Builder.
  You can use Report Manager to browse the report server folders or search for specific reports। You can view a report, its general properties, and past copies of the report that are captured in report history। Depending on your permissions, you might also be able to subscribe to reports for delivery to an e-mail inbox or a shared folder on the file system.

To use Report Manager, you must have Microsoft Internet Explorer 6.0 or later.

Report Manager is used only for a report server that runs in native mode. It is not supported for a report server that you configure for SharePoint integrated mode.

Some Report Manager features are not available in SQL Server Express with Advanced Services. For more information, see Features Supported by Reporting Services in SQL Server Express.

Starting and Using Report Manager
Report Manager is a Web application that you open by typing the Report Manager URL in the address bar of a browser window. When you start Report Manager, the pages, links, and options that you see will vary based on the permissions you have on the report server. To perform a task, you must be assigned to a role that includes the task. A user who is assigned to a role that has full permissions has access to the complete set of application menus and pages available for managing a report server. A user assigned to a role that has permissions to view and run reports sees only the menus and pages that support those activities. Each user can have different role assignments for different report servers, or even for the various reports and folders that are stored on a single report server.

The following illustration shows Report Manager as it appears to a user who has Content Manager permissions on the report server. A user who has less permission will see fewer links and options on the Home page.

For more information about roles, see Granting Permissions on a Native Mode Report Server. For instructions on how to start Report Manager, see How to: Start Report Manager.

If you are using Windows Vista or Windows Server 2008, you must configure the report server for local administration before you can use Report Manager to manage a local report server instance. For instructions on how to configure the server, see How to: Configure a Report Server for Local Administration on Windows Vista and Windows Server 2008.

Configuring Report Manager
Report Manager configuration consists of defining a URL for the application. Additional configuration is required if your deployment includes running Report Manager on a separate computer.

You can customize Report Manager in very limited ways. For example, you can modify the application title on the Site Settings page. If you are a Web developer, you can modify the style sheets that contain the style information used by Report Manager. Because Report Manager is not specifically designed to support customization, you must thoroughly test any modification that you make. If you find that Report Manager does not meet your needs, you can develop a custom report viewer or configure SharePoint Web parts to find and view reports in a SharePoint site.

Reporting Services Components and Tools

SQL Server Reporting Services is a set of processing components, tools, and programmatic interfaces that support the development and use of rich reports in a managed environment. The tool set includes development tools, configuration and administration tools, and report viewing tools.

Processing is distributed across multiple components. Central and specialized processors are used to retrieve data, process report layout, render presentation formats, and deliver to target destinations. Presentation processing occurs after the data is retrieved and is decoupled from data processing, allowing multiple users to review the same report simultaneously in formats designed for different devices or quickly change the viewing format of the report, from HTML to PDF or Microsoft Excel or XML, with a single click. The modular architecture is designed for extensibility. Developers can include reporting functionality in custom applications or extend reporting functionality to support custom features.

Programmatic interfaces for easy integration with new or existing applications and portals include:

• Simple Object Access Protocol (SOAP)
• URL endpoints
• Windows Management Instrumentation (WMI)
  

In This Section

Report Server Describes the Report Server component, which provides data and report processing, and report delivery. The Report Server component includes several subcomponents that perform specific functions. This topic introduces each component of Report Server.

Report Designer Report Designer is a comprehensive report design tool that is appropriate for both administrators and developers. Report Designer runs in the Business Intelligence Development Studio, which is fully integrated with Microsoft Visual Studio 2005 or later, or can be run as a separate application. Report Designer provides several query designers specific to various supported data source types. Report Designer supports advanced reporting functionality such as free-form reports and custom report items.There is also a Report Wizard associated with Report Designer. The Report Wizard steps you through the report design process to produce a simple tabular or chart report, which can be opened and enhanced in Report Designer or used as is.

Report Builder 2.0 Report Builder 2.0, the new version of Report Builder, features a Microsoft Office-like authoring environment. It offers many new features such as wizards for creating new tables, matrices, and charts, enhanced data layout, data visualization, richly formatted text, and on-demand rendering. You can customize and update all existing reports, regardless of where they were initially designed.

Report Builder 1.0 Report Builder 1.0 allows information workers to explore business information in a report model without having to understand the underlying data source structures and create ad hoc reports for personal or organizational use. Report Builder 1.0 is fully integrated with Reporting Services. Ad hoc reports can be revised, managed, and secured by using the other Reporting Services tools. An unique feature of Report Builder is clickthrough functionality that enables people to explore the business data that is available to them via ad hoc reports.

Model Designer Model Designer is used to generate and customize report models of business data for use with Report Builder or Report Designer.

Report Manager Report Manager is a Web-based report access and management tool that you access through Microsoft Internet Explorer 6.0 or later. Information workers as well as content managers use Report Manager to find, run, and subscribe to published reports. Content managers use Report Manager to manage reports, data sources, and resources; configure report processing options and security

Reporting Services Configuration Tool The Reporting Services Configuration tool is used to configure a Reporting Services installation. Reporting Services can be reconfigured after installation to support changing business requirements and scenarios.

Report Server Command Prompt Utilities

SQL Server Reporting Services includes three command line utilities that you can use to administer a report server. These utilities are installed automatically when you install a report server.

Reporting Services in Business Intelligence Development Studio

SQL Server Business Intelligence Development Studio is a Visual Studio shell that hosts Report Designer and Model Designer.

SQL Server Tools for Report Server Administration

SQL Server also provides tools that administrators can use to manage a Reporting Services installation.

Reporting Services Scenarios

Reporting Services is an enterprise reporting solution that meets a wide range of implementation scenarios. You can use the tools and applications included in Reporting Services to create, manage, and distribute reports. Out-of-the-box tools and applications are available for expert report designers as well as information workers who need an easy way to create their own reports and explore business data. If you are developer, you can integrate Reporting Services functionality within a custom application, or extend it to support the type of functionality you require.

End-to-End Report Processing and Management with Reporting Services Tools and Applications
The tools and applications in Reporting Services provide a full spectrum of features to support enterprise reporting requirements. Reporting Services provides out-of-the-box support for reporting against a number of commonly used data source types, and reports can be rendered and distributed in a variety of popular formats, such as Microsoft Office Word and Excel or Adobe PDF. Although report definitions can be revised programmatically, no programming experience is necessary to use Reporting Services tools and applications.

• Report server administrators and content managers can use SQL Server Management Studio, Report Manager, and the Reporting Services Configuration tool to deploy and manage report server content, and they can use Model Designer to create report models of business data for use as report data sources.
  
• Report authors can use Report Designer or Report Builder to create reports that visualize business data in traditional and interactive formats.
  
• Business users can use Report Manager, SharePoint Web Parts, or a Web browser to view reports on demand, or subscribe to reports that are delivered through e-mail.
  For more information about the tools available, see Reporting Services Features, Reporting Services Components and Tools, and Reporting Services in Business Intelligence Development Studio. For more information about developing and managing enterprise reporting solutions, see Designing and Implementing Reports and Administration.

SharePoint Integration

You can configure a report server to run within a deployment of a SharePoint product or technology and use the collaboration and centralized document management features of Windows SharePoint Services 3.0 or Office SharePoint Server 2007 with Reporting Services. For more information, see Reporting Services and SharePoint Technology Integration.

Ad hoc Reporting with Report Builder and Model-driven Data Exploration
Information workers need an easy-to-use tool to access and report on business data on an ad hoc basis. Reporting Services includes Report Builder and Model Designer, which simplify the process of creating ad hoc reports by providing report templates and pre-generated models of business data. Report models are stored and secured on a report server as data sources that can be used by all the Reporting Services authoring tools. Reports created in Report Builder can also be saved to a report server, where they can be managed and distributed like any other Reporting Services report.

Report Builder reports also provide clickthrough functionality, which enables users to click data in the report and see related data in the report model on-the-fly. You can attach customized reports to the report model that pops up when the data field is clicked. For more information about ad hoc reporting, see Designing and Implementing Reports Using Report Builder 1.0. For more information about the Report Builder or Model Designer used in this scenario, see Reporting Services Components and Tools.

Development Scenarios for Corporate and Third-Party Developers
Reporting Services supports a variety of scenarios for developers who want to add reporting features to corporate applications or third-party commercial software.

Application Integration

Developers can use Reporting Services to include reporting features in their applications. For some applications, the addition of reports completes a feature set by providing a way to present data that the application tracks, creates, or monitors. Use Report Designer to create reports based on either a data source provided in your application or one that is publicly available. Use the APIs to define access and add support for any built-in report server features. For more information, see the Reporting Services Developer's Guide.

Alternatively, if your application does not require all of the features provided in a report server, the ReportViewer controls can be included in Microsoft Visual Studio 2005. In contrast with Reporting Services, the ReportViewer controls are distributable with an application.

Visual Studio ReportViewer Web Server and Windows Forms Controls for Embedding Reports in Custom Applications

Visual Studio provides Reporting Services functionality through a ReportViewer control that can be embedded in other applications and through a version of the Report Designer and Report Wizard that is included with Visual Studio. The control embeds report processing and viewing capabilities directly into your application. For more information about the Visual Studio Report Designer and ReportViewer controls, see Reporting Services and ReportViewer Controls in Visual Studio.

Redistribution Through SQL Server Express with Advanced Services

You can include SQL Server Express in custom applications to provide an internal database engine to store and manage application data. If you want to redistribute reporting functionality in a custom application, SQL Server Express with Advanced Services includes a subset of Reporting Services features that you can use or distribute with your application. SQL Server Express is a free Web download that can be used with a separate Web download called SQL Server Express Toolkit. The SQL Server Express Toolkit provides a report authoring tool. You can use SQL Server Express with the ReportViewer controls. There are some limitations with regard to the report server SOAP endpoints. For more information, see Reporting Services in SQL Server Express with Advanced Services.

Hosting Reports in Custom Dashboards and SharePoint Sites

Because reports can accommodate and present data from a variety of sources, many organizations use the interactive reporting features of Reporting Services to distribute data in tabular, chart, and gauge formats via portal applications. Reports can be hosted in a portal page or created so that they mimic a dashboard-style Web application by embedding multiple reports, charts, gauges, and data-driven images into a single free-form report layout. Whenever tabular or graphic data needs to be incorporated into a line-of-business application, consider adding a Reporting Services report instead. For more information, see Nesting Data Regions.

Building Custom Report Design and Report Management Tools

The tools and applications included with Reporting Services are based on programmatic interfaces that are available to all users. This means that you can extend or replace the applications and tools included in Reporting Services with a custom tool set. For example, if you want an alternative to the Business Intelligence Development Studio authoring environment used for Report Designer, you can develop a custom report authoring tool to replace it. To build a custom Web portal or report management tool, review the API to learn about the report server management functions that you must support. Reporting Services includes a Windows Management Instrumentation (WMI) provider that you can use to develop Windows-based tools used for server administration. For more information, see the Reporting Services Developer's Guide.

Extending Reporting Services Functionality

Reporting Services is designed for extensibility. You can create custom extensions to support additional kinds of data sources, delivery approaches, security models, and report items. When creating custom extensions, the degree of difficulty can vary considerably depending on the kind of extension you are creating and the functionality you need it to support. Data processing extensions are typically the most straightforward to create, while rendering extensions can be very difficult if you are supporting the entire report schema.

Reporting Services Features

• Relational, multidimensional, XML, and custom data sources Build reports using data from any data source type that has a Microsoft .NET Framework-managed data provider, OLE DB provider, or ODBC data source. You can create reports that use relational and multidimensional data from SQL Server and Analysis Services, Oracle, Hyperion, and other databases. You can use an XML data processing extension to retrieve data from any XML data source. You can use table-valued functions to design custom data sources.
  
  
• Tabular, matrix, chart, and free-form report layouts Create tabular reports for column-based data, matrix reports for summarized data, chart reports for graphical data, and free-form reports for everything else. A new data region introduced in SQL Server 2008 enables you to create reports that combine table and matrix layouts. Reports can embed other reports and charts, along with lists, graphics, and controls for dynamic Web-based applications.
  
  
• Ad hoc reports Create and save reports on the fly and save them locally or publish them directly to a report server or SharePoint site, where they can be managed like other reports. Ad hoc reporting is supported through report models and report templates using a ClickOnce application called Report Builder.
  
  
• Drillthrough reports and interactivity Add interactive features by providing links to subreports and drillthrough reports. Use parameters to filter data for customized views. Model-based reports provide infinite clickthrough functionality so users can explore the data that is available to them.
  
  
• Presentation formats Choose a presentation format when you open the report, or after you open the report. You can choose Web-oriented, page-oriented, and desktop application formats. Formats include HTML, MHTML, PDF, XML, CSV, TIFF, Word, and Excel.
  
  
• Custom controls and report items You can embed custom controls or report items that you create or purchase from a third-party vendor. A custom control requires a custom report processing extension. You can add scripted expressions in Microsoft Visual Basic.
  
  
• Navigation You can add bookmarks and document maps to provide navigation options within a large report.
  
  
• Aggregations You can aggregate and summarize data using controls and expressions.
  
  
• Graphical elements You can embed or reference images and other resources, including external content.
  

Report and Model Design Tools

• Report Designer Create reports in a full-featured report authoring application। Report Designer is hosted within Business Intelligence Development Studio. With Report Designer, you can create complex reports based on a number of different data source types, define simple to complex layouts, add chart and gauge data presentations, include interactive features like drillthrough reports and document maps, preview reports, and save reports to your local computer or publish them to a test or production report server. Report Designer includes query builders and an expression editor.
• Model Designer Create models that support ad hoc reporting in Reporting Services. You can generate models automatically based on an existing schema or view, refine the model, and then publish it to a report server.

• Report Builder 2.0 Create reports in a Microsoft Office-like authoring environment in the new version of Report Builder. Take advantage of new features such as enhanced data layout, data visualization, richly formatted text, and on-demand rendering. Create reports with multiple data regions, pull data from multiple data sources into a single report, and directly query relational and multidimensional data sources. Customize and update all existing reports, regardless of whether they were designed in Report Designer or in Report Builder 1.0.
  
• Report Builder 1.0 Create ad hoc reports using templates and pre-organized data and save them directly to a report server. Reports that you create in Report Builder 1.0 can be accessed, distributed, and managed just as you would any other report.
  

Deployment and Administration

• Server-based platform Centralize report storage and management, provide secure access to reports and folders, control how reports are processed and distributed, and standardize how reports are used in your business.
  
• Integration with a deployment of a SharePoint product or technology You can configure a report server to run in SharePoint integrated mode, providing access to report server content and operations from within a SharePoint site or farm. For more information, see Reporting Services and SharePoint Technology Integration.
  
• Reporting Services Configuration Deploy and maintain an existing report server using a graphical user interface to configure service accounts, virtual directories, report server databases, encryption keys, and scale-out deployment.
  
• Report Manager Configure role-based security and manage report server content by setting properties on data sources, reports, folders, resources, and report models. You can configure report execution, report history, and set limits on processing time, monitor and cancel pending or in-process reports, and create and manage data source connections and schedules independently of the reports that they are associated with.
  
• Integration with SQL Server Management Studio and SQL Server Configuration Manager Report server administrators can use the tools provided in SQL Server to manage a Reporting Services installation.
  
• Command-line utilities Command-line utilities support report server configuration, key management, and scripted operations.
  
• Role-based security Use role-based security to control access to folders, reports, and resources. Security settings follow an inheritance pattern through the folder structure. You can vary security at any branch to redefine user access down to the item level.
  

Report Access and Delivery Features

• On-demand access over Web connections Use a browser to navigate a folder hierarchy to find and work with reports and other items. You can reference reports from a Favorites list in a Web browser, or link from a Web portal.
  
• SharePoint Web parts Reporting Services supports different levels of integration. If you deploy a report server in SharePoint integrated mode, you can access reports from libraries on a SharePoint site. If you are using a native mode report server, you can embed Web parts to view reports and navigate the report server folder hierarchy.
  
• My Reports and My Subscriptions In Report Manager, you can store and manage reports and subscriptions in a personal workspace.
  
• Subscriptions for E-mail or File share Delivery Automate report delivery through a standard subscription and set report presentation preferences. Users who prefer to view a report in Microsoft Excel, for example, can specify that format in a subscription. Deliver a rendered report to an e-mail inbox. Set delivery options that control whether the report is delivered as a link or attachment. Deliver a rendered report to a shared folder. Set delivery options that control whether the report is overwritten or added to an existing folder.
  
• Data-driven subscriptions Automate report distribution through data-driven subscriptions, which generate a recipient list and delivery instructions at run time from an external data source. Use a query and column-mapping information to customize report output for a large number of users.
  

Programmability and Extensibility

• Report Definition Language (RDL) RDL describes all possible elements of a report using an XML grammar that is validated by an XML schema. The report definition of an individual report is based on RDL and contains instructions for rendering the design at run time. RDL is extensible. You can add support for elements or features that are not present in the existing RDL schema, and then build custom tools and report rendering extensions to handle the features you create.
  
• SOAP API Use Report Server Web service methods to access a report server and Report Server Web service programmatically.
  
• URL access You can access report server items through parameterized URL strings. All reports and items stored in a report server are addressable through the report server namespace.
  
• WMI provider Reporting Services includes a Windows Management Instrumentation (WMI) provider that you can use to manage the Report Server Windows service.
  
• Extensible delivery, data processing, rendering, security, and report processing You can create custom delivery extensions to route reports to file shares, internal archive stores, or internal applications. You can extend data processing to query, convert, or transform data from new data source types. You can create custom rendering extensions to support report presentation in application formats or Web-oriented formats that are not provided with the product. You can build or integrate a security extension that provides an alternative to the Windows Authentication model.
  
  

Understanding Groups (Reporting Services)

In Report Designer, a group is a named set of data from the report dataset that is bound to a data region. Basically, a group organizes a view of a report dataset. All groups in a data region specify different views of the same report dataset.

To help visualize what a group is, refer to the following figure that shows the Tablix data region in Preview. In this figure, the row groups categorize the dataset by product type and the column groups categorize the dataset by geographic region and year.

The following sections help describe the various aspects of groups.

What Makes a Group?
A group has a name and a set of group expressions that you specify. The set of group expressions can be a single dataset field reference or a combination of multiple expressions. At runtime, Report Designer combines and applies group expressions to data in a group. For example, you have a group that uses a data field to organize the data in the data region. At runtime, Report Designer first organizes the data by date, and then displays and totals other dataset values for each date.

When Do I Create Groups?
In most cases, Report Designer automatically creates a group for you when you design a data region. For a table, matrix, or list, groups are created when you drop fields on the Grouping pane. For a chart, groups are created when you drop fields on the chart drop-zones. For a gauge, you must use the gauge properties dialog box. For a table, matrix, or list, you can also create a group manually. For more information, see see How to: Add or Delete a Group in a Data Region (Reporting Services). For an example of how to add groups when you create a report, see Tutorial: Creating a Basic Table Report.

How Can I Modify a Group? After you create a group, you can set data region-specific properties, such as filter and sort expressions, page breaks, and group variables to hold scope-specific data. For more information, see Working with Group Features (Reporting Services).

To modify an existing group, open the appropriate group properties dialog box. You can change the name of the group. Also, you can specify group expressions based on a single field or multiple fields, or on a report parameter that specifies a value at run time. You can also base a group on a set of expressions, such as the set of expressions that specify age ranges for demographic data. For more information, see Examples of Group Expressions (Reporting Services).

How are Groups Organized?
Understanding group organization can help you design data regions that display different views of the same data by specifying identical group expressions.

Groups are internally organized as members of one or more hierarchies for each data region. A group hierarchy has parent/child groups that are nested and can have adjacent groups.

If you think of the parent/child groups as a tree structure, each group hierarchy is forest of tree structures. A Tablix data region includes a row group hierarchy and a column group hierarchy. Data associated with row group members expands horizontally across the page and data associated with column group members expands vertically down the page. The Grouping pane displays row group and column group members for the currently selected Tablix data region on the design surface. For more information, see Grouping Pane.

A chart data region includes a category group hierarchy and a series group hierarchy. Category group members are displayed on the category axis and series group members are displayed on the series axis.

Although typically not needed for gauge data regions, groups do let you specify how to group data to aggregate on the gauge.

What Types of Groups are Available per Data Region?
Data regions that expand as a grid support different groups than data regions that display summary data visually. Thus, a Tablix data region, and the tables, lists, and matrices that are based on the Tablix data region, support different groups than a chart or gauge. The following sections discuss the type of and purpose for grouping in each type of data region.

Although groups have different names in different data regions, the principles behind how you create and use groups are the same. When you create a group for a data region, you specify a way to organize the detail data from the dataset that is linked to the data region. Each data region supports a group structure on which to display grouped data.

Groups in a Tablix Data Region: Details, Row, and Column Groups

As shown earlier in this topic, a Tablix data region enables you to organize data into groups by rows or columns. However, row and column groups are not the only groups available in a Tablix data region. This data region can have the following types of groups:

• Details Group The Details group consists of all data from a report dataset after Report Designer applies dataset and data region filters. Thus, the Details group is the only group that has no group expression.
  
  Basically, the details group specifies the data that you would see when you run a dataset query in a query designer. For example, you have a query that retrieves all columns from a sales order table. Thus, the data in this detail group includes all the values for every row for all the columns in the table. The data in this detail group also includes values for any calculated dataset fields that you have created.
  
  
  By default, when you add a table or list to your report, Report Designer automatically creates the Details group for you, and adds a row to display the detail data. By default, when you add dataset fields to cells in this row, you see simple expressions for the fields, for example, [Sales]. When you view the data region, the details row repeats once for every value in the result set.
  
  
• Row groups and column groups You can organize data into groups by rows or columns. Row groups expand vertically on a page. Column groups expand horizontally on a page. Groups can be nested, for example, group first by [Year], then by [Quarter], then by [Month]. Groups can also be adjacent, for example, group on [Territory] and independently on [ProductCategory].
  
  When you create a group for a data region, Report Designer automatically adds rows or columns to the data region and uses these rows or columns to display group data.
  
  
• Recursive hierarchy groups A recursive hierarchy group organizes data from a single report dataset that includes multiple levels. For example, a recursive hieararchy group could display an organization hierarchy, for example, [Employee] that reports to [Employee]. Reporting Services provides group properties and built-in functions to enable you to create groups for this kind of report data. For more information, see Creating Recursive Hierarchy Groups (Reporting Services).
  
• The following list summarizes the way you work with groups for each data region:

• Table Define nested row groups, adjacent row groups, and recursive hierarchy row groups (such as for an organizational chart). By default, a table includes a details group. Add groups by dragging dataset fields to the Grouping pane for a selected table.
  
  
• Matrix Define nested row and column groups, and adjacent row and column groups. Add groups by dragging dataset fields to the Grouping pane for a selected matrix.
  
  
• List By default, supports the details group. Typical use is to support one level of grouping. Add groups by dragging dataset fields to the Grouping pane for a selected list.
  After you add a group, the row and column handles of the data region change to reflect group membership. When you delete a group, you have the choice between deleting the group definition only or deleting the group and all its associated rows and columns. For more information, see Understanding Tablix Data Region Cells, Rows, and Columns.

To limit the data to display or use in calculations for detail or group data, set filters on the group. For more information, see Filtering Data in a Report.

By default, when you create a group, the sort expression for the group is the same as the group expression. To change the sort order, change the sort expression. For more information, see Grouping and Sorting Report Data.

Understanding Group Membership for Tablix Cells

Cells in a row or column of a Tablix data region can belong to multiple row and column groups. When you define an expression in the text box of a cell that uses an aggregate function (for example, =Sum(Fields!FieldName.Value), the default group scope for a cell is the inner most child group to which it belongs. When a cell belongs to both row and column groups, the scope is both innermost groups. You can also write expressions that calculate aggregate subtotals scoped to a group relative to another set of data. For example, you can calculate the percent of a group relative to the column group or to all data for the data region (such as =Sum(Fields!FieldName.Value)/Sum(Fields!FieldName.Value,"ColumnGroup")). For more information, see Understanding the Tablix Data Region and Calculating Totals and Other Aggregates (Reporting Services).

Groups in a Chart: Category and Series Groups

A chart supports nested category and series groups. Charts do not display detail data. Add groups to a chart by dragging dataset fields to the category and series drop zones for a selected chart.

Shape charts such as pie charts support category groups and nested category groups. Other charts such as bar charts support category groups and series groups. You can nest groups, but must make sure that the number of categories or series do not obscure the presentation of information about the chart. For more information, see Adding Data to a Chart Data Region.

Groups in a Gauge: Data Groups

A gauge typically does not use groups, but supports specifying group data. When you specify a group in a gauge, the group is used for aggregations which calculate the displayed data values. Add groups to a gauge through shortcut menu for a selected gauge. For more information, see Adding Data to a Gauge Data Region.

Adding Parameters to Your Report

In Reporting Services, parameters are used to specify the data to use in a report,
connect related reports together, and vary report presentation. To design a report
that uses parameters effectively, you must understand how parameters and dataset
queries work together, how parameters and expressions work together, how parameters
can be managed on the report server for a published report, and what questions a
report is designed to answer. What you choose to parameterize can influence the
report design and layout.

Query parameters are added to a dataset query by way of the query designers or the
Dataset Properties dialog box. After you create a query with parameters, Reporting
Services automatically links query parameters to report parameters with the same
name. New report parameters are added and modified by way of the Report Data pane.

Using Parameters in Reporting Services
The most common use of parameters is to vary report data retrieved by dataset queries.
In this scenario, users are prompted for a value or values when they run the report,
and the dataset query retrieves only the data that is requested. The dataset query
includes query parameters and Reporting Services automatically creates corresponding
report parameters that are indirectly linked to the query parameters. You can change
the report parameter properties in the report design tools to include a valid values
list and a user prompt that displays at run time.

You can also create cascading parameters, which retrieve hierarchical data from
a data source. For example, the first parameter might filter on product category,
and the second parameter might filter on product. Cascading parameters are used
to organize and limit the number of available values for the user.

Not all data sources support queries parameters. For those times when you cannot
filter data at the source, you can use report parameters to provide a users with
the ability to filter data after it has been retrieved for the report. While all
query parameters have corresponding report parameters, you can define report parameters
independently in the report designer tools. Report parameters can also be used to
change the appearance or organization of a report after the data has been retrieved.

Parameters are used to connect reports to sub reports and drillthrough reports.
These report parameters might be hidden, using values in the main report to select
the correct data for the connected report, or they might be visible to the user
at run time.

After a report parameter is created, you can modify the default values and other
parameter properties, such as visibility. For example, report parameters can use
built-in variables like UserID, so you might want to create a report with a hidden
parameter that selects data specific to the user running the report. Parameters
can contain single values or multiple values, use a static or query-based valid
values list, and accept null or blank values. Multivalue parameters enable users
to select more than one value at run time.

After a report is published, parameters can be managed independently from the report
definition. Depending on how you configure the parameter in the report at design
time, report server administrators can modify many parameter properties in the published
report. They can also create sets of parameters linked to the same report definition
that customize the report for multiple audiences.

Parameters in Reporting Services

Using Parameters to Control Report Data Discusses how to use parameters to filter report data, to change the sort order
for detail and group data, and to modify queries to work with specific data providers.

Using Query Parameters with Specific Data Sources (Reporting
Services) Describes the query syntax expected by the various data sources.

Using Parameters to Control Report AppearanceDiscusses how to use parameters to change report appearance using expression-based
properties, including conditionally hiding report items and conditionally changing
text color based on the values of a text box.

Using Parameters to Connect to Other ReportsDiscusses how to use parameters to link to drillthrough reports, subreports, and
linked reports.


Using Single-Value and Multivalue Parameters Discusses how to use single-value and multivalue parameters.

Creating Report Parameters and Setting Report Parameter
Properties Discusses how to create and set properties on a report parameter when you author
the report, including data type, default values, parameter prompts, available values,
multiple values, and whether parameters are internal, hidden, or visible to the
user at run time.

Adding a Document Map

Adding a Document Map

You can use a document map in a report to provide users with a way to navigate to certain areas of the report. When you view a report that includes a document map, a separate side pane appears next to the report. Report sections and groups are arranged in a hierarchy of links. Clicking items in the document map refreshes the report and displays the area of the report that corresponds to the item in the document map.

The document map is intended for use in the HTML rendering extension. Other rendering extensions have different ways of articulating a document map:

• PDF renders a document map as the Bookmarks pane.
  
• Excel renders a document map as a named worksheet that includes a hierarchy of links. Report sections are rendered in separate worksheets that are included with the document map in the same workbook.
  
• Word includes a document map as the table of contents.
  
  
• TIFF, XML ,and CSV ignore document maps.
  

For more information, see Comparing Interactive Functionality for Different Report Rendering Extensions.

To create a document map, add document map labels to those report items that should appear in the map, such as report items and groups. If any report items have a value set for the document map label, a document map is automatically generated when a user views the report.

To toggle the document map on and off, use the Document Map button on the report viewer toolbar. For reports rendered in HTML, you can specify whether to show the document map or not when the report is opened via a URL. The following example initially hides the document map for the report DocumentMapReport:

http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fDocumentMapReport&rs:Command=Render&rc:DocMap=False



How to: Add Items to a Document Map (Reporting Services) 
    
A document map provides a set of navigational links to report items in the rendered report. A user can click links in the document map to jump to the report page that displays that item. 
    
To add links to the document map, you set the DocumentMapLabel property of the report item to text that you create or to an expression that evaluates to the text that you want display in the document map. You can also add the unique values for a table or matrix group to the document map. For example, for a group based on color, each unique color is a link to the report page that displays the group instance for that color. For nested groups, the document map displays the group values in a hierarchy.
    
You can also create a URL to a report that overrides the display of the document map, so that you can .run the report without displaying the document map, and then click the Show/Hide button on the report viewer toolbar to toggle the display. 
  
To add a report item to a document map
    
In Design view, make sure the Properties pane is visible. Click the report item such as a table, matrix, or gauge that you want to add to the document map. The report item properties appear in the Properties pane.
         
In the Properties pane, type the text that you want to appear in the document map in the DocumentMapLabel property, or enter an expression that evaluates to a label. For example, type Sales Chart.
   
     
Repeat step 1 for every report item that you want to appear in the document map.
            
Click Preview. The report runs and the document map displays the labels you created. Click any link to jump to the report page with that item.
    

add unique group values to a document map
   
In Design view, select the table, matrix, or list that contains the group that you want to display in the document map. The Grouping pane displays the row and column groups.
      

      
In the Row Groups pane, right-click the group, and then click Edit Group. The Generalpage of the Tablix Group Properties dialog box opens.
              
Click Advanced.
       
      
In the Document map list box, type or select an expression that matches the group expression.
       
      
Click OK.
       
      
Repeat steps 1-4 for every group that you want to appear in the document map.
       
      
Click Preview. The report runs and the document map displays the goup values. Click any link to jump to the report page with that item. 
       
      
To hide the document map when you view a report 
   
In Report Manager, browse to the report that has the document map.
For example, for the AdventureWorks sample reports, the following URL specifies the report named Product Catalog.
          
Copy Code
http://localhost/Reports/Pages/Report.aspx?ItemPath=%2fAdventureWorks+Sample+Reports%2fProduct+Catalog
       
      
Copy the report path on the server. In the example, the report path is %2fAdventureWorks+Sample+Reports%2fProduct+Catalog.
       
      
Create a new URL with the following three components:
The report viewer on the report server: http://localhost/ReportServer/Pages/ReportViewer.aspx?

        
          
The name of the report you copied in step 1, for example: %2fAdventureWorks+Sample+Reports%2fProduct+Catalog

            
The device information parameters that specify hiding the document map: &rs%3aCommand=Render&rc%3aFormat=HTML4.0&rc%3aDocMap=False
           
The following URL consists of these three components appended in the order they are listed.
          
Copy Code
http://localhost/ReportServer/Pages/ReportViewer.aspx?
%2fAdventureWorks+Sample+Reports%2fProduct+Catalog
&rs%3aCommand=Render&rc%3aFormat=HTML4.0&rc%3aDocMap=False
To use this URL, copy it and remove all line breaks.
       
      
Paste the URL in Report Manager, and then press ENTER. The report runs, and the document map is hidden. 

Adding Links to a Report

You can add links that allow users to open Web pages or other reports, or jump to another location within the same report. To add a link, you define an action for a report item such as a text box, image, chart, or gauge. Use the Action page of the corresponding properties dialog box to set the type of link. When the user clicks that report item, the action that you define takes place. Reporting Services supports three types of links with the following actions:

• Bookmark links Jump to other areas within the report.
  
• Drillthrough report links Jump to other reports on the same report server.
  
• Hyperlinks Jump to URLs that specify the address of Web pages or reports on a report server by using URL access.
  

Bookmark Links

A bookmark link is a link that a user clicks to move to another area or report item in a report. To create a bookmark, set a bookmark on the destination report item and add bookmark links on report items that users should click, such as a word or image, to jump to the bookmarked report item. You can set bookmarks on any report item, including text boxes, images, charts, and gauges. You can add multiple bookmark links to the same bookmark. For more information, see How to: Add a Bookmark to a Report (Reporting Services).

Drillthrough Report Links :

A drillthrough report is a report that a user opens by clicking a link within another report. Drillthrough reports commonly contain details about an item that is contained in an original summary report. For example, you might have a sales summary report with a list of orders and sales totals. When a user clicks an order number in the summary list, another report opens that contains details about the order.

A drillthrough report serves a different purpose than a subreport. A subreport is processed as part of the main report. For example, if a subreport that displays order detail information is added to a table cell in the detail row, the subreport is processed once per row of the table and rendered as part of the main report. A drillthrough report is only processed and rendered when the user clicks the drillthrough link in the summary main report.

A drillthrough report typically contains parameters that are passed to it by the summary report. In the sales summary report example, the summary report includes the field [OrderNumber] in a text box in a table cell. The drillthrough report contains a parameter that takes the order number as a value. When you set the drillthrough report link on the text box for [OrderNumber], set the parameter for the target report to [OrderNumber]. When the user clicks order number in the summary report, the target detail report opens and displays the information for that order number. To view instructions about customizing drillthrough reports based on parameter values, see Adding Parameters to Your Report and InScope Function (Reporting Services).

To preview a summary report with drillthrough links on the report client, you must set the drillthrough report name to a report in the current project. To view a summary report with drillthrough links after it is published, you must ensure that the drillthrough reports reside on the same report server as the summary report. The drillthrough report name can contain a relative or absolute path to the report. You can add drillthrough links to text boxes, images, charts, and gauges. For more information, see How to: Add a Drillthrough Action on a Report (Reporting Services).

Hyperlinks :You can add a hyperlink to a report item so that a user can access a Web page by clicking the item. A hyperlink can be a static URL or an expression that evaluates to a URL. If you have a field in a database that contains URLs, the expression can contain that field, resulting in a dynamic list of hyperlinks in the report. You can add hyperlinks to text boxes, images, charts, and gauges. For more information, see How to: Add a Hyperlink to a URL (Reporting Services).

You can also specify URLs to reports on any report server that you and your users have permission to view using URL requests to the report server, known as URL access. For example, you can specify a report and hide the document map for the user when they first view the report. For more information, see URL Access.

List Data Region in SSRS

A list data region repeats with each group or row in the report dataset. A list can be used for free-form reports or in conjunction with other data regions. You can define lists that contain any number of report items. A list can be nested within another list to provide multiple groups of data.

Adding a List to Your Report

Add a list to the design surface from the Toolbox. By default, the list initially has a single cell in a row associated with the detail group.

When you select a list on the design surface, row and column handles appear, as shown in the following figure.

The list you start with is a template based on the Tablix data region. After you add a list, you can continue to enhance the design by changing the the content or appearance of the list by specifying filter, sort, or group expressions, or changing the way the list displays across report pages. For more information, see Controlling the Tablix Data Region Display on a Report Page (Reporting Services). Although the list starts with a single column and row, you can further continue to develop your list design by adding nested or adjacent row groups or column groups, or adding additional detail rows. For more information, see Exploring the Flexibility of a Tablix Data Region.

If you have a report model data source, you can also create simple list reports using Report Builder. For more information, see How to: Start Report Builder.

Displaying Data in a Free-form Layout To organize report data in a free-form layout instead of a grid, you can add a list to the design surface. Drag fields from the Report Data pane to the cell. By default, the cell contains a rectangle that acts as a container. Move each field in the container until you have the design you want. Use the snaplines that appear when you drag text boxes in the rectangle container to help you align edges vertically and horizontally. Remove unwanted white space by adjusting the size of the cell. For more information, see How to: Change Row Height or Column Width (Reporting Services).

The following figure shows a list that displays information about an order, including these fields: Date, Order, Qty, Product, LineTotal, and an image.

In Preview, the list repeats to display the field data in the free-form format, as shown in the following figure:

Displaying Data with One Level of Grouping Because a list automatically provides a container, you can use a list to display grouped data with multiple views. To change the default list to specify a group, edit the Details group, specify a new name, and specify a group expression.

For example, you can embed a table and a chart that show different views of the same dataset. You can add a group to the list so that the nested report items will repeat once for every group value. The following figure shows a list grouped by product category. Notice that there is no detail row. Two tables are nested side by side in the list. The first table displays the subcategories with total sales. The second table displays the category grouped by geographical area, with a chart that shows the distribution of subcategories.

In Preview, the table displays total sales for all subcategories of bicycles, and the table beside it displays the breakdown of sales per geographical area. By using an expression to specify the background color for the table and a custom palette for the chart, the first table also provides the legend for the chart colors.

Gauge Data Regions In SSRS

The Gauge data region is a one-dimensional data region that displays a single value in your dataset. An individual gauge is always positioned inside a gauge panel, where you can add child or adjacent gauges. You can use the gauge panel to create multiple gauges inside a single gauge panel that share common functions such as filtering, grouping, or sorting.

You can use gauges to perform many tasks in a report:

• 
  Display key performance indicators (KPIs) in a single radial or linear gauge.
  
  
• 
  Place a gauge inside a table or matrix to illustrate values inside each cell.
  
  
• 
  Use multiple gauges in a single gauge panel to compare data between fields.
  
  

The following illustration shows the basic elements of a single gauge in the gauge panel.

Reporting Services provides two types of gauges: radial and linear. Unlike the chart types available in the Select Chart Type dialog box, the gauge types available in the Select Gauge Type dialog box are created by using a combination of gauge properties. Therefore, you cannot change the gauge type the same way you change a chart type. To change the gauge type, you must remove the gauge and re-add it to the design surface.

A radial gauge has at least one scale and one pointer. You can have multiple scales by right-clicking the gauge and selecting Add Scale. By default, this will create a smaller scale that is positioned inside the first scale. Radial gauges have three types of pointers: marker, bar, and needle. The needle pointer is only available for the radial gauge. You can have multiple pointers by right-clicking the gauge and selecting Add Pointer. This will create another pointer on the same scale, but if you have multiple scales, you can associate a pointer with any scale on the gauge.

The scale on a radial gauge is a circular scale that displays labels and tick marks. There are two sets of tick marks, minor and major.

A linear gauge functions the same as a radial gauge, except for its shape and orientation. Linear gauges are useful for integrating into the table or matrix data regions to show progress data.