SSRS - handling nulls and blanks

I have an SSRS report....it was working fine it seemed in that I was handling blanks...but then I reliazed I had nulls in my data...and since then I have not been able to get it to work.

Here is how im handling nulls.  I will show you all the where clauses..im essentially handling them all the same.  

I will also provide the dataset for two of them so you can see how im manipulating the dropdown.

In the where clauses below..it was working with blanks....
In the native linked for example..if the  user selects all...I changed it so is null should be part of all....not sure if thats correct ..but I need is null to show in those params.

In the others I added the coalesce...that also does not seem to be working.  In my report I change the value in the dropdown for a blank to the word 'None'...and that is why you see my use of none here.  I do not pass the choice for null in my dropdowns...but the situation needs to be handled so isnull values would appear just as if they were blanks.

CAST(c.cUdf4 as DateTime) between @DateClientCreatedStart and @DateClientCreatedEnd 
AND
(((@NativeLinked = 'Native' or @NativeLinked= 'Linked') and t2.[Native/Linked] = @NativeLinked) or ((@NativeLinked = 'All') and t2.[Native/Linked] is null or  @NativeLinked = 'Native' or @NativeLinked= 'Linked'))
And 
(((@ActiveClient = 0 or @ActiveClient= 1) and c.lActive = @ActiveClient) or ((@ActiveClient = 2) and @ActiveClient = 0 or @ActiveClient= 1 or c.lActive is null))
And 
(((@ActiveContact = 'Active' or @ActiveContact= 'Inactive') and t2.ContactStatus = @ActiveContact) or ((@ActiveContact = 'All') and @ActiveContact = 'Active' or @ActiveContact= 'Inactive' or t2.ContactStatus  is null))
AND
case when replace(COALESCE(t2.State,''), ',', '') = '' then 'None' else replace(COALESCE(t2.State,'None'), ',', '') end IN ( SELECT splitdata FROM dbo.fnSplitString(@State,',') )
AND
case when replace(COALESCE(t2.Title,''), ',', '') = '' then 'None' else replace(COALESCE(t2.Title,'None'), ',', '') end IN ( SELECT splitdata FROM  dbo.fnSplitString(@Title,',') ) 
AND
case when replace(COALESCE(c.cIndustry,''), ',', '') = '' then 'None' else  replace(COALESCE(c.cIndustry,'None'), ',', '') end IN ( SELECT splitdata  FROM dbo.fnSplitString(@Industry,',') ) 
AND
case when replace(COALESCE(c.cUDF5,''),',','') = '' or  replace(RTRIM(LTRIM(COALESCE(c.cUDF5,''))),',','None') = 'No Selection' then 'None'  else replace(COALESCE(c.cUdf5,''), ',', '') end IN ( SELECT splitdata FROM dbo.fnSplitString(@EntityType,',') ) 
AND
case when replace(COALESCE(cf.ic_cClientRating,''), ',', '') = '' then 'None' else  replace(COALESCE(cf.ic_cClientRating,'None'), ',', '') end  IN ( SELECT splitdata FROM dbo.fnSplitString(@ClientRating,',') ) 
AND
case when replace(COALESCE(c.cUdf7,''), ',', '') = '' then 'None' else replace(COALESCE(c.cUdf7,'None'), ',', '') end IN ( SELECT splitdata  FROM dbo.fnSplitString(@PartnerDirector,',') )
AND
case when replace(COALESCE(cf.ic_cReferralSource,''), ',', '') = '' then 'None' else replace(COALESCE(cf.ic_cReferralSource,'None'), ',', '') end  IN ( SELECT splitdata FROM dbo.fnSplitString(@ReferralSource,',') )
AND
case when replace(COALESCE(cf.ic_cReferralSourceName,''), ',', '') = '' then 'None' else replace(COALESCE(cf.ic_cReferralSourceName,'None'), ',', '') end IN ( SELECT splitdata FROM dbo.fnSplitString(@ReferralsourceName,',') )  

Open in new window


The example with NativeLinked is pretty simple....i literraly just give the user 3 options to pick from and handle it here in the where.


For the others they are all very similar:

Here is dataset for one.

select t1.cUDF5

