Passaggio di più valori per un singolo parametro in Reporting Services

Ho diversi parametri Multi-Select nel mio rapporto. Sto cercando di trovare un modo per passare più valori per un singolo parametro nella stringa di query Web? Se passo in un singolo valore, funziona bene.

Il report funziona bene selezionando più scelte per un singolo parametro. Il mio problema si trova nella stringa di query web.

Sebbene la soluzione di John Sansom funzioni, c’è un altro modo per farlo, senza dover utilizzare una UDF con valore scalare potenzialmente inefficiente. Nel report SSRS, nella scheda Parametri della definizione della query, impostare il valore del parametro su

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

Nella tua query, puoi quindi fare riferimento al valore in questo modo:

 where yourColumn in (@) 

Questo è quello che uso quando si passa un parametro multi-select ad un altro multi-select param.

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

Questa è una delle funzionalità supportate in SQL Reporting Services.

Quello che devi fare è passare tutti gli elementi selezionati come una singola stringa alla stored procedure. Ogni elemento all’interno della stringa sarà separato da una virgola.

Quello che faccio allora è dividere la stringa usando una funzione che restituisce la stringa fornita come una tabella. Vedi sotto.

 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 

Puoi quindi fare riferimento ai risultati nella clausola where della tua query principale in questo modo:

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

Spero che questa soluzione ti serva. Per favore sentiti libero di porre qualsiasi domanda tu possa avere.

Cheers, John

John Sansom e Ed Harper hanno grandi soluzioni. Tuttavia, non ero in grado di farli funzionare quando si tratta di campi ID (ad es. Interi). Ho modificato la funzione di divisione di seguito per eseguire il CAST dei valori come numeri interi in modo che la tabella si unisca con le colonne della chiave primaria. Ho anche commentato il codice e aggiunto una colonna per l’ordine, nel caso in cui l’ordine delimitato dalla lista fosse 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 

Utilizzare questa funzione come precedentemente indicato con:

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

ORACOLO:

La frase “IN” (soluzione di Ed) non funzionerà contro una connessione Oracle (almeno la versione 10). Tuttavia, ha trovato questo semplice work-around che fa. Utilizzando la scheda del parametro del set di dati, converti il ​​parametro multi-valore in un CSV:

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

Quindi nella clausola WHERE dell’istruzione SQL utilizzare la funzione di instring per verificare una corrispondenza.

  INSTR(:name, TABLE.FILENAME) > 0 

Mi sono imbattuto in un problema con il meraviglioso meraviglioso fn_MVParam. SSRS 2005 ha inviato i dati con un apostrofo come 2 virgolette.

Ho aggiunto una riga per risolvere questo problema.

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

La mia versione di fn usa anche varchar invece di 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 

Modifica della grande soluzione John, risolvere:

  • Errore “2 virgolette”
  • spazio dopo uno di pezzo nel parametro

    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 

Solo un commento: mi sono imbattuto in un mondo di dolore cercando di ottenere una clausola IN per funzionare in una connessione a Oracle 10g. Non penso che la query riscritta possa essere correttamente passata a un db 10g. Ho dovuto abbandonare completamente il multi-valore. La query restituirebbe i dati solo quando è stato scelto un singolo valore (dal selettore dei parametri multi-valore). Ho provato i driver MS e Oracle con gli stessi risultati. Mi piacerebbe sapere se qualcuno ha avuto successo con questo.

  1. Creare il set di dati per l’elenco nel report
  2. Fare clic con il tasto destro del mouse sul parametro e selezionare i valori disponibili
  3. Seleziona il set di dati appena creato come set di dati
  4. Aggiungi il valore che passa alla stored procedure come campo valore
  5. Aggiungi la descrizione del parametro al campo label (se il parametro è customerID allora l’etichetta potrebbe essere CustomerName ex.)
  6. Infine, aggiungi il seguente codice alla procedura memorizzata

dichiara @paramName AS NVARCHAR (500),

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

Questo funziona alla grande per me:

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

Sarebbe probabilmente più semplice aggiungere prima i valori multipli a una tabella e poi unirli o qualsiasi altra cosa desideri (anche con i caratteri jolly) o salvare i dati in un’altra tabella per utilizzarli in un secondo momento (o anche aggiungere i valori a un’altra tabella) .

