Im Artikel „JTL-Wawi – Direktvertrieb mit Vertriebsmitarbeitern lösen“ habe ich die Anforderungen für die Umsatzauswertung für Vertriebsmitarbeiter dargelegt und schon mal einen Lösungsansatz skizziert. Anschließend habe ich im Artikel „LibreOffice Base + JTL-Wawi – Anbindung der JTL-Wawi Datenbank“ gezeigt, wie mit der ODBC-Schnittstelle eine Verbindung zur Datenbank hergestellt wird und im Artikel „JTL-Wawi – Sicherer Zugriff auf die SQL-Datenbank“ wird gezeigt wie die Verbindung gesichert wird, so dass für die Auswertung nur Leserechte zur Verfügung stehen. Jetzt mache ich mich an die Auswertung mit LibreOffice Calc. Doch bevor ich die Daten in Calc einlese und mit einer Pivot-Tabelle auswerte, muss ein Zwischenschritt eingebaut werden.

JTL-Wawi verwendet den Microsoft SQL-Server. Dabei handelt es sich um ein relationales Datenbanksystem. Für mich bedeutet dies, dass die Informationen über mehrere Tabellen verteilt in der Datenbank liegen und über sogenannte Relationen mit einander verbunden sind. Diese Relationen werden nicht unbedingt von LibreOffice Base übernommen. Ich muss also eine Abfrage über die entsprechenden Datenbank-Tabellen erstellen, damit ich die Daten aus der Abfrage dann in LibreOffice Calc auswerten kann. Dazu öffne ich wieder meine eazyBusiness.odb, klicke links unter Datenbank auf Abfragen und anschließend auf „Abfrage in der Entwurfsansicht erstellen…“

Es öffnet sich ein leeres Abfrage-Entwurfsfenster und das Fenster „Tabelle oder Abfrage hinzufügen“. Aus dem Fenster „Tabelle oder Abfrage hinzufügen“ wähle ich jetzt die Tabellen aus, die ich für meine Auswertung benötige und mit einem Klick auf hinzufügen werden die Tabellen dem Abfrageentwurf zugefügt. Für meine Auswertung habe ich folgende Tabellen ausgewählt:

• tBestellungEckDaten
• tbestellung
• trechnung
• tkunde

In der oberen Fensterhälfte des Abfrageentwurfs habe ich jetzt alle Tabellen die ich für meine Abfrage benötige. Ich habe hier die Tabelle im Fenster so angeordnet, dass ich im nächsten Schritt mit der Maus die Relationen zwischen den entsprechenden Tabellenfeldern setzen kann.

Eine Relation ist die Beziehung zwischen zwei Feldern. Schauen wir uns die Tabelle tBestellungEckDaten und tbestellung an, so sehen wir, dass das Feld kBestellung in beiden Tabellen vorhanden ist. In den Tabellen sind diese Felder leicht zu erkennen, da sie meistens ein Schlüsselsymbol vorne dran haben, dass für Primärschlüssel steht (ohne dass ich jetzt näher darauf eingehe). Mit dem Feld kBestellung in beiden Tabellen werden die Datensätze verbunden, damit sie später in den Formularen und Berichten angezeigt werden können. Um die Relation in LibreOffice Base anzulegen, klicke ich mit der linken Maustaste das Feld kBestellung in der Tabelle tBestellungEckDaten an, halte die Maustaste gedrückt und ziehe dann die Maus auf das Feld kBestellung in der Tabelle tbestellung ziehe und dann die linke Maustaste wieder loslasse. Den Vorgang wieder hole ich für die Felder:

• Tabelle tkunde: kKunde – tKunde_kKunde
• Tabelle trechnung: tBestellung_kBestellung – kBestellung

Anschließend sieht mein Layout-Fenster wie im Screenshot unten aus.

Okay jetzt kann ich in der unteren Fensterhälfte des Abfrage-Designers meine Abfrage erstellen. In Spalte 1 der Abfrage soll das Jahr der Rechnung stehen. Das Erstelldatum der Rechnung finde ich in der Tabelle trechnung. Leider aber nur als Datum in der Form Tag Monat Jahr. Darum muss ich aus dem Datum das Jahr extrahieren. Das mache ich mit der SQL-Funktion YEAR(Datum). Also trage ich in der Zeile Feld die Funktion

YEAR(trechnung.dErstellt)

und in der Zeile Alias gebe ich den Wert Jahr ein. Damit erhalte ich das Jahr. Das gleiche mache ich in der zweiten Spalte, nur dass ich die hier die Funktion MONTH(Datum) verwende und als alias Monat hinterlege.

Die erste Hürde habe ich damit genommen. Jetzt kann ich später in der Pivot-Tabelle die Daten nach Jahr und Monat bequem filtern. Anschließend ziehe ich nur noch mit der Maus das Feld cRechnungsNr aus der Tabelle trechnung und die Felder cHerkunft und cFirma aus der Tabelle tKunde in meinen Abfrageentwurf. Die Felder cVorname und cName aus der Tabelle tKunde verbinde ich, in dem ich in der nächsten Spalte einfach

tKunde.cVorname + „ „ + tKunde.cName

eintrage und als Alias den Wert Name eingebe. Jetzt fehlen für meine Auswertung noch zwei Spalten. Einmal der Wert der Bestellung abzüglich von Gutschriften und Gutscheinen und die Provision, die sich daraus errechnet. Also trage ich in die nächsten Spalte die folgende Formel in die Zeile Feld ein:

tBestellungEckDaten.fWert – tBestellungEckDaten.fGutschrift – tBestellungEckdaten.fGutschein

in die Zeile Alias trage ich Wert ein. Auf die gleiche Weise errechne ich die Provision für den Vertriebsmitarbeiter. Hier muss ich nur die Formel von der Spalte Wert nehmen und in Klammern setzen und mit 0.03 multiplizieren um eine 3 Prozentige Vertriebsprovision zu bekommen. Die nächste Spalte enthält somit folgende Formel in der Zeile Feld:

(tBestellungEckDaten.fWert – tBestellungEckDaten.fGutschrift – tBestellungEckdaten.fGutschein) * 0.03

Bitte beachten, dass anstatt des Kommas ein Punkt in der Formel geschrieben wird! In die Zeile Alias gebe ich den Wert Provision ein.

Das Ganze sieht dann so aus:

Wer es sich ganz einfach machen will der schaltet unter Ansicht – Design-Ansicht an-, ausschalten um auf die SQL-Ansicht und kopiert folgenden Code in das Textfenster:

SELECT YEAR( trechnung . dErstellt ) AS Jahr , MONTH( trechnung . dErstellt ) AS Monat , trechnung . cRechnungsNr AS ReNr , tkunde . cHerkunft AS Herkunft , tkunde . cFirma AS Firma , cVorname + + cName AS Name , tBestellungEckDaten . fwert - tBestellungEckdaten . fGutschrift - tBestellungEckdaten . fGutschein AS Wert , ( tBestellungEckDaten . fwert - tBestellungEckdaten . fGutschrift - tBestellungEckdaten . fGutschein ) * 0.003 AS Provision FROM eazybusiness . dbo . tbestellung AS tbestellung , eazybusiness . dbo . trechnung AS trechnung , eazybusiness . dbo . tkunde AS tkunde , eazybusiness . dbo . tBestellungEckDaten AS tBestellungEckDaten WHERE tbestellung . kBestellung = trechnung . tBestellung_kBestellung AND tbestellung . tKunde_kKunde = tkunde . kKunde AND tbestellung . kBestellung = tBestellungEckDaten . kBestellung

Anschießend die Designansicht wieder anschalten und die Abfrage als qryUmsaetze abspeichern. Jetzt kann die Datenbank wieder geschlossen werden.

Der letzte Schritt besteht jetzt daraus, in einer leeren LibreOffice Calc Arbeitsmappe eine Pivot-Tabelle für die Auswertung zu erstellen. Ich öffne Calc und wähle im Menü Daten → Pivot-Tabelle → Erstellen… aus.

Im Fenster Quelle auswählen markiere ich „In LibreOffice angemeldete Datenquelle” und klicke auf OK.
Als Datenbank wähle ich eazyBusiness aus, anschließend schränke ich den Typ auf Abfrage ein und wähle meine Datenquelle qryUmsaetze aus.
Jetzt kann ich die Felder aus Verfügbare Felder mit der Maus in die richtigen Bereiche meiner Pivot-Tabelle ziehen. Jahr ziehe ich nach Seitenfelder, Herkunft nach Spaltenfelder. Monat, Firma und Name ziehe ich nach Zeilenfelder. Wert und Provision kommen in die Datenfelder und die Einstellung Summe davor belasse ich. Daten entferne ich mit einem Klick auf den Eintrag und anschließend auf die Entf-Taste der Tastatur. Jetzt kann ich schon auf OK klicken um meine Pivot-Tabelle zu erstellen.

Das war es schon. Diese Tabelle kann ich jetzt abspeichern. Ich muss mich nicht mehr um die Daten kümmern. Eine Einfache Aktualisierung unter Daten → Pivot-Tabelle → Aktualisieren reicht aus um die Daten der Tabelle auf den neuesten Stand zu bringen. Dafür ist dann noch nicht mal die Eingabe des Passwortes notwendig.