Robb Hill
asked on
SSRS - Blanks in Multiselect not returning a resultset
I have a dataset like the following:
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:
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 ''
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
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,',') )
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Pawan:
I must have had something cached...this is not workign still :(
I must have had something cached...this is not workign still :(
ASKER
I will just open a new ticket...
ASKER
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.
Thanks again for your Help Pawan.
ASKER
My solution explains how to get the null ....and Pawan helps with the Where clause.
ASKER