Skip to content

Home

Jump to bottom Edit New page
ericperez edited this page Sep 9, 2015 · 8 revisions

#Preamble

If you have data in your Web application, then you (or somebody else) probably wants to know something about it, so you write reports to query the data and present it to users in various formats. This usually involves writing code to get data out of a database (or other data source,) processing it, then presenting it through some Web UI. The problem is that pretty much everyone has their own way of writing reports and most implementations are cumbersome, need a lot custom code for each report, and require lengthy 'release cycles' to deploy.

The main idea behind Squerly is that reports should be easy to write, easy to consume, and quick to deploy/publish. The data source (flat file, SQL database, Google Spreadsheet, etc.) is the heart of any report. Writing SQL queries against a database (or simply loading data from a flat file) is usually not the most time-consuming part of building reports (it's part of the 20% custom code.) What takes the most development time is writing all the supporting code to consume and filter input data, process the report results, aggregate it, and return it to the user in a format that's usable and understandable. Squerly is built in such a way that it can handle data from many sources, flexible enough that you can run any PHP code to process it, and output the results of all reports you write in a plethora of output formats (CSV, XML, JSON, HTML Table, and more to come in the near future.)

#Introduction

From a developer's perspective, writing reports generally follows a standard pattern:

  • Gathering input from the user while validating and sanitizing it
  • Running some initial code to set up variables, environment state, pre-processing, plug in user-input, etc.
  • Loading data from a data source (such as MySQL database or CSV file)
  • Running post-processing code on the output to get it in a format ready for user consumption (changing column labels, aggregating data, etc.)
  • Outputting the results of the report to the user in various formats (HTML table, CSV, JSON, various types of Graphs, etc.)

From an end-user's perspective, consuming reports usually consist of:

  • Loading an HTML form that allows the user to change certain input parameters for the report (Start/End Dates, etc.,)
  • Clicking a 'Run Report' or 'Get Results' button,
  • And lastly, getting data back as a table, chart, spreadsheet, or other representation.

Squerly attempts to abstract out all the commonalities that most reports share into a framework that allows for quick development and deployment of reports. Because Squerly treats reports as objects with properties that can be stored in a traditional database or SQLite file, instead of blocks of code that live in PHP files you gain the advantage of being able to deploy reports as quickly as you can save them (and because they simply exist as rows in a database) creating reports becomes very 'cheap.' Instead of having to write a bunch of code to create a new report (which probably looks surprisingly similar to code that you wrote for previous reports with minor differences) at the most basic level all you have to do is add a new report record through the Squerly 'Add Report' UI and click 'Save' and you're done.

#Requirements

  • PHP 5.3.7 or newer (tested on up to 5.5.9)
  • Composer
  • Apache Web Server
  • I have only tested Squerly so far on Mac OS X and Linux (Even on Raspberry Pi!,) but it's very possible it could also work on Windows

#Installation

#Setup

  • Create an Apache virtual host with a DocumentRoot that points to the Squerly root folder (the path that contains 'index.php') which looks something like this:

<VirtualHost *:80> SetEnv ENVIRONMENT development ServerName yoursquerlyhost.com ServerAlias www.yoursquerlyhost.com DocumentRoot /var/www/squerly/ DirectoryIndex index.php <Directory /var/www/squerly> AllowOverride FileInfo </Directory> </VirtualHost>

NOTE: Currently the code will only work from the root of a domain/sub-domain! e.g. http://subdomain.example.com/ANYTHING_HERE/squerly/report will not work but http://subdomain.example.com/squerly/report will.

See the Apache documentation if you don't know how to do this.

  • Install the required PHP modules: sudo apt-get install php5 php5-sqlite php5-mysql php5-gd (on Linux; use Brew or MacPorts on Mac)

  • Run "composer install" in the root folder (See the Composer Docs for help if you don't know how to do this.)

  • Rename config/squerly.config.php.default to squerly.config.php and update any necessary settings

  • Edit the config/report_db_connections.json file with your database settings. This file contains the database configuration strings for all databases that are available for SQL-based reports to use (you can put as many database connections in this file as you want.) Currently MySQL, PostgreSQL, and SQLite databases are supported.

  • Load the hostname of your virtual host in your browser with '/squerly/report' suffixed to it e.g. if your domain name is www.example.com, enter http://www.example.com/squerly/report into your browser.

  • At this point you should see a (mostly-empty) screen that has a link to create a new report; click on that to start your path to Report nirvana.

  • Currently there is no authentication built in to the application (yet!) so it's recommended that you add HTTP authentication to the folder where Squerly is installed to prevent unauthorized use (you will have a big security problem if you don't do this.) See here for more information on adding HTTP authentication to Apache. It is also recommended to use Squerly on a server configured with SSL (to prevent third-party evesdropping on your report queries and/or output.)

#Creating your first report

  • Load /squerly/report/add on the virtual host where you installed Squerly
  • Currently Squerly can create reports from CSV files, XML files, and SQL databases [MySQL, PostgreSQL, and SQLite]
  • Enter a name into the 'Name' input field
  • Type 'csv', 'xml', or 'sql' into the 'Type' field depending on what type of report you intend to create (this is lame, I know--soon this will be a drop-down)
  • In the 'DB Adapter' field, enter the name of a database that you put in the config/report_db_connections.json file previously (this field should also be a drop-down, I know, but it just hasn't been built yet.)
  • Enter a valid SQL SELECT statement into the 'Query' field if you chose type 'SQL', or enter a file path or URI to an XML or CSV file into the 'Input Data URI' field if you chose one of those formats.
  • Load /squerly/report/load/1 to open the report 'run' screen.

#Report Output Formats

  1. table = HTML table enhanced with 'Datatables' (this is the default if no context is explicitly chosen)
  2. csv = Comma-delimited file (download) for spreadsheet import
  3. json = JSON-encoded string
  4. xml = XML representation of report results
  5. kml_points = Allows the report results to be rendered on a Google Map
  6. MORE OUTPUT FORMATS COMING SOON!

##What about input parameters?

  • You can allow user input to be place into your reports by using template tags in the query.
  • Currently tags take on this format {[tag_name]}
  • Whatever you name the tag, {[article_id]} for instance, will be populated with values from $_REQUEST on the /report/render/{ID} URI e.g. /squerly/report/render/1?article_id=5
  • Squery will see 'article_id=5' from $_REQUEST, create a bind parameter placeholder where the template tag was {[article_id]} and pass the value '5' into the query as a bind parameter.
  • This feature is in its infancy and will be greatly expanded in the future to allow you to build entire forms simply by placing template tags in your report query and/or input URI!
  • Note: All parameter form elements are required to be filled in by the end-user at report load time.

More to come shortly! Check back soon.

Add a custom footer
Add a custom sidebar
Clone this wiki locally