Impostare il valore del parametro tramite espressione nel set di dati:

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

La query stessa:

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

Esempio di caratteri jolly:

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

Mi piacerebbe capire un modo per farlo senza SQL dinamico, ma non penso che funzionerà a causa del modo in cui SSRS passa i parametri alla query effettiva. Se qualcuno lo sa meglio, per favore fatemelo sapere.

Se si desidera passare più valori in RS tramite una stringa di query, è sufficiente ripetere il parametro del report per ciascun valore.

Per esempio; Ho una colonna RS chiamata COLS e questa colonna si aspetta uno o più valori.

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

Quello che puoi fare è aggiungere questo codice nella procedura memorizzata:

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

(Supponendo che @s sia una stringa multivalore (come “A, B, C”))

Sono nuovo nel sito e non riesco a capire come commentare una risposta precedente, che è ciò che ritengo dovrebbe essere. Non ho nemmeno potuto votare il post di Jeff, che credo mi abbia dato la risposta. In ogni modo …

Mentre posso vedere come funzionano alcuni dei post migliori e le modifiche successive, ho solo accesso in lettura al database, quindi nessuna soluzione UDF, SP o basata su visualizzazione funziona per me. Quindi la soluzione di Ed Harper sembrava buona, tranne per il commento di VenkateswarluAvula che non puoi passare una stringa separata da virgola come parametro in una clausola WHERE IN e aspettarti che funzioni come ti serve. Ma la soluzione di Jeff per ORACLE 10g colma questa lacuna. Li ho messi insieme al blog post di Russell Christopher su 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 e ho la mia soluzione:

Crea il tuo parametro multi-selezione MYPARAMETER usando qualsiasi fonte di valori disponibili (probabilmente un set di dati). Nel mio caso, la multi-selezione proveniva da un gruppo di voci di testo, ma sono sicuro che con alcuni aggiustamenti funzionerebbe con altri tipi. Se si desidera selezionare Seleziona tutto come posizione predefinita, impostare la stessa origine del valore predefinito. Questo ti dà la tua interfaccia utente, ma il parametro creato non è il parametro passato al mio SQL.

Passando allo SQL, e la soluzione di Jeff al problema WHERE IN (@MYPARAMETER), ho un problema tutto mio, in quel 1 dei valori (‘Charge’) appare in uno degli altri valori (‘Non Charge’) ), il che significa che CHARINDEX potrebbe trovare un falso positivo. Avevo bisogno di cercare il parametro per il valore delimitato sia prima che dopo. Ciò significa che devo assicurarmi che l’elenco separato da virgole abbia anche una virgola iniziale e finale. E questo è il mio snippet SQL:

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

Il bit nel mezzo è creare un altro parametro (nascosto nella produzione, ma non durante lo sviluppo) con:

  • Un nome di MYPARAMETER_LIST
  • Un tipo di testo
  • Un singolo valore disponibile di ="," + join(Parameters!MYPARAMETER.Value,",") + "," e un’etichetta che
    non importa (dato che non verrà visualizzato).
  • Un valore predefinito esattamente uguale
  • Per sicurezza, imposto sempre Aggiorna in entrambe le proprietà avanzate dei parametri

È questo parametro che viene passato a SQL, che sembra essere una stringa ricercabile ma che SQL gestisce come qualsiasi parte di testo.

Spero che mettere insieme questi frammenti di risposte aiuti qualcuno a trovare quello che stanno cercando.

In passato ho fatto ricorso a stored procedure e a una funzione per selezionare più anni in una query di SQL Server per i servizi di reporting. L’uso dell’espressione Join nel valore del parametro di query, come suggerito da Ed Harper, non funzionerebbe ancora con una clausola SQL IN nell’istruzione where. La mia risoluzione era quella di utilizzare quanto segue nella clausola where insieme al parametro Join expression: e charindex (cast (Schl.Invt_Yr come char (4)), @Invt_Yr)> 0

Si tratta di utilizzare la funzione join per salvare un parametro multi-valore e quindi ripristinare le stesse selezioni esatte dal database in un secondo momento.

