Count rows of a filtered tablix in SSRS

Question

I want to show row count of my filtered tablix in a text-box inside my SSRS report header. I googled quite a bit but nothing helped.

Suggestion at this link gives me count but that is restricted to count of rows coming on current page. Even if my result has 2 pages, I get count of rows in first page only.

I also tried by adding a new column for row number and then show it's value in header's text-box but the problem remain same as above.

I tried using global shared variables in report(I am using custom code for filter), this helped upto an extent but they keep on accumulating value every-time I click view report button :(

Also please consider that moving filter criteria in stored procedure is not an option for me.

Thanks, Ravi

Answer 1

For me, the count did not work on the filtered dataset, it always (both for COUNT and COUNTROWS) returned the count of the original dataset.

Since I found no way to make it work, I ended up using CountDistinct, since the filtering is static and I know which data column value to count on.

=CountDistinct(Fields![Fieldname].Value)

Answer 2

Right click page outside of the white part and you will get to the ReportProperties section.

Adjust the page Height to something like 500 (you can make it as high as you want, it will only extend the page to the required height anyway) Click OK

Go back to the page, and right click above your tablix and click insert textbox.

Right click the textbox and click expression.

Paste =CountRows()

Click okay, Preview the report. Hope this is what you required.

Answer 3

The solution below worked like a charm for me. It's from a post by QuestionDude on the asp.net forum here:

1. Add a row to the bottom of the tablix outside of the detail group.
2. In any cell in this "footer" row, set the expression to =COUNT("Fields.[pick a field].Value"). Always paranoid, I used a unique field. Not sure it matters. Let's call that cell Textbox17.
3. Outside of the tablix, you can use the expression ReportItems!Textbox17.Value to display the row count of the filtered tablix.
4. Hide the footer row.

All credit to QuestionDude.