Friday, July 10, 2009

Custom Assemblies in SQL SERVER Reporting Services(SSRS)

Create custom assembly
1) Open Visual Studio 2008 and create a new Project.
2) The New Project window will open; Select the Visual C# node; from the right pane select Class Library. Complete the following:
In the Name field enter "rsCustomAssembly"
In the Location field enter "C:\Visual Studio 2008\Projects"
In the Solution field enter "rsCustomAssembly"
Click OK when you are done
3) Delete the default class.cs file created. When prompted click OK.
4) Add a new class by right clicking on the solution name and selecting Add >> Class...
5) Name the new class Utilities.
6) Enter the code below into the class file and save it.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace rsCustomAssembly
{
public class Utilities
{
///
/// Sample concatenate string method; not intended for production use.
/// Note: the string object already provides a concatenate method - string.concat(object o)
///

///
///
///
public static string Concat(string str1, string str2)
{
return str str1+ " " + str2;
}

public static string HelloReportingServices()
{
return "Reporting Services";
}
}
}
Your screen should look similar

7) From the top menubar you will find Build; Click this and select rsCustomAssembly from the list. The bottom status bar should presenet Build succeeded. If not, check your code again to make sure it follows the description above.
Copy the assembly to the Visual Studio Private assemblies and Report Server bin
You must copy you assembly (.dll) to the report designer and report server folders before you can use the assembly in Reporting Services. You can find your assembly by locating the bin folder in your project. For example: C:\Visual Studio 2008\Projects\rsCustomAssembly\rsCustomAssembly\bin\Debug
Note: the location is dependent on your install
For Report Designer: C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies
For Report Server: C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin
You have completed with this step.
Create a new Report
This step we will perform the following:
Create a new report
Reference the custom assembly
Add a new textbox to the report designer with a custom expression
1) Open BIDS and create a new project, like you did in 1) under "Create custom assembly".
2) Under the Project Type pane, select Business Intelligence Projects; from within the Templates pane, select Report Server Project and complete the following:
For the Name field enter SampleReport
For the Location field enter C:\Visual Studio 2008\Projects
For the Solution field enter SampleReport
Click OK
3) From the Solution Explorer, right click on the Reports folder and select Add >> New Item
The Add New Item window will open

4) From within the Templates pane, select Report and leave the default name of Report1.rdl
5) Click Add
Reference the Assembly
1) From the top menu bar select Report >> Report Properties
Note: if you don't see the Report menu item, try placing your cursor anywhere in the design view; you should then see the menu item.
2) The Report Properties window opens. From within the left pane, select References.
3) Click Add under "Add or remove assemblies".
4) Click the ellipsis right of the text box. The Add Reference window will open.
5) Select the Browse tab and navigate to the Report Server bin location, where you copied your .dll, as described earlier in this article under Copy the assembly to the Visual Studio Private assemblies and Report Server bin
you should see a similar value in the reference text box
6) Click OK to save and close the Report Properties window.
Add Expression and run report
1) Drag and drop a Text Box from the Toolbox pane on the left of your design pane. Strech the text box so you have some room for text.
2) Right click the text box you just added and select Expression
3) For the expression value enter :
=rsCustomAssembly.Utilities.Concat("Reporting", "Services")
4) Click OK to save and close the Expression window.
5) To the right of the Design tab, click Preview. You should see the following:
Save your work. You have completed referencing a custom assembly in SQL Server Reporting

SQL SERVER Reporting Services: Create and Call a Custom Assembly

namespace MyAssembly
{
public class Colors
{
public string PercentageToColor(double percentage)
{
string returnValue = string.Empty;

if (percentage < 20)
{
returnValue = "red";
}
else if (percentage < 80)
{
returnValue = "blue";
}
else
{
returnValue = "green";
}

return returnValue;
}
}
}
Once you’ve compiled your class you need to copy the resulting assembly to the directory in which it is accessible from within your report:
· To use it in the report designer, you need to copy it to: C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
· To make it available for reports that have been deployed to the report server, you need to copy it to: C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin

