= S3Report =
[[TOC]]

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]
== 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 [#URLMethods 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:
{{{#!python
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:

{{{#!python
   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:

{{{#!python
   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):

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

See also the main [wiki:S3/TableConfigurationSettings#PivotTableReports 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