Optional parameter with input parameter, how do that?

Question

I have one input parameter and two multivalued parameters. I will set the two multivalued parameters as optional. Is there any way to get the results from input in the report without selecting the two multivalued parameters? I tried this one, but it doesn't work: Link

That should work fine. Can you explain what sorta error you receive if any? The basic idea is to have your multivalued parameters set to allow null values (unless you use another sentinal value), then you give multivalued parameters a null value with a display text of "All" or whatever verbage fits in the context, then ignore the value in your query when its null. This in effect makes the parameter optional, especially if you default the parameter to your 'All' value. — Mark W
hi thx for comment. I get an error "The 'blbala' parameter is missing a value". In where-Clausel I did so: Where( (@Employee_ID IS NULL) OR (Employee_ID IN (7750,2250)) — Pera
by the way, the two multivalued parameter are depented. It likes Country and location. If you choose england than you get all location (london, etc.) or you choose germany (munich, hamburg etc.) .I will get the value per default. — Pera

Mark W Mark W · Accepted Answer · 2013-05-01T15:13:10

So with countries in mind, ill try to do a quick example. I realize this isnt a multivalued param solution, but it does what you need. In the RDL you will define two parameters, one for Country, and another for City or whatever. You could then populate the country parameter from a stored procedure, and use the Name as the display text, and the ID as the value. (If you set the default value to null, All will be the first item selected)

 SELECT C.Name, C.ID From Countries AS C
 UNION
 SELECT 'All', NULL

enter image description here

Then, you populate the City parameters values with another procedure which uses the selected value for the country parameter (This creates a forward dependency, so make sure that Country appears before City in the parameters list or you will get an error when building the report).

 DECLARE @Country_ID int
 SELECT C.Name, C.ID FROM Cities AS C 
 WHERE (C.Country_ID = @Country_ID)
 UNION
 SELECT 'All', NULL

Now you have your procedure which returns the result set for the report accept both a Company, and a City ID parameter and in your where clause you will have something like this:

 DECLARE @Country Int,
         @City Int

 SELECT * FROM X
 WHERE  (X.Country_ID = @Country_ID OR @Country_ID IS NULL) AND
        (X.City_ID = @City_ID OR @City IS NULL)

Obviously you will have to change this a bit to fit your query, but this should allow the user to pick just a country, or a country and a city, or none at all.

PS. Dont forget to check the "Allow Null Value" checkbox for the paremeters in question if you plan on using null as your sentinel value.

enter image description here

Optional parameter with input parameter, how do that?

1 Answers