В моем отчете есть несколько параметров Multi-Select. Я пытаюсь найти способ передать несколько значений для одного параметра в строке веб-запроса? Если я передаю одно значение, он отлично работает.
Отчет работает отлично, выбирая несколько вариантов для одного параметра. Моя проблема заключается в строке веб-запроса.
Хотя решение Джона Сансом работает, есть еще один способ сделать это, без необходимости использовать потенциально неэффективный скалярно-оцененный UDF. В отчете SSRS на вкладке параметров определения запроса установите значение параметра
=join(Parameters!<your param name>.Value,",")
В вашем запросе вы можете ссылаться на значение следующим образом:
where yourColumn in (@<your param name>)
Это то, что я использую при передаче параметра multi-select в другой параметр multi-select.
=SPLIT(JOIN(Parameters!<your param name>.Value,","),",")
Это одна из плохих поддерживаемых функций в службах SQL Reporting Services.
Что вам нужно сделать, так это передать все выбранные вами элементы в виде одной строки в хранимую процедуру. Каждый элемент внутри строки будет разделен запятой.
Затем я разделяю строку, используя функцию, которая возвращает предоставленную строку в виде таблицы. См. Ниже.
ALTER FUNCTION [dbo].[fn_MVParam]
(@RepParam nvarchar(4000), @Delim char(1)= ',')
RETURNS @Values TABLE (Param nvarchar(4000))AS
BEGIN
DECLARE @chrind INT
DECLARE @Piece nvarchar(100)
SELECT @chrind = 1
WHILE @chrind > 0
BEGIN
SELECT @chrind = CHARINDEX(@Delim,@RepParam)
IF @chrind > 0
SELECT @Piece = LEFT(@RepParam,@chrind - 1)
ELSE
SELECT @Piece = @RepParam
INSERT @Values(Param) VALUES(CAST(@Piece AS VARCHAR))
SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
IF LEN(@RepParam) = 0 BREAK
END
RETURN
END
Затем вы можете ссылаться на результаты в предложении where вашего основного запроса следующим образом:
where someColumn IN(SELECT Param FROM dbo.fn_MVParam(@sParameterString,','))
Надеюсь, вы найдете это решение полезным. Пожалуйста, не стесняйтесь задавать любые вопросы, которые могут возникнуть.
Cheers, Джон
Джон Сэнсом и Эд Харпер имеют отличные решения. Однако я не смог заставить их работать при работе с полями идентификаторов (т.е. Целыми). Я изменил функцию split ниже, чтобы CAST значения как целые числа, поэтому таблица будет объединяться с столбцами первичного ключа. Я также прокомментировал код и добавил столбец для заказа, в случае, если порядок списка с разделителями был значительным.
CREATE FUNCTION [dbo].[fn_SplitInt]
(
@List nvarchar(4000),
@Delimiter char(1)= ','
)
RETURNS @Values TABLE
(
Position int IDENTITY PRIMARY KEY,
Number int
)
AS
BEGIN
-- set up working variables
DECLARE @Index INT
DECLARE @ItemValue nvarchar(100)
SELECT @Index = 1
-- iterate until we have no more characters to work with
WHILE @Index > 0
BEGIN
-- find first delimiter
SELECT @Index = CHARINDEX(@Delimiter,@List)
-- extract the item value
IF @Index > 0 -- if found, take the value left of the delimiter
SELECT @ItemValue = LEFT(@List,@Index - 1)
ELSE -- if none, take the remainder as the last value
SELECT @ItemValue = @List
-- insert the value into our new table
INSERT INTO @Values (Number) VALUES (CAST(@ItemValue AS int))
-- remove the found item from the working list
SELECT @List = RIGHT(@List,LEN(@List) - @Index)
-- if list is empty, we are done
IF LEN(@List) = 0 BREAK
END
RETURN
END
Используйте эту функцию, как было отмечено ранее:
WHERE id IN (SELECT Number FROM dbo.fn_SplitInt(@sParameterString,','))
ORACLE:
Фраза "IN" (Ed Solution) не будет работать против соединения Oracle (по крайней мере, версии 10). Тем не менее, нашел эту простую работу, которая делает. Используя вкладку параметров набора данных, включите параметр многозначного значения в CSV:
:name =join(Parameters!name.Value,",")
Затем в вашем операторе SQL предложение WHERE использует функцию instring для проверки соответствия.
INSTR(:name, TABLE.FILENAME) > 0
У меня возникла проблема с замечательным fn_MVParam. SSRS 2005 отправил данные с апострофом в виде 2 кавычек.
Я добавил одну строку, чтобы исправить это.
select @RepParam = replace(@RepParam,'''''','''')
Моя версия fn также использует varchar вместо nvarchar.
CREATE FUNCTION [dbo].[fn_MVParam]
(
@RepParam varchar(MAX),
@Delim char(1)= ','
)
RETURNS @Values TABLE (Param varchar(MAX)) AS
/*
Usage: Use this in your report SP
where ID in (SELECT Param FROM fn_MVParam(@PlanIDList,','))
*/
BEGIN
select @RepParam = replace(@RepParam,'''''','''')
DECLARE @chrind INT
DECLARE @Piece varchar(MAX)
SELECT @chrind = 1
WHILE @chrind > 0
BEGIN
SELECT @chrind = CHARINDEX(@Delim,@RepParam)
IF @chrind > 0
SELECT @Piece = LEFT(@RepParam,@chrind - 1)
ELSE
SELECT @Piece = @RepParam
INSERT @VALUES(Param) VALUES(@Piece)
SELECT @RepParam = RIGHT(@RepParam,DATALENGTH(@RepParam) - @chrind)
IF DATALENGTH(@RepParam) = 0 BREAK
END
RETURN
END
Модификация великого решения Джона, решить:
пробел после одной части в параметре
ALTER FUNCTION [dbo].[fn_MVParam]
(@RepParam nvarchar(4000), @Delim char(1)= ',')
RETURNS @Values TABLE (Param nvarchar(4000))AS
BEGIN
//2 quotes error
set @RepParam = replace(@RepParam,char(39)+char(39),CHAR(39))
DECLARE @chrind INT
DECLARE @Piece nvarchar(100)
SELECT @chrind = 1
WHILE @chrind > 0
BEGIN
SELECT @chrind = CHARINDEX(@Delim,@RepParam)
IF @chrind > 0
SELECT @Piece = LEFT(@RepParam,@chrind - 1)
ELSE
SELECT @Piece = @RepParam
INSERT @Values(Param) VALUES(CAST(@Piece AS VARCHAR(300)))
//space after one of piece in parameter: LEN(@RepParam + '1')-1
SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam + '1')-1 - @chrind)
IF LEN(@RepParam) = 0 BREAK
END
RETURN
END
Просто комментарий - я столкнулся с миром боли, пытаясь заставить предложение IN работать в соединении с Oracle 10g. Я не думаю, что переписанный запрос может быть правильно передан в 10g db. Мне пришлось полностью отказаться от многозначности. Запрос возвращал данные только тогда, когда было выбрано одно значение (из селектора параметров с несколькими значениями). Я попробовал драйверы MS и Oracle с теми же результатами. Мне бы хотелось услышать, если кто-то добился успеха в этом.
объявить @paramName AS NVARCHAR (500),
IF RIGHT (@paramName, 1) = ',' НАЧАТЬ SET @paramName = LEFT ((@paramName, LEN ((@paramName) -1) END
Это отлично работает для меня:
WHERE CHARINDEX(CONVERT(nvarchar, CustNum), @CustNum) > 0
Вероятно, было бы проще добавить несколько значений в таблицу сначала, а затем вы можете присоединиться или как угодно (даже с помощью подстановочных знаков) или сохранить данные в другой таблице для последующего использования (или даже добавить значения в другая таблица).
Задайте значение параметра через выражение в наборе данных:
="SELECT DISTINCT * FROM (VALUES('" & JOIN(Parameters!SearchValue.Value, "'),('") & "'))
AS tbl(Value)"
Сам запрос:
DECLARE @Table AS TABLE (Value nvarchar(max))
INSERT INTO @Table EXEC sp_executeSQL @SearchValue
Пример подстановочных знаков:
SELECT * FROM YOUR_TABLE yt
INNER JOIN @Table rt ON yt.[Join_Value] LIKE '%' + rt.[Value] + '%'
Мне бы хотелось выяснить способ сделать это без динамического SQL, но я не думаю, что это сработает из-за того, как SSRS передает параметры фактическому запросу. Если кто-то знает лучше, пожалуйста, дайте мне знать.
Что вы также можете сделать, это добавить этот код в свою хранимую процедуру:
set @s = char(39) + replace(@s, ',', char(39) + ',' + char(39)) + char(39)
(Предполагая, что @s является многозначной строкой (например, "A, B, C" ))
Если вы хотите передать несколько значений в RS через строку запроса, все, что вам нужно сделать, это повторить параметр отчета для каждого значения.
Например; У меня есть столбец RS, называемый COLS, и этот столбец ожидает одно или несколько значений.
&rp:COLS=1&rp:COLS=1&rp:COLS=5 etc..
Я новичок в этом сайте и не могу понять, как комментировать предыдущий ответ, и это то, что я считаю. Я также не мог проголосовать за пост Джеффа, который, как мне кажется, дал мне мой ответ. В любом случае...
Пока я вижу, как работают некоторые из великих сообщений и последующие твики, я только имею доступ к чтению в базу данных, поэтому никакие решения UDF, SP или представления не работают. Поэтому решение Ed Harper выглядело неплохо, за исключением комментария VenkateswarluAvula, что вы не можете передать строку, разделенную запятой, в качестве параметра в предложение WHERE IN, и ожидать, что она будет работать по мере необходимости. Но решение Jeff для ORACLE 10g заполняет этот пробел. Я положил их вместе с сообщением в блоге Russell Christopher на http://blogs.msdn.com/b/bimusings/archive/2007/05/07/how-do-you-set-select-all-as-the-default-for-multi-value-parameters-in-reporting-services.aspx, и у меня есть мое решение:
Создайте свой многоэкранный параметр MYPARAMETER, используя любой источник доступных значений (возможно, набор данных). В моем случае мульти-выбор был из нескольких записей TEXT, но я уверен, что с некоторыми настройками он будет работать с другими типами. Если вы хотите, чтобы "Выбрать все" было по умолчанию, установите тот же источник, что и по умолчанию. Это дает вам пользовательский интерфейс, но созданный параметр не является параметром, переданным моему SQL.
Пропуск вперед к SQL и решение Jeff для проблемы WHERE IN (@MYPARAMETER), у меня есть проблема со всеми моими собственными, в том, что 1 из значений ( "Charge" ) появляется в одном из других значений (' Non Charge '), то есть CHARINDEX может найти ложноположительный. Мне нужно было искать параметр для разделительного значения как до, так и после. Это означает, что мне нужно убедиться, что в списке, разделенном запятыми, есть ведущая и обучающая запятая. И это мой SQL-фрагмент:
where ...
and CHARINDEX(',' + pitran.LINEPROPERTYID + ',', @MYPARAMETER_LIST) > 0
Бит в середине - это создать еще один параметр (скрытый в процессе производства, но не при разработке):
="," +
join(Parameters!MYPARAMETER.Value,",") + "," и метка, которая
не имеет значения (поскольку он не будет отображаться).Этот параметр передается в SQL, который просто является поисковой строкой, но SQL обрабатывает как любой фрагмент текста.
Надеюсь, что эти фрагменты ответов помогут кому-то найти то, что они ищут.
В прошлом я прибегал к использованию хранимых процедур и функции для выбора нескольких лет в запросе SQL Server для служб отчетов. Использование выражения Join в значении параметра запроса, предложенном Ed Harper, все равно не будет работать с предложением SQL IN в инструкции where. Мое решение состояло в том, чтобы использовать следующее в предложении where вместе с параметром Join expression: и charindex (cast (Schl.Invt_Yr as char (4)), @Invt_Yr) > 0
Речь идет об использовании функции join для сохранения многозначного параметра и последующего восстановления тех же самых параметров из базы данных позже.
Я только что закончил отчет с требованиями, что параметры должны быть сохранены, а когда отчет снова открывается (отчет передается параметром OrderID), значения, ранее выбранные пользователем, должны быть снова выбраны.
В отчете использовалась половина из десяти параметров, каждая из которых имела свой собственный набор данных и в результате выпадающего списка. Параметры зависели от предыдущих параметров, чтобы сузить область окончательного выбора, и когда отчет был "просмотрен", для заполнения была вызвана хранимая процедура.
Хранимая процедура получила каждый из параметров, переданных ей из отчета. Он проверил таблицу хранения в базе данных, чтобы проверить, сохранены ли какие-либо параметры для этого идентификатора OrderID. Если нет, то он сохранил все параметры. Если это так, он обновляет все параметры для этого порядка (это тот случай, когда пользователь меняет свое мнение позже).
Когда запускается отчет, существует набор данных dsParameters, который выводит текст SQL, и выбирает одну строку для этого идентификатора заказа, если таковой имеется. Каждый из параметров в отчете получает свое значение по умолчанию из этого набора данных и его список выбора из набора данных, посвященного этому параметру.
У меня возникла проблема с параметром multi-select. Я использовал команду join (@Value, "," ) в списке параметров основного набора данных, передавая хранимой процедуре строку с разделителями-запятыми. Но как его восстановить? Вы не можете передать строку с разделителями-запятыми обратно в поле значений по умолчанию параметра.
IF OBJECT_ID('tempdb..#Parse','U') IS NOT NULL DROP TABLE #Parse
DECLARE @Start int, @End int, @Desc varchar(255)
SELECT @Desc = fldDesc FROM dbCustomData.dbo.tblDirectReferralFormParameters WHERE fldFrom = @From and fldOrderID = @OrderID
CREATE TABLE #Parse (fldDesc varchar(255))
SELECT @Start = 1, @End = 1
WHILE @End > 0
BEGIN
SET @End = CHARINDEX(',',@Desc,@Start)
IF @End = 0
BEGIN
INSERT #Parse SELECT REPLACE(SUBSTRING(@Desc,@Start,LEN(@Desc)),',','') AS fldDesc
BREAK
END
ELSE
BEGIN
INSERT #Parse SELECT REPLACE(SUBSTRING(@Desc,@Start,@[email protected]),',','') AS fldDesc
END
SET @Start = @End + 1
END
SELECT * FROM #Parse
Каждый раз, когда открывается форма, этот набор данных проверяет базу данных для сохраненной строки для этого многозначного параметра. Если его нет, он возвращает null. Если он включен, он анализирует запятые и создает строку для каждого из значений.
Затем в поле значений по умолчанию установлен этот набор данных и fldDesc. Оно работает! Когда я выбираю один или несколько, они сохраняют и пополняют, когда форма открывается снова.
Надеюсь, это поможет. Я искал какое-то время и не нашел упоминания о сохранении строки объединения в базе данных, а затем разобрал ее в наборе данных.
Таким образом, умножить текстовые значения окажутся в запросе с одинарными кавычками вокруг каждого используемого = join (Parameters! Customer.Value, "','" ). Поэтому после ".Value" это запятая, двойная кавычка, одинарная кавычка, запятая, одинарная кавычка, двойная кавычка, close-bracket. просто:)
Мне нужно решение для Oracle, и я нашел, что это работало для меня внутри моего запроса для моего отчета для DB >= 10g.
выберите *, из которого в ( выберите regexp_substr (, '[^,] +', 1, level) из двойного connect by regexp_substr (, '[^,] +', 1, level) не равно null);