________________________________________
Microsoft SQL Server 2008 Reporting Services provides a complete, server-based platform designed to support a wide variety of reporting needs enabling organizations to deliver relevant information where needed across the entire enterprise.
SQL Server 2008 Reporting Services provides a complete enterprise reporting solution that can support thousands of users by providing them with the reports that they need when and where they need them. These reports can bring together data from the many different data sources that exist within an organization, and deliver them in rich rendering formats to users across the organization and beyond. With Reporting Services, you can create interactive, tabular, graphical, or free-form reports from relational, multidimensional, or XML-based data sources. You can publish reports, schedule report processing, or access reports on-demand. It provides a unified, server-based, extensible, and scalable platform from which to deliver and present information. Its scope extends from traditional paper reports to web-based delivery and interactive content. SSRS can also be configured to deliver reports to peoples’ inboxes, file shares, and so on. SSRS is capable of generating reports in various formats, such as the web-oriented Hypertext Markup Language (HTML) and desktop application (Microsoft Excel and CSV) formats, thus allowing users to manipulate their data in whatever format is required. In addition, SharePoint can be used as a front end for SSRS, allowing reports to be presented directly in corporate portals.
Features
• Design reports with any structure by using the new unique layout capabilities of Flexible Report Layout
• Benefit from enhanced performance and scalability to reach all users throughout the enterprise
• Render reports into Microsoft Office Word format
• Integrate Reporting Services with Microsoft Office SharePoint Services for central delivery and management of business insight
• Create reports with richly formatted text
• Display data graphically with enhanced visualization capabilities
SSRS Tools
________________________________________
SSRS provides many tools but there are three main tools from the user perspective:
• Report Viewer: The primary mechanism for viewing reports over the Web. Report Manager is the name of the website that SSRS sets up. It provides a very clean and neatly organized user interface for end users. Developers can also embed a Report Viewer control into both ASP.NET and Windows Forms applications. It is a freely redistributable control that enables embedding reports in applications developed using the .NET Framework. Reports are designed with drag-and-drop simplicity using Report Designer included in Visual Studio 2008 (Standard edition and above.)
• Report Builder: The tool that provides users with a front end for ad hoc reporting against a SQL Server or Analysis Services database. Unlike most ad hoc reporting tools, users of Report Builder do not need to know Structured Query Language (SQL) or anything about joins or grouping to create reports.
• Report Designer: Tool that takes on the job of building advanced reports. Although Report Builder does a good job as an ad hoc reporting tool, Report Designer was made to tackle really advanced reports. Report Designer is a collection of design surfaces and graphical tools that are hosted within the Microsoft Visual Studio environment. Report Designer provides tabbed views for Layout and Preview that allow you to design a report interactively. You can add datasets to accommodate a new report design idea, or adjust report layout based on preview results. In addition to the Data, Layout, and Preview design surfaces, Report Designer provides query design tools, an Expression editor, and wizards to help you place images or step you through the process of creating a simple report. Reports are based on report definition (.rdl) files that you create in Report Designer. All of the features that you can add to a report are described by the Report Definition Language (RDL). While designing a report, you have the option of testing it locally before publishing it to a report server. When you preview a report, Report Designer can use the same processing and rendering extensions that the report server uses, ensuring that users see the report as it was intended when they run the report. When ready, use Report Designer to publish reports to a report server.
Editions of Reporting Services
________________________________________
SSRS comes in five editions, which mirror the editions of SQL Server and Visual Studio. These editions range from free starter editions to full-scale Enterprise editions.
Edition Quick Overview
Express Express Edition offers a lightweight edition of SSRS for developers who want to learn how to use SSRS.
Workgroup Workgroup is for use in small departmental organizations or branch offices. Should the need arise; Workgroup Edition can be upgraded to Standard or Enterprise editions.
Standard Standard Edition is for use in small- to medium-sized organizations or in a single-server environment. Standard Edition supports all the features of SSRS, except highly specialized data-driven subscriptions, and infinite drill down through Report Builder.
Enterprise Enterprise Edition is for use in large organizations with complex database and reporting needs. Enterprise Edition is fully functional, and supports scale-out functionality across a web farm.
Developer
Developer Edition is essentially the same as Enterprise Edition, but has different licensing requirements to make it easy for people to develop enterprise applications. Developer Edition is licensed per developer in development environments.
Report Lifecycle
________________________________________
The report life cycle consists of creation, management, processing, and delivery phases.
Report Definition: In the creation phase, you use an authoring tool to create a report definition. A report definition is a blueprint of a report. The report definition contains layout, connection, and query information about the report. To create a report, you create a report definition file using Report Designer, Report Builder 2.0, or Report Builder 1.0. You create reports on a client computer, separate from the report server
Report Management: After you complete a report definition, you publish it to a report server where it becomes a managed report. A managed report is any report that is saved in a report server and has properties and associated metadata that allow the report to be secured, scheduled, moved, renamed, linked to other reports, and deleted. One of the principal advantages of using Reporting Services is the ability to manage reports and related items such as folders, data source connections, and resources, from a central location. You can define security, set properties, and schedule operations. You can also create shared schedules and shared data sources and make them available for general use. To manage reports and the reporting environment, use Report Manager. Report management includes the following tasks:
• Organizing the reporting environment into folders to store collections of reports.
• Enabling features such as My Reports, report history, and e-mail report delivery.
• Securing access to folders and reports by assigning users and groups to roles.
• Building shared schedules and shared data sources that you want to make available for general use.
Both users and report server administrators can manage reports, but in different ways. Users can publish and manage reports in a personal workspace named My Reports. Report server administrators can manage the entire report server folder namespace.
Report Generation: During report processing, the report definition is processed to get the data, merge the data with report layout, and render the output into a format suitable for viewing. Report processing produces a generated report. Reports are processed either on demand when a user selects a report from the report server, or according to a schedule.
Report Consumption: Users can access a generated report in a variety of ways. When a report is accessed by a user, it is viewed through an application (for example, a Web browser or an application such as Microsoft Excel) routed to a delivery target such as an e-mail inbox, printer, or Web site.
Architecture Diagram
________________________________________
Reporting Services meets this challenge with a set of integrated, multitier components. Because Reporting Services is a Microsoft .NET-based platform that can use both a Web service and an Application programming interface (API). The Reporting Services architecture is multi-tiered with an application layer, server layer, and data layer. It is modular and scalable so a single installation can be distributed across multiple computers. The key components of Reporting services Architecture are Report Server, Report Manager, and Report Builder.
Reporting Services architecture diagram
Report Server
The report server is the main component of Reporting Services. The report server is implemented as a Microsoft Windows service and as a Web service that provides an optimized and parallel processing infrastructure for processing and rendering reports. It consists of a Web service and a Windows service. The Web service exposes a set of programmatic interfaces that client applications use to access report servers. The Windows service provides initialization, scheduling and delivery services, and server maintenance. Through its subcomponents, the report server processes report requests and makes reports available for on-demand access or scheduled distribution. Report server subcomponents include processors and extensions. 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. Report Server processes report requests and retrieves report properties, formatting information, and data. Report Server merges the formatting information with the data and renders the final report.
• Processors
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.
• Extensions
The report server supports custom 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.
• Security Extensions
Security extensions are used to authenticate and authorize users and groups to a report server. The default security extension is based on Windows authentication. You can also create a custom security extension to replace default security if your deployment model requires a different authentication approach (for example, if you require forms-based authentication for Internet or extranet deployment). Only one security extension can be used in a single Reporting Services installation. You can replace the default Windows authentication security extension, but you cannot use it alongside a custom security extension.
• Data Processing Extensions
Data Processing extensions are used to query a data source and return a flattened row set. Reporting Services uses different extensions to interact with different types of data sources. You can use the extensions that are included in Reporting Services, or you can develop your own extensions. Data processing extensions for SQL Server, Analysis Services, Oracle, OLE DB, and ODBC data sources are provided. Reporting Services can also use any ADO.NET data provider. Data processing extensions process query requests from the Report Processor component by performing the following tasks:
• Open a connection to a data source.
• Analyze a query and return a list of field names.
• Run a query against the data source and return a rowset.
• Pass parameters to a query, if required.
• Iterate through the rowset and retrieve data.
Some extensions can also perform the following tasks:
• Analyze a query and return a list of parameter names used in the query.
• Analyze a query and return the list of fields used for grouping.
• Analyze a query and return the list of fields used for sorting.
• Provide a user name and password to connect to the data source.
• Pass parameters with multiple values to a query.
• Rendering Extensions
Rendering extensions transform data and layout information from the Report Processor into a device-specific format. Reporting Services includes six rendering extensions: HTML, Excel, CSV, XML, Image, and PDF.
• HTML Rendering Extension. When you request a report from a report server through a Web browser, the report server uses the HTML rendering extension to render the report. The HTML rendering extension generates all HTML using UTF-8 encoding. For more information, see Designing for HTML Output and Browser Support in Reporting Services.
• Excel Rendering Extension. The Excel rendering extension renders reports that can be viewed and modified in Microsoft Excel 97 or later. This rendering extension creates files in Binary Interchange File Format (BIFF). BIFF is the native file format for Excel data. Reports that are rendered in Microsoft Excel support all of the features available for any spreadsheet. For more information, see Designing for Microsoft Excel Output.
• CSV Rendering Extension. The Comma-Separated Value (CSV) rendering extension renders reports in comma-delimited plain text files, without any formatting. Users can then open these files with a spreadsheet application, such as Microsoft Excel, or any other program that reads text files. For more information, see Designing for CSV Output.
• XML Rendering Extension. The XML rendering extension renders reports in XML files. These XML files can then be stored or read by other programs. You can also use an XSLT transformation to turn the report into another XML schema for use by another application. The XML generated by the XML rendering extension is UTF-8 encoded. For more information, see Designing for XML Output.
• Image Rendering Extension. The Image rendering extension renders reports to bitmaps or metafiles. The extension can render reports in the following formats: BMP, EMF, GIF, JPEG, PNG, TIFF, and WMF. By default, the image is rendered in TIFF format, which can be displayed with the default image viewer of your operating system (for example, Windows Picture and Fax Viewer). You can send the image to a printer from the viewer. Using the Image rendering extension to render reports ensures that the report looks the same on every client. (When a user views a report in HTML, the appearance of that report can vary depending on the version of the user's browser, the user's browser settings, and the fonts that are available.) The Image rendering extension renders the report on the server, so all users see the same image. Because the report is rendered on the server, all fonts that are used in the report must be installed on the server. For more information, see Designing for Image Output.
• PDF Rendering Extension. The PDF rendering extension renders reports in PDF files that can be opened and viewed with Adobe Acrobat 6.0 or later. For more information, see Designing for PDF Output.
• Report Processing Extensions
Report processing extensions can be added to provide custom report processing for report items that are not included with Reporting Services. By default, a report server can process tables, charts, matrices, lists, text boxes, images, and all of the other report items described in Working with Report Items. If you want to add special features to a report that require custom processing during report execution (for example, if you want to embed a Microsoft MapPoint map), you can create a report processing extension to do so.
• Delivery Extensions
Scheduling and Delivery Processor uses delivery extensions to deliver reports to various locations. Reporting Services includes an e-mail delivery extension and a file share delivery extension. The e-mail delivery extension sends an e-mail message through Simple Mail Transport Protocol (SMTP) that includes either the report itself or a URL link to the report. Short notices without the URL link or report can also be sent to pagers, phones, or other devices. The file share delivery extension saves reports to a shared folder on your network. You can specify a location, rendering format, and file name, and overwrite options for the file you create. You can use file share delivery for archiving rendered reports and as part of a strategy for working with very large reports. Delivery extensions work in conjunction with subscriptions. When a user creates a subscription, the user chooses one of the available delivery extensions to determine how the report is delivered.
Report Manager
Report Manager is a Web-based report access and management tool that provides a user interface to a single report server instance and you access through Microsoft Internet Explorer. The user interface consists of Web pages and controls. There are pages for viewing items, setting properties, and creating and modifying subscriptions, schedules, shared data sources, and roles.
You can use Report Manager to perform the following tasks:
• View, search, and subscribe to reports.
• Create, secure, and maintain the folder hierarchy to organize items on the server.
• Configure site properties and defaults. You can also determine the availability of My Reports to support publishing and creating reports in a personal workspace.
• 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.
• 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.
The ability to perform a task in Report Manager depends on user role assignments. 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. 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.
Report Builder
Report Builder is a report authoring tool that you can use to design ad hoc reports using data sources, sometimes called report models, and templates. You can publish your reports to a report server or export them as a different file type, such as a TIFF, PDF, HTML, or Excel file. Using Report Builder, users can interactively explore the related data within the report model. Click through reports are automatically generated so that report viewers can follow the report model's navigation paths to explore the data. When clicking through the data, queries are generated automatically by passing information about which data that the user is using to create their report, typically referred to as the context of the current data location.
Report Builder is a Click Once Win Forms application that is accessed from the report server for easy centralized management. Report Builder reports are published using Report Definition Language (RDL), which allows users to take advantage of Reporting Service’s full capabilities. Because Report Builder reports are saved as RDL, they can be opened and modified using the advanced programming capabilities in Report Designer. Report Builder reports are managed, secured, and delivered using the same methods and APIs used to manage, secure, and deliver Report Designer reports.
Report Builder can be accessed through a URL or from Report Manager, a component of Reporting Services that enables users to publish and view reports on a report server. Users need assigned permission to access Report Builder. In the role-based security model that is implemented for Reporting Services, users who are assigned to the Content Manager role can create and edit reports in Report Builder. Local administrators are automatically assigned to this role; you can create a custom role definition. As long as the customized role includes the "Consume reports" task, users who are assigned to that role will have sufficient permission to create and modify reports using Report Builder.
Quick Overview
• Report Server is the core engine that drives Reporting Services.
• Report Manager is a Web-based administrative interface for Reporting Services.
• Report Designer is a developer tool for building complex reports.
• Report Builder is a simplified end-user tool for building reports.
• The Report Server database stores report definitions. Reports themselves can make use of data from many different data sources.
Report Model
A report model is a semantic layer of metadata stored in the SSRS database, describing the data from a business point of view, adding many additional properties that enable the quick and easy authoring of reports. Of course, a report model is not something an end-user would prepare.
I can create a report model in BIDS using the Report Model Project template. I have to start with a data source. In the Solution Explorer window of the BIDS, you will see an additional folder called Data Source Views. A data source view is metadata from the source database, transferred to the development machine, and stored in an XML format. They enable disconnected development. Authoring a report model can take a long time, and it would not be acceptable to work in a connected environment, connected to the production server. A data source view is a development-time object only; it is not a part of the deployment. When the data source and the data source view are prepared, I can start the Report Model wizard by right-clicking on the Models folder in the Solution Explorer window. The wizard can create many metadata elements automatically, including entities from tables, attributes from columns, roles for associations between tables (foreign keys), date attributes variations (year, quarter, month, and so on) from date time columns, different aggregations for numeric columns and much more, as shown in Screenshot 3.
I accept all the wizard defaults and, after the wizard finishes its work, I come to the Semantic Model Designer window. Here I can add additional properties, such as conceptual names and format strings. I changed the expression. You can see the Semantic Model Designer with expanded date element variations and numeric aggregates in Screenshot When the model is completed, you can deploy it to your report server just as you would deploy a report, and now your analysts can start authoring their own reports, using the Report Builder. If a user has permissions to use the Report Builder, they can access the tool from Report Manager. With Report Builder, authoring a report is a reasonably simple and straightforward process.
Report Definition Language
Report Definition Language (RDL) is an XML-based schema for defining reports. A report definition contains data retrieval and layout information for a report. Report Definition Language (RDL) is an XML representation of this report definition.The schema defines:
• Report layout– the body of the RDL file defines all of the objects that will be displayed in the report, including fields, images and tables
• Each dataset, the data source for each dataset and database connection information (where no data source is used)
• A set of fields in each dataset that can be populated with data
• Any parameters that are used in the report
For Example
Document element
Let's first take a look at the document element:
The document element is called Report – no surprise there. It references two XML namespaces:
DataSources
The first element of the RDL file, below the namepsace reference is DataSources. In this element you can define a data source, or multiple DataSources, for your reports:
Catalog=ReportingDemo
Okay, so now you have a good idea of the RDL that is written behind the scenes when the SQL Report Designer is doing its thing.
Creation of Project in SSRS
________________________________________
Step 1
(Click on Start button Programs-->Microsoft SQL Server 2008 --> SQL Server Business Intelligence Development Studio)
Step 2
(File -->New--> Project)
Step 3
(Give Project name, Solution name and Directory name)
Creation of Shared DataSource
________________________________________
Step 1
In Solution Explorer right Click the shared DataSource Folder and add new datasource
Step 2
Shared DataSource Properties window will open. And then specify the name, Type & Connection String.
Step3:
Click the Edit button to bring up the Connection Properties dialog:
Creation of Reports
________________________________________
Step 1
In Solution Explorer right Click the Reports folder and click add new items
Step 2
Add new item window will appear in that we can create report through wizard or manually.
Step 3:
Report is added in your project see in solution explorer.
Creation of Reports through Wizard
________________________________________
Step 1
In Solution Explorer right Click the Reports folder and click add new report
Step 2
Reports Wizard will appear follow the steps to create a report
Step 3
Select the data source to retrieve data
Step 4
Design a query to execute against DataSource
Step 5
Select type of the report that you want to create.
Step 6
Group the data in table
Step 7
Specify the basic layout of report
Step 8
Specify the formatting of reports
Step 9
Completion of Report and specify the Report name
Report Items
________________________________________
The Toolbox window contains all the report items available. Report items can be data regions, graphical elements, and free-standing text. You can choose from six data regions: table, matrix, rectangle, list, sub report, and chart. Graphical elements you can add include a line and an image.
Textbox: It Displays labels, Fields or values calculated from expression.
Table: Displays data in a grid that has a fixed number of columns and variables numbers of rows. A table is a data region that presents data row by row. Table columns are static. Table rows expand downwards to accommodate the data. You can add groups to tables, which organize data by selected fields or expressions.
Matrix: Displays aggregated data in a grid that has a fixed number of columns and variables numbers of rows. 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 columns and rows and static columns and rows. Columns or rows can contain other columns or rows, and can be used to group data.
List: Displays a set of report items that is repeated for each group or rows of data. A list is a data region that presents data 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 the list.
Image: Displays Bitmap image such as logo or photos.
Subreport: Displays embedded report within the current report.
Charts: displays data graphically as bars, pies and other chart types. A chart presents data graphically. Examples of charts include bar, pie, and line charts, but many more styles are supported.
Gauge: Displays’ a value, Fields or expression as a linear or radical gauge. A gauge presents data as a range with an indicator pointing to a specific value within the range. Gauges are used to display key performance indicators (KPIs) and other metrics.
Adding items to report
1. Drag and drop the selected item from toolbox to your report
2. Right click the report in your design tab and select insert to add your items to your report
Report Data Window
________________________________________
Report data window contains built-in-fields as seen above in figure and parameter folder to which we can add and remove parameters to report level , image folder that contains list of image files which is been uploaded and Datasource folder which contains the detail about dataset or about data. From here we can drag and drop the fields into our report items.
Working with Datasets
A report definition can include one or more datasets. Each dataset is essentially a query used to gather data for the report, but it also contains a pointer to the data source.
In Report Data right click the Datasource and add dataset
Dataset properties window will appear. In Query tab specify the name of dataset, select the datasource and type of query we need to retrieve the data.
Working with Tablix Data Region
Use the Tablix data region to display fields from a dataset either as detail data or as grouped data in a grid or free-form layout. Reporting Services provides three templates for a Tablix data region that you can add directly from the Toolbox:
Table. Use a table to display detail data, organize the data in row groups, or both. The Table template contains three columns with a table header row and a details row for data. The following figure shows the initial table template, selected on the design surface:
Add a table to the design surface from the Toolbox. By default, a new table has a fixed number of columns with a header row for labels and a data row for detail data. The following figure shows a new table added to the design surface.
When you select the table, row and column handles appear on the outside of the table and brackets appear inside cells. Row handles display graphics that help you understand the purpose of each row. Brackets indicate group membership for a selected cell. The following figure shows a selected empty cell in a default table.
The row handle for the Data row shows the details symbol ( ). To display data on these rows, drag fields from the Report Data pane to the table cells in either the header or the details row. Both rows are filled in simultaneously. To add additional columns, drag the field to the table until you see an insertion point. After you add dataset fields to the table, you can change the default format for dates and currency to control the way they display in the report. The following diagram shows a table data region with these fields: Date, Order, Product, Qty, and Line Total.
Check your design by viewing the report in Preview. The table expands down the page as needed. The label row and the details row each display once for every row in the dataset query result set.
Each product sold in the order is listed on a separate row, along with the quantity and the line total for the item, as shown in the following figure:
Working with Matrix Data Region
Use a matrix to display grouped data and summary information. You can group data by multiple fields or expressions in row and column groups. Matrixes provide functionality similar to crosstabs and pivot tables. At run time, as the report data and data regions are combined, a matrix grows horizontally and vertically on the page. You can format the rows and columns to highlight the data you want to emphasize
Add a matrix to the design surface from the Toolbox. The matrix initially has a row group, a column group, a corner cell, and a data cell, as shown in the following figure.
When you select a matrix on the design surface, row and column handles appear, as shown in the following figure.
Add groups by dragging dataset fields to the Row Groups and Column Groups areas of the Grouping pane. The first field that you drag to the row groups or column groups’ pane replaces the initial empty default group. You can then apply formatting for each cell, depending on the data.
In Preview, the matrix expands to show the row group and column group values. The cells display summary values, as shown in the following figure.
The matrix you start with is a template based on the Tablix data region. You can continue to develop your matrix design by adding nested or adjacent row groups or column groups, or even adding detail rows.
In this example, the query has filtered data values to only include those values for Europe and for the years 2003 and 2004. However, you can set filters on each group independently.
To add a total column for an adjacent column group, click in the column group definition cell and use the Add Total command. A new static column is added next to the column group, with a default aggregate sum for every numeric field in the existing rows.
Working with List
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. 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. It Displays 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. Remove unwanted white space by adjusting the size of the cell.
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:
The dotted lines displays in these figures are included to show the free-form layout for each field value. Typically, you would not use dotted lines in a production report.
Working with Gauge Data Region
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.
Reporting Services provides two types of gauges: radial and linear. After you add a gauge to the design surface, simply drag a dataset field to the gauge and the field will appear in the data drop-zone. By default, when a field is added to the gauge, the field is aggregated, and the resulting value is attached to the pointer via the Value property. By default, the gauge aggregates all of the values in your field into one value that is displayed on the gauge. You can add grouping to the gauge in order to view individual groups or individual rows on the gauge. When grouping and filtering is applied, the gauge uses the pointer value to display the last group or row in the returned dataset.
You can add multiple values to an individual gauge by adding another pointer. This pointer can belong to the same scale, or you can add another scale and then associate the pointer with that scale. Following is the window appears when you drag and drop the gauge item from toolbox.
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.
After data is added, when you right-click on the pointer, you will get Clear Pointer Value and Delete Pointer options. The Clear Pointer Value option will remove the field attached to the gauge, but the pointer will still appear on the gauge. The Delete Pointer option will remove the field from the gauge and delete the pointer from view. If you re-add a field to the gauge, the default pointer will reappear. After you have added the field to the gauge, you must set the maximum and minimum values on the corresponding scale in order to give context to the value on the gauge. You also have the option of setting the minimum and maximum values on a range, which shows a critical area on the scale. The gauge will not automatically set the minimum or maximum values on the scale or the range because it cannot determine how the value should be perceived.
Working with Sub Report
A sub-report is a report item that displays another report inside the body of a main report. Conceptually, a sub-report is similar to a frame in a Web page. It is used to embed a report within a report. Any report can be used as a sub-report. The report that the sub-report displays is stored on a report server, usually in the same folder as the parent report. You can design the parent report to pass parameters to the sub-report. A sub-report can be repeated within data regions, using a parameter to filter data in each instance of the sub-report.
You can place a sub-report in the main body of the report, or in a data region. If you place a sub-report in a data region, the sub-report will repeat with each instance of the group or row in the data region. To pass a value from the group or row to the sub-report, in the sub-report value property, use a field expression for the field containing the value you want to pass to the sub-report parameter.
A sub-report is a report that is embedded into another report. Sub-reports can take parameters and execute their own datasets. A key aspect to note is that a sub-report in SSRS is actually just another report (unlike some reporting tools, where a sub-report is a special construct). In fact, in SSRS you can execute a sub-report on its own.
To add a sub-report to a report, you simply drag a sub-report control onto the report and tell it which report to display. If the sub-report requires parameters, you have to tell the main report which value to pass to the sub-report. It's actually very simple.
Let's add a new report to the project and call it MainReport.rdl. Create a new dataset using the shared datasource and the query:
SELECT CustomerID, FirstName, LastName FROM Customer
Switch to the Layout tab. Drag a table on the report detail area. Set up the first column to display the customer's first name (by dragging that column from the Datasets tab into the Detail row) and set up the second column to display the customer's last name. Label the third column 'Address'. Preview the report, just to be sure it works.
Create another report, and call this one MySubReport.rdl. This time, create a dataset that uses the shared data source, and use the following query text:
SELECT Address, City, State, ZipCode
FROM Customer
WHERE (CustomerID = @CustomerID)
In the Layout tab, use text boxes to create the address layout. You can simply drag the text boxes onto the screen by clicking on the field in datasets tab and dragging it onto design surface. You will also note that when you do this, the expression used to set the value property for the textbox uses the First() function. This function will use the value from the first row returned by the dataset. All other rows returned are ignored.
Now preview the report and use '100' for the CustomerID parameter.
Let's jump back to the MainReport.rdl. To embed the sub-report, drag a Sub-Report control into the detail cell for the column you labeled 'Address'. Right-click on the Sub-Report control and select Properties. In the Properties dialog choose MySubReport from the sub-report dropdown.
Next, switch to the Parameters tab. This is where you connect your subreport to the main report. You do this by indicating which value from the main report is to be passed to the subreport to fulfill its parameter requirements.
In the Parameter Name column choose CustomerID and in the Parameter Value column choose =Fields!CustomerID.Value. This will wire up the subreport to whichever customer is being displayed in the row of the table.
Click OK to close the dialog, and then preview the main report.
Working with Charts
When you want to summarize data in a visual format, use the Chart data region. Charts enable you to present large volumes of aggregated information at a glance. It is important to carefully prepare and understand your data before you create a chart, as this will help you design your charts quickly and efficiently.
The following illustration shows many of the different elements used in the chart.
Creating a basic chart
Start by creating a new report server project and add a shared data source that points to your database. Add a new report to the project. Set up a data set for the report using the Simple query.
On your new report, drag the open content area out so that we have room to work and then drag a chart control onto the content area. As soon you drag the chart items on to your report window will appear to gives you can choose the chart.
After selecting column type graph, It should look like this: From the Dataset fly-out window expand the dataset you created. Drag the field into the area of the chart and preview.
Tablix Properties
1. Right click the tablix and click properties
General: In general tab specifies the name, dataset and display options.
Visibility: In this tab specify the state of visibility based on expression as well.
Filters: Based upon expression and value filter data accordingly.
Sorting: sort the column based on column or by expression this is how we can achieve the dynamic sorting.
1. Select the table and press F4 then below property window will appear.
DataSetName: Specify the dataset name to use.
FixedCoulmnHeader: indicate whether the column header remain displayed on the page while page scrolls.
FixedRowHeader: indicate whether the row header remain displayed on the page while page scrolls.
KeepTogether: Indicates whether to keep all sections of the data region together on one page.
NoRowsMessage: specifies the message to display in the data region data is not available.
OmitBorderOnpageBreak: indicates whether borders should appear around report items that span multiple pages.
TextBox Properties
Padding: Specify the amount of padding between report items and boundary and its content.
Bordercolor: Specify the color of the entire border or the individual border lines of items.
CanGrow: Indicates whether the report item automatically increases in size to accommodate.
CanShrink: Indicates whether the report item automatically decreases in size to accommodate.
ToolTip: specifies the textual label for report items.
Adding Header & Footer to Report
Right click the report and select insert to add page header and footer. Footer and header will appear on each page. Within the footer and header, we will include:
• Identification of the system user printing the report;
• The report file title;
• Page number;
• Total number of pages in the report.
Header Properties
Right click the area of page header and select header properties then the header properties window will appear.
Insert PageNo to header and display months range.
Drag & Drop the textbox from toolbox and go to the textbox expression
For PageNo add particular expression
="PageNo :"&Globals!PageNumber & " To " &Globals!TotalPages
For displaying month
=IIF(Month(Parameters!StartDate.Value)=Month(Parameters!EndDate.Value),"For " &MonthName(Month(Parameters!StartDate.Value)),
"From "& MonthName(Month(Parameters!StartDate.Value)) &" To " &MonthName(Month(Parameters!EndDate.Value)))
Sample preview of the report:
Footer Properties
Right click the area of page Footer and select footer properties then the footer properties window will appear. You can select this expression from built in function.
Insert Username at footer
Drag & Drop the textbox from toolbox and go to the textbox expression and add particular expression for adding username. You can select this expression from built in function.
="User :"&User!UserID
Sample preview of the report:
Adding Totals
To add totals, select cells with numeric data, and then right click the selected cell add click to add total. The following figure shows a typical totals row that includes both automatic and manually specified totals:
Right click the selected cell add click to add total.
Right click the Detail tab in rows Groups window and add click to add total.
In Preview, the report displays the header row and the details row once for every row in the dataset query result set, and it displays the totals row. The follow figure shows the last few rows of the table including the total row.
Adding a Groups
Step 1:
Right click the detail region in rows group window and select to add group. You can add parents group to your table or child group to your table.
Step 2:
Specify the column you are grouping & also select the add group header
Concept: The Difference between Detail Data and Grouped Data
The difference between detail data and grouped data is a concept well understood by those of us who are familiar with tabular reporting in general. Detail data is all the data from a report dataset as it comes back from the data source. Detail data is essentially what we see in the Query Designer results pane when we run a dataset query. The actual detail data includes calculated fields that we create, and is restricted by filters set on the dataset, data region, and details group. We display detail data on a detail row by using a simple expression such as [Quantity]. When the report runs, the detail row repeats once for each row in the query results at run time. Grouped data is detail data that is organized by a value that we specify in the group definition, for example, [SalesOrder]. We display grouped data on group rows and columns by using simple expressions that aggregate the grouped data, for example, [Sum(Quantity)].
Concept: Row Groups and Column Groups
Groups are organized as members of group hierarchies. Row group and column group hierarchies are identical structures on different axes. We can think of row groups as expanding down the page and column groups as expanding across the page.
A tree structure represents nested row and column groups that have a parent/child relationship, for example, a category with subcategories (such as we see in the Adventure Works 2008 Product data). The parent group is the root of the tree and child groups are its branches. Groups can also have an independent, adjacent relationship, for example, sales by territory and sales by year. Multiple unrelated tree hierarchies are called a forest. In a Tablix data region, row groups and column groups are each represented as an independent forest.
Concept: Static and Dynamic Rows and Columns and How They Relate to Groups
A Tablix data region organizes cells in rows and columns that are associated with groups. Because group structures for row groups and columns are identical, we can adequately accomplish the consideration of both in a discussion of row groups. While we talk about row groups, we can apply the same concepts to column groups.
A row is either static or dynamic. A static row is not associated with a group. When the containing report runs, a static row renders once. Table headers and footers are examples of static rows. Static rows display labels and totals. Cells in a static row are scoped to the data region. A dynamic row is associated with one or more groups.
A dynamic row renders once for every unique group value for the innermost group. Cells in a dynamic row are scoped to the innermost row group and column group to which the cell belongs. Dynamic detail rows are associated with the details group that is automatically created when we add a table or list to the design surface. By definition, the Details group is the innermost group for a Tablix data region. Cells in detail rows display detail data. Dynamic group rows are created when we add a row group or column group to an existing Tablix data region. Cells in dynamic group rows display aggregated values for the default scope.
The Add Total feature automatically creates a row outside the current group on which to display values that are scoped to the group. We can also add static and dynamic rows manually. Visual indicators help us to understand which rows are static and which rows are dynamic.
Cascading Parameters
Adding parameters to reports enables users to provide specific values when the report is processed in order to filter the information represented in the report. For example, a user could provide a Region parameter value for a sales report to view only sales in the specified region. Parameters help report authors reach a wider audience with a single report, and empower users with a more flexible and interactive experience. Adding parameters to report based on some condition and display data according. But for each parameter we need have different datasets.
Before adding parameters to reports first create different dataset for different parameters.
For Country retrive the country data from table. i.e. ”Select Distinct Country from Employee”
For State based on country values of state will display so retrive data based on country i.e.
“Select Distinct State from Employee where Country IN(@Country)” here we use ‘@’ to pass the value of parameter.
For District based on state display district i.e. “Select Distinct District from Employee where State IN(@State)”
For city based on district display city i.e. “Select Distinct City from Employee where District IN(@District)”
Now create parameters add specify the particulat dataset for particular parameter.
Step 1:
To add parameter right click the parameter folder in report data window and add parameter.
Step 2:
Report parameter properties window appear. In general tab selects the name, display name and data type of the parameter.
In Available value tab specify the dataset you already created to retrieve the data from DS.
Then after preview particular report output is as follows:
Drill-through Report
Drill through allows reports to be linked together. It provides a method to simplify reports and enable users to get to more complex or detailed data when required.
Step 1:
First create sub report with parameter properties as follows. Specify the parameter visibility as hidden and marks allow null values.
Step 2:
In your main report when particular field is click then it will go to sub report above. So in your main report, right click the particular field and go to the textbox properties.
Then go to the action tab and specify the report do you want to redirect and specify the parameter and value if you want to view report based on Catergory Name.
Drill-Down (Toggle)
Drill down is the ability to expand reports to see additional detail.
Step 1:
Right click the detail section in rows group window and select group properties
Step 2:
Select Visibility tab and according to the group items toggle the rows and continued for group4 and till group1 for toggle. Here in visibility tab you can hide or show columns or particular group based on expression.
Step 3:
Due the fault in SSRS 2008 toggle image will come is reserve when you preview. So you need to change the image state.
First close your report then right click the particular report and go to view code. Particular report rdl code file will appear. In that code search the items to which you have included toggle. To change the state of the image add add the particular code which is highlighted in blue to your code. Add this code to particular columns you added toggle.
Step 4: save the file and close this file and preview your toggling. Below is the example of toggle image.
Interactive Sorting
Applying sort capabilities to a report enables users to sort the data by any of the columns the report contains in ascending or descending order. For example, a user viewing a report containing a list of sales might sort the data first by date, and then by sales amount.
Step 1:
Right click the column to which you want to add sorting and select text box properties.
Step 2:
Select the interactive sorting tab. If particular column you selected is in group then select particular group name and select the column which you need to sort. If that item is not involved in group then select detail rows and sort accordingly.
Then after preview particular report output is as follows for interactive sort:
Number & Date Formatting
If you need to display currency in the numerical fields or you need to display date in different format then select the particular column and select textbox properties and In Number tab select the particular format .
For currency format:
For Date format:
Displaying Header in each page
1. Right click the tablix and select properties tablix property window will appear. Here select the one option in row header and column header.
2. Go to the Row Group & Column Group window and click the arrow in last and select Advanced Mode.
3. After selection static columns are also appear in that window as below.
4. Select the Row Group Static column first static column and press F4
Then properties window will appear as below then change three properties i.e.
FixedData =True, KeepWithGroup=After, RepeatOnNewPage=True
5. Then after changing the properties now headers will be coming to next page and also will scrolling the page down the header will also scroll.
Document Map
Users can use a document map to navigate a report easily. The document map is displayed as a side pane in the report, and users can use the hierarchy of links it contains to move around the various sections of the report.
Select and right click the tab to which you want to create the Document Map. For Eg:
I want to add the Document Map according to the country. So In rows Groups window, right click the particular country group and go to the properties.
And In advance tab select the Document map accordingly.
Then after preview particular report output is as follows:
Highlighting particular column based on Expression
Select the particular column and press F4 then properties window will appear and then select background and click to expression. Expression dialogue box will appear add the following expression
=iif(RowNumber(Nothing) Mod 2,"Green","White")
Then after preview particular report output is as follows
Inserting Image
Add image to particular column or group of column you can merge cells and insert the particular image. Select the textbox properties and go to tab Fill. In this tab select the image source from where you are inserting your image.
Sample preview of the report:
Deploying Report
Now let’s deploy it to the report server. First we must set the report server url, Right-click on the solution in the Solution Explorer and click on Properties.
You will see the following dialog. Change the TargetReportFolder value to something you wish and provide the TargetServerURL as http://yourserver/reportserver (not just /report!) and Press save.
Now right-click on the solution again and select Deploy.
When it finishes open the url http://yourserver/report (not /reportserver!). You will see something like this. Go ahead and click on Test.Reports (or whatever you specified as TargetReportFolder) and then click on your test report. You will be greeted with the web version of your report.
Now your Boss will not want to go the web page to get that report, so let’s look at sending it to him per email automatically. First we need to change the security settings for connecting to the data source. In the report page, click on Properties then Data Sources tab. Change the “Connect using” to “Credentials stored securely in the report server” and specify an account that has access to the data. Also check “Use as Windows credentials”.
Exporting Report
Users need to be able to access and share reports in the format with which they are most familiar. Reporting Services 2008 supports rendering to most common file formats including HTML, PDF, CSV, XML, and Image (TIFF) and also offers complete new rendering in Microsoft Office Word as well as improved rendering in Microsoft Office Excel. Reports in Microsoft Office formats are fully editable, enabling users to create custom documents in based on these reports.
Go to the properties window and select Report and change the Interactivesize and pageSize properties.
Interactive Size: specifies the default size of pages when rendering the report in an interactive renderer.
Page Size: specify the width and height for report.
SSRS Standards
Layout and General Display
Header
The title of the report will be the only item in the header. If the report contains sub-reports, the header should dynamically change to display the correct title of the sub-report that is displayed.
Footer
Include the following expressions on every report as footers. They display useful and informative information.
Footer Item Expression Sample Output
Date and Time Printed / User ID ="Printed by " + User!UserID + " on " + DateTime.Now.ToString() Printed on 3/1/2006 3:16:30 PM
Execution Time ="Execution Time: " +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).TotalSeconds <> 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours & " hour(s), ", "") +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes & " minute(s), ", "") +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds & " second(s)", ""))
) Execution time: 1 minute, 10 seconds
Page x of y ="Page " + Globals!PageNumber.ToString() + " of " + Globals!TotalPages.ToString() Page 3 of 10
Colors
The primary colors for the report:
Generic Color Name Hexadecimal Code
White #FFFFFF
Black #000000
Dark Green #00B24C
Light Green #7CD62B
Fonts
Trebuchet MS is the appropriate standard font for all reports. Font sizes should be used as follows:
Item Font (Style, Family, Size, Weight)
Report Title Normal, Trebuchet MS, 14pt, Bold
Field Names (Table Header) Normal, Trebuchet MS, 8pt, Bold
Data Fields Normal, Trebuchet MS, 8pt, Normal
Aggregation Fields (Sum, Avg, etc.) Normal, Trebuchet MS, 8pt, Bold
Footer Fields Normal, Trebuchet MS, 8pt, Normal
Other font sizes should be chosen as appropriate to denote higher or lower importance in the overall report layout.
Hyperlinks
Hyperlinks should be underlined and blue for better user visibility and easier navigation.
Naming Conventions
Usage of “Report”
The word “Report” is redundant as part of the title of a report. It should not be included. For example, a report for Mileage and Drive Time may be called “Excessive Mileage Trending,” not “Excessive Mileage Trending Report.”
Filenames
Filenames will consist of a binomial nomenclature, the report number and the report name, both as listed in the Master Report Tracking Sheet. They will be separated by a dash (-). There will be no spaces in the file name. For example, the report that fulfills requirement 77 is report number 10.5, and its name is “RSR Call Summary Report.” Therefore, its filename would be “10.5-RSRCallSummaryReport.rdl”. All drill down reports will be start with (_) underscore and rest things will be remain same.
Report Titles
Reports should be titled according to their names in the Master Report Tracking Sheet. However, they can be changed to accommodate the requests of the appropriate business users.
Column/Row Headers
Use Normal, Trebuchet MS, 8pt, Bold, and White.
Header Cell Names
These should be defined in accordance with the header name in the report mockup that is signed off by the business.
DataSets
All datasets with start with ‘ds’ and name should be title case for example: Fiscalyear so it should be dsFiscalYear
Displaying Data
Format Numbers
Numbers should always be displayed in the appropriate local format. Use the following Format Codes as appropriate:
Data to be formatted: Proper Format Code Improper Format Code
Number N0 #,###
Currency C0 $#,###
Percentage P0 #%
The number following the letter as part of the code
Change the Subscription E-mail Subject
In the Subscription email, report's ExecutionTime should not be showed in Subject, because it ruins conversation threading in Outlook – they cannot be ordered by subject. The subject of the subscription should be exactly same as the report's name.
Optionally, the ExecutionTime can be included in the Comments Section if the user so desires.
