Solved

change parameter to 'ALL' when is NULL

Posted on 2011-03-21
1
250 Views
Last Modified: 2012-06-27
I want to change the nulls to 'All' instead of showing NULL in the parameter.  I have 'Allow NULL values option selected'

This is my dataset for a parameter I am using for the parameters
--this data set shows null and works fine when rpt is exec
select distinct  Terr from dimcustomer
order by Terr
--this does not work, it thinks 'All' is not null
select distinct  isnull(Terr,'All') as Terr from dimcustomer
order by Terr

Any suggestions
0
Comment
Question by:elucero
1 Comment
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 35185387
Do you have blank values in Terr instead of NULL? try this.
select distinct  isnull(nullif(ltrim(rtrim(Terr)),''),'All') as Terr 
  from dimcustomer 
 order by Terr

Open in new window

0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Written by Valentino Vranken. Introduction: In a previous article (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Reporting-On-Data-From-Stored-Procedures-part-1.html) I announced that I would writ…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
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

685 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