Crystal Reports - Standard Reporting mit HTML und
dynamischem SQL unter Oracle

Vielfältige Möglichkeiten zur Berichterstellung stellt das Tool Crystal Reports als Alternative zu Oracle Reports zur Verfügung. Mit wenigen Handgriffen ist es möglich, einen dynamisch zu konfigurierenden Bericht mit Zugriff auf diverse Datenquellen zu realisieren. Alle Zutaten, die man hierzu braucht, sind wenige Kentnisse über Crystal Reports, über simple HTML-Tags und - bei Zugriff auf eine Oracle-Datenbank - über dynamisches SQL unter Oracle.

Abb. 1: Unterberichte. Vergrößern Abb. 2: Formel zur Parameterübergabe an Unterberichte. Abb. 3: Formeltext. Vergrößern Abb. 4: Berichtskopf - Verknüpfung von Unterberichten. Vergrößern   CREATE TYPE ty_data AS OBJECT ( nsort NUMBER, dsort DATE, vsort VARCHAR2(100), data1 VARCHAR2(4000) ); CREATE TYPE ty_data_tab AS TABLE OF ty_data; CREATE FUNCTION get_data ( params IN VARCHAR2 DEFAULT NULL ) RETURN ty_data_tab PIPELINED ... CREATE TABLE mitarbeiter ( mitarbeiternr NUMBER NOT NULL, mitarbeitername VARCHAR2(30), beruf VARCHAR2(30), vorgesetzter NUMBER, eintrittsdatum DATE, gehalt NUMBER, provision NUMBER, abteilungsnr NUMBER ); CREATE TABLE abteilung ( abteilungsnr NUMBER NOT NULL, abteilungsname VARCHAR2(30), niederlassung VARCHAR2(30) ); Abb. 5: Verwendete Datenbankobjekte. DECLARE TYPE tt_param_list IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(50); param_lis t tt_param_list; param_string VARCHAR2(100) := 'SQL=STD_REP|' || 'OPTIONS=1,2,3,4|VON=01.01.2009|BIS=|' || 'BEMERKUNG=Hallo Welt'; param VARCHAR2(100); BEGIN FOR n IN 1 .. REGEXP_COUNT(param_string, '[^|]+') LOOP param := REGEXP_SUBSTR(param_string, '[^|]+', 1, n); param_list(REGEXP_SUBSTR(param, '[^=]+', 1, 1)) := REGEXP_SUBSTR(param, '[^=]+', 1, 2); END LOOP; END; Abb. 6: Zerlegen von Parametern. //Beispiel für den Berichtkopf SELECT * FROM TABLE( get_data( '{?PARAMS}|BEREICH=KOPF' ) ) Abb. 7: Selektionsbeispiel für den Berichtskopf. Abb. 8: Hinzufügen von Feld DATA1 in Unterbericht BERICHTKOPF. Vergrößern Abb. 9: Beispielausgabe des Reports. Vergrößern

Einleitung

Dieser Artikel beschreibt die Möglichkeit der dynamischen Berichtserstellung mit Crystal Reports und Nutzung einer Oracle-Datenbank zur Konfiguration und Auswertung von Daten. Mit Hilfe dieses Reports kann die wesentliche Logik in der Datenbank untergebracht werden. Damit lässt sich die Anzahl zu erstellender Reports verringern. Dies wiederum verringert die Komplexität und den Pflegeaufwand der benötigten Berichte. Der Datenschutz kann durch eine Rechtevergabe geregelt werden.

Vorbereitung

Für die Umsetzung des in diesem Artikel beschriebenen Reports wird Crystal Reports in der Version 12 (2008) und Oracle 11g verwendet. Eine Testversion von Crystal Reports kann unter http://www.sap.com/ heruntergeladen werden. Die Nutzung von Oracle 11g macht die Verwendung von dynamischem SQL und großen Zeichenketten (CLOB) einfacher.

Aufbau des Reports

Der Aufbau des Reports ist recht einfach. Wie Abbildung 1 zeigt, wird für jeden Bereich (Berichtkopf, Seitenkopf, Details, usw.) ein Unterbericht erstellt. Im Berichtkopf werden Parameterfelder hinzugefügt. Diese werden in der Parametermaske dem Endbenutzer zur Auswahl oder Eingabe von Daten angezeigt. Um Parameterfelder für den Nutzer auf dem Report "unsichtbar" zu machen, werden die Rahmen entfernt und die Schriftfarbe auf weiß gesetzt. Die Rahmen der Unterberichte werden ebenfalls entfernt. Parameterfelder, wie zum Beispiel Auswahllisten, können unter Crystal Reports Werte aus einer Selektionsanweisung enthalten. In diesem Fall bietet sich auch die Möglichkeit an, die in diesem Artikel beschriebene Funktion zu nutzen.

Parameterübergabe an Unterberichte

Damit jeder Unterbericht mit den Parametern des Benutzers arbeiten und spezifische Daten anzeigen kann, wird eine Formel für alle Unterberichte erstellt (siehe Abbildung 2 und 3). Diese Formel fasst alle Parameter zusammen und dient später der Verknüpfung zu den Unterberichten. Damit die Verknüpfung erstellt werden kann, muss sich in den Unterberichten jeweils ein Parameter befinden, der die Parameterwerte aufnehmen kann (siehe Abbildung 4). Die Parameterwerte werden beispielsweise mit Pipe "|" getrennt. Sollte ein Parameter Listenwerte enthalten, so kann die Crystal Reports Funktion Join genutzt werden. Sie liefert alle Werte einer Liste, getrennt mit dem angegebenen Separator: Join({?PARAMLIST}, ",") // liefert z. B. "ListValue1,ListValue2,...". Ist der gesamte Parameter-String zusammengefügt, folgt die Betrachtung der Datenbank.

Zur Datenbankebene

Damit der Report mit Leben gefüllt werden kann, wird ein Stück PL/SQL-Code auf der Datenbankseite benötigt. Dies kann entweder eine Prozedur mit REF CURSOR oder eine PIPELINED FUNCTION sein. In diesem Artikel wird die Möglichkeit mit PIPELINED FUNCTION vorgestellt. Die Funktion sollte so aufgebaut werden, dass sie alle Parameter aus Crystal Reports aufnehmen kann und eine COLLECTION zurückliefert (siehe Abbildung 5).

Parameter zerlegen

Da alle Parameter als lange Zeichenkette übergeben werden, muss diese erst zerlegt werden. Mit Hilfe der einzelnen Parameter und Parameterwerte kann dann die Logik im Hauptteil der Funktion gestaltet werden. Für einen verbesserten Zugriff auf einzelne Parameter werden diese in einem assoziativem Array gespeichert. Als Beispiel kann hier auf den PL/SQL-Code in Abbildung 6 zurückgegriffen werden.

Zurück zur Funktion

Alle Parameter liegen nun in mundgerechten Stücken vor und können entsprechend ausgewertet werden. Die Hauptaufgabe der Funktion besteht darin, die Selektionsanweisung für die Ausgabe der Daten "zusammenzubauen" und diese dann per PIPE-Anweisung auszugeben. Die komplette Prozedur finden Sie hier.

Crystal Reports und
PIPELINED FUNCTIONS

Wirft man einen Blick zurück in den Report, fällt auf, dass es noch keine Datenbankverbindung bzw. Felder gibt, die Daten anzeigen könnten. Geht man nun in einen Unterbericht, kann über den Datenbankassistenten von Crystal Reports eine Verbindung zur Oracle-Datenbank aufgebaut und ein neuer Befehl hinzugefügt werden. Die Selektion auf eine PIPELINED FUNCTION ist sehr einfach mit der Funktion TABLE() zu realisieren. Damit die Funktion auch mit unseren Parametern arbeiten kann, binden wir sie gleich mit ein (siehe Abbildung 7).

Der Parameter PARAMS muss hier noch einmal rechts in der Parameterliste erzeugt werden. Erst dadurch wird der Platzhalter {?PARAMS} durch den Inhalt des Parameters PARAMS vor der Ausführung der Query ausgetauscht.

Anzeigefelder im HTML-Format

Da nun die Verbindung zur Datenbank steht, sollten die Daten auch angezeigt werden. Dazu wird beispielsweise das Feld "DATA1" (siehe Abbildung 8) dem Report hinzugefügt. Wählt man nun für das Feld im Kontextmenü den Punkt "Feld formatieren", kann unter der Tabpage "Absatz" die Textinterpretation auf HTML eingestellt werden. Es ist darauf zu achten, welche Schriftart man für dieses HTML-Feld wählt. Soll der angezeigte Text tabellarisch dargestellt werden, empfiehlt sich eine nichtproportionale Schriftart. Hierdurch können Abstände zwischen Spalten mit Leerzeichen realisiert werden.

HTML im Report

Crystal Reports interpretiert einfache HTML-Tags für die Anzeige im Report, wie z. B. <B> für Bold, <I> für Italic, <U> für Underline, <FONT> für Schriftart, <PRE> für Darstellung und vorformatierten Text. Das HTML-Tag <TABLE> wird von Crystal Reports leider nicht komplett interpretiert. Die Breite einer Spalte wird gekonnt ignoriert. Dies wäre eine Alternative zum vorformatierten Text mit <PRE>.

Wie der Quelltext zeigt, werden in der Query für die Detaildaten des Reports schon einfache HTML-Tags verwendet, z. B. für die unterschiedliche Einfärbung von Gehältern. In Abbildung 9 wird die Ausgabe mit diesem Quelltext dargestellt.

Verbesserungen

Der im Beispiel verwendete Report kann mit einigen Handgriffen komfortabler gestaltet werden. Beispielsweise könnten alle Select-Anweisungen in einer Tabelle gespeichert und je nach Parameterwerten selektiert und zusammengestellt werden. Crystal Reports unterstützt nicht nur Platzhalter {?PLATZHALTER} sondern auch Funktionen {@FUNKTION}, die ggf. komplexere Anweisungen ausführen. Platzhalter für Parameter haben wir schon beim Aufruf der PIPELINED FUNCTION kennengelernt. Warum sollte man die Idee einer Funktion nicht auch unter PL/SQL realisieren. Anstatt direkt eine SQL-Anweisung auszuführen, könnte erst ein PL/SQL-Quelltext aus einer Tabelle dynamisch ausgeführt werden. Eine Bindevariable vom Typ CLOB würde die auszuführende Query beinhalten und dann durch die Funktion ausgeführt werden.

Fazit

Die angedeutete Lösung zur dynamischen Berichterstellung mag für komplexe Ausgaben nicht immer das geeignete Mittel sein. Man erreicht jedoch einen gewissen Standard für wiederkehrende Reports und eine bessere Übersicht durch Zentralisierung der Logik in der Datenbank.

Karsten Fiedler (info@ordix.de).

Seitenanfang

Seite drucken

Seite weiterempfehlen