from
(  select distinct case when replace(c.cUDF5,',','') = '' or  replace(RTRIM(LTRIM(c.cUDF5)),',','') = 'No Selection' then 'None' else replace(c.cUDF5,',','') end as cUDF5
			  from cadoc_crm..tClient c)t1
order by CASE when  t1.cUDF5 = 'None' THEN 1 else 2 end, t1.cUDF5

Open in new window

LVL 11
Robb HillSenior .Net DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Hey Robb,

I have created a sample RDL report with below data. Please do the changes as in the report and see if you get what is required.

CREATE TABLE [CountryRegion] (Name Varchar(100),CountryRegionCode VarChar(100))

INSERT INTO [CountryRegion] VALUES
('','Blank'),
('Afghanistan','AF'),
('Albania','AL'),
('Algeria','DZ'),
('American Samoa','AS'),
('Andorra','AD'),
('Angola','AO'),
('Anguilla','AI'),
('Antarctica','AQ'),
('Antigua and Barbuda','AG'),
('Argentina','AR'),
('Armenia','AM'),
('Aruba','AW'),
('Australia','AU'),
('Austria','AT'),
('Azerbaijan','AZ'),
('Bahamas, The','BS'),
('Bahrain','BH'),
('Bangladesh','BD'),
('Barbados','BB'),
('Belarus','BY'),
('Belgium','BE'),
('Belize','BZ'),
('Benin','BJ'),
('Bermuda','BM'),
('Bhutan','BT'),
('Bolivia','BO'),
('Bosnia and Herzegovina','BA'),
('Botswana','BW'),
('Bouvet Island','BV'),
('Brazil','BR'),
('British Indian Ocean Territory','IO'),
('Brunei','BN'),
('Bulgaria','BG'),
('Burkina Faso','BF'),
('Burundi','BI'),
('Cambodia','KH'),
('Cameroon','CM'),
('Canada','CA'),
('Cape Verde','CV'),
('Cayman Islands','KY'),
('Central African Republic','CF'),
('Chad','TD'),
('Chile','CL'),
('China','CN'),
('Christmas Island','CX'),
('Cocos (Keeling) Islands','CC'),
('Colombia','CO'),
('Comoros','KM'),
('Congo','CG'),
('Congo (DRC)','CD'),
('Cook Islands','CK'),
('Costa Rica','CR'),
('Croatia','HR'),
('Cuba','CU'),
('Cyprus','CY'),
('Czech Republic','CZ'),
('Denmark','DK'),
('Djibouti','DJ'),
('Dominica','DM'),
('Dominican Republic','DO'),
('Ecuador','EC'),
('Egypt','EG'),
('El Salvador','SV'),
('Equatorial Guinea','GQ'),
('Eritrea','ER'),
('Estonia','EE'),
('Ethiopia','ET'),
('Falkland Islands (Islas Malvinas)','FK'),
('Faroe Islands','FO'),
('Fiji Islands','FJ'),
('Finland','FI'),
('France','FR'),
('French Guiana','GF'),
('French Polynesia','PF'),
('French Southern and Antarctic Lands','TF'),
('Gabon','GA'),
('Gambia, The','GM'),
('Georgia','GE'),
('Germany','DE'),
('Ghana','GH'),
('Gibraltar','GI'),
('Greece','GR'),
('Greenland','GL'),
('Grenada','GD'),
('Guadeloupe','GP'),
('Guam','GU'),
('Guatemala','GT'),
('Guinea','GN'),
('Guinea-Bissau','GW'),
('Guyana','GY'),
('Haiti','HT'),
('Heard Island and McDonald Islands','HM'),
('Honduras','HN'),
('Hong Kong SAR','HK'),
('Hungary','HU'),
('Iceland','IS'),
('India','IN'),
('Indonesia','ID'),
('Iran','IR'),
('Iraq','IQ'),
('Ireland','IE'),
('Israel','IL'),
('Italy','IT'),
('Jamaica','JM'),
('Japan','JP'),
('Jordan','JO'),
('Kazakhstan','KZ'),
('Kenya','KE'),
('Kiribati','KI'),
('Korea','KR'),
('Kuwait','KW'),
('Kyrgyzstan','KG'),
('Laos','LA'),
('Latvia','LV'),
('Lebanon','LB'),
('Lesotho','LS'),
('Liberia','LR'),
('Libya','LY'),
('Liechtenstein','LI'),
('Lithuania','LT'),
('Luxembourg','LU'),
('Macao SAR','MO'),
('Macedonia, Former Yugoslav Republic of','MK'),
('Madagascar','MG'),
('Malawi','MW'),
('Malaysia','MY'),
('Maldives','MV'),
('Mali','ML'),
('Malta','MT'),
('Marshall Islands','MH'),
('Martinique','MQ'),
('Mauritania','MR'),
('Mauritius','MU'),
('Mayotte','YT'),
('Mexico','MX'),
('Micronesia','FM'),
('Moldova','MD'),
('Monaco','MC'),
('Mongolia','MN'),
('Montserrat','MS'),
('Morocco','MA'),
('Mozambique','MZ'),
('Myanmar','MM'),
('Namibia','NA'),
('Nauru','NR'),
('Nepal','NP'),
('Netherlands','NL'),
('Netherlands Antilles','AN'),
('New Caledonia','NC'),
('New Zealand','NZ'),
('Nicaragua','NI'),
('Niger','NE'),
('Nigeria','NG'),
('Niue','NU'),
('Norfolk Island','NF'),
('North Korea','KP'),
('Northern Mariana Islands','MP'),
('Norway','NO'),
('Oman','OM'),
('Pakistan','PK'),
('Palau','PW'),
('Palestinian Authority','PS'),
('Panama','PA'),
('Papua New Guinea','PG'),
('Paraguay','PY'),
('Peru','PE'),
('Philippines','PH'),
('Pitcairn Islands','PN'),
('Poland','PL'),
('Portugal','PT'),
('Puerto Rico','PR'),
('Qatar','QA'),
('Réunion','RE'),
('Romania','RO'),
('Russia','RU'),
('Rwanda','RW'),
('Saint Helena','SH'),
('Saint Kitts and Nevis','KN'),
('Saint Lucia','LC'),
('Saint Pierre and Miquelon','PM'),
('Saint Vincent and the Grenadine','VC'),
('Samoa','WS'),
('San Marino','SM'),
('São Tomé and Príncipe','ST'),
('Saudi Arabia','SA'),
('Senegal','SN'),
('Serbia and Montenegro','CS'),
('Seychelles','SC'),
('Sierra Leone','SL'),
('Singapore','SG'),
('Slovakia','SK'),
('Slovenia','SI'),
('Solomon Islands','SB'),
('Somalia','SO'),
('South Africa','ZA'),
('South Georgia and the South Sandwich Islands','GS'),
('Spain','ES'),
('Sri Lanka','LK'),
('Sudan','SD'),
('Suriname','SR'),
('Svalbard and Jan Mayen','SJ'),
('Swaziland','SZ'),
('Sweden','SE'),
('Switzerland','CH'),
('Syria','SY'),
('Taiwan','TW'),
('Tajikistan','TJ'),
('Tanzania','TZ'),
('Thailand','TH'),
('Timor-Leste','TL'),
('Togo','TG'),
('Tokelau','TK'),
('Tonga','TO'),
('Trinidad and Tobago','TT'),
('Tunisia','TN'),
('Turkey','TR'),
('Turkmenistan','TM'),
('Turks and Caicos Islands','TC'),
('Tuvalu','TV'),
('U.S. Minor Outlying Islands','UM'),
('Uganda','UG'),
('Ukraine','UA'),
('United Arab Emirates','AE'),
('United Kingdom','GB'),
('United States','US'),
('Uruguay','UY'),
('Uzbekistan','UZ'),
('Vanuatu','VU'),
('Vatican City','VA'),
('Venezuela','VE'),
('Vietnam','VN'),
('Virgin Islands, British','VG'),
('Virgin Islands, U.S.','VI'),
('Wallis and Futuna','WF'),
('Yemen','YE'),
('Zambia','ZM'),
('Zimbabwe','ZW')

Open in new window

BlankMultiSelectAllOption.rdl
0
Robb HillSenior .Net DeveloperAuthor Commented:
need for nulls too...this looks like its only addressing blank
0
Pawan KumarDatabase ExpertCommented:
Hi Robb,
Updated rdl for you .
BlankMultiSelectAllOption.rdl
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.