Here is a bit more information to clarify what type of reports we want to be able to generate. Most of it is based on what Antzi already wrote, this document only extends it.

IT IS IN NO WAY COMPLETE, but a draft implementation is working (outside CVS). This draft implementaion is only analysis for now.

Examples

1- Time sheet

 List activities (time start, type activity, description, time spent) for Author X in month Y.
 Show a column total of time spent
 Filters: Author ID, Date interval

Example report result:

Time start	Activity	Description	Time spent
9h00	Consultation	...	1h
10h00	Reading	Researched law concerning xyz	2h
13h00	Travel	Met with client	1h
14h00	Consultation	Talked with client	2h
16h00	Travel	Went back to office	1h
17h00	Other	Reported my activities	30m
Total	---	---	7h30

2- Activity types

 List the hours spent on each type of activities for each author in month X.
 Show a line total for sum of time spent
 Possibly, show only for a specific case
 Filters: Date interval, Case ID (optional)

Example report result:

Author name	Reading	Interview	Travel	Other	Total
Alice	3h45	5h15	4h00	1h00	14h00
Bob	3h30	4h00	2h00	2h00	11h30

2b- Average activity types

 How many hours are spent in average for each activity types for all cases in month X?
 Filters: Date interval

Example report result:

Author name	Reading	Interview	Travel	Other	Total
Alice	2h00	3h15	2h00	1h00	8h15
Bob	1h30	2h00	1h00	2h00	6h00

3- Monthly case overview by author

 Show how many cases were opened, closed and appealed, for each author, in month X.
 Show total for each column (either average or sum).
 Filters: Date interval

Example report result:

Author name	#cases opened	#cases closed	#appeals
Alice	5	4	1
Bob	4	2	0

4- Case activity for each stage

 Show how time was spent for each case stage.
 Show sum of hours for each row
 Show sum of hours for time column
 Filters: Case ID, Author ID (optional)

Example report result:

Stage	Reading	Interview	Travel	Other	Total
Investigation	1h	10h	2h	1h	14h
Pre-trial	3h	2h	1h	0h	6h
Trial	4h	2h	1h	1h	8h
Appeal	2h	0h30	1h	2h	5h30
Total	—	--	—	--	33h30

---

Implementation

The report is a 2 dimensional matrix. It can combine one or two tables. This still isn’t clear at all, how to automate, so let’s go through the examples above and see how we a user could create such reports.

1- Time sheet

Time start	Type	Description	Time spent
9h00	Consultation	...	1h
10h00	Reading	Researched law concerning xyz	2h
13h00	Travel	Met with client	1h
14h00	Consultation	Talked with client	2h
16h00	Travel	Went back to office	1h
17h00	Other	Reported my activities	30m
Total	---	---	7h30

The user can select the fields from the "Follow-up" table, sort using the "time start" column, and filter using the Author’s Name/ID.

Another way to do it would be to select "Follow-up - Time Start" for the matrix line, then the other fields for the matrix columns. This is a lazy way to apply the sort on "time start".

Since I want to explain how 2D reporting works, let’s put it this way:

 Line:

• Source: lcm_followup
• Fields: date_start

 Columns:

• Source: lcm_followup
• Fields: type, description, date_end - date_start

2- Activity types

Author name	Reading	Interview	Travel	Other	Total
Alice	3h45	5h15	4h00	1h00	14h00
Bob	3h30	4h00	2h00	2h00	11h30

 Line:

• Source: lcm_author
• Fields: ID, name_first, name_middle, name_last

 Columns:

• Source: lcm_followup
• Fields: foreach(followup_type) sum(date_end - date_start)

To implement this, we can add an "enum_type" on lcm_field to indicate the source of the enumeration. In this case, the source of the enumeration is the system keyword "followups". The above "Columns" therefore becomes:

The report then loops through the follow-up types and prints the sum.

It’s still a bit tricky to indicate that we want the sum, not the avg() or the count(). One possibility is to add a column in lcm_field for the "action" on the enum_type.

 Columns:

• Source: lcm_followup
• Fields: date_end - date_start with action "sum"

3- Monthly case overview by author

Author name	#cases opened	#cases closed	#appeals
Alice	5	4	1
Bob	4	2	0

 Line:

• Source: lcm_author
• Fields: ID, name_first, name_middle, name_last

 Columns:

• Source: lcm_followup
• Fields: sum(type = ’open’), sum(type = ’closed’), sum(type = ’appeal’)

The problem with this is that it assumes that only one author has opened, closed or appealed the case.

4- Case activity for each stage

Stage	Reading	Interview	Travel	Other	Total
Investigation	1h	10h	2h	1h	14h
Pre-trial	3h	2h	1h	0h	6h
Trial	4h	2h	1h	1h	8h
Appeal	2h	0h30	1h	2h	5h30
Total	—	--	—	--	33h30

 Line:

• Source: lcm_followup
• Field: stage

 Columns:

• Source: lcm_followup
• Field: type with action sum()

---

Of course, the biggest mess is to join the tables correctly, filter, etc.

In fact, we should provide filters for specific tables. For example:

 If lcm_author is "line", then expect that the user will want to filter by some column of "lcm_author" (name, type of author, ...)

 If lcm_followup is "line" or "column", expect that the user will want to filter by "date_start" or "date_end". Or even, in more advanced reporting, to list activities grouped by month (but that’s a luxury, since running the report many times for different periods will do the same thing.