Django Reporting Options

Question

I want to create a new business application using the Django framework. Any suggestions as to what I can use as a reporting framework? The application will need to generate reports on various business entities including summaries, totals, grouping, etc. Basically, is there a Crystal reports-like equivalent for Django/Python?

Textualism

see stackoverflow.com/q/8238975/302521 for some answer of using BIRT with Django. Not what you may be looking for, but we made it work alright for fancy formatted reports. – Ingulf

I use POD: #16944097 – Raynaraynah

Answer 1

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

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

Arin

You should say that this is alpha, has two opened bugs and the project activity is less than you could expect. – Memorabilia

May 2014 now and that code repo hasn't been updated since Dec 2009 – Nicolas

I should note, a LOT of the projects on that site have pretty bad bit-rot and wont work with modern django. It is, alas, the nature of open source. (Seems like slick reports is modern though. Sadly django-report-builder appears abandoned and doesnt work at all with modern django. Shame, that thing was good) – Atrip

Answer 2

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.

Placenta

I hope that someday you can get it to work with Django 1.10 and the latest release of Django Rest Framework. It looks like such a useful tool to have! – Circumference

Open a bug report if the latest DRF is causing trouble. Django 1.10 is a known issue and is partially supported right now. – Placenta

@Placenta I already have my queries with filters ready (using django_filters) . I just need to add a button for Download. Is it possible to only add the downloading capability to my Filter Set ? – Fourteenth

Hey, I realise you've moved on, but it'd be super useful to have that thing updated to work with Django 4/5. report builder was great software, kinda sad to see it in a state of bit-rot. And clients absolutely loved being able to jiggle together their own reports. – Atrip

Answer 3

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.

Zita

This is great! I hope people notice it as it is buried in a 9 years old thread. There are other threads in SO where this answer can illuminate other people too. Like those related to custom aggregations and/or changes to the admin template change_list.html – Unpretentious

Thank you :)! I can assist in other threads as well, but I would need your help finding them. – Zita

Oh, boy. I read tons of them. But I don't remember how to find them. This is the only thing that it occurs to me at the moment > stackoverflow.com/search?q=%22change_list_template%22 – Unpretentious

@Zita It's true, this solution is really great, and feels proper. I'm having a heck of a time googling resources on doing it this way. Any good resources for extending this method, perhaps with an "index" of reports? Thanks a million, seriously. – Kania

This solution also works for django 3.2 as 9/13/21 – Kania

@fish, glad it helped! This fit perfectly with my needs, so I didn't dig deeper. But you could implement an "index" of reports simply by a separate reports app that just contains the admin reports and nothing else. – Zita

Answer 4

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.

Evaevacuant

With a good reporting framework, you get multiple display views and sorting, and the need to create templates is eliminated. – Arin

@Daniel: with a "good" reporting framework... Nice sentiment. Any examples or suggestions? You can write a generic template for simple columnar reports very quickly. Not sure I see what would make a reporting tool any better than simply doing the obvious in Django. – Evaevacuant

@S.Lott, reporting frameworks are useful when you want more than a simple flat table of data. eg, newforms is pretty nice, but that doesn't make the admin interface overkill. – Arin

I mean, your ultimate report builder would probably be to SOMEHOW get crystal reports , PowerBI or BIRT somehow integrated in. But who's got the time of day to pull that off? – Atrip

Answer 5

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.

Hagerty

django-tables development seems to be going on at github.com/bradleyayers/django-tables with documentation at readthedocs.org/docs/django-tables/en/latest. The latest PyPI release pypi.python.org/pypi/django-tables/0.1 from the original author is from July 2008. – Corney

both django tables and datashaping no longer seem to exist - all repositories and doc pages are giving 404s – Nicolas

Yeah looks like they are gone. Probably these days one could use DataFrame structures from pandas (see short description here: gregreda.com/2013/10/26/working-with-pandas-dataframes), especially "Grouping" section – Hagerty