Monthly Archives: March 2013

Berechnungen summieren in einem NL Befehl

In dem Post Aging Report am Beispiel Artikelbestand habe ich beschrieben wie die Restbeträge näherungsweise berechnet werden können. Die Werte sind nicht genau wenn für einen Artikel in einem Intervall unterschiedliche Einstandspreise vorgekommen sind. In diesem Fall müsste man alle Zeilen der Artikelposten auflisten und die Berechung einzeln ausführen. Mit Jet Reports kann man das auch in einer Zelle ausführen. Das ist allerdings schon etwas anspruchsvoller und setzt sichere Kenntnise der Grundbefehle voraus.

In dem Beispielbericht wird zur Ermittlung des Restbetrages folgende Formel verwendet:

Restbetrag = ((“Einstandsbetrag (tatsächl.) ” + “Einstandsbetrag (erwartet)”) / “Menge”) * “Restmenge”

In dem Beispielbericht werden die Felder der Formel einzeln summiert und dann die Berechnung über die Summen ausgeführt . Wie schon beschrieben passt das nur exakt, wenn die Einstandspreise der Berechnung alle gleich waren. Damit das Ergebnis immer korrekt ist, muss man die Berechnung für jede Artikelpostenzeile einzeln ausführen und das jeweilige Ergebnis summieren. In Jet Reports kann man das mit NF-Funktionen erreichen, die in NL-Funktionen verwendet werden. Statt eines Feldnames kann man nämlich auch eine Berechnung an einen NL(“Summe”;..)-Befehl übergeben werden.

=NL(“Summe”;”Artikelposten”;{Formel für den Restbetrag};”Artikelnr.”
;”4711″;”Buchungsdatum”;”1.1.12..31.1.12″;”Restmenge”;”<>0″)

Der NL-Befehl wird bei dieser Variante jede Zeile der Artikelposten durchlaufen, die dem Filter entsprechen und das Berechnungsergebnis summieren. Intern steht Jet Reports damit ein Zeilenschlüssel zur Verfügung (Wie aus einem NL(“Zeilen”;…) Befehl). Diesen Schlüssel kann man mit NF-Befehlen verwenden. Den Schlüssel muss man aber gar nicht angeben, denn der umgebende NL-Befehl “weiß” ja bereits welches gerade die aktuelle Zeile der Artikleposten ist. Man kann den Schlüssel innerhalb eines NL-Befehls daher einfach leer lassen. Die Berechnung sähe dann so aus:

Restbetrag = (NF(;”Einstandsbetrag (tatsächl.) ” + NF(;”Einstandsbetrag (erwartet)”)) / NF(;”Menge”)) * NF(;”Restmenge”)

Das sieht schon gut aus. Es wird aber leider noch etwas komplizierter. Um die Berechnung in einem NL-Befehl durchzuführen muss der Begriff die Form einer Excel-Formel haben. Das wird Jet Reports mit einem einleitenden Gleichheitszeichen signalisiert.

“={Formel für den Restbetrag}”

Sieht einfach aus, bringt aber eine weitere Schwierigkeit in unsere Aufgabe. Damit unsere Berechnung mit den NF-Befehlen klappt, brauchen wir die Anführungszeichen der Feldnamen. Ein Anführungszeichen beendet aber leider das einleitende Anführungszeichen der gesamten Formel. Das kann man in Excel eingeben, indem man vor jedes auszugebende Anführungszeichen ein weiteres Anführungszeichen stellt.

“=(NF(;””Einstandsbetrag (tatsächl.)”” + NF(;””Einstandsbetrag (erwartet)””)) / NF(;””Menge””)) * NF(;””Restmenge”)””

Das Erfassen der Anführungszeichen kann recht unübersichtlich werden. Daher hat Jet Reports eine Funktion dafür integriert. Im Tools-Menü von Jet Reports findet man den Punkt “Quote”. Das setzt die Anführungszeichen für die aktuelle Zelle. Der Menüpunkt Unquote stellt den Ursprungszustand wieder her. Ich baue mir die Befehle daher Schritt für Schritt zusammen und verwende zum Schluss die Quote funktion:

  • NF-Befehl mit dem Jfx erstellen
    (Mit zugehörigem Zeilenschlüssel um den Feldnamen auszuwählen)
    =NF($E$19;”Einstandsbetrag (tatsächl.))
  • NF Befehl zum Text umwandeln und den Schlüssel entfernen
    NF(;”Einstandsbetrag (tatsächl.)”)
  • Den Text mit dem Quote-Befehl aus dem Tools-Menü umwandeln
    =”NF(;””Einstandsbetrag (tatsächl.)””)”

Das ist zugegebenermaße etwas kompliziert und nur was für Jet Reports Experten, aber die Funktion selber ist sehr mächtig. Die erstellte Formel können wir nun einfach in den Feld-Parameter vom NL(“Summe”;..)-Befehl eintragen.

=NL(“Summe”;”Artikelposten”; “=(NF(;””Einstandsbetrag (tatsächl.)”” + NF(;””Einstandsbetrag (erwartet)””)) / NF(;””Menge””)) * NF(;””Restmenge”)””;”Artikelnr.”
;”4711″;”Buchungsdatum”;”1.1.12..31.1.12″;”Restmenge”;”<>0″)

Mit diesem Befehl wird nun tatsächlich die Berechnung für jede Zeile der gefilterten Artikelposten ausgeführt. Das Ergebnis ist eine stets korrekte Berechnung des Lagerbestandes. Besonders nett ist dabei, dass sogar der Drilldown auf die Artikelposten funktioniert.

Ich habe das auf den vorherigen Aging-Bericht angewendet. Nun entfallen alle Zwischenberechnungen. Da diese Funktion ohnehin was für Experten ist, habe ich die Spalten auch dynamisch erstellen lassen. Ich habe jetzt nur noch zwei NL(“Summe”;…)-Befehle im Bericht. Einen für die Restmenge und einen für den Restbetrag.

Ausgeführt sieht der neue Bericht dann so aus:

Item Aging Experte Ausgefuehrt

Item Aging Experte ausgefuehrt

Per Drilldown kann man die Werte in der Anwendung kontrollieren

Item Aging Experte Drilldown

Item Aging Experte Drilldown

Im Designmodus kann man den Aufbau erkennen.

Item Aging Experte Design

Item Aging Experte Design

Den Bericht können Sie hier herunterladen:

JetReports-Artikelbestand-Aging-Experte.xls

Die Formeln im Bericht sind in englischer Sprache gehalten. Auf diese Weise kann
der Bericht auch auf installation in anderen Sprachen als deutsch ausgeführt
werden. Das funktioniert, weil Jet Reports in den Formeln die gewählte Sprache
(Caption) und alternativ den programmierten Feldnamen (Name) verwendet. Wenn Sie
Ihre Navision in verschiedenen Sprachen betreiben, dann empfehle ich deswegen
das Sprachmodul in den Datenquelleneinstellungen gar nicht erst zu laden. Dann
braucht man die Formeln in den Berichten nicht zu übersetzen.

 

Andreas Koblischke
datenkultur GmbH

 

Neue Version verfügbar: Jet Essentials 2012 R2 Build 12.5.13072.0

Es gibt eine neue Version von Jet Reports zum Download.

Für diese Version muss Ihr Update mindestens noch bis zum 14.3.2013 in Ihrer Jet Reports Lizenz eingetragen sein.

Änderungen seit Version 12.5.13039.0

  • Support für alle vier Authentifizierungsmethoden von Dynamics NAV 2013 hinzugefügt.
  • Geschwindigkeit für Dynamics NAV 2013 Datenquellen verbessert.
  • Fehler behoben: Dynamics NAV Datenquellen sortierten z.T. nicht korrekt, wenn mehr als zwei Sortierfelder angeben wurden
  • Fehler behoben: Im Jfx-Assistenten waren für die Funktionen NL(“Zeilen”;..) und NL(“Spalten”;..) die Spezialfilter (z.B. Link=, etc.) nicht im Lookup verfügbar.

Alle Änderungen der letzten Versionen können Sie in der ChangeList nachlesen.

Für den Download der Dateien und Informationen brauchen Sie einen Zugriff auf die Supportseiten von Jet Reports. Wenn Sie dort noch keinen Zugang haben, dann können Sie sich hier für den Zugang anmelden.

Bitte verwenden Sie für

– 32 Bit Office diese Installationsversion

– 64 Bit Office diese Installationsversion

Wichtig: Sie können die aktuelle Version nur installieren, wenn Sie noch in der Wartung sind. Sie finden die Information in Ihrer Jet Reports Lizenzdatei im Punkt “Updates thru”. Die Lizenzdatei ist ein Textdatei, die auf .JLF endet und sich oft im Programmverzeichnis von Jet Reports befindet. In der Datenquellen-Einrichtung können Sie die Lizenzdatei ebenfalls einsehen. In alten Versionen gab es einen Knopf “Teste Verbindung”. In den neueren Versionen finden Sie auf dem Reiter “Allgemein” unten links einen Link. Wenn Sie darauf klicken, dann wird Ihre Lizenzdatei geöffnet. Bitte beachten Sie, dass es sich um ein amerikanisches Datumsformat handelt (Jahr/Monat/Tag).

Andreas Koblischke
www.datenkultur.de