Last updated on July 28th, 2022 at 01:33 pm
Hi Everyone! Today I want to talk about a very simple, but effective feature that you can setup on your SQL Server Reporting Services (SSRS) reports. Wouldn’t it be useful to know, automatically, how many rows are within a table instead of counting each one? I think that’s very helpful, so in this blog post I show you how to add, and display in a text box, the total number of rows in a SSRS table.
Most of Recast Endpoint Insight’s reports list details about an object, for example computers or databases. Our customers want to know immediately what the total number of objects listed are because it is impractical to count thousands of objects by hand. What do we do? We simply add a text box above the table with the total number of rows. This way the report reader can quickly see the number of objects itemized in a table.
Once you get the hang of what I’m going to show you, you can do other tricks with it too!
Add the Total Number of Rows in a SSRS Table
In order to demonstrate this feature, I am using one of Recast Endpoint Insight’s SQL Server reports, the List of Computers by Database Compatibility Level report. I want to display the total number of databases in a text box above the table. By the way, there is a reason why I deliberately chose this report. I tell you about it later on in this post.
Let’s check out the report on the preview page (see below). At the moment, it does not have a text box to tell me the number of databases.
From the above image, you can see that this report is displaying information about one specific collection and one specific compatibility level.
In this table, the total number of rows is also the total number of databases. Given that there aren’t a lot of databases in this 3-page report. You could count each row to learn that there are 86 databases. In most cases, however, counting each row is clearly not a good use of time or practical. Especially if you are dealing with hundreds or thousands of computers and databases.
Let me show you how I add the total number of rows in a SSRS table.
I start by going to the Design layout tab. Next, select the table as shown in the above image. As I mentioned earlier, I want the text box to appear above the table in the left-hand corner.
Next, make some room for the text box by moving the table down about 18-points. Or if you are measuring in inches, I always use .25 of an-inch. Highlighted in the above image is the space where I want to place the text box.
Next, from the Toolbox menu. Choose Text Box and drag it onto the design layout where the text box is to be placed.
You can see above where I inserted and re-sized the Text box in order to fit it in the space.
Now right-click on the Text box and choose the option Expression…
Expression
=string.format(“Total Number of SQL Databases: {0}”,
Count(Fields!Database_Name.Value, “ES_DatabaseCompatibility_List”))
In the Expression window, paste the expression given above.
You can see the steps I took to add the data columns which are marked in the screenshot above. I wanted the total number of databases. So I chose the category Datasets (1), the item ES_DatabaseCompatibility_List (2) and the values First(Database_Name) (3). Once pasted (double-click on the value) into the expression. I edited the function and removed First and replaced it with Count (4).
If you like, you can replace Database_Name (3) from the equation to any column in your report’s participating datasets.
Lastly, click OK. With that you are done. The total number of databases will be displayed just above the table in your report.
Previewing the SSRS Report
After setting-up the text box, click on Preview to run the report.
Finally, when you run the report, the text box is populated with the expression data. It appears just above the table as it was configured on the design page. Now you know how to add the total number of rows in a SSRS table!
Conclusion and Why I Chose this Report
I picked this report because next week Garth Jones uses the same one to show you how he adds information about the total number of computers in a SSRS report. It’s a slightly different take on what I just showed you. You won’t want to miss this tip because if, as in the example above, you needed to make changes to 86 SQL databases, how many computers would you need to visit? By the way, last week I showed you how to do the same thing with Power BI in, How to Add a Card to Display the Total Number of Computers in a Power BI Table.
There are many other features in SSRS that can be used as add-ons to improve your reports. Check out this blog post for more tips! I hope you find this post helpful and that you come up with more report design ideas. For any questions you can always reach out to me on Twitter @SuaresLeonard. Until then, good luck!