S3Report

S3Report is a REST method handler for data analysis.

Functionality

S3Report generates a full page which includes a set of S3Search widgets, a pivot table (a.k.a. contingency table) from S3Resources and graphs based on the Pivot Tables.

Caveats

S3Report supports grouping and aggregation by virtual fields - however, using this without care can lead to severe scalability problems:

With large numbers of records involved it can take a long time to compute all virtual field values - and especially where this involves additional database lookups it may quickly exhaust any available bandwidth. Example calculation:

Records in the table	Number of virtual fields with DB lookups	Concurrent users	Total number of queries
1,000	1	1	1,001
1,000	5	1	5,001
30,000	5	1	150,001
30,000	5	5	750,005
30,000	5	100	15,000,100

Unless you're running on a super-computer, such a scenario would take your site beyond any reasonable response times - and users may end up waiting forever to get their report (make sure your site has a reasonable timeout for requests).

Without virtual fields, you have a very different scenario:

Records in the table	Number of virtual fields with DB lookups	Concurrent users	Total number of queries
1.000	0	1	1
30,000	0	100	100

Therefore, it is strongly recommended to minimize (better: avoid) the use of virtual fields which include additional DB lookup as rows, cols or fact parameter in reports wherever large numbers of records can be expected.

URL Methods

S3Report responds to the /report URL method for all resources (generic method).

The following parameters are accepted:

Parameter	Explanation	required?
rows	the name of the field to be used for the table rows	yes
cols	the name of the field to be used for the table columns	no (if no cols are specified, all instance values appear in 1 column)
fact	aggregation function fact=method(selector)	yes
table	whether to hide the pivot table (table=0) or show it (table=1)	no
chart	render as chart by default, format: chart=<type>:<dimension>	no

rows, cols and fact support the same options as list_fields:

• fields in the table
• virtual fields in the table
• fields/virtual fields in tables linked by foreign keys ($-notation)

The chart option accepts:

• type: piechart|barchart|breakdown
• dimension: rows|cols

S3Report supports a number of aggregation functions. The following functions have been tested so far:

Function	Explanation
list	a comma-separated list of all instance values
count	the number of instance values
sum	the sum of all instance values
avg	the average (mean) of all instance values

Examples

• ​http://localhost:8000/eden/org/organisation/report?rows=country&cols=sector_id&fact=name
• ​http://localhost:8000/eden/pr/person/report?rows=age_group&cols=gender&fact=id&aggregate=count

$-notation for references:

• ​http://localhost:8000/eden/org/office/report?rows=location_id$parent&cols=type&fact=name

reduced parameter list:

• ​http://localhost:8000/eden/org/office/report?rows=type&aggregate=count
• ​http://localhost:8000/eden/org/organisation/report?rows=sector_id

Report Options

Table configurations can contain a report_options item, which is used to configure reports and the report options form.

Report_options can contain rows, cols, fact (+optional methods), defaults and precision properties.

The defaults property is a dict that contains the default values for the report. It can contain a value for rows, cols, fact, and totals (as described in URL Methods).

The precision property can be used to determine the maximum precision of aggregates for a fact field.

Here is an example of a report_options item:

report_options = {
        "rows": report_fields, # which fields can be pivot table rows (list of selectors)
        "cols": report_fields, # which fields can be pivot table columns (list of selectors)
        "fact": report_facts,  # possible facts, list of fact descriptors like "method(selector)"
        "defaults": {
                "rows": "project_id",
                "cols": "name",
                "fact": "sum(time_actual)",
                "totals": True,
        },
        "precision": {
                "time_actual": 2,  # round aggregations of this field to 2 decimals
        },
}

All items in report_options and defaults are optional.

If fact and methods are configured like:

   report_options = {
       "fact": [selector1, selector2, selector3],
       "methods": [method1, method2], # methods can be omitted, falls back to a list of all supported methods
       ...
   }

then S3Report will automatically provide a selection of all (mathematically) meaningful field-method-combinations. Some of these combinations may not be relevant for the use-case, though. To specify field-method-combinations instead, you can configure it like:

   report_options = {
       "fact": [
               "method(selector1)",
               "method(selector2)",
               "method(selector3)",
               ],
       ...
   }

Optionally, you can define a custom label for each combination (which will be used in the select list):

   report_options = {
       "fact": [
               (T("ReportLabel1"), "method(selector1)"),
               (T("ReportLabel2"), "method(selector2)"),
               (T("ReportLabel3"), "method(selector3)"),
               ],
       ...
   }

See also the main table configuration page.

Report UI Options

Show/Hide Report Option Fields

This can be done with the deployment setting:

settings.ui.hide_report_options = False # default = True

Future Plans

See BluePrint/Reporting