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?
 
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
 
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
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.

 
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
 
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
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.

All Courses

From novice to tech pro — start learning today.