Timetable grouped by tag

Question

I'm looking for a way to use time tracking information from org-mode agenda files to build a timetable. Time spent in different tasks would be indicated, grouped by tag and restricted to a given time-frame, sort of like a clocktable dynamic block, but grouped by tag instead of file/category/headline.

For example, if my agenda contained the following data (possibly scattered among different files):

* TODO project 1
** TODO task 1                                                  :tag1:
   :LOGBOOK:
   CLOCK: [2013-06-27 Thu 18:00]--[2013-06-27 Thu 19:04] =>  1:04
   CLOCK: [2013-06-26 Wed 17:00]--[2013-06-26 Wed 17:32] =>  0:32
   :END:
** TODO task 2                                                  :tag2:
   :LOGBOOK:
   CLOCK: [2013-06-27 Thu 17:00]--[2013-06-27 Thu 18:00] =>  1:00
   CLOCK: [2013-06-27 Thu 15:00]--[2013-06-27 Thu 15:50] =>  0:50
   :END:

* TODO project 2                                                :tag2:
  :LOGBOOK:
  CLOCK: [2013-06-27 Thu 19:04]--[2013-06-27 Thu 21:00] =>  1:56
  CLOCK: [2013-06-27 Thu 15:50]--[2013-06-27 Thu 17:00] =>  1:10
  :END:

I would like to get this kind of results:

#+BEGIN: clocktable-by-tag :maxlevel 2 :tags ("p1" "p2") :tstart "2013-06-27" :tend "2013-06-28"
| Tag  | Headline        | Time   |      |
|------+-----------------+--------+------|
| tag1 | *Tag time*      | *1:04* |      |
|      | TODO project 1  | 1:04   |      |
|      | \__ TODO task 1 |        | 1:04 |
|------+-----------------+--------+------|
| tag2 | *Tag time*      | *4:56* |      |
|      | TODO project 1  | 1:50   |      |
|      | \__ TODO task 2 |        | 1:50 |
|      | TODO project 2  | 3:06   |      |
#+END:

Is there any standard way to do this with org? If not, I'm thinking of cycling through agenda files and tags, using org-get-table-data to collect clocking information; would there be another, more efficient way of doing things?

Answer 1

Here is what I've come to: https://gist.github.com/ffevotte/5899058

It's not very optimized, but seems to get the job done and should support most of the clocktable dynamic block arguments.

Sample (anonymized) output on my real org agenda files:

#+BEGIN: clocktable-by-tag :tags ("p_f3c" "p_sc") :tstart "2013-01-01" :tend "2013-05-19" :maxlevel 2
| Tag   | Headline                     | Time (h) |       |      |
|       |                              |      <r> |       |      |
|-------+------------------------------+----------+-------+------|
| p_f3c | *Tag time*                   |  *18.42* |       |      |
|       | File *xxx.org*               |    18.42 |       |      |
|       | . xxxxxxxxxxxxxxxxxxx        |          | 13.03 |      |
|       | . \__ xxxxxxxxxxxxx          |          |       | 7.78 |
|       | . \__ xxxxxxxxxxxxxxxxxxxxx  |          |       | 3.98 |
|       | . xxxxxxxxxxxxxxxxxx         |          |  5.38 |      |
|       | . \__ xxxxxxxxxxxxxxx        |          |       | 5.38 |
|-------+------------------------------+----------+-------+------|
| p_sc  | *Tag time*                   |  *18.90* |       |      |
|       | File *yyyy.org*              |     4.42 |       |      |
|       | . xxxxxxxxxxxxxxxxxxxxxx     |          |  2.83 |      |
|       | . xxxxxxxxxxxxxxxxxx         |          |  1.58 |      |
|       | . \__ xxxxxxxxxxxxxxxxxxxxxx |          |       | 1.58 |
|       | File *todo.org*              |    14.48 |       |      |
|       | . xxxxxxxxxxxxxxxx           |          | 14.48 |      |
|       | . \__ xxxxxxxxxxx            |          |       | 2.00 |
|       | . \__ xxxxxxxxxxxxx          |          |       | 8.48 |
|       | . \__ xxxxx                  |          |       | 4.00 |
#+END:

Answer 2

I have come up with an improvement of this solution that allows for a :summary t option and better formatting of the hours (using org-duration-from-minutes). You can find it in this gist. The result with summarising enabled looks like this:

  #+BEGIN: clocktable-by-tag :tags ("work" "client1") :summary t
  | Tag     | Headline   | Time (h) |
  |---------+------------+----------|
  | work    | *Tag time* |     1:29 |
  |---------+------------+----------|
  | client1 | *Tag time* |     0:45 |
  
  #+END:

Note: I came up with this solution while answering a similar question.