how to define a parameter in reporting services to choose to show/hide not null/null values?

Hi All,

I am using SQL Server Reporting Services 2005 and in my report I want to create a parameter called "hasEmail" with 3 possible values (Yes, No, Both) to show/hide the customers who have/don't have emails or both.

Can anyone please help?

Try using a filter with the following entries. This assumes that there is an "Email" field of type string in the data set you are using, and the parameter name with the three values is ShowOnlyWithEmail. We use Len instead of IIF and a check for Nothing, because =IIF(IsNothing(Fields!Email.Value), 0, Fields!Email.Value.Length) will throw an exception if the field value is Nothing (IIF is a function and all arguments are evaluated before the function call), and Len will return 0 if the field value is Nothing.

ExpressionOperatorValue=Len(Fields!Email.Value)
>=IIF(Parameters!ShowOnlyWithEmail.Value="Yes", 0, -1)=Len(Fields!Email.Value)
<=IIF(Parameters!ShowOnlyWithEmail.Value="No", 1, Int32.MaxValue)

If the parameter is set to "Yes", then the first filter entry will remove any rows that do not have an email address and the second will not filter any rows.
If the parameter is set to "No", the the first filter entry will not filter any rows, and the second will filter all rows with a length less than 1.
If the parameter is set to "Both", then both filter entries will not filter any rows.

Ian|||It works fine. Thank you. What does LEN stand for? (Like REM is remove).|||Len is a function from VB to determine the length of an string (or size of any object) that does not blow up if a null value is passed in.