• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 658
  • Last Modified:

SQL Reporting Services 2005 Passing NULL value when using Query drop down for parameter.

In the Report Parameters dialog, I have a Parameter which allows NULL values therefore I have ticked the Allow null value.  The parameter uses a query to get the list of valid responses when a user inserts this information.  However when viewing the report there is no way of passing a NULL value, if I change the parameter to a text box a null value checkbox is present.

Is there any way of using a query for a parameter and being able to pass a null value, without having to insert a row (for the query to pick up) and then testing whether the user has selected this row and use it as a null value?

Many Thanks
0
bobstery
Asked:
bobstery
1 Solution
 
simon_kirkCommented:
Could you not change your SQL statement to convert the NULL to something else before passing the data to RS?
0
 
YurichCommented:
you have to be more careful using NULL values even if you allow them in your parameters as they can produce unpredicted results when used in calculations. as an alternative, you can always use

ISNULL( object_name, value), e.g. ISNULL( myvar, 0 )

Regards,
Yurich
0
 
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectCommented:
ji you  can change the order into report parameter and palce all the parameter at last...
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
zachaustinCommented:
Try this in your parameter query:

If your query is: select id, name from customer

make it:
select id, name from customer
        union
select null, 'None'
0
 
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectCommented:
may be use used parameter as default and if your don't want to pass that parameter then you need to change the order at the last.
0
 
bobsteryAuthor Commented:
All of the suggested replies don't apply, I got the user to input the name rather than selecting it from a drop down.  More prone to errors from the user yes, but at least you can pass a null value and also the list only has 5 elements, so I displayed them at the top of the form.

Many Thanks
0
 
YurichCommented:
you don't have to use NULL, as simon suggested you can use something else instead of null and then pass it to your query and in the query you can use a case when ... and select (or do whatever you're doing) values with the NULL parameter. If you really want to, you can have in a drop down list NULL as a label but something else as a value. To have this effect, you can add a little union for a query that you're using to populate your drop-down list, like:

select distinct category_id, category_name from my_table

union

select 'null_value' as category_id, 'NULL' as category_name

then you will have a NULL in your drop down box, you will pass 'null_value' to your query.

regs,
yurich
0
 
YurichCommented:
I do believe my suggestion would do the trick.

regards,
Yurich
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now