Your SQL questions, answered

Tables in SSRS – Querychat

Table is one of the most important building elements in SSRS. It is used to display data in two-dimensional format consisting of rows and columns.

A table can be put in a report in two ways:

  • Drag-and-drop a Table report item from Toolbox tab

Figure 1: Table item in Toolbox tab

  • Right-click on report design surface and select Table item

Figure 2: Insert Table item on design surface of report

After the table is added to the report design surface, we will see the following:

Figure 3: The look of Table item on the design surface

As we can see in the previous picture, each table consists of header and data.

In order to explain the usage of tables in reporting, we will first display a report for a Departments database table, and afterwards we will create one additional row that will count total number of students in all departments. Table formatting will also be presented.

Table: Departments

If we want to display table data in a report table item, we need to connect the table item with particular dataset containing reference to Departments table.

First, we need to be sure the report contains the dataset we want to connect to a particular table item.

Figure 4: Locating the dataset within a report

Afterwards, we connect the dataset to the table report item in the following way:

          1. Select the table item on report design surface

          2. Go to table item properties and set DataSetName to DepartmentsDS

Figure 5: Setting the dataset resource for Table item

          3. Now we are able to define data for each table column: we select particular column, click on the icon in the upper-right corner, and select dataset column name which we want to display in that table column.

Figure 6: Assigning dataset columns to table item columns

          4. We do previous step for all the dataset columns that we want to display in the table item.

          5. As we add the dataset columns to the table item columns, header of table item will be automatically populated with the name of the dataset column.

Figure 7: Automatic assignment of header names

          6. If we want to change the name of the header, we can do it easily by positioning on the header field and typing in the text we want. In the example, header names are changed in a way that they are made upper-case.

          7. We can resize the table columns by positioning on the side of the column and extending the column to the desired width

Figure 8: Resizing the column width

          8. We can also do some report formatting on table columns by making Report Formatting toolbar visible. We make Report toolbar visible by clicking on the menu View -> Toolbars -> Report Formatting

In the example, we made table header names bold and centered.

          9. So far, the report containing the table looks like this:

Figure 9: Appearance of the table item in a report

         10. Next step is adding a table row that will contain an information of total number of students of all departments.

         11. We position on the last table row and add additional row like presented in the picture below

Figure 10: Adding new row to a table item

Here we have chosen to insert a table row outside a group, because we want to use the table row to hold summarized information related to all groups within the dataset(in this case, a group represents a row).

         12. Now we can see the new table row being added.

Figure 11: New row added to a table item

         13. In new table row, we add label “Total” saying what type of information the table row will hold. In the table cell related to “Number of students” column we add an expression which will sum up all previous table cells from that table column. An expression is added if we right-click on the table cell and select “Expression…”

Figure 12: Adding an expression to a table cell

         14. The expression is defined by using the Sum function over dataset column “numberofstudents” in a way as presented in the picture below.

Figure 13: Adding an expression to a table cell

         15. As we can see in the previous picture, there are a plenty of functions we can use in order to build an expression. We can incorporate parameters, built-in fields as well as variables, and we also have a plenty of choice when it comes to functions and operators. This example will present usage of Sum function used against a dataset column.

         16. After we have designed a new table row containing new information which we don’t have in the dataset, the report table has the following appearance:

Figure 14: Appearance of optimized table item in a report

As we can see, there is a new row that gives us the information about total number of students in all departments.

Leave a Reply

Notify of
Trusted by people who work at, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc