The goal behind creating a reporting framework is

1. To allow easy addition of varity of reports without affecting or modifying existing reports.
2. To ensure that all the reports have consistant behavior and UI i.e. sorting, export, print, grouping

To add a new report perform following steps. In the below steps we will create a Users By Course report.

First step is to create a SQL query that will get all the data that is required for a particular report.

Example: This is the query for getting all the data required by users by Course for a particular company

SELECT courses.title,CONCAT_WS(' ', users.firstname, UPPER(MID(users.lastname, 1,1))) AS Name,enrollments.created_at,SUM(progress_scores.percentage_score)/COUNT(*) AS progress_percentage, enrollments.completed_at, rs.percentage_score AS score, rs.id  
FROM `enrollments` 
INNER JOIN users ON users.id = enrollments.user_id 
INNER JOIN courses ON enrollable_id = courses.id 
INNER JOIN course_contents ON course_contents.course_id = courses.id 
LEFT JOIN progress_scores ON enrollments.id = progress_scores.enrollment_id AND progress_scores.content_id = course_contents.content_id 
LEFT JOIN (SELECT  * FROM response_sets ORDER BY response_sets.created_at DESC) AS rs ON rs.enrollment_id = enrollments.id  
WHERE (enrollments.enrollable_type = 'Course' AND (users.company_id = 23)) 
GROUP BY enrollments.id 
ORDER BY enrollments.created_at DESC;

Now that we have formulated the SQL query, we need to use it in the reporting framework. Start by added a class in /app/reports folder. The newly added class should inherit Reports::Base class.

Example: Add users_by_course.rb in app/reports folder.

class Reports::UsersByCourse < Reports::Base
end

Every class that is inherited from Reports::Base class need to implement few methods. The methods are

• description_lines - Should return an array of string that will be used to display the details of current report including its current filter criteria. This will be used when the report is printed.
• setup_sql - Should return an object of Reports::Sql class. The object should be setup with proper column names, conditions, group by clause, order by clause, joins etc…
• valid_options - Should return a hash of valid filter options and its default values.

Example: Reports::UsersByCourse

Following general steps can be followed for implementing any report class.

- Define initialize method - Typically the initialize function should take 2 arguments. 1) The filter options 2) Reference to a User object that corresponds to the user who is viewing the report. Make sure that you are calling the super class' implementation of initialize by using super method.

def initialize(options, current_user)
  super options, current_user     # Call the super class implementation
  @title = "Users By Course"      # Title of the Report, which will be seen in the views.
end

- Define valid_options method - It should return a hash that defines valid filter options and its default values.

def valid_options
  DEFAULT_OPTIONS.merge(
    :from => nil,
    :to => nil,
    :company_id => nil,
    :min_score => nil,
    :max_score => nil,
    :completed_status => '',
    :sort_key => 'courses.title'
  )
end

- Define setup_sql method - Should return an object of Reports::Sql class. The object should be setup with proper column names, conditions, group by clause, order by clause, joins etc…The Reports::Sql object can be setup by breaking down the SQL query that was formulated in Step 1. For example

def setup_sql
  Reports::Sql.build_sql do
    add_from "enrollments"     # Add the name of table present in the FROM clause in your SQL statement
 
    # Add columns present in the SELECT clause
    add_column "courses.title", "Course", {:group => true}    # If column need to be grouped in UI then set :group option to true
    add_column "CONCAT_WS(' ', users.firstname, UPPER(MID(users.lastname, 1,1))) AS Name", "Name"
    add_column "enrollments.created_at", "Enrolled Date", {:format => :date}         # If column need to be formatted as Date in UI then set :format to :date
    add_column "SUM(progress_scores.percentage_score)/COUNT(*) AS progress_percentage", "Progress", {:format => :percent}  # If column need to be formatted as Percentage in UI then set :format to :percent
    add_column "enrollments.completed_at", "Completed Date", {:format => :date}
    add_column "rs.percentage_score AS score", "Score", {:format => :percent}
 
    # Add the JOIN clause
    joins "INNER JOIN users ON users.id = enrollments.user_id INNER JOIN courses ON enrollable_id = courses.id INNER JOIN course_contents ON course_contents.course_id = courses.id LEFT JOIN progress_scores on enrollments.id = progress_scores.enrollment_id AND progress_scores.content_id = course_contents.content_id LEFT JOIN (SELECT  * FROM response_sets ORDER BY response_sets.created_at DESC) AS rs ON rs.enrollment_id = enrollments.id"
 
    # Add condition clause. This can be similar to how we add condition clasuse to active record find method. To build complex conditions use EZ::Where::Condition available ez-where plugin.
    conditions ["enrollments.enrollable_type = ? AND users.company_id = ?", 'Course', current_user.company.id]
 
    # Specify the group by clause
    group_by 'enrollments.id'
 
    # Specify the order by Clause
    order_by "courses.title"
  end 
end

The newly created Report class should be added to Reports::Factory class

Example add a additional case statement

when 'users_by_course'   # This string will be the name by which the report will be accessed via browser e.g. /reports/users_by_course
  Reports::UsersByCourse.new(params, current_user)

- Go to app/helpers/reports_helper.rb - Add a new report item to the array that is returned by reports_list method. For example add below code

  # 1st Argument is: The name of the report. Same as what you defined in Reports::Factory
  # 2nd Argument is: The category within which the current reports falls in. Will be used to show the report name in UI under the given category.
  # 3rd Argument is: The Title of report that will shown in Report listing page.
  ReportsHelper::ReportItem.new('users_by_course', 'Courses and Content', 'Users By Course')

- Add a new partial file to app/views/reports/filter_templates. The name of partial should be same as the report name used in its corresponding entry the Reports::Factory. For current example add app/views/reports/filter_templates/_users_by_course.html.haml. - Add appropriate html markup to the view. (Refer to one of the existing partial to find out what markup, styles and CSS classes to use)