July 14, 2011 at 9:52 am
Hi,
I have used a split function as I am using multiple select parameters (in SSRS report). This works fine as long as the value is one continuous word and does not have a space between it. However, if there is a value say for e.g.- 'United States' and it is selected(checked), the split function does not recognize it and hence is not displayed on the report.
Does anyone know how to get around this issue ?
As an example, this is the syntax for the split function in the sql code:
ORIGIN IN (select distinct Item From dbo.Split(@sCountryofreceipt,','))
I hope I am able to explain the problem correctly. Thanks for your help.
Regards,
Paul
July 14, 2011 at 10:37 am
Can you post the code for the UDF (dbo.Split)?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 18, 2011 at 2:17 am
GSquared (7/14/2011)
Can you post the code for the UDF (dbo.Split)?
Thanks for your reply, please find the code below:
---------------------------------------------
CREATE FUNCTION [dbo].[Split](@ItemList [nvarchar](4000), @delimiter [char](1))
RETURNS @IDTable TABLE (
[Item] [varchar](50) NULL
) WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @tempItemList NVARCHAR(4000)
SET @tempItemList = @ItemList
DECLARE @i INT
DECLARE @Item NVARCHAR(4000)
SET @tempItemList = REPLACE (@tempItemList, ' ', '')
SET @i = CHARINDEX(@delimiter, @tempItemList)
WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)
INSERT INTO @IDTable(Item) VALUES(@Item)
IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END
---------------------------------------------
July 18, 2011 at 6:35 am
This line is removing spaces from your options:
SET @tempItemList = REPLACE (@tempItemList, ' ', '')
However, rather than fixing that, I recommend replacing the whole function. Here's an article on how to do that more efficiently: http://www.sqlservercentral.com/articles/Tally+Table/72993/
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 18, 2011 at 8:37 am
GSquared (7/18/2011)
This line is removing spaces from your options:
SET @tempItemList = REPLACE (@tempItemList, ' ', '')However, rather than fixing that, I recommend replacing the whole function. Here's an article on how to do that more efficiently: http://www.sqlservercentral.com/articles/Tally+Table/72993/
Thanks for your reply. I have'nt changed anything as yet but just wanted to bring a strange thing to your notice. What I noticed now was that if I only selected (check mark) values with space between them for e.g.- United Kingdom and United States, then the report displays data for both the countries. However, if I select the 'Select All' option, then the values with the space between them get omitted in the report. Is this problem related to the 'Select All' option ?
July 18, 2011 at 8:55 am
It has to do with how your string split function operates.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply