Neues View für Berichte anlegen

Wie man auf der Seite Statistik und Berichte sehen kann, gibt es vielfältige Möglichkeiten, Auswertungen zu erstellen. Allerdings ist man, wenn man den internen Reportgenerator benutzt, immer an das Korsett der vorgegebenen Tabellen und Views gebunden. Diese Hürde kann man aber natürlich in iDempiere auch überwinden.

Grundsätzlich kann man aufgrund einer bestehenden Tabellendefinition auch in iDempiere eine Berichtsansicht definieren, die die Sicht auf die Tabelle einschränkt. Die größte Flexibilität - insbesondere die Möglichkeit, mehrere Tabellen miteinander zu verknüpfen (JOIN) - bietet aber ein echtes Datenbank-View, dessen Erstellung hier beschrieben wird.

Anwendungsfall

Bei der Umstellung meiner Buchhaltung hatte ich mich entschieden, den Kontenplan des Unternehmens zu wechseln. Um den Zusammenhang zur alten Buchführung nicht zu verlieren, hatte ich die Tabelle der Konten (C_ElementValue) um ein Feld "OldName" erweitert. Hier habe ich die Kontonummer der alten Buchhaltung eingetragen.

Auswertungen der Buchhaltung in iDempiere basieren zumeist auf zwei Menüpunkten, die in der jetzigen Übersetung beide "Buchführungsdaten Details" heissen. Die eine (das Fenster) greift auf die Tabelle "Fact_Acct", die andere (der Bericht) greift auf die Tabelle "RV_Fact_Acct" zu.

Diese Tabelle ist bereits ein Beispiel dafür, was wir hier vorhaben. Es handelt sich nämlich nicht um eine Tabelle, sondern um ein Datenbank-View. Views, die Daten für Berichte zusammenstellen, beginnen per Konvention mit "RV_" (für "Report View") und können beliebige Daten aus der Menge der iDempiere-Tabellen zusammenstellen.

Leider enthält dieses View zwar alle relevanten Felder aus der Kontobeschreibung, aber nicht die "OldName" Spalte. Also habe ich mich entschlossen, dieses als Beispiel für ein neues View zu nehmen. Hier kann man nicht nur bereits vorher sehen, wie so ein View aufgebaut ist, sondern wir können auch die bereits vorhandenen Definitionen nutzen und kopieren und müssen nicht jedes Feld neu definieren.

Übrigens wäre es natürlich viel einfacher möglich, die zusätzliche Spalte in das vorhandene View aufzunehmen. Dann wäre das hier aber kein Beispiel für ein neues View. :-)

View erzeugen

