Reporting and Charting

Before we begin, charts and reporting get more fun and interesting the more data available. So below is a custom management command code that you can use to generate data for the whole current year. This will definitely enhance our experience with this section. Also you can use it to benchmarking Ra Performance.

Generating test data

Create and add below code to ‘sample_erp/management/commands/generate_data.py’

import random
import datetime
import pytz
from django.core.management import BaseCommand


class Command(BaseCommand):
    help = 'Generates data for simple sales app'

    def add_arguments(self, parser):
        parser.add_argument('--clients', type=int, action='store', help='Number of client to get generated, default 10')
        parser.add_argument('--product', type=int, action='store',
                            help='Number of products t0o get generated, default 10')
        parser.add_argument('--expenses', type=int, action='store',
                            help='Number of Expense to get generated, default 10')

        parser.add_argument('--expense-transaction', type=int, action='store',
                            help='Number of records per day,  default 10')

    def handle(self, *args, **options):
        from ...models import Client, Product, SalesTransaction, SalesLineTransaction, Expense, ExpenseTransaction
        from django.contrib.auth.models import User
        user_id = User.objects.first().pk
        client_count = options.get('clients', 10) or 10
        product_count = options.get('products', 10) or 10
        records_per_day = options.get('records', 10) or 10

        expense_count = options.get('expenses', 10) or 10
        etransaction_per_day = options.get('expense-transaction', 3) or 3

        # Generating clients
        already_recorded = Client.objects.all().count()
        clients_needed = client_count - already_recorded
        if clients_needed > 0:
            for index in range(already_recorded, already_recorded + clients_needed):
                Client.objects.create(title=f'Client {index}', lastmod_user_id=user_id)
            self.stdout.write(f'{clients_needed} client(s) created')

        # Product
        already_recorded = Product.objects.all().count()
        product_needed = product_count - already_recorded
        if product_needed > 0:
            for index in range(already_recorded, already_recorded + product_needed):
                Product.objects.create(title=f'Product {index}', lastmod_user_id=user_id)
            self.stdout.write(f'{product_needed} product(s) created')

        already_recorded = Expense.objects.all().count()
        Expenses_needed = expense_count - already_recorded
        if Expenses_needed > 0:
            for index in range(already_recorded, already_recorded + Expenses_needed):
                Expense.objects.create(title=f'Expense {index}', lastmod_user_id=user_id)
            self.stdout.write(f'{Expenses_needed} Expense(s) created')

        # generating sales
        # we will generate 10 records per day for teh whole current year
        sdate = datetime.datetime(datetime.date.today().year, 1, 1)
        edate = datetime.datetime(datetime.date.today().year, 12, 31)

        client_ids = Client.objects.values_list('pk', flat=True)
        product_ids = Product.objects.values_list('pk', flat=True)
        expense_ids = Expense.objects.values_list('pk', flat=True)

        delta = edate - sdate  # as timedelta
        for i in range(delta.days + 1):
            day = sdate + datetime.timedelta(days=i)
            day = pytz.utc.localize(day)
            for z in range(1, records_per_day):
                chosen_client = random.choice(client_ids)
                SalesLineTransaction.objects.create(
                    doc_date=day,
                    sales_transaction=SalesTransaction.objects.create(doc_date=day, client_id=chosen_client,
                                                                      lastmod_user_id=user_id),
                    product_id=random.choice(product_ids),
                    client_id=chosen_client,
                    quantity=random.randrange(1, 10),
                    price=random.randrange(1, 10),
                    lastmod_user_id=user_id
                )

            for z in range(1, etransaction_per_day):
                ExpenseTransaction.objects.create(
                    doc_date=day,
                    expense_id=random.choice(expense_ids),
                    value=random.randrange(1, 10),
                    lastmod_user_id=user_id
                )
            self.stdout.write(f'{day} Done')
            self.stdout.flush()

        self.stdout.write('----')
        self.stdout.write('Done')

Then let’s run the command

$ python manage.py generate_data

# and here with the default arguments in case you want to fine tune
$ python manage.py generate_data --clients 10 --products 10 --records 10 --expense 10 --expense-transaction 3

Now we have some test data to give us a more complete look. Let’s create some reports!!

