A Simple Pattern to Generate Reports in Rails

29 March 2016 on . 8 minutes to read

Your users shouldn't have to wait 1.5 seconds for their report.

Intro

You’ve got an app that’s used for some function; over the course of time it’s collected tons of data, and now your customers want ways to see what’s going on within the app. You put together a simple reporting scheme, but once you load it up with production data, you get a timeout while the report generates. It’s that slow. Read on for how to use a pure ruby on rails approach to build reporting for your app and store historical reports.



The Need To Optimize Reporting

Apps that get used by even a decent amount of users for any given amount of time will easily build tables with a few hundred thousand records in them. What happens when your customers require reporting functionality? You can try to build the fully optimized version straight away, or you can throw together something within a few hours that will meet their basic needs, but is slow. Thankfully, with ActiveJob, you don’t need to worry that generating your reports will take time. Just run it in the background. At some point, you’ll need to optimize your queries and calculations, but the first step; making sure the customer is presented with a page that informs them and works is, in my opinion, the most important.

Why this pattern?

Remember, your customer pays you for features, primarily through their perception of those features, not for the good feeling you as a developer get when you build the quickest multi table SQL query known to mankind. I expect anyone reading this is either a Rails developer, or an aspiring one. If you’ve got a Rails app, coding in Ruby is both accessible and known; so keeping a pure RoR programming pattern ensures that any dev who picks your app up will quickly understand what’s going on. It’s easy to modify, and you don’t have to worry about naming collisions between fields from different tables which would be a concern when constructing SQL views or Postgres materialized views.

Going to need to save historical reports anyways; take the easy optimization first

An anecdote: I developed this pattern for generating reports when I ran into the issue of doing joins across tables where a report was being generated on a 40k records at a time, and this data set will only get larger as time goes on. Also, getting those 40k rows from a 50k row table is tiny relative to what a lot of production data will look like. When a user would go to a page that would display a live report, it was taking 30+ seconds on a 1x Heroku web worker. For reference, this same report took ~10 seconds on a current gen Macbook Pro with an SSD. At this point it was obvious that up to the minute live reports weren’t feasible without some major optimizations. Instead, I decided to make a job that generated this report and then saved the results to the database. This way users would also be able to view historical reports.

The Pattern

The basic pattern that I used is below:

  • create table report_model_name
  • make model Report::ModelName
  • calculate Report::ModelName fields in Report::Generator::ModelName
  • cache everything in instance variables to prevent any chance of duplicating expensive queries
  • save a new Report::ModelName

For the examples, we’ll be generating statistics for United States citizens in case you’re wondering where Citizen and its fields originate.

Report Generator User Flow and Background Job

Before I dive in to adding a new field, read the following to get a higher level few of how a user requests a report be generated and how your app needs to be setup to handle this.

View, Controller, Routes, Generator Job

views/report/citizens/new.html.slim

.page-title
  h3.title New Citizen Report
=simple_form_for([:admin, @citizen_report], html: {class: "form-vertical"}) do |f|
  =f.submit 'Generate Report', class: 'btn btn-success btn-lg w-md m-b-5 pull-right'

controllers/report/citizens_controller.rb

class Report::CitizensController < Report::ReportsController
  def index
    @citizen_reports = Report::Citizen.all
  end

  def new
    @citizen_report = ::Report::Citizen.new
  end

  def create
    CitizenReportGeneratorJob.perform_later()
    redirect_to admin_report_citizens_path, notice: 'Generating new report. Check back shortly.'
  end

  def show
    @citizen_report = ::Report::Citizen.find(params[:id])
  end
end

jobs/citizen_report_generator_job.rb

class ReportReportGeneratorJob < ActiveJob::Base
  def perform(status: nil)
    ::Report::Generator::DealJacket.new.save_report
  end
end

models/report/generator/citizen.rb

class Report::Generator::DealJacket
  attr_accessor :deal_jackets, :status

  def initialize(asset_status)
    @status = asset_status
    @deal_jackets = ::DealJacket.where(status: asset_status)
  end

  def save_report
    Report::Citizen.new(
      ::Report::Citizen.new.attributes
    ).save!
  end

config/routes.rb

namespace :report do
  resources :citizens, only: [:index, :new, :create, :show]
end

Not pictured: an index page where you list all the reports.

This allows a user to go to localhost:3000/report/citizen/new, generate a report, and be taken back to the index page. When that happens, they’ll be notified that their report is being generated. As long as people are kept updated on what’s going on, they’re generally happy. It’s like standing in line; folks don’t mind as long as they’re notified of the reason. Later, they refresh the report page, your report pops up and then they can view it. Easy enough.

Steps involved to add new fields

To add a new report field, do the following:

  • Add a field to the model’s report
  • Create a generator function to calculate this field
  • Add field to generator’s attribute hash
  • Add field to report view

For the above steps, I’ll show how to calculate the average age of all the Citizens. Assume that Citizen#age already exists.

New Field Migration

The basic form of this is: rails g migration add_fieldname_to_report_model_name fieldname:fieldtype

For our example, run rails g migration add_age_to_report_citizen average_age:integer

Create Generator Function

models/generator/citizen.rb

class Report::Generator::Citizen
  def average_age
    Citizen.average(:age)
  end
end

Add field to generator’s attribute hash

models/generator/citizen.rb

class Report::Generator::Citizen
  def average_age
    Citizen.average(:age)
  end

  def attributes
    {
      average_age: average_age
    }
  end
end

Display Average Age on the Report

views/report/citizens/show.html.slim

.panel-body
    .row
      .col-md-3 Average Age
      .col-md-9 = @citizen_report.average_age
    .row

You’ll have to do each of those steps once for each new attribute. I’d highly recommend throwing a decorator in the mix as well to properly display what you’re showing.

Future optimizations

As I said earlier, this is a very basic, pure Ruby implementation. So far I’ve only touched on the framework of setting this up. Obviously you’ll want to focus on optimizing the performance of this, but that will take place in another post that will go over using built in SQL functions, rails functions such as indices, :includes and :pluck, as well as implementing this same functionality with views and materialized views so you can quickly report on associated information from related records.


If you enjoy having free time and the peace of mind that a professional is on your side, then you’d love to have me work on your project.

Contact or view a list of available services to see how I’ll make your life better, easier and bring satisfaction back into you running your business.