We are working through a beta release of an internal project so I'm getting a chance to solve many different problems right now. Today I had to update a report that has an issue where its count at the bottom was displaying the total number or records instead of just those that were visible.
Basically this is a report of applicants who have passed all tests during a job recruitment process. If they fail any step then they shouldn't show on the list. I did some tricks with the data of the report to check to see if the failed any test and hide a rectangle if they have. This formula used a few aggregates to get the job done. Of course anyone who's used SSRS 2005 knows you can't do aggregates of aggregates, so that makes getting the count at the bottom much more difficult.
The trick I used was to create a "global" variable in the code of the report. I wrapped that variable in a Function that looked like:
public dim passedCount as int32 = 0
public function IncreasePassedCount(a as boolean, b as boolean) as boolean
if a or b then
return true
end if
passedCount = passedCount + 1
return false
end function
a and b are flags that specify whether or not there was a failure, since there's 2 possible ways an applicant could fail on a given row. If they have failed then we return true (so that way hidden = true, somewhat counter intuitive for web and windows programmers). Otherwise they have passed everything and we want to keep everything visible.
This code is then called via the Visibility expression on the rectangle that holds all the data. That expression looks like:
=Code.IncreasePassedCount(
Sum(Fields!PassedByScore.Value) <> Count(Fields!TestScoreId.Value),
Sum(Fields!PassedByFlag.Value) <> Count(Fields!TestScoreId.Value))
The IncreasePassedCount function serves double duty for me; a. it increases the passedCount variable as needed b. it returns a boolean to hide or show the rectangle. This way I can guarantee my code is being called only when it needs to be to increase my counter. If you try to call this method in an IIF statement the method always seems to get called, even when it is only in the true or only the false clause. I'm guessing this is just how SSRS resolves its code behind.
Now with that in place I have a very simple text box that has my total count, now based on what's visible not on all the records.
=Code.passedCount