How to Create a Drilldown Using Ruby on Rails and JQGrid

November 9, 2009 § Leave a comment

My team was recently given the task to update our web-based HTML reporting system so that a customer could drill down on data aggregated by a report key to see the detailed, non-aggregated data. To accomplish this, we chose to use the excellent JQGrid widget with the treegrid option. Our online reporting system is implemented using Ruby on Rails and Ruport. This blog post describes how to load a Ruport table into the JQGrid treegrid widget.

Imagine that we’re building a budget tracking application that records purchases. Each purchase has three attributes: category, name, and price. We want to build a report to summarize by category and the ability to see more detail for any given category.

For the sake of simplicity, we’ll mock data by creating it right in the controller using Ruport. In practice, we’d get this from a model.

def index
    @t = Table(%w(category name price))
    @t << ['Tools', 'hammer', 10.33]
    @t << ['Tools', 'wrench', 3.2]
    @t << ['Tools', 'sonic screwdriver', 150]
    @t << ['Restaurants', 'Joes sandwich bar', 33.33]
    @t << ['Restaurants', 't Friethuis', 44.78]
    @t << ['Utilities', 'gas', 127.47]
    @t << ['Utilities', 'electric', 25.60]
    @t << ['Utilities', 'phone', 120.44]
end

Now, we can add this to our view

<%=@t.to_html%>

to yield the following functional, but ugly report.

category name price
Tools hammer 10.33
Tools wrench 3.2
Tools sonic screwdriver 150
Restaurants Joes sandwich bar 33.33
Restaurants t Friethuis 44.78
Utilities gas 127.47
Utilities electric 25.6
Utilities phone 120.44

Now we’ll spruce the report up a with JQGrid. First download the widget from http://www.trirand.com/blog. At the dowload page, select jqGrid, formatting, and TreeGrid. Following the instructions on how to install the widget in js/install.txt. Also, if you don’t already have a jquery UI theme, roll one from the theme roller. To get the grid into the view, add the following javascript

$(function() {
    jQuery("#treegrid").jqGrid({
    treeGrid: true,
    treeGridModel : 'adjacency',
    ExpandColumn : 'name',
    url: '/category_drilldowns.xml',
    datatype: "xml",
    mtype: "GET",
    colNames:["id","Category", "Price"],
    colModel:[
    {
        name:'id',
        index:'id',
        width:0,
        hidden:true,
        key:true
    },
    {
        name:'name',
        index:'name',
        width:200
    },
    {
        name:'price',
        index:'price',
        align:'right',
        width:200,
        formatter:'currency',
        formatoptions:{decimalSeparator:".", thousandsSeparator: ",", decimalPlaces: 2}
    },
    ],
    height:'auto',
    footerrow: true,
    userDataOnFooter: true,
    headerrow: false,
    ExpandColClick: true,
    rowNum: -1
    });

and some HTML

<table id="treegrid" class="scroll" cellpadding="0" cellspacing="0"></table>

to get this.

Picture 2

JQGrid treegrid currently only supports loading data using AJAX, so we need to update our controller action to pass the XML data that it is expecting. Let’s update the controller action to do this.

def index
    @t = mocked_model
    respond_to do |format|
      format.html
      format.xml  # index.xml.builder   
    end
  end

  private
  def mocked_model
    t = Table(%w(category name price))
    t << ['Tools', 'hammer', 10.33]
    t << ['Tools', 'wrench', 3.2]
    t << ['Tools', 'sonic screwdriver', 150]
    t << ['Restaurants', 'Joes sandwich bar', 33.33]
    t << ['Restaurants', 't Friethuis', 44.78]
    t << ['Utilities', 'gas', 127.47]
    t << ['Utilities', 'electric', 25.60]
    t << ['Utilities', 'phone', 120.44]

    return t
  end
end

And create a builder index.xml.builder

def write_row(xml, row, id, parent_id=nil)
  xml.tag! "row" do
    xml.tag! "cell", id
    ['name', 'price'].each do |col_name|
      xml.tag! "cell", row[col_name]
    end
    xml.tag! "cell", parent_id ? 1 : 0              
    xml.tag! "cell", parent_id ? parent_id : 'NULL'
    xml.tag! "cell", parent_id ? 'true' : 'false'  
    xml.tag! "cell", 'false'                       
  end
end

xml.instruct!

xml.tag! "rows" do
  id=0
  Grouping(@t, :by => 'category').each do |n, g|
    write_row(xml, 
                    {'name' => n, 
                     'price' => g.sigma('price')},
                    id)
    parent_id = id
    id++
    g.each do |r|
      write_row(xml, r, id, parent_id)
      id++
    end
  end
end

And here we are!

Picture 1

Tagged: , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

What’s this?

You are currently reading How to Create a Drilldown Using Ruby on Rails and JQGrid at Jamie's Big Software Adventure.

meta

%d bloggers like this: