Oracle BI Publisher Tipp Dynamische Excel-Sheets mit RTF-Templates erzeugen

Autor Rainer Willems Version 10.1.3.4 Datum 15.01.2009 English  Click here

Ausgangslage

In diesem Tipp soll folgender Bericht erstellt werden:

Wir wollen also eine Liste der Mitarbeiter einer Abteilung mit Gehalt und Provision sowie deren Addition und den jeweiligen Summen der Spalten. Basierend auf der  Query 

 select ename, job, sal, comm, deptno 
 from emp
 where deptno = :p_deptno

läßt sich diese Anforderung recht einfach umsetzen (Download des Templates hier):

Für die Addition von Gehalt und Provision wurde <?xdofx:decode(COMM,'',SAL+COMM)?> gewählt, da COMM auch Null sein kann.  Alternativ hätte man in die Addition auch in der Abfrage durchführen k&uoml;nnen.

Soweit so gut, mit diesem Template lassen sich nun die gängigen Zielformate erzeugen.  Verwendet man aber nun die Excel-Ausgabe und verändert darin Werte, werden sich weder die Addition, noch die Summen anpassen:

Will man also ein Excel-Sheet bekommen, mit dem der Anwender "arbeiten" können soll, ist dieses Template nicht geeignet , da alle Zellenwerte statisch im Sheet eingetragen sind.

Erstellen eines Templates für dynamische Sheets

Die grün markierten Felder im Template müssen derart verändert werden, damit diese im resultierenden Excel-Sheet dynamisch werden.:

Es ist möglich, Excel-Formeln direkt im RTF-Template zu verwenden.
Für die Addition brauchen wir die Summierung der Spalten C & D  (Gehalt & Provision) der gleichen Zeile. Hierzu muss initial geschaut werden, in welcher Excel-Zeile die Ausgabe beginnt. In diesem Beispiel ist dies die Zeile 4, d.h. wir müssen zum aktuellen Datensatz 3 hinzuaddieren und schreiben die Formel direkt in das RTF-Template

 =CZEILE+DZEILE

wobei Zeile ein Form-Feld mit dem Inhalt

 <?position()+3?>

ist. Für die Summierung der Gehälter schreiben wir

 =SUM(C4:CMaxZ)

wobei MaxZ ein Form-Feld mit dem Inhalt

 <?xdofx:count(ENAME)+3?>

ist. Wie bei der Spalten Addition taucht hier die 3 auf, da die Ausgabe in der vierten Zeile beginnt. Daher auch das C4 in der Summe. Beides könnte man über eine Variable ebenfalls dynamisch verpacken, um bei späteren änderungen (z.B. dem Einfügen einer Textzeile zu Beginn) nicht in allen Feldern änderungen vornehmen zu müssen.
Das gleiche machen wir für die Spalte D (Provision) sowie die Spalte E (Einkommen).

Mit dem folgenden Template (Download des Templates hier)

erhalten wir also ein Excel Sheet, in welchem nach änderungen der Ausgangswerte wie Gehalt oder Provision auch die Summen (oder eben auch weitere Formeln und Regeln wie =IF...) im Excel Sheet selbst aktualisiert werden. Allerdings kann dieses Template so nicht mehr für andere Ausgabeformate wie PDF verwendet werden, da hier die Formeln nicht interpretiert werden können:

Mit dieser Methode können sehr mächtige Excel Sheets erzeugt werden. Der Oracle Partner virtual7 GmbH hat die vorhandenen Möglichkeiten des Produkts erweitert und zusätzliche XSL-Attribute implementiert und einen Weg entwickelt, Datenänderungen aus den Excel Sheets wieder in die Datenbank zurückfließen zu lassen.

Einschränkungen

Mit RTF-Templates können nicht alle Excel-Funktionalitäten ausgenutzt werden. So können keine Excel-Files mit mehreren Worksheets erzeugt werden. Auch werden Funktionen wie Kopfzeilen oder Kommentare in den Excel Sheets nicht unterstützt. Werden die Zellen nicht dynamisch generiert, könnte auch die maximal mögliche Anzahl von Spalten in einer Tabelle in Winword (63) ein limitierender Faktor sein. 

Alternativ könnte man den Excel Analyser verwenden, mit welchem Excel-Templates erzeugt werden können. Dieser unterstützt die nativen Excel-Möglichkeiten (inkl. Macros), kann aber in der aktuellen Version nur flache Strukturen verarbeiten.