docs/reporting.rst - master - githubmirror/commcare-hq

docs/reporting.rst
228 строк7 KB
Jennifer Schweers
Removed old 'dynamic reports' feature
08 мар 2022, 02:25
08 мар 2022, 02:25d9150db
Reporting
=========

A report is
    a logical grouping of indicators with common config options (filters etc)

The way reports are produced in CommCare is still evolving so there are a number
of different frameworks and methods for generating reports. Some of these are
*legacy* frameworks and should not be used for any future reports.


Recommended approaches for building reports
-------------------------------------------

Things to keep in mind:

* `report API <report_api>`_


* `sqlagg`_
* `couchdbkit-aggregate`_ (legacy)

.. _sqlagg: https://github.com/dimagi/sql-agg
.. _couchdbkit-aggregate: https://github.com/dimagi/couchdbkit-aggregate
.. _sqlalchemy: http://docs.sqlalchemy.org/en/rel_0_8/core/tutorial.html

Example Custom Report Scaffolding
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

.. code-block:: python

    class MyBasicReport(GenericTabularReport, CustomProjectReport):
        name = "My Basic Report"
        slug = "my_basic_report"
        fields = ('corehq.apps.reports.filters.dates.DatespanFilter',)

        @property
        def headers(self):
            return DataTablesHeader(DataTablesColumn("Col A"),
                                    DataTablesColumnGroup(
                                        "Group 1",
                                        DataTablesColumn("Col B"),
                                        DataTablesColumn("Col C")),
                                    DataTablesColumn("Col D"))

        @property
        def rows(self):
            return [
                ['Row 1', 2, 3, 4],
                ['Row 2', 3, 2, 1]
            ]

Hooking up reports to CommCare HQ
---------------------------------

Custom reports can be configured in code or in the database. To configure custom reports in code
follow the following instructions.

First, you must add the app to `HQ_APPS` in `settings.py`.  It must have an `__init__.py` and a
`models.py` for django to recognize it as an app.

Next, add a mapping for your domain(s) to the custom reports module root to the `DOMAIN_MODULE_MAP`
variable in `settings.py`.

Finally, add a mapping to your custom reports to `__init__.py` in your custom reports submodule:

.. code-block:: python

    from myproject import reports

    CUSTOM_REPORTS = (
        ('Custom Reports', (
            reports.MyCustomReport,
            reports.AnotherCustomReport,
        )),
    )


.. _sql:

Reporting on data stored in SQL
-------------------------------

As described above there are various ways of getting reporting data into
and SQL database. From there we can query the data in a number of ways.

Extending the ``SqlData`` class
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The ``SqlData`` class allows you to define how to query the data
in a declarative manner by breaking down a query into a number of components.

.. autoclass:: corehq.apps.reports.sqlreport.SqlData
    :members: table_name, distinct_on, columns, filters, filter_values, group_by, keys

This approach means you don't write any raw SQL. It also allows you to
easily include or exclude columns, format column values and combine values
from different query columns into a single report column (e.g. calculate percentages).

In cases where some columns may have different filter values e.g. males vs females,
**sqlagg** will handle executing the different queries and combining the results.

This class also implements the ``corehq.apps.reports.api.ReportDataSource``.

See `Report API <report_api_>`_ and `sqlagg`_ for more info.

e.g.

.. code-block:: python

    class DemoReport(SqlTabularReport, CustomProjectReport):
        name = "SQL Demo"
        slug = "sql_demo"
        fields = ('corehq.apps.reports.filters.dates.DatespanFilter',)

        # The columns to include the the 'group by' clause
        group_by = ["user"]

        # The table to run the query against
        table_name = "user_report_data"

        @property
        def filters(self):
            return [
                BETWEEN('date', 'startdate', 'enddate'),
            ]

        @property
        def filter_values(self):
            return {
                "startdate": self.datespan.startdate_param_utc,
                "enddate": self.datespan.enddate_param_utc,
                "male": 'M',
                "female": 'F',
            }

        @property
        def keys(self):
            # would normally be loaded from couch
            return [["user1"], ["user2"], ['user3']]

        @property
        def columns(self):
            return [
                DatabaseColumn("Location", SimpleColumn("user_id"), format_fn=self.username),
                DatabaseColumn("Males", CountColumn("gender"), filters=self.filters+[EQ('gender', 'male')]),
                DatabaseColumn("Females", CountColumn("gender"), filters=self.filters+[EQ('gender', 'female')]),
                AggregateColumn(
                    "C as percent of D",
                    self.calc_percentage,
                    [SumColumn("indicator_c"), SumColumn("indicator_d")],
                    format_fn=self.format_percent)
            ]

        _usernames = {"user1": "Location1", "user2": "Location2", 'user3': "Location3"}  # normally loaded from couch
        def username(self, key):
            return self._usernames[key]

        def calc_percentage(num, denom):
            if isinstance(num, Number) and isinstance(denom, Number):
                if denom != 0:
                    return num * 100 / denom
                else:
                    return 0
            else:
                return None

        def format_percent(self, value):
            return format_datatables_data("%d%%" % value, value)


.. _report_api:

Report API
----------
Part of the evolution of the reporting frameworks has been the development of
a *report api*. This is essentially just a change in the architecture of
reports to separate the data from the display. The data can be produced
in various formats but the most common is an list of dicts.

e.g.

.. code-block:: python

  data = [
    {
      'slug1': 'abc',
      'slug2': 2
    },
    {
      'slug1': 'def',
      'slug2': 1
    }
    ...
  ]

This is implemented by creating a report data source class that extends
``corehq.apps.reports.api.ReportDataSource`` and overriding the
:func:`get_data` function.

.. autoclass:: corehq.apps.reports.api.ReportDataSource
    :members: slugs, get_data

These data sources can then be used independently or the CommCare reporting
user interface and can also be reused for multiple use cases such as
displaying the data in the CommCare UI as a table, displaying it in a map,
making it available via HTTP etc.

An extension of this base data source class is the ``corehq.apps.reports.sqlreport.SqlData``
class which simplifies creating data sources that get data by running
an SQL query. See section on `SQL reporting <sql_>`_ for more info.

e.g.

.. code-block:: python

  class CustomReportDataSource(ReportDataSource):
      def get_data(self):
          startdate = self.config['start']
          enddate = self.config['end']

          ...

          return data

  config = {'start': date(2013, 1, 1), 'end': date(2013, 5, 1)}
  ds = CustomReportDataSource(config)
  data = ds.get_data()