[Fixed]-Django Reporting Options

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.

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.

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.

Edit
It really looks like both packages are gone, but now we have a nice data structure, borrowed from R — DataFrame in pandas package
Quick tutorial (pay attention to section “Grouping”)

I don’t know about complete reporting solution for Django (or Python), but make reporting with Django is quite easy with or without ORM:

• django-tables can give you very basic structure for handling table data (asc/desc server-side sorting etc)
• you can use standart django 1.1 queryset aggregates (django-reporting uses them) for totals/subtotals stuff.

Personally I use django-tables and neithere’s datashaping python package for quick summary/avg/median/IQR/filtering stuff because I have many different data sources (REST data, two mysql dbs, csv files from R) with only few of them in django db now.

Pycha is one of candidates for me to draw simple charts.

I don’t like client-side ajax-based grids etc for reporting, but you can use it with django templates too.