SQL Report Builder - Multiple Parameter

Question

I have searched and tried so many different things and to me the problem seems to be straight forward.

I have a DataSet that the user can refine by selecting a dropdown of Yes or No - this looks up the database table and pulls out any records where the column value is Yes, the problem is when I look for No - I need this to return all values in the database so where the value is Yes or No.

Can someone please advise?

SELECT * FROM @TestTable Where Show=@Show

I have set up my Parameter and the Avaliable Values are

Yes - with a value of YES
ALL - with a value of IS NOT NULL - this ends up showing nothing

EDIT -

TestTable has 2 Columns Name | Show

Values are:

Name 1 | Yes
Name 2 | Yes
Name 3 | No
Name 4 | Yes

My report has the Parameter @Show whwen the report loads they are to choose to display Permanent staff only - if they select Yes then it would pull out

Name 1
Name 2
Name 4

If they select No i need the report to show

Name 1 
Name 2
Name 3
Name 4

Your question does not make sense. Show us some example data and expected output. — Adriaan Stander
I need the Report Parameter to pass IS NOT NULL to the query, but I think the way it is doing it from Report Builder is SELECT * FROM @TestTable Where Show='IS NOT NULL' — user3219693
Yes, that is probably what it is doing. So if you had the query to be SELECT * FROM @TestTable Where (Show=@Show and @Show='Yes') OR (@Show<>'Yes') would that not work? — Adriaan Stander

Bjarke Brask Rubeksen Bjarke Brask Rubeksen · Accepted Answer · 2014-01-31T11:38:41

You really need to add some more info on your problem. But you could use an If statement in your sql or in your server scripting.

MsSql : http://technet.microsoft.com/en-us/library/ms182717.aspx

MySql : http://dev.mysql.com/doc/refman/5.1/en/control-flow-functions.html

You could also do something like this :

SELECT * 
FROM MY_TABLE 
WHERE @parameter IS NULL OR Show = @parameter;

SQL Report Builder - Multiple Parameter

1 Answers