Understanding Pagination in Reporting Services

Understanding Pagination in Reporting Services
Pagination refers to the number of pages within a report and how report items are arranged on these pages. Pagination in Reporting Services varies depending on the rendering extension you use to view and deliver the report. When you run a report on the report server, the report uses the HTML renderer. HTML follows a specific set of pagination rules. If you export the same report to PDF, for example, the PDF renderer is used and a different set of rules are applied; therefore, the report paginates differently. To successfully design an easy-to-read report for your users that is optimized for the renderer that you plan to use to deliver your report, you need to understand the rules used to control pagination in Reporting Services.
This topic discusses the impact of the physical page size and the report layout on how hard page break renderers render the report.
The Report Body
The report body is a rectangular container displayed as white space on the design surface. It can grow or shrink to accommodate the report items contained within it. The report body does not reflect the physical page size and, in fact, the report body can grow beyond the boundaries of the physical page size to span multiple report pages. Some renderers, such as Microsoft Excel, Word, HTML and MHTML, render reports that grow or shrink depending on the contents of the page. Reports rendered in these formats are optimized for screen-based viewing, such as in a Web browser. These renderers add vertical page breaks when required.
You can format the report body so that there is a border color, border style and border width. You can also add a background color and background image.
The Physical Page
The physical page size is the paper size. The paper size that you specify for the report controls how the report is rendered. Reports rendered in hard page break formats insert page breaks horizontally and vertically based on the physical page size to provide an optimized reading experience when printed or viewed in a hard page break file format. Reports rendered in soft page break formats insert page breaks horizontally based on the physical size to provide an optimized reading experience when viewed in a Web browser.
By default, the page size is 8.5 x 11 inches but you can change this size by using the Report Properties, Page Setup dialog box or by changing the PageHeight and PageWidth properties in the Properties pane. The page size does not grow or shrink to accommodate the contents of the report body. If you want the report to appear on a single page, all the content within the report body must fit on the physical page. If it does not fit and you use the hard page break format, then the report will require additional pages. If the report body grows past the right edge of the physical page, then a page break is inserted horizontally. If the report body grows past the bottom edge of the physical page, then a page break is inserted vertically.
If you want to override the physical page size that is defined in the report, you can specify the physical page size using the Device Information settings for the specific renderer that you are using to export the report. For more information, see Reporting Services Device Information Settings.
Margins
Margins are drawn from the edge of the physical page dimensions inward to the specified margin setting. If a report item extends into the margin area, it is clipped so that the overlapping area is not rendered. If you specify margin sizes that cause the horizontal or vertical width of the page to equal zero, the margin settings default to zero. Margins are specified using the Report Properties, Page Setup dialog box or by changing the TopMargin, BottomMargin, LeftMargin and RightMargin properties in the Properties pane. If you want to override the margin size that is defined in the report, you can specify the margin size using the Device Information settings for the specific renderer that you are using to export the report.
The area of the physical page that remains after space is allocated for margins, column spacing, and the page header and footer, is called the usable page area. Margins are only applied when you render and print reports in hard page break renderer formats. The following image indicates the margin and usable page area of a physical page.

Newsletter-Style Columns
Your report can be divided into columns, such as columns in a newspaper, that are treated as logical pages rendered on the same physical page. They are arranged from left to right, top to bottom, and are separated by white space between each column. If the report is divided into more than one column, each physical page is divided vertically into columns, each of which is considered a logical page. For example, suppose you have two columns on a physical page. The content of your report fills the first column and then the second column. If the report does not fit entirely within the first two columns, the report fills the first column and then the second column on the next page. Columns continue to be filled, from left to right, top to bottom until all report items are rendered. If you specify column sizes that cause the horizontal width or vertical width to equal zero, the column spacing defaults to zero.
Columns are specified using the Report Properties, Page Setup dialog box or by changing the TopMargin, BottomMargin, LeftMargin and RightMargin properties in the Properties pane. If you want to use a margin size that is not defined, you can specify the margin size using the Device Information settings for the specific renderer that you are using to export the report. Columns are only applied when you render and print reports in PDF or Image formats. The following image indicates the usable page area of a page containing columns.

Report Design Basics

