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.
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.
Instant livechat to an Expert!
Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service!
Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.