SQL Server 2008 R2 SSRS wartet mit einer interessanten Neuerung auf, nämlich der Suche in Datasets. Damit gibt es Möglichkeiten zwei Datasets in einem Datenbereich zu verknüpfen.
Grundsätzlich gilt: Ein Datenbereich in einem Report kann nur an ein Dataset gebunden sein. Es ist also sinnvoll, alle benötigten Felder bereits vom Datenbankserver in ein Dataset vereinigen zu lassen. Es sind aber auch Situationen denkbar, in denen es nicht möglich ist, die benötigten Felder in ein Dataset zu integrieren (z.B. bei der Nutzung von bereits bestehenden Shared Datasets oder bei verschiedenen Quellen). Die Möglichkeiten in einem Datenbereich auf Inhalte eines zweiten Datasets zuzugreifen waren bisher wenig komfortabel. Es gab dazu nur die Funktionen Last, First und die Aggregatfunktionen, die auf das gesamte Ziel-Dataset anzuwenden sind. Ein wahlfreier Zugriff auf eine beliebige Zeile in dem Ziel-Dataset, anhand eines Suchwertes, wurde nicht von SSRS unterstützt. Um das zu erreichen, war es nötig, das komplette Dataset in einem Hashtable zu cachen und dann mittels einer Custom-Code-Funktion den gesuchten Wert zu ermitteln.
Mit der Veröffentlichung von SQL Server 2008 R2 SSRS gibt es dafür jetzt die Lookup-Funktionen. Diese gibt es gleich in drei Versionen: Lookup, LookupSet und MultiLookup.
Die Lookup-Funktion wertet einen Wert (Ausdruck) pro Zeile aus und sucht durch Vergleich mit diesem Wert eine passende Zeile im Ziel-Dataset. LookupSet wertet ebenfalls einen Wert pro Zeile im aktuellen Dataset aus, liefert aber alle passenden Zeilen aus dem Ziel-Dataset. MultiLookup wertet mehrere Werte pro Zeile des aktuellen Datasets aus und liefert alle passenden Zeile aus dem Ziel-Dataset. Konkret wird für jede passende Zeile ein Wert zurückgegeben, der das Ergebnis der Auswertung eines Ausdrucks über die Felder der Zeile ist. Im einfachsten Fall wird der Wert eines der Felder zurückgegeben.
Diese drei Lookup-Funktionen sollen hier demonstriert werden.
Lookup Ein Beispiel für den Einsatz von Lookup:
Der Umsatz pro Mitarbeiter aus Dataset2 soll in der Tabelle angezeigt werden, die mit Dataset1 verknüpft ist.
Dazu wird ein neues Feld mit einem Ausdruck erstellt. Der hier verwendete Ausdruck lautet: =Lookup(Fields!MitarbeiterID.Value, Fields!PersonalID.Value, Fields!Umsatz.Value, „DataSet2“)
- Der erste Parameter, Fields!MitarbeiterID.Value, ist ein Feld der Tabelle (Datenbereichs), die an Dataset1 gebunden ist. Es ist aber auch möglich einen Ausdruck zu verwenden, um z.B. mehrere Felder zu konkatenieren, wenn der Vergleich mit den Zeilen im Ziel-Dataset mehrere Spalten berücksichtigen soll.
- Der zweite Parameter , Fields!PersonalID.Value , ist ein Feld aus dem Dataset2, in dem nach einer passenden Zeile gesucht werden soll. Es kann auch ein Ausdruck verwendet werden. Wie bei einem Join wird verglichen ob dieser Wert mit dem Suchwert (der erste Parameter) übereinstimmt.
- Der dritte Parameter, Fields!Umsatz.Value , gibt den Rückgabewert der Funktion an. Auch hier kann anstelle eines Feldes ein Ausdruck angegeben werden. Bei Lookup wird der Rückgabewert aus der ersten Zeile ermittelt, bei der die Suche erfolgreich war. Gibt es keine Zeile, die die Bedingung erfüllt, liefert der Lookup-Aufruf Nothing zurück.
- Der vierte Parameter benennt das Ziel-Dataset (hier Dataset2), in dem ein passender Wert gesucht wird.
Die mit Dataset1 verbundene Tabelle enthält jetzt die gewünschten Werte aus DataSet2.
LookupSet
Bei der Lookup-Funktion wird wie bereits beschrieben nur die erste Zeile berücksichtigt, auf die der Suchwert passt. LookupSet wertet immer alle Zeilen des Ziel-Datasets aus und liefert für jede passende Zeile einen Wert zurück. LookupSet gibt diese Werte in Form eines Array zurück. Dieses Array kann nicht direkt als Ausgabe eines Textfeldes verwendet werden. Es muss also eine Bearbeitung z.B. mit der VB-Funktion Join erfolgen wodurch aus dem Array ein anzeigefähiger Wert wird. Das Array kann auch als Argument für eine Custom-Code-Funktion verwendet werden, um einen Anzeigewert zu bestimmen.
Beispiel für die Verwendung von LookupSet
=Join(LookupSet(Fields!ProductCategoryID.Value, Fields!ProductCategoryID.Value, Fields!Name.Value, „DataSet2″),“, „)
Zu Jeder Zeile in DataSet1 werden alle passenden Zeilen aus DataSet2 gesucht. Join wandelt das Ergebnis-Array in einen String um, der anschließend im Feld Subcategory zu sehen ist.
Zu Jeder ProductCategory sind nun alle Subcategories in einem Feld zu sehen.
MultiLookup
MulitLookup nimmt keinen einzelnen Wert sondern ein Array von Suchwerten pro Zeile. Ein Aufruf von MultiLookup wird dann so durchgeführt, dass für jedes Element des Suchwerte-Arrays ein gesonderter Lookup-Aufruf durchgeführt wird. Die Ergebnisse der einzelnen Lookup’s werden dann dem Ergebnis-Array zugefügt.
Ein Mehrwertiger Parameter kann direkt als Suchwert verwendet werden. Ein String, der eine Liste von Werten enthält, muss erst in ein Array umgewandelt werden. Dazu eignet sich die VB-Funktion Split.
Beispiel: Ein Mehrwertiger Parameter enthält mehrere Monate, die als Auflistung von Zahlwerten angegeben sind. Mithilfe eines Aufrufes der Funktion MultiLookup werden die Zahlwerte in den jeweiligen Monatsnamen umgewandelt. Dabei wird das Dataset Monatsnamen als Ziel-Dataset für die Suche verwendet.
Anzeige der Parameterwerte in einem Textfeld mit dem Ausdruck =Join(Parameters!Monate.Value,“, „)
Anzeige der Werte nach Ã?nderung des Ausdrucks auf =Join(MultiLookup(Parameters!Monate.Value, Fields!MonatNr.Value,Fields!MonatName.Value,“Monatsnamen“),“, „)