April 28, 2016 at 12:20 pm
So I need to right an Expression to combine multiple values.
In the dataset I have this query:
Select
Null As fundid,
' All ' As description from qfund
Union
Select Distinct
chk.fundid,
f.description
From
paycheck chk Inner Join
qfund f
On chk.fundid = f.fundid
When running the query you get this:
fundiddescription
NULL All
C00303582 HTA FUND
C01938137 NTSP RISK HTA
MSC000000002 CNC RISK
MSC000000061 UNITED
MSC000000067 NTSP RISK CNC
MSC000005747 HUMANA NC
April 28, 2016 at 12:43 pm
Something like this perhaps...
"MSC000000002" & "C00303582" &"MSC000005747" &....
-- Itzik Ben-Gan 2001
April 28, 2016 at 12:52 pm
"MSC000000002"&"C00303582"&"MSC000005747"&"MSC000006211"&"MSC000000067"&"C01938137"&"MSC000000061"
Still returns a blank set. Where as Just one value returns data
April 28, 2016 at 2:11 pm
SilverBack (4/28/2016)
"MSC000000002"&"C00303582"&"MSC000005747"&"MSC000006211"&"MSC000000067"&"C01938137"&"MSC000000061"Still returns a blank set. Where as Just one value returns data
Try
="MSC000000002"&"C00303582"&"MSC000005747"&"MSC000006211"&"MSC000000067"&"C01938137"&"MSC000000061"
-- Itzik Ben-Gan 2001
April 28, 2016 at 2:24 pm
multiple values where? In a multi-select parameter? In your report somewhere?
April 28, 2016 at 2:59 pm
In an Expression
pietlinden (4/28/2016)
multiple values where? In a multi-select parameter? In your report somewhere?
April 28, 2016 at 3:27 pm
SilverBack (4/28/2016)
In an Expressionpietlinden (4/28/2016)
multiple values where? In a multi-select parameter? In your report somewhere?
What do those values mean? Where are they coming from? How did they get there? Why do you need them in an expression? More details = better help
-- Itzik Ben-Gan 2001
April 28, 2016 at 6:57 pm
JOIN()?
April 29, 2016 at 1:01 pm
Correct. Except I have it working if you select just one. I am looking for it to select all of the values and give the option of all.
Thom A (4/29/2016)
Are you actually saying that you have a multi value parameter, and you want to return results if the field you are querying is any of those the user has selected?
May 3, 2016 at 2:01 am
I use a split udf to do this. When you use SSRS to pass a multivalue parameter, it'll pass them all in a comma delimited string.
I do the split with the following (which I butchered from somwhere ages ago, apologies, but I haven't got an annotation):
Create FUNCTION [dbo].[Split_udf](@String nvarchar(MAX), @Delimiter char(1))
RETURNS @Results TABLE (Items nvarchar(MAX))
AS
BEGIN
DECLARE @index INT
DECLARE @slice nvarchar(MAX)
--Have to set to 1 to start with
SELECT @index = 1
WHILE @index !=0
BEGIN
--get the index of the first instance of the delimiter
SELECT @index = CHARINDEX(@Delimiter,@String)
--put everything to the left of the delimiter into the slice variable
IF @index !=0
SELECT @slice = LEFT(@String,@INDEX - 1)
ELSE
SELECT @slice = @String
--Put it into the result sel
INSERT INTO @Results(Items) VALUES(@SLICE)
--Cut the string now and put the rest back in
SELECT @String = RIGHT(@String,LEN(@String) - @index)
--If we've run out of characters, we're done!
IF LEN(@String) = 0 BREAK
END
RETURN
END
In your main query you would then need to put the following in the where cause:
fundid in (Select Items from dbo.Split_udf(@fundidlist, ','))
I wouldn't suggest using an "all" option, as SSRS will do this for you.
Hope that helps.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply