Excel 2013 "Dashboard" und Reporting für e-commerce

Kunde und Projektumfang

Der Kunde

Der Kunde ist die Holding eines international aufgestellten Versandunternehmens mit einem boomenden Online-Geschäft. Die verschiedenen Sparten und Töchter des Unternehmens konzentrieren sich auf unterschiedliche Märkte und Zielgruppen (z.B. Mode, Lifestyle). Jede Sparte führt einen oder mehrere Webshops für die unterschiedlichen Marken. Der Traffic wird mit Hilfe des Produkts Webtrends analysiert und in einer MySQL-Datenbank konsolidiert.

Projektumfang und Anforderungen

Die Stabsabteilung e-commerce hat die Aufgabe, dem Management die Key Performance Indicators (KPIs) des Online-Geschäfts in Übersichten ("Dashboards") zusammen mit Detailangaben ("Deep Dives") für die einzelnen Branchen und Marken auf monatlicher Basis zu liefern. Als Ausgabeformat wurde PDF gewünscht.

Wichtige Anforderungen: a) Die Möglichkeit, Änderungen und Anpassungen unabhängig von IT und/oder Entwicklern vornehmen zu können, b) Die Lösung soll Tools verwenden, für die Power-User in der Abteilung ausreichend vorhanden sind (Excel, PowerPoint). Ich wurde gebeten, eine entsprechende Lösung zu entwickeln.

Systemkontext

Mein Teil im Gesamtprojekt (siehe Zeichnung) war begrenzt auf die Entwicklung einer Excel-basierten Lösung, um die bereits konsolidierten Daten aus MySQL abzurufen und im gewünschten Format auszugeben. Die anderen Komponenten des Systemkontextes unterlagen nicht meiner Verantwortung.

Die Lösung

Excel-Vorlagen / Trennung von Daten und Präsentation

Für jede Übersicht und jeden Report gibt es eine Excel-Datei, die als Vorlage dient. In der Vorlage befinden sich ein oder mehrere Blätter mit Datenbereichen, Formeln, Parametern und SQL-Anweisungen. Hier werden die Daten unformatiert vom Datenabruf eingetragen.

Ein Blatt fasst alle Daten zusammen ("Präsentation"). Das Layout dieses Blatts kann frei gestaltet werden (Zeilenhöhe, Spaltenbreite, Anordnung). Formeln übernehmen die Daten aus den Datenblättern:

Ein Dashboard:

Die SQL-Anweisungen sind mit Platzhaltern versehen. Zur Laufzeit des Programms werden die Platzhalter durch die benötigten Parameter ersetzt (Datumswerte, Selektion), so dass aus der Datenbank die gewünschten Werte abgerufen werden können.

Das Konzept erlaubt die Nutzung der hervorragenden Analysemöglichkeiten von Excel 2013: klassische Diagramme, Datenlinien, und Sparklines. Erweiterungen sind unproblematisch möglich mit ad-hoc-charts oder Grafiken, die über die "Kamera-Funktion" zu einem Bild zusammengefasst werden. Zwar müssen bei Editieren der Vorlagen Regeln eingehalten werden, aber sie können ohne Probleme von einem Excel-Power-User der Abteilung (nach Einweisung) geändert oder erweitert werden.

Das Reporting Tool (VBA-Teil)

Der 2. Teil der Lösung ist das Reporting Tool, eine Excel-Datei mit VBA-Programmcode (XLSM-Format). Diese Datei enthält

• einen Dialog, in dem der Benutzer wichtige Parameter für das Reporting festlegen kann (Auswerungsmonat, Branchen, Fokus)
• die gesamte Programmlogik
  
  • für die Dateierstellung aus den Vorlagen, und
  • für den Datenabruf (Parsen und Aktualisierung der SQL-Anweisungen, Übertragung in die Zielbereiche)
• die üblichen Hilfsfunktionen und -prozeduren für Log, Datenbankverbindung, etc.

Word: Versicherungsvertrag aus HOST-Daten

Anforderung: Für einen Übergangszeitraum bis zur Einführung eines neuen Systems sollen bestimmte, kleinvolumige Versicherungsverträge über ein Word-Frontend erstellt werden. Die Daten werden aus nächtlich aus der HOST-Vertragsverwaltung durch Filetransfer in das PC-System in Form von stellenorientierten komprimierten Textdateien übertragen und sind von dort zu übernehmen.

Lösung: Ein Visual-Basic-Programm überwacht das Transferverzeichnis, archiviert die Dateien und entpackt sie in Zielverzeichnisse. Ein Word Add-in produziert aus den Daten Versicherungsverträge. Dabei werden je nach Vertragstyp andere Layouts verwendet oder andere Zusatzdokumente (Versicherungsbedingungen) eingefügt. Die Zuordnungsanweisungen (Variablen => Bausteine) sind in einer Excel-Tabelle abgelegt und können unabhängig vom Programmcode erweitert werden.

Kommentar: Hier handelt es sich um ein typisches Beispiel, wie ein Office-Add-in kostengünstig eine temporäre Lücke schließt. Die Lösung ist nicht für den Dauerbetrieb geeignet, kann aber für zwei bis drei Jahre zufriedenstellend laufen und bewahrt das Unternehmen vor teuren Anpassungen auf dem Grossrechner-System.

Eine kurze Präsentation finden Sie hier.

<< Zurück

Excel: Generierung von versandfähigen Excel-Dateien aus Großrechner-Quelldaten

Aufgabe: Die Abrechnungsdaten der Depotverwaltung für Großkunden werden monatlich in Form einer Textdatei der Fachabteilung angeliefert. Jeder Kunde soll seine persönlichen Abrechnungsdaten in Form von Excel-Dateien erhalten.

Lösung: Die Fachabteilung erhält ein bei Bedarf zu ladendes Add-in für Excel. Nach Auswahl der Quelldatei bildet das VBA-Programm über SQL-Abfragen zunächst eine Kundenliste und generiert danach automatisch im Zielverzeichnis für alle Kunden die versandfertigen Excel-Dateien. Die Detaildaten in den Excel-Arbeitsmappen werden korrekt formatiert (Layout, Zahlenformate), sortiert und mit AutoFiltern versehen.

<< Zurück

Word: Rechnungsdokument aus Daten eines Abrechnungssystems

Anforderung: Nach einer Unternehmensfusion muss das deutsche Abrechnungssystem durch ein US-System ersetzt werden. Für die Fakturierung in Deutschland werden Word-Dokumente im Firmenlayout und mit standortbezogenen Absenderinformationen benötigt, die das US-System nicht liefern kann. Je nach Kunden soll die Auswahl unter mehreren Faktura-Layouts möglich sein (ausführlich / knapp / mit Anlage etc.)

Schwierigkeiten: Als einzige Schnittstelle stehen Textdateien mit den Leistungsdetails und Abrechnungspositionen zur Verfügung. Die Struktur der Textdateien ist layoutorientiert, nicht inhaltsorientiert (kein XML). Die Textdatei enthält nicht alle benötigten Daten, so dass bestimmte Informationen zusätzlich aus einer SQL-Server-Datenbank abgerufen werden müssen.

Lösung: Die Programmlogik ist aus Performancegründen zwischen einer DLL (Visual Basic) und einer VBA-basierten Word-Vorlagendatei aufgeteilt.

• Daten einlesen und homogenisieren. Die ausgewählte Textdatei wird analysiert und in den Speicher gelesen. Nach dem Analyseprozeß können alle Rechnungsdaten und Leistungsdetails aus der internen Objekthierarchie abgerufen werden. Fehlende Informationen wurden während der Analyse aus der SQL-Server-Datenbank geholt.

• Interaktion des Benutzers. Der Benutzer kann anschließend über ein Dialogfeld das benötigte Layout und die Zielsprache auswählen. Bei der Dokumenterstellung werden die standortabhängigen Kontoverbindungen sowie die Absenderinformationen (Infoblock) berücksichtigt.

• Ablage im DMS. Das Ausgabedokument wird abschließend unter der korrekten Kunden- und Aktennummer im Dokumentenmanagementsystem gespeichert. Die weitere Bearbeitung ist nur unter Dokumentschutz in festgelegten Bereichen zulässig.

<< Zurück