Lektion 5: Hinzufügen eines an eine benutzerdefinierte Funktion zu übergebenden Berichtsparameters

Aktualisiert: 12. Dezember 2006

Berichtsparameter können an Eingabeparameter gebunden sein, die an eine benutzerdefinierte Funktion oder an eine gespeicherte Prozedur in einer Datasetabfrage übergeben werden. Eine benutzerdefinierte Funktion kann eine Tabelle oder einen Skalarwert (einzelnes Ergebnis) zurückgeben. Mit einer Datasetabfrage wird grundsätzlich ein Ergebnis aus einer benutzerdefinierten Funktion oder gespeicherten Prozedur abgerufen. Ein Vorteil einer benutzerdefinierten Funktion im Vergleich zu einer gespeicherten Prozedur besteht darin, dass Sie sie direkt in eine Transact-SQL SELECT-Anweisung aufnehmen und damit das Funktionsergebnis mit anderen Spalten im Abfrageresultset kombinieren können.

Benutzerdefinierte Funktionen und gespeicherte Prozeduren akzeptieren mehrwertige Parameter, die Arrays sind, nicht direkt. Zum Übergeben eines mehrwertigen Arrays an eine gespeicherte Prozedur können Sie die Visual Basic-Funktion JOIN verwenden, um eine verkettete Zeichenfolge aller Werte zu erstellen, und die benutzerdefinierte Funktion so schreiben, dass sie einen Zeichenfolgenparameter mit mehreren Auswahlmöglichkeiten akzeptiert, die durch ein Trennzeichen Ihrer Wahl getrennt sind.

Die benutzerdefinierte Funktion ufnGetContactInformation akzeptiert einen Datenbankbezeichner für die Person.Contact-Tabelle und ruft Werte für ContactID, Name, Phone, EmailAddress, JobTitle und ContactType ab. In diesem Bericht erstellen Sie einen Parameter, mit dem es den Benutzern ermöglicht werden soll, eine vordefinierte Teilmenge von anzuzeigenden Feldern auszuwählen.

In dieser Lektion erstellen Sie einen neuen Bericht, der den Namen "Contact Information" und einen Parameter hat, der einen Datenbankbezeichner für die Person akzeptiert, die als Kontakt für eine Wiederverkäuferfiliale aufgeführt ist. Sie erstellen eine Abfrage, mit der die benutzerdefinierte Funktion ufnGetContactInformation aus der AdventureWorks-Datenbank aufgerufen wird. Darüber hinaus binden Sie den Berichtsparameter an den Eingabeparameter @ContactID der benutzerdefinierten Funktion.

Sie bereiten diesen Bericht dahingehend vor, dass er als Unterbericht im Resellers Worldwide-Bericht verwendet werden kann. Zu diesem Zweck erstellen Sie Parameter für die zu verwendende Hintergrund- und Textfarbe, entfernen die Leerzeichen um den Bericht und legen die Ränder sowie die Berichtsbreite fest. Sie erstellen einen mehrwertigen Parameter für diesen Bericht, mit dem im Hauptbericht angegeben werden kann, welche Felder für die Kontaktinformationen angezeigt werden sollen.

So öffnen Sie das Berichtsserverprojekt Advanced Parameters Tutorial

1. Öffnen Sie in SQL Server Business Intelligence Development Studio das Berichtsserverprojekt Advanced Parameters Tutorial, wenn es noch nicht geöffnet ist.

So erstellen Sie aus einer benutzerdefinierten Funktion einen neuen Bericht

1. Klicken Sie im Projektmappen-Explorer mit der rechten Maustaste auf den Ordner Berichte. Wählen Sie Hinzufügen und anschließend Neues Element aus. Das Dialogfeld Neues Element hinzufügen wird geöffnet.

2. Wählen Sie im Bereich Vorlagen die Option Bericht aus.

3. Geben Sie im Textfeld Name den Namen Contact Information.rdl ein.

4. Klicken Sie auf Hinzufügen. Der Bericht wird in der Datenansicht geöffnet.

5. Wählen Sie in der Dropdownliste Dataset die Option <Neues Dataset> aus. Das Dialogfeld Dataset wird geöffnet.

6. Geben Sie auf der Registerkarte Abfrage in das Textfeld Name den Namen ContactInformation ein.

7. Überprüfen Sie, ob die Datenquelle gleich Resellers ist.

8. Überprüfen Sie, ob der Befehlstyp gleich Text ist.

9. Fügen Sie folgende Abfrage in den Bereich Abfragezeichenfolge ein.

   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
   

   Diese Abfrage verwendet den @ContactID-Parameter, um einen Datenbankbezeichner an die benutzerdefinierte ufnGetContactInformation-Funktion zu übergeben, die ContactID als Eingabeparameter akzeptiert.

