• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 80
  • Last Modified:

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 
(((@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'))
(((@ActiveClient = 0 or @ActiveClient= 1) and c.lActive = @ActiveClient) or ((@ActiveClient = 2) and @ActiveClient = 0 or @ActiveClient= 1 or c.lActive is null))
(((@ActiveContact = 'Active' or @ActiveContact= 'Inactive') and t2.ContactStatus = @ActiveContact) or ((@ActiveContact = 'All') and @ActiveContact = 'Active' or @ActiveContact= 'Inactive' or t2.ContactStatus  is null))
case when replace(COALESCE(t2.State,''), ',', '') = '' then 'None' else replace(COALESCE(t2.State,'None'), ',', '') end IN ( SELECT splitdata FROM dbo.fnSplitString(@State,',') )
case when replace(COALESCE(t2.Title,''), ',', '') = '' then 'None' else replace(COALESCE(t2.Title,'None'), ',', '') end IN ( SELECT splitdata FROM  dbo.fnSplitString(@Title,',') ) 
case when replace(COALESCE(c.cIndustry,''), ',', '') = '' then 'None' else  replace(COALESCE(c.cIndustry,'None'), ',', '') end IN ( SELECT splitdata  FROM dbo.fnSplitString(@Industry,',') ) 
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,',') ) 
case when replace(COALESCE(cf.ic_cClientRating,''), ',', '') = '' then 'None' else  replace(COALESCE(cf.ic_cClientRating,'None'), ',', '') end  IN ( SELECT splitdata FROM dbo.fnSplitString(@ClientRating,',') ) 
case when replace(COALESCE(c.cUdf7,''), ',', '') = '' then 'None' else replace(COALESCE(c.cUdf7,'None'), ',', '') end IN ( SELECT splitdata  FROM dbo.fnSplitString(@PartnerDirector,',') )
case when replace(COALESCE(cf.ic_cReferralSource,''), ',', '') = '' then 'None' else replace(COALESCE(cf.ic_cReferralSource,'None'), ',', '') end  IN ( SELECT splitdata FROM dbo.fnSplitString(@ReferralSource,',') )
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

(  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

Robb Hill
Robb Hill
  • 2
1 Solution
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))

('American Samoa','AS'),
('Antigua and Barbuda','AG'),
('Bahamas, The','BS'),
('Bosnia and Herzegovina','BA'),
('Bouvet Island','BV'),
('British Indian Ocean Territory','IO'),
('Burkina Faso','BF'),
('Cape Verde','CV'),
('Cayman Islands','KY'),
('Central African Republic','CF'),
('Christmas Island','CX'),
('Cocos (Keeling) Islands','CC'),
('Congo (DRC)','CD'),
('Cook Islands','CK'),
('Costa Rica','CR'),
('Czech Republic','CZ'),
('Dominican Republic','DO'),
('El Salvador','SV'),
('Equatorial Guinea','GQ'),
('Falkland Islands (Islas Malvinas)','FK'),
('Faroe Islands','FO'),
('Fiji Islands','FJ'),
('French Guiana','GF'),
('French Polynesia','PF'),
('French Southern and Antarctic Lands','TF'),
('Gambia, The','GM'),
('Heard Island and McDonald Islands','HM'),
('Hong Kong SAR','HK'),
('Macao SAR','MO'),
('Macedonia, Former Yugoslav Republic of','MK'),
('Marshall Islands','MH'),
('Netherlands Antilles','AN'),
('New Caledonia','NC'),
('New Zealand','NZ'),
('Norfolk Island','NF'),
('North Korea','KP'),
('Northern Mariana Islands','MP'),
('Palestinian Authority','PS'),
('Papua New Guinea','PG'),
('Pitcairn Islands','PN'),
('Puerto Rico','PR'),
('Saint Helena','SH'),
('Saint Kitts and Nevis','KN'),
('Saint Lucia','LC'),
('Saint Pierre and Miquelon','PM'),
('Saint Vincent and the Grenadine','VC'),
('San Marino','SM'),
('São Tomé and Príncipe','ST'),
('Saudi Arabia','SA'),
('Serbia and Montenegro','CS'),
('Sierra Leone','SL'),
('Solomon Islands','SB'),
('South Africa','ZA'),
('South Georgia and the South Sandwich Islands','GS'),
('Sri Lanka','LK'),
('Svalbard and Jan Mayen','SJ'),
('Trinidad and Tobago','TT'),
('Turks and Caicos Islands','TC'),
('U.S. Minor Outlying Islands','UM'),
('United Arab Emirates','AE'),
('United Kingdom','GB'),
('United States','US'),
('Vatican City','VA'),
('Virgin Islands, British','VG'),
('Virgin Islands, U.S.','VI'),
('Wallis and Futuna','WF'),

Open in new window

Robb HillSenior .Net DeveloperAuthor Commented:
need for nulls too...this looks like its only addressing blank
Pawan KumarDatabase ExpertCommented:
Hi Robb,
Updated rdl for you .
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

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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