SSRS - Blanks in Multiselect not returning a resultset

I have a dataset like the following:

 select row_number() OVER (order by t1.cTitle) as titlerow,t1.cTitle

			  from
			  (select distinct coalesce(n.cTitle,'') cTitle
			  from cadoc_crm..tContact n
			  )t1

			  order by t1.cTitle
			 

Open in new window



This creates as dropdown on report server...

Iit is a "MULTISELCT"   with first value blank, and then all the distinct values.



Now my stored proc where clause for this is the following:


t2.Title IN ( SELECT splitdata FROM  dbo.fnSplitString(@Title,',') )

Open in new window




When I do this...I can still get the value..but not when its ''


here is my split function which I think might ignore the leading ''

USE [cadoc_crm]
GO
/****** Object:  UserDefinedFunction [dbo].[fnSplitString]    Script Date: 11/6/2017 6:14:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output(splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

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:
>> When I do this...I can still get the value..but not when its '' ??

Here we need change the where clause.
( t2.Title IN ( SELECT splitdata FROM  dbo.fnSplitString(@Title,',') ) OR @Title = '' )

or we need to use JOINS like below....

Yourquery
INNER JOIN (  ( SELECT splitdata FROM  dbo.fnSplitString(@Title,',') ) k ON k.splitdata = t2.Title
0
Robb HillSenior .Net DeveloperAuthor Commented:
testing now.
0
Robb HillSenior .Net DeveloperAuthor Commented:
This is still not returning the blanks.

I read once where blanks are not returned from SSRS if they are in the beginning of a comma delimited list..

I think the query fails because the value..though it looks like it should be there..isnt there.

The user clearly checks select all which includes the blank value....

But I dont think that '' is every read in by the parameter being the first value in the list.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Robb HillSenior .Net DeveloperAuthor Commented:
That was the issue...

So here is the solution...apparently having a blank in the first element of the delimited string the value is lost.

When changing the dataset and using your where clause change that looks for '' this al works now.

So long as the '' is not in the front it will work.  So here I moved it to bottom.


select row_number() OVER (order by t1.cTitle) as titlerow,t1.cTitle

                    from
                    (select distinct coalesce(n.cTitle,'') cTitle
                    from cadoc_crm..tContact n
                    )t1

                    order by CASE when  t1.cTitle = '' then 1 else 0 end
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
Robb HillSenior .Net DeveloperAuthor Commented:
Pawan:

I must have had something cached...this is not workign still :(
0
Robb HillSenior .Net DeveloperAuthor Commented:
I will just open a new ticket...
0
Robb HillSenior .Net DeveloperAuthor Commented:
The final issue with this was there was commas in the data...I had to use a replace function as well to eliminate the commas in the dataset.


Thanks again for your Help Pawan.
0
Robb HillSenior .Net DeveloperAuthor Commented:
My solution explains how to get the null ....and Pawan helps with the Where clause.
0
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
Microsoft SQL Server

From novice to tech pro — start learning today.