Formatierungen von Pivot-Abfragen für den Einsatz von

Formatierungen von Pivot-Abfragen für den Einsatz
von APEX bei Termin- und Einsatzplanungen
Issam Lamani und Rüdiger Steffan
Fakultät für Wirtschaftswissenschaften
Hochschule Wismar
Schlüsselworte:
Terminplan, Einsatzplan, Pivot, APEX, Tooltip, Rollbalken, Scrollen, JavaScript
Kurzfassung:
Dieser Beitrag ist das Ergebnis einer umfangreichen Recherche und es wird gezeigt, wie die speziellen
Formatierungen in APEX eingerichtet werden können. Ziel sind Schritt-für-Schritt-Anleitungen zur
Implementierung derartiger Berichte in eigenen Anwendungen. Dazu wird auch das Zusammenspiel von
HTML/CSS, JavaScript, Prozessen und Templates in APEX genauer erläutert. Als Beispiel wird eine Applikation
zur Prüfungsorganisation vorgestellt.
Einleitung
An der Hochschule Wismar werden in der Fakultät für Wirtschaftswissenschaften Oracle XE
und Oracle Application Express zur Prüfungsorganisation eingesetzt. Ähnlich wie in vielen
Unternehmen wurden die abteilungsinternen Prozesse zunächst mit Hilfe von Excel-Tabellen
und entsprechenden VBA-Makros unterstützt, was jedoch zu Konsistenzproblemen führte und
schwierig erweiterbar war. Das entwickelte Gesamtsystem ist in Abb. 1a dargestellt [1]. Für
die Verwaltung von Stammdaten, Raumreservierungen etc. steht das zentrale HochschulInformations-System LSF (Lehre, Studium, Forschung) zur Verfügung. Jedoch werden
individuelle Planungsprozesse und abteilungsspezifische Verwaltungsdaten damit nicht
ausreichend unterstützt. Planungsergebnisse (z.B. Prüfungstermine) können für weitere
Planungsschritte (z.B. Raumreservierungen) wieder in das zentrale Informationssystem
geschrieben werden. Für personenbezogene Stammdaten oder für die Möglichkeit zur
Benutzerauthentifizierung kann auf zentrale Verzeichnisdienste zugegriffen werden.
Ein Auszug aus dem Datenbankdesign ist in Abb. 1b zu sehen. Die daraus resultierenden
Tabellen werden im Wesentlichen für die nachfolgend beschriebenen Berichte verwendet.
Ziel sind Page-Detail-Kreuztabellen, die in Abb. 1c vereinfacht dargestellt sind. Es handelt
sich dabei zum einen um Terminpläne für drei Prüfungswochen, die einen schnellen Überblick darüber geben, ob in einem Studiengang in einem Tagesabschnitt mehrere Prüfungen
parallel stattfinden, was vermieden werden soll. In den Feldern sind Kurzinformationen über
den Termin dargestellt. Beim Bewegen der Maus über das Feld sollen automatisch Detailinformationen und auch Querverweise angezeigt werden (sog. Tooltip). Bei der Einsatzplanung von Aufsichtskräften für Prüfungen sind Pivot-Berichte vorteilhaft, bei denen
Personen und Prüfungen gegenübergestellt sind. Bei der Zuteilung von Personen wird die
gesamte Auslastung einer Person dynamisch angezeigt. Aufgrund der Vielzahl von Personen
und Prüfungsfächern sind Tabellen mit Rollbalken erforderlich, bei denen jedoch die erste
Zeile und die erste Spalte zur besseren Orientierung fixiert sein müssen.
Abb. 1: Einsatz von Oracle APEX zur Prüfungsorganisation an der Fakultät für Wirtschaftswissenschaften
(FWW) der Hochschule Wismar. a) Zugriff auf LDAP zur Benutzerauthentifizierung und Datenaustausch mit
dem Hochschul-Informations-System LSF (Lehre, Studium, Forschung). b) Auszug aus dem ER-Diagramm als
Basis der Pivot-Abfragen für Prüfungsterminpläne und Einsatzplanung von Aufsichtskräften. c) Ziel sind
Darstellungen der Pivot-Abfragen als Page-Detail-Kreuztabellen mit Rollbalken und fixierten Rändern.
Derartige Berichte sind in Excel relativ einfach umsetzbar; sie stehen in APEX jedoch nicht
unmittelbar zur Verfügung. In diesem Beitrag wird gezeigt, wie die speziellen
Formatierungen in APEX eingerichtet werden können. Ziel sind Schritt-für-SchrittAnleitungen zur Implementierung derartiger Berichte in eigenen Anwendungen mit
Verweisen auf die rechergierten Quellen. Dazu wird auch das Zusammenspiel von
HTML/CSS, JavaScript, Prozessen und Templates in APEX genauer erläutert. Schließlich
sollen die Probleme und Erfahrungen bei der Recherche für die Lösungsansätze
zusammengefasst und weitere Lösungsansätze aufgezeigt werden.
Dynamische Pivot-Abfragen in APEX
Für Pivot-Abfragen wird ab Oracle Version 11g in SQL die Pivot-Klausel (bzw. PivotOperator als Teil der FROM-Klausel) unterstützt, mit deren Hilfe Zeilen in Spalten
umgewandelt werden [2]. Oracle XE basiert auf Version 10g und unterstützt diese Klausel
daher noch nicht. Für die Berichte in Abb. 1 wird eine Unterabfrage in der FROM-Klausel
dynamisch generiert. Es entsteht eine variable Anzahl von Spalten, je nach Anzahl der
Semester des Studiengangs. Als Beispiel ist nachfolgend das Abfrageprinzip für die Darstellung der Prüfungsnummern und Fachkürzel in den Feldern der Pivot-Tabelle für die erste
Prüfungswoche im Studiengang Wirtschaftsinformatik Bachelor (6 Semester) dargestellt:
SELECT tag, datum,
MAX(DECODE(semester,1,pfnr||','||fachkuerzel,null))
MAX(DECODE(semester,2,pfnr||','||fachkuerzel,null))
MAX(DECODE(semester,3,pfnr||','||fachkuerzel,null))
MAX(DECODE(semester,4,pfnr||','||fachkuerzel,null))
MAX(DECODE(semester,5,pfnr||','||fachkuerzel,null))
MAX(DECODE(semester,6,pfnr||','||fachkuerzel,null))
FROM pruefungsplan
as
as
as
as
as
as
-- Zeilen
s1,
s2,
s3,
s4,
s5,
s6 -- Spalten
WHERE woche=1 AND sfkuerzel||stabkuerzel = 'WIBa'
GROUP BY tag, datum
ORDER BY datum ;
Die resultierende Pivot-Tabelle ist in Abb. 1c (links) vereinfacht dargestellt, wobei
pruefungsplan eine View basierend auf den Tabellen in Abb. 1b ist. Der PL/SQL-Block zur
Generierung dieser Abfrage kann direkt in APEX im Abschnitt Source unter
"Application>Page>Edit Region" implementiert werden. Zur besseren Wiederverwendbarkeit
in anderen APEX-Applikationen wird die Funktion jedoch in der Datenbank gespeichert. Der
Berichtstyp ist "SQL Query (PL/SQL function body returning SQL query)" und der Aufruf im
Abschnitt Source unter Verwendung eines Application Items für den Studiengang sowie mit
einer expliziten Angabe der ersten Prüfungswoche lautet:
return pruefungsplan_abfrage(:P301_SGKUERZEL, 1) ;
Die Generierung von Abfragen für Pivot-Tabellen zur Einsatzplanung erfolgt nach dem
gleichen Prinzip.
Realisierung eines Tooltip-Fensters für die Terminpläne
Der Platz für Einträge in den Feldern der Pivot-Tabelle für Terminpläne ist begrenzt. Daher
sollen beim Bewegen des Mauszeigers auf ein Feld, automatisch entsprechende
Detailinformationen und vor allem auch Querverweise auf Termine anderer Studiengänge im
gleichen Zeitabschnitt gezeigt werden. Derartige Informationsfenster werden Tooltips bzw.
im Windows-Umfeld Ballon-Tips genannt [3].
Zur Realisierung in APEX kann dazu ein JavaScript verwendet werden, das unter [4] zu
finden ist. Die einzelnen Schritte zur Implementierung sowie die Abläufe beim automatischen
Aufruf eines Tooltips in APEX sind in Abb. 2 dargestellt und sollen im folgenden
zusammengefasst werden:
(0)
(1)
(2)
(3)
(4)
(5)
(6)
(7)
Für jede dynamische Spalte: Option onmouseover bei Spaltenformatierung (Column Attributes).
JavaScript, unter Shared Components gespeichert.
Aufruf einer PL/SQL-Prozedur (für die Detailabfrage) durch einen Application Process.
Application Items, die in PL/SQL (initialisiert im JavaScript)) aufgerufen werden können (Funktion v()).
PL/SQL-Prozedur wird aufgerufen und macht SQL-Abfragen für den Tooltip-Inhalt.
PL/SQL-Prozedur gibt HTML-Code an Java-Script zurück.
Java-Script erhält Informationen als HTML-Code. Region mit modifiziertem CSS-Layer wird ausgeführt.
Tooltip-Fenster wird angezeigt (Fensterrahmen als Image-Dateien unter Shared Components).
Das JavaScript könnte auch direkt im Kontext einer Spaltenformatierung implementiert
werden. Um das Skript auch in anderen Berichten wiederverwenden zu können, empfiehlt
sich aber die Speicherung unter "Shared Components>Files" (in diesem Fall mit dem Namen
pruefungsdetail.js). Die Datei kann dabei in den Bereich Cascading Style Sheets, Images
oder Static Files hochgeladen werden. Der Zugriff erfolgt in jedem Fall über die
Austauschvariable #WORKSPACE_IMAGES#.
Auf der Seite des Berichts wird eine HTML-Region mit Display Point: After Header angelegt,
in der das Skript sowie die Ränder des Tooltip-Fensters mittels Image-Dateien definiert sind,
die ebenso unter Shared Components gespeichert sind (vgl. [4]):
Abb. 2: Ablauf zur Darstellung eines Tooltip-Fensters. Erläuterungen zu den Nummern erfolgen im Text. Im
Hintergrund ist die Darstellung eines Prüfungsterminplans zu sehen. In einem Feld wird der Termin in Kurzform
angezeigt. Beim Bewegen der Maus über das Feld erscheinen Detailinformationen sowie Querverweise.
Der Eintrag im Abschnitt Region Source der HTM-Region lautet somit:
<script src="#WORKSPACE_IMAGES#pruefungsdetail.js" type="text/javascript"></script>
<tr>
<td valign="middle" rowspan="3"><img src="#WORKSPACE_IMAGES#point.png" /></td>
<td><img src="#WORKSPACE_IMAGES#top1.png" width="10" height="11" alt="" /></td>
<td style="background-image:url(#WORKSPACE_IMAGES#top2.png)"></td>
<td><img src="#WORKSPACE_IMAGES#top3.png" width="10" height="11" alt="" /></td>
</tr>
<tr>
<td style="background-image:url(#WORKSPACE_IMAGES#middle1.png)">
<img src="#WORKSPACE_IMAGES#middle1.png" width="10" height="1" /></td>
<td style="background:#FFF;"><div id="rollover_content"></div></td>
<td style="background-image:url(#WORKSPACE_IMAGES#middle2.png)">
<img src="#WORKSPACE_IMAGES#middle2.png" width="10" height="1" /></td>
</tr>
<tr>
<td><img src="#WORKSPACE_IMAGES#bottom1.png" width="10" height="11"></td>
<td style="background-image:url(#WORKSPACE_IMAGES#bottom2.png)"></td>
<td><img src="#WORKSPACE_IMAGES#bottom3.png" width="10" height="11"></td>
</tr>
</table>
Unter Column Attributes des Berichts muss Heading Typ: Column Names gewählt werden,
da die Anzahl Spalten der Pivot-Abfrage dynamisch ist. Zur Formatierung einer Spalte wird
unter HTML Expression folgender Ausdruck eingetragen (Beispiel für die Spalte namens
COL04), wobei das JavaScript mit Übergabeparameter aufgerufen wird:
<p style="display: block; width:90px;"><font color="#0000FF"><span style="font-size:0.8em;"
onmouseover="PRUEFUNGSDETAIL(this,'#COL04#',P301_SGKUERZEL.value,'1')">#COL04#</span></font>
</p>
Für den Inhalt des Tooltip-Fensters ist in der Datenbank ein PL/SQL-Servlet namens
pruefungs_details mit entsprechenden SELECT-Anweisungen gespeichert. Um dieses
PL/SQL-Servlet im JavaScript aufrufen zu können, muss unter "Shared
Components>Logic>Application Processes" ein Prozess vom Typ Anonymer Block angelegt
werden (hier mit dem Namen process_pruefungsdetail), der das PL/SQL-Servlet aufruft
(Eintrag des Aufrufs unter Process Text). Im JavaScript kann dann das Objekt htmldb_Get
für den Zugriff auf APEX-Konstrukte (Prozesse, Items, etc.) wie folgt verwendet werden:
var get =
new htmldb_Get(null,$x('pFlowId').value,'APPLICATION_PROCESS=process_pruefungsdetail',1);
this.dGet();
function dGet(){
this.dTimeout = setTimeout("this.dCancel()",3000);
get.add('FACHKUERZEL',fachkue);
get.add('STUDIENGANG',studiengang);
get.add('SEMESTER',semester);
get.add('PRUEFUNGS_PAGE_ID',$x('pFlowStepId').value);
get.GetAsync(dShow);
}
Die JavaScript-Funktion htmldb_Get() basiert auf dem AJAX-Framework (Asynchronous
JavaScript and XML) und ist grundsätzlich in jedem APEX-Bericht verfügbar [4]. Sie erzeugt
einen HTTP-Request und fügt mit add() die Parameter hinzu, die dann im PL/SQL-Servlet
verwendet werden können.
Rollbalken für Berichte zur Einsatzplanung von Aufsichtskräften
Die Pivot-Tabelle sowie das Funktionsprinzip zur Realisierung von Rollbalken mit fixierter
linker Spalte und Kopfzeile ist in Abb. 3 zu sehen.
Die Generierung einer dynamischen Pivot-Abfrage erfolgt zunächst grundsätzlich nach dem
gleichen Prinzip wie im vorhergehenden Abschnitt beschrieben. Das Ergebnis ist eine PivotTabelle mit einer dynamischen Anzahl Spalten und Zeilen, in der nur mit den Rollbalken des
Browsers navigiert werden kann (Abb. 3: Original-Tabelle). Auf der Seite Page Rendering
wird unter HTML Header das JavaScript conford.js eingetragen, das wieder in den Bereich
"Shared Components>Static Files" hochgeladen wurde:
<style type="text/css">
.hb {background-color:#E0E0F8;}
.hw {background-color:#FFFFFF;}
.hG {background-color:#A4A4A4;}
.tableBoxOuter {width:1000px;height:300px;}
</style>
<script src="#WORKSPACE_IMAGES#cornford.js" type="text/javascript"></script>
Der Quelltext des Skripts ist u.a. auf http://railsforum.com/viewtopic.php?id=10689 oder [6]
zu finden (Copyright Richard Cornford, 2004). Alternativ könnte auch hier der gesamte
Quelltext in den Berech HTML Header kopiert werden.
Das Skript bewirkt eine Modifizierung der HTML-Tabelle auf der entsprechenden
Berichtseite. Es werden dabei drei Kopien der Tabelle erstellt, die dazu verwendet werden,
die linke Spalte, die Kopfzeile und den Tabelleninhalt unabhängig voneinander darzustellen.
Bei der so zusammengesetzten Tabelle kann dann der Tabelleninhalt unabhängig von der
linken Spalte und der Kopfzeile mit Rollbalken bewegt werden. Eine Konsequenz davon ist
jedoch, dass das Feld in der oberen linken Ecke leer bleibt. Es müsste dafür im Prinzip noch
eine zusätzliche Kopie der Tabelle durch das JavaScript erstellt werden.
Abb. 3: Funktionsprinzip des JavaScripts für Rollbalken mit fixierter Zeile und Spalte. Damit der Inhalt der
Pivot-Tabelle unabhängig von der ersten Zeile und Spalte verschoben werden kann, werden aus der
Originaltabelle drei Kopien erstellt, von denen jedoch nur ein Teil verwendet wird (linke Spalte aus Kopie Nr.1,
Kopfzeile aus Kopie Nr. 2 und Tabelleninhalt aus Kopie Nr. 3). Diese werden dann überlagert dargestellt.
Die Stylesheet CSS-Elemente hb, hw, hG und tableBoxOuter werden im JavaScript
verwendet und legen die Farben der Teilbereiche (linke Spalte, Kopfzeile und Tabelleninhalt)
sowie die Größe des Tabelleninhaltes fest.
Um die Spaltennamen zu fixieren, muss das Template des Berichts, das auf der Seite Report
Attributes ausgewählt wird, modifiziert werden. Hierzu wird unter "Page>Templates" ein
neues Template vom Typ Named Column (row template) erstellt. Im Feld "Before first and
after last row text>Before Rows" müssen die Einträge <td> durch <th> ersetzt werden, was
die absolute Positionierung der ersten Zeile bewirkt. Dies muss ebenso für die erste Spalte
gemacht werden (im Abschnitt Row Template 1). Ohne diese Einstellungen werden die
Spaltennamen bzw. die erste Spalte beim Scrollen mit bewegt. Ein Template vom Typ
Generic Columns (column template) erlaubt diese Einstellungen beispielsweise nicht. Wichtig
ist darüber hinaus auch, dass eine ausreichende Anzahl Spalten erzeugt wird, da ein Bericht
vom Typ Named Column (row template) als Standardeinstellung nur 5 Spalten enthält. Den
Rest übernimmt das JavaScript durch die Generierung von <div>-Elementen, welche CSSFormate enthalten, die dann dafür sogen, dass von jeder kopierten Tabelle nur bestimmte
Inhalte gezeigt werden.
Fazit
Die Ausgaben von APEX basieren vollständig auf HTML, so dass Lösungsansätze für
besondere Formatierungen meist in diesem Umfeld zu finden sind. Als Ausgangspunkt für
Recherchen ist daher z.B. http://de.selfhtml.org naheliegend. Sofern das richtige Stichwort
bekannt ist (z.B. Tooltip), gibt es auch in den Foren von Oracle Lösungsvorschläge für
APEX, häufig jedoch natürlich nur auf Englisch. Geeignete CSS-Definitionen oder
JavaScripte werden im Header einer Seite oder direkt bei Spaltenformatierungen integriert.
AJAX (Asynchronous JavaScript And XML) hat den Vorteil, dass Teile einer Webseite sich
ändern können, ohne die gesamte Seite nochmals neu geladen werden muss [5]. Die
Fehlersuche kann wie bei allen HTML-Seiten schwierig sein, da im Browser im Fehlerfall
meist nur "Error on page" angezeigt wird.
Bei der Recherche zu Tooltips lieferte zunächst [3] eine sehr gute allgemeine Beschreibung,
mit deren Hilfe dann in den Foren von APEX gesucht wurde. Ein vollständiges Beispiel zur
Realisierung von Tooltips in APEX ist auf der Seite [4] zu finden, erfordert jedoch zuvor die
erfolgreiche Installation der Beispielanwendung. Hilfreich war die Kenntnis, dass das
PL/SQL-Servlet für den Inhalt der Tooltip-Box auch direkt in APEX gespeichert, bearbeitet
und auf Fehler getestet werden kann ("Home>SQL Workshop>SQL Scripts>Run
Script>RUN").
Obwohl das verwendete JavaScript zur Generierung von Rollbalken mit fixierter Zeile und
Spalte keine Frames benutzt, war [7] für das Verständnis des Funktionsprinzips sehr nützlich.
Ferner war auch das Firebug Add-on für Firefox hilfreich, mit der Quelltext von Seiten
betrachtet, bzw. temporär geändert werden kann. In APEX muss die maximale Anzahl
Spalten vorbereitet werden, die bei den dynamischen Abfragen entstehen können. Um diese
zusätzlichen Spalten dynamisch ausblenden zu können, ist ein weiteres JavaScript
erforderlich.
Quellenangaben
[1] Rüdiger Steffan und Wolfgang Eichholz: Datenbankdesign für die fakultätsinterne Prüfungsorganisation.
Wismarer Diskussionspapiere, in Bearbeitung.
[2] Oracle® Database SQL Language Reference 11g Release 2 (11.2) Part Number E10592-02
http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/toc.htm
[3] Walther Zorn: Tooltips, JavaScript Bibliothek zum Download.
http://www.walterzorn.de/tooltip/tooltip.htm
[4] David Peake: Express Web 2.0
http://www.oracle.com/technology/oramag/oracle/07-sep/o57browser.html
[5] Oracle: APEX und AJAX
http://apex.oracle.com/pls/otn/f?p=11620:63:2698705798679629::NO:::
http://www.dba-oracle.com/t_html_db_apex_ajax_application_express.htm
http://www.oracle.com/global/de/community/tipps/ajax-02/index.html
[6] Forum Thread: How to implement fixed column headers
http://forums.oracle.com/forums/thread.jspa?threadID=364732&start=0&tstart=0
http://www.oasections.com/articles/scrollabletable.html
[7] Gernot Back: Übergroße Tabellen in koordiniert scrollenden Frames
http://aktuell.de.selfhtml.org/artikel/javascript/scrolltabelle/#a3
Kontaktadresse:
Rüdiger Steffan, Prof. Dr.-Ing.
Lehrgebiet Datenbank- und Datenkommunikationssysteme
Fakultät für Wirtschaftswissenschaften, Hochschule Wismar
University of Technology Business and Design
Phillip-Müller-Straße 14
D-23966 Wismar
Telefon:
Fax:
E-Mail
Internet:
+49(0) 3841-753606
+49(0) 3841-7539606
[email protected]
www.wi.hs-wismar.de/~steffan/