Optional Report Parameter? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Optional Report Parameter?

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2010-02-24 : 23:11:19

Hi all -

I have a report with 7 parameters that the user can select. Four of the parameters are absolutely needed, but the last three are not.

I have coded my query to use ISNULL(@param4, columnName) to handle if NULLs are passed in for the last three columns/parameters.

However, the SSRS interface seems to require the user to select a value from the parameter drop-down list even though I have set them to "Allow null value" and I've even tried a Default value. No matter what I do, the user is always required to select something even though they really don't need to for these last three parameters.

Is there anyway to solve this?

Thanks,
- will

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-02-24 : 23:30:49

Hi,

It seems that you have defined the report type as String and the available values are in drop down list against the parameter.

In this case only workaround i know is that you need to pass some default value for the parameter ( and not null).

Regards,
Bohra

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-02-25 : 01:17:23

You can have the top element (in the list of available values for the parameter) as single space (both lable and Value ). Specify the same as default value.

In the sp you can trim the parameter and see whether any value is passed or not.

I tested this approach in my local environment and its working (It is not forcing me to select the parameter).

Another advantage of single space approach is that if user by mistakenly selected a value, he can deselect the same by selecting the top element (element with space) which is not allowed in other case.

Regards,
Bohra

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2010-02-25 : 01:21:16

quote:
Originally posted by pk_bohra

You can have the top element as single space. Specify the same as default value.

In the sp you can trim the parameter and see whether any value is passed or not.

I tested this approach in my local environment and its working (It is not forcing me to select the parameter).

Hi. Thanks for reply, but I'm really not sure what you are trying to convey in your resposes. They really don't seem to have anything to do with my question/problem. Or, maybe I am just not understanding?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-02-25 : 01:38:36

quote:
Originally posted by dhw

quote:
Originally posted by pk_bohra

You can have the top element as single space. Specify the same as default value.

In the sp you can trim the parameter and see whether any value is passed or not.

I tested this approach in my local environment and its working (It is not forcing me to select the parameter).

Hi. Thanks for reply, but I'm really not sure what you are trying to convey in your resposes. They really don't seem to have anything to do with my question/problem. Or, maybe I am just not understanding?

Hi,
Let me explain what i understood by your initial post.
You have seven parameters in the RDL File. To repeat RDL file.
Out of that user needs to select only 4 parameters and selection of rest 3 is at user's wish.

In the same post you have mentioned that you have tried "Allow nulls" and even specified default value but still user is forced to select one of the values (this field is one of the three optional fields)

If my understanding is correct then i can explain my solution. If my understanding is wrong, then i am sorry for confusion.

Regards,
Bohra

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2010-02-25 : 01:52:08

quote:

Let me explain what i understood by your initial post.
You have seven parameters in the RDL File. To repeat RDL file.
Out of that user needs to select only 4 parameters and selection of rest 3 is at user's wish.

In the same post you have mentioned that you have tried "Allow nulls" and even specified default value but still user is forced to select one of the values (this field is one of the three optional fields)

If my understanding is correct then i can explain my solution. If my understanding is wrong, then i am sorry for confusion.

Hi -
I think that you have my question correct. When you say RDL file, you mean the report, right? So, yes I when the user is presented with the 7 parameter drop-down lists, I would like it if the user only had to be forced to select the 4 required ones and not have to even select the 3 optional ones.

Thanks

Kristen
Test

22859 Posts

Posted - 2010-02-25 : 02:01:11

" I have coded my query to use ISNULL(@param4, columnName) to handle if NULLs are passed in for the last three columns/parameters"

Separate issue, but I assume you mean you are doing:


WHERE ...
    AND columnName = ISNULL(@param4, columnName)

if so beware that that will NOT select rows where columnName IS NULL (obvious not a problem if ColumnNmae is defined as Not Null). If you need to allow NULL values for ColumnName then you need:


WHERE ...
    AND (@param4 IS NULL OR columnName = @param4)

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-02-25 : 02:09:19