10. Klicken Sie auf der Symbolleiste auf Ausführen (!). Das Dialogfeld Abfrageparameter definieren wird geöffnet.

11. Geben Sie in der Spalte Parameterwert für @ContactID den Wert 1 ein. Mit dem Resultset werden die Kontaktinformationen für den Filialenkontakt angezeigt, der durch die ContactID angegeben wird. Die im Resultset zurückgegebenen Spalten sind in der Benutzerfunktion definiert.

12. (Optional) Überprüfen Sie, ob der Abfrageparameter @ContactID an den Berichtsparameter ContactID gebunden ist. Klicken Sie auf der Berichtssymbolleiste auf Ausgewähltes Dataset bearbeiten(…) und dann auf die Registerkarte Parameter. Der @ContactID-Parameter wird auf den Wert =Parameters!ContactID.Value festgelegt.

Im nächsten Arbeitsschritt erstellen Sie einen Standardwert für den Berichtsparameter ContactID. Durch die Angabe eines Standardwertes für die einzelnen Parameter wird der Bericht automatisch verarbeitet, wenn Sie auf Vorschau klicken.

So fügen Sie einen Standardwert für ContactID hinzu

1. Klicken Sie im Menü Bericht auf Berichtsparameter. Das Dialogfeld Berichtsparameter wird geöffnet. Dabei ist ContactID im Bereich Parameter ausgewählt.

2. Wählen Sie im Abschnitt Eigenschaften im Bereich Standardwerte die Option Nicht abgefragt aus. Geben Sie 1 ein.

3. Klicken Sie auf OK.

Im nächsten Arbeitsschritt erstellen Sie einen mehrwertigen Zeichenfolgenparameter, mit dem die Kontaktinformationen ausgewählt werden können, die angezeigt werden sollen. Sie erstellen eine Liste der nicht abgefragten, verfügbaren Werte für Telefon, E-Mail bzw. Keine.

So erstellen Sie den Berichtsparameter FieldsToDisplay

1. Wählen Sie in der Layoutansicht im Menü Bericht den Befehl Berichtsparameter aus. Das Dialogfeld Berichtsparameter wird geöffnet.

2. Klicken Sie auf Hinzufügen. Es wird ein neuer Parameter mit Standardwerten erstellt.

3. Geben Sie im Abschnitt Eigenschaften im Textfeld Name den Namen FieldsToDisplay ein. Überprüfen Sie, ob der Datentyp gleich String ist.

4. Geben Sie an der Eingabeaufforderung die Zeichenfolge Select contact information to display: ein.

5. Wählen Sie die Option Mehrwertig aus.

6. Überprüfen Sie, ob die Option Leeren Wert zulassen ausgewählt ist.

7. Wählen Sie im Abschnitt Verfügbare Werte die Option Nicht abgefragt aus. Verwenden Sie zum Eingeben von Werten die folgende Tabelle.

Wenn für einen Parameter verfügbare Werte vorliegen, müssen Sie einen verfügbaren Wert angeben, der <Leer> ist, um die Option Leeren Wert zulassen auszuwählen.

Im nächsten Arbeitsschritt erstellen Sie das Layout für diesen Bericht. Da der Bericht in einem Hauptbericht als Unterbericht verwendet wird, ist das Layout vereinfacht: In einem Textfeld wird der Kontaktname und die Berufsbezeichnung angezeigt, und in einem weiteren Textfeld werden E-Mail- und Telefoninformationen bedingt angezeigt.

So erstellen Sie das Berichtslayout für diese Informationen

1. Klicken Sie auf die Registerkarte Layout. Der Bericht wird in der Layoutansicht geöffnet.

2. Ziehen Sie aus der Toolbox ein Tabellenberichtselement auf die Entwurfsoberfläche.

3. Klicken Sie mit der rechten Maustaste auf eine Spaltenüberschrift, und wählen Sie Spalten löschen aus. Es bleiben zwei Spalten übrig.

4. Fügen Sie den folgenden Ausdruck in das erste Textfeld in der Detailzeile ein:

   =Fields!Name.Value & vbCrLf & "[ " & Fields!JobTitle.Value & " ]"
   