Creating Reports

In our sample_erp app, let’s create a reports.py file it can be any name, this is just a convention. in this file we will be creating our report classes

How much each Client bought (in value)

Below code in a sample report class structure to answer this question

Add it to reports.py

from django.utils.translation import ugettext_lazy as _
from ra.reporting.registry import register_report_view
from ra.reporting.views import ReportView
from .models import Client, SalesLineTransaction, Product


@register_report_view
class ClientTotalBalance(ReportView):
    report_title = _('Clients Balances')

    base_model = Client
    report_model = SalesLineTransaction

    group_by = 'client'
    columns = ['slug', 'title', '__balance__']

Now, we need to load reports.py during the app life cycle so our code is executed. Best way to do such action is in AppConfig.ready

# in sample_erp __init__.py
default_app_config = 'sample_erp.apps.SampleERPConfig'

# in sample_erp/apps.py
from django.apps import AppConfig


class SampleErpConfig(AppConfig):
    name = 'sample_erp'

    def ready(self):
        super().ready()
        from . import reports

Now re-run runserver, go to to the dashboard, You’ll find a new menu Reports which would contains a Client sub menu. Click on the Clients menu will open the Client Report List, which will load the first report automatically.

We can notice that

  1. Report table is sortable and searchable (Thanks to datatables.net )
  2. Report can also be exported to Excel, can also be printed with a dedicated html template
  3. You can filter by Date , Client and Product. For the later two, the widget allow you to select multiple objects.
  4. All filters and calculation are done automatically.

Let’s create another report that answers the following question

How much each product was sold?

@register_report_view
class ProductTotalSales(ReportView):
    # Title will be displayed on menus, on page header etc...
    report_title = _('Product Sales')

    # What model is this report about
    base_model = Product

    # What model hold the data that we want to compute.
    report_model = SalesLineTransaction

    # The meat and potato of the report.
    # We group the records in SimpleSales by Client ,
    # And we display the columns `slug` and `title` (relative to the `base_model` defined above)
    # the magic field `__balance__` computes the balance (of the base model)
    group_by = 'product'
    columns = ['slug', 'title', '__balance_quantity__']

Did you notice that both class definition are almost the same. Main differences are the base_model and in group_by and we used __balance_quantity__ which summarize the field “quantity” instead of the field “value”.

For more information about available options checkout the Django Slick Reporting documentation Here

Now let’s create a 3rd report.

A Client Detailed statement.

Which is a simple list of the sales transaction

@register_report_view
class ClientDetailedStatement(ReportView):
    report_title = _('client Statement')
    base_model = Client
    report_model = SalesLineTransaction


    columns = ['slug', 'doc_date', 'doc_type', 'product__title', 'quantity', 'price', 'value']

Adding Charts

To add charts to a report, we’d need to add to chart_settings . Here is an example we will add two charts to teh first report we created ClientTotalBalance

class ClientTotalBalances(ReportView):
    ...
    chart_settings = [
        {
            'id': 'pie_chart',
            'type': 'pie',
            'title': _('Client Balances'),
            'data_source': ['__balance__'],
            'title_source': 'title',
        },
        {
            'id': 'bar_chart',
            'type': 'bar',
            'title': _('Client Balances [Bar]'),
            'data_source': ['__balance__'],
            'title_source': 'title',
        },
    ]

Reload your development server and check how those charts are displayed in the Client Balances report.

Neat right ?

So to create a report we need to a dictionary to a chart_settings list containing

  • id: (optional) Name used to refer to this exact chart in front end (we will use that in Adding charts & report widgets) default is type-{index}
  • type: what kind of chart it is bar, pie, line, column
  • data_source: a list of Field name(s) of containing the numbers we want to chart,
  • title_source: a list label(s) respective to the data_source
  • title: the chart title

Time Series

A time series is a report where the columns represents time unit (year/month/week/day)

Let’s see an example

@register_report_view
class ProductSalesMonthly(ReportView):
    report_title = _('Product Sales Monthly')

    base_model = Product
    report_model = SalesLineTransaction

    group_by ='product'
    columns = ['slug', 'title']

        # how we made the report a time series report
    time_series_pattern = 'monthly'
    time_series_columns = ['__balance__']

