Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2006-07-18
9
Medium Priority
?
654 Views
Last Modified: 2012-05-05
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
Comment
Question by:bobstery
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 14

Expert Comment

by:simon_kirk
ID: 17136931
Could you not change your SQL statement to convert the NULL to something else before passing the data to RS?
0
 
LVL 21

Expert Comment

by:Yurich
ID: 17149718
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
 
LVL 11

Expert Comment

by:Ved Prakash Agrawal
ID: 17152658
ji you  can change the order into report parameter and palce all the parameter at last...
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Expert Comment

by:zachaustin
ID: 17275332
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
 
LVL 11

Expert Comment

by:Ved Prakash Agrawal
ID: 17340840
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
 

Author Comment

by:bobstery
ID: 17427807
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
 
LVL 21

Accepted Solution

by:
Yurich earned 150 total points
ID: 17427861
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
 
LVL 21

Expert Comment

by:Yurich
ID: 17582498
I do believe my suggestion would do the trick.

regards,
Yurich
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
How to increase the row limit in Jasper Server.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question