sql - parametros - Pasar múltiples valores para un solo parámetro en Reporting Services

parametros dinamicos reporting services

Question

Tengo varios parámetros Multi-Select en mi informe. Estoy tratando de encontrar una manera de pasar en múltiples valores para un solo parámetro en la cadena de consulta web? Si paso en un solo valor, funciona bien.

El informe funciona bien seleccionando múltiples opciones para un solo parámetro. Mi problema radica en la cadena de consulta web.

Answer 1

1. Crear el conjunto de datos para la lista en el informe
2. Haga clic derecho en el parámetro y seleccione valores disponibles
3. Seleccionar el conjunto de datos recién creado como conjunto de datos
4. Agregue el valor que pasa al procedimiento almacenado como campo de valor
5. Agregue la descripción del parámetro al campo de etiqueta (si el parámetro es customerID, la etiqueta podría ser CustomerName ej.)
6. Finalmente, agregue el siguiente código a su procedimiento almacenado

declarar @paramName AS NVARCHAR (500),

SI ES DERECHA (@paramName, 1) = '','' BEGIN SET @paramName = LEFT ((@ paramName, LEN ((@ paramName) -1) END

Answer 2

Así que multiplicar los valores de texto terminarían en la consulta con comillas simples alrededor de cada una de ellas I used = join (Parameters! Customer.Value, "'',''"). Así que después de ".Value" que es coma, comillas dobles, comillas simples, coma, comillas simples, comillas dobles, corchetes. simples :)

Answer 3

Aunque la solución de John Sansom funciona, hay otra forma de hacerlo, sin tener que utilizar un UDF con valor escalar potencialmente ineficiente. En el informe de SSRS, en la pestaña de parámetros de la definición de la consulta, establezca el valor del parámetro en

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

En su consulta, puede hacer referencia al valor de esta manera:

where yourColumn in (@<your param name>)

Answer 4

En el pasado, he recurrido al uso de procedimientos almacenados y una función para seleccionar varios años en una consulta de SQL Server para informes de servicios. El uso de la expresión Join en el valor del parámetro de consulta sugerido por Ed Harper, aún no funcionaría con una cláusula SQL IN en la sentencia where. Mi resolución fue usar lo siguiente en la cláusula where junto con el parámetro Join expression: y charindex (cast (Schl.Invt_Yr como char (4)), @Invt_Yr)> 0

Answer 5

Esta es una de las funciones soportadas de SQL Reporting Services.

Lo que debe hacer es pasar todos sus elementos seleccionados como una sola cadena a su procedimiento almacenado. Cada elemento dentro de la cadena estará separado por una coma.

Lo que hago es dividir la cadena usando una función que devuelve la cadena proporcionada como una tabla. Vea abajo.

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

A continuación, puede hacer referencia a los resultados en la cláusula where de su consulta principal así:

where someColumn IN(SELECT Param FROM dbo.fn_MVParam(@sParameterString,'',''))

Espero que encuentres esta solución de utilidad. Por favor, siéntase libre de plantear cualquier pregunta que pueda tener.

Saludos, John

Answer 6

Esto es lo que uso al pasar un parámetro de selección múltiple a otro parámetro de selección múltiple.

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

Answer 7

Esto funciona genial para mí:

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

Answer 8

John Sansom y Ed Harper tienen excelentes soluciones. Sin embargo, no pude hacer que funcionen cuando se trata de campos ID (es decir, enteros). Modifiqué la función de división siguiente para CAST los valores como enteros, de modo que la tabla se unirá a las columnas de clave principal. También comenté el código y agregué una columna para ordenar, en caso de que el orden de la lista delimitada fuera significativo.

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

Use esta función como se indicó anteriormente con:

WHERE id IN (SELECT Number FROM dbo.fn_SplitInt(@sParameterString,'',''))

Answer 9

La siguiente solución funcionó para mí.

1. En la pestaña de parámetros de las propiedades de su conjunto de datos, haga clic en el icono de expresión (! http://chittagongit.com//images/fx-icon/fx-icon-16.jpg [símbolo fx]) junto al parámetro que necesita para permitir la coma. entrada delimitada para.

2. En la ventana de expresión que aparece, use la función Dividir (Funciones comunes -> Texto). Ejemplo que se muestra a continuación:

= Split (Parámetros! ParameterName.Value, ",")

Answer 10

Lo que también puede hacer es agregar este código en su procedimiento almacenado:

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

(Asumiendo que @s es una cadena multivaluada (como "A, B, C"))

Answer 11

Me encontré con un problema con el maravilloso fn_MVParam. SSRS 2005 envió datos con un apóstrofo como 2 citas.

Agregué una línea para arreglar esto.

select @RepParam = replace(@RepParam,'''''''''''','''''''')

Mi versión de fn también usa varchar en lugar de 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

Answer 12

Modificación de la gran solución de Juan, resuelve:

• Error "2 comillas"

• espacio después de uno de pieza en parámetro

  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

Answer 13

Necesitaba una solución para Oracle y encontré esto funcionó para mí dentro de mi consulta para mi informe para DB> = 10g.

select * from where in (seleccione regexp_substr (, ''[^,] +'', 1, nivel) desde la conexión dual mediante regexp_substr (, ''[^,] +'', 1, nivel) no es nulo);

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

Answer 14

ORÁCULO:

La frase "IN" (Solución de Ed) no funcionará contra una conexión de Oracle (al menos la versión 10). Sin embargo, encontró este sencillo trabajo que sí lo hace. Usando la pestaña del parámetro del conjunto de datos, convierta el parámetro multivalor en un CSV:

    :name =join(Parameters!name.Value,",")

Luego, en la cláusula WHERE de su instrucción SQL, use la función intring para buscar una coincidencia.

    INSTR(:name, TABLE.FILENAME) > 0

Answer 15

Probablemente sea más fácil agregar los valores múltiples a una tabla primero y luego puedes unirte o lo que quieras (incluso con comodines) o guardar los datos en otra tabla para su uso posterior (o incluso agregar los valores a otra tabla) .

Establezca el valor del parámetro a través de la expresión en el conjunto de datos:

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

La consulta en sí:

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

INSERT INTO @Table EXEC sp_executeSQL @SearchValue 

Ejemplo de comodín:

SELECT * FROM YOUR_TABLE yt 

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

Me encantaría encontrar una manera de hacerlo sin SQL dinámico, pero no creo que funcione debido a la forma en que SSRS pasa los parámetros a la consulta real. Si alguien sabe mejor, házmelo saber.

Answer 16

Se trata de utilizar la función de unión para guardar un parámetro multivalor y luego restaurar las mismas selecciones exactas de la base de datos más adelante.

Acabo de terminar un informe que tenía requisitos de que los parámetros deben guardarse, y cuando el informe se abre de nuevo (el informe se pasa un parámetro OrderID), los valores elegidos previamente por el usuario deben seleccionarse nuevamente.

El informe utilizó la mitad de una docena de parámetros, cada uno tenía su propio conjunto de datos y la lista desplegable resultante. Los parámetros dependían de los parámetros previos para reducir el alcance de la selección final, y cuando se "visualizaba" el informe, se llamaba a un procedimiento almacenado para que se completara.

El procedimiento almacenado recibió cada uno de los parámetros que se le pasaron desde el informe. Verificó una tabla de almacenamiento en la base de datos para ver si se guardaba algún parámetro para ese OrderID. Si no, entonces guardó todos los parámetros. Si es así, actualizó todos los parámetros para esa orden (este es el caso donde el usuario cambia de opinión más adelante).

Cuando se ejecuta el informe, hay un conjunto de datos dsParameters que es texto SQL que se apaga y selecciona la única fila para ese orderID, si es que hay uno. Cada uno de los parámetros del informe obtiene su valor predeterminado de este conjunto de datos y su lista de selección de un conjunto de datos dedicado a ese parámetro.

Tuve problemas con el parámetro de selección múltiple. Usé un comando join (@Value, ",") en la lista de parámetros del conjunto de datos principal, pasando al procedimiento almacenado una cadena delimitada por comas. Pero, ¿cómo restaurarlo? No puede volver a alimentar la cadena delimitada por comas al recuadro de valores predeterminados del parámetro.

Tuve que crear otro conjunto de datos para dividir el parámetro, de manera similar a lo que está hablando. Se parece a esto:

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

Cada vez que se abre el formulario, este conjunto de datos comprueba la base de datos de una cadena guardada para este parámetro multivaluado. Si no hay uno, devuelve nulo. Si está activado, analiza las comas y crea una fila para cada uno de los valores.

A continuación, el cuadro de valores predeterminados se establece en este conjunto de datos y fldDesc. ¡Funciona! Cuando elijo uno o muchos, guardan y reabastecen cuando el formulario se abre de nuevo.

Espero que esto ayude. Busqué por un tiempo y no encontré ninguna mención de guardar la cadena de unión en una base de datos y luego analizarla en un conjunto de datos.

Answer 17

Si desea pasar múltiples valores a RS a través de una cadena de consulta, todo lo que necesita hacer es repetir el parámetro de informe para cada valor.

Por ejemplo; Tengo una columna RS llamada COLS y esta columna espera uno o más valores.

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

Answer 18

Solo un comentario: me encontré con un mundo de dolor tratando de obtener una cláusula IN para trabajar en una conexión con Oracle 10g. No creo que la consulta reescrita se pueda pasar correctamente a un db de 10g. Tuve que soltar el multivalor por completo. La consulta devolvería los datos solo cuando se eligió un único valor (del selector de parámetros multivalor). Probé los controladores MS y Oracle con los mismos resultados. Me encantaría saber si alguien ha tenido éxito con esto.

Answer 19

Soy nuevo en el sitio y no pude entender cómo comentar una respuesta anterior, que es lo que creo que debería ser. Tampoco pude votar la publicación de Jeff, que creo que me dio mi respuesta. De todos modos ...

Si bien puedo ver cómo funcionan algunas de las grandes publicaciones y los ajustes posteriores, solo tengo acceso de lectura a la base de datos, por lo que no funcionan las soluciones basadas en UDF, SP o basadas en vistas. Así que la solución de Ed Harper se veía bien, excepto por el comentario de VenkateswarluAvula de que no se puede pasar una cadena separada por comas como parámetro a una cláusula WHERE IN y esperar que funcione como lo necesites. Pero la solución de Jeff para el ORACLE 10g llena esa brecha. Los puse junto con la publicación de blog de Russell Christopher en 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 y tengo mi solución:

Cree su parámetro multi-selección MYPARAMETER usando cualquier fuente de valores disponibles (probablemente un conjunto de datos). En mi caso, la selección múltiple era de un grupo de entradas de TEXTO, pero estoy seguro de que con algunos ajustes podría funcionar con otros tipos. Si desea que Seleccionar todo sea la posición predeterminada, configure la misma fuente como el valor predeterminado. Esto le da su interfaz de usuario, pero el parámetro creado no es el parámetro pasado a mi SQL.

Saltando al SQL, y la solución de Jeff al problema WHERE IN (@MYPARAMETER), tengo un problema en sí mismo, porque 1 de los valores (''Charge'') aparece en uno de los otros valores (''Non Charge'') ), lo que significa que CHARINDEX podría encontrar un falso positivo. Necesitaba buscar el parámetro para el valor delimitado tanto antes como después. Esto significa que necesito asegurarme de que la lista separada por comas también tenga una coma inicial y una de seguimiento. Y este es mi fragmento de SQL:

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

El bit en el medio es crear otro parámetro (oculto en la producción, pero no mientras se desarrolla) con:

• Un nombre de MYPARAMETER_LIST
• Un tipo de texto
• Un único valor disponible de ="," + join(Parameters!MYPARAMETER.Value,",") + "," y una etiqueta que
  realmente no importa (ya que no se mostrará).
• Un valor predeterminado exactamente igual
• Solo para estar seguro, configuré Always Refresh en las propiedades avanzadas de ambos parámetros

Es este parámetro el que pasa a SQL, que resulta ser una cadena de búsqueda pero que SQL maneja como cualquier texto.

Espero que juntar estos fragmentos de respuestas ayude a que alguien encuentre lo que está buscando.

Answer 20

esto funcionó para un conjunto distinto de cadenas (por ejemplo, "START", "END", "ERROR", "SUCCESS")

1) definir un parámetro de informe (por ejemplo, @log_status) y marcar "Permitir valores múltiples"

2) definir un conjunto de datos
3) abrir la ventana de propiedades del conjunto de datos
3a) en la pestaña de consulta, ingrese su consulta: ej.

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

3b) en la pestaña de parámetros, introduzca su parámetro, p. Ej.
Parametername: @log_status ; Parametervalue: <<Expr>>
3c) para el Expr haga clic en el botón "fx" e ingrese:

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

¡terminado! (es similar a la solución de Ed Harper, pero siento decir que esto no funcionó para mí)