Ein View basiert auf einer Datenbankabfrage (engl. Query), die aus einem SQL SELECT Befehl besteht. Diese Abfrage wird durch den Datenbank-Befehl "CREATE VIEW" in ein View gegossen. Wer PgAdmin3 benutzt, kann die Views innerhalb der Datenbank in einem eigenen Ast der Baumansicht sehen. Klickt man auf das View, erscheint rechts im Fenster die zugrundeliegende Abfrage. Die kann man nun per Copy & Paste in ein SQL-Fenster übernehmen und als Befehl ausführen. Der View für unser Beispiel wird folgendermassen definiert:

  CREATE OR REPLACE VIEW rv_fact_acct_bay AS 
  SELECT f.ad_client_id, f.ad_org_id, f.isactive, f.created, f.createdby, f.updated, f.updatedby, f.fact_acct_id, f.c_acctschema_id, f.account_id, 
       f.datetrx, f.dateacct, f.c_period_id, f.ad_table_id, f.record_id, f.line_id, f.gl_category_id, f.gl_budget_id, f.c_tax_id, f.m_locator_id, 
       f.postingtype, f.c_currency_id, f.amtsourcedr, f.amtsourcecr, f.amtsourcedr - f.amtsourcecr AS amtsource, f.amtacctdr, f.amtacctcr, 
       f.amtacctdr - f.amtacctcr AS amtacct, 
       CASE
           WHEN (f.amtsourcedr - f.amtsourcecr) = 0::numeric THEN 0::numeric
           ELSE (f.amtacctdr - f.amtacctcr) / (f.amtsourcedr - f.amtsourcecr)
       END AS rate, f.c_uom_id, f.qty, f.m_product_id, f.c_bpartner_id, f.ad_orgtrx_id, f.c_locfrom_id, f.c_locto_id, f.c_salesregion_id, f.c_project_id, 
       f.c_campaign_id, f.c_activity_id, f.user1_id, f.user2_id, f.a_asset_id, f.description, o.value AS orgvalue, o.name AS orgname, 
       ev.value AS accountvalue, ev.name, ev.accounttype, bp.value AS bpartnervalue, bp.name AS bpname, bp.c_bp_group_id, p.value AS productvalue, 
       p.name AS productname, p.upc, p.m_product_category_id, f.c_projectphase_id, f.c_projecttask_id, f.c_subacct_id, f.userelement1_id, 
       f.userelement2_id, o.description AS ad_org_description, o.isactive AS ad_org_isactive, ev.ad_org_id AS c_elementvalue_ad_org_id, 
       ev.c_bankaccount_id, ev.c_currency_id AS c_elementvalue_c_currency_id, ev.c_element_id, ev.c_elementvalue_id, 
       ev.description AS c_elementvalue_description, ev.isactive AS c_elementvalue_isactive, ev.isbankaccount, ev.isforeigncurrency, 
       ev.issummary AS c_elementvalue_issummary, bp.acqusitioncost AS c_bp_acqusitioncost, bp.actuallifetimevalue AS c_bp_actuallifetimevalue, 
       bp.ad_language AS c_bp_ad_language, bp.ad_orgbp_id AS c_bp_ad_orgbp_id, bp.ad_org_id AS c_bp_ad_org_id, 
       bp.bpartner_parent_id AS c_bp_bpartner_parent_id, bp.c_dunning_id AS c_bp_c_dunning_id, bp.c_greeting_id AS c_bp_c_greeting_id, 
       bp.c_invoiceschedule_id AS c_bp_c_invoiceschedule_id, bp.c_paymentterm_id AS c_bp_c_paymentterm_id, bp.created AS c_bp_created, 
       bp.createdby AS c_bp_createdby, bp.c_taxgroup_id AS c_bp_c_taxgroup_id, bp.deliveryrule AS c_bp_deliveryrule, 
       bp.deliveryviarule AS c_bp_deliveryviarule, bp.description AS c_bp_description, bp.dunninggrace AS c_bp_dunninggrace, bp.duns, 
       bp.firstsale AS c_bp_firstsale, bp.flatdiscount AS c_bp_flatdiscount, bp.freightcostrule AS c_bp_freightcostrule, 
       bp.invoicerule AS c_bp_invoicerule, bp.isactive AS c_bp_isactive, bp.iscustomer AS c_bp_iscustomer, 
       bp.isdiscountprinted AS c_bp_isdiscountprinted, bp.isemployee AS c_bp_isemployee, bp.ismanufacturer AS c_bp_ismanufacturer, 
       bp.isonetime AS c_bp_isonetime, bp.ispotaxexempt AS c_bp_ispotaxexempt, bp.isprospect AS c_bp_isprospect, bp.issalesrep AS c_bp_issalesrep, 
       bp.issummary AS c_bp_issummary, bp.istaxexempt AS c_bp_istaxexempt, bp.isvendor AS c_bp_isvendor, bp.logo_id AS c_bp_logo_id, 
       bp.m_discountschema_id AS c_bp_m_discountschema_id, bp.m_pricelist_id AS c_bp_m_pricelist_id, bp.naics, bp.name2 AS c_bp_name2, 
       bp.numberemployees AS c_bp_numberemployees, bp.paymentrule AS c_bp_paymentrule, bp.paymentrulepo AS c_bp_paymentrulepo, 
       bp.po_discountschema_id AS c_bp_po_discountschema_id, bp.po_paymentterm_id AS c_bp_po_paymentterm_id, 
       bp.po_pricelist_id AS c_bp_po_pricelist_id, bp.poreference AS c_bp_poreference, bp.potentiallifetimevalue AS c_bp_potentiallifetimevalue, 
       bp.rating AS c_bp_rating, bp.referenceno, bp.salesrep_id AS c_bp_salesrep_id, bp.salesvolume AS c_bp_salesvolume, 
       bp.sendemail AS c_bp_sendemail, bp.shareofcustomer AS c_bp_shareofcustomer, bp.shelflifeminpct AS c_bp_shelflifeminpct, 
       bp.so_creditlimit AS c_bp_so_creditlimit, bp.socreditstatus AS c_bp_socreditstatus, bp.so_creditused AS c_bp_so_creditused, 
       bp.so_description AS c_bp_so_description, bp.taxid, bp.totalopenbalance AS c_bp_totalopenbalance, bp.updated AS c_bp_updated, 
       bp.updatedby AS c_bp_updatedby, bp.url AS c_bp_url, p.ad_org_id AS m_product_ad_org_id, p.classification, p.copyfrom AS m_product_copyfrom, 
       p.created AS m_product_created, p.createdby AS m_product_createdby, p.c_revenuerecognition_id, p.c_subscriptiontype_id, p.c_taxcategory_id, 
       p.c_uom_id AS m_product_c_uom_id, p.description AS m_product_description, p.descriptionurl, p.discontinued, p.discontinuedat, p.documentnote, 
       p.group1, p.group2, p.guaranteedays, p.guaranteedaysmin, p.help, p.imageurl, p.isactive AS m_product_isactive, p.isdropship, 
       p.isexcludeautodelivery, p.isinvoiceprintdetails, p.ispicklistprintdetails, p.ispurchased, p.isselfservice, p.issold, p.isstocked, 
       p.issummary AS m_product_issummary, p.isverified, p.iswebstorefeatured, p.lowlevel, p.m_attributeset_id AS m_product_m_attributeset_id,
       p.m_attributesetinstance_id AS m_product_m_asi_id, p.m_freightcategory_id, p.m_locator_id AS m_product_m_locator_id, 
       p.processing AS m_product_processing, p.producttype, p.r_mailtext_id, p.salesrep_id AS m_product_salesrep_id, p.s_expensetype_id, p.shelfdepth, 
       p.shelfheight, p.shelfwidth, p.sku, p.s_resource_id, p.unitsperpack, p.unitsperpallet, p.updated AS m_product_updated, 
       p.updatedby AS m_product_updatedby, p.versionno, p.volume, p.weight
       -- Extension Thomas Bayen
       , ev.oldname
  FROM fact_acct f
  JOIN ad_org o ON f.ad_org_id = o.ad_org_id
  JOIN c_elementvalue ev ON f.account_id = ev.c_elementvalue_id
  LEFT JOIN c_bpartner bp ON f.c_bpartner_id = bp.c_bpartner_id
  LEFT JOIN m_product p ON f.m_product_id = p.m_product_id;
     
  ALTER TABLE rv_fact_acct_bay OWNER TO adempiere;