Hi,

By RDL I mean Report. SSRS reports are commonly called as RDL file also (atleast in my office

)

You have seven dropdown list.
When you specify some values in "Available Values:" pane of "Report parameters" window of the report then only it appears as drop down list.

You are having a static list or the values in the dropdown list are generated from any query ?

It it is a static list then add one more value in the "Available Values:" list and it should be a space. Both Label and Value should be a space and should be moved to top position.

In the same form you will see "Default Values:" box also.
In that select the option "Non-Queried" and enter a single space in the value text box.

Now just check whether this workaround works for you or not..

Regards,
Bohra

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2010-02-25 : 02:25:38

To answer the question regarding the parameter drop down lists, some are generated from a query and a couple are from a static list.

So, it sounds like you are saying that I have to add some sort of dummy row/value to each of the lists (a space) and also set the Default value for the parameter to also be a space.

That sounds easy enough for the static lists and I guess for the queries, I'd have to simply add a UNION with the space added also at the top.

Interesting work around. I'll give it a try tomorrow and let you know how it works.

thanks
- will

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-02-25 : 02:53:57

quote:
Originally posted by dhw

To answer the question regarding the parameter drop down lists, some are generated from a query and a couple are from a static list.

So, it sounds like you are saying that I have to add some sort of dummy row/value to each of the lists (a space) and also set the Default value for the parameter to also be a space.

That sounds easy enough for the static lists and I guess for the queries, I'd have to simply add a UNION with the space added also at the top.

Interesting work around. I'll give it a try tomorrow and let you know how it works.

thanks
- will

Remember one additional advantage of this approach:
If user by mistakenly selected a value, he can deselect the same by selecting the top element (element with space) which is not allowed in other case.

Hope the suggested workaround helps you.

Regards,
Bohra

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-25 : 09:41:51

quote:
Originally posted by pk_bohra

quote:
Originally posted by dhw

To answer the question regarding the parameter drop down lists, some are generated from a query and a couple are from a static list.

So, it sounds like you are saying that I have to add some sort of dummy row/value to each of the lists (a space) and also set the Default value for the parameter to also be a space.

That sounds easy enough for the static lists and I guess for the queries, I'd have to simply add a UNION with the space added also at the top.

Interesting work around. I'll give it a try tomorrow and let you know how it works.

thanks
- will

Remember one additional advantage of this approach:
If user by mistakenly selected a value, he can deselect the same by selecting the top element (element with space) which is not allowed in other case.

Hope the suggested workaround helps you.

Regards,
Bohra

it would make more sense to make this additional option names <No Selection> for clarity rather than keeping it as a space

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2010-02-26 : 01:25:47

quote:
Originally posted by pk_bohra

quote:
Originally posted by dhw

To answer the question regarding the parameter drop down lists, some are generated from a query and a couple are from a static list.

So, it sounds like you are saying that I have to add some sort of dummy row/value to each of the lists (a space) and also set the Default value for the parameter to also be a space.

That sounds easy enough for the static lists and I guess for the queries, I'd have to simply add a UNION with the space added also at the top.

Interesting work around. I'll give it a try tomorrow and let you know how it works.

thanks
- will

Remember one additional advantage of this approach:
If user by mistakenly selected a value, he can deselect the same by selecting the top element (element with space) which is not allowed in other case.

Hope the suggested workaround helps you.

Regards,
Bohra

Thanks again. Yes, this worked for me. I ended up using "All" instead of a blank space.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-02-26 : 03:58:55

Welcome

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-26 : 08:34:27

quote:
Originally posted by dhw

quote:
Originally posted by pk_bohra

quote:
Originally posted by dhw

To answer the question regarding the parameter drop down lists, some are generated from a query and a couple are from a static list.

So, it sounds like you are saying that I have to add some sort of dummy row/value to each of the lists (a space) and also set the Default value for the parameter to also be a space.

That sounds easy enough for the static lists and I guess for the queries, I'd have to simply add a UNION with the space added also at the top.

