Reporting Skills for Microsoft® SQL Server™ 2005 Reporting Services

An Overview of SSRSSSRS provides applications and tools to manage the entire lifecycle of enterprise reporting, which includes authoring, publishing, and managing reports. SSRS supports interactive reports, which are based on OLTP or OLAP data, in addition to reports that generate static content. These reports are server based and can be generated in several supported formats, such as XLS, PDF, HTML, and XML. SSRS displays the entire report server operations as Web services to provide interoperability with different solutions. Moreover, SSRS provides an extension model for data processing, rendering, security and delivery processing engines. By using this model, you can build custom extension tools.

SSRS contains a set of tools, such as Report Designer, Report Server, Report Manager, Report Model Designer, and Report Builder, for creating, publishing, and managing reports.

Component Description
Report Designer
A report-authoring tool that is hosted in the Microsoft Visual Studio® environment. This tool is used to design reports and publish them to a report server.

Report Server
A component that includes a Web service and the Microsoft Windows® Report Server service. The Web service provides a set of programmatic interfaces that client applications use to access report servers. The Report Server service provides scheduling and delivery services.

Report Manager
A Web-based report access and management tool that you can use to create, view, and manage reports.

Report Model Designer
A new tool in that is included in Business Intelligence Development Studio of SQL Server 2005. You can use this tool to view, design, edit, and refine report models.

Report Builder
A report-authoring tool that is used to design spontaneous reports by using data sources or report models and templates.

Authoring Reports

Report Designer is an authoring tool that you can use to create a report definition. A report definition is a blueprint of a report that is created before the report is processed and rendered. A report definition includes the layout, connection, and query information for generating the report. This definition is a Report Definition Language (RDL) file that defines the structure of the report by using an XML grammar. Because RDL is also an open schema, you can add attributes and elements to it.

Report Designer transforms your design choices into a report definition. However, you can also use any third-party authoring tool that generates report definitions. Report Designer creates a project workspace in Microsoft Visual Studio, and supports drag-and-drop operations to create reports. Typically, while using Report Designer, programming knowledge is required only if you want to add functionality to the reports.

To create reports by using Report Designer, you need to connect to the desired data source and build a query that retrieves data. Next, you need to lay out the report by dragging table, matrix, chart, and other report controls onto the design surface. Finally, you need to add data to the report and set properties to customize the layout of the report.

In SSRS, you can write custom code for report item values, styles, and formatting by using Microsoft® Visual Basic .NET. For example, you can write custom code to format currencies based on locale, flag certain values with special formatting, or apply other business rules that are in practice in your company. You can also reference an assembly that has been generated by using any language supported by the .NET Framework.

Publishing and Managing Reports

After you have designed and tested a report, you can use Report Designer to publish the report to a production report server. A report server is a stateless, server-managed component on the Web server that uses the SQL Server database engine to store metadata and object definitions. The report server exposes APIs that can be used for publishing reports.

After you publish a report, it becomes a managed component of the report server. You can manage the reports on the report server by using Report Manager or SQL Server Management Studio. The following are some of the report management tasks that you can perform by using Report Manager and the Simple Object Access Protocol (SOAP) APIs included with SSRS 2005:

You can set security permissions for individual reports to control user access to the reports. SSRS uses Windows authentication and role-based authorization to determine access to the report server. Role assignments also determine if a particular user or set of users have the privileges to perform specific report management tasks.

You can schedule reports to be generated at specific times or during off-peak hours. You can also schedule reports to be generated either once or on an hourly, daily, weekly, or monthly basis.

You can cache a copy of a processed report on the report server. Caching shortens the time required to retrieve a report if the report is large or is accessed frequently. When the server is restarted, all cached instances are reinstated when the Report Server Web service comes back online.

You can use subscriptions to schedule and automate the delivery of the most up-to-date report. A subscription is a standing request to deliver a report at a specific time or in response to an event, and in a defined format. Subscriptions provide an alternative to on-demand reporting, where you need to actively select the report each time you want to view it.

Accessing Reports

After a report is generated, it needs to be accessible to the end users who have the permissions to view the report. Users can access and view the reports by using a Web browser or Microsoft Excel 2003. SSRS 2005 supports two methods for accessing and delivering reports, on-demand access and subscription-based access.

On-demand access allows users to select reports from a report-viewing tool. To view a report on demand, a user needs to select a report from the report server folder hierarchy. Users can browse through the report server folders or search for specific reports by using Report Manager.

Subscription-based access automatically generates and delivers reports to a destination. To receive a report automatically, a user has to subscribe to that report. When the report runs, the user is either notified that the report is available or a copy of the report is sent through an e-mail message.

Report server administrators can build data-driven subscriptions that deliver reports to a large group of users. In a data-driven subscription, delivery settings are built from the stored data when the subscription is triggered.

Designing Ad Hoc Report Models

In SSRS, you can view, design, and edit report models by using Report Model Designer. Report Model Designer is a new SSRS tool, which is included in the Business Intelligence Development Studio. This tool uses a new XML-based definition language, called Semantic Model Definition Language (SMDL) for creating report models. SMDL is a set of information that describes what data is available, how the data is related, and where the data is located.

A report model provides an abstraction layer for the data store. Therefore, you can send queries to the abstraction layer rather than to the underlying data. The model permits aliasing the columns of the data store and the definition of relations across the referenced objects. This ensures that the end user is not required to know the underlying structures and can be provided with a business view of the system.

To build a report model, you need to first create a report model project in Report Model Designer. A report model project is the development environment for developing a report model and includes a data source (.ds) file, a data source view (.dsv) file, and one or more report model (.smdl) files. However, only one data source and data source view can be used to create a report model file. This file serves as a base for creating ad hoc reports in Report Builder.

Building Ad Hoc Reports

Report Builder is a report-authoring tool that you can use to design ad hoc reports by using data sources and report models. You can then publish the reports to a report server and manage them.

Report Builder provides the capability to work over the abstracted models provided by Report Model Designer. By using Report Builder, you can create table, matrix, and chart reports. When you launch Report Builder, you need to select the report model on which you want to base the report. Then, you need to select a report layout template and drag the entities and fields that you want to add to your report onto the design area. You can then customize the report by filtering, grouping, and sorting, or adding formulas to calculate values.

While designing a report, you need to work with the report layout. Report Builder uses Report Definition Language (RDL) to design report layouts. Therefore, the actual data is not visible when you are working on the report layout. However, you can run the report to see the data within the report layout. When you run the report, the report server combines the data with the report layout. After creating and saving your report, you can continue to open the report and edit the report layout. However, you can use Report Builder to open only reports that are created by using Report Builder.

