Part II - Creating beautiful reports in Ruby on Rails

In Part 1, we looked at the principles behind creating beautiful reports in Ruby on Rails. Please read part 1 (see below) before reading this posting.

LibreOffice

You now need to install LibreOffice.See Part I for why we are using LibreOffice. You can get the download as well as installation instructions here.

At this stage, you should really install LibreOffice on your desktop/laptop as well because you will be using it to create the template.

Placeholders
The LibreOffice template will contain placeholders that the Ruby code will then substitute at runtime for real values. These place holders are text values within the document and table column rows.

Creating the template
The beauty of this method of creating reports is that you can use LibreOffice to design the reports.

1. Start up LibreOffice.
2. Click "Text Document". This should open a new document. Note that with LibreOffice you can create template files and set the default template as we have done for our SMS Speedway mobile messaging service. This is handy if you are going to be creating many reports that should include standard features like company logo, headers, footers, etc.

For the purposes of this document, we will create a simple report. We will base it on our excellent business text messaging service, SMS Speedway, to report on text messages that were unable to be delivered (e.g. incorrect mobile number). Having written the report, we would be able to use the same procedure described here to expand the report to include statuses of other services that form a part of SMS Speedway, for example social media communications that were unsuccessful (e.g. timed out due to recipient not logged into FaceBook, Twitter, or Google Plus).

Expanding the report would be a simple case of creating a subsequent section in LibreOffice and putting in the appropriate fields as described here. Right now, we have a blank document, so let's get started.

This is what our template looks like:

Exception report template screenshot

The logo, title (SMS EXCEPTIONS REPORT) and the created field are in the header section of the document. Click this link if you would like help on how to create headers and footers in LibreOffice. What's important to note is our first bit of templating that will later be populated by our Ruby code:

Screenshot showing a marker for fixed text

[UPDATE to section below] Please note: The creator of this gem has written to me and asked that I point out that the use of fields is now redundant and won't work with certain versions of LibreOffice. You should merely use the [XXX] placeholders as plain text. I have left in this (old) method of achieving this as as some people may still find it useful for certain gem/libreoffice  combinations. But please use plain text markers first!

The entry "[TODAYS_DATE]" will be replaced by our ruby code with the current date every time the report is run. This is entered as a user field.

1. Place the cursor at the point where you wish to enter the placeholder that will later be substituted for the real value. In this case, it's where we see [TODAYS_DATE] in the image above.
2. Insert/fields/other (or ctrl-F2).
3. In the Value field, it is important to enter the placeholder name IN CAPS and encased in square brackets ("[" and "]").
4. Click the green tick and then the "insert" to insert the field.

Entering a user field

You should now see [TODAYS_DATE] as shown in the previous image.

Later, when we pass this template to the Ruby code, it will look for the text value TODAYS_DATE and it will substitute the current timestamp. An example is shown here:

Text marker with value substituted

Sections
Usually, with a report, you need to be able to group data items together. In our example, we have transmission files, meaning the files that clients can upload to the SMS Speedway server that contain messages to be sent (if they do not want to use the web site or integrate their software using our extensive API).

If a client uploads more than one transmission file, for each file, we wish to report the exceptions. This therefore forms a logical grouping, to be repeated for each file in the report.

To enable this grouping, we need to create LibreOffice sections. In our example, our section is called MAIN_SECTION. Note that there are no enclosing square brackets ("[" and "]") and that the name is in CAPs. This name (shown below) can be seen at the bottom of the document window when you have clicked into the section.

Showing the section name in LibreOffice

To insert a section, choose Insert / Section:

Inserting a section in LibreOffice

 Subsequent items such as the transmission file name and the table containing the exceptions data can now be entered in the section. This will enable the report to duplicate all the items in the section for each transmission file.

Free text placeholders
Where less precise formatting is required, you can simply add the placemarker, enclosed in square brackets ( "[" and "]") in the text. And example in our template above is this bit:

Plain text placeholder

In this case, the [USER_REF] is simply plain text as we have the rest of the line as space and no special formatting requirements,

Tables
Perhaps one of the most useful layout options is the use of tables. In our template, we have:



However, the table needs to be named. As we can see here at the bottom of the Document window:

Named table

1. Insert the table (Insert/table)
2. Create your columns, if you want to have headings, enter the headings i the first row.
3. Set the table name: Table/Table properties then click the tab Table.
4. Enter a name, it must be in CAPS, but as opposed to text and user field value strings, the table name MUST NOT be enclosed in square brackets:

Table name

Now you need to enter the column value placeholders. These, as per text and user field placeholders must be CAPS and also enclosed in square brackets ("[" and "]"). These can be seen in the image of the table above ([MOBILE], [STATUS] and [MESSAGE]).

Note
It is important to note that some items need to be encased in square brackets and others not. All placeholders must be CAPs. In general, placeholders need to be encased in square brackets but object names (section, table) do not have square brackets.

Saving the file
Save your file (file/save). You should now have your template file of type .odt.

The Ruby code
Now you need to write the Ruby code. The template handling magic is implemented in the gem odf-report. Our SMS Speedway system is based on the enterprise class Postgres DBMS so we include the Postgres gem. We also make provision for emailing reports, hence the mail gem:

require "rubygems"
require "pg"
require "odf-report"
require 'mail'

Data Classes
We read the data from the database, but we store each data item in an object such that the odf-report gem can access it and obtain the data.

For example, the report has several transmission files. The template (see above) has the [USER_REF] placeholder for the transmission file. So we define the class as follows:

class TransmitFile
  attr_accessor :user_ref, :timestamp, :msgs
  def initialize(_user_ref, _timestamp, _msgs=[])
    @user_ref=_user_ref
    @timestamp=_timestamp
    @msgs=_msgs
  end
end

Note the attr_accessor which is effectively Ruby shorthand to define the setters and getters for the variables in the class by the same name.

From the above, you can see that @msgs is an array. I.e. Each file is associated with some messages. To define the structure to hold those messages, we have:

class Msg
  attr_accessor :number, :status, :message
  def initialize(_number, _status, _message)
    @number=_number
    @status=_status
    @message=_message
  end
end

 And this means that for each message, where @currTransmitFile holds the current transmit file, we simply do this:

@currTransmitFile.msgs << Msg.new(number,status,msg)

In this manner, we can select from the database, all the transmit files that need to be in the report as an outer loop with an inner loop selecting all the relevant messages for the current transmit file and associated as shown above. Each file, once its messages have been associated with it, is then added to the @items array before the next file in the outer loop is processed:

        @items << @currTransmitFile

 We therefore end up with @items containing a list of all the transmit files that should be in the report, with each file having all the messages that should be in the report for that file associated with is in the msgs attribute.

Recap
Let's quickly recap:

• We have a .odt file, created in LibreOffice that has a template.
• The template has a report timestamp field called TODAYS_DATE
• It has a section called MAIN_SECTION
• Within that section there is a free text placeholder called USER_REF
• and there is also a table called EXCEPTION_TABLE that has three colums with placeholders in each: MOBILE, STATUS and MESSAGE.
• We also have an array of data objects of type TransmitFile with the user_ref field in it.
• Each TransmitFile has an array of objects of type Msg that have the fields number, status and message that map to the columns in the template's EXCEPTION_TABLE table field.

Merging the data into the template
Now we need do a data merge on the template. First, we need to instantiate the ODFReport module. We have called our template exceptionsReport_template.odt:

report = ODFReport::Report.new("exceptionsReport_template.odt") { |r|
.
.
}

 The above creates our report object and associates the template file with our report.

Now we need to tell ODFReport about the placeholders, tables and sections in our template:

report = ODFReport::Report.new("exceptionsReport_template.odt") { |r|
  r.add_field :todays_date, Time.now.strftime('%Y-%m-%d %H:%M:%S')

  r.add_section(:MAIN_SECTION, @items) do |s|
        s.add_field(:user_ref,:user_ref)

        s.add_table("EXCEPTION_TABLE", :msgs, :header=>false) do |t|
           t.add_column(:mobile, "number")
           t.add_column(:status, "status")
           t.add_column(:message, "message")
        end
  end

}