Report Design Basics
To create a basic report, you must specify which data you need for a report, how you want to organize it on the page, and how you want a user to view the report.
A report has a header, a body, and footer. A report also has a default page size with page headers and page footers. You can place report items such as images, text boxes, and lines in headers and footers. The body of the report contains the report data. You can place any type of report item in the body, including tables, matrices, lists, charts, and gauges. You link report data to report items on the design surface. When the report is processed, the report data and layout items are combined. When you view the report, the combined data and layout elements are sent to a report renderer. The renderer dynamically determines how much data fits on each page. Report parameters are used to specify the data to use in a report, connect related reports together, and vary report presentation.
With Reporting Services, you can place report items anywhere on a report; you are not limited to "bands" of data. You can place data regions with different sets of data side-by-side. Certain report items can also contain other report items. For more information, see Working with Data Regions and Adding Other Report Items.

Specifying Report Data

Reports use the following types of report data: a report dataset with a field collection (from a query that runs on a data source and returns data in rows and columns, where the column names provide the default field collection), built-in fields provided by Reporting Services, images that are used in the report, and report parameters. Report data is organized on the Report Data pane. Report data is linked to report layout elements through report expressions. Expressions can be written using built-in libraries or can access the full range of Visual Basic .NET Framework classes. For more information, see Connecting to Report Data and Working with Report Expressions.
When your dataset query includes variables, query parameters are automatically created for the dataset, and corresponding report parameters are automatically created for the report. When the user runs the report, they can type or select values that help customize the report data for their interests. For more information, see Using Parameters to Control Report Data.
Sample data for tutorials is available using the AdventureWorks OLTP database. For more information, see Reporting Services Samples.

Organizing Report Data on a Page
Reports use a variety of report items to organize data on a report page. Report items are available in the Toolbox. You drag report items to the report design surface and then drag report data from the Report Data pane to the report items on the design surface. The design surface is not WYSIWIG. The report items have an initial layout position that may change when the report is processed. The following list describes typical uses for different report items:
Textbox. Use for titles, date stamps, and report names. Placeholder text appears in text boxes where you have linked data and layout items. The placeholder text is display text for an underlying report expression. During report processing, report data is substituted for the report expressions and the calculated values replace the placeholder text.
Table, Matrix. Use to display tabular or crosstab data from a report dataset. Table and Matrix are templates of a Tablix data region and provide a starting grid layout for data from a report dataset.
Chart. Use to graphically display data from a report dataset. You can also nest a chart inside a Tablix data region.
Gauge. Use to present a visual image for a single value within a range of values.
List. Use to create a free-form layout like forms on a Web page. A List is a template of a Tablix data region and provides a starting layout for data from a report dataset.
Image. Use to add existing images to a report.
Subreport. Use as a placeholder another report. A subreport must be designed separately and published to the report server to be included in a parent report.
Line, Rectangle. Use lines and rectangles as graphical elements. A rectangle can also be used as a container for other report items. Use a rectangle to help control how report items like data regions appear on a report page when the report is rendered.
A typical report uses Tablix data regions. You drag fields from the Report Data pane and drop them on a Tablix cell for a data region, or to the Grouping pane to define groups for the selected data region.

Previewing or Exporting the Report

When you run a report using Preview, the report authoring client processes the report locally and displays the rendered report in HTML. Reporting Services extensions perform much of this work: a data processing extension is used to retrieve the data based on the type of data source and a rendering extension is used to provide report output based on the selected format. To see the report in other rendering formats, use the Export button on the toolbar. Different extensions can change the way data is processed and the report is rendered. For more information, see Understanding Rendering Behaviours.
When you publish (also known as deploy) a report to the report server, you must accept or change the default deployment properties. The report server processes and renders the report. Then you view the report using Report Manager by browsing to the report location on the report server, for example, http://localhost/reports/reportname.

Pagination
Pagination in a report is determined by the page size of the report and any page breaks placed on report items. Rendering extensions that support page size, such as Image and PDF, format the data in the report to fit within each page. Rendering extensions that do not support page size render all data between page breaks on a single page. Some extensions that do not support page size may employ soft page breaks. The HTML rendering extension does this. The position of soft page breaks are determined by the size of the page, but are not as exact as page breaks placed by rendering extensions that support page size. All rendering extensions that support page breaks on items will start a new page after each page break in the report. For more information about working with page size and page breaks in Report Designer, see Understanding Pagination in Reporting Services.