5. Fügen Sie den folgenden Ausdruck in das zweite Textfeld in der Detailzeile ein:

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

   Dieser Ausdruck sucht einfach in den Werten, die für den mehrwertigen FieldsToDisplay-Parameter ausgewählt sind. Wenn NONE ausgewählt ist (nur ein Parameter und die Bezeichnung lautet None), ergibt der Ausdruck einen leeren Wert. Wenn EmailAddress vorhanden ist, ergibt ein Teil des Ausdrucks "EmailAddress". Wenn Phone vorhanden ist, ergibt ein Teil des Ausdrucks "Phone". Diese beiden Werte sind mit einem Wagenrücklauf verkettet. Der gesamte ausgewertete Ausdruck wird im Inhalt des zweiten Feldes angezeigt.

   Mit diesem Beispiel werden folgende Punkte veranschaulicht:

   • Ermitteln der Anzahl der ausgewählten Werte in einem mehrwertigen Parameter: Parameters!FieldsToDisplay.Count
   • Für einen mehrwertigen Parameter: Testen der letzten Bezeichnung im Array auf eine bestimmte Zeichenfolge (in diesem Fall "None"): InStr("None",Parameters!FieldsToDisplay.Label(Parameters!FieldsToDisplay.Count-1))>0
   • Bedingtes Zurückgeben einer Zeichenfolge in Abhängigkeit davon, ob ein Wert in einem mehrwertigen Parameter gefunden wurde (dabei wird in der verketteten Zeichenfolge aller ausgewählten Werte nach dem mehrwertigen FieldsToDisplay-Parameter gesucht; wenn EmailAddress gefunden wurde, wird der spezielle Wert von EmailAddress aus dem Dataset zurückgegeben, und wenn EmailAddress nicht gefunden wurde, wird ein leerer Wert zurückgegeben): IIF(InStr(Join(Parameters!FieldsToDisplay.Value,","),"EmailAddress")>0,Fields!EmailAddress.Value,"")

   Hinweis:
   Die Visual Basic-Funktion IIF wertet alle Funktionsparameterwerte aus, die an sie übergeben werden. Demnach können Sie diesen Ausdruck nicht für Datasetfelder verwenden, die gleich NULL sein können.

6. (Optional) Klicken Sie auf Vorschau. Ändern Sie Parameterwerte für die beiden Parameter, und zeigen Sie die Ergebnisse an.

Im nächsten Arbeitsschritt formatieren Sie diesen Bericht so, dass er als Unterbericht verwendet werden kann. Somit wird er in einem Hauptbericht wie vorgesehen angezeigt, wenn die Kopf- und Fußzeile deaktiviert, die Tabellenbreite festgelegt, zusätzliche Leerzeichen entfernt und die Schriftart sowie der Hintergrund an den Hauptbericht angeglichen werden. Zudem geben Sie Parameter an, sodass der Farbhintergrund und die Schriftfarbe als Parameter aus dem Hauptbericht übergeben werden können.

So entfernen Sie Leerzeichen und legen die Berichtsgröße fest

1. Klicken Sie auf die Tabelle, um die Tabellenhandles anzuzeigen.

2. Klicken Sie mit der rechten Maustaste auf das Detailzeilenhandle. Wählen Sie im Kontextmenü die Optionen Tabellenkopf und Tabellenfuß aus, und legen Sie beide auf Aus fest. In der Tabelle wird nur eine Detailzeile angezeigt.

   In den nächsten drei Schritten müssen Sie die Berichtsbreite auf eine bekannte Größe festlegen. Diese Größe ist erforderlich, wenn Sie in der nächsten Lektion dem Hauptbericht ein Berichtselement für den Unterbericht hinzufügen.

3. Wählen Sie die Tabelle aus. Überprüfen Sie im Eigenschaftenfenster, ob die Tabellenbreite dem Wert 3 entspricht. Passen Sie diesen Wert auf das erforderliche Minimum an, um die Kontaktinformationen anzuzeigen.

   Mit der Tabellenbreite, die Sie für den Unterbericht definieren, wird bestimmt, wie der Unterbericht aussieht, wenn er im Hauptbericht angezeigt wird. Wenn Sie eine feste Größe für den Unterbericht festlegen möchten, legen Sie die Höhe und Breite auf die gewünschten Werte fest. Sie können auch die Eigenschaften CanGrow und CanShrink eines Textfeldes festlegen, damit das Textfeld dem Textinhalt entsprechend vertikal vergrößert und verkleinert werden kann. CanGrow und CanShrink werden nicht auf die horizontale Breite angewendet. Gegenwärtig können Textfelder nicht so festgelegt werden, dass die Breite anhand des Textes angepasst wird.

4. Wählen Sie das erste Spaltenheader-Textfeld aus. Scrollen Sie im Eigenschaftenfenster zu Breite, und geben Sie 1,5 oder mithilfe der Standardmaßeinheit einen beliebigen Wert ein, mit dem Sie die Breite auf die Hälfte der Tabelle festlegen.

