Can I allow a user to select nothing a multi-valued parm?

Question

Can I allow a user to select nothing a multi-valued parm?

FunkyDexter

Hall of Fame

Points: 3907
More actions
October 6, 2016 at 7:09 am

#330732

We have a report where a user can search for persons.
One of the parameters allows the user to select which indicators a person needs to have in order to be returned. There are six possible indicators (IDs 1 to 6) and the person should have all of the indicators a user selects in order to be returned (they can have others too but they must have ALL the ones the user selects). I've got that bit working fine.
However, there is also a need for the user to be able to select nothing in this drop down and the have the report effectively disregard this parameter. So for example, if the user entered a surname of Smith and choose nothing in the indicators list, they should get all the Smiths in the database regardless of what indicators they have.
I'm really looking for a way of allowing a use to select none of the items in a multi valued parameter but when I do this I get a message asking to provide a value for the parameter. I've tried setting it to allow blanks but still get the message. Setting a multi valued parameter to allow Nulls is not allowed.
NB this is not the same as having them Select All because, in this paradigm, that would be the equivalent of searching for Persons who had all of the possible indicators.
P.S. Apologies in advance for not providing sample data but 1. this question's not really data dependent and 2. I'm getting a weird problem when I try to post with a script at the moment. I'm hoping that by keeping this post nice and simple, it'll post successfully. Fingers crossed.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

FridayNightGiant SSCertifiable Points: 7151 More actions · Answer 1

Are you passing the variable to a stored procedure or to a dataset in the report?

Off the top of my head you could add an option in your multi select variable called None or Null. If this was placed at the top of the list and had a value of -1 then the following will return Null if selected. I pass the parameter to a stored procedure and split it here so I'm not sure if this will work when passing to a dataset on the report.

=IIF(Parameters!ReportParameter1.Value(0)=-1,Nothing,Join(Parameters!ReportParameter1.Value,","))

Of course this means that if they select all or select other items and forget to un-select the None option then it will always return null. To get round this you could use the .Count option to count the parameters selected. If greater than 1 then skip testing for the -1 value.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

This is pretty simple. You would just add a <None> option to your parameter drop-down; have a look at this:

-- table that provides the parameter options

DECLARE @yourtable TABLE (val varchar(10));

INSERT @yourtable VALUES ('xxx'),('yyy'),('zzz');

-- for your parameter's datasource add a "<None>" value to your parameter options

SELECT val = '<None>'

UNION ALL

SELECT val

FROM @yourtable

ORDER BY val;

This should get "<none>" at the top; you could also do something like: ORDER BY REPLACE(val,'<None>',char(1))

Then, in the query that uses that/those parameter values you would add this condition to your WHERE clause:

OR @parameter = '<none>'

or, because it's a multi-select you could also do this:

OR @parameter LIKE '%<none>%'

Note: my code examples got messed up < is a "less than", > is a "greater than"

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

FunkyDexter Hall of Fame Points: 3907 More actions · Answer 3

Hi Guys and thanks for the replies.

I had already considered adding a "None" option but that doesn't seem right from a user's perspective. On a multi select it would mean a user could select "None" and another option(s) at the same time which seems nonsensicle. It'll do as a fallback option but it's not really what I'm looking for. I'm really looking to somehow allow the user to not select anything, which is not really the same as selecting "None".

Sorry if that comes across as ungrateful. I'm honestly grateful for any suggestions I get.

FD

Edit> As I read this back I realise I haven't been clear. I'm talking about a Multi-Select parm rather than multi-value. So the Checked ListBox rather than the single select drop down. Apologies if I was unclear.

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 4

Add extra parameters with Ignore IDX with "No"/"Yes" (with default No) and adjust the dataset for it?

FunkyDexter Hall of Fame Points: 3907 More actions · Answer 5

Sorry but I don't understand what you're saying there. Could you elaborate for me?

I don't see an option for Ignore IDX.

Thanks.

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 6

ID1 value X

ID2 value Y

ID3 value Z

ID4 value A

ID5 value B

ID6 value C

Ignore ID1: N

Ignore ID2: N

Ignore ID3: N

Ignore ID4: N

Ignore ID5: Y (do not filter on B (the value of ID5))

Ignore ID6: N

FunkyDexter Hall of Fame Points: 3907 More actions · Answer 7

Oh, I see. I'm not mad keen on that because it suffers from the same issue as adding a None option: it allows the user to make nonsensical selections. What does it mean when the user selects both ID1 and Ignore ID1?

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 8

That means you don't filter the value of ID1

and only filter on ID2,3,4,5,6

Something like

where (@IgnoreID1='Y' or @ID1 in (......))

and (@IgnoreID2='Y' or @ID2 in (......))

...