Improving the Report Design
You typically work back and forth between the Design and Preview tabs to design the report and then run the report to see the rendered result in the report viewer. Typical changes you might make to a report to improve its presentation or performance are:
Add report parameters to allow a user to change the report data. Report parameters that are linked to query parameters on the dataset properties can reduce the amount of data retrieved from a data source. If you supply default parameters for each report parameter, the report runs automatically on first use. You can also provide a list of valid values for a parameter, so the user can choose from values that are known to exist on the data source. For more information, see Adding Parameters to Your Report.
Generalize a query by adding query parameters. The syntax for queries that include variables depends on the type of data source you connect to. For more information, see Using Query Parameters with Specific Data Sources (Reporting Services) and Using Variables and Parameters (Database Engine).
Organize report pages to provide answers to specific questions at a glance. For more information, see Understanding Pagination in Reporting Services.
Add interactive sort buttons to data region columns. For more information, see Sorting Data in a Report.
Add a date stamp and report name to the report. For more information, see Using Built-in Field References in Expressions (Reporting Services).
Use visibility options to hide complexity, but allow a user to drill down into areas of interest. For more information, see Hiding Report Items Conditionally.
Provide a document map or a customized internal links table for a report for each of navigation. For more information, see Adding a Document Map or Adding Links to a Report.
Align chart groups with table groups to provide a way to visualize tabular data. For more information, see Linking Multiple Data Regions to the Same Dataset.
Provide conditional color or images to highlight data values. For more information, see Adding Conditional Formatting (Reporting Services).

How to Create Linked report ?

Linked report
Linked report is a report server item that provides an access point to an existing report. Conceptually, it is similar to a program shortcut that you use to run a program or open a file.
A linked report is derived from an existing report and retains the original's report definition. A linked report always inherits report layout and data source properties of the original report. All other properties and settings can be different from those of the original report, including security, parameters, location, subscriptions, and schedules.
You can create a linked report when you want to create additional versions of an existing report. For example, you could use a single regional sales report to create region-specific reports for all of your sales territories.
Although linked reports are typically based on parameterized reports, a parameterized report is not required. You can create linked reports whenever you want to deploy an existing report with different settings.
To create a linked report
1. In Report Manager, open the report that you want to link to, and then click the Properties tab. The General Properties page appears.
2. Click Create Linked Report.
3. Type a name for the new linked report. Optionally type a description.
4. To select a different folder for the report, click Change Location. Click the folder you want to use, or type the folder name in the Location box. Click OK. If you do not select a different folder, the linked report is created in the current folder (where the report it is based on is stored).
5. Click OK. The linked report opens.
A linked report's icon differs from other items managed by a report server. The following icon indicates a linked report:

Data Regions in SSRS

Data regions are report items that display the rows of data from report datasets. Data from datasets can be displayed in a table, matrix, list, chart, or gauge data region. Data regions expand as needed to display the data from the dataset. Table, Matrix, and List data regions are based on the Tablix data region. A Tablix data region supports multiple row and column groups and with static and dynamic rows and columns.

Table
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. For information about adding a table to a report, see Adding a Table (Reporting Services).

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 columns and rows and static columns and rows. Columns or rows can contain other columns or rows, and can be used to group data. For information about adding a matrix to a report, see Adding a Matrix (Reporting Services).

List
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. For information about adding a list to a report, see Adding a List (Reporting Services).

Chart
A chart presents data graphically. Examples of charts include bar, pie, and line charts, but many more styles are supported. For information about adding a chart to a report, see Working with Chart Data Regions.

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. Examples of gauges include linear and circular. For more information about adding a gauge to a report, see Working with Gauge Data Regions.

Nested Data Regions
You can nest data regions within other data regions. For example, if you want to create a sales record for each sales person in a database, 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 the list to show the employee's sales record. For more information, see Nesting Data Regions.
Multiple Data Regions Linked to the Same Dataset
You can link more than one data region to the same dataset to provide different views of the same data. For example, you can show the same data in a table and in a chart. You can author the report to provide interactive sort buttons on the table, so that when you sort the table, the chart is also automatically sorted. For more information, see Linking Multiple Data Regions to the Same Dataset.

Types of Reports

In Reporting Services, you can use reports in a variety of ways. This topic describes the terminology used to describe the various types of reports and the ways reports get created and used. A single report can have characteristics from more than one type; for example, snapshot reports can be parameterized, ad hoc reports incorporate clickthrough report functionality due to the report models upon which they are based, and subreports can be linked reports.
With Reporting Services, you can create the following types of reports:
Parameterized reports
Linked reports
Snapshot reports
Cached reports
Ad hoc reports
Clickthrough reports
Drilldown reports
Drillthrough reports
Subreports
Report Appearance, Processing, and Delivery Terminology
There are several ways to think about report type. You might think about it as the way data appears in the report. In Reporting Services, the appearance of data in a report depends on the type of data region you use; for example, tabular reports and chart reports use different data regions. For more information about how to display data, see Data Regions. Likewise, the functionality that is available in a report depends on the output format; for example, interactive features like drillthrough reports are available in Web-based export formats but not in all Image-based export formats. A report's final output format affects which features you can include in a report.
Parameterized Reports
A parameterized report uses input values to complete report or data processing. With a parameterized report, you can vary the output of a report based on values that are set when the report runs. Parameterized reports are frequently used for drillthrough reports, linked reports, and subreports, connecting and filtering reports with related data.
Using Parameters
Parameters are used in dataset queries to select report data, to filter the result set that the query returns, or to set layout properties used to display or hide parts of a report. You can also specify cascading parameters that populate a series of dependent, drop-down parameter lists. For example, a drop-down list of Region parameter values can be used to populate a drop-down list of City parameter values.
You can use parameters with linked reports by pairing a specific parameter with each linked report to change the outcome. For example, you can create a single regional sales report that shows the sales for all regions, and then use a parameter for each linked report to filter data for a particular region. Specific parameter values can be stored with the report so that users do not have to type values.
Not all parameters may be visible in the report at run time. A report author, report server administrator, or content manager can specify which values to use and then hide the input fields on the report.
Query Parameters and Report Parameters
Reporting Services supports two kinds of parameters: query parameters and report parameters. Query parameters are used during data processing to select or filter data. Query parameters are specified in the syntax of a data processing extension. If a query parameter is specified, a value must be provided either by the user or by default properties to complete the SELECT statement or stored procedure that retrieves data for a report. Report parameters are used during report processing to show a different aspect of the data. A report parameter is usually used to filter a large set of records, but it can have other uses depending on the queries and expressions used in the report. Report parameters differ from query parameters in that they are defined in a report and processed by the report server, while query parameters are defined as part of the dataset query and processed on the database server.

Linked Reports
A linked report is a report server item that provides an access point to an existing report. Conceptually, it is similar to a program shortcut that you use to run a program or open a file.
A linked report is derived from an existing report and retains the original's report definition. A linked report always inherits report layout and data source properties of the original report. All other properties and settings can be different from those of the original report, including security, parameters, location, subscriptions, and schedules.
You can create a linked report on the report server when you want to create additional versions of an existing report. For example, you could use a single regional sales report to create region-specific reports for all of your sales territories.
Although linked reports are typically based on parameterized reports, a parameterized report is not required. You can create linked reports whenever you want to deploy an existing report with different settings.

Snapshot Reports
A report snapshot is a report that contains layout information and query results that were retrieved at a specific point in time. Unlike on-demand reports, which get up-to-date query results when you select the report, report snapshots are processed on a schedule and then saved to a report server. When you select a report snapshot for viewing, the report server retrieves the stored report from the report server database and shows the data and layout that were current for the report at the time the snapshot was created.
Report snapshots are not saved in a particular rendering format. Instead, report snapshots are rendered in a final viewing format (such as HTML) only when a user or an application requests it. Deferred rendering makes a snapshot portable. The report can be rendered in the correct format for the requesting device or Web browser.
Report snapshots serve three purposes:
Report history. By creating a series of report snapshots, you can build a history of a report that shows how data changes over time.
Consistency. Use report snapshots when you want to provide consistent results for multiple users who must work with identical sets of data. With volatile data, an on-demand report can produce different results from one minute to the next. A report snapshot, by contrast, allows you to make valid comparisons against other reports or analytical tools that contain data from the same point in time.
Performance. By scheduling large reports to run during off-peak hours, you can reduce processing impact on the report server during core business hours.

