Django Reporting Options

Building upon @s-lott's suggestion, you can add reports to the admin site using a proxy model, a model admin class with custom changelist_view() and a custom template that derives from admin/base_site.html.

Assuming Django v2.1 (for model view permissions) and a classic customers, products and sales domain, here's a full example:

  1. Create a proxy model to show the report in admin index page: 
    class SalesReport(SalesOrder):
    class Meta:
        proxy = True
  2. Create a model admin class for the model: 
    @admin.register(SalesReport)
class SalesReportAdmin(admin.ModelAdmin):
    ...
  3. Implement the report view: 
    def sales_report(self, request):
    monthly_products_by_customer_sql = '''
    SELECT c.name AS customer,
           p.name AS product,
           COUNT(DISTINCT o.id) AS total_orders,
           SUM(oi.quantity) AS total_products,
           SUM(oi.quantity * oi.price) AS total_amount
    FROM sales_salesorder o
    INNER JOIN customers_customer c ON o.customer_id = c.id
    INNER JOIN sales_salesorderitem oi ON o.id = oi.sales_order_id
    INNER JOIN products_product p ON oi.product_id = p.id
    WHERE o.departure_date >= %s AND o.departure_date <= %s
    GROUP BY c.id, p.id
    ORDER BY total_amount DESC;
    '''
    start, end = get_previous_month_start_end_date()
    with connection.cursor() as cursor:
        cursor.execute(monthly_products_by_customer_sql, (start, end))
        results = namedtuplefetchall(cursor)
        totals = Totals(
            total_orders=sum(r.total_orders for r in results),
            total_products=sum(r.total_products for r in results),
            total_amount=sum(r.total_amount for r in results),
        )
        context = dict(
            self.admin_site.each_context(request),
            title=f'Sales report for {start} - {end}',
            period_start=start,
            period_end=end,
            results=results,
            totals=totals,
        )
        return TemplateResponse(request, 'sales/report.html', context)
  4. Return the report view from changelist_view(), wrapping it into admin_site.admin_view() to protect it from unauthorized access 
    def changelist_view(self, request):
    return self.admin_site.admin_view(self.sales_report)(request)

  5. Remove add, change, delete permissions so that only view permission remains and protect change and history views:

    def has_add_permission(self, request):
    return False

def has_change_permission(self, request, obj=None):
    return False

def has_delete_permission(self, request, obj=None):
    return False

def change_view(self, *args, **kwargs):
    raise PermissionDenied

def history_view(self, *args, **kwargs):
    raise PermissionDenied

  6. Helpers and imports for the sales_report() view are as follows:

    from collections import namedtuple
from django.core.exceptions import PermissionDenied    
from django.db import connection
from django.template.response import TemplateResponse

Totals = namedtuple('Totals', ['total_orders', 'total_products', 'total_amount'])

def namedtuplefetchall(cursor):
    '''Return all rows from a cursor as a namedtuple'''
    desc = cursor.description
    nt_result = namedtuple('Result', [col[0] for col in desc])
    return [nt_result(*row) for row in cursor.fetchall()]

def get_previous_month_start_end_date():
    today = datetime.date.today()
    prev_month_last = datetime.date(today.year, today.month, 1) - datetime.timedelta(1)
    prev_month_first = datetime.date(prev_month_last.year, prev_month_last.month, 1)
    return prev_month_first, prev_month_last
  7. Add the following template to sales/report.html, deriving from admin/base_site.html to use the admin layout: 
    {% extends "admin/base_site.html" %}
{% block content %}
<div id="content-main"><div class="results">
  <table>
    <thead>
      <tr>
        <th scope="col"><div class="text">Customer</div></th>
        <th scope="col"><div class="text">Product</div></th>
        <th scope="col"><div class="text"># orders</div></th>
        <th scope="col"><div class="text"># products</div></th>
        <th scope="col"><div class="text">Amount €</div></th>
      </tr>
    </thead>
    <tbody>
      {% for result in results %}
      <tr class="row1">
        <td>{{ result.customer }}</td>
        <td>{{ result.product }}</td>
        <td>{{ result.total_orders }}</td>
        <td>{{ result.total_products }}</td>
        <td>{{ result.total_amount|floatformat:2 }}</td>
      </tr>
      {% endfor %}
      <tr class="row1" style="font-weight: bold">
        <td>&nbsp;</td><td>&nbsp;</td>
        <td>{{ totals.total_orders }}</td>
        <td>{{ totals.total_products }}</td>
        <td>{{ totals.total_amount|floatformat:2 }}</td>
      </tr>
    </tbody>
  </table>
</div></div>
{% endblock %}

Now the report will be listed in admin index page with view-only icon 👁, it is protected from unauthorized access and has a consistent look with the rest of the admin site.


I made django-report-builder. It lets you build ORM queries with a gui and generate spreadsheet reports. It can't do templates, that would be a great feature to add though.


There is a grid on djangopackages.com which may be of use evaluating options:

https://www.djangopackages.com/grids/g/reporting/


These are just HTML templates with ordinary view functions.

This doesn't require much: Parameters come in from a form; write the query in the view function, passing the queryset to the template. The template presents the report.

Why would you need something more than this?

You can use generic list/detail views to save yourself from having to write as much code. If you go this route, you provide the query set and the template to a generic view that handles some of the processing for you.

Since you must write the query in Crystal reports or Django, you're not really getting much leverage from a "reporting" tool.

Tags:

Python

Django

Reporting

Recent Posts