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 Add Parameter-Choose Available Values-Non queried- there you can put in the label and values that you want to return (Yes,No,Both) Then you will have to use these parameters to filter your dataset. Hope that helps.|||

You said : "Then you will have to use these parameters to filter your dataset."

The problem is the parameter doesn't exactly match the field. For example, it's not like the case that: OK the parameter chosen by user is "Bicycles" so only show me the data (WHERE the param is Bicycle). The problem I have is that some of the customers have provided their emails in the database and some haven't. I want to be able to show (or not show) the customers that have (or have not) email address. Maybe I should use "EXISTS" or something, because like I said it's not a matter of exactly matching the string (bicycle for example) with the field; It's a matter of true/false if the email exists or not.

I don't know.

Any thoughts?

|||

Yes, right click table or list- if thats what you are using - properties- filters- I don't know about your particular case but here is one that I used a parameter to filter in- same concept, but how to apply to your case I'm not sure- you'll have to play with it.

(Expression)=Fields!CONT_FREQ_CODE.Value

(Operand) =

(Value) =iif(Format(Parameters!Report_Parameter_0.value,"MM")=3 or Format(Parameters!Report_Parameter_0.value,"MM")=6 or Format(Parameters!Report_Parameter_0.value,"MM")=9 or Format(Parameters!Report_Parameter_0.value,"MM")=12,Fields!CONT_FREQ_CODE.Value,"M")

|||

Thanks Kimberly,

I found my answer at

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=494455&SiteID=1

Thank you anyway.

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.

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.