————————————————————————————–

SSRS Core Concepts

——————————————————————————–

SSRS has a modular architecture. This feature supports processing of reports across multiple components that can be extended or integrated into custom applications. This feature allows developers to extend the reporting functionality to support custom features. SSRS also provides an API that is used as a Web service with SOAP and URL endpoints. This allows easy integration with new or existing applications and portals.

Data Sources, Datasets, Fields, and Filters

When you write a report, you need to identify the underlying report data. This includes setting up a connection to a data source, defining a query, and defining the list of fields that you want to include in the report. By using the following components of SSRS, you can specify the data that is to be included in a report.

Data Sources
An SRSS data source contains information about the connection to a database. This includes information, such as the server name, database name, and user credentials. The information contained in a data source depends on the type of database. SSRS supports relational databases such as SQL Server and multidimensional databases such as SSAS.

A data source can be contained in a single report, or can be shared by multiple reports. The definition for a report-specific data source is stored within the generated report. However, the shared data source is stored as a definition on the report server.

Datasets
An SSRS dataset contains information about the query and provides a reference to the data generated by the query, which is used by a report. A dataset includes a pointer to a data source, the query, and information about the data, such as collation and case-sensitivity. A dataset also includes a list of fields to be used by the report. A report can contain multiple datasets. These datasets can be used by different data regions on the report. The datasets can also be used to provide dynamic lists of parameters.

Fields
An SSRS dataset in a report contains a list of fields. A field can either be a database field or a calculated field. A database field contains a name property that can be used to provide a custom name for the database field. A calculated field includes a name and an expression. The expression can be simple, for example, concatenation of two database fields including first name and the last name, or it can be used to perform complex calculations.

Filters
An SSRS filter is used to filter report data after it has been retrieved from the data source. Filters are different from query parameters. When you use a query parameter, the data is filtered at the source. However, when you use a filter, the entire data set is retrieved, and then the filter is applied to the retrieved data.

A filter is useful for snapshot reports, in which the data is retrieved and stored with the snapshot report. By using filters, you can limit the data that a report displays, while using the static snapshot data. Filters are also useful when the data source does not support the use of query parameters to filter data.

Report Items

A report in SRSS uses report items to display data and graphical elements, such as data regions, text boxes, images, lines, rectangles, and subreports.

Table: A table or matrix cell includes a text box by default to display data. Text boxes can be placed anywhere on a report and can contain labels, fields, or calculated data. You can also use expressions to define data in text boxes.

Image: An image can be used to display a URL, an image stored on a Web server, embedded image data, or an image from binary data in a database. SRSS supports .bmp, .jpeg, .gif, and .png files.

Line: A line is a graphical element that you can place anywhere on a page. A line is defined by a start point and an end point and can have various attributes, such as weight and color assigned to it. A line has no data associated with it.

Rectangle: A rectangle can be used in two ways, as a graphical element and as a container for other report items. When you place report items within a rectangle, you can move them with the rectangle. This is useful for keeping numerous text boxes and other items together in a report.

Subreport: A subreport is an item in a report that points to another report on the report server. The report that the subreport refers to can either be a full report, which can also run on its own, or it can be a report that looks best when embedded in the main report.

All items in a report including groups, tables, matrix columns, rows, graphical elements and the report itself have properties associated with them. These properties govern the appearance and functioning of the item.

Moreover, each item has a set of properties that determine whether the item is visible or hidden. You can use these properties to hide items on a report. Preferably, you should hide data based on other data in the report, and provide an item that the user can click to toggle items between visible and hidden. For example, you can create a report that shows summary data when the report is first loaded and shows detail rows when users click a particular text box.

Data Regions

Data regions are report items that display the data that is stored in the underlying datasets. The following are the different types of data regions:

Table: A table data region presents data in a row and column format. The columns in the table are static and the rows expand downwards to accommodate the data. To organize the data in a table by selected fields or expressions, you can add groups.

Matrix: A matrix is also known as a crosstab. A matrix data region contains both columns and rows that expand to accommodate the data. A matrix can have dynamic or static columns and rows.

Chart: A chart data region presents data graphically in the form of bar, pie, and line charts.

List: A list data region displays data that is arranged in a freeform fashion. You can arrange report items to create a form with text boxes, images, and other data regions placed anywhere within a list.

You can nest data regions within other data regions. For example, if you want to create a sales record in a database for every sales person in the company, you can create a list with text boxes and an image to display information about the employee, and then add table and chart data regions that show the employee’s sales record.

The data within data regions, such as table, matrix, and list, can be sorted by fields and expressions. The sort capability is added to a report before it is published. You can sort data during data processing by specifying an ORDER BY clause in the query. You can also configure a report to support end-user sorting, allowing users to interactively change the sort order while viewing a report. To do this, you can use parameters to pass values to the underlying query.

Parameters

You can add parameters to a report to manipulate the data contained in the report. You can use parameters to pass values to an underlying query or filter. You can also use parameters to pass values to variables that are used for calculating data within a report.

You can design a report that allows the users to enter a parameter or select a parameter from the list of available values or valid values. These available values contain a set of value/label pairs. When the report is run, the users can select a label from the list and the corresponding value will be used as the parameter to generate the report.

You can also generate a report by defining a default value for the parameter. If all the parameters in a report have default values, the report will immediately display data when the report is run. If at least one parameter does not have a default value, then the report will only display data after the user enters all parameter values and runs the report. There are two types of default values, non-queried and queried.

A non-queried default value is a static value or an expression. A queried default value is an expression that points to a field in a dataset. If the query returns multiple rows, the value from the first row of the returned dataset is used.

Document Maps

A document map is a viewing mode that you can use to quickly navigate through the different sections of a report. When you view HTML, Excel, or PDF reports, a document map appears alongside the report. This document map acts as a table of contents for the report. When you click an item in the document map, the report refreshes to display the section of the report that corresponds to that item.

To create a document map, you need to add document map labels to the report items that should appear in the map, such as group headings and charts. These document map labels appear as items in the document map. When you view a report that contains document map labels by using the HTML Viewer, the document map is automatically generated. To view the document map, you can click the Show/Hide button on the report toolbar in the HTML Viewer.

—————————————————————————————-

Creating a Data Source

The first step in creating a report is to create a data source. A data source is a named collection of properties that describes a connection to an external database. It contains the following components:

Name of the data processing extension that is used to process the query.

Connection string that is used to locate the data source.

Credentials that are used to access the data source.

