Month: February 2018

Finding a Bloody Decent Django Reporting Tool

I have never liked creating reports and custom tables based on filters for end users. I don’t think any developer does.

It feels like a very generic task and can be achieved (more) easily and with greater agility with the use of SQL. The group_by does not really exist in django. It smartly hides that from the developer and rightly so. It also seems that django was never strong on reporting, and was more reporting on news events…

Unfortunately when a client now asks for totals, group bys and custom filtering…django-tables2 just does not cut it anymore. So as a lazy developer, let us find something that can help us from reinventing the wheel and writing so much damn code.

What do I need out of the reporting package?

  • Automatic filters – filters of related fields, dates and relevant validation should be created automatically and be customisable
  • Fields to show, group by and aggregations should be options when generating the table of report
  • Subtotals and totals should show based on the grouped by options selected
  • Formats of output should be automatic based on django settings and be able to be overriden

But pictures paints 1000 words, so here are some screenshots of what I am trying to create:

The main report screen:

Available options that update dependent fields and uncheck users not linked to specific projects or clients:

 


This is what the final “grouped by” report with totals and sub totals looks like:

Django Reporting Packages

These are some of the packages I found. There are some more and a bit more info at djangopackages.com

Updated (2018-02-23)

Django Model Report

I thought django-model-report was the white knight but it does not support python3.

It has exactly what I am looking for in a report, check this screenshot (grouped by):

Unfortunately it does not support python 3, so I have to throw it out of the mix.

Django Report Builder

A good offering, works with python3 but lacks the ability to select from a dropdown when filtering. You need the exact id or name (there is no dropdown) or available options.

django-report-builder

You can export to xls or csv but there are some issues when translating, displaying and aggregating django native model fields like DurationField

django-report-builder display fields issue

Django-SQL-Explorer

A decent offering but you need to know SQL well and for me the schema was not showing at all. So it was difficult to guess the table and column names.

What Did I use in the End for my Django Reporting Tool?

At the end of the day, I decided to use pandas via django-pandas. This gave me all the versatility of sql (and more) and also has some helpers to export the dataframe to excel, csv or something similar.

I created the frontend using django forms and custom javascript – ie. I had to do a bit of coding (but not from scratch I made use of django forms and jquery).

customer-report-formGenerating of the HTML, CSV, PDF and excel report was done with the help of pandas and easy pdf.

Which package is the King of Django Table Display?

I’ve been building a web app.

For simplicity sake, I have been making use of normal bootstrap tables and Django’s ListView. It works well, with my tests but it creates tables that are plain that lack search and sorting. Although pagination works nicely. There comes a time when you need to get professional and add a bit more functionality to improve the user’s experience.

What Else is out there

It was suggested to me that JQuery Datatables works well and I think it does. There are some packages available for django tables and some that use datatables so I thought I would give them a try and see if they could make my life simpler than implementing jQuery Datatables directly.

Django-Tables2

Set up feels pretty easy. Column ordering is built in, bollean fields are converted into ticks and crosses automagically. It changes emails to mailto: links., relatedLink and Link columns.

So a lot less stuff you need to change.

Using them with a class based view is a bit crappy and they are very much python based (server side). Not Javascript.

I’ve run into a bit of trouble when adding filtering as a class-based view from django-filters.

It is my opinion to save you time in customising for extra rows for different users and custom formats and display of columns, you should be using django-tables2 on your project.

It works really well with bootstrap and semantic-ui, just make sure to set the options correctly.

Documentation is also really good.

Django-datatable-view

Documentation and github readme lacks a quick start or getting started section. Adding the simple bootstrapping steps should be the base of every readme and helps people get up and runnings as quick as possible.

There is just way too much text and pontification on how the server side implementation is trumping client side implementation of datatables.

Show us the bloody code…

I even made a pull request to fix this issue and create a getting started guide for django-datatable-view.

I also mentioned the issue but alas the owner of the repo has no clue and that shows in the above comments regarding the shit documentation. You can see her response to what I deem a simplification and improvement to the project. Shot down in flames by this SJW.

I tried but now that repo is dead to me and to the python/django community. It is irrelevant and I recommend you avoid using django-datatable-view because it is crappy and unmaintained.

Well it seems there are issues as get_all_field_names is being used but it was removed in django >= 1.10

Django-eztables

Looked promising but it seems to be unloved and was hurt by the changes in jQuery datatables 1.10 . The issue I got was not resolved and hence I could not move forward.

Not one to use this day and age.

Django-jinja-knockout

Well just looking at the github page, it isn’t simple it requires special settings, MiddleWare and ContextProcessors

Read it all here

Django-Datatable

Hopefully I have left the best for last.

Nope, also having trouble with this thing.

What Now?

It is very important of you have an app with even 1 table that you use django-tables2 it will make your life that much easier in terms of creating and displaying tables.

I made use of the Datatables library on it’s own with django-tables2 as the packages that are supposed to do this neatly and effectively don’t really do that and are pretty much not at the level.

In my case I just disabled ordering and filtering (which is poor server-side style) and did not do server side pagination.

All of that is handled with datatables and it worked out pretty well. I also added a few settings to make it work with bootstrap:


class UserTable(tables.Table):
 '''User table
 '''
 class Meta:
 model = User
 template_name = 'django_tables2/bootstrap-responsive.html'
 attrs = {
 'class': 'table table-bordered table-striped table-hover',
 'id': 'userTable'
 }
 fields = (
 'first_name', 'last_name', 'email',
 'company', 'role', 'employee_type',
 'is_active'
 )
 orderable = False
 empty_text = '-'

Then in the template displaying the table and initialising data tables is pretty easy with:

<div class="row">
<div class="col-xs-12">{% render_table table %}</div>
</div>

and


{% block page_js %}
<script type="text/javascript" src="{% static '/js/jquery.dataTables.min.js' %}"></script>
<script type="text/javascript" src="{% static '/js/dataTables.bootstrap.min.js' %}"></script>
<script>
$(document).ready(function(){
 $('#userTable').DataTable();
});
</script>
{% endblock %}