Reporting Services 2005 has made our lives easier by providing end-to-end reporting solutions. It supports authoring, managing and delivering both enterprise and ad-hoc reporting.

An enterprise report can be authored with just a few components. The authoring requires a data source, a report template, a dataset and report items including one or more data regions. This article speaks about one of these data regions - the “Table Data Region”, which incidentally is the most commonly used data region.

The “Table Data Region” provides for simple structuring of the report layout by specifying the number of columns to be displayed and the number of rows to be repeated. Once defined, the numbers of columns in the “Table Data Region” are fixed and do not get increased or decreased like in a “Matrix Data Region”. The “Table Data Region” initially contains three sections; Header, Detail and Footer. The usages of these three sections are described below. Let us first complete the initial steps of adding a data source and a dataset, and then look into the table data region.

Open “SQL Server Business Intelligence Development Studio” and create a project by selecting the “Report Server Project” template under “Business Intelligence Projects” types. Add a new data source called DataSourceAdventureWorks.rds to the project, which should connect to the AdventureWorks database. Add a new report template and name it as Report with Table Data Region.rdl. Create a dataset by using the first tab in the report, by selecting the Add Data Source option and typing in the below query:
 

SELECT YEAR([Sales].[SalesOrderHeader].[OrderDate]) AS OrderYear,
              DATENAME(mm, [Sales].[SalesOrderHeader].[OrderDate]) AS OrderMonth,
              MONTH([Sales].[SalesOrderHeader].[OrderDate]) AS OrderMonthNumber,
              SUM([Sales].[SalesOrderDetail].[LineTotal]) AS Total
FROM [Sales].[SalesOrderHeader]
       INNER JOIN [Sales].[SalesOrderDetail]
              ON [Sales].[SalesOrderHeader].[SalesOrderID] = [Sales].[SalesOrderDetail].[SalesOrderID]
GROUP BY YEAR([Sales].[SalesOrderHeader].[OrderDate]),
       DATENAME(mm, [Sales].[SalesOrderHeader].[OrderDate]),
       MONTH([Sales].[SalesOrderHeader].[OrderDate])

Your report would now look like this;

Adding the Table Data Region

The initial steps are completed. Open the layout tab and drag and drop a “Table Data Region”. Notice that the “Table Data Region” can be placed anywhere in the body. Initially there are three rows and three columns; and the rows are categorized into three types, header, detail and footer with unique icons. Rows and columns can be deleted, but at any given time there should be at least one cell. Not only that, there can also be more than one row from each type. Look at the image below, notice that there are two detail rows, one header row and one footer row. Adding additional rows can be done by right clicking on the row icon and selecting either “Insert Row Above” or “Insert Row Below”. Similarly, columns can also be added by selecting either “Insert Column to the Left” or “Insert Column to the Right” from the menu.
 

Each cell in the table contains a Textbox. A textbox can contain either a static value or an expression. Open the .rdl file by a using text editor (or right-click on the report and click on View Code) and examine the resulting xml file. See how cells are organized. Textboxes can be replaced with other Report Items. Simply drag an item from the Toolbox and drop onto one of the cells. You may notice that all types of items cannot be placed on all cells. For an example, a Table item cannot be placed into cells in the Detail rows but on Header or Footer rows.

Filling the table with data

Setting data fields to columns is really a simple task. One way is to type the expression in the cell (in the Textbox actually) directly. The second way or the simplest way is dragging data field from the dataset and dropping it onto the cell. If the “Dataset Window” is not open, it can be opened by clicking on the last menu item in the View menu.

Now let’s add a couple of data fields from the dataset to the table. Make sure you have only one Detail row. If multiple detail rows exist, delete the additional rows. Drag the OrderYear field and drop it onto the first cell of the Detail row. Once dropped, the value of the Textbox is set with an expression like =Fields.OrderYear.Value. You may notice that the syntax of the expression is in Visual Basic.NET. Note that the cell above it (i.e. the first cell of the Header row) has been automatically filled with the value “Order Year”. The Reporting Services Designer automatically converts column names with meaningful names whenever possible. For example, the Designer converts column names that are in Pascal notation or names each word is separated with an underscore.

Drag OrderMonth to the next cell in the Detail row. Make sure that both cells in the Detail row are left aligned. If not, use Report Formatting toolbar to format the cell. Open the toolbar using View -> Toolbars -> Report Formatting if is not visible.

So far, we have added two fields. Let’s add the third one. Add the Total onto the third cell of the Detail row. Make sure it is right aligned. Now the layout of the report would like this;

Done! Go to Preview and see the report. You may notice that months have not been sorted for the year. This can be simply corrected. Get the properties of the table. This can be opened by right-clicking the top left corner of the table (first cell of the icon column). Open “Sorting” tab of the Table Properties window. On the “Sort on” list box, add two expressions (can be selected from the drop-down), the OrderYear and the OrderMonthValue. See the below image of Table Properties window.

Save the settings and preview it. Records are sorted as we set, as we want. This also can be done through data level by extending the TSQL with;

ORDER BY OrderYear, OrderMonthNumber 

Now it is sorted but note that values of the total column are not properly formatted. Let’s fix it. Right click on the “Total” cell of the detail row and click on the Properties menu item. Open the “Format” tab in the Textbox Properties window. The “Format code” can be set by either writing directly it on the “Format code” input box or selecting one from the built-in format list. Second way is the easiest way. Click on the ellipsis button next to the “Format code” input box. Select “Currency” from the “Standard“ list. If prefer, you can enter custom format by selecting the “Custom” radio button. For example, if need to show without decimals, enter the code as “C0”. Once set, preview the report and see.