Reporting Services provides different types of data sources such as SQL Server, Oracle, ODBC, and OLE DB. By using SSRS, you can connect to relational databases such as SQL Server and multidimensional (OLAP) data sources such as SQL Server Analysis Services (SSAS).

You need to define the connection information of a data source while creating a report or data-driven subscription. However, after publishing the report, you can modify the data source properties or map the report to a different data source altogether. You can also embed a data source connection in a report or subscription, or define it as a shared data source item. A shared data source item can be created to define a connection to a frequently used data source and reference it whenever connection information of that data source is needed. A report server manages a shared data source item separately.

Steps to Create a Data Source
To create a data source, you need to perform the following steps:

Select the data source: During the creating of a report by using the Report Wizard, the first option is to select the data source. Here, the wizard also provides the option of defining a new data source and designating the same as a shared data source.

Specify connection information: In the Connections Properties dialog box, a data source type is selected. The provider selected to access a relational or multidimensional underlying data store also determines the other details that are required to connect to the data source.

While referencing data source connections, you can specify the server name by referring to the default or a named instance of the server. You can reference named instances by using the command \. In data source connections, you can also specify an initial catalog. If this information is not provided, the default database of the connected user is used.

Creating a Report Using the Report Wizard

This demonstration shows how to build a report in Business Intelligence Development Studio. The first step in creating a report is to create a report project. To do this, launch the Report Project Wizard. Next, create a data source that will be used to retrieve data. By using the wizard, you can select an existing data source or create a new data source.

In this demonstration, you will create a new data source, Adventureworks. On the Select the Data Source page, ensure that the data source type is selected as Microsoft SQL Server. Then, click Edit to set the connection properties of the data source. Specify the database server name. Notice that in the Log on to the server section, the Use Windows NT Integrated Security option is selected by default. Now select Adventureworks as the data source, and click Next.

To create a dataset from the selected data source, enter an appropriate query string or build a query by using Query Builder. In this demonstration, you will build a query by using Query Builder. To create a new query in the graphical query designer, click Generic Query Designer. Then, select the table based on which you want to define the query. In the Add Table dialog box, notice that all the database tables are listed. You can select one or more tables from this list.

In this demonstration, you will create a query based on the Product table. So, select Product (Production) table and click Add. Add the fields Name, Product Number, Color, ListPrice, Size, and Class to the query, and then click OK. On the Design the Query page, click Next. The Select the Report Type page appears. On this page, you can specify whether you want to create a tabular or matrix report. Based on your selection, relevant wizard pages will be displayed.

Demonstrated procedure
Creating a Report Using the Report Wizard

Start Business Intelligence Development Studio.

On the File menu, point to New, and then click Project. The New Project dialog box appears.

In the Visual Studio installed templates section, click Report Project Wizard. Click OK. The Report Wizard appears.

On the Welcome to the Report Wizard page, click Next. The Select the Data Source page appears.

On the Select the Data Source page, double-click the Name box, type Adventureworks, and then click Edit. The Connection Properties dialog box appears.

In the Server name box, type your computer name.

In the Select or enter a database name list, click AdventureWorks, click OK, and then click Next. The Design the Query page appears.

On the Design the Query page, click Query Builder. The Query Builder window appears.

On the Data view toolbar, use the toggle tool to switch from Generic Query Designer to Graphical Query Designer.

On the Data view toolbar, click Add Table. The Add Table dialog box appears.

Scroll down the Tables list, click Product (Production), and then click Add. The fields of the Product table appear in the Query Builder Tables pane. Click Close.

Select the Name, ProductNumber, Color, ListPrice, Size, and Class check boxes, and then click OK. The generated query appears in the Query string box.

On the Design the Query page, click Next. The Select the Report Type page appears.

Designing a Table Report

The Report Wizard provides options to build either a tabular or matrix report. The report is based on a selected data source. You can use Query Builder to design a query for fetching the data from the data source. Query Builder provides different interfaces for relational and multidimensional data.

In a tabular report, a table data region presents data in a row and column format. The columns in the table are static and the rows can expand downwards to accommodate the data. You can create a tabular report by clicking Tabular on the Select the Report Type page. When you do so, the Design the Table page is displayed. On this page, you can specify the arrangement of data in the table by using the following components:

Page: Fields added to this list are displayed at the page level. You can use this section to create a separate report for each set of data. For example, when you create accounts statement reports for your customers, you can use the Page section to display the customer address in the page header.

Group: Fields added to this list are used to group data. Each group of data is displayed in a separate section. For example, in an account statement, you can group data based on the Month field to automatically obtain monthly subtotals.

Details: Fields added to this list appear in the Details section of the report. These fields are grouped by the fields in the Group section.

To add a field, click the field in the Available fields list, and then click the Page, Group, or Details button. Alternatively, you can drag the field into the appropriate box. After selecting the fields, you need to specify the layout options for the report by using the Choose the Table Layout page. The Report Wizard provides two built-in layout options, Stepped and Block.

Click to view and print a table that explains the table layout options.
After selecting the layout, you need to select a style to be applied to the report by using the Choose the Table Style page. The five built-in styles available are Bold, Casual, Corporate, Compact, and Plain. The final step in creating a report is to specify the location for publishing the report and the name for the report. When you complete all the steps, Report Designer creates the report and displays it within the report project.

Modifying a Table Report
You can modify the table report by adding columns and rows. You can do this by right-clicking the row or column, and then selecting the appropriate action. These visual selections are similar to the set on column and set on row definitions in the MDX queries and provide a visual interface to replicate the hierarchy in the multidimensional data store. You can also add a new table to an existing report by using the Table control in the Toolbox.

A tabular report can use one of the following layout options.

Layout Option Description
Stepped
Creates a report that contains one column for each field and the group fields appear in the group headers to the left of the detail field columns.

Block
Creates a report that contains one column for each field and the group fields appear in the first detail row for each group.

You can select the Include subtotals check box to include subtotals for the numeric fields in the report. If the Stepped layout is selected, the subtotal is placed in the group header rows. If the Block layout is selected, the subtotals appear in the group footer rows.

When you select the stepped layout, you can select the Enable drilldown check box to hide the inner groups of the report. This provides a visibility toggle, which results in a drilldown report.

Designing a Matrix Report

In a matrix report, a matrix data region represents data in a multidimensional format. The rows and columns in the matrix can expand to accommodate the report data. A matrix can have dynamic or static rows and columns.

You can create a matrix report by selecting Matrix on the Select the Report Type page. When you do so, the Design the Matrix page is displayed. On this page, you can specify the arrangement of data in the matrix by using the following components:

Page: Fields added to this list are displayed at the page level. You can use this section to create a separate report for each set of data.

Columns: Fields added to this list map to the multidimensional selection on columns. Columns can be hierarchical and enable drilldown based on the column selection.

Rows: Fields added to this list map to the multidimensional selection on rows. Rows can be hierarchical and enable drilldown based on the row selection.

Details: Fields added to this list appear in the Details section of the report. The numerical data in this section is aggregated and displayed for each section based on the column and row selection.

Note
The numerical data in the details section is aggregated. By using data aggregation, you can display subtotals by row grouping. The default aggregation is the SUM function. Based on the row selection, the wizard also provides the option of drill down.

To add a field, click the field in the Available fields list, and then click the Page, Columns, Rows, or Details button. Alternatively, you can drag the field into the appropriate box.

After selecting the fields, you need to select a style to be applied to the report by using the Choose the Matrix Style page. The five built-in styles available are Bold, Casual, Corporate, Compact, and Plain.

The final step in creating a report is to specify the location for publishing the report and the name of the report. When you complete all the steps, Report Designer creates the report and displays it within the report project.

Modifying a Matrix Report
You can modify the matrix report by adding static rows and columns that are fixed at design time, or by adding dynamic rows and columns that are based on the underlying data. To add a dynamic row or column, drag the desired field from the Fields list onto a column or row header in the matrix. To add a static row or column, drag a field from the Fields list onto a populated data cell. You can also add a new matrix to an existing report by using the Matrix control in the Toolbox.

Creating a Dataset

You can create a dataset from any type of data store, such as relational or multidimensional, by using the Dataset tab in Report Designer. To create a dataset, click New Dataset on the Dataset tab. This opens the Dataset dialog box. In this dialog box, you need to specify the name and the data source for the dataset. You also need to select a command type. The following are the available command types:

Text: To use a query

StoredProcedure: To execute a stored procedure by name.

TableDirect: To retrieve data from a table by name.

You need to specify a query string, which can be a query, a stored procedure, or a table name. You can use the other tabs of Dataset dialog box to specify several properties of the dataset.

To create an MDX dataset for an Analysis Server database, you can use the Data view to specify a cube from which data needs to be retrieved. The metadata browser displays the objects stored in the current cube from where you can drag objects to the results and filter panes to form an MDX dataset.

You can also add calculated members and specify filters to an MDX dataset. Calculated members are custom members that are defined in a query. For example, if a cube contains the Sales and Cost members, you can define a calculated member named Profit that displays the difference between Sales and Cost. You can use the calculated members pane to add calculated members to your dataset.
Roll over the image for more information.A rollover graphic of the Dataset tab in Report Designer.

Creating a Chart Data Region

You can use the chart data region to graphically represent the data in a report. To add a chart data region to a report, in the Layout view, first click the Chart control in the Toolbox. Then, on the design surface, drag a box of the size you want the chart to be. Alternatively, you can click the design surface to create a chart of fixed size.

After adding the chart data region, in the Layout view, right-click the chart, and then click Properties. The Chart Properties dialog box is displayed. On the General tab, specify the chart name, chart type, and chart subtype. Then, click the Data tab and specify the following details:

Dataset name: To select a dataset for the chart.

Values: To provide the data for the chart. For example, in a column chart, data from the Values list determines the height of the columns and the labels on the y-axis of the chart.

Category groups: To group the data being displayed in the chart. Categories provide the labels for chart elements. For example, in a column chart, category labels are placed on the x-axis of the chart, one for each set of columns.

Series groups: To add an additional dimension of data to a chart. For example, in a column chart that displays sales by product, you can add a series group to display data by year for each group. Series group labels are included in the legend of the chart.

SSRS supports several types of charts, such as column, bar, line, pie, XY (scatter), bubble, area, doughnut, and stock.

Click to view and print a job aid to view more information about the chart types.
After defining the data to be used in the chart, you can define the appearance of the x-axis and y-axis in the chart by using the X Axis and Y Axis tabs of the Chart Properties dialog box, respectively. Then, you can specify the appearance of the legend in the chart by using the Legend tab and define the three-dimensional effects to be used by using the 3D Effect tab. Finally, you can set the filter options for the chart by the Filters tab.

The following are the chart types supported by SSRS.

Chart Type Description
Column
Displays data as sets of vertical columns. Values are represented by the height of the columns as measured on the y-axis. Category labels are displayed on the x-axis. Column charts are typically used to compare values between categories.

Bar
Displays data as sets of horizontal bars. Values are represented by the length of the bars as measured on the x-axis. Category labels are displayed on the y-axis. Bar charts are also typically used to compare values between categories.

Line
Displays data as a set of points connected by a line. Values are represented by the height of the point as measured on the y-axis. Category labels are displayed on the x-axis. Line charts are typically used to compare values over a period of time.

Pie
Displays data as percentages of the whole. Categories are represented by individual slices. The size of the slice is determined by the value. Pie charts are typically used to show percentages.

XY (Scatter)
Displays data as a set of points in space. Values are represented by the position of the point in the chart space. Categories are represented by different points in the chart. Scatter charts are typically used to compare distinct values across categories.

Bubble
Displays a set of symbols whose position and size are based on the data in the chart. Values are represented by the position of the point in the chart space and the size of the symbol. Categories are represented by different symbols in the chart.

Area
Displays data as a set of points connected by a line, with a filled-in area below the line. Values are represented by the height of the point as measured on the y-axis. Category labels are displayed on the x-axis. Area charts are typically used to compare values over a period of time.

Doughnut
Displays data as percentages of the whole. Categories are represented by individual slices. The size of the slice is determined by the value. Doughnut charts are typically used to show percentages.

Stock
Displays data as a set of lines with markers for high, low, close, and open values. Values are represented by the height of the marker as measured on the y-axis. Category labels are displayed on the x-axis.

Creating a List Data Region

A list is a data region that displays data in a freeform fashion. You can use a list data region to display a set of distinct non-repetitive values. You can also create a form that contains text boxes, images, and other data regions within a list.

You can create a list with any number of report items by using Report Designer. A list can be nested within another list to provide multiple groupings of data. For example, if you want to create a sales record for sales executives, you can create a list with text boxes and an image to display information about the employee, and then add table and chart data regions to show the employee’s sales record.

To add a list, you need to perform the following steps:

In the Layout view, in the Toolbox, click List.

On the design surface, drag a box to the size of the list you need. Alternatively, click the design surface to create a list with a fixed size.

Interactive Sorting

The data in the data regions of a table, matrix, or list reports can be grouped or sorted based on fields and expressions. You can form groups within a table to provide logical sections of data. In a matrix, groups are displayed as dynamic columns or rows. In a list, you can include any number of report items and form separate groups for each item. You can also create multiple groups by nesting a list into another report.

You can sort data in a data region by using sort orders specified in a query or parameter values of the report. In addition, SSRS provides a feature of sorting the data in a report after it is generated. This feature is applicable only for table and matrix reports.

SSRS also provides sorting on recursive hierarchy. A recursive hierarchy is a hierarchy of data in which all parent-child relationships are represented in the data. For example, an organization chart displaying the reporting structure in an organization can be created by using a recursive hierarchy.

To provide interactive sorting in a published report, you need to set textbox properties on column headings. You can specify sorting for multiple columns in the same report, and for nested or grouped data. To verify the sort action, you can preview the report or view the report by using Report Manager. In the output report, columns that support interactive sorting display arrow icons to indicate sort order. To change the sort order at run time, click the column headings.

To implement interactive sorting, perform the following steps:

In a published report, right-click a column heading based on which you want to sort the report.

Click Properties. The Textbox Properties dialog box is displayed.

Click the Interactive Sort tab.

Select the Add to add an interactive sort action to this text box check box.

To specify a sort expression, select the field that corresponds to the column for which you are defining the sort action.

Select the data region and scope for the sort. This determines whether the sort action applies to all of the data regions in a report, is limited to the data region that contains the textbox, or is scoped to some other set of data regions.

Click OK.

Nested Data Regions and Subreports

You can use a nested data region to display the same data region multiple times in a report. For example, you can create a sales order data report that repeats a single sales order table multiple times, once for each employee. You can use nested data regions to nest groups of data from the same data source within a single data region.

A subreport is an item that displays another report inside the body of a main report. If you have to create a report that uses grouping with different datasets, you need to use a subreport. You can use subreports to:

Nest groups of data from different data sources within a single data region.

Reuse a subreport in multiple parent reports.

Display a standalone report within another report.

When you run a report that contains a subreport, the report server has to process each report separately. If the report contains nested data regions, the report server processes only one report and thus saving the processing time.

The NoRows Property

Data regions are report items that generate and display rows of data based on underlying datasets. These rows are displayed as tables, matrices, lists, or charts. When the dataset of a data region does not return any data, a text box showing the value of the NoRows property is displayed.

By default, the NoRows property text box contains the same window properties, such as Color, Font, Padding, as that of the data region. However, you can edit these properties by using the Properties window of the Report Designer.

—————————————————————————————

Deploying a Report from Visual Studio

You need to set several deployment properties to deploy a report from Visual Studio.

In Solution Explorer, right-click the report project, and then click Properties.

In the Property Pages dialog box, set the several deployment properties, such as OverwriteDataSources, TargetDataSourceFolder, TargetReportFolder, and TargetServerURL.

After configuring the deployment properties, to deploy all the reports to the report server, click Deploy Solution on the Build menu. Alternatively, to deploy a report, right-click the report (.rdl file), and then click Deploy.

Deploying a Report Using Scripts

Scripting can be used to copy security definitions to multiple reports on a server, add and delete report server items, and copy report server items from one server to another. By using scripts, you can easily and efficiently perform repetitive tasks on a report server and duplicate work from one server to another. You can also interact with the report server by using Windows Management Instrumentation (WMI) in scripts.

You can use the SQL Server Management Studio to generate scripts against the objects hosted in the Object Explorer tree of an SSRS server. These scripts can be used to host multiple instances across servers. You can also play back these objects by using the rs command-line utility.

You can perform various operations on a report server by using the rs utility (Rs.exe). You can administer a report server by using Visual Basic .NET scripts and the rs utility. You can do this by executing the command rs -i inputfile -s serverURL [-u username] [-p password] [-l timeout] [-b] [-v var=value] [-t] at a command prompt.

The parameters of the rs utility are listed in the following table.

Option Parameter Description
-i
inputfile
Script file for execution.

-s
serverURL
URL (including server and vroot) to execute script.

-u
username
User name used to log in to the server.

-p
password
Password used to log in to the server.

-l
timeout
Number of seconds before the connection to the server times out. The default value is 60 seconds. If you specify this value to 0, infinite time out is set.

-b

Run as a batch and rollback if the command fails.

-v
var=value
Variables and values to pass to the script.

-t
trace
Include trace information in error message.

The rs utility takes a reporting services script (.rss) file as input.

An .rss file is a Visual Basic .NET code file, which defines the Reporting Services SOAP API. It is written against a proxy that is built on the Web Service Description Language (WSDL). You can create .rss files in VB.NET and use them to run any of the Reporting Services Web service operations.

Deploying a Report Using Report Manager

Report Manager is a Web-based tool that can be used to manage reports on a report server. By default, this tool is installed along with the report server. You can access Report Manager by using the shortcut available in the SSRS program group or the URL http:///Reports$instanceName. The Report Manager administrative website can be used to create and manage report folders, and organize reports under different folders.

To create a new folder in Report Manager, you need to perform the following steps:

Select the folder under which you want to create the new folder, and then click New Folder.

In the New Folder page, specify the folder name, and then click OK.

To upload a report to Report Manager, you need to upload the Report Definition Language (RDL) file for the report. The RDL file is an XML-based definition file that contains the complete definition of the report.

To upload a report to Report Manager, you need to perform the following steps:

Create an RDL file by using the report development tools such as Visual Studio Report Designer.

Create a folder in Report Manager, and then click Upload File.

Select the RDL file, provide a name to the report, and then save the definition.

In addition to the normal deployment options, you can integrate the deployment of reports into an application deployment project. A .NET application can deploy reports to the report server by posting the RDL files by using a Web services call containing the SOAP definition.

Managing Reports:Configuring Shared Data Sources

A data source defines the connection between a report and the underlying data source. You can create a data source that is specific to a report or shared by multiple reports. A shared data source provides connection information to multiple reports. You can create a shared data source by using SQL Server Management Studio or Report Manager.

To create a shared data source by using SQL Server Management Studio, you need to perform the following steps:

In Object Explorer, expand a report server node.

Right-click the folder in which you want to create the data source, and then click New Data Source.

On the General page of the New Data Source dialog box, specify the data source name, data source type, connection string, and security credentials. Click OK.

When you create a data source in a folder, it automatically becomes a shared data source.

To create a shared data source by using Report Manager, you need to perform the following steps:

Navigate to the Contents page and click New Data Source.

On the New Data Source page, specify the data source name, data source type, connection string, and security credentials. Click OK.

After creating a data source, you can open it in Report Manager by selecting it from the folder in which it was created.

Security Credentials of a Data Source
You can specify the following security credential options to a shared data source by using the Security tab of the Data Source Properties dialog box:

To prompt the user for a login name and password, click The credentials supplied by the user running the report.

To use the data source with reports that support subscriptions or other scheduled operations, click Credentials stored securely in the report server.

To pass the credentials of the user accessing the report to the server hosting the external data source, click Windows NT Integrated Security.

If you do not want to apply any security credentials, click Credentials are not required.

Managing Reports:Configuring Report Properties

After a report is published, you can set several report properties to meet customized requirements. For example, you can set properties to specify how users access the report, how the report server connects to an external data source, and how the report executes. To set report properties, you can use either Report Manager or SQL Server Management Studio.

To set report properties by using SQL Server Management Studio, you need to perform the following steps:

In Object Explorer, expand the report server node.

Expand the Home folder and navigate to the folder containing the report that you want to configure.

Right-click the report and then, click Properties.

In the Report Properties dialog box, click the page containing the properties you want to configure.

Configure the required properties, and then click OK.

You can also use SQL Server Management Studio to script report properties. This script is an rss script and is used by the Rs.exe utility for configuring report properties.

To set report properties by using Report Manager, you need to perform the following steps:

Navigate to the Contents page. Navigate to the report that you want to configure and open it.

Click the Properties tab and configure the desired report property.

When you have finished configuring the properties, click Apply.

You can then use Execution Properties to control whether the report executes on demand or on a schedule. You can set Execution Properties as one of the specifications described below.

Hide All

On Demand
This property specifies that the report should be cached when it is run, resulting in up-to-date data being displayed in the report. The cache is valid until it reaches the expiration time set. With this approach, if ten users open the report simultaneously, ten queries are sent to the data source for processing.

On Demand From Cache
This property specifies that the report should be cached temporarily when the report is run. With this approach, if ten users open the report, only the first request results in report processing. The report is cached, and the remaining nine users view the cached report.

From Snapshots
A report snapshot contains the layout information and a dataset that is retrieved at a specific point in time. A report snapshot is created and subsequently refreshed on a schedule, thus allowing you to specify when the report should be processed. For example, you can run a report as a report snapshot to prevent the report from being run during scheduled backups.

Managing Reports:Configuring Subscriptions

A subscription is a standing request to the report server to deliver a report at a specific time or in response to an event. Subscriptions use delivery extensions to distribute a report in a specific way and format. A subscribed report can be delivered by e-mail or file transfer or by custom delivery mechanisms. Administrators can use the null delivery provider method of subscription to improve server performance by preloading the cache.

Subscriptions can also be used to generate offline reports, generate archival reports, and batch reporting. For the subscription to deliver a report, you need to configure the credentials or specify no credentials.

In Report Manager, to create a subscription for a report, open the report you want to subscribe. On the Subscriptions tab of the report, click New Subscription to provide the subscription details.

The user creating a subscription owns it. Ownership of a subscription is not transferable. However, you can add additional users to a subscription depending on the configuration of file settings. Subscriptions may be standard or data-driven.

You can use Execution Properties to control whether the report executes on demand or on a schedule. You can set Execution Properties as one of the specifications described below.

Standard Subscriptions
Standard subscriptions are created and managed by individual users. This model is used to deliver data to a set of pre-defined users. A standard subscription consists of static values that cannot be varied during subscription processing. For each standard subscription, there is exactly one set of report presentation options, delivery options, and report parameters.

Data-Driven Subscriptions
Data-driven subscriptions can be used if you have a larger recipient list. In this model, you can customize a report to a set of users who are specified by a query. For this, you need to define a report with a query to vary report output for each recipient as required by them. This query is performed against a database or another data store that contains subscriber data. Data-driven subscriptions are dynamic as the values used for generating the reports are retrieved at run time from a data source.

Managing Reports:Scheduling Report Subscriptions and Processing

SSRS supports the following report-management tasks:

Delivery of reports in a standard or data-driven subscription.

Generation of report history so that new snapshots can be added to the report history at regular intervals.

Refreshing the data in a report execution snapshot.

Expiration of a cached report so that it can be subsequently refreshed.

To implement these tasks, you can use two types of schedules:

Report-specific schedules: Defined for an individual report. These schedules are created inline when you define a subscription or set report execution properties.

Shared schedules: Defined for multiple reports. These schedules are created separately, and then referenced in a subscription or property page when you need to specify schedule information.

You can create and manage shared schedules by using the Object Explorer in SQL Server Management Studio.

To create a shared schedule, you need to perform the following steps:

In Object Explorer, expand a report server node, right-click the Shared Schedules folder, and then click New Schedule.

On the General page of the New Shared Schedule dialog box, specify the name and the details of the shared schedule.

To modify an existing shared schedule, expand the Shared Schedules folder, right-click the schedule you want to modify, and then click Properties.

You can also use Report Manager to manage shared schedules. To do so, on the global toolbar, click Site Settings. In the Other section on the displayed page, click Manage shared schedules.

Report Manager also provides an option to pause and resume shared schedules that are in the scheduling queue. However, you cannot pause and resume a running report. When a shared schedule is paused, all report processing associated with the schedule is deferred until the schedule is resumed.

Managing Reports:Configuring Security Settings

SSRS provides security settings for the reports hosted in the report server. When users request for reports, the report server checks the user credentials and authenticates the user. However, when the report is generated offline and delivered by mail or as a file based on a subscription, the credentials are retrieved from the subscription configuration on the report server.

You can configure Internet Information Services (IIS) and Windows authentication for accessing reports from the report server. You can additionally secure IIS by using SSL or certificates. You can also configure Kerberos protocol authentication for multiple credential handshakes in the process of requesting reports and validating permissions.

In the report server, users are assigned to roles and the roles are assigned permissions on folders. All items inside a folder derive permissions from the folder. The subfolders or reports can choose to override the folder-level security settings. Even if users are authenticated by the Kerberos protocol, they have to be authorized by the report server to access a report.

In Report Manager, to add a user or group to one of the roles, click Site Settings on the global toolbar. In the Security section, click Configure site-wide security, and then click New Role Assignment to add a new user or group.

Click to view and print the table that explains the default roles and permissions in the report server.
When a new system role is assigned, the users or group accounts that you specify in the role assignment are domain accounts. The report server references users and groups from a Microsoft Windows domain, but does not create or manage them. The authentication process is handled outside the report server when a new system role is assigned. You can customize the permission sets for all new roles in Report Manager except predefined system roles.

There are two kinds of roles for report configuration, system-level roles and item-level roles. The system-level roles specify the system-level permissions of users such as modifying system role assignments and definitions, system properties, and shared schedules. The item-level roles specify the folder-level permissions of users such as viewing and managing folders, and creating reports. The default roles and permissions of a report server are displayed in the following table.

Roles Permissions

Browser
To view folders, reports, and resources.

To manage individual subscriptions.

Content Manager
To view folders, data sources, reports, and resources.

To manage all subscriptions, data sources, folders, individual subscriptions, report history, reports, and resources.

To set security for individual items.

To create linked reports.

My Reports
To view folders, data sources, reports, and resources.

Manage data sources, folders, individual subscriptions, report history, reports, and resources in My Reports Folder.

Create and publish reports linked reports.

Publisher
Manage data sources, folders, reports, and resources.

Create and publish reports and linked reports to the report server.

System Administrator
To view and modify system role assignments, system role definitions, system properties, and shared schedules.

System User
To view system properties, and shared schedules.

Managing Reports:Rendering and Viewing Reports

Report Definition Language (RDL) is an open schema that specifies how reports should be populated, executed, and rendered. You can create an RDL file by using Visual Studio Report Designer.

Report server uses rendering extensions for exporting reports to various formats. A rendering extension is a component or module of a report server that transforms the report data and layout into a device-specific format. SSRS includes six rendering extensions namely, HTML, XLS, CSV or text, XML, Image, and PDF. The HTML rendering extension renders a report in HTML 4.0 for versions 5.5 and 6 of Microsoft Internet Explorer. SSRS can also render reports in MIME Encapsulation of Aggregate HTML (MHTML). MHTML extends HTML to embed encoded objects, such as images, in the HTML document. In addition to these rendering extensions, you can create rendering extensions to export reports in other formats.

After rendering reports, you can use a report server to view them in three ways.

Click to view and print the table that explains the three ways to view a published report.
SSRS provides an inbuilt mechanism to maintain historical snapshot copies of reports on the report server. The report server execution log contains information about the reports that execute on the server. You can use this log to find out how often a report has been requested, which formats are used most frequently, and what percentage of processing time is spent on each processing phase. You can turn on or off the report execution-logging feature by using the options on the Site Settings page of Report Manager. By using this page, you can also specify the duration for which you want to keep the log entries.

Rendering and Viewing Reports

Print this page.The following table explains the three ways of viewing a published report.

Tool Description
Browser
A tool that helps you to view a report through a direct connection to the report server. To view a report, you can browse the folder hierarchy of the report server and select the report. However, the print functionality of a browser is not ideal for printing reports. To improve the print quality of the reports and to print multiple pages, you can use the client-side print functionality provided by SSRS.

Report Manager
A Web-based tool that includes features for viewing and managing reports. The easiest way to select and run a report by using Report Manager is to open the application and then search for the report. You can also navigate to the report that you want to view, select it to execute and render it.

Alternatively, you can execute the report by typing the URL http://< servername>/ReportServer?< reportfolder>/< reportname>.

Microsoft SharePoint® Web Parts
A tool that provides a Reporting Services Web part that can be configured to render a report on demand to any portal hosted by the SharePoint Portal Server. This report can be saved to any format, such as XLS and PDF, and stored on a file share. This file share serves as an offline repository for viewing older copies of the report.

—————————————————————————————

Using Report Model Designer:An Overview of Report Models

A report model is a business description of an underlying database. It describes data in terms of entities, attributes, and relationships or roles. These terms can then be used by end users to build ad hoc reports in Report Builder.

Report models provide familiar business names for database fields and tables, logically grouped model items, and predefined relationships between items within the data source. Therefore, the model allows the end user to comprehend the data without needing to understand the underlying data structure.

In technical terms, a report model is the metadata description of a data source, and describes the business relationships between the objects in a data source view. Therefore, a report model is one step removed from a data source view.

Before you build a report model, you must first create a report model project in the Business Intelligence Development Studio. A report model project is the development environment that is used to build a report model.

You can build your report model in two ways:

Launch Report Model Designer and start designing a model by using a data source.

Automatically generate a model by using a predefined set of rules.

If you are using Report Model Designer, you need to first create a data source and a data source view, and then generate the report model. Model Designer can only generate report model projects from SQL Server databases. Report models are published by Report Model Designer to the report server.

You can secure model items by setting properties and permissions to determine how model items behave in Report Builder. After the model is published, the end users must have the permissions to access the model in order to use it in Report Builder. In SQL Server Management Studio, you can do this by assigning role-based security permissions.

Using Report Model Designer:Creating a Report Model Project

You can create a report model by using Report Model Designer, which is part of the Business Intelligence Development Studio. Before you create a report model, you first need to create a report model project.

To create a report model project in Report Model Designer, you need to perform the following steps:

Click File, point to New, and then click Project.

In the Project Types list, click Business Intelligence Projects.

In the Templates list, click Report Model Project. Specify the name and location for the report model project.

A report model project consists of one or more data source (.ds) files, one or more data source view (.dsv) files, and one or more report model (.smdl) files. However, you can reference only one data source and data source view in an .smdl file. Therefore, before creating the report model, you need to create the data sources and the data source views.

You can create data source views by using the Data Source View Wizard. This wizard provides you the option of selecting related tables automatically. For example, if the data store had relationships across various tables, the data source view will inherit the same relationships when it is created. Also, you can relate tables in the data source view even if they were not related in the data store.

The data source view is the bridge between the database layer and the report model layer. After the data source view is created, you can modify the data source view by using the Data Source View Designer. You can view the table and column properties of the data source view by using the Table Organizer window.

Using Report Model Designer:Entities, Source Fields, and Expressions

Report Model Designer provides several logical collections of objects that map or extends the items in a database. These object collections include entities, attributes, and their relationships within a database. An entity is an object that maps to a table or view in the Data Source view. An entity contains attributes that describe the entity. Entities can map to multiple database items, but can bind to only a single database. For example, CustomerInformation can be an entity in a Sales database. The following table shows the types of attributes that describe an entity.

Click an item to view more information.
Attribute Type Description
Source FieldA source field is an attribute that maps to a column in a table. For example, FirstName and LastName are attributes that map to columns of a Customer table. After binding an entity, you can add a source field to a table.

Expression

An expression is an attribute that is derived from other attributes. An expression can contain functions, operators, and constants. For example, an expression FullName can be a concatenation of attributes FirstName and LastName. After binding an entity, you can add an expression to a table. You can also customize an expression by using Visual Basic .NET and write functions that use the full range of .NET Framework classes and referenced assemblies.
A source field is an attribute that maps to a column in a table. For example, FirstName and LastName are attributes that map to columns of a Customer table. After binding an entity, you can add a source field to a table.