Interesting work around. I'll give it a try tomorrow and let you know how it works.

thanks
- will

Remember one additional advantage of this approach:
If user by mistakenly selected a value, he can deselect the same by selecting the top element (element with space) which is not allowed in other case.

Hope the suggested workaround helps you.

Regards,
Bohra

Thanks again. Yes, this worked for me. I ended up using "All" instead of a blank space.

good

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ezaz
Starting Member

2 Posts

Posted - 2010-03-12 : 07:26:28

Hi,

Am trying to open .rdl reports from my web application(aspx) using reportviewer. My problem is, i have 3 parameters on the report. user can select either one or ALL to generate the report.

But in my report design how can i handle this under WHERE clause. Cause i don ot know if user gonna pass 1 or 2 or all parameter values.

Plz help.

Thanks in advance.
ME

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-12 : 11:21:47

quote:
Originally posted by ezaz

Hi,

Am trying to open .rdl reports from my web application(aspx) using reportviewer. My problem is, i have 3 parameters on the report. user can select either one or ALL to generate the report.

But in my report design how can i handle this under WHERE clause. Cause i don ot know if user gonna pass 1 or 2 or all parameter values.

Plz help.

Thanks in advance.
ME

make the parameters default value as NULL and use where like this


WHERE (Field1=@Param1 OR @Param1 IS NULL)
AND (Field2=@Param2 OR @param2 IS NULL)
AND (Field3=@Param3 OR @Param3 IS NULL)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ezaz
Starting Member

2 Posts

Posted - 2010-03-15 : 04:55:47

Hi Visakh16,

Thank you for hte reply.

But my query still having errors in WHERE clause.

SELECT * FROM table_name WHERE @Parameter1 IS NULL OR Field1 = @Parameter1;

Do i need to make any changes to the parameter design itself OR does it need specific expression while assigning the values... like IIF(ISNOTHING(Parameter1.Value),"null",Parameter1.Value).

Kindly assist. I have to validate my query against below 3 parameters which can be optional.
1. status
2. start date - end date
3. supervisor

Thanks in advance.

ME

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-15 : 05:01:50

quote:
Originally posted by ezaz

Hi Visakh16,

Thank you for hte reply.

But my query still having errors in WHERE clause.

SELECT * FROM table_name WHERE @Parameter1 IS NULL OR Field1 = @Parameter1;

Do i need to make any changes to the parameter design itself OR does it need specific expression while assigning the values... like IIF(ISNOTHING(Parameter1.Value),"null",Parameter1.Value).

Kindly assist. I have to validate my query against below 3 parameters which can be optional.
1. status
2. start date - end date
3. supervisor

Thanks in advance.

ME

hi,

quote:

But my query still having errors in WHERE clause.

what is the error u got.
Have u tried Visakh reply.see his reply.it should work
make the parameters default value as NULL and use where like this

WHERE (Field1=@Param1 OR @Param1 IS NULL)
AND (Field2=@Param2 OR @param2 IS NULL)
AND (Field3=@Param3 OR @Param3 IS NULL)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 09:48:09

quote:
Originally posted by ezaz

Hi Visakh16,

Thank you for hte reply.

But my query still having errors in WHERE clause.

SELECT * FROM table_name WHERE @Parameter1 IS NULL OR Field1 = @Parameter1;

Do i need to make any changes to the parameter design itself OR does it need specific expression while assigning the values... like IIF(ISNOTHING(Parameter1.Value),"null",Parameter1.Value).

Kindly assist. I have to validate my query against below 3 parameters which can be optional.
1. status
2. start date - end date
3. supervisor

Thanks in advance.

ME

you need to make the default values of above parameters null in report (make allow null true). and then in query behind use like


where (@status is null or yourstatusfield=@status)
and (@startdate is null or yourdatefield > = @startdate)
and (@enddate is null or yourdatefield <=@enddate)
and (@supervisor is null or supervisorfield=@supervisor)
..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources