Report Builder CountRows: Count Rows in a Group

In Microsoft Report Builder, count rows in a group with =CountRows("GroupName"), replacing GroupName with the tablix group’s actual name. Put the expression in a textbox within the group, such as its header or footer; an explicit group scope makes clear which group’s rows are being counted.

What CountRows counts

CountRows is a built-in aggregate for paginated reports. It returns the number of rows in the scope you specify. In a grouped tablix, the scope can be the name of a row or column group, so the result is the number of rows belonging to that group instance—not the number of groups in the report.

For example, imagine a tablix grouped by product category, with one detail row for each sale. If a category contains three sales records, the group-scoped expression returns 3 for that category. Another category with eight detail records returns 8. The aggregate is evaluated separately as the report processes each instance of the group.

That is different from counting distinct categories. If there are five category groups, a group row count does not return 5; it reports each group’s own number of rows. The scope determines what set of rows is being counted.

Enter the expression in Report Builder

In Design view, find the tablix and the group whose rows you want to count. The Row Groups or Column Groups pane can help identify the group.
Select a textbox in that group’s header or footer. If the tablix does not have a suitable cell, add a group header or footer using the group’s context menu.
Open the textbox’s expression editor, commonly by right-clicking it and choosing Expression, or by selecting the expression button for its Value property.
Enter =CountRows("GroupName"), using the group’s real name, and confirm the expression.
Preview the report and check several groups against their detail rows.

To find or change the name, right-click the group in the grouping pane and open Group Properties. The group’s name is shown on the General page. Use that name in the expression, not the field used to group the data and not necessarily the label displayed to report readers. A group could be named CategoryGroup while its grouping expression uses the Category field and its header displays “Product category.” Those are three different things.

Choose the right scope

The scope argument tells Report Builder which rows belong in the aggregate. For a group, it is a quoted string containing that group’s name. The scope must be a valid named scope for the report item where the expression appears. A dataset, data region, and group can each define a scope, but they produce different totals.

Group scope: counts rows for the current instance of a named group. This is usually the right choice for a per-group count in a group header or footer.
Data-region scope: counts rows across a tablix or other data region, rather than separately within each group.
Dataset scope: counts rows in a dataset scope, which may be broader than the rows displayed by one tablix.
Current scope: =CountRows(Nothing) asks for the row count in the current scope. Its meaning depends on where the textbox sits, so it can be less obvious to maintain than a named scope.

If the number unexpectedly repeats or appears to be a grand total, check the scope first. A textbox in a group row does not guarantee that an expression scoped to the whole data region will return a per-group number. Conversely, a group-scoped expression placed in a location that is not within a valid containing scope may produce an error.

A worked category example

Suppose a sales report has a dataset named SalesData, a tablix grouped by category, and one detail row per transaction. The row group is named CategoryGroup. A footer for that group can show a count with =CountRows("CategoryGroup"). If the category has four transaction rows in the group, the expression returns 4. The next category is counted independently.

For a count of every row in the tablix regardless of category, use the data region’s name as the scope instead, assuming that name is valid where the expression appears. If the data region is named SalesTablix, the corresponding expression is =CountRows("SalesTablix"). These two expressions answer different questions: “How many rows are in this category?” and “How many rows are in this tablix?”

Be aware that a displayed detail line is not always equivalent to one source record. A tablix can have nested row groups, column groups, or aggregate rows. Decide whether you mean source records, detail rows at a particular level, or rendered lines, then select a scope that matches that meaning. Verify the result with a small sample where you can count the underlying rows.

CountRows versus Count and distinct counts

CountRows counts rows in a scope. It does not test whether a particular field is populated. If a row has a blank or null value in a field, that row can still contribute to the row count.

The Count aggregate answers a different question: how many non-null values an expression has in the selected scope. For example, counting a customer ID field can return fewer than CountRows if some rows have a null ID. Use CountDistinct when the question is how many different values occur, such as the number of unique customers represented in a group. Check the aggregate’s scope and the field’s null behavior before comparing the results.

A common misconception is that “count the group” means count the distinct group labels. It does not. A group label identifies the group; CountRows counts rows within its scope. To report the number of groups, the report design or data needs to count group instances, rather than count the detail rows inside each instance.

How filters and hidden rows affect the result

Filters can change which rows are available at a given scope. A dataset filter, data-region filter, or group filter can affect the rows included in the relevant part of the report. If a count seems too low or too high, inspect filters at each level, including filters defined in Group Properties and filters on the tablix.

Visibility is not the same as filtering. Hiding a row or group through a visibility expression generally changes whether it is displayed, not whether its data is included in an aggregate. Therefore, do not assume that CountRows means “count only the lines a reader can currently see.” If the requirement is to count rows that meet a condition, use an appropriate filtered data scope or design an expression that explicitly accounts for the condition. Test the expression with both visible and hidden examples.

Pagination also does not normally change the logical group count. A group can continue across pages, but its scope remains the group instance; a page break is not automatically a new data group. If you need a count per page, that is a different reporting requirement and may need a different design.

Nested groups and detail rows

Reports often group data at more than one level—for example, region, then category, then transaction details. In that design, the scope you choose determines the level being counted. A category group count can show transactions in one category; a region group count can show rows across the region, depending on the tablix structure and the scope’s valid relationship to the textbox.

For a count in a nested group, use the name of the group whose rows you intend to count rather than assuming that the innermost visible row is the correct scope. A count in a category footer might need the category scope; a summary in a region footer might need the region scope. When an aggregate is placed inside a child group, a parent scope may be useful for a parent-level total. A sibling or child scope that is not valid from the textbox’s location may cause a scope error.

Group scope counts rows at that scope; it does not automatically count the number of child group instances. If a region contains three categories and each category contains several transactions, counting the region’s transaction rows is not the same as counting its three categories. State the intended unit—transactions, categories, or regions—before choosing an aggregate.

Common errors and how to troubleshoot them

Using the group expression instead of its name: If the group is based on Fields!Category.Value, do not put that field expression inside the scope string. Use the group’s name from Group Properties.
Misspelling the scope: Compare the quoted name with the group or data-region name in the report design. Scope is a name, not a display caption.
Putting the expression in the wrong row: A textbox in a static tablix row may not have the same scope context as a textbox in the group’s header or footer. Move it into the relevant group row or use a valid containing scope.
Counting the wrong object: Confirm whether you need a group count, a tablix total, a dataset total, a non-null field count, or a distinct-value count.
Comparing against the unfiltered source: Review dataset, tablix, and group filters before concluding that the aggregate is wrong.

When Report Builder reports that the scope parameter is invalid, first verify that the scope is a quoted, valid name and that the named group or data region contains the report item where the expression is used. If the expression runs but the number is surprising, verify the group level and filters before changing the expression.

Frequently Asked Questions

How do I count rows in each group in Report Builder?
Put =CountRows("GroupName") in a textbox in the group’s header or footer, replacing the quoted name with the actual group name. Preview the report to confirm the count for each group.

Can I use CountRows(Nothing) for a group count?
Sometimes. Nothing refers to the current scope, and that scope depends on the textbox’s location. Use the explicit group name when you want the expression to clearly target a particular group.

Why does CountRows return the same number for every group?
The expression may be scoped to the dataset or whole data region rather than the group. Check the scope name and make sure the textbox is in the intended group row.

Does CountRows include blank fields?
Yes. It counts rows, not nonblank values in a chosen field. Use the Count aggregate if you need to count non-null values, or CountDistinct for distinct values.

Does CountRows count hidden rows?
Rows hidden through visibility settings may still be part of the aggregate’s data scope. Filters affect the rows available to the scope; hiding a row is not the same as filtering it out.

Can I count rows in a group footer?
Yes. A group footer is a common place for a per-group count. Use the group’s name as the scope and confirm that the footer belongs to the intended row group.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.