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...

Comments

• Anonymous
  January 10, 2006
  Ok I am so confused trying to get parameters to work...please help me this was so easy in crystal...could you email me marty@teamresnick.com...my basic question I created a report parameter ot ask for a date...then I want to use a field that uses that date in a where clause in my dataset...I am so confused :)
• Anonymous
  January 13, 2006
  I responded to you offline.
• Anonymous
  February 09, 2007
  Hi, I encounter a parameter problem that confuses me badly. I have a Web App, say App1, and browse the server report, say Report1, through Report viewer. I pass a parameter (P1)value from App1 to Report1. at the report side, I create a dataset, say DS1, against the P1's value. Then I create second parameter P2 whose value comes from DS1, and I set its default value as DV1 (it could be Null, or querying from DS1 or a constant).The problem is here, when the report is run, @P2 can show correct value list in its dropdown, but after I select the values  and click 'view report', the @P2 value come back to the default one, DV1, and the report comes out against the DV1 (not what I selected).Could you tell me if I miss sth?Thanks
• Anonymous
  September 01, 2013
  Hi, I have a problem with snapshot that, I can't filter it dynamically when calling from webapp. Actually the case is, I have few parameters set to default in order to get snapshots, which is just working fine. But the requirement is this snapshot needs to be filter to subset depending on user giving dynamic filters from webapp. is there any way of designing filters on the snapshot dynamically? Any help would be greatly appreciated. Thanks, garry