Before you can access your custom assembly you have to reference the assembly: Open the report properties (Menu: Report – Report Properties) and select the References tab.
Browse to your assembly and define a Class Name and an Instance Name. (The Class Name and the Instance Name or only for non-static methods). Make sure to prefix your class name with the assembly name.
Now you can call the methods in your assembly from your report, using an expression:
· To call a static method: =..
· To call an instance method: =Code..
So in our example this would be: =Code.TestColor.PercentageToColor(Fields!Percentage.Value)
That's it! You've just created and called a custom assembly from your report...
In one of my future articles I will explain how to pass parameters to the class constructor and I will also tell something about SSRS and .Net Permissions.

Tablix Data Region in SSRS


Tablix Data Region in SSRS The Tablix data region is a generalized layout report item that displays report data in cells that are organized into rows and columns. Report data can be detail data as it is retrieved from the data source, or aggregated detail data organized into groups that you specify. Each Tablix cell can contain any report item, including a text box, an image, or another data region such as a Tablix region, chart, or gauge. To add multiple report items to a cell, first add a rectangle, which acts as a container, and then add the report items to the rectangle. The table, matrix, and list data regions are represented in the Toolbox by templates for the underlying Tablix data region. When you add one of these templates to a report, you are actually adding a Tablix data region that is optimized for a specific data layout. By default, a table template displays detail data in a grid layout, a matrix displays group data in a grid layout, and a list displays detail data in a free-form layout. By default, each Tablix cell in a table or matrix contains a text box. The cell in a list contains a rectangle. You can replace a default report item with a different report item, for example, an image. As you define groups for a table, matrix, or list, Report Designer adds rows and columns to the Tablix data region on which to display grouped data. Understanding the Tablix data region requires understanding the following: 1. The difference between detail data and grouped data. 2. Groups, which are organized as members of group hierarchies on the horizontal axis as row groups and on the vertical axis as column groups. 3. The purpose of Tablix cells in the four areas of a Tablix data region: the body, the row group headers, the column group headers, and the corner. 4. Static and dynamic rows and columns and how they relate to groups. Once you understand these concepts, you can recognize the structure that Report Designer adds for you when you add templates and create groups, and modify the structure to suit your own needs. Report Designer provides multiple visual indicators to help you recognized Tablix data region structure. For more information, see Understanding Tablix Data Region Cells, Rows, and Columns। Understanding Detail and Grouped Data Detail data is all the data from a report dataset as it comes back from the data source. Detail data is essentially what you see in the query designer results pane when you run a dataset query. The actual detail data includes calculated fields that you create, and is restricted by filters set on the dataset, data region, and details group. You 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 you specify in the group definition, for example, [SalesOrder]. You display grouped data on group rows and columns by using simple expressions that aggregate the grouped data, for example, [Sum(Quantity)]. For more information, see Understanding Groups (Reporting Services)
Understanding Group Hierarchies Groups are organized as members of group hierarchies. Row group and column group hierarchies are identical structures on different axes. 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. 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 columns groups are each represented as an independent forest. For more information, see Understanding Groups (Reporting Services)
Understanding Tablix Data Region Areas A Tablix data region has possible four areas for cells: the Tablix corner, the Tablix row group hierarchy, the Tablix column group hierarchy, or the Tablix body. The Tablix body always exists. The other areas are optional. Cells in the Tablix body area display detail and group data. Cells in the Row Groups area are created automatically when you create a row group. These are row group header cells and display row group instance values by default. For example, when you group by [SalesOrder], group instance values are the individual sales orders that you are grouping by. Cells in the Column Groups area are created automatically when you create a column group. These are column group header cells and display column group instance values default. For example, when you group by [Year], group instance values are the individual years that you are grouping by. Cells in the Tablix corner area are created automatically when you have both row groups and column groups defined. Cells in this area can display labels, or you can merge the cells and create a title. For more information, see Understanding Tablix Data Region Areas
Understanding Static and Dynamic Rows and Columns 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, this discussion uses row groups. You can apply the same concepts to column groups. A row is either a static or dynamic. A static row is not associated to a group. When the report runs, a static row renders once. Table headers and footers are static rows. Static rows display labels and totals. Cells in a static row are scoped to the data region. A dynamic row is associated to 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 you 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 you 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. You can also add static and dynamic rows manually. Visual indicators help you understand which rows are static and which rows are dynamic. For more information, see Understanding Tablix Data Region Cells, Rows, and Columns। Understanding Tablix Data Region Areas A Tablix data region has four areas that contain Tablix cells: the corner, the row group area, the column group area, and the body. Cells in each area have a distinct function. You add cells to the Tablix body area to display detail and grouped data. Report Designer adds cells to the row group or column group area when you create a group in order to display group instance values. Report Designer creates Tablix corner cells when both row groups and column groups exist. On the design surface, dotted lines denote the four areas of a selected Tablix data region। The following figure shows the areas for a Tablix region with nested row groups based on category and subcategory, nested column groups based on geography and country/region, and an adjacent column group based on year.
The following list describes each area: Tablix corner area. (Optional) A Tablix corner is located in the upper-left corner, or upper-right corner for right-to-left (RTL) layouts. This area is automatically created when you add both row groups and column groups to a Tablix data region. In this area, you can merge cells and add a label or embed another report item. In the figure, merged cells in the corner display the label Sales by Area and Year. Tablix column groups area. (Optional) Tablix column groups are located in the upper-right corner (upper-left corner for RTL layout). This area is automatically created when you add a column group. Cells in this area represent members of the column groups hierarchy, and display the column group instance values. In the figure, the cells that display [Geography] and [CountryRegion] are nested column groups, and the cell that displays [Year] is an adjacent column group. The column [Total] displays the aggregated totals across each row. Tablix row groups area. (Optional) Tablix row groups are located on the lower-left corner (lower right for RTL layout). This area is automatically created when you add a row group. Cells in this area represent members of the row groups hierarchy, and display row group instance values. In the figure, the cells that display [Category] and [Subcat] are nested row groups. The Total row below Subcat repeats with each category group to show the aggregated subtotals for each column. The grand total row shows the totals for all categories. Tablix body area. The Tablix body is located in the lower right corner (lower left for RTL layout). The Tablix body displays detail and grouped data. In this example, only aggregated data is used. The scope for the expression is determined by the innermost groups to which the text box belongs. Cells in the Tablix body display detail data when they are members of a detail row and they represent aggregate data when they are members of a row or column associated with a group. By default, cells in a group row or column that contain simple expressions that do not include an aggregate function, evaluate to the first value in the group. In the figure, the cells display the aggregate totals for line totals for all sales order. When the report runs, column groups expand right (or left, if the Direction property of the Tablix data region is set to RTL) for as many columns as there are unique values for a group expression. Row groups expand down the page. For more information, see Understanding Tablix Data Region Cells, Rows, and Columns। The following figure shows the Tablix data region in Preview. The row group area displays two category group instances for Clothing and Components. The column group are displays a geography group instance for North America, with two nested country/region group instances for Canada (CA) and the United States (US). In addition, the adjacent column displays two year group instances for 2003 and 2004. The Total column row displays the row totals; the totals row that repeats with the category group shows subcategory totals, and the grand total row displays the category totals once for the data region. Understanding Tablix Data Region Cells, Rows, and Columns To control how the rows and columns of a Tablix data region display data in a report, you must understand how to specify rows and columns for detail data, for group data, and for labels and totals. In many cases, you can use the default structures for a table, matrix, or list to display your data. For more information, see Adding a Table (Reporting Services), Adding a Matrix (Reporting Services), or Adding a List (Reporting Services). A Tablix data region displays detail data on detail rows and detail columns and grouped data on group rows and group columns. When you add row groups and column groups to a Tablix data region, rows and columns on which to display the data are automatically added. You can manually add and remove rows and columns to customize a Tablix data region and control the way your data displays in the report. To understand how to customize a Tablix data region, you should first understand how to interpret the visual cues you see when you select a Tablix data region on the design surface. Understanding Tablix Visual Cues Visual cues on a Tablix data region help you work with a Tablix data region to display the data you want. Row and Column Handles When you select a Tablix data region, the row and column handle graphics indicate the purpose of each row and column. Handles indicate rows and columns that are inside a group or outside a group. The following table shows a variety of handle displays. Icon Description Only the details group on the row group hierarchy One outer group and the child details group One outer group, one inner group; no details group One outer group, one inner group, and the child details group One outer group with a footer row for totals and one inner group One outer group with a footer row for totals, one inner group with a footer row for totals, and one details row One outer group with a header for labels and a footer for totals, and an inner group; no details group Group Rows Rows inside a group repeat once per unique group value and are typically used for aggregate summaries. Rows outside a group repeat once with respect to the group and are used for labels or subtotals. When you select a Tablix cell, row and column handles and brackets inside the Tablix data region show the groups to which a cell belongs. This figure displays the following visual cues: Row and column handles that indicate group associations. Highlighted group indicators that show the innermost group membership for a selected cell. Group indicators that show all group memberships for a selected cell. Total Rows After you add row and column groups, you can add a row to display totals for columns and a column to display totals for rows. The following figure shows a matrix with both row and column groups, and a total row and a total column. Grouping Pane The Grouping pane displays the row and column groups for the currently selected Tablix data region on the design surface. The following figure shows the Grouping pane for this Tablix data region. The Row Groups pane shows the parent group Category and child group Subcat. The Column groups pane shows the parent group Geography and child group CountryRegion, and also the Year group, which is an adjacent group to the Geography group. When you select the Subcat group in the Row Groups pane, the group bar turns a darker shade of orange, and the corresponding row group member cell is selected on the design surface. Displaying Data on Rows and Columns Rows and row groups and columns and column groups have identical relationships. The following discussion describes how to add rows to display detail and group data on rows in a Tablix data region, but the same principles apply to adding columns to display detail and grouped data. For each row in a Tablix data region, a row is either inside or outside each row group. If the row is inside a row group, it repeats once for every unique value of the group, known as a group instance. If the row is outside a row group, it repeats only once in relation to that group. Rows outside all row groups are static and repeat only once for the data region. For example, a table header or footer row is a static row. Rows that repeat with at least one group are dynamic. When you have nested groups, a row can be inside a parent group but outside a child group. The row repeats for every group value in the parent group, but displays only once in relation to the child group. To display labels or totals for a group, add a row outside the group. To display data that changes for every group instance, add a row inside the group. When you have detail groups, each detail row is inside the detail group. The row repeats for every value in the dataset query result set. For more information about group hierarchies, see Understanding Groups (Reporting Services). The following figure shows a Tablix data region with nested row groups and a details group. For a Tablix data region that displays detail data, the details group is the innermost child group. Rows that you add to a details group repeat once per row in the result set for the query for the dataset linked to this Tablix data region. The following figure shows the last page of the rendered report. In this figure, you can see the last detail rows and the subtotal row for the last order. For each column in a Tablix data region, the same principles apply. For example, a column is either inside or outside each column group; to display totals, add a column outside the group. To remove rows and columns associated to a group, you can delete the group. When you delete a group, you have the choice between deleting the group definition only or deleting the group and all its associated rows and columns. By deleting just the group, you preserve the row and column layout on the data region. When you delete the group and its related rows and columns, you are deleting all static rows and columns (including group headers and footers) and dynamic rows and columns (including group instances) that are associated with that group. For step-by-step instructions about adding or deleting rows and columns, see How to: Insert or Delete a Row (Reporting Services) and How to: Insert or Delete a Column (Reporting Services). Understanding Tablix Cells Tablix cells belong to one of four Tablix areas: the Tablix body, Tablix row or Tablix column group areas, or the Tablix corner. Although each cell can potentially display any value in the dataset, the default function for each cell is determined by its location. For detailed information about Tablix areas, see Understanding Tablix Data Region Areas. By default, cells in Tablix row and column group areas represent group members. Group members are organized into multiple tree structures in the report definition. The row group hierarchy expands horizontally. The column group hierarchy expands vertically. These cells are added automatically when you create a group, and display the unique values for a group at run time. Cells in the Tablix corner are created when there are both row and column group areas. You can merge cells in this area to create a label or embed another report item. Cells in the Tablix body area can display detail data when the cell is in a detail row or column and aggregated group data when the cell is in a group row or column. The scope for the data in a cell is the intersection of the innermost row group and innermost column group to which the cell belongs. Merging and Splitting Cells Inside a Tablix area, you can merge multiple adjacent cells together. For example, you can create cells for labels that span multiple columns or rows. In the Tablix corner area, cells can be combined in only one direction at a time: horizontally across columns or vertically down rows. To merge a block of cells, merge the cells horizontally first. After all cells have been merged into a single cell in each row, select adjacent cells (you can select all adjacent cells in a column) and merge them. In the Tablix body area, cells can only be merged horizontally. Merging cells vertically is not supported. For more information, see How to: Merge Cells in a Data Region (Reporting Services). You can split a cell that was previously merged. You can split cells horizontally across columns or vertically down rows. To split a cell into a block of cells, split the cell horizontally first, and then split vertically as many times as necessary. Flexibility of a Tablix Data Region When you add a table, matrix, or list data region from the Toolbox, you start with an initial template for a Tablix data region, but you are not limited by that template. You can continue to develop how your data displays by adding or removing any Tablix data region feature such as groups, rows, and columns. When you delete a row or column group, you have the option of deleting the rows and columns that are used to display group values. You can also add or remove rows and columns manually. To understand how rows and columns are used to display detail and group data, see Understanding the Tablix Data Region. After you change the structure of the Tablix data region, you can set properties to help control the way the report renders the data region; for example, you can repeat column headers at the top of every page, or keep a group header with the group. For more information, see Controlling the Tablix Data Region Display on a Report Page (Reporting Services). Changing a Table to a Matrix By default, a table has detail rows that display the values from the report dataset. Typically, a table includes row groups that organize the detail data by group, and then includes aggregated values based on each group. To change the table to a matrix, add column groups. Typically, you would remove the details group when the data region has both row and column groups so that you can display only the summary values for the groups. For more information, see How to: Add or Delete a Group in a Data Region (Reporting Services). By definition, when you create a matrix, you add a Tablix corner cell. You can merge cells in this area and add a label. For more information, see How to: Merge Cells in a Data Region (Reporting Services). Changing a Matrix to a Table By default, a matrix has row groups and column groups and no detail group. To change a matrix to a table, remove column groups and add a details group to display on the details row. For more information, see How to: Add or Delete a Group in a Data Region (Reporting Services) and How to: Add a Details Group (Reporting Services). Changing a Default List to a Grouped List By default, a list has detail rows and no groups. To change the list to use a group row, rename the details group and specify a group expression. For more information, see How to: Add or Delete a Group in a Data Region (Reporting Services) Creating Stepped Displays By default, when you add groups to a Tablix data region, cells in the row group header area display group values in column. When you have nested groups, each group displays in a separate column. To create a stepped display, remove all group columns except one, and format the remaining column to display the group hierarchy as an indented text display. For more information, see How to: Create a Stepped Report. Adding an Adjacent Details Group By default, the details group is the innermost child group in a group hierarchy. You cannot nest a group under the details group. You can create additional adjacent details groups, to display the top 5 products and the bottom 5 products by sales, for example. Because you can add filter and sort expressions on each group, you can show two views of detail data from the same dataset in one Tablix data region. For more information, see Understanding Groups (Reporting Services), How to: Add or Delete a Group in a Data Region (Reporting Services), and How to: Add a Filter (Reporting Services). Controlling the Tablix Data Region Display on a Report Page (Reporting Services) After you add a Tablix data region to a report, you can continue to develop your design as you work between the Design view and Preview in Report Designer. This topic describes properties for a Tablix data region that you can modify to change the way a Tablix data region appears when you view it in a report. Controlling the Appearance of Data The following features help control the appearance of a Tablix data region: Formatting data. To format data in a table, matrix, or list, set the format properties of the text box in the cell. You can set properties for multiple cells at the same time. To format data in a chart, set formatting properties on the series. For more information, see Formatting Reports and Report Items and Formatting a Chart. You can also format data by writing expressions. For more information, see Adding Conditional Formatting (Reporting Services), Using Expressions (Reporting Services), and Expression Examples (Reporting Services). Controlling sort order. To control the sort order, you must define sort expressions on the data region. To control sort order for rows and columns that are associated with a group, you must define sort expressions on the group, including the details groups. You can also add interactive sort buttons to enable the user to sort a Tablix data region or its groups. For more information, see Working with Group Features (Reporting Services) and How to: Sort Data in a Data Region (Reporting Services). Displaying a message when there is no data. When no data exists for a report dataset at run time, you can write your own message to display in place of the data region. For more information, see How to: Set a No Data Message for a Data Region. Conditionally hiding data. To conditionally control whether to show or hide a data region or parts of a data region, you can set the Hidden property to True or to an expression. Expressions can include references to report parameters. You can also specify a toggle item, so that user can decide to display detail data. For more information, see Hiding Report Items Conditionally. Merging cells. Multiple contiguous cells within a table can be combined into a single cell. This is known as a column span or a cell merge. Cells can only be combined horizontally or vertically. When you merge cells, only the data in the first cell is preserved. Data in other cells is removed. Merged cells can be split into their original columns. For more information, see How to: Merge Cells in a Data Region (Reporting Services). Controlling Tablix Data Region Position and Expansion on a Page The following features help control the way a Tablix data region displays in a rendered report: Controlling the position of a Tablix data region in relation to other report items. A Tablix data region can be positioned above, next to, or below other report items on the report design surface. At run time, Reporting Services expands the Tablix data region as needed for the data retrieved for the linked dataset, moving peer report items aside as needed. To anchor a Tablix next to another report item, you must make the report items peers and adjust their relative positions. For more information, see Understanding Rendering Behaviors. Changing the Expansion Direction. To control whether a Tablix data region expands across the page from left-to-right (LTR) or from right-to-left (RTL), use the Direction property, which can be accessed through the Properties window. For more information, see Rendering Data Regions. Controlling How a Tablix Data Region Renders The following features help control how cells in row and column group areas appear in a rendered report. For more information about row and column groups areas, see Understanding Tablix Data Region Areas. For more information about how exporting a report to different formats affects the way a Tablix data region renders on a page, see Understanding Rendering Behaviors. Controlling paging. To control the amount of data that displays on each report page, you can set page breaks on data regions. You can also set page breaks on groups. Page breaks can affect the on-demand rendering performance by reducing the amount of data that needs to be processed on each page. For more information, see Understanding Pagination in Reporting Services and How to: Add a Page Break (Reporting Services). Displaying data on either side of row headers. You are not limited to displaying row headers on the side of a Tablix data region. You can move the row headers between columns, so that columns of data appear before the row headers. To do this, modify the GroupsBeforeRowHeaders property for the matrix. You can access this property through the Properties window. The value for this property is an integer; for example, a value of 2 will display two groups instances of data region column data before displaying the column containing the row headers. Controlling Row and Column Headings. You can control how row and column headings repeat or freeze when you view a report. For more information, see Controlling Row and Column Headings. For step-by-step instructions, see the following topics: · Repeating row and column headers on multiple pages. You can display row and column headers on each page that a Tablix data region spans. For more information, see How to: Display Row and Column Headers on Multiple Pages (Reporting Services). · Keeping row and column headers in view when scrolling. You can control whether to keep the row and column headers in view when you scroll a report using a browser. For more information, see How to: Keep Headers Visible When Scrolling Through a Report (Reporting Services)









Thursday, July 9, 2009

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