5. Wählen Sie das zweite Spaltenheader-Textfeld aus. Scrollen Sie im Eigenschaftenfenster zu Breite, und geben Sie 1,5 oder mithilfe der Standardmaßeinheit einen beliebigen Wert ein, mit dem Sie die Breite auf die Hälfte der Tabelle festlegen.

6. Wählen Sie die Tabelle aus. Verschieben Sie die Tabelle mithilfe der Pfeiltasten an den Anfang der Berichtsseite, und richten Sie den Tabellenrand am Berichtsrand aus.

7. Bewegen Sie die Maus über dem Rand der Berichtsoberfläche (den gepunkteten weißen Hintergrund). Wenn sich der Mauszeiger in einen Doppelpfeil ändert, erfassen Sie den Rand der Berichtsoberfläche, und verschieben Sie ihn in die Nähe des Tabellenrandes. Somit werden Leerzeichen entfernt, wenn der Bericht gerendert wird.

8. Entfernen Sie entsprechend auch Leerzeichen aus dem unteren Bereich der Berichtsseite, indem Sie die Berichtsoberfläche in die Nähe des unteren Randes der Tabelle verschieben.

So fügen Sie Parameter zum Festlegen von Farben hinzu

1. Wählen Sie in der Layoutansicht im Menü Bericht den Befehl Berichtsparameter aus. Das Dialogfeld Berichtsparameter wird geöffnet.

2. Klicken Sie auf Hinzufügen. Es wird ein neuer Parameter mit Standardwerten erstellt.

3. Geben Sie im Abschnitt Eigenschaften im Textfeld Name den Namen BackgroundColor ein. Überprüfen Sie, ob der Datentyp gleich String ist.

4. Wählen Sie die Option Ausgeblendet aus. Das Textfeld Eingabeaufforderung ist deaktiviert.

5. Deaktivieren Sie die Option Leeren Wert zulassen.

6. Überprüfen Sie, ob im Abschnitt Verfügbare Werte die Option Nicht abgefragt ausgewählt ist. Lassen Sie die Werttabelle leer.

7. Klicken Sie im Abschnitt Standardwerte auf Nicht abgefragt, und geben Sie Azure ein.

   Im Unterbericht werden Parameterwerte verwendet, die aus dem Hauptbericht an den Unterbericht übergeben werden. Wenn im Hauptbericht für einen bestimmten Parameter kein Wert angegeben ist, wird der Standardwert des Parameters für den Unterbericht verwendet.

8. Erstellen Sie einen neuen Parameter mit dem Namen FontColor, indem Sie die Schritte 1 bis 6 ausführen.

9. Klicken Sie im Abschnitt Standardwerte auf Nicht abgefragt, und geben Sie SteelBlue ein.

10. Klicken Sie auf OK.

11. Klicken Sie in der Layoutansicht mit der rechten Maustaste auf die Tabellendetailzeile. Scrollen Sie im Eigenschaftenfenster zu BackgroundColor. Legen Sie den Wert auf den folgenden Ausdruck fest:

    =Parameters!BackgroundColor.Value
    

12. Scrollen Sie im Eigenschaftenfenster zu Color. Legen Sie den Wert auf den folgenden Ausdruck fest:

    =Parameters!FontColor.Value
    

13. Klicken Sie auf Vorschau. Überprüfen Sie, ob der Hintergrund der Tabellenzeile und die Schriftfarbe auf die Parameterwerte für BackgroundColor und FontColor festgelegt sind.

So fügen Sie eine Berichtsbeschreibung hinzu

1. Klicken Sie auf die Registerkarte Layout.

2. Wählen Sie im Menü Bericht den Befehl Berichtseigenschaften aus. Das Dialogfeld Berichtseigenschaften wird geöffnet.

3. Geben Sie im Textfeld Beschreibung folgenden Text ein: Subreport to show contact information.

4. Klicken Sie auf OK.

Nächste Schritte

Sie haben einen Bericht mit Kontaktinformationen für eine Wiederverkäuferfiliale erfolgreich erstellt. Sie haben den Bericht für die Verwendung als Unterbericht formatiert, indem Sie die Breite festgelegt, zusätzliche Leerzeichen entfernt und Parameter zur Steuerung der Darstellung und des Inhalts erstellt haben. In der nächsten Lektion fügen Sie diesen Bericht dem Resellers Worldwide-Bericht als Unterbericht hinzu. Siehe Lektion 6: Hinzufügen eines Unterberichts mit Parametern.

Änderungsverlauf

Siehe auch

Andere Ressourcen

Verwenden von Parametern in Reporting Services
Grundlagen zu benutzerdefinierten Funktionen

Hilfe und Informationen

Informationsquellen für SQL Server 2005