Why don’t we show the total of all orders at the end of the report? Drag the “Total” data field from the dataset and drop it onto the last cell of Footer row (Just below the Total cell of the Detail row). Once it is dropped, Reporting Services Designer automatically adds SUM aggregate function to the expression. If different aggregation required, open the “Expression” window by right-clicking on the textbox and clicking the “Expression” menu item. You can find all aggregates under “Common Functions”. Once the aggregation is set, add bit formatting to the textbox (cell) by using Report Formatting toolbar. Increase the size of the font and bold it. If want make header values bold too. See the preview below;

Preview and see the report again.

Grouping data

Grouping allows you to arrange data in the report as you want. For example, you can group months by year, avoiding duplicate years on each row. Not only that, it allows to add any aggregation to the group. Reporting Services supports nesting groups too.

Let’s group months by year. Right-click on the “Detail icon” on the table and click the “Insert Group”. Under the “General” tab in the “Grouping and Sorting Properties” window, change the default name as “table1_Group1_OrderYear”. Expand the drop-down in the first row of “Expression column” in the “Group on” list. Select “OrderYear” for the expression. Done! Now you will see two addition rows, above and the below of the Detail row. Top one is the Group Header and the below one is the Group Footer. Preview the report and see. Though the report is grouped by OrderYear, it cannot be clearly seen. Year still get repeated because it is still on Detail row. Move the OrderYear textbox from Detail row onto Group Header row making it as the group header name. Now there shouldn’t be repetition on year values. Let’s show the total for the OrderYear too. Just like you add the data field Total to the Footer, add it to the Group Footer. Make it bold and change format of it as “Currency”. See the image below.

Preview the report and see how grouping works.

Interactive Sorting

This feature facilitates end-user to sort data while the report is being viewed on the browser. Once the column is enabled with “Interactive Sorting” by specifying the “scope for data to be sorted” and “scope for sort expression to be evaluated”, user will see an icon next to the column name that indicates that the column is enabled for “Interactive Sorting”. User can get the data sorted either ascending or descending by clicking icon.

First let’s enable Interactive Sorting on “Order Year” column. Get the properties of the “Order Year” column (first cell of the Header row NOT the cell of Group Header row). Open the “Interactive Sorting” tab. Enable Interactive Sorting by checking the first checkbox. Set the “Sort Expression” as ”OrderYear”. Set the “Data region or grouping to sort” as “Current scope”. This indicates that scope of the textbox is the scope we want to sort. Select “Choose data region or grouping” radio button under “Evaluate sort expression in this scope” and select the name of the group we set for “OrderYear” from the dropdown.

Save it and refresh the report in the Preview. You will see an icon with two arrows next to the “Order Year” column name. Click and see how the sorting works.

Let’s add “Interactive Sorting” to the “Order Month” differently. Assume that the user needs to sort months on a particular year without changing the order of months in other years. For example, user needs to see months in ascending order for year 2002 but not for other years. In this case, “Data region or grouping to sort” has to be set for the “table1_Group1_OrderYear”. If you open the Properties of “Order Month” (2nd Cell of Header row) and expand the DropDown under the “Data region or grouping to sort” after enabling “Interactive Sorting”, you will notice that the group we want to set is not there. This is because it is in the Header row. Now move the “Order Month” textbox to the cell down (2nd cell in the Group Header row) and get the properties of it.

Now you will see the group name under dropdown of the “Data region or grouping to sort”. You can either select it or leave it with “Current scope” radio button selected. Select the “OderMonthNumber” for “Sort Expression”.

Done! Go to “preview” and refresh. Since the “Order Month” is moved to “Group Header” now it repeats in all groups. And the icon for “Interactive Sorting” is displayed next to it.

If you click on one the ascending icon of the Order Month column for Order Year 2002, it only sorts months under 2002.

Enable Drilldown feature

The report can be set in such a way that allows user to hide the detail sections of the report and see the summary only. If require, user can drilldown the summary level and see the detail of it. For example, we can set the report that initially shows years and let user to expand a year see relevant months for it. This can be simply setup. First we need to set the “Detail” row in such a way where its visibility can be controlled by another textbox; “OrderYear”. In order to setup this, right-click on the “Detail” row icon and click on the “Edit Group” menu item. It opens “Grouping and Sorting Properties” of the “Detail” row. Open the “Visibility” tab. Make sure that “Initial Visibility” is set to “Visible”. Enable “Visibility can be toggled by another item” checkbox. Select the name of the textbox of “OrderYear” (first cell in the Group Header row); “OrderYear” from the “Report Item” dropdown. Save it and preview the report. You will see an icon (+) before the value of “Order Year” that indicates the visibility of the lines under can be controlled. You may notice that the icon has been set for (+) sign that indicates the summary of the year has been hidden (or collapsed) thought they are visible (expanded). In order to fix this, get the properties of the “OrderYear” textbox and open “Visibility” tab. Select “Expanded (-)” radio button under “Initial appearance of the toggle image for this report item”. All set! Preview and see.

This article briefly explains the usage of Table Data Region. Next is Matrix Data Region. Stay tune.

[ RATE this ARTICLE, and COMMENT on it ]