Lezione 5: Aggiunta di un parametro del report da passare a una funzione definita dall'utente

Articolo
12/15/2008

Data aggiornamento: 12 dicembre 2006

I parametri del report possono essere associati a parametri di input passati a una funzione definita dall'utente (ufn) o a una stored procedure in una query del set di dati. Le funzioni definite dall'utente possono restituire un valore di tabella o scalare (risultato singolo). Per definizione, una query del set di dati recupera un risultato da una stored procedure o una funzione definita dall'utente. Uno dei vantaggi delle funzioni definite dall'utente rispetto alle stored procedure è che possono essere incluse direttamente in un'istruzione Transact-SQL SELECT. In questo modo è possibile unire il risultato della funzione ad altre colonne del set di risultati della query.

Le funzioni definite dall'utente e le stored procedure non accettano direttamente parametri multivalore, che sono matrici. Per passare una matrice multivalore a una stored procedure, è possibile utilizzare la funzione JOIN di Visual Basic per creare una stringa concatenata di tutti i valori e scrivere la funzione definita dall'utente per accettare un parametro di stringa con più selezioni separate dal delimitatore desiderato.

La funzione definita dall'utente ufnGetContactInformation accetta un identificatore del database per la tabella Person.Contact e recupera i valori per ContactID, Name, Phone, EmailAddress, JobTitle e ContactType. In questo report verrà creato un parametro per consentire agli utenti di scegliere un subset di campi predefinito per la visualizzazione.

In questa lezione verrà creato un nuovo report denominato "Contact Information" con un parametro che accetta un identificatore del database per la persona indicata come contatto per un negozio rivenditore. Verrà creata una query che chiama la funzione definita dall'utente ufnGetContactInformation dal database AdventureWorks e il parametro del report verrà associato al parametro di input @ContactID della funzione definita dall'utente.

Si preparerà il report per l'utilizzo come sottoreport nel report Resellers Worldwide, creando parametri per il colore del testo e dello sfondo, rimuovendo lo spazio vuoto attorno al report e impostando i margini e la larghezza del report. Verrà creato un parametro multivalore per il report che consente al report principale di specificare quali campi visualizzare per le informazioni di contatto.

Per aprire il progetto Server report Advanced Parameters Tutorial

In SQL Server Business Intelligence Development Studio aprire il progetto Server report Advanced Parameters Tutorial, se non è ancora aperto.

Per creare un nuovo report da una funzione definita dall'utente

In Esplora soluzioni fare clic con il pulsante destro del mouse sulla cartella Report. Scegliere Aggiungi e quindi Nuovo elemento. Verrà visualizzata la finestra di dialogo Aggiungi nuovo elemento.
Nel riquadro Modelli selezionare Report.
Nella casella di testo Nome digitare Contact Information.rdl.
Fare clic su Aggiungi. Il report verrà aperto nella visualizzazione Dati.
Nell'elenco a discesa Set di dati selezionare <Nuovo set di dati>. Verrà visualizzata la finestra di dialogo Set di dati.
Nella casella di testo Nome della scheda Query digitare ContactInformation.
Verificare che Origine dati sia Resellers.
Verificare che Tipo di comando sia Text.
Nel riquadro Stringa query incollare la query seguente.
```
SELECT udf.ContactID, udf.FirstName + N' ' + udf.LastName AS Name,
   c.Phone, c.EmailAddress, udf.JobTitle, udf.ContactType
   FROM ufnGetContactInformation(@ContactID) udf
   JOIN Person.Contact c ON udf.ContactID = c.ContactID
```
Questa query utilizza un parametro denominato @ContactID per passare un identificatore del database alla funzione definita dall'utente ufnGetContactInformation che accetta ContactID come parametro di input.
Fare clic su Esegui (!) sulla barra degli strumenti. Verrà visualizzata la finestra di dialogo Definisci parametri query.
Nella colonna Valore parametro digitare 1 per @ContactID. Nel set di risultati vengono visualizzate le informazioni di contatto per il contatto del negozio identificato con ContactID. Le colonne restituite nel set di risultati sono definite nella funzione definita dall'utente.
Verificare che il parametro di query @ContactID sia associato al parametro del report ContactID (facoltativo). Nella barra degli strumenti del report fare clic su Modifica set di dati selezionato(…) e quindi sulla scheda Parametri. Il parametro @ContactID è impostato sul valore =Parameters!ContactID.Value.

Nella procedura seguente verrà creato un valore predefinito per il parametro del report ContactID. La definizione di un valore predefinito per ogni parametro consente l'elaborazione automatica del report quando si fa clic su Anteprima.

Per aggiungere un valore predefinito per ContactID

Scegliere Parametri report dal menu Report. Verrà visualizzata la finestra di dialogo Parametri report con ContactID selezionato nel riquadro Parametri.
Nella sezione Proprietà selezionare Non da query nella sezione Valori predefiniti. Digitare 1.
Fare clic su OK.

Nella procedura seguente verrà creato un parametro di stringa multivalore con cui selezionare le informazioni di contatto da visualizzare. Verrà creato un elenco di valori disponibili non da query per Phone, Email o None.

