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)।
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)।
Copy paste from MSDN books online and running the blog?
ReplyDeleteToo Good Sir,
ReplyDeleteIt Is Very Help Full