sql server reporting services: Hi all!
My report is ran from a sproc and I have one parameter called
@district. When I preview the report the prompt comes up and lists the
district names like I want it to. The user can then pick one or more
than 1 districts to view data from. When the report is ran for all
districts it works fine, but when I chose just one district the report
still runs for all districts. I have been playing with this for a
while, but can't seem to get anywhere with what I'm doing. Anyone have
any ideas?

I have a quick question about Report parameters. It does not pull
anything up. I think it has something to do with the dates. Can someone
tell me if my WHERE clause is correct?

SELECT P.id, uid, R.rname,time_spent, C.cname, close_date, title,
start_date , PR.pname, stoclength, inrange, A.Action_Name,
workstart_date, sname

FROM problems AS P
LEFT OUTER JOIN dbo.categories AS C
ON P.category = C.category_id
JOIN dbo.status AS S
ON P.status = S.status_id
JOIN dbo.reps AS R
ON P.rep = R.rep_id
JOIN dbo.priority AS PR
ON P.priority = PR.priority_id
JOIN dbo.actions AS A
ON P.action = A.Action_ID

WHERE start_date BETWEEN @StartDate and @EndDate

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

--



The other thing you can do is to create dynamic sql and use exec to execute
the string.

Bruce Loehle-Conger

MVP SQL Server Reporting Services


[quoted text, click to view]

It looks fine. Make sure your report parameter is defined as date. Layout,
Report Menu-> Report Parameters


--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]