Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Multi Value Paramter

1 view
Skip to first unread message

Ryan Mcbee

unread,
Dec 28, 2007, 4:06:00 PM12/28/07
to
I am building a year end report and have two problems;
1.) When I use the multiparameter functionality, nothing is returning in my
dataset when I run the report. I am using a stored proc for my report. The
proc uses where department=in @dept as the parameter, that is how I have done
it before not using a stored proc. Any ideas why I am not getting data
returned?

2.) I using a grouping by employee and want only one employees data to
print on a page at once. How can I get this done? Each employee has a
different amount of detail data. I do not want blank pages between each
employees data set, but want each employees data seperated from another.

Thanks in advance,
Ryan

Ryan Mcbee

unread,
Dec 28, 2007, 4:09:01 PM12/28/07
to
BTW, when I select just one department for my multiparamter, it works fine.
It just can't handle more then one.

Owen J.

unread,
Dec 31, 2007, 11:10:01 AM12/31/07
to
I believe the syntax is incorrect. Change this..

where department=in @dept

to

where department in @dept

Owen J.

unread,
Dec 31, 2007, 11:16:00 AM12/31/07
to
Oops, forgot one thing the parens around the parameter.

where department in (@dept)

Bruce L-C [MVP]

unread,
Jan 2, 2008, 9:02:53 AM1/2/08
to
What you say below is true for SQL statements in RS. However, you cannot
pass a multi value parameter into a stored procedure and have it work. This
is a SQL Server issue not RS.

Here is my previously posted answer for this:
What doesn't work has nothing really to do with RS but has to do with Stored

Procedures in SQL Server. You cannot do the following in a stored procedure.

Let's say you have a Parameter called @MyParams

Now you can map that parameter to a multi-value parameter but if in your

stored procedure you try to do this:

select * from sometable where somefield in (@MyParams)

It won't work. Try it. Create a stored procedure and try to pass a

multi-value parameter to the stored procedure. It won't work.

What you can do is to have a string parameter that is passed as a multivalue

parameter and then change the string into a table.

This technique was told to me by SQL Server MVP, Erland Sommarskog

For example I have done this

inner join charlist_to_table(@STO,Default)f on b.sto = f.str

So note this is NOT an issue with RS, it is strictly a stored procedure

issue.

Here is the function:

CREATE FUNCTION charlist_to_table

(@list ntext,

@delimiter nchar(1) = N',')

RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,

str varchar(4000),

nstr nvarchar(2000)) AS

BEGIN

DECLARE @pos int,

@textpos int,

@chunklen smallint,

@tmpstr nvarchar(4000),

@leftover nvarchar(4000),

@tmpval nvarchar(4000)

SET @textpos = 1

SET @leftover = ''

WHILE @textpos <= datalength(@list) / 2

BEGIN

SET @chunklen = 4000 - datalength(@leftover) / 2

SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)

SET @textpos = @textpos + @chunklen

SET @pos = charindex(@delimiter, @tmpstr)

WHILE @pos > 0

BEGIN

SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))

INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)

SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))

SET @pos = charindex(@delimiter, @tmpstr)

END

SET @leftover = @tmpstr

END

INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)),

ltrim(rtrim(@leftover)))

RETURN

END

GO


--
Bruce Loehle-Conger
MVP SQL Server Reporting Services


"Owen J." <Ow...@discussions.microsoft.com> wrote in message
news:8CDE459A-4CF2-4CB9...@microsoft.com...

Ryan Mcbee

unread,
Jan 2, 2008, 12:37:00 PM1/2/08
to
Bruce,
I am confused on this one. My query is something like select * from
upr30300 where department in (@dept)

Do I need to create the function you below?

Thanks,
Ryan

Bruce L-C [MVP]

unread,
Jan 2, 2008, 12:47:53 PM1/2/08
to
If you are writing a stored procedure then yes. If you are using the query
directly in Reporting Services then no.


--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"Ryan Mcbee" <Ryan...@discussions.microsoft.com> wrote in message
news:51E1B274-4601-4228...@microsoft.com...

Ryan Mcbee

unread,
Jan 2, 2008, 1:20:01 PM1/2/08
to
Bruce,
Can you take a look at this syntax? I have the function built, but do not
think my join is correct. Thanks,Ryan

