Re: [O] org-collector calculated rows?

[Nick Dokos]

Rehan Iftikhar <address@hidden> wrote:

> Hi Org list!
> 
> For those of you familiar with org-collector (ie. propview dynamic
> block) is there already a way to include a calculated row?
> 
> For example, in the demonstration of how to use org-collector @
> http://orgmode.org/worg/org-contrib/org-collector.html, I'd be
> interested in:
>   * Simple Example: displaying the sum of the "Amount" column
>   * More Complex Example: displaying the mean of the "Amount" column
> for each week.
> 
> Does anything like this already exist?
> 

You can do that, but you have to take some care. The following uses
formula syntax from very recent org-mode (e.g.  the last row, last
column designation @>$> does not work in older versions and you'd have
to resort to the old, deprecated notation $LR2) but other than that it
should work with no problems in org-mode 7.x say (and probably older
versions as well).

I can add a formula to each of the tables to calculate
the sum in the first case and the mean in the second case.
Adding an item and then recalculating the tables by pressing
C-c C-c on each #+BEGIN line works fine.

The thing that does not quite work is initialization: you cannot start
with empty #+BEGIN/#+END sections, add the formula and then press C-c
C-c: that gives an error[fn:1]. But pressing C-c C-c first, thereby
populating the table, and *then* adding the formula works fine. C-c C-c
will not only repopulate the table but also recalculate the formula.

HTH,
Nick


--8<---------------cut here---------------start------------->8---

* Example

#+BEGIN: propview :id "december" :conds ((string= spendtype "food")) :cols 
(ITEM amount)
| "ITEM"                           | "amount" |
|----------------------------------+----------|
| "Grocery Store [2008-12-01 Mon]" |    56.77 |
| "Restaurant [2008-12-08 Mon]"    |    30.67 |
| "Grocery Store [2008-12-09 Tue]" |    56.76 |
|----------------------------------+----------|
|                                  |    144.2 |
#+TBLFM: @>$> = vsum(@I..II)  
#+END:

#+BEGIN: propview :cols (ITEM (+ 400 amount)) :scope tree :match "example"
| "ITEM"                           | "(+ 400 amount)" |
|----------------------------------+------------------|
| "December Spending"              |                0 |
| "Week One"                       |                0 |
| "Grocery Store [2008-12-01 Mon]" |           456.77 |
| "Athletic club [2008-12-02 Tue]" |            475.0 |
| "Week Two "                      |                0 |
| "Restaurant [2008-12-08 Mon]"    |           430.67 |
| "Grocery Store [2008-12-09 Tue]" |           456.76 |
|----------------------------------+------------------|
|                                  |        259.88571 |
#+TBLFM: @>$> = vmean(@I..II)
#+END:

** December Spending                                                   :example:
   :PROPERTIES:
   :ID:       december
   :END:

*** Week One
**** Grocery Store [2008-12-01 Mon]
     :PROPERTIES:
     :amount: 56.77
     :spendtype: food
     :END:
**** Athletic club [2008-12-02 Tue]
     :PROPERTIES:
     :amount: 75.00
     :spendtype: health
     :END:
*** Week Two 
**** Restaurant [2008-12-08 Mon]
     :PROPERTIES:
     :amount: 30.67
     :spendtype: food
     :END:
**** Grocery Store [2008-12-09 Tue]
     :PROPERTIES:
     :amount: 56.76
     :spendtype: food
     :END:
--8<---------------cut here---------------end--------------->8---

Footnotes:
[fn:1] The error I get is

Debugger entered--Lisp error: (error "(wrong-type-argument stringp nil)")
  signal(error ("(wrong-type-argument stringp nil)"))
  error("%s" (wrong-type-argument stringp nil))
  (condition-case er (let ((cols (plist-get params :cols)) (inherit (plist-get 
params :inherit)) (conds (plist-get params :conds)) (match (plist-get params 
:match)) (scope (plist-get params :scope)) (content-lines (org-split-string 
(plist-get params :content) "\n")) id table line pos) (save-excursion (when 
(setq id (plist-get params :id)) (cond ((not id) nil) ((eq id (quote global)) 
(goto-char (point-min))) ((eq id (quote local)) nil) ((setq idpos 
(org-find-entry-with-id id)) (goto-char idpos)) (t (error "Cannot find entry 
with :ID: %s" id)))) (org-narrow-to-subtree) (setq table (org-propview-to-table 
(org-propview-collect cols conds match scope inherit))) (widen)) (setq pos 
(point)) (when content-lines (while (string-match "^#" (car content-lines)) 
(insert (pop content-lines) "\n"))) (insert table) (insert "\n|--") (org-cycle) 
(move-end-of-line 1) (message (format "point-%d" pos)) (while (setq line (pop 
content-lines)) (when (string-match "^#" line) (insert "\n" line))) (got
 o-char pos) (org-table-recalculate (quote all))) (org-collector-error (widen) 
(error "%s" er)) (error (widen) (error "%s" er)))
  org-dblock-write:propview((:name "propview" :id "december" :conds ((string= 
spendtype "food")) :cols (ITEM amount) :indentation-column 0 :content 
#("#+TBLFM: @>$> = vsum(@I..II)  \n" 0 29 (font-lock-fontified t org-category 
"collector" fontified t face org-meta-line) 29 30 (font-lock-fontified t 
org-category "collector" fontified t rear-nonsticky t face org-meta-line) 30 31 
(fontified t font-lock-fontified t face org-meta-line))))
  funcall(org-dblock-write:propview (:name "propview" :id "december" :conds 
((string= spendtype "food")) :cols (ITEM amount) :indentation-column 0 :content 
#("#+TBLFM: @>$> = vsum(@I..II)  \n" 0 29 (font-lock-fontified t org-category 
"collector" fontified t face org-meta-line) 29 30 (font-lock-fontified t 
org-category "collector" fontified t rear-nonsticky t face org-meta-line) 30 31 
(fontified t font-lock-fontified t face org-meta-line))))
  (let* ((pos (point)) (line (org-current-line)) (params (org-prepare-dblock)) 
(name (plist-get params :name)) (indent (plist-get params :indentation-column)) 
(cmd (intern (concat "org-dblock-write:" name)))) (message "Updating dynamic 
block `%s' at line %d..." name line) (funcall cmd params) (message "Updating 
dynamic block `%s' at line %d...done" name line) (goto-char pos) (when (and 
indent (> indent 0)) (setq indent (make-string indent 32)) (save-excursion 
(org-beginning-of-dblock) (forward-line 1) (while (not (looking-at 
org-dblock-end-re)) (insert indent) (beginning-of-line 2)) (when (looking-at 
org-dblock-end-re) (and (looking-at "[  ]+") (replace-match "")) (insert 
indent)))))
  (save-window-excursion (let* ((pos (point)) (line (org-current-line)) (params 
(org-prepare-dblock)) (name (plist-get params :name)) (indent (plist-get params 
:indentation-column)) (cmd (intern (concat "org-dblock-write:" name)))) 
(message "Updating dynamic block `%s' at line %d..." name line) (funcall cmd 
params) (message "Updating dynamic block `%s' at line %d...done" name line) 
(goto-char pos) (when (and indent (> indent 0)) (setq indent (make-string 
indent 32)) (save-excursion (org-beginning-of-dblock) (forward-line 1) (while 
(not (looking-at org-dblock-end-re)) (insert indent) (beginning-of-line 2)) 
(when (looking-at org-dblock-end-re) (and (looking-at "[   ]+") (replace-match 
"")) (insert indent))))))
  org-update-dblock()
  (save-excursion (org-update-dblock))
  (cond ((or (and (boundp (quote org-clock-overlays)) org-clock-overlays) 
org-occur-highlights org-latex-fragment-image-overlays) (and (boundp (quote 
org-clock-overlays)) (org-clock-remove-overlays)) (org-remove-occur-highlights) 
(org-remove-latex-fragment-image-overlays) (message "Temporary 
highlights/overlays removed from current buffer")) ((and (local-variable-p 
(quote org-finish-function) (current-buffer)) (fboundp org-finish-function)) 
(funcall org-finish-function)) ((run-hook-with-args-until-success (quote 
org-ctrl-c-ctrl-c-hook))) ((or (looking-at org-property-start-re) 
(org-at-property-p)) (call-interactively (quote org-property-action))) 
((org-on-target-p) (call-interactively (quote org-update-radio-target-regexp))) 
((and (org-in-regexp "\\[\\([0-9]*%\\|[0-9]*/[0-9]*\\)\\]") (or 
(org-on-heading-p) (org-at-item-p))) (call-interactively (quote 
org-update-statistics-cookies))) ((org-on-heading-p) (call-interactively (quote 
org-set-tags))) ((org-at-table\.el-p) (message 
 "Use C-c ' to edit table.el tables")) ((org-at-table-p) 
(org-table-maybe-eval-formula) (if arg (call-interactively (quote 
org-table-recalculate)) (org-table-maybe-recalculate-line)) (call-interactively 
(quote org-table-align)) (orgtbl-send-table (quote maybe))) ((or 
(org-footnote-at-reference-p) (org-footnote-at-definition-p)) 
(call-interactively (quote org-footnote-action))) ((org-at-item-checkbox-p) 
(let* ((cbox (match-string 1)) (struct (org-list-struct)) (old-struct 
(copy-tree struct)) (parents (org-list-parents-alist struct)) (prevs 
(org-list-prevs-alist struct)) (orderedp (org-entry-get nil "ORDERED")) (firstp 
(= (org-list-get-top-point struct) (point-at-bol))) block-item) 
(org-list-set-checkbox (point-at-bol) struct (cond ((equal arg (quote ...)) 
"[-]") ((equal arg (quote ...)) nil) ((equal "[ ]" cbox) "[X]") (t "[ ]"))) 
(org-list-struct-fix-ind struct parents) (org-list-struct-fix-bul struct prevs) 
(setq block-item (org-list-struct-fix-box struct parents prevs ordere
 dp)) (when block-item (message "Checkboxes were removed due to unchecked box 
at line %d" (org-current-line block-item))) (org-list-struct-apply-struct 
struct old-struct) (org-update-checkbox-count-maybe) (when firstp 
(org-list-send-list (quote maybe))))) ((org-at-item-p) (let* ((struct 
(org-list-struct)) (old-struct (copy-tree struct)) (parents 
(org-list-parents-alist struct)) (prevs (org-list-prevs-alist struct)) (firstp 
(= (org-list-get-top-point struct) (point-at-bol)))) (org-list-struct-fix-ind 
struct parents) (org-list-struct-fix-bul struct prevs) (when arg 
(org-list-set-checkbox (point-at-bol) struct "[ ]") (org-list-struct-fix-box 
struct parents prevs)) (org-list-struct-apply-struct struct old-struct) (when 
arg (org-update-checkbox-count-maybe)) (when firstp (org-list-send-list (quote 
maybe))))) ((save-excursion (beginning-of-line 1) (looking-at 
org-dblock-start-re)) (beginning-of-line 1) (save-excursion 
(org-update-dblock))) ((save-excursion (beginning-of-line 1) (lo
 oking-at "[    ]*#\\+\\([A-Z]+\\)")) (cond ((equal (match-string 1) "TBLFM") 
(save-excursion (beginning-of-line 1) (skip-chars-backward " 
\n      ") (if (org-at-table-p) (org-call-with-arg (quote 
org-table-recalculate) (or arg t))))) (t (let 
((org-inhibit-startup-visibility-stuff t) (org-startup-align-all-tables nil)) 
(org-save-outline-visibility (quote use-markers) (org-mode-restart))) (message 
"Local setup has been refreshed")))) ((org-clock-update-time-maybe)) (t (error 
"C-c C-c can do nothing useful at this location")))
  (let ((org-enable-table-editor t)) (cond ((or (and (boundp (quote 
org-clock-overlays)) org-clock-overlays) org-occur-highlights 
org-latex-fragment-image-overlays) (and (boundp (quote org-clock-overlays)) 
(org-clock-remove-overlays)) (org-remove-occur-highlights) 
(org-remove-latex-fragment-image-overlays) (message "Temporary 
highlights/overlays removed from current buffer")) ((and (local-variable-p 
(quote org-finish-function) (current-buffer)) (fboundp org-finish-function)) 
(funcall org-finish-function)) ((run-hook-with-args-until-success (quote 
org-ctrl-c-ctrl-c-hook))) ((or (looking-at org-property-start-re) 
(org-at-property-p)) (call-interactively (quote org-property-action))) 
((org-on-target-p) (call-interactively (quote org-update-radio-target-regexp))) 
((and (org-in-regexp "\\[\\([0-9]*%\\|[0-9]*/[0-9]*\\)\\]") (or 
(org-on-heading-p) (org-at-item-p))) (call-interactively (quote 
org-update-statistics-cookies))) ((org-on-heading-p) (call-interactively (quote 
org-set-tags
 ))) ((org-at-table\.el-p) (message "Use C-c ' to edit table.el tables")) 
((org-at-table-p) (org-table-maybe-eval-formula) (if arg (call-interactively 
(quote org-table-recalculate)) (org-table-maybe-recalculate-line)) 
(call-interactively (quote org-table-align)) (orgtbl-send-table (quote maybe))) 
((or (org-footnote-at-reference-p) (org-footnote-at-definition-p)) 
(call-interactively (quote org-footnote-action))) ((org-at-item-checkbox-p) 
(let* ((cbox (match-string 1)) (struct (org-list-struct)) (old-struct 
(copy-tree struct)) (parents (org-list-parents-alist struct)) (prevs 
(org-list-prevs-alist struct)) (orderedp (org-entry-get nil "ORDERED")) (firstp 
(= (org-list-get-top-point struct) (point-at-bol))) block-item) 
(org-list-set-checkbox (point-at-bol) struct (cond ((equal arg ...) "[-]") 
((equal arg ...) nil) ((equal "[ ]" cbox) "[X]") (t "[ ]"))) 
(org-list-struct-fix-ind struct parents) (org-list-struct-fix-bul struct prevs) 
(setq block-item (org-list-struct-fix-box struct p
 arents prevs orderedp)) (when block-item (message "Checkboxes were removed due 
to unchecked box at line %d" (org-current-line block-item))) 
(org-list-struct-apply-struct struct old-struct) 
(org-update-checkbox-count-maybe) (when firstp (org-list-send-list (quote 
maybe))))) ((org-at-item-p) (let* ((struct (org-list-struct)) (old-struct 
(copy-tree struct)) (parents (org-list-parents-alist struct)) (prevs 
(org-list-prevs-alist struct)) (firstp (= (org-list-get-top-point struct) 
(point-at-bol)))) (org-list-struct-fix-ind struct parents) 
(org-list-struct-fix-bul struct prevs) (when arg (org-list-set-checkbox 
(point-at-bol) struct "[ ]") (org-list-struct-fix-box struct parents prevs)) 
(org-list-struct-apply-struct struct old-struct) (when arg 
(org-update-checkbox-count-maybe)) (when firstp (org-list-send-list (quote 
maybe))))) ((save-excursion (beginning-of-line 1) (looking-at 
org-dblock-start-re)) (beginning-of-line 1) (save-excursion 
(org-update-dblock))) ((save-excursion (begin
 ning-of-line 1) (looking-at "[         ]*#\\+\\([A-Z]+\\)")) (cond ((equal 
(match-string 1) "TBLFM") (save-excursion (beginning-of-line 1) 
(skip-chars-backward " 
\n      ") (if (org-at-table-p) (org-call-with-arg ... ...)))) (t (let 
((org-inhibit-startup-visibility-stuff t) (org-startup-align-all-tables nil)) 
(org-save-outline-visibility (quote use-markers) (org-mode-restart))) (message 
"Local setup has been refreshed")))) ((org-clock-update-time-maybe)) (t (error 
"C-c C-c can do nothing useful at this location"))))
  org-ctrl-c-ctrl-c(nil)
  call-interactively(org-ctrl-c-ctrl-c nil nil)