Per creare un parametro del report FieldsToDisplay

Nella visualizzazione Layout scegliere Parametri report dal menu Report. Verrà visualizzata la finestra di dialogo Parametri report.
Fare clic su Aggiungi. Verrà creato un nuovo parametro con valori predefiniti.
Nella sezione Proprietà, digitare FieldsToDisplay nella casella di testo Nome. Verificare che il tipo di dati sia String.
In Messaggio di richiesta digitare Select contact information to display:.
Selezionare la casella di controllo Multivalore.
Verificare che la casella di controllo Consenti nessun valore sia selezionata.
Nella sezione Valori disponibili selezionare Non da query. Utilizzare la tabella seguente per immettere i valori.

Etichetta	Valore
Phone	Phone
Email	EmailAddress
None	<Vuoto>

Se ci sono valori disponibili per un parametro, è necessario indicare un valore disponibile <Blank> per selezionare la casella di controllo Consenti nessun valore.

Nella procedura seguente verrà creato il layout del report. Poiché il report verrà utilizzato come sottoreport in un report principale, il layout è semplice: una casella di testo con il nome e il titolo professionale del contatto e una casella di testo con la visualizzazione condizionale del numero di telefono e dell'indirizzo di posta elettronica.

Per creare il layout del report per queste informazioni