Reload your development server , go to Product reports, and check the Product Sales Monthly report.

All we did was adding

  • time_series_pattern which describe which pattern you want to compute (daily/monthly/yearly)
  • time_series_columns where we indicated which field to compute for each time series period.

Noticed that time_series_columns is a list? This means that we can have more fields computed fpr each period.

In the above report, we computed the sum of value of sales for each product, for each period. We can also know the sum of quantity of each product for each period as well. Just add '__balance_quantity__' to the time_series_columns list.

Reload your app and check the results. You should see that for each month, we have 2 fields “Balance QTY” and “Balance”

Now let’s add some charts, shall we ?

# Add chart settings to your ProductSalesMonthlySeries
@register_report_view
class ProductSalesMonthly(ReportView):
    ...
    chart_settings = [
        {
            'id': 'movement_column_ns',
            'title': _('comparison - Column'),
            'data_source': ['__balance__'],
            'title_source': ['title'],
            'type': 'column',
        },
        {
            'id': 'movement_bar',
            'title': _('comparison - Column - Stacked'),
            'data_source': ['__balance__'],
            'title_source': ['title'],
            'type': 'column',
            # 'stacked': True,
            'stacking': 'normal',
        },
        {
            'id': 'movement_column_total',
            'title': _('comparison - Column - Total'),
            'data_source': ['__balance__', '__balance_quantity__'],
            'title_source': ['title'],
            'type': 'column',
            'plot_total': True,
        },
        {
            'id': 'movement_line',
            'title': _('comparison - line'),
            'data_source': ['__balance__'],
            'title_source': ['title'],
            'type': 'line',
        },
        {
            'id': 'movement_line_stacked',
            'title': _('comparison - Area - Stacked-Percent'),
            'data_source': ['__balance__'],
            'title_source': ['title'],
            'type': 'area',
            'stacking': 'percent',
        },
        {
            'id': 'movement_line_total',
            'title': _('comparison - line - Total'),
            'data_source': ['__balance__'],
            'title_source': ['title'],
            'type': 'line',
            'plot_total': True,
        },
    ]

6 charts to highlight the patterns. Reload the development server and reload the report page and check the output.

The charts brings our attention that the slops are always rising … that’s because we’re using the __balance__ report field. which is a compound total of the sales. In fact, in those reports, we might be more interested in the non compound total, and there is a report field for that which comes by default called __total__

Let’s change __balance__ to __total__ in ProductSalesMonthly and check the results for yourself how is it different.

Exercise: I’m confident you can now create a time series report for the Client sales per month, Yeah ?

It would look like something like this

@register_report_view
class ClientSalesMonthlySeries(ReportView):
    report_title = _('Client Sales Monthly')

    base_model = Client
    report_model = SalesLineTransaction


    group_by = 'client'
    columns = ['slug', 'title']

    time_series_pattern = 'monthly'
    time_series_columns = ['__total__']

You can add charts to this report too !

Cross-tab report

A cross tab report is when the column represents another different named data object

@register_report_view
class ProductClientSalesCrosstab(ReportView):
    base_model = Product
    report_model = SalesLineTransaction
    report_title = _('Product Client sales Cross-tab')

    group_by = 'product'
    columns = ['slug', 'title']

    # cross tab settings
    crosstab_model = 'client'
    crosstab_columns = ['__total__']

    chart_settings = [
        {
            'type': 'column',
            'data_source': ['__total__'],
            'plot_total': False,
            'title_source': 'title',
            'title': _('Detailed Columns'),

        },
        {
            'type': 'column',
            'data_source': ['__total__'],
            'plot_total': False,
            'title_source': 'title',
            'stacking': 'normal',
            'title': _('Stacked Columns'),

        },
        {
            'type': 'pie',
            'data_source': ['__total__'],
            'plot_total': True,
            'title_source': 'title',
            'title': _('Total Pie'),

        }
    ]

Lke with the time series pattern, we added

  • crosstab_model: the field representing the model to use as comparison column.
  • crosstab_columns the report field(s) we want to compare upon, in the crosstab .
  • we used __total__ report field.