Transmission de plusieurs valeurs pour un seul paramètre dans Reporting Services

J’ai plusieurs parameters de sélection multiple dans mon rapport. J’essaie de trouver un moyen de transmettre plusieurs valeurs pour un seul paramètre dans la chaîne de requête Web? Si je passe une seule valeur, cela fonctionne bien.

Le rapport fonctionne correctement en sélectionnant plusieurs choix pour un seul paramètre. Mon problème réside dans la chaîne de requête Web.

Bien que la solution de John Sansom fonctionne, il existe un autre moyen de le faire sans avoir à utiliser un UDF à valeur scalaire potentiellement inefficace. Dans le rapport SSRS, dans l’onglet Paramètres de la définition de la requête, définissez la valeur du paramètre sur

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

Dans votre requête, vous pouvez alors référencer la valeur comme suit:

 where yourColumn in (@) 

C’est ce que j’utilise pour transmettre un paramètre multi-sélection à un autre paramètre multi-sélection.

 =SPLIT(JOIN(Parameters!.Value,","),",") 

C’est l’une des fonctionnalités médiocres sockets en charge dans SQL Reporting Services.

Ce que vous devez faire est de passer tous vos éléments sélectionnés en une seule chaîne à votre procédure stockée. Chaque élément de la chaîne sera séparé par une virgule.

Ce que je fais ensuite, c’est diviser la chaîne en utilisant une fonction qui renvoie la chaîne fournie sous forme de tableau. Voir ci-dessous.

 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 

Vous pouvez ensuite référencer les résultats dans la clause where de votre requête principale comme suit:

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

J’espère que vous trouverez cette solution utile. N’hésitez pas à poser vos questions.

Cheers, John

John Sansom et Ed Harper ont d’excellentes solutions. Cependant, je n’ai pas pu les amener à travailler avec les champs d’identification (c.-à-d. Les entiers). J’ai modifié la fonction de division ci-dessous pour convertir les valeurs en nombres entiers afin que la table se joigne aux colonnes de clé primaire. J’ai également commenté le code et ajouté une colonne pour l’ordre, au cas où l’ordre des listes délimitées serait significatif.

 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 

Utilisez cette fonction comme indiqué précédemment avec:

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

ORACLE:

La phrase “IN” (solution de Ed) ne fonctionnera pas avec une connexion Oracle (au moins la version 10). Cependant, trouvé ce travail simple qui fait. En utilisant l’onglet des parameters du jeu de données, transformez le paramètre multi-valeurs en un fichier CSV:

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

Ensuite, dans la clause WHERE de votre instruction SQL, utilisez la fonction inssortingng pour rechercher une correspondance.

  INSTR(:name, TABLE.FILENAME) > 0 

J’ai rencontré un problème avec le merveilleux fn_MVParam. SSRS 2005 a envoyé des données avec une apostrophe sous forme de 2 guillemets.

J’ai ajouté une ligne pour résoudre ce problème.

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

Ma version de la fn utilise également varchar au lieu 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 

Modification de la solution John, résolvez:

  • Erreur “2 citations”
  • espace après un morceau en paramètre

    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
    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 

Juste un commentaire – je me suis heurté à un monde de souffrance en essayant de faire fonctionner une clause IN dans une connexion à Oracle 10g. Je ne pense pas que la requête réécrite puisse être correctement transmise à une firebase database de 10g. J’ai dû abandonner complètement le multi-valeur. La requête renvoie des données uniquement lorsqu’une seule valeur (provenant du sélecteur de paramètre à valeurs multiples) a été choisie. J’ai essayé les pilotes MS et Oracle avec les mêmes résultats. J’aimerais savoir si quelqu’un a eu du succès avec cela.

  1. Créez le jeu de données pour la liste dans le rapport
  2. Cliquez avec le bouton droit sur le paramètre et sélectionnez les valeurs disponibles
  3. Sélectionner le jeu de données nouvellement créé en tant que jeu de données
  4. Ajouter la valeur passant à la procédure stockée comme champ de valeur
  5. Ajoutez la description du paramètre au champ label (si le paramètre est customerID, alors label pourrait être CustomerName ex.)
  6. Enfin, ajoutez le code suivant à votre procédure stockée

déclarer @paramName AS NVARCHAR (500),

