0

I'd like to have a clocktable report which groups TODOs by their CATEGORY property instead of by file. As an example, this is my current clocktable report:

#+BEGIN: clocktable :scope cashpw/org-clock--agenda-with-archives :block "2023-11-13" :narrow 100 :fileskip0 t :filetitle t :properties ("CATEGORY")
#+CAPTION: Clock summary at [2023-11-13 Mon 20:04], for Monday, November 13, 2023.
|                          |                | <100>                                     |        |
| File                     | CATEGORY       | Headline                                  |   Time |
|--------------------------+----------------+-------------------------------------------+--------|
|                          | ALL            | *Total time*                              | *4:10* |
|--------------------------+----------------+-------------------------------------------+--------|
| Personal TODOs           |                | *File time*                               | *4:01* |
|                          | Personal TODOs | Personal email                            |   0:01 |
|                          | Study          | Flashcards                                |   0:23 |
|                          | Food           | Lunch                                     |   0:20 |
|                          | Food           | Dinner                                    |   0:20 |
|                          | Fitness        | Walk (evening)                            |   0:47 |
|                          | Fitness        | Stretch 1                                 |   0:16 |
|                          | Fitness        | Stretch 2                                 |   0:14 |
|                          | Health         | Brush teeth (morning)                     |   0:03 |
|                          | Fitness        | Shoulders                                 |   1:09 |
|--------------------------+----------------+-------------------------------------------+--------|
| Personal TODOs (archive) |                | *File time*                               | *0:09* |
|                          | Personal TODOs | Confirm mortgage payment went through     |   0:03 |
|                          | Personal TODOs | Confirm credit card payments went through |   0:04 |
#+END:

I'd like to group the items by category --- ignoring file boundaries --- like this:

#+BEGIN: clocktable :scope cashpw/org-clock--agenda-with-archives :block "2023-11-13" :narrow 100 :fileskip0 t :filetitle t :properties ("CATEGORY")
#+CAPTION: Clock summary at [2023-11-13 Mon 20:04], for Monday, November 13, 2023.
|                | <100>                                     |        |
| Category       | Headline                                  |   Time |
|----------------+-------------------------------------------+--------|
|                | *Total time*                              | *4:10* |
|----------------+-------------------------------------------+--------|
| Personal TODOs | *Category time*                           | *X:XX* |
|                | Personal email                            |   0:01 |
|                | Confirm mortgage payment went through     |   0:03 |
|                | Confirm credit card payments went through |   0:04 |
|----------------+-------------------------------------------+--------|
| Study          | *Category time*                           | *X:XX* |
|                | Flashcards                                |   0:23 |
|----------------+-------------------------------------------+--------|
| Food           | *Category time*                           | *X:XX* |
|                | Lunch                                     |   0:20 |
|                | Dinner                                    |   0:20 |
|----------------+-------------------------------------------+--------|
| Fitness        | *Category time*                           | *X:XX* |
|                | Walk (evening)                            |   0:47 |
|                | Stretch 1                                 |   0:16 |
|                | Stretch 2                                 |   0:14 |
|                | Shoulders                                 |   1:09 |
|----------------+-------------------------------------------+--------|
| Health         | *Category time*                           | *X:XX* |
|                | Brush teeth (morning)                     |   0:03 |
|----------------+-------------------------------------------+--------|
#+END:

What I've tried so far

  1. Reviewed the docs

  2. private/clocktable-formatter-group-by-prop

    Unfortunately this solution collapses the "Headline" column.

    #+BEGIN: clocktable :scope cashpw/org-clock--agenda-with-archives :block "2023-11-13" :narrow 200 :fileskip0 t :filetitle t :properties ("CATEGORY") :formatter cashpw/clocktable-formatter-group-by-prop :hidefiles t
    #+CAPTION: Clock summary at [2023-11-13 Mon 20:12], for Monday, November 13, 2023.
    | <200>          |        |
    | Headline       |   Time |
    |----------------+--------|
    | *Total time*   | *4:10* |
    |----------------+--------|
    | Fitness        |   2:26 |
    | Food           |   0:27 |
    | Health         |   0:03 |
    | Personal TODOs |   0:45 |
    | Pet            |   0:06 |
    | Study          |   0:23 |
    #+END:
    
  3. org-dblock-write:clocktable-by-tag

    This is the most promising. I'll need to fix up the functions since :tags changed and also switch from checking tags to checking categories.

1 Answer 1

2

I've extended ffevotte's clocktable-by-tag gist and published it as a repo on Github: cashpw/clocktable-by-tag. I've included the full script below in case this link ever expires.

An example of the clocktable this generates:

#+BEGIN: clocktable-by-tag :files-fn cashpw/agenda-with-archives :block "2023-11-13" :maxlevel 5
|         |                                           |      <r> |      |
| Tag     | Headline                                  | Time     |      |
|---------+-------------------------------------------+----------+------|
|         | All *Total time*                          |   *4:13* |      |
|---------+-------------------------------------------+----------+------|
| finance | *Tag time*                                |   *0:07* |      |
|         | Confirm mortgage payment went through     |          | 0:03 |
|         | Confirm credit card payments went through |          | 0:04 |
|---------+-------------------------------------------+----------+------|
| health  | *Tag time*                                |   *1:42* |      |
|         | Stretch 1                                 |          | 0:16 |
|         | Stretch 2                                 |          | 0:14 |
|         | Brush teeth (morning)                     |          | 0:03 |
|         | Shoulders                                 |          | 1:09 |
|---------+-------------------------------------------+----------+------|
| study   | *Tag time*                                |   *0:23* |      |
|         | Flashcards                                |          | 0:23 |
|---------+-------------------------------------------+----------+------|
| nil     | *Tag time*                                |   *0:48* |      |
|         | Personal email                            |          | 0:01 |
|         | Walk (evening)                            |          | 0:47 |
#+END:
(require 'org-clock)
(require 's)

(defun clocktable-by-tag--shift-cell (n)
  (let ((str ""))
    (dotimes (i n)
      (setq str (concat str "| ")))
    str))

(defun clocktable-by-tag--get-clock-data (files params)
  "Return a list of all clock table data entries from FILES.

- PARAMS is a plist; see `org-clock-get-table-data'"
  (seq-reduce (lambda (all-clock-data file)
                (let* ((clock-data (with-current-buffer (find-file-noselect file)
                                     (org-clock-get-table-data (buffer-name)
                                                               params))))
                  (cl-destructuring-bind (_ total-minutes entries) clock-data
                    (if (> total-minutes 0)
                        (append all-clock-data
                                entries)
                      all-clock-data)
                    )))
              files
              '()))

(defun clocktable-by-tag--insert-row (tag entries)
  "Insert a row of ENTRIES for TAG.

- ENTRIES: List of entries with TAG; see `org-clock-get-table-data'"
  (insert "|--\n")
  (insert (s-lex-format "| ${tag} | *Tag time* |\n"))
  (let ((total 0))
    (dolist (entry entries)
      (cl-destructuring-bind (level headline _ _ minutes _) entry
        (setq total (+ total minutes))
        (let ((indent (org-clocktable-indent-string level))
              (shift-cell (clocktable-by-tag--shift-cell level))
              (duration (org-duration-from-minutes minutes)))
          (insert (s-lex-format "| |${indent}${headline} | ${shift-cell} ${duration} |\n")))))
    (save-excursion
      (let ((duration (org-duration-from-minutes total)))
        (re-search-backward "*Tag time*")
        (org-table-next-field)
        (org-table-blank-field)
        (insert (s-lex-format "*${duration}*")))))
  (org-table-align))

(defun clocktable-by-tag--get-tags (clock-data-entries)
  "Return unique list of tags within CLOCK-DATA-ENTRIES.

This function expects output in the form of a list of entries from `org-clock-get-table-data'."
  (seq-uniq
   (seq-reduce
    (lambda (tags-with-duplicates entry)
      (append tags-with-duplicates
              (nth 2 entry)))
    clock-data-entries
    '())))

(defun clocktable-by-tag--get-entries-by-tag-hash (entries)
  "Build a hash table of ENTRIES indexed by their first tag."
  (let ((entry-hash (make-hash-table :test 'equal)))
    (dolist (entry entries)
      (let* ((tags (nth 2 entry))
             (tag (nth 0 tags))
             (entries (gethash tag
                               entry-hash)))
        (puthash tag
                 (append entries
                         `(,entry))
                 entry-hash)))
    entry-hash))

(defun clocktable-by-tag--sum-durations (clock-data)
  "Return the total minutes logged for all entries in CLOCK-DATA."
  (seq-reduce (lambda (total-minutes entry)
                (cl-destructuring-bind (_ _ _ _ minutes _) entry
                  (setq total-minutes (+ total-minutes minutes))))
              clock-data
              0))

(defun clocktable-by-tag--get-files (params)
  "Return list of files from which to construct clocktable.

See `org-dblock-write:clocktable' for information on PARAMS.

Users can provide files in two ways:

1. ':files': A list file paths
2. ':files-fn': A function which is called without arguments and should return a list of file paths

If both are provided, ':files' is used."
  (let ((files-fn (plist-get params :files-fn))
        (files (plist-get params :files)))
    (when (and (not files-fn)
               (not files))
      (error "ERROR [clocktable-by-tag] You must provide either :files-fn or :files as parameters."))
    (or files
        (funcall files-fn))))

(defun clocktable-by-tag--insert-table-headings ()
  "Insert the initial table headings."
  (insert "| | | <r> |\n")
  (insert "| Tag | Headline | Time |\n")
  (insert "|--\n")
  (insert "| | All *Total time* | \n"))

(defun org-dblock-write:clocktable-by-tag (params)
  "Create a clocktable grouped by tags. Only look at first tag on each headline.

- PARAMS: See `org-dblock-write:clocktable'"
  (clocktable-by-tag--insert-table-headings)
  ;; We can't sort by tags unless we collect the tags
  (plist-put params :tags t)
  (let* ((files (clocktable-by-tag--get-files params))
         (clock-data (clocktable-by-tag--get-clock-data files
                                                        params))
         (entries-hash (clocktable-by-tag--get-entries-by-tag-hash clock-data))
         (tags (hash-table-keys entries-hash)))
    (dolist (tag tags)
      (clocktable-by-tag--insert-row tag
                                     (gethash tag entries-hash)))
    (save-excursion
      (let ((duration (org-duration-from-minutes (clocktable-by-tag--sum-durations clock-data))))
        (re-search-backward "*Total time*")
        (org-table-next-field)
        (org-table-blank-field)
        (insert (s-lex-format "*${duration}*"))
        (org-table-align)))))
2
  • I've extended the answer to include the source code and a usage example.
    – cashpw
    Commented Nov 15, 2023 at 4:10
  • Thank you. That will help others.
    – Drew
    Commented Nov 15, 2023 at 4:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.