Ich weiss, das ist schrecklich lang - aber so ist nun mal mein Beispiel. Grundsätzlich dienen die RV_-Views ja auch dazu, dem Benutzer für seine Berichte möglichst viele Möglichkeiten zu bieten. Deshalb ist es eigentlich sinnvoll, hier auch möglichst viele Spalten anzulegen. Man weiss nie, was man später mal braucht.

Der obige View unterscheidet sich übrigens von dem original vorgegebenen, den ich als Kopiervorlage benutzt habe, nur durch die eine Zeile ", ev.oldname" (um die es hier geht). Es sieht also komplizierter aus als es ist.

Tabellendefinition anlegen

Nun logge ich mich als System-Administrator in den Mandanten "System" ein und öffne das Fenster "Tabellen und Felder". Dort suche ich die Tabelle "RV_Fact_Acct" und kopiere Ihren Eintrag. In der Kopie ändere ich den Tabellennamen in der Datenbank auf "RV_Fact_Acct_BAY" (wie oben im "CREATE VIEW"-Befehl angegeben) und wähle einen passenden Namen für die Tabellendefiniton wie z.B. "Kontensätze ausführlich". Dann kann ich über den Prozess-Button "Spalten aus Tabelle kopieren" in der Toolbar alle Spalten-Einträge von der ursprünglichen Tabellendefinition in meine neue kopieren. Nachdem ich das gemacht habe, kann ich noch mittels des Knopfes "Spalten aus Datenbank erzeugen" die bereits angelegten Spalten um neue ergänzen. An dieser Stelle wird meine Spalte "OldName" automatisch erzeugt. :-)

Legt man eine Tabellendefiniton ganz neu an, ist es nur wichtig, zu wissen, das man das Feld "Ansicht" auswählt. Dies besagt, das es sich bei der definierten Tabelle um ein View handelt und sorgt z.B. dafür, das iDempiere weiss, das die Tabelle nur lesbar ist.

Außerdem sollte man einen Haken bei "hohes Volumen" setzen. Dieser sorgt dafür, das man beim Öffnen unseres Datenfensters später sofort eine Suchmaske angezeigt bekommt (und nicht die ersten Datensätze). Da unser Ziel zumeist ein Bericht ist, ist die Suche (die eine Filterung der Daten bewirkt) meistens das erste, was wir wollen.

Fensterdefinition anlegen

Um einen Bericht anzuzeigen ist eigentlich kein normales Fenster (in dem man Werte editieren kann) nötig. Ich mag das dennoch, weil man dort nochmal einen unverstellten Blick auf die Berichtsdaten hat. (Bei sehr langen Views (wie unserem hier) muss ich jedoch warnen, das die ZK-Oberfläche von iDempiere durch die grosse Tabelle sehr langsam wird.) Letztlich kann man hier auch einen schnellen Prototypen des Suchfilters erstellen und ändern.

Ich habe nun also im System-Mandanten das Fenster "Fenster, Register und Felder" geöffnet und ein neues Fenster erstellt. Den Fenstertyp kann man auf "nur Suche" stellen. Nachdem man einen Namen vergeben hat, erzeugt man ein Register. Hier vergibt man einen Namen und eine Beschreibung. Die "Einzelsatzdarstellung" schaltet man besser aus. In dieses Register können wir nun mit dem Knopf "Felder erstellen" die Felder aus der Tabellendefinition herüberziehen. Das war's dann eigentlich auch schon.

Menüpunkt anlegen

Um das Anzeigefenster im Menü zu verankern öffne ich im System-Mandanten das Fenster "Menü" und lege einen neuen Eintrag vom Typ "Fenster" an, der auf die soeben erzeugte Fensterdefinition verweist. Nun starte ich das Programm neu als normaler Benutzer neu und starte mein Fenster.

Filter erfassen

Wenn ich das Fenster starte, sehe ich das Suchfenster mit den vorgegebenen Suchparametern. Dort stehen nun die Spalten der Tabelle, die in der Tabellendefinition als "Auswahlspalte" angewählt sind. Hier ist eine einfache Filterung möglich.

Wem das nicht reicht, der kann in der Suche auf das Register "erweitert" klicken und eine komplexere Abfrage eingeben. Vor allem ist es möglich, diese Suchparameter durch das Eingabefeld oben (mit dem Diskettensymbol daneben) mit einem Namen zu versehen und abzuspeichern. Dadurch kann man die Suche später immer wieder laden und dann ggf. anpassen und ausführen.

Hat man die Suchparameter eingegeben, drückt man auf den grünen Haken. Erscheinen die gefilterten Datensätze, so kann man direkt auf den Berichts-Knopf in der Toolbar klicken, um den Bericht anzuzeigen.

Diese Methode, einen Suchfilter einzubringen, ist sehr leistungsfähig, weil die erweiterte Suche sehr gut konfigurierbar ist. Dafür muss man ein Anzeigefenster öffnen (das man ja eigentlich gar nicht braucht) und ein bisschen herumklicken. Wer es in der Bedienung einfacher haben möchte, sollte stattdessen einen eigenen Bericht definieren (siehe unten).

