Last updated on August 6th, 2022 at 04:02 pm
Over the last six months or so, I published a number of blog posts about many different types of finishing touches you can add to your reports and this post is no exception. Have you ever looked at a report, especially one with multiple pages, and wondered, “How many computers are in this report?” If you answered, “Yes!” then wouldn’t it be nice to know at the outset what the total number of computers (or rows) are within a report? In this blog post, I will show you how to incorporate this finishing touch by adding a row count to your report. This is something that can be easily done and it will help take your reports to the next level.
Sure, you might be thinking, but why not count each row within a report and make a note about how many computers are affected by a particular issue? I know from my own past experiences that you don’t always have that kind of time. Also, immediately knowing the number of computers affected by an issue helps me understand how big of a problem I have on my hands. For example, I can see from the screenshot below that only 11 computers have unknown software update scan results, so this tells me that it’s the computer itself that is the problem. If the total number of computers, however, was my entire lab then I would look elsewhere for the underlying problem.
Row Count Expression
In all of my reports, I try to make my life easy by only using Netbios_Name0 for my computer’s name. I also always use SCCM2 as my primary dataset. So, for me, I can cut and paste the expression below into my Row Count text box and I’m done. Since this will not work for you, I will show you how to edit this expression later on in this post, but first, what does this expression do?
=string.format(“Total number of PCs: {0}”,count(Fields!Netbios_Name0.Value, “SCCM2”))
This portion of the expression count(Fields!Netbios_Name0.Value, “SCCM2”) will return a count of Netbios_Name0 within the dataset SCCM2.
This portion, string.format(“Total number of PCs: {0}”, will display the text between the quotes, replacing the {0} with the results of the count listed above.
Adding a Row Count
Using an existing report, I will add the row count above this table/matrix. Throughout this post, I will refer to both a table and a matrix simply as a table. I will do this for convenience purposes only.
I start by moving the table down by two spaces.
You can move the table down by two spaces simply by selecting the table and using the down arrow. Each click of the down arrow is a space, so in this scenario I clicked down twice.
Next, drag and drop a Text Box from the Toolbox into the empty space you created. My preference is to make the text box 3.75 in x 0.25 in. This allows the text to be displayed without the need for word wrapping.
Tip
Make sure that the text box does NOT overlap any other objects within your report. Doing so can cause problems later on when you render the report. Additionally, you will get a warning message in Visual Studio if there is any overlap.
Next, select the text box, right click on it and select Expression…
Here I copy and paste the row count expression I showed you in the Row Count Expression section.
This is what the text box should look like after pasting the expression.
Now to edit the expression. First, remove the count(Fields!Netbios_Name0.Value, “SCCM2” from the expression text box. Make sure that your cursor is between the comma and closing bracket. Select Datasets under Category. The first item is selected for you by default. If you have more than one dataset, make sure that you select the appropriate dataset under the Item area.
In the Values area, double-click on the value you want to count. Your expression text box should look similar to the one above.
Now replace the word, “First,” with the word, “count.” I highlighted it in the section above. Click OK to close the expression text box window.
Finally, preview the report. If everything is done correctly, your report will now have a row count listed just above the table. Don’t forget that you might need to align the text to the left.
Summary
I hope this tip demonstrates how adding the row count to a report is helpful. This is one of those little gems that I suggest including in all of your reports. In the long run, you will thank yourself!
If you have any questions about adding a row count to a report, please feel free to contact me at @GarthMJ.