CODEWITHSUNDEEP
reporting-servicesssrs-2008
TheToddmonster
TheToddmonster

Reputation: 23

If one value is returned for a parameter then set as default, if more than one value is returned do not select a default

We have a series of reports which return a set of values for a parameter based on the userID. This works and we're happy with the way it works.

Now we need to implement a default parameter setting. The logic being

If there is only one value in the parameters available dataset, then set that as the default.

If there is more than one value in the parameters available dataset, then leave the parameter blank.

This is what I have so far - I know I have the following issues:

-Parameters cannot read fields, therefore I need the expression to look at the dataset as a whole.

-I'm unsure what my then statement should be to allow the user to review all available values without them being selected. 

=IIf(CountDistinct(Fields!storekey.Value, "UserStoreVerification")) = 1, First(Fields!storekey.Value, "UserStoreVerification")," ")

Upvotes: 1

Views: 4316

Answers (2)

Nathan Griffiths
Nathan Griffiths

Reputation: 12756

You can create a separate dataset to populate the "default values" for the parameter. In this dataset you can add logic to count the number of rows that would be returned by the other dataset that provides the parameter values. If there are greater than 1 values returned by the first query then the second dataset just returns NULL (i.e. no default values are selected).

Example

If your original dataset for parameter values (e.g. "dsParamProduct") used a query like this:

SELECT ProductNumber 
FROM dbo.Product
WHERE Available = 'Yes'

Then the dataset query for the default values (e.g. "dsParamProductDefault") could be something like this:

 DECLARE @ValueCount INT

 SELECT @ValueCount = COUNT(*)
 FROM
 (
    SELECT ProductNumber 
    FROM dbo.Product
    WHERE Available = 'Yes'
 ) vals

 IF @ValueCount = 1
    SELECT ProductNumber 
    FROM dbo.Product
    WHERE Available = 'Yes'
 ELSE 
    SELECT NULL

Supplying "NULL" as the default value when there is more than one value will mean none of the available values are selected and therefore the user will have to manually select them (assuming that NULL isn't a valid value for your parameter - if it is then make sure the default query will return something else that is definitely not valid). If there is only one possible value then the default value query just returns the same result as the parameter values dataset, which means that the parameter value will be selected.

Upvotes: 1

djangojazz
djangojazz

Reputation: 13232

  1. Set up another parameter that is dependent on the first, same type but slightly different name, and do your code at bottom with one suggested change:. Change " " at the end before the parenthese end to be 'NOTHING' instead. I believe this is interpreted by SQL as NULL which is what you want.

  2. Now you should be getting population of the parameter so I would debug and check it by just dragging and dropping it to the design surface and it should be black if you have more than one default value. You can optionally make this parameter 'hidden' once you can confirm it works.

  3. Now you trick your main dataset with a nifty predicate (or else use some other logic if it suits you better)

    Where value = isnull(@DependentParam, value)

Basically this is stating "if the parameter is not null use it, else equate the clause to be everything as it will assume value = value".

Upvotes: 0

Related Questions