Gastbeitrag: Noch flexiblere Reports

Ein (der erste) Gastbeitrag auf Xojoblog von Martin T. Vielen Dank dafür!

Aufbauend auf den Beitrag vom 5. April 2017 geht es mit diesem Thema hier ein wenig weiter. Hilfreich dafür war der Beitrag „Xojo Reports: Query multiple tables in Body-Section (inline Reports)“ im Xojo-Forum.

Der Umfang von Xojos Report-Engine wird hier und da sehr kritisiert. Das liegt zum Teil an jahrelangen nicht behobenen „groben“ Bugs wie auch beispielsweise an der fehlenden Möglichkeit, eine dynamische Zeilenhöhe für Textausgaben mittels ReportField innerhalb des Body-Bereichs geliefert zu bekommen. Aber einige Beschränkungen lassen sich einfach umgehen.

Nehmen wir an, wir möchten im dynamischen Body-Bereich die Daten mehrerer Tabellen abfragen. Das geht nur solange gut, wie die unterschiedlichen Tabellen gleichnamige Spalten haben, die im Body-Bereich eines Report, zumeist über die ReportField.DataField-Eigenschaft abgefragt werden.

Als Beispiel denken wir uns eine Datenbank mit zwei Tabellen. Die eine nennen wir Gerichte und die andere Zutaten. Beide besitzen eine Spalte Name. Gerichte hat noch eine zusätzliche Spalte namens Kochdauer, und die Zutatentabelle noch eine Spalte namens Herkunftsland.

Union Tables
Die Koch-Datenbank, Tabelle Gerichte im Fokus.

Fügen wir einem Projekt einen neuen Report hinzu. In dessen Body-Bereich setzen wir nun zwei ReportFields. Das eine nennen wir NameField. In dessen DataField schreiben wir Name.

Mit der Datenbank-SELECT-Abfrage können wir nun alle Datensätze der Gerichte und Zutaten Tabellen hintereinander abrufen:

 SELECT * FROM Gerichte, Zutaten;

Nun möchten wir aber noch ein zweites ReportField, welches uns die Kochdauer (für Gerichte) bzw. das Herkunftsland (für Zutaten) ausgibt. Ja, wie benennen wir nun das zweite ReportField? Lediglich das Namensfeld gleicht sich in beiden Tabellen. Die zweite Spalte jedoch unterscheidet sich im Namen von der ersten. Also bleiben wir auch unspezifisch und benennen das zweite ReportField Col2Field, wobei wir als DataField-Eigenschaft Col2 angeben. Col2 ist eine temporär generierte Spalte. Sie wird nur bei der Abfrage generiert.

Bildschirmfoto 2018-04-29 um 12.18.36.png

Daten aller Tabellen, vereinigt euch!

Wie füllen wir nun den Report einerseits mit Gerichten und andererseits mit den Zutaten?

 

Hier helfen wir uns mit der SQL-Anweisung UNION, die Daten aus unterschiedlichen Tabellen abfragen kann. Ihre Grundstruktur:

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE bedingung]

UNION

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE bedingung]

Wichtig dabei: Die Anzahl der abgefragten Spalten in den Tabellen muss identisch sein! Benötigen wir gar nicht so viele Daten aus einer Tabelle, oder besitzt diese gar keine passende Stelle, können wir uns via SQL leere Felder liefern lassen. Und wir können eigene Spalten per Code generieren. Der Select

SELECT '1Überschrift' AS 'Type', 'Gerichte' AS 'Name', '' AS 'Col2'

etwa liefert einen Recordset mit einem Datensatz, darin das generierte Feld Type mit dem Inhalt „1Überschrift“, das Feld Name mit dem erzeugten Wert „Gerichte“, und das Feld col2 bleibt leer.

Type verwenden wir, um während des Report.Runs das Feld Name zu formatieren. Die Spalte wird sozusagen in der SQL-Abfrage temporär erzeugt, aber nicht physisch. Sie dient dazu, die Einträge der Tabellen später in der gewünschten Reihenfolge zu auszugeben. Deswegen werden den Type-Bezeichnungen (Überschrift, Gerichte und Zutaten) auch Zahlen vorangestellt. Das ist ein Trick in der SQL-Abfrage, da der UNION-Befehl nicht mit dem GROUP BY-Parameter zusammenarbeitet bzw. Xojo-Reports dann nicht das gewünschte Ergebnis generieren würden. Die zwei Zeilen

SELECT '1Überschrift' AS 'Type', 'Gerichte' AS 'Name', '' AS 'Col2'

und

SELECT '3Überschrift' AS 'Type', 'Zutaten' AS 'Name', '' AS 'Col2'

sind optional, um später den abgefragten Zeilen eine Überschrift, wonach sie „gruppiert“ sind, zuzuweisen. Man kann aber auch auf diese zwei Zeilen verzichten. Dann jedoch werden die Daten der zwei Tabellen direkt hintereinander ausgegeben.

Man könnte auf diese Art und Weise auch noch Leerzeilen zwischen den Überschriften und den Einträgen einfügen, dann heben sich die Überschriften noch besser ab.

Im Beispielprojekt sind alle drei Varianten als SQL-Konstanten eingefügt. Probieren Sie es einmal aus!

Mit ein bisschen Fleißarbeit kann man also auch dynamische SQL-UNION-Abfragen generieren.

Enthält Type den Begriff „Überschrift“, setzen wir dessen Schriftfarbe auf Rot und zeichnen den Text halbfett aus. Der BeforePrinting-EventHandler von NameField:

Sub BeforePrinting() Handles BeforePrinting
 FormatÜberschrift(Me)
End Sub

Und die dazu passende Methode:

Private Sub FormatÜberschrift(field As ReportField)
 Dim isÜberschrift As Boolean = InStr(Self.DataSource.Field("type").StringValue, "Überschrift") <> 0
 field.Bold = isÜberschrift
 field.TextColor = If(isÜberschrift, &cFB010600, &c00000000)
End Sub

Damit hätten wir im Report ein rotes Feld mit dem Inhalt „Gerichte“, aber noch keinen Inhalt. Deshalb wird der Select nun mit einem Union erweitert:

SELECT '1Überschrift' AS 'Type', 'Gerichte' AS 'Name', '' AS 'Col2'
UNION 
SELECT '2Gerichte' AS 'Type', 'Name' AS 'Name', 'Kochdauer' AS 'Col2' FROM 'Gerichte'

womit nach der Überschrift alle Datensätze der Tabelle Gerichte folgen, mit dem Type „2Gerichte“, dem Namen aus dem Feld Name und Col2 mit dem Inhalt von Kochdauer.

Um die Tabelle Zutaten erweitert, schaut unsere Abfrage, die nun je nach Tabelle im Col2Field die gewünschte Kochdauer oder Zutat ausgibt, dann so aus:

SELECT '1Überschrift' AS 'Type', 'Gerichte' AS 'Name', '' AS 'Col2' 
UNION 
SELECT '2Gerichte' AS 'Type', 'Name' AS 'Name', 'Kochdauer' AS 'Col2' FROM 'Gerichte'  
UNION 
SELECT '3Überschrift' AS 'Type', 'Zutaten' AS 'Name', '' AS 'Col2' 
UNION 
SELECT '4Zutaten' AS 'Type', 'Name' AS 'Name', 'Herkunftsland' AS 'Col2' FROM `Zutaten` ORDER BY Type, Name;

Wenn Sie nun den Report ausführen, erhalten Sie einen tollen Bericht, der sortiert die Daten aus verschiedenen Tabellen ausgibt. Und alles nur mit dem Body-Bereich:

Union Result.png

Das Beispielprojekt nebst Datenbank finden Sie hier. Legen Sie die Datenbank auf den Schreibtisch, damit das Programm sie findet.

Update

Eine wunderbare kurze deutschsprachige Einführung in die Nutzung des UNION-Befehl gibt es in diesem YouTube-Video.

Ein Gedanke zu “Gastbeitrag: Noch flexiblere Reports

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.