Another way to deal with defaults and snapshots in SQL Reporting Services
Here's an interesting scenario one of my customers had and how we approached it.
The customer filters his report by region and subregion using parameters. The @Region parameter is given a default value and then hidden: the cutomer uses this parameter so he can build multiple linked reports that filter using different regions.
As a twist, he also filters his dataset locally using the @subregion parameter. Because the query which his report calls is fairly expensive to run, he bases the report off of a snapshot. This is where the trouble begins.
To get the snapshot to work, both parameters must have default values - this isn't news to anyone. However, when you think about it, we don't really USE the @subregion parameter to do any filtering until the data has made it to the report anyway. However, we still need to provide a default value for this paramter, potentially generating a report which doesn't initially match the user's subregion.
What we need is a dataset filter condition that conditionally filters based on whether the user has *chosen* a subregion parameter value. We ended up coming up with a bogus @subregion parameter value (0) that we could set as the default. No subregion is actually identified by a value of 0, but we can key on it to decide whether or not to do our local dataset filtering.
Then, in the dataset filter dialog, we use good ol' iif():
Expression: =iif (Parameters!subregion.Value <> 0, Fields!subregion.Value,Fields!Region.Value)
Operator: =
Value: = iif(Parameters!subregion.Value <> 0, Parameters!subregion.Value, Parameters!Region.value)
So, if @subregion <> 0 (indicating a user has actually chosen a value in the UI), we filter the subregion field of the dataset against the @subregion parameter. If @subregion = 0 (bogus key value), we instead locally filter on Fields!Region.Value = Parameters!Region.Value. We know this expression will return ALL the records from the region without filtering on subregion at all...