Solved

SQL Server 2005 Report Parameters Settings

Posted on 2006-07-11
1
597 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Suggested Solutions

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…
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…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

740 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