Last updated on August 6th, 2022 at 04:02 pm
Today, I’m going to show you how to add the total number of computers in a SSRS table. This is a slightly different take on what my colleague Leonard showed you in last week’s post, How to Add the Total Number of Rows in a SSRS Table. Before starting, I must tell you that if there is one computer on each row of your table, then you can follow Leonard’s steps. If, however, there is more than one row per computer, you need to follow what I’m about to show you. What do I mean?
Take a look at the above report. You can quickly tell that there is only one computer with three SQL Server instances. This report also happens to be three pages long, so you have to trust me when I say that there are more computers within this report.
Why Enhansoft Adds the Total Number of Computers in a SSRS Table
Most of Enhansoft Reporting’s reports note total amount details about significant objects above the table.
In the report example that I’m using in this post, about a specific database compatibility level, you see the total number of SQL databases listed above the table. Besides knowing this information, our customers also want to know, “How many computers are in this report?” Who wants to manually count each row to see the total numbers of that? Not me! See below for detailed instructions on how we display this information.
CountDistinct
As you saw in last week’s post, you can use the Count aggregate function to see how many rows are in a table, but as I told you earlier, in my example there are lots of rows for one computer. This is where the aggregate function CountDistinct comes into play. As its name implies, it counts all distinct values within a column. Therefore, if I use CountDistinct with the computer column, I end up with the total number of computers in a SSRS table.
By the way, I am using the same SQL Server report from Enhansoft Reporting that Leonard used in his blog post because it lets me demonstrate these two different, but very similar aggregate functions.
How to Add the Total Number of Computers in a SSRS Table
Let’s check out, again, the report on the preview page (see below). At the moment, it tells me the number of databases only.
Let’s get started by adding the count of computers.
On the Design layout tab, I select the existing text box where the, “Total Number of SQL Databases,” already exists. I right-click on it and then choose the option Expression…
Expression
=string.format(“Total Number of SQL Databases: {0} Total Number of Computers: {1}”,
Count(Fields!Database_Name.Value, “ES_DatabaseCompatibility_List”),
CountDistinct(Fields!PC_Name.Value, “ES_DatabaseCompatibility_List”))
Now, I simply replace the existing expression by pasting the expression above into the area provided. This new expression updates the text box to display both the total number of SQL databases and the total number of computers.
Is there anything that you should note about the equation above? There is nothing special about it or its text other than the {0} and {1} found in line one. These represent the values which are listed in order after the string (found in line two and three).
Follow the same steps listed in the previous blog post in order to update your text box with your columns from the datasets.
You might ask, “Why is the number of databases listed first and the number of computers listed second in the text box for this report? That is a great question! We picked databases to be listed first because that is what the report is all about. In the table, however, it made more sense to list the computer information first before the database details.
Previewing the SSRS Report
Please remember to test your updates by previewing the report. Once everything looks right, click on Preview to run the report.
Now, both the total number of databases and computers are listed in our report. Success! Wasn’t that easy? Once you get the hang of how to add the total number of computers in a SSRS table – and the number of rows, you can let your imagination run wild by leveraging aggregate functions and a simple text box!
By the way, if you need help doing the same with Power BI reports, check out Leonard’s post.
For any questions you can reach out to me on Twitter @GarthMJ.