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:
couchdbkit-aggregate (legacy)
Example Custom Report Scaffolding
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:
from myproject import reports
CUSTOM_REPORTS = (
('Custom Reports', (
reports.MyCustomReport,
reports.AnotherCustomReport,
)),
)
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.
- class corehq.apps.reports.sqlreport.SqlData(config=None)[source]
- property columns
Returns a list of Column objects. These are used to make up the from portion of the SQL query.
- property distinct_on
Returns a list of column names to create the DISTINCT ON portion of the SQL query
- property filter_values
Return a dict mapping the filter keys to actual values e.g. {“enddate”: date(2013, 1, 1)}
- property filters
Returns a list of filter statements. Filters are instances of sqlagg.filters.SqlFilter. See the sqlagg.filters module for a list of standard filters.
e.g. [EQ(‘date’, ‘enddate’)]
- property group_by
Returns a list of ‘group by’ column names.
- property keys
The list of report keys (e.g. users) or None to just display all the data returned from the query. Each value in this list should be a list of the same dimension as the ‘group_by’ list. If group_by is None then keys must also be None.
These allow you to specify which rows you expect in the output data. Its main use is to add rows for keys that don’t exist in the data.
- e.g.
group_by = [‘region’, ‘sub_region’] keys = [[‘region1’, ‘sub1’], [‘region1’, ‘sub2’] … ]
- table_name = None
The name of the table to run the query against.
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 and sqlagg for more info.
e.g.
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
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.
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
get_data() function.
- class corehq.apps.reports.api.ReportDataSource(config=None)[source]
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 for more info.
e.g.
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()