• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • 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
0
elucero
Asked:
elucero
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

0
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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