Ho appena terminato un report che richiedeva il salvataggio dei parametri e quando il report viene riaperto (il report riceve un parametro OrderID), i valori precedentemente scelti dall’utente devono essere nuovamente selezionati.

Il rapporto utilizzava una mezza dozzina di parametri, ognuno dei quali aveva il proprio set di dati e il conseguente elenco a discesa. I parametri dipendevano dai parametri precedenti per restringere l’ambito della selezione finale e quando il report veniva “visto” veniva richiamata una procedura memorizzata.

La stored procedure ha ricevuto ciascuno dei parametri passati dal report. Ha controllato una tabella di archiviazione nel database per verificare se sono stati salvati tutti i parametri per tale ID ordine. In caso contrario, ha salvato tutti i parametri. Se è così, ha aggiornato tutti i parametri per quell’ordine (questo è il caso in cui l’utente cambia idea in seguito).

Quando viene eseguito il report, c’è un set di dati dsParameters che è un testo SQL che si spegne e seleziona la singola riga per quel orderID se ce n’è uno. Ciascuno dei parametri nel report ottiene il suo valore predefinito da questo set di dati e la sua lista di selezione da un set di dati dedicato a quel parametro.

Ho avuto problemi con il parametro multi-select. Ho usato un comando join (@Value, “,”) nell’elenco dei parametri del set di dati principale, passando alla stored procedure una stringa delimitata da virgole. Ma come ripristinarlo? Non è ansible alimentare la stringa delimitata da virgole nella casella dei valori predefiniti del parametro.

Ho dovuto creare un altro set di dati per dividere il parametro, in un modo simile a quello di cui stai parlando. Sembra questo:

 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 

Ogni volta che si apre il modulo, questo set di dati controlla il database per una stringa salvata per questo parametro multivalore. Se non ce n’è uno, restituisce null. Se è attivo, analizza le virgole e crea una riga per ciascuno dei valori.

Quindi la casella dei valori predefiniti è impostata su questo set di dati e fldDesc. Funziona! Quando ne scelgo uno o molti, salvano e si riempiono di nuovo quando il modulo viene riaperto.

Spero che aiuti. Ho cercato per un po ‘e non ho trovato alcuna menzione di salvare la stringa di join in un database e quindi analizzarla in un set di dati.

Quindi moltiplicare i valori di testo finirebbe nella query con le virgolette singole attorno a ogni I usato = join (Parametri! Customer.Value, “‘,'”). Quindi dopo “.Value” che è una virgola, virgolette, virgolette singole, virgole, virgolette singole, virgolette doppie, parentesi chiusa. Semplici 🙂

questo ha funzionato per un insieme distinto di stringhe (ad esempio “START”, “END”, “ERROR”, “SUCCESS”)

1) definire un parametro di rapporto (ad esempio @log_status) e selezionare “Consenti valori multipli”
inserisci la descrizione dell'immagine qui

2) definire un set di dati
3) aprire la finestra delle proprietà del set di dati
3a) nella Query-Tab inserisci la tua richiesta: es

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

3b) nella Parameters-Tab inserisci il tuo Parametro, es
Parametername: @log_status ; Parametervalue: < >
3c) per l’expr cliccare sul pulsante “fx” e inserire:

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

inserisci la descrizione dell'immagine qui

finito! (è simile alla soluzione di Ed Harper, ma mi dispiace dirlo non ha funzionato per me)

La soluzione seguente ha funzionato per me.

  1. Nella scheda parametri delle proprietà del set di dati fai clic sull’icona dell’espressione (! http://chittagongit.com//images/fx-icon/fx-icon-16.jpg [simbolo fx]) accanto al parametro devi consentire la virgola voce delimitata per.

  2. Nella finestra dell’espressione che appare, utilizzare la funzione Dividi (Funzioni comuni -> Testo). Esempio mostrato di seguito:

= Split (Parametri! ParameterName.Value, “”)

Avevo bisogno di una soluzione per Oracle e ho trovato che questo funzionava per me nella mia query per il mio rapporto per DB> = 10g.

selezionare * da dove in (selezionare regexp_substr (, ‘[^,] +’, 1, level) da dual connect di regexp_substr (, ‘[^,] +’, 1, level) non è nullo);

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