Reporting Services에서 단일 매개 변수에 대한 여러 값 전달

Reporting Services에서 단일 매개 변수에 대한 여러 값 전달

---

보고서에 여러 다중 선택 매개 변수가 있습니다. 웹 쿼리 문자열의 단일 매개 변수에 대해 여러 값을 전달하는 방법을 찾으려고합니까? 단일 값을 전달하면 제대로 작동합니다.

보고서는 단일 매개 변수에 대해 여러 선택 사항을 선택하여 잘 실행됩니다. 내 문제는 웹 쿼리 문자열에 있습니다.

---

John Sansom의 솔루션이 작동하지만 잠재적으로 비효율적 인 스칼라 값 UDF를 사용하지 않고도이를 수행 할 수있는 또 다른 방법이 있습니다. SSRS 보고서에서 쿼리 정의의 매개 변수 탭에서 매개 변수 값을

=join(Parameters!<your param name>.Value,",")

쿼리에서 다음과 같이 값을 참조 할 수 있습니다.

where yourColumn in (@<your param name>)

---

이것은 다중 선택 매개 변수를 다른 다중 선택 매개 변수에 전달할 때 사용하는 것입니다.

=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,','))

이 솔루션이 사용되기를 바랍니다. 질문이 있으시면 언제든지 문의 해주십시오.

건배, 존

---

John Sansom과 Ed Harper는 훌륭한 솔루션을 가지고 있습니다. 그러나 ID 필드 (예 : 정수)를 다룰 때 작동하도록 할 수 없습니다. 아래 분할 함수를 수정하여 값을 정수로 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,','))

---

신탁:

"IN"문구 (Ed 's 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도 nvarchar 대신 varchar를 사용합니다.

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

---

위대한 John 솔루션의 수정, 해결 :

• "따옴표 2 개"오류

• 매개 변수의 조각 중 하나 뒤에 공백

  
  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
  
  

---

그냥 코멘트-Oracle 10g와의 연결에서 IN 절을 사용하려고 시도하는 동안 상처를 입었습니다. 다시 작성된 쿼리가 10g db에 올바르게 전달 될 수 있다고 생각하지 않습니다. 다중 값을 완전히 삭제해야했습니다. 쿼리는 단일 값 (다중 값 매개 변수 선택기에서)을 선택한 경우에만 데이터를 반환합니다. 동일한 결과로 MS 및 Oracle 드라이버를 사용해 보았습니다. 누구든지 이것으로 성공했는지 듣고 싶습니다.

---

또한 할 수있는 것은 저장 프로 시저에이 코드를 추가하는 것입니다.

set @s = char(39) + replace(@s, ',', char(39) + ',' + char(39)) + char(39)

(@s가 다중 값 문자열 (예 : "A, B, C")이라고 가정)

---

나는 사이트에 처음 왔고 이전 답변에 대해 의견을 말하는 방법을 알 수 없었습니다. 나는 또한 Jeff의 게시물에 투표 할 수 없었습니다. 어쨌든 ...

몇 가지 훌륭한 게시물과 후속 조정이 어떻게 작동하는지 볼 수 있지만 데이터베이스에 대한 읽기 액세스 권한 만 있으므로 UDF, SP 또는보기 기반 솔루션이 작동하지 않습니다. 따라서 Ed Harper의 솔루션은 쉼표로 구분 된 문자열을 매개 변수로 WHERE IN 절에 전달할 수없고 필요에 따라 작동 할 것으로 기대할 수 없다는 VenkateswarluAvula의 의견을 제외하고는 좋아 보였습니다. 그러나 ORACLE 10g에 대한 Jeff의 솔루션은 그 차이를 메워줍니다. 나는 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로 건너 뛰고 WHERE IN (@MYPARAMETER) 문제에 대한 Jeff의 솔루션으로 넘어 가면 값 중 1 개 ( 'Charge')가 다른 값 중 하나 ( 'Non Charge')에 나타납니다. ), 즉 CHARINDEX가 위양성을 찾을 수 있습니다. 매개 변수에서 이전과 이후에 구분 된 값을 검색해야했습니다. 즉, 쉼표로 구분 된 목록에 선행 및 후행 쉼표도 있는지 확인해야합니다. 그리고 이것은 내 SQL 스 니펫입니다.

where ...
and CHARINDEX(',' + pitran.LINEPROPERTYID + ',', @MYPARAMETER_LIST) > 0

중간에있는 부분은 다음을 사용하여 다른 매개 변수 (프로덕션에는 숨겨져 있지만 개발 중에는 아님)를 만드는 것입니다.

• MYPARAMETER_LIST의 이름
• 텍스트 유형
• 사용 가능한 단일 값 ="," + join(Parameters!MYPARAMETER.Value,",") + ","및
  실제로 중요하지 않은 레이블 (표시되지 않기 때문에).
• 정확히 동일한 기본값
• 확실히하기 위해 두 매개 변수의 고급 속성에서 항상 새로 고침을 설정했습니다.

검색 가능한 문자열이지만 SQL이 텍스트 조각처럼 처리하는 것은 SQL로 전달되는 매개 변수입니다.

이 답변 조각을 모아서 누군가가 원하는 것을 찾는 데 도움이되기를 바랍니다.

---

조인 함수를 사용하여 다중 값 매개 변수를 저장 한 다음 나중에 데이터베이스에서 정확히 동일한 선택을 복원하는 방법입니다.

매개 변수를 저장해야한다는 요구 사항이있는 보고서를 방금 완료했으며 보고서를 다시 열면 (보고서가 OrderID 매개 변수로 전달됨) 이전에 사용자가 선택한 값을 다시 한 번 선택해야합니다.

보고서는 6 개의 매개 변수를 사용했으며, 각 매개 변수에는 고유 한 데이터 세트와 결과 드롭 다운 목록이 있습니다. 매개 변수는 최종 선택 범위를 좁히기 위해 이전 매개 변수에 종속되었으며 보고서를 "볼 때"저장 프로 시저를 호출하여 채웠습니다.

스토어드 프로시 저는 보고서에서 전달 된 각 매개 변수를 수신했습니다. 데이터베이스의 스토리지 테이블을 확인하여 해당 OrderID에 대해 저장된 매개 변수가 있는지 확인했습니다. 그렇지 않은 경우 모든 매개 변수를 저장했습니다. 그렇다면 해당 주문에 대한 모든 매개 변수를 업데이트했습니다 (사용자가 나중에 마음을 바꾸는 경우).

보고서가 실행되면 나가는 SQL 텍스트 인 dsParameters 데이터 세트가 있으며 해당 orderID에 대한 단일 행이있는 경우 선택합니다. 보고서의 각 매개 변수는이 데이터 세트에서 기본값을 가져오고 해당 매개 변수 전용 데이터 세트에서 선택 목록을 가져옵니다.

다중 선택 매개 변수에 문제가 발생했습니다. 기본 데이터 집합 매개 변수 목록에서 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,@End-@Start),',','') AS fldDesc 
            END
        SET @Start = @End + 1
    END

SELECT * FROM #Parse

---

양식이 열릴 때마다이 데이터 세트는 데이터베이스에서이 다중 값 매개 변수에 대해 저장된 문자열을 확인합니다. 하나가 없으면 null을 반환합니다. on이면 쉼표를 구문 분석하고 각 값에 대한 행을 만듭니다.

그런 다음 기본값 상자가이 데이터 세트 및 fldDesc로 설정됩니다. 효과가있다! 하나 또는 여러 개를 선택하면 양식을 다시 열 때 저장하고 보충합니다.

이게 도움이 되길 바란다. 나는 잠시 동안 검색했지만 조인 문자열을 데이터베이스에 저장 한 다음 데이터 세트에서 구문 분석하는 것에 대한 언급을 찾지 못했습니다.

---

1. 보고서의 목록에 대한 데이터 집합 만들기
2. 매개 변수를 마우스 오른쪽 버튼으로 클릭하고 사용 가능한 값을 선택합니다.
3. 새로 생성 된 데이터 세트를 데이터 세트로 선택
4. 저장 프로 시저에 전달되는 값을 값 필드로 추가
5. 레이블 필드에 매개 변수 설명을 추가하십시오 (매개 변수가 customerID이면 레이블은 예를 들어 CustomerName 일 수 있음).
6. Finally, add following code to your stored procedure

declare @paramName AS NVARCHAR(500),

IF RIGHT(@paramName, 1) = ',' BEGIN SET @paramName = LEFT((@paramName, LEN((@paramName)-1) END

---

It would probably be easier to add the multi values to a table first and then you can join or whatever you'd like (even with wildcards) or save the data to another table for later use (or even add the values to another table).

Set the Parameter value via expression in the dataset:

="SELECT DISTINCT * FROM (VALUES('" & JOIN(Parameters!SearchValue.Value, "'),('") & "')) 
AS tbl(Value)"

The query itself:

DECLARE @Table AS TABLE (Value nvarchar(max))

INSERT INTO @Table EXEC sp_executeSQL @SearchValue 

Wildcard example:

SELECT * FROM YOUR_TABLE yt 

INNER JOIN @Table rt ON yt.[Join_Value] LIKE '%' + rt.[Value] + '%'

I'd love to figure out a way to do it without dynamic SQL but I don't think it'll work due to the way SSRS passes the parameters to the actual query. If someone knows better, please let me know.

---

So multiply text values would end up in the query with single quotes around each I used =join(Parameters!Customer.Value,"','"). So after ".Value" that is comma, double-quote, single-quote, comma, single-quote, double-quote, close-bracket. simples :)

---

this worked for a distinct set of strings ( e.g. "START", "END", "ERROR", "SUCCESS" )

1)define a Report-Parameter ( e.g. @log_status ) and check "Allow multiple values"

2) define a dataset
3) open the dataset-properties window
3a) in the Query-Tab enter your query: e.g.

select * from your_table where (CHARINDEX(your_column, @log_status,0) > 0)

3b) in the Parameters-Tab enter your Parameter, e.g.
Parametername: @log_status ; Parametervalue: <<Expr>>
3c) for the Expr click on the "fx"-Button and enter:

=join(Parameters!log_status.Value,",")

finished! ( it's similar to Ed Harper's solution, but sorry to say this didn't work for me )

---

The below solution worked for me.

1. In the parameter tab of your dataset properties click on the expression icon (!http://chittagongit.com//images/fx-icon/fx-icon-16.jpg [fx symbol]) beside the parameter you need to allow comma delimited entry for.

2. In the expression window that appears, use the Split function (Common Functions -> Text). Example shown below:

=Split(Parameters!ParameterName.Value,",")

---

If you want to pass multiple values to RS via a query string all you need to do is repeat the report parameter for each value.

For example; I have a RS column called COLS and this column expects one or more values.

&rp:COLS=1&rp:COLS=1&rp:COLS=5 etc..

---

In the past I have resorted to using stored procedures and a function to select multiple years in a SQL Server query for reporting services. Using the Join expression in the query parameter value as suggested by Ed Harper, still would not work with an SQL IN clause in the where statement. My resolution was to use the following in the where clause along with the parameter Join expression: and charindex (cast(Schl.Invt_Yr as char(4)) , @Invt_Yr) > 0

---

This works great for me:

WHERE CHARINDEX(CONVERT(nvarchar, CustNum), @CustNum) > 0

---

I needed solution for Oracle and I found this worked for me inside my query for my report for DB>=10g.

select * from where in ( select regexp_substr(,'[^,]+', 1, level) from dual connect by regexp_substr(, '[^,]+', 1, level) is not null );

source https://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement

---

As of MSSQL 2016 - with compatibility level 130, you can utilize String_Split() to parse your joined parameter from SSRS. Say you wanted to populate a parameter from a query in SSRS and then pass that param to a stored proc or SSRS Shared Dataset:

1. Add two datasets to your SSRS report, one that returns a list of values and labels to display in your parameter and one that has the actual data you're looking to filter. Each of these datasets can be a stored proc or shared dataset or embedded query.
2. Create a parameter in SSRS that is NOT on the dataset you want to filter. Let's call it Customer
3. Set Customer param to allow multiple values and set up Available Values tab with the dataset, labels, and values you want to display from the query.
4. Right click the dataset you're looking to filter and add a parameter that IS defined in the stored procedure. Let's call it CustomerList.
5. Click the expression button next to the value field for this parameter and do Join(Parameters!Customer.Value, ",")
6. In your stored proc or shared dataset, utilize string_split to break the comma-delimited @CustomerList param into an array: Customer.CustID in (select value from string_split(@CustomerList, ',') where value = Customer.CustID)

참고URL : https://stackoverflow.com/questions/512105/passing-multiple-values-for-a-single-parameter-in-reporting-services