Safety in Numbers

Brighter Planet's blog

Posted by Seamus on Friday, July 20, 2012.

Simple, clean reports in Ruby

Our report library for Ruby is the shortest path between

mysql> select * from employees;
+----+------------+-----------+---------+------------+---------+
| id | first_name | last_name | salary  | birthdate  | role    |
+----+------------+-----------+---------+------------+---------+
|  1 | Deirdre    | Irish     |   45000 | 1960-09-10 | Liaison |
|  2 | Gregor     | German    | 16000.5 | 1950-09-09 | Tech    |
|  3 | Spence     | Scot      |    5000 | 1955-12-11 | Joker   |
|  4 | Vincent    | French    | 8000.99 | 1947-04-17 | Fixer   |
|  5 | Sam        | American  | 16000.5 | 1930-04-02 | Planner |
+----+------------+-----------+---------+------------+---------+

and simple, clean reports like

90% of the way by default

Did you notice these little details?

Business-class typography: Arial 10pt, left-aligned text and dates, right-aligned numbers and currency
Auto-fit to contents: always enabled
Autofilters: always added to your column headers
Freeze pane: always frozen beneath your column headers

Here’s the code that generated it:

class Tps < Report
  table 'Hierarchy' do
    head do
      row 'TPS code', :code
      row 'Date', :date
      row 'Section', 'Hierarchy'
    end
    body do
      rows :employees, ['last_name ASC']
      column('Full name') { first_name + ' ' + last_name }
      column 'Role'
      column 'Salary', :type => :Currency
    end
  end
  table 'Seniority' do
    head do
      row 'TPS code', :code
      row 'Date', :date
      row 'Section', 'Seniority'
    end
    body do
      rows :employees, ['birthdate DESC']
      column('Full name') { first_name + ' ' + last_name }
      column 'Birthdate'
      column 'Over 70?'
    end
  end
  attr_reader :code
  def initialize(code)
    @code = code
  end
  def employees(order)
    Employee.order(order).each { |employee| yield employee }
  end
  def date
    Date.today
  end
end

And here’s how you generate it:

>> t = Tps.new('ABC123')
=> #<Tps:0x00000101426998 @code="ABC123">
>> t.xlsx.path
  Employee Load (36.1ms)  SELECT `employees`.* FROM `employees` ORDER BY last_name ASC
  Employee Load (0.3ms)  SELECT `employees`.* FROM `employees` ORDER BY birthdate DESC
=> "/var/folders/Nm/Nm0HFsHAH90Zyt7NLDUZUE+++TI/-Tmp-/133538737_Report__Xlsx.xlsx"
>> t.pdf.path
  Employee Load (0.2ms)  SELECT `employees`.* FROM `employees` ORDER BY last_name ASC
  Employee Load (0.1ms)  SELECT `employees`.* FROM `employees` ORDER BY birthdate DESC
=> "/var/folders/Nm/Nm0HFsHAH90Zyt7NLDUZUE+++TI/-Tmp-/133548261_Report__Pdf.pdf"
>> t.csv.paths
  Employee Load (0.3ms)  SELECT `employees`.* FROM `employees` ORDER BY last_name ASC
  Employee Load (0.2ms)  SELECT `employees`.* FROM `employees` ORDER BY birthdate DESC
=> ["/var/folders/Nm/Nm0HFsHAH90Zyt7NLDUZUE+++TI/-Tmp-/133553761_Report__Csv__Table_Hierarchy.csv", "/var/folders/Nm/Nm0HFsHAH90Zyt7NLDUZUE+++TI/-Tmp-/133553767_Report__Csv__Table_Seniority.csv"]

You get paths to files in the tmp dir. Only the CSV output format gives you multiple files.

Three output formats: XLSX, PDF, and CSV

You’ve already seen the XLSX output format - it’s currently the most advanced.

The PDF output format starts each table on its own page:

The CSV output format puts each table into its own file:

TPS code,ABC123
Date,2012-07-19
Section,Hierarchy

Full name,Role,Salary
Sam American,Planner,16000.5
Vincent French,Fixer,8000.99
Gregor German,Tech,16000.5
Deirdre Irish,Liaison,45000.0
Spence Scot,Joker,5000.0

tps0.csv tps1.csv

How the DSL works

See the following for a line-by-line analysis…

# don't forget to inherit from Report
class Tps < Report

  # this is the sheet name in excel
  table 'Hierarchy' do

    head do
      # calling Tps#code
      row 'TPS code', :code

      # calling Tps#date
      row 'Date', :date

      # no calls are made
      row 'Section', 'Hierarchy'
    end

    body do
      # Tps#employees('last_name ASC')
      rows :employees, ['last_name ASC']

      # instance_eval'ing the proc on Employee... getting Employee#first_name + ' ' + Employee#last_name
      column('Full name') { first_name + ' ' + last_name }

      # Employee#role
      column 'Role'

      # formatted as currency where available (currently only XLSX output)
      column 'Salary', :type => :Currency
    end
  end

Header and footer print styles

You can apply formatting to the XLSX and PDF output formats according to what the underlying libraries support:

class Tps < Report
  # [...]

  # Where 72 = 1 inch
  format_pdf(
    :stamp => File.expand_path('../acme_letterhead/report_template_landscape.pdf', __FILE__),
    :body => { :width => (10*72), :header => true },
    :document => {
      :top_margin => 118,
      :right_margin => 36,
      :bottom_margin => 72,
      :left_margin => 36,
      :page_layout => :landscape,
    }
  )

  # Whatever is supported by https://github.com/seamusabshere/xlsx_writer
  format_xlsx do |xlsx|
    xlsx.quiet_booleans!
    acme_logo = xlsx.add_image(File.expand_path('../acme_letterhead/acme_logo.emf', __FILE__), 118, 107)
    acme_logo.croptop = '11025f'
    acme_logo.cropleft = '9997f'
    brighterplanet_logo = xlsx.add_image(File.expand_path('../acme_letterhead/brighterplanet_logo.emf', __FILE__), 116, 36)
    xlsx.header.left.contents = acme_logo
    xlsx.header.right.contents = 'Corporate TPS Reporting Program'
    xlsx.footer.left.contents = 'Confidential'
    xlsx.footer.center.contents = [ 'Powered by ', brighterplanet_logo ]
    xlsx.footer.right.contents = :page_x_of_y
    xlsx.page_setup.top = 1.5
    xlsx.page_setup.header = 0
    xlsx.page_setup.footer = 0
  end
end

Wishlist

Finalize the DSL - do you like it?
Finish documenting all the methods
Make sure XLSX output format renders on all versions of Microsoft Office above 2007

What blog is this?

Safety in Numbers is Brighter Planet's blog about climate science, Ruby, Rails, data, transparency, and, well, us.

Who's behind this?

We're Brighter Planet, the world's leading computational sustainability platform.

Who's blogging here?

Patti Prairie CEO