Fare clic sulla scheda Layout. Il report verrà aperto nella visualizzazione Layout.
Dalla Casella degli strumenti trascinare l'elemento del report Tabella nell'area di progettazione.
Fare clic con il pulsante destro del mouse su un'intestazione di colonna e scegliere Elimina colonne. Rimarranno due colonne.
Nella prima casella di testo nella riga di dettaglio incollare l'espressione seguente:
```
=Fields!Name.Value & vbCrLf & "[ " & Fields!JobTitle.Value & " ]"
```
Nella seconda casella di testo nella riga di dettaglio incollare l'espressione seguente:
```
=IIF((Parameters!FieldsToDisplay.Count=1) AND 
   (InStr("None",Parameters!FieldsToDisplay.Label(
          Parameters!FieldsToDisplay.Count-1))>0),"",
    IIF(InStr(Join(Parameters!FieldsToDisplay.Value,","),
       "EmailAddress")>0,Fields!EmailAddress.Value,"") + 
vbCrLf + IIF(InStr(Join(Parameters!FieldsToDisplay.Value,","),
       "Phone")>0,Fields!Phone.Value,""))
```
Questa espressione esamina semplicemente i valori selezionati per il parametro multivalore FieldsToDisplay. Se è selezionato NONE (un solo parametro e l'etichetta è None), l'espressione non restituisce alcun valore. Se è disponibile EmailAddress, parte dell'espressione restituisce "EmailAddress". Se è disponibile Phone, parte dell'espressione restituisce "Phone". Questi due valori sono concatenati con un ritorno a capo. L'intera espressione valutata viene visualizzata nel contenuto della seconda casella.

In questo esempio sono illustrati i punti seguenti:
- Ricerca del numero di valori selezionati in un parametro multivalore: Parameters!FieldsToDisplay.Count
- Per un parametro multivalore, verifica dell'ultima etichetta nella matrice per una stringa specifica (in questo caso "None"): InStr("None",Parameters!FieldsToDisplay.Label(Parameters!FieldsToDisplay.Count-1))>0
- Restituzione condizionale di una stringa basata sul fatto se un valore si trova in un parametro multivalore (viene eseguita la ricerca nella stringa concatenata di tutti i valori selezionati per il parametro multivalore FieldsToDisplay; se viene trovato EmailAddress, viene restituito il valore specifico di EmailAddress dal set di dati, se invece non viene trovato non viene restituito alcun valore): IIF(InStr(Join(Parameters!FieldsToDisplay.Value,","),"EmailAddress")>0,Fields!EmailAddress.Value,"")
[!NOTA] La funzione IIF di Visual Basic valuta tutti i valori del parametro della funzione passati. Non è pertanto possibile utilizzare questa espressione con campi del set di dati che possono avere valore Null.
Fare clic su Anteprima (facoltativo). Modificare i valori dei due parametri e visualizzare i risultati.

Nella procedura seguente il report verrà formattato per l'utilizzo come sottoreport affinché venga visualizzato in modo ottimale in un report principale: verranno disattivati l'intestazione e il piè di pagina, verrà impostata la larghezza della tabella, verrà rimosso lo spazio vuoto superfluo e verranno impostati il tipo di carattere e lo sfondo in modo che corrispondano a quelli del report principale. Verranno inoltre forniti parametri in modo che il colore dello sfondo e del tipo di carattere possano essere passati come parametri dal report principale.

Per rimuovere lo spazio vuoto e impostare le dimensioni del report

Fare clic all'interno della tabella per visualizzarne i quadratini di ridimensionamento.
Fare clic con il pulsante destro del mouse sull'handle di riga di dettaglio. Dal menu di scelta rapida scegliere Intestazione tabella e Piè di pagina tabella e impostarli su Disattivato. Nella tabella verrà visualizzata solo la riga di dettaglio.

Nei tre passaggi seguenti è necessario regolare la larghezza del report su una dimensione nota. Questa dimensione sarà necessaria quando si aggiunge un elemento del report sottoreport al report principale nella lezione successiva.
Selezionare la tabella. Nella finestra Proprietà verificare che la larghezza della tabella sia 3. Regolare questo valore sul valore minimo necessario per visualizzare le informazioni di contatto.

La larghezza della tabella definita per il sottoreport determina l'aspetto del sottoreport quando viene visualizzato nel report principale. Per impostare dimensioni fisse per il sottoreport, impostare l'altezza e la larghezza sui valori desiderati. È inoltre possibile impostare le proprietà CanGrow e CanShrink di una casella di testo per consentirne l'espansione e la riduzione in verticale in base alle dimensioni del testo. CanGrow e CanShrink non sono applicabili alla larghezza orizzontale. Non è possibile impostare una casella di testo in modo che la relativa larghezza venga regolata automaticamente in base al testo contenuto.
Selezionare la prima casella di testo di intestazione di colonna. Nella finestra Proprietà andare a Width e immettere 1.5 o, utilizzando l'unità di misura predefinita, immettere il valore necessario per impostare la larghezza su metà della tabella.
Selezionare la seconda casella di testo di intestazione di colonna. Nella finestra Proprietà andare a Width e immettere 1.5 o, utilizzando l'unità di misura predefinita, immettere il valore necessario per impostare la larghezza su metà della tabella.
Selezionare la tabella. Con i tasti di direzione spostare la tabella nella parte superiore della pagina del report e allineare il bordo della tabella con quello del report.
Passare il mouse sul bordo dell'area del report (lo sfondo bianco punteggiato). Quando il mouse si trasforma in freccia doppia, selezionare il bordo dell'area del report e spostarlo accanto al bordo della tabella. In questo modo viene eliminato lo spazio vuoto quando si esegue il rendering del report.
In modo analogo, rimuovere lo spazio vuoto dalla parte inferiore della pagina del report spostando l'area del report vicino alla parte inferiore della tabella.

Per aggiungere parametri per impostare i colori

Nella visualizzazione Layout scegliere Parametri report dal menu Report. Verrà visualizzata la finestra di dialogo Parametri report.
Fare clic su Aggiungi. Verrà creato un nuovo parametro con valori predefiniti.
Nella sezione Proprietà digitare BackgroundColor nella casella di testo Nome. Verificare che il tipo di dati sia String.
Selezionare la casella di controllo Nascondi. La casella di testo Messaggio di richiesta verrà disattivata.
Deselezionare la casella di controllo Consenti nessun valore.
Nella sezione Valori disponibili verificare che Non da query sia selezionato. Lasciare vuota la tabella dei valori.
Nella sezione Valori predefiniti selezionare Non da query e digitare Azure.

I valori dei parametri passati dal report principale a un sottoreport verranno utilizzati nel sottoreport. Se nel report principale non è specificato un valore per un determinato parametro, verrà utilizzato il valore predefinito del parametro del sottoreport.
Creare un nuovo parametro denominato FontColor eseguendo i passaggi da 1 a 6.
Nella sezione Valori predefiniti selezionare Non da query e digitare SteelBlue.
Fare clic su OK.
Nella visualizzazione Layout fare clic con il pulsante destro del mouse sulla riga di dettaglio della tabella. Nella finestra Proprietà andare alla proprietà BackgroundColor. Impostare il valore sull'espressione seguente:
```
=Parameters!BackgroundColor.Value
```
Nella finestra Proprietà andare alla proprietà Color. Impostare il valore sull'espressione seguente:
```
=Parameters!FontColor.Value
```
Fare clic su Anteprima. Verificare che lo sfondo della riga della tabella e il colore del tipo di carattere siano impostati sui valori per BackgroundColor e FontColor.

Per aggiungere una descrizione del report

Fare clic sulla scheda Layout.
Scegliere Proprietà report dal menu Report. Verrà visualizzata la finestra di dialogo Proprietà report.
Nella casella Descrizione digitare il testo seguente: Subreport to show contact information.
Fare clic su OK.

Passaggi successivi

In questo modo è stato creato un report per visualizzare le informazioni di contatto di un negozio rivenditore. Il report è stato formattato per essere utilizzato come sottoreport impostandone la larghezza, rimuovendo lo spazio vuoto superfluo e creando parametri per controllarne l'aspetto e il contenuto. Nella lezione successiva questo report verrà aggiunto come sottoreport al report Resellers Worldwide. Vedere Lezione 6: Aggiunta di un sottoreport con parametri.

Cronologia modifiche

Versione	Cronologia
12 dicembre 2006	Contenuto modificato: Correzione dell'esempio di codice con `udf.ContactID = c.ContactID`.

Vedere anche

Altre risorse

Utilizzo di parametri in Reporting Services
Nozioni fondamentali sulle funzioni definite dall'utente

Guida in linea e informazioni

Assistenza su SQL Server 2005

Condividi tramite