Expressions

This guide assumes you are creating a resource that extends CubedReportResource if you are extending from ModelResource then you want to be looking at the core tastypie docs

Intro

Expression Types:

We have two types of expressions in our project that are each used for different things. Standard expressions, these tell django how to get a field's data with and without using a group by and looks like this:

@classmethod
def sales_variance(c):
    return (c.fm_sales_field - c.lc_sales_field)

And then we have __agg expressions, these are only used to create the totals property in meta and look like this:

@classmethod
def agg__sales_variance(c):
    return (Sum(c.fm_sales_field) - Sum(c.lc_sales_field))

When to make an Expression

Expressions will be automatically made for all field's on the model. So if you are just wanting to put data out of the DB onto the page, and then have a totals bar sum that, you do not need to create anything. Expressions are for when we want to either add additional_metrics to the queryset, or when we want to change the data being returned from the table using SQL.

Some Examples

"I want to get the percentage difference between two field's"

divide field b by the result of filed a - field b:

@classmethod
def sales_variance_pct(c):
    return Coalesce(((c.fm_sales_field - c.lc_sales_field) / c.lc_sales_field), 0)

"Field X isn't associated with products or cost and I want to evenly distribute the data"

In some cases data in reports might not have the correct scaling applied to it, an example of this is cost, to get around this cost can be distributed to products evenly by dividing the value by the count of products:

@classmethod
def cost(c):
    return Sum(c.cost_field) / Count(c.product_field, distinct=True, output_field=DecimalField())

Referencing the results of other Expressions

This is more tricky and if you get it wrong you may find that your group by may have had extra field's added to it, or Django struggles to find the field you have referenced.

I massively recommend reading the Django Docs On Query Expression to understand what is going on under the hood if you are struggling.

We have three places where we reference the field name in our Tastypie set up, firstly in the field install:

config_fields = BaseFields(strict=True)
config_fields.install_additional_metric('date', DateTimeField())

secondly in the @classmethod of the Expressions class:

@classmethod
def date(cls):
    return TruncMinute(cls.date_field)

and finally in the F() Expression where we tell django that we are pointing to the model field or annotated column.

Standard expressions

to reference the result of a standard expression, you need to use it's F() value, for example F('cost') or c.cost_field this way django will know that you want the value of the expression you have written so its fine for it to be wrapped in a sub query / aliased:

class ExampleExpressions(object):
    cost_field          = F('cost')
    lc_sales_field      = F('lc_sales')

    @classmethod
    def cost(c):
        return Sum(c.cost_field) / Count(c.product_field, distinct=True, output_field=DecimalField())

    @classmethod
    def lc_cpa(c):
        return Coalesce((c.cost_field / c.lc_sales_field), 0)

When the query set gets built for this resource, the sql will calculate Sum(table.cost) / Count(table.product_id) as "cost" and then ("cost" / table.lc_sales) as "lc_cpa"

Agg Expressions

Our __agg expressions are much easier due to how django aggregation gets all the data that is needed into a sub query and the referenced later so you are fine to create aggregate expressions by calling other class methods:

@classmethod
def agg__lc_cpa(c):
    return Coalesce((c.agg__cost() / Sum(c.lc_sales_field)), 0)

Totals

We are, specifically, adding the ability to “format” Totals in a customizable way. This is due to the fact that the django/sql to calculate the Totals is run as part of the main queryset, and in some cases displaying that Total is wrong - for example Bools, Percentages, Strings. However, with this new “format_total” function, we can return any “text”.

The following function is in the baseField class and can be overriden by any class that inherits from BaseField class. For example, the Market Share field returns a value but change Over Time and Change Over Time % do not. Thats due to the fact that the Totals property is irrelevant and unneeded for those two fields/expressions.

def format_total(self, value):

    """

        Handles formatting the data in the field.

        Extend in each Field subclass to use.

    """

    return self.format(value)

We added the functionality of customizing the calculation of totals according to the required output and field type.

class DecimalFieldNoTotal(DecimalField):

    def format_total(self, value):

        return ''

Here is the first use/application of the function format_total. We implement the Class DecimalFieldNoTotal which inherits the DecimalField class and overrides the format_total to return the desired output for totals which in this specific case is to return blank.