Note that the placeholders in our template are associated with Ruby symbols of the same name, but take careful note of the casing, it's important!

•  :todays_date is associated with [TODAYS_DATE], likewise :user_ref.
• :MAIN_SECTION is associated with MAIN_SECTION
•  EXCEPTION_TABLE with EXCEPTION_TABLE
• however the column placeholders are again lowercase that maps to uppercase placeholders in the template ( mobile, :status, :message).

add_section
The r.add_section call associates the section in the template called MAIN_SECTION with the object array @items. ODFReport will iterate through each object, creating a copy of MAIN_SECTION for every iteration, and filling in the placeholders each time if they have been associated with attributes in @items.

This is how those associations are made:

add_field
The s.add_field call associates template placeholders with values. The first parameter is the template placeholder name as described above, the second is the value.

With :todays_date, we  pass through the current time formatted exactly as it should appear. This is a fixed value.

However, for the :user_ref, we pass :user_ref as the second parameter. this is telling add_field that the attribute called user_ref in the @items item under consideration, contains the value for this placeholder.

Likewise, when we tell ODFReport about the table called EXCEPTION_TABLE in the report (add_table), the first parameter gives the table's name in the template (EXCEPTION_TABLE), the second parameter tells it that the array called msgs in the item under consideration contains the table data objects.

add_column
As with add_field, the add_column method associates a template table column placeholder with a data object attribute. Hence:
           t.add_column(:mobile, "number")

tells the ODFReport module that placholder [MOBILE] in the template is associated with the object attribute "number" in the current msgs object.

ODFReport will iterate through all the msgs, object by object, and fill in each row, be getting the attribute for each column's placeholder.

Generating the report
Now that we have associated the template placeholders with the data structure that we populated from the database, all that remains is to generate the report.

We want the report to go to a file called exceptionsReport.odt. The following call will create the report:

report.generate('./exceptionsReport.odt')

However, we want this to be a cross-platform report, readable on all devices and on all operating systems without the need for office software to be installed. The best file format is PDF, so we need to convert the .odt file into a .pdf file. This is where the magic of headless LibreOffice comes into its own:

`/usr/bin/libreoffice4.0 --headless --invisible --convert-to pdf ./exceptionsReport.odt`

The ` in Ruby executes the command as a sub-process. The result is a file called exceptionsReport.pdf.

And there you have it, your beautiful report, created as a PDF!

For your convenience, here is the working part of this code:

report = ODFReport::Report.new("exceptionsReport_template.odt") { |r|
  r.add_field :todays_date, Time.now.strftime('%Y-%m-%d %H:%M:%S')

  r.add_section(:MAIN_SECTION, @items) do |s|
        s.add_field(:user_ref,:user_ref)

        s.add_table("EXCEPTION_TABLE", :msgs, :header=>false) do |t|
           t.add_column(:mobile, "number")
           t.add_column(:status, "status")
           t.add_column(:message, "message")
        end
  end

}
report_file_name = report.generate('./exceptionsReport.odt')
`/usr/bin/libreoffice4.0 --headless --invisible --convert-to pdf ./exceptionsReport.odt`

Design without the pain
Now it is possible for your graphic designers or the marketing department to redesign the reports. Perhaps they want to market a promotion or change the font. Maybe they need to update the company contact details or change the logo.

As long as they keep the placeholder names, section names and table names the same, they can do whatever they like. It's merely a matter of replacing the template file with the new one and the report is instantly updated without any code changes required.

Customised reports, common code
Another advantage to this technique is that by specifying your template, you can create highly customised reports (e.g. with a client's logo on it) yet keep your code common.

Through careful planning, a wealth of beautiful, tailored output is possible without the headache of code impact, testing cycles and release procedures and other hoops normally associated with application releases, no matter how minor they are.

Conclusion
A major hole in Ruby and Rails' enterprise class capabilities has been filled. A large section of business software is all about the output from that software and entire teams in enterprises are devoted to just that task.

Ruby and Ruby on Rails are now enabled to play a role in serious enterprise and business information and reporting software. A new era has begun.