SELECT #a_payytd.PAYCODE, #a_payytd.PRIMARYCODE, #a_payytd.TYPE,
#a_payytd.employid, RTRIM(UPR00100.LASTNAME)
+ ', ' + UPR00100.FRSTNAME AS name, CASE WHEN
#a_payytd.type = 1 THEN #a_payytd.ytd ELSE 0 END AS EarningsYTD,
CASE WHEN #a_payytd.type = 1 THEN currentpaycheck ELSE
0 END AS CurrentEarnings,
CASE WHEN #a_payytd.type = 1 THEN
#a_payytd.currenthours ELSE 0 END AS EarningHourCurrent,
CASE WHEN type = 1 THEN #a_payytd.hoursytd ELSE 0 END
AS EarningHoursYTD,
CASE WHEN #a_payytd.type = 2 THEN
#a_payytd.currentpaycheck ELSE 0 END AS DeductionsCurrent,
CASE WHEN #a_payytd.type = 2 THEN #a_payytd.ytd ELSE 0
END AS DeductionsYTD, CASE WHEN #a_payytd.type = 5 OR
#a_payytd.type = 4 THEN currentpaycheck ELSE 0 END AS
CurrentTax, CASE WHEN #a_payytd.type = 5 OR
#a_payytd.type = 4 THEN #a_payytd.ytd ELSE 0 END AS
TAXYTD, CASE WHEN #a_payytd.type = 3 THEN currentpaycheck ELSE 0 END AS
CurrentBenefit,
CASE WHEN type = 3 THEN #a_payytd.ytd ELSE 0 END AS
BenefitYTD, UPR00102.ADDRESS1, UPR00102.CITY, UPR00102.STATE,
UPR00102.ZIPCODE, UPR00100.LASTNAME,
UPR00100.FRSTNAME, UPR00100.SOCSCNUM, UPR00100.BRTHDATE, #a_payytd.PAYCODE AS
Expr1,
#a_payytd.TYPE AS Expr2, #a_payytd.employid AS Expr3,
#a_payytd.CurrentPaycheck, #a_payytd.YTD, #a_payytd.CurrentHours,
#a_payytd.Hoursytd,
UPR00100.SUTASTAT, UPR00100.ATACRVAC,
UPR00100.VACCRAMT, UPR00100.VACCRMTH, UPR00100.VACAPRYR, UPR00100.VACAVLBL,
UPR00100.DEPRTMNT, UPR00100.STRTDATE,
UPR00100.SUPERVISORCODE_I, UPR00100.JOBTITLE, UPR00300.FEDEXMPT,
UPR00700.STATECD,
UPR00700.PRSNEXPT, UPR00300.FDFLGSTS, UPR00400.PAYRTAMT
FROM dbo.charlist_to_table(, DEFAULT) AS charlist_to_table_1 INNER
JOIN
UPR00102 INNER JOIN
UPR00100 ON UPR00102.ADRSCODE = UPR00100.ADRSCODE ON
charlist_to_table_1.str = UPR00100.DEPRTMNT RIGHT OUTER JOIN
UPR00400 RIGHT OUTER JOIN
#a_payytd ON UPR00400.EMPLOYID = #a_payytd.employid
AND UPR00400.PAYRCORD = #a_payytd.PRIMARYCODE ON
UPR00100.EMPLOYID = #a_payytd.employid AND
UPR00102.EMPLOYID = #a_payytd.employid LEFT OUTER JOIN
UPR00700 ON #a_payytd.employid = UPR00700.EMPLOYID
LEFT OUTER JOIN
UPR00300 ON #a_payytd.employid = UPR00300.EMPLOYID
WHERE (UPR00100.DEPRTMNT = @Dept)
ORDER BY #a_payytd.employid, #a_payytd.TYPE

Bruce L-C [MVP]

unread,
Jan 2, 2008, 2:17:21 PM1/2/08
to
I assume you are getting an error.

FROM dbo.charlist_to_table(, DEFAULT) AS charlist_to_table_1 INNER JOIN

UPR00102 (where is your on clause saying what charlist_to_table_1 is joing
with UPR00102 on?)


--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"Ryan Mcbee" <Ryan...@discussions.microsoft.com> wrote in message

news:421FB544-358E-4152...@microsoft.com...

Ryan Mcbee

unread,
Jan 2, 2008, 3:05:23 PM1/2/08
to
Bruce,
I plugged in my parameter and the report still will not let me run multi
parameters. I do not get an error, it just does not return any data. Check
out the syntax below.
Thanks,
Ryan

SELECT [#a_payytd].PAYCODE, [#a_payytd].PRIMARYCODE, [#a_payytd].TYPE,
[#a_payytd].employid, RTRIM(UPR00100.LASTNAME)

+ ', ' + UPR00100.FRSTNAME AS name,

RTRIM(UPR00100_1.LASTNAME) + ', ' + UPR00100_1.FRSTNAME AS bossname,

CASE WHEN #a_payytd.type = 1 THEN #a_payytd.ytd ELSE 0
END AS EarningsYTD,
CASE WHEN #a_payytd.type = 1 THEN currentpaycheck ELSE
0 END AS CurrentEarnings,
CASE WHEN #a_payytd.type = 1 THEN
#a_payytd.currenthours ELSE 0 END AS EarningHourCurrent,
CASE WHEN type = 1 THEN #a_payytd.hoursytd ELSE 0 END
AS EarningHoursYTD,
CASE WHEN #a_payytd.type = 2 THEN
#a_payytd.currentpaycheck ELSE 0 END AS DeductionsCurrent,
CASE WHEN #a_payytd.type = 2 THEN #a_payytd.ytd ELSE 0
END AS DeductionsYTD, CASE WHEN #a_payytd.type = 5 OR
#a_payytd.type = 4 THEN currentpaycheck ELSE 0 END AS
CurrentTax, CASE WHEN #a_payytd.type = 5 OR
#a_payytd.type = 4 THEN #a_payytd.ytd ELSE 0 END AS
TAXYTD,
CASE WHEN #a_payytd.type = 3 THEN currentpaycheck ELSE
0 END AS CurrentBenefit,
CASE WHEN type = 3 THEN #a_payytd.ytd ELSE 0 END AS
BenefitYTD, UPR00102.ADDRESS1, UPR00102.CITY, UPR00102.STATE,
UPR00102.ZIPCODE, UPR00100.LASTNAME,

UPR00100.FRSTNAME, UPR00100.SOCSCNUM, UPR00100.BRTHDATE, [#a_payytd].PAYCODE
AS Expr1,
[#a_payytd].TYPE AS Expr2, [#a_payytd].employid AS
Expr3, [#a_payytd].CurrentPaycheck, [#a_payytd].YTD,
[#a_payytd].CurrentHours,
[#a_payytd].Hoursytd, UPR00100.SUTASTAT,

UPR00100.ATACRVAC, UPR00100.VACCRAMT, UPR00100.VACCRMTH, UPR00100.VACAPRYR,
UPR00100.VACAVLBL, UPR00100.DEPRTMNT,
UPR00100.STRTDATE, UPR00100.SUPERVISORCODE_I, UPR00100.JOBTITLE,
UPR00300.FEDEXMPT,
UPR00700.STATECD, UPR00700.PRSNEXPT,
UPR00300.FDFLGSTS, UPR00400.PAYRTAMT

FROM UPR41700 INNER JOIN


UPR00102 INNER JOIN
UPR00100 ON UPR00102.ADRSCODE = UPR00100.ADRSCODE ON

UPR41700.SUPERVISORCODE_I = UPR00100.SUPERVISORCODE_I INNER JOIN
UPR00100 AS UPR00100_1 ON UPR41700.EMPLOYID =
UPR00100_1.EMPLOYID INNER JOIN
dbo.charlist_to_table(@dept, DEFAULT) AS
charlist_to_table_1 ON UPR00100.DEPRTMNT = charlist_to_table_1.str RIGHT

OUTER JOIN
UPR00400 RIGHT OUTER JOIN
[#a_payytd] ON UPR00400.EMPLOYID =

[#a_payytd].employid AND UPR00400.PAYRCORD = [#a_payytd].PRIMARYCODE ON
UPR00100.EMPLOYID = [#a_payytd].employid AND
UPR00102.EMPLOYID = [#a_payytd].employid LEFT OUTER JOIN
UPR00700 ON [#a_payytd].employid = UPR00700.EMPLOYID
LEFT OUTER JOIN
UPR00300 ON [#a_payytd].employid = UPR00300.EMPLOYID
WHERE (UPR00100.DEPRTMNT IN (@dept))
ORDER BY [#a_payytd].employid, [#a_payytd].TYPE


Bruce L-C [MVP]

unread,
Jan 2, 2008, 3:46:15 PM1/2/08
to
You have several outer and inner join statements without matching On
clauses. I suggest you simplify for testing (perhaps just with the multi
parameter and the table it is joining against).


--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"Ryan Mcbee" <Ryan...@discussions.microsoft.com> wrote in message

news:7AC24747-648A-4B3F...@microsoft.com...

Ryan Mcbee

unread,
Jan 2, 2008, 9:07:01 PM1/2/08
to
Bruce,
I think I have it figured out. I took away the syntax"where in (@dept)" at
the end of my query. Once I took that away and just left the syntax in the
join to the function, it was fine.

Thanks,
Ryan

0 new messages