Using Report Model Designer:Roles, Folders, and Perspectives

Entities are a collection of attributes, roles, and folders. An entity definition can be enhanced by using roles, folders, and perspectives.

Click an item to view more information.
Category Description
RoleA role defines the relationship between entities. The relationship type can be one-to-one or one-to-many. For example, in a company, an employee belongs to a department and a department can have multiple employees. To create a role, you can select the entities that form the relationship and then select the attributes that define the relationship. In addition, you need to map these attributes to the relationship type.

FolderA folder is a logical group of entities. A folder may contain additional definitions, such as perspectives or other folders. By using folders, you can provide a hierarchical definition for entities. You can view a folder hierarchy in Report Designer in the same way as in Windows Explorer.

Perspective

A perspective is a constrained view of a model. Unlike the complexity of a model, a perspective provides a simpler view of the model for increased usability. For example, a company can have a model that contains the information about the entire company but maintains separate perspectives for each of the departments in the company, such as sales, finance, and inventory.

Perspectives are also a useful way to secure the data. Because perspectives are subsets of the data, users can be given permissions to the perspectives and not to the underlying model. This ensures that the users view only a part of the data.
A role defines the relationship between entities. The relationship type can be one-to-one or one-to-many. For example, in a company, an employee belongs to a department and a department can have multiple employees. To create a role, you can select the entities that form the relationship and then select the attributes that define the relationship. In addition, you need to map these attributes to the relationship type.

Transcript
This demonstration shows how to create a report model by using Business Intelligence Development Studio. The first step in creating a report model is to create a report model project. In this demonstration, you will create a report model project, report model demo.

Next, create a new data source by using the Data Source Wizard. On the Welcome page of the Wizard, click Next. This demonstration uses an existing connection, AdventureWorksDW. On the Select how to define the connection page, verify whether AdventureWorksDW is selected in the Data connections list and click Next. On the Complete the Wizard page, verify whether the data source name is Adventure Works DW and click Finish. Notice that new data source is added to the Data Sources folder.

The next step is to create a data source view by using the Data Source View Wizard. The report model will be based on this data source view. In the Relational data sources list of the page, ensure that the AdventureworksDW data source is selected and click Next. Then, select all the tables from the Available Objects list and add them to the Included Objects list. Verify whether the name of view is AdventureworksDW and click Finish. Notice that new data source view is added to the Data Source Views folder.

Next, create a new report model by using the Report Model Wizard. In the Available Data Source Views list, verify whether Adventure works DW is selected and click Next. On the Select report model generation rules page, you need to select one or more rules that are used to generate the metadata of the model from the data source. Notice that some rules are selected by default. You can also select a language for the report model. In this demonstration, click Next to accept the default report model generation rules specified in the wizard.

On the Update Statistics page, verify whether the Update Statistics Before Generating option is selected. This option will update statistics if the data source view has been modified or the data in the data source has changed significantly. Click Next to accept the defaults. The report model is generated. Click Finish to complete the wizard. Notice that the new report model is added to the Reports Models folder.

Finally, verify whether the name of the report model is Adventure Works DW and click Run.

Demonstrated procedure
Creating a Report Model

Start Business Intelligence Development Studio.

On the File menu, point to New, and then click Project. The New Project dialog box appears.

In the Visual Studio installed templates section, click Report Model Project.

In the Name box, change the project name to report model demo, and then click OK.

In Solution Explorer of the report model project, right-click Data Sources, and then click Add New Data Source. The Data Source Wizard appears.

On the Welcome to the Data Source Wizard page, click Next. The Select how to define the connection page appears.

On the Select how to define the connection page, click Next to accept the defaults. The Complete the Wizard page appears.

On the Complete the Wizard page, click Finish to add the data source.

In Solution Explorer, right-click Data Source Views, and then click Add New Data Source View. The Data Source View Wizard appears.

On the Welcome to Data Source View Wizard page, click Next. The Select a Data Source page appears.

On the Select a Data Source page, click Next to accept the defaults. The Select Tables and Views page appears.

On the Select Tables and Views page, click the >> button, and then click Next. The Completing the Wizard page appears.

On the Completing the Wizard page, click Finish to add the new data source view.

In Solution Explorer, right-click Report Models, and then click Add New Report Model. The Report Model Wizard appears.

On the Welcome to the Report Model Wizard page, click Next. The Select Data Source View page appears.

On the Select Data Source View page, click Next to accept the defaults. The Select report model generation rules page appears.

On the Select report model generation rules page, click Next to accept the defaults. The Update Statistics page appears.

On the Update Statistics page, click Next to accept the defaults. The Completing the Wizard page appears.

On the Completing the Wizard page, click Run.

Click Finish

Using Report Model Designer:Publishing a Report Model

You can use Report Builder to create ad hoc reports only after the report model has been published to the report server. You can publish the report to a production report server by using Report Designer.

To publish a report model from Visual Studio, you need to perform the following steps:

Select the report model.

On the Build menu, click Deploy Solution. Alternatively, right-click the report model, and then click Deploy.

Before publishing the report model, you need to specify the report server and the folder in the Properties dialog box of the report project.

The model definition for the report model is stored in an XML file, which is based on the Semantic Model Definition Language (SMDL). SMDL is based on the Unified Modeling Language (UML). SMDL contains the definitions for the entities, folders, perspectives, and roles. When a model is published, the definition for the data source is mapped to the SDML and the entire model definition is published to the report server.

Using Report Builder:An Overview of Report Builder

Lesson Introduction

A report model provides a user-friendly way of viewing the contents and the connection information of a database. To build a report model, you need to create a report model project by using Report Model Designer. A report model project is a container for the model and consists of a data source, a data source view, and one or more report model files. After you create the report model project, Report Builder can use the model to develop ad hoc reports.

Report Builder is a report authoring tool that you can use to create and design ad hoc reports. The advantage of using Report Builder is that you can create ad hoc reports without being familiar with the underlying database structures. Moreover, you are not required to know any complex programming languages for creating reports.

Report Builder provides report templates that contain pre-defined data regions to build table, matrix, and chart reports. To create these reports, you need to select a report template and then drag the fields to the design area. The data to be included in the reports can be manipulated by filtering, grouping and sorting, or by using formulas. The reporting model contains the information that is required by Report Builder to automatically generate the source query and retrieve the requested data. You can format the reports by applying custom colors or fonts. You can also preview, print, and publish the reports.

Report Builder is a ClickOnce WinForms application that can be accessed by using Report Manager for centralized management. The Reports created in Report Builder are published by using RDL. Therefore, you can also open and modify these reports in Report Designer.