SI RIGHT (@paramName, 1) = ‘,’ BEGIN SET @paramName = LEFT ((@ paramName, LEN ((@ paramName) -1) END

Cela fonctionne très bien pour moi:

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

Il serait probablement plus facile d’append d’abord les valeurs multiples à une table, puis de les joindre ou de les utiliser (même avec des caractères génériques) ou de les enregistrer dans une autre table pour les utiliser ultérieurement (ou même les append à une autre table). .

Définissez la valeur du paramètre via l’expression dans le jeu de données:

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

La requête elle-même:

 DECLARE @Table AS TABLE (Value nvarchar(max)) INSERT INTO @Table EXEC sp_executeSQL @SearchValue 

Exemple de joker:

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

Je serais ravi de trouver un moyen de le faire sans SQL dynamic, mais je ne pense pas que cela fonctionnera en raison de la façon dont SSRS transmet les parameters à la requête réelle. Si quelqu’un sait mieux, faites-le moi savoir.

Si vous souhaitez transmettre plusieurs valeurs à RS via une chaîne de requête, il vous suffit de répéter le paramètre de rapport pour chaque valeur.

Par exemple; J’ai une colonne RS appelée COLS et cette colonne attend une ou plusieurs valeurs.

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

Vous pouvez également append ce code dans votre procédure stockée:

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

(En supposant que @s est une chaîne à plusieurs valeurs (comme “A, B, C”))

Je suis nouveau sur le site et je ne pouvais pas commenter une réponse précédente, ce que je pense que cela devrait être. Je ne pouvais pas non plus voter contre le post de Jeff, ce qui, je crois, m’a donné ma réponse. De toute façon …

Bien que je puisse voir le fonctionnement de certains messages géniaux et de quelques modifications ultérieures, je n’ai qu’un access en lecture à la firebase database, donc aucune solution UDF, SP ou à base de vues ne fonctionne pour moi. Donc, la solution d’Ed Harper semblait bonne, à l’exception du commentaire de VenkateswarluAvula selon lequel vous ne pouvez pas passer une chaîne séparée par des virgules en tant que paramètre dans une clause WHERE IN et espérer qu’elle fonctionnera selon vos besoins. Mais la solution de Jeff à l’ORACLE 10g comble cette lacune. Je les ai mis avec le blog de Russell Christopher sur http://blogs.msdn.com/b/bimusings/archive/2007/05/07/how-do-you-set-select-all-as-the-default- pour-multi-value-parameters-in-reporting-services.aspx et j’ai ma solution:

Créez votre paramètre multi-sélection MYPARAMETER en utilisant la source des valeurs disponibles (probablement un jeu de données). Dans mon cas, le multi-sélection provenait d’un groupe d’entrées de texte, mais je suis sûr qu’avec quelques réglages, cela fonctionnerait avec d’autres types. Si vous souhaitez que Sélectionner tout soit la position par défaut, définissez la même source que la valeur par défaut. Cela vous donne votre interface utilisateur, mais le paramètre créé n’est pas le paramètre passé à mon SQL.

En allant de l’avant avec le SQL et la solution de Jeff au problème WHERE IN (@MYPARAMETER), j’ai un problème qui me concerne, en ce sens que l’une des valeurs («Charge») apparaît dans l’une des autres valeurs («Non Charge»). ), ce qui signifie que CHARINDEX pourrait trouver un faux positif. Je devais rechercher le paramètre pour la valeur délimitée à la fois avant et après. Cela signifie que je dois m’assurer que la liste séparée par des virgules comporte également une virgule. Et ceci est mon extrait de code SQL:

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

Le bit du milieu consiste à créer un autre paramètre (caché dans la production, mais pas en cours de développement) avec:

  • Un nom de MYPARAMETER_LIST
  • Un type de texte
  • Une seule valeur disponible de ="," + join(Parameters!MYPARAMETER.Value,",") + "," et une étiquette
    n’a pas vraiment d’importance (puisqu’il ne sera pas affiché).
  • Une valeur par défaut identique
  • Juste pour être sûr, je mets Always Refresh dans les propriétés avancées des deux parameters

C’est ce paramètre qui est passé à SQL, qui se trouve être une chaîne de recherche mais que SQL gère comme n’importe quel morceau de texte.

J’espère que mettre ensemble ces fragments de réponses aidera quelqu’un à trouver ce qu’il cherche.

Dans le passé, j’ai eu recours à des procédures stockées et à une fonction pour sélectionner plusieurs années dans une requête SQL Server pour des services de génération de rapports. L’utilisation de l’expression Join dans la valeur du paramètre de requête, comme suggéré par Ed Harper, ne fonctionnerait toujours pas avec une clause SQL IN dans l’instruction where. Ma résolution consistait à utiliser les éléments suivants dans la clause where avec le paramètre expression de jointure: et charindex (cast (Schl.Invt_Yr en tant que char (4)), @Invt_Yr)> 0

Il s’agit d’utiliser la fonction de jointure pour enregistrer un paramètre à valeurs multiples, puis restaurer les mêmes sélections exactes à partir de la firebase database ultérieurement.

Je viens de terminer un rapport qui exigeait que les parameters soient enregistrés, et lorsque le rapport est rouvert (le rapport est passé avec un paramètre OrderID), les valeurs précédemment choisies par l’utilisateur doivent être à nouveau sélectionnées.

Le rapport a utilisé une demi-douzaine de parameters, chacun ayant son propre dataset et la liste déroulante résultante. Les parameters dépendaient des parameters précédents pour affiner la scope de la sélection finale, et lorsque le rapport était “affiché”, une procédure stockée était appelée à remplir.

La procédure stockée a reçu chacun des parameters transmis à partir du rapport. Il vérifiait une table de stockage dans la firebase database pour voir si des parameters avaient été enregistrés pour cet OrderID. Si non, alors il a sauvegardé tous les parameters. Si c’est le cas, il a mis à jour tous les parameters de cette commande (c’est le cas lorsque l’utilisateur change d’avis ultérieurement).

Lorsque le rapport s’exécute, il existe un jeu de données dsParameters qui est un texte SQL qui s’éteint et sélectionne la seule ligne pour cet ID de commande s’il en existe un. Chacun des parameters du rapport reçoit sa valeur par défaut de cet dataset et sa liste de sélection d’un dataset dédié à ce paramètre.

J’ai rencontré des problèmes avec le paramètre de sélection multiple. J’ai utilisé une commande join (@Value, “,”) dans la liste des parameters du dataset principal, en passant à la procédure stockée une chaîne délimitée par des virgules. Mais comment le restaurer? Vous ne pouvez pas renvoyer la chaîne délimitée par des virgules dans la zone des valeurs par défaut du paramètre.

J’ai dû créer un autre jeu de données pour diviser le paramètre, d’une manière similaire à celle dont vous parlez. Cela ressemble à ceci:

 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 

Chaque fois que le formulaire s’ouvre, cet dataset recherche dans la firebase database une chaîne enregistrée pour ce paramètre à plusieurs valeurs. S’il n’y en a pas, il renvoie null. S’il est activé, il parsing les virgules et crée une ligne pour chacune des valeurs.

Ensuite, la boîte de valeurs par défaut est définie sur cet dataset et fldDesc. Ça marche! Lorsque je choisis un ou plusieurs, ils sauvegardent et remplissent lorsque le formulaire est à nouveau ouvert.

J’espère que ça aide. J’ai cherché pendant un certain temps et je n’ai trouvé aucune mention de la sauvegarde de la chaîne de jointure dans une firebase database et de son parsing dans un jeu de données.

Ainsi, multiplier les valeurs de texte se retrouverait dans la requête avec des guillemets simples autour de chacun des éléments suivants = join (Parameters! Customer.Value, “‘,'”). Donc, après “.Value”, il s’agit d’une virgule, d’un guillemet double, d’un guillemet simple, d’une virgule, d’un guillemet simple, d’un guillemet double et d’un crochet. simples 🙂

cela a fonctionné pour un ensemble distinct de chaînes (par exemple “START”, “END”, “ERROR”, “SUCCESS”)

1) définir un paramètre de rapport (par exemple @log_status) et cocher “Autoriser plusieurs valeurs”
entrer la description de l'image ici

2) définir un dataset
3) ouvrez la fenêtre des propriétés du dataset
3a) dans l’onglet Requête entrez votre requête: par exemple

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

3b) dans l’onglet Paramètres, entrez votre paramètre, par exemple
Parametername: @log_status ; Parametervalue: <>
3c) pour le clic Expr sur le bouton “fx” et entrez:

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

entrer la description de l'image ici

fini! (c’est similaire à la solution d’Ed Harper, mais désolé de dire que cela n’a pas fonctionné pour moi)

La solution ci-dessous a fonctionné pour moi.

  1. Dans l’onglet Paramètres des propriétés de votre jeu de données, cliquez sur l’icône de l’expression (! http://chittagongit.com//images/fx-icon/fx-icon-16.jpg [symbole fx]) à côté du paramètre requirejs pour autoriser la virgule entrée délimitée pour.

  2. Dans la fenêtre d’expression qui apparaît, utilisez la fonction Split (Fonctions communes -> Texte). Exemple ci-dessous:

= Split (Paramètres! ParameterName.Value, “,”)

J’avais besoin d’une solution pour Oracle et j’ai trouvé que cela fonctionnait pour moi dans ma requête concernant mon rapport pour DB> = 10g.

select * from where in (sélectionnez regexp_substr (, ‘[^,] +’, 1, level) from double connect par regexp_substr (, ‘[^,] +’, 1, level) n’est pas null);

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