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

change parameter to 'ALL' when is NULL

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
1 Solution
SharathData EngineerCommented:
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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