Solved

SQL Server 2005 Report Parameters Settings

Posted on 2006-07-11
1
600 Views
Last Modified: 2008-03-03
Greetings,

When setting values in SQL Server 2005 Reporting parameter setting, how do you go about setting a multiple values where one of the values is NULL. For example:


select emp_name,title,emp_id
from emp
where emp_id=@emp_id



In this example I want to have the luxury to select all values, including the values that are null in the emp_id field or select only the null values in the emp_id field. When setting this up in SQL Server 2005 Reporting parameter section, it allows you to set each value separately but I want to know what is the proper syntax to set a value to NULL in SQL Server 2005 Reporting parameter section?
0
Comment
Question by:Omega002
[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
1 Comment
 
LVL 14

Accepted Solution

by:
simon_kirk earned 250 total points
ID: 17081908
Could you not change your SQL statement to convert the NULL before passing the data to RS?

e.g.

select emp_name,title,COALESCE(Cast(emp_id AS VARCHAR),'No Value')
from emp
where COALESCE(CAST(emp_id AS VARCHAR),'No Value')=@emp_id

or

select emp_name,title,COALESCE(emp_id,0)
from emp
where COALESCE(emp_id,0)=@emp_id
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
Problem Statement In an SAP BI BO Integration project when a BO universe is built on a BEx query, there can be an issue of unit & formatted value objects not getting generated in a BO universe for some key figures. This results in an issue whereb…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

732 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