Issue with SELECT "IN" statement

In Reporting Services I have following query. For some reason query only
picks the FIRST choice for State or Product â?¦ even though user selects
multiple products or states â?¦ what is the issue here? Could you please help
me?
Thank you.
SELECT
DATENAME(MONTH, dbo.fnGetDSDate(ts_time)) AS SubmitMonth,
YEAR(dbo.fnGetDSDate(ts_time)) AS SubmitYear,
s.ts_name
FROM ShowAll INNER JOIN TS_STATE s on newstate = s.id
WHERE
(transitionlabel <> 'Update' OR transitionlabel <> '')
AND ts_productsubsystem IN (@.ProductId)
AND ts_newstate IN (@.StateId)On Jun 4, 10:43 am, ozcan <o...@.discussions.microsoft.com> wrote:
> In Reporting Services I have following query. For some reason query only
> picks the FIRST choice for State or Product ... even though user selects
> multiple products or states ... what is the issue here? Could you please help
> me?
> Thank you.
> SELECT
> DATENAME(MONTH, dbo.fnGetDSDate(ts_time)) AS SubmitMonth,
> YEAR(dbo.fnGetDSDate(ts_time)) AS SubmitYear,
> s.ts_name
> FROM ShowAll INNER JOIN TS_STATE s on newstate = s.id
> WHERE
> (transitionlabel <> 'Update' OR transitionlabel <> '')
> AND ts_productsubsystem IN (@.ProductId)
> AND ts_newstate IN (@.StateId)
You will most likely need to loop through the multi-select report
parameter and insert the values in a temp table (in a query/stored
procedure outside the report) and then use the above query to access
the values in the temp table. Here is a query that should get you
started in looping through the multi-select report parameter values
selected.
--CREATE PROC ParseRSMultiParameterList
DECLARE
@.STATES VARCHAR(MAX)
--AS
DECLARE @.STATEBUFFER VARCHAR(MAX),
@.END_POSITION INT;
--TEST DATA--
SET @.STATES = 'AL,TN,CA,OH';
--TEST DATA--
CREATE TABLE #STATELIST (State char(2));
SET @.STATEBUFFER = @.STATES;
WHILE (LEN(@.STATEBUFFER) > 0)
BEGIN
IF (CHARINDEX(',', @.STATEBUFFER) > 0)
BEGIN
SET @.END_POSITION = CHARINDEX(',', @.STATEBUFFER);
INSERT INTO #STATELIST VALUES (SUBSTRING(@.STATEBUFFER, 1,
(@.END_POSITION - 1)));
END
IF (CHARINDEX(',', @.STATEBUFFER) = 0)
BEGIN
SET @.END_POSITION = LEN(@.STATEBUFFER);
INSERT INTO #STATELIST VALUES (SUBSTRING(@.STATEBUFFER, 1,
(@.END_POSITION + 1)));
END
SET @.STATEBUFFER = RIGHT(@.STATEBUFFER, (LEN(@.STATEBUFFER) -
@.END_POSITION));
END
SELECT
DATENAME(MONTH, dbo.fnGetDSDate(ts_time)) AS SubmitMonth,
YEAR(dbo.fnGetDSDate(ts_time)) AS SubmitYear,
s.ts_name
FROM ShowAll INNER JOIN TS_STATE s on newstate = s.id
WHERE
(transitionlabel <> 'Update' OR transitionlabel <> '')
AND ts_productsubsystem IN (@.ProductId)
AND ts_newstate IN (select State from #STATELIST)
DROP TABLE #STATELIST;
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||You may want to look at the Report, Parameters setting (from layout view) and
see if that parameter has the "multi-value" checkbox selected. If it is not
checked, I would think that the end user would only be able to select one
single parameter, and this does not seem to be the issue - but may want to
check it.