Berichts-Ausgabe und Berichts-Format konfigurieren

Die Konfiguration des Berichts läuft nun wie bei allen Berichten des internen Berichtsgenerators (natürlich kann auch JasperReports, Charts, etc. zum Einsatz kommen) ab. Deshalb beschreibe ich das hier nicht sondern verweise auf die Seite Statistiken und Berichte. Es bietet sich auf jeden Fall an, ein eigenes Druckformat zu definieren, das die Felder und das Layout beschreibt, wie man es haben will.

direkten Bericht erstellen

Anstatt eines Fensters können wir aus der Tabelle auch direkt einen Bericht (oder natürlich auch beides) erzeugen. Mit einem solchen Bericht nehme ich dem Benutzer die Entscheidungen ab, wonach er suchen soll und welches Druckformat er nutzt etc. Hierzu öffne ich im System-Mandant das Fenster "Bericht & Prozess" und lege einen neuen Bericht an.

Ich aktiviere das Feld "Bericht" (denn ich möchte keinen Prozess definieren).

Berichts-Parameter

Im "Bericht & Prozess"-Fenster gibt es ein Unterregister, in dem ich Parameter eingeben kann. Hier kann ich einerseits definieren, welche Fragen der Bericht stellt, wenn er vom Benutzer gestartet wird. Nach den dann eingegebenen Werten kann dann gefiltert werden. Hier können aber auch durch die Standardlogik und die Schreibschutzlogik fest vorgegebene Werte angegeben werden, die für genau diesen Bericht passen (und für den nächsten vielleicht anders sind, obwohl der auf derselben Tabelle/View operiert).

Formeln und Gruppierung

Ich erzeuge durch den Zoom auf das Feld "Bericht Datenbank-Ansicht" ein neues Objekt dieser Art. Die "Bericht Datenbank Ansicht" verknüpft dann noch einmal den Bericht mit der Tabellendefinition. Sie erlaubt eine weitere Filterung der Daten per SQL-WHERE Befehl und eine Sortierung der Daten.

Brauche ich das nicht, reicht es, die Berichts-Ansicht nur anzulegen.

Andererseits erlaubt sie auch, in einem Untertab Formelfelder einzusetzen sowie eine Gruppierung der Daten vorzunehmen. Gruppierung bedeutet, das ich z.B. nur einen Buchungsdatensatz pro Tag erzeuge und alle anderen saldiere. Formelfeld bedeutet, das ich einerseits innerhalb der Daten rechnen kann ("soll + haben" ergibt dann "Saldo"). Ich kann aber auch komplexere SQL-Abfragen z.B. mit Subselects schreiben, die auf ganz andere Tabellen der Datenbank zugreifen. Zuletzt kann ich hier auch Summenformeln verwenden, die ich brauche, wenn ich nach einem anderen Feld gruppiert habe. (d.h. ich gruppiere nach dem Datum und der Saldo wird aufsummiert).

Die Gruppierung kann man übrigens auch im Druckformat erzeugen. Der Unterschied ist, das die Gruppierung in "Bericht Datenbank Ansicht" bereits viel früher im Programmlauf stattfindet und so in den Zwischenschritten viel weniger Daten mitgeschleppt werden. Das spart Speicherplatz und Rechenzeit und kann bei sehr grossen Datenmengen den Unterschied machen, ob die Abfrage überhaupt möglich ist. Es ist ein Unterschied, ob ich sage "nimm Dir zuerst einmal alle Umsätze, gruppiere sie dann nach dem Bundesland und sortiere sie" oder ob ich sage "nimm Dir zuerst einmal die Umsätze nach Bundesland und sortiere sie dann".

Menüpunkt anlegen

Um den Bericht im Menü zu verankern gehe ich ähnlich vor wie für das Fenster oben beschrieben. Lediglich die ausgewählte Aktion ist "Bericht".

Links

• http://www.adempiere.com/Creating_a_Report_View - kurzer Artikel zum Thema im alten Wiki