Cached Reports
A cached report is a saved copy of a processed report. Cached reports are used to improve performance by reducing the number of processing requests to the report processor and by reducing the time required to retrieve large reports. They have a mandatory expiration period, usually in minutes.

Ad Hoc Reports
An ad hoc report can be created from an existing report model by using Report Builder. Ad hoc reports refer specifically to Report Builder reports, and not to reports that you can create using the Report Wizard. Ad hoc reports leverage report models and pre-defined templates to enable information workers to quickly and easily explore business data using familiar terminology and data structures that have been implemented in the report model. Ad hoc reports can be saved and run locally or published to a report server, just like other Reporting Services reports.

Clickthrough Reports
A clickthrough report is a report that displays related data from a report model when you click the interactive data contained within your model-based report. These reports are generated by the report server based on the information contained within the report model. The person who created the model determines which fields are interactive and which fields are returned when a clickthrough report is opened. These field settings cannot be changed in the report authoring tools.
Clickthrough reports are autogenerated. However, you can create an alternative customized report to the model for interactive data items that is displayed instead. The custom report is a standard Reporting Services report.

Drilldown Reports
Drilldown reports initially hide complexity and enable the user to toggle conditionally hidden report items to control how much detail data they want to see. Drilldown reports must retrieve all possible data that can be shown in the report.
reports with large amounts of data, consider drillthrough reports instead.

Drillthrough Reports
Drillthrough reports are standard reports that are accessed through a hyperlink on a text box in the original report. Drillthrough reports work with a main report and are the target of a drillthrough action for a report item such as placeholder text or a chart. The main report displays summary information, for example in a matrix or chart. Actions defined in the matrix or chart provide drillthrough links to reports that display greater details based on the aggregate in the main report. Drillthrough reports can be filtered by parameters, but they do not have to be. Drillthrough reports differ from subreports in that the report does not display within the original report, but opens separately. They differ from clickthrough reports in that they are not autogenerated from the data source, but are instead custom reports that are saved on the report server. They differ from drilldown reports in that they retrieve the report data only for the specified parameters or for the dataset query.

Subreports
A subreport is a report that displays another report inside the body of a main report. Conceptually, a subreport 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 subreport. The subreport can use different data sources than the main report. The report that the subreport displays is stored on a report server, usually in the same folder as the parent report. You can set up the parent report to pass parameters to the subreport.
Although a subreport can be repeated within data regions using a parameter to filter data in each instance of the subreport, subreports are typically used with a main report as a briefing book or as a container for a collection of related reports.

Reporting Services Features

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

What is SQL SERVER Reporting Services?

Microsoft SQL Server 2008 Reporting Services (SSRS) provides a full range of ready-to-use tools and services to help you create, deploy, and manage reports for your organization, as well as programming features that enable you to extend and customize your reporting functionality.
SQL Server 2008 Reporting Services (SSRS) is a server-based reporting platform that provides comprehensive reporting functionality for a variety of data sources. Reporting Services includes a complete set of tools for you to create, manage, and deliver reports, and APIs that enable developers to integrate or extend data and report processing in custom applications. Reporting Services tools work within the Microsoft Visual Studio environment and are fully integrated with SQL Server tools and components.
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. Reporting Services also enables you to create ad hoc reports based on predefined models, and to interactively explore data within the model. You can select from a variety of viewing formats, export reports to other applications, and subscribe to published reports. The reports that you create can be viewed over a Web-based connection or as part of a Microsoft Windows application or SharePoint site. Reporting Services provides the key to your business data.

Microsoft SQL Server Reporting Services provides a complete server-based platform that is designed to support a wide variety of reporting needs, including managed enterprise reporting, ad-hoc reporting, embedded reporting, and Web-based reporting, to enable organizations to deliver relevant information where needed across the entire enterprise. Reporting Services provides the tools and features necessary to author a variety of richly formatted reports from a wide range of data sources, and provides a comprehensive set of familiar tools used to manage and secure an enterprise reporting solution. Reports are processed and delivered quickly and effectively, enabling users to either receive reports automatically through subscriptions, to access reports from a central report repository on an ad-hoc basis, or to consume reports in the context of their business processes through reports directly embedded into their business or Web applications