Oracle BI Publisher Tipp

Excel Templates

Autor


Rainer Willems

Version

10.1.3.4.1 + Rollup Patch 9546699

Datum

03.03.2011


In diesem Tipp geht es um die (relativ) neue Möglichkeit mit Excel Templates zu erstellen. Mit Hilfe des Excel-Analyzers konnte man zwar auch bisher schon mit Excel ein Template bauen, dieses hatte konnte aber nur mit flachen Strukturen umgehen oder man musste mit Macros die Daten bearbeiten. Allerdings ist (noch) kein Template Builder für Excel vorhanden. Das bedeutet, dass man sein Template ohne Toolunterstützung bauen muss, aber auch, dass man das Ergebnis eines Templates nur über das Laufen auf dem Server erzeugen kann. Ohne Tool gibt es eben auch kein Preview, wie man es aus Word gewohnt ist.  Allerdings kann mit dem Patch 9821068 auch der Template Viewer zum Preview verwendet werden. Wer die Templates übrigens mit 11g verwenden möchte, muss sich auf das Release 11.1.1.4 gedulden. Dann wird es auch einen Excel Template Builder geben.  Benötigt wird übrigens mindestes Microsoft Excel 2003 und die Templates werden als xls und nicht im neuen xlsx -Format verwendet.

Wie beim Excel Analyzer benötigen wir ein Worksheet names XDO_METADATA , welches wir später verstecken werden. Dieses Sheet besteht aus einer Header- und einer Data Constraints Sektion. 

In ersterer gibt es nur eine Variable, die gefüllt sein muss, das ist der Template Type . Neben TYPE_EXCEL_TEMPLATE, welches wir hier verwenden, könnten hier noch TYPE_EXCEL_ANALYZER_TEMPLATE oder TYPE_EXCEL_TEMPLATE_FOR_ANALYZER verwendet werden.  Die Informationen in den Zeilen 1,2,3,7 und 8 dienen rein der Dokumentation. Unter Preprocess XSL Template könnte ein XSLT-File zum preprocessing der verwendeten Daten angegeben werden.
Der Zelle Data Constraints: folgen optionale Einstallungsmöglichkeiten, gleichwohl muss diese Zelle vorhanden sein, auch wenn keinerlei Data Constraints verwendet werden. Später hierzu mehr .

Wir bauen uns ein zweites Sheet mit dem gewünschten Layout und füllen es mit Dummy-Daten



Als Datengrundlage gehen wir von einer simplen Master Detail beziehung zwische Abteilungen und Mitarbeitern aus ( mydata.xml)


Unter dem Tab Formulas können wir logische Namen mit Define Name erstellen. Als Name wählen wir XDO_GROUP_?G_DEPT? , in Fragezeichen eingehüllt steht also die "Wiederholgruppe" unserer XML-Struktur. Unter Refers to wird der Bereich markiert, auf den diese Gruppe abgebildet werden soll, in unserem Beispiel also der komplette Bereich. Markieren wir den ausgewählten Bereich, erscheint in der oberen Leiste links nicht mehr die linke, obeere Zelle des Bereiches (hier A1), sondern unser definierter Name.



In dieses Feld können wir unser neues Objekt auch direkt einfügen. Wir markieren also die Zellen A4 bis F4 und tippen dort XDO_GROUP_?G_EMP? für die Mitarbeitergruppe ein, definieren also so den Wiederholbereich für diese XML-Struktur. Die einzelnen Inhalte des Daten XMLs werden in den Zellen auf die gleiche Weise (also über Define Names oder direktes Eintragen) definiert. Der Syntax lautet hier XDO_? feldname


Testen können wir dies nun mit der passenden Version des Template Viewers, welcher nun auch die Auswahl Excel Templates enthält. Zu beachten ist, dass mit einem Excel Template auch nur Excel als Ausgabe gewählt werden kann (genauer gesagt kann man hier zwar auch die anderen Ausgabeformate wählen, es wird aber nicht funktionieren).



Und schon  sehen wir das Ergebnis unseres Templates.



Jetzt wollen wir pro Abteilung ein eigenes Sheet bekommen und diese auch noch mit dem Abteilungsnamen benennen. Hierzu können wir in  der Data Constraints Sektion des XDO_METADATA Tabs die Paramter  XDO_SHEET_? und XDO_SHEET_NAME_? verwenden. Ersterer definiert die Bedingung des Sheetwechsels ("Gruppenwechsel"), in unserem Beispiel die Abteilung, also die passende referenz auf unser Daten-XML: <?.//G_DEPT?> . Der zweite Parameter definiert den Tab-Namen, in welchem wir eben den Abteilungsnamen selektieren ( <?.//DNAME?> ).



Es könnten hier auch neue Gruppen und Elemente erstellt werden, Parameter definiert werden, Formatierungseigenschaften gesetzt werden oder Subtemplates angezogen werden ...  für eine vollständigere Aufzählung warten wir lieber die Dokumentation ab.
Damit dieser Ansatz funktioniert, müssen wir das Objekt XDO_GROUP_?G_DEPT? aus der Seite wieder entfernen, da wir diese Wiederholgruppe ja nun auf einer anderen Ebene verwenden. Dies kann einfach über den Name Manager gemacht werden.
Das Ergebnis erfreut den BI Publisher Entwickler, da ein solcher Output bisher eher schwer zu erreichen war.



Jetzt verfeinern wir die Ausgabe noch durch Berechnungen und Summen. Fangen wir mit der Summierung der Gehälter einer Abteilung an. Wir fügen eine Zeile hinzu und definieren mittels Excel-Funktionalität die Summe, hier mittels =SUM($D$4:D4) . Je nach Anzahl der Datensätze wird nun die passende Summe berechnet, da $D$4 quasi auf der Zelle D4 "stehen bleibt", wärend der Eintrag D4 mit der Anzahl der Datensätze mitläuft. 



Und das Ergebnis sieht wie folgt aus, wobei in zelle D7 nun =SUM($D$4:D6) steht.



Nun noch die Addition von Gehalt und Provision zum Einkommen an. Wir erweitern einfach das Excel-Sheet und addieren die vorhergehenden Felder mittels Excel-Funktionalität. Dies könnte man zwar auch im SQL direkt machen, dann wäre das resultierende Excel-Sheet aber nicht dynamisch.



Zu beachten ist hier, dass wir die Definition von XDO_GROUP_?G_EMP? um die neue Spalte erweitern müssen, damit diese Summe auch für jede Zeile berechnet wird. Dies kann wieder über den Name Manager geschehen.



Und schon haben wir ein Excel Sheet mit mehreren Tabs und nativen Excel-Funktionlitäten ohne Makro-Programmierung, wie dies mit dem Excel Analyzer notwendig wäre.




Zum Nachvollziehen des Beispieles hier die notwendigen Files: Template , Daten , Ergebnis