Geeks With Blogs
O(geek) Ryan Cain, running in time proportional to Geek.

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:

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.


Posted on Thursday, June 26, 2008 4:15 PM | Back to top

Comments on this post: SSRS Global Variable "Trick"

# re: SSRS Global Variable "Trick"
Requesting Gravatar...
Great post! thanks a lot.
Left by Sandesh on Sep 13, 2009 2:28 PM

# re: SSRS Global Variable "Trick"
Requesting Gravatar...
You are the man. This was just the simple solution I needed.
Left by HeyThereLameMan on Apr 11, 2013 12:54 PM

# re: SSRS Global Variable "Trick"
Requesting Gravatar...

I used your code as you have given. However, the passedCount value does not seem to increment, although I confirmed that the function was correctly being called because it returned the Boolean value back.

Am I missing anything?
Left by MK on Jul 12, 2013 11:25 AM

# re: SSRS Global Variable "Trick"
Requesting Gravatar...
@MK - what you are missing is what I was missing.
The "global" variable passedCount needs to be declared as 'public shared'...

so: public shared dim passedCount as int32 = 0

this resolved my issue with the assignment statement in the function not updating the external public variable.
Left by stevo on Sep 25, 2013 8:11 PM

# re: SSRS Global Variable "Trick"
Requesting Gravatar...
Thank you very much for the post and thank you stevo. This has worked for me :)
Left by Paradox on Nov 24, 2015 3:51 PM

Your comment:
 (will show your gravatar)

Copyright © Ryan Cain | Powered by: