How Do You Add the Total Number of Computers in a SSRS Table?

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?

Add the Total Number of Computers in a SSRS Table - 1 Computer with 3 SQL Server Instances

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.

Add the Total Number of Computers in a SSRS Table - 1 Computer with 3 SQL Server Instances

Let’s get started by adding the count of computers.

Add the Total Number of Computers in a SSRS Table - Design Tab

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.

Add the Total Number of Computers in a SSRS Table - Expression Window

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

Add the Total Number of Computers in a SSRS Table - Preview Tab

Please remember to test your updates by previewing the report. Once everything looks right, click on Preview to run the report.

Add the Total Number of Computers in a SSRS Table - Updated 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.

Showing 2 comments
  • Paul Edwards
    Reply

    Hey Garth,

    I tried this out today on one of my SSRS reports, but I get the following error when I try to run it:
    “The Value expression for the textrun ‘Textbox6.Paragraphs[0].TextRuns[0]’ contains a colon or a line terminator. Colons and line terminators are not valid in expressions.”
    Sure enough, if I drop the colon form the text string it works! Not a big deal, but how come it does this, but works OK for you?
    Here’s the expression I am using:
    =string.format(“Total Number of Collections: {0}”,
    Count(Fields!Collection_Name.Value, “DataSet1”))

    • Garth Jones (Admin)
      Reply

      Hi Paul. I hope that you had a create thanksgiving!

      I copied and pasted your expression above and tested within my lab. It works fine after change the column to use. 🙂
      =string.format(“Total Number of Collections: {0}”,
      Count(Fields!PC_Name.Value, “SCCMR2”))

      The things to watch out for are quotes, make sure that your are NOT use the stylized quotes!

Leave a Comment