On 2014-10-31 12:32, Vikas Rawal <vikaslists@agrarianresearch.org> writes:

> 2. Generating reports: Babel results do not use the standard Org table
> syntax. Given my limited knowledge of lisp, I am finding it difficult
> to programme customising these reports in Org. It would be of much
> benefit if other users could share how they customise ledger reports
> in Org. Some examples would be great.

I generate reports and deal with my budget using org. I describe my
setup here:
http://lists.gnu.org/archive/html/emacs-orgmode/2014-04/msg00727.html

Since then, I've slightly refined these functions, here is the current
version:

#+begin_src org
#+name: call_ledger
#+begin_src emacs-lisp :var lcmd="bal" :var bucket="Quotidien" :var prefix="Expenses:" :var period=()
  (let* ((name (org-trim bucket))
         (bname (concat "'^" prefix name "'"))
         (ledger "ledger -f ~/Documents/Org/mescomptes.ledger")
         (parg (when period (concat " -p '" period "'")))
         (cutcmd "tail -1 | cut -d ' ' -f 2")
         (cmd 
          (concat ledger " -J " parg " " lcmd " " bname " | " cutcmd))
         (res (org-trim (shell-command-to-string cmd))))
    (if (equal res "") 0 res))
#+end_src

#+name: monthly_average_since
#+begin_src emacs-lisp :var start-date="2014-03-01" :var amount=100
  (let ((nbdays (- (time-to-days (current-time))
                   (time-to-days (org-read-date nil t start-date)))))
    (calc-eval "round($ / (12 * ($$ / 365.25)), 2)" nil amount nbdays))
#+end_src

#+name: monthly_average
#+begin_src emacs-lisp :var starty=2014 :var startm=3 :var amount=100 
  (let* ((tm (decode-time))
         (cmonth (nth 4 tm))
         (cyear (nth 5 tm))
         (nbmonths (+ (* 12 (- cyear starty)) (- cmonth startm))))
    (calc-eval "round($ / $$, 2)" nil amount nbmonths))
#+end_src

#+name: ledger_average
#+begin_src emacs-lisp :var b="Quotidien" :var sy=2014 :var sm=3  :var sd="2014-03-01" :var p="Expenses:"
  (let* ((per (format "from %d-%d-01 to this month" sy sm))
         (a (org-sbe call_ledger (bucket (eval b)) (prefix (eval p)) (period (eval per)))))
    (org-sbe monthly_average (amount (eval a)) (starty (eval sy)) (startm (eval sm))))
#+end_src

#+name: ledger_budget
#+BEGIN_SRC emacs-lisp :results output :var table=budget :var year=2014 :var month=04
  (princ (format "%d-%02d-01 * Budget %d %02d\n" year month year month))
  (mapcar
   (lambda (tuple)
     (princ (format "    Bucket:Expenses:%s    %d €\n" (car tuple) (cadr tuple))))
   (butlast (cdr table) 1))
  (princ "    Bucket:Unallocated:EUR\n")
#+END_SRC
#+end_src

The table looks like this now

#+begin_src org
#+name: budget
| Bucket | Planned | Remaining | This Month | Last Month | Average |
|--------+---------+-----------+------------+------------+---------|
| Name   | …       | …         | …          | …          | …       |
|--------+---------+-----------+------------+------------+---------|
| Total  | …       | …         | …          | …          | …       |
#+TBLFM: @2$3..@>>$3='(org-sbe call_ledger (bucket (concat "\"" $1 "\"")) (prefix "\"Bucket:Expenses:\""))
#+TBLFM: @2$4..@>>$4='(org-sbe call_ledger (bucket (concat "\"" $1 "\"")) (period "\"this month\""))
#+TBLFM: @2$5..@>>$5='(org-sbe call_ledger (bucket (concat "\"" $1 "\"")) (period "\"last month\""))
#+TBLFM: @2$6..@>>$6='(org-sbe ledger_average (b (concat "\"" $1 "\"")))
#+TBLFM: @>$2..@>$6=vsum(@2..@-1)
#+end_src

Best,

Alan

-- 
OpenPGP Key ID : 040D0A3B4ED2E5C7