Php Reports

Basic Settings

reportDir is the path to the directory containing all of your report files. It is relative to the project's root.

dashboardDir is the path to the directory containing all of your dashboard configuration files. It is relative to the project's root.

cacheDir is the path to the cache directory. This directory needs write access. Some relatively long term data is stored here, so using /tmp is not recommended. This is also relative to the project's root.

default_file_extension_mapping defines the default file extensions for different report types. Here is an example:

array(
  'sql'=>'Pdo',
  'php'=>'Php',
  'js'=>'Mongo',
  'ado'=>'Ado',
  'pivot'=>'AdoPivot',
)

*Note - This mapping can be overridden on a report-by-report basis if needed.

Email Settings

On each report, there is an "Email Report" button. For this to work, an outgoing mail server needs to be set up. Here are the basic mail settings:

'email_settings'=>array(
  'enabled'=>true,
  'from'=>'reports@yourdomain.com'
)

There are 3 different methods you can use to send email.

mail - use PHP's mail function. This is the simplest method if your server is configured for it.

'email_settings'=>array(
  'enabled'=>true,
  'from'=>'reports@yourdomain.com',
  'method'=>'mail'
)

sendmail - use your system's sendmail command.

'email_settings'=>array(
  'enabled'=>true,
  'from'=>'reports@yourdomain.com',
  'method'=>'sendmail',
  'command'=>'/usr/sbin/sendmail -bs'
)

smtp - use an SMTP server. This is the most complex and flexible method. Here is a simple example:

'email_settings'=>array(
  'enabled'=>true,
  'from'=>'reports@yourdomain.com',
  'method'=>'smtp',
  'server'=>'smtp.yourdomain.com',
  'port'=>25
)

You can also specify encryption and a username and password if needed. Here is an example for using Gmail:

'email_settings'=>array(
  'enabled'=>true,
  'from'=>'youremail@gmail.com',
  'method'=>'smtp',
  'server'=>'smtp.gmail.com',
  'port'=>465,
  'username'=>'youremail@gmail.com',
  'password'=>'yourpassword',
  'encryption'=>'ssl'
)

Environments and Databases

This is where you define the data sources the reports pull from.

Php Reports supports multiple environments (e.g. "Production" and "Staging") and allows users to switch between them easily.

Within each environment, you define the database connection info. You can have as many different databases as you want. Here is an example:

'environments'=>array(
  'production'=>array(
    'pdo'=>array(
      'dsn'=>'mysql:host=localhost;dbname=test',
      'user'=>'readonly',
      'pass'=>'password',
    ),
    'mongo'=>array(
      'host'=>'mongodb.yourdomain.com',
      'port'=>'27017'
    )
  )
)

It is recommended to use readonly database users whenever possible to protect against possible database corruption.

Advanced Settings

report_formats is an array that controls the download options for a report. By default, all the supported download options are allowed:

array(
  'csv'=>'CSV',
  'xlsx'=>'Download Excel 2007',
  'xls'=>'Download Excel 97-2003',
  'text'=>'Text',
  'table'=>'Simple table',
  // An element with the value "divider" will cause a visual break in the list
  'raw data'=>'divider',
  'json'=>'JSON',
  'xml'=>'XML',
  'sql'=>'SQL INSERT command',
  'technical'=>'divider',
  'debug'=>'Debug information',
  'raw'=>'Raw report dump',
)

bootstrap_themelist is an array of available bootstrap themes for the theme-switcher. By default, all the supported themes are allowed:

array(
  'default',
  'amelia',
  'cerulean',
  'cosmo',
  'cyborg',
  'flatly',
  'journal',
  'readable',
  'simplex',
  'slate',
  'spacelab',
  'united'
)

bootstrap_theme sets the default theme. If not set, default will be used.

ga_api configures an optional Google Analytics API integration. This lets you query Google Analytics from within a PHP Report. This setting is an associative array with the following properties:

array(
  'applicationName'=>'PHP Reports',
  'clientId'=>'abcdef123456',
  'clientSecret'=>'1234abcd',
  'redirectUri'=>'http://example.com/'
)

twig_init_function is an anonymous function that lets you configure the Twig templating engine.

'twig_init_function'=> function(Twig_Environment $twig) {
  // Add custom twig plugins here...
}

PDO Reports

PDO reports consist of 1 or more SQL statements separated by semicolons. By default, the result of the last statement is used for the report. At the top of the report is a comment block with name, description, and any other report headers you need.

All PDO reports are parsed with the Twig templating language before running.

-- My Report
-- This lists all the products that cost
-- more than a given price.
-- VARIABLE: { name: "min_price", display: "Minimum Price" }

SELECT Name, Price FROM Products WHERE Price > "{{ min_price }}"

Variables and all the other headers you can use are covered in detail in the Report Headers section.

You can also display more than one result in a single report by using special SQL comments. Here is an example:

-- Multiple Datasets
-- This will display two tables

-- @dataset true
-- @title Products
SELECT * FROM Products;

-- @dataset true
-- @title Orders
SELECT * FROM Orders;

AdoDB reports

There are two types of AdoDB reports - a simple, query based report type (similar to PDO report type) and more complex, pivot table generator.

In the first case the report will contain one or more SQL queries with the last used for the report. At the top of the report is a comment block with name, description, and any other report headers you need.

All AdoDB reports are parsed with the Twig templating language before running.

-- My Report
-- This lists all the products that cost
-- more than a given price.
-- VARIABLE: { name: "min_price", display: "Minimum Price" }

SELECT Name, Price FROM Products WHERE Price > "{{ min_price }}"

Variables and all the other headers you can use are covered in detail in the Report Headers section.

In order to use different database systems different connection string have to be provided in the configuration file:

'environments'=>array(
  'main'=>array(
    'ado'=>array(
      'uri'=>'$driver://$username:$password@hostname/$database?options[=value]'
    )
  )
)

A list of supported databases can be found on AdoDB documention website followed by the explaination on DSN URI format.

A specific variation of this report type is a PivotTable report type, sometimes reffered to as Cross-Tabulations. AdoDB documentation provide a good explaination on how this type of query will be created and executed. php-reports include a specific extension to AdoDB report type and in order to simplify the use of this report without a need to create complex SQLs or logic. Still, because it is a specific type of AdoDB report, there are couple of ways to handle them in php-reports right now. The easiest would be to register this type of report in the configuration (this is currently not a default action) and the either to create a new DB configuration entry or to use a specific header for AdoDB database configuration reuse. The examples below assume the latter scenario:

'environments'=>array(
'default_file_extension_mapping' => array(
    'sql'=>'Pdo',
    'php'=>'Php',
    'js'=>'Mongo',
    'ado'=>'Ado',
    'pivot'=>'AdoPivot',
)

-- Operations per day
-- Operations per day grouped by operation type
-- OPTIONS: {
--      database: "ado"
-- }
-- VARIABLE: {
--      name: "range",
--      display: "Report Range",
--      type: "daterange",
--      default: { start: "-120 day", end: "yesterday" }
-- }

tables: blocked_accounts
rows: DATE_FORMAT(operation_date, '%Y-%m-%d') AS "Date values"
columns: operation_type
where: operation_date BETWEEN "{{ range.start }}" AND "{{ range.end }}"
#agg_field: id
#agg_label: Number of
agg_fun: SUM
show_count: true

In the example above OPTIONS header was added to point to AdoDB report type database configuration.

The report body consist of a list of parameters required to generate a cross-column query:

In all of the above parameters variables are allowed and can be used to make the report more dynamic.

MongoDB Reports

MongoDB reports consist of a javascript file that is run in the MongoDB Shell and outputs an array of JSON objects. Here is an example:

// My Other Report
// Lists all food products in a MongoDB collection
// MONGODATABASE: MyDatabase
// VARIABLE: { name: "active_only", display: "Active Items Only?", type: "select", options: ["yes","no"] }

var query = {type: 'food'};

if(active_only == 'yes') {
    query.status = 'active';
}

var result = db.FoodItems.find(query);

printjson(result);

You can include more than one dataset in a single MongoDB report by using a different return JSON format. Here's an example:

// Multiple Datasets

printjson([
  {
    title: "Foods",
    rows: [
      {
	name: "banana",
	type: "fruit"
      },
      {
	name: "bacon",
	type: "meat"
      }
    ]
  },
  {
    title: "Beverages",
    rows: [
      {
	'beverage name': "water",
	temperature: "cold"
      },
      {
	'beverage name': "coffee",
	temperature: "hot"
      }
    ]
  }
]);

PHP Reports

You can also write reports using PHP. PHP reports must output JSON data, just like MongoDB reports above.

Writing reports in PHP is especially useful for fetching data from APIs or doing complex data processing that is out of the scope of SQL. Here is an example:

<?php
//My Third Report
//This connects to the Stripe Payments api and shows a list of charges
//VARIABLE: { name: "count", display: "Number of Charges" }

if($count > 100 || $count < 1) throw new Exception("Number of Charges must be between 1 and 100");

require_once("/path/to/Stripe/Stripe.php");

$charges = Stripe_Charge::all(array("count" => $count));

$rows = array();
foreach($charges as $charge) {
    $rows[] = array(
        'Charge Id'=>$charge->id,
        'Amount'=>number_format($charge->amount/100,2),
        'Date'=>date('Y-m-d',$charge->created)
    );
}

echo json_encode($rows);
?>

Just like with MongoDB reports, you can include multiple datasets by using a different output format. Here's an example:

// Multiple Datasets

echo json_encode(array(
  array(
    'title'=>'Dataset 1',
    'rows'=>array(
      array(
	'first name'=>'John',
	'last name'=>'Smith'
      ),
      array(
	'first name'=>'Jane',
	'last name'=>'Doe'
      )
    )
  ),
  array(
    'title'=>'Dataset 2',
    'rows'=>array(
      array(
	'company'=>'Acme Inc.',
	'year started'=>'1950'
      ),
      array(
	'company'=>'Star Fleet',
	'year started'=>'2215'
      )
    )
  )
));	      

Custom Report Types

Adding your own report type is easy. Report Type classes extend ReportTypeBase and live in classes/report_types/. You can also place them in classes/local/report_types/ if you want them ignored by git.

The existing report type classes are well commented and are the best place to start when making your own.

Variable Header

This header adds a "Configure Report" section to the top of the report where the user can set variables you specify. The values of these variables are passed into the report and can be used in queries.

Examples

A simple text variable:

VARIABLE: { name: "userid", display: "User Id" }

Select variables show a dropdown menu with the defined choices.

VARIABLE: { 
  name: "category", 
  display: "Category", 
  type: "select", 
  options: ["Reptile","Mammal","Dinosaur"] 
}

Date variables are parsed with strtotime before being passed into the report. This means it supports all common date formats as well as relative values like "-1 week" and "last tuesday".

VARIABLE: { name: "start", display: "Start Date", type: "date", format: "F j, Y" }

Daterange variables use a nice datepicker widget to select a date range (similar to airline or hotel sites. It has presets for things like "yesterday", "last week", "this month", etc. The value of the variable is an object containing the properties "start" and "end". Both of these dates are in the format "Y-m-d".

VARIABLE: {
    "name": "range",
    "type": "daterange",
    "default": {
        "start": "-1 week",
        "end": "now"
    }
}

Chart Header

This header adds a graph or chart to the report. Charts are drawn using the Google Data Visualization API and many different formats are available.

Examples

For charts of type "LineChart", "BarChart", and "ColumChart", the first column is the x axis. Each following column is a data series on the y axis.

CHART: {
	"columns": ["Product", "Started Purchase", "Completed Purchase"],
	"type": "LineChart",
	"title": "Shopping Cart Abandonment",
	"width": "600px",
	"height": "400px"
}

The chart type "GeoChart" supports many different formats. Checkout https://developers.google.com/chart/interactive/docs/gallery/geochart for full documentation of the different column orders supported.

CHART: {
  type: "GeoChart",
  columns: ["State", "Number of Sales"]
}

CHART: {
  type: "GeoChart",
  columns: ["Lattitude", "Longitude", "Population"],
  markers: true
}

"AnnotatedTimeLine" charts are used to show time series data with important events marked. The first column is either a date or datetime. Many different date formats are supported, but it's safest to use either "Y-m-d" or "m/d/Y". The second column is the numeric value on the y axi

There are 2 more optional columns that add annotations for specific data points. The third column contains an annotation title (e.g. "Hottest day on record"). The fourth column contains a more detailed description. Check out https://developers.google.com/chart/interactive/docs/gallery/annotatedtimeline for full documentation.

CHART: {
  type: "AnnotatedTimeLine",
  columns: ["Purchase Timestamp", "Order Total", "Order Notes"]
}

Histograms take a single numeric column, split the x-axis into ranges, and plot the distribution of data across these ranges. Histograms work with the types "LineChart", "BarChart", and "ColumnChart".

CHART: {
  type: "ColumnChart",
  columns: ["Order Total"],
  xhistogram: true,
  buckets: 10
}

Include Header

Includes another report in the currently running one.

The included report's headers are parsed and the report contents are prepended to the current report before running.

Possible uses include:

• Creating a temp table for a set of MySql reports
• Defining helper functions for MongoDB or PHP reports
• Setting up an API connection for a PHP report

Examples

INCLUDE: {"report": "relative/to/current/report.sql"}

INCLUDE: {"report": "/relative/to/reportdir/report.sql"}

Filter Header

The Filter header modifies all the values in a column.

Some possible uses include:

• Replace an ip address with the city, state where it is located
• Star Rating filter that replaces a numberic column (1-5) with images of stars
• Right align values
• Link a user id in one report to a differnt report with that user's full account history

Examples

FILTER: {"column": "Ip Address", "filter": "geoip"}

FILTER: {
    "column": "Article", 
    "filter": "link",
    "params": {
        "blank": true,
        "display": "View Article"
    }
}

Rollup Header

The Rollup header adds a footer row to the bottom of the report containing summary information that you specify.

This header is most useful for showing sums and averages for numeric columns, but it is flexible enough to allow any kind of summary data.

Examples

ROLLUP: {
  columns: {
    "Product": "Total:",
    "Number Sold": "{{sum}}"
  }
}

ROLLUP: {
  columns: {
    "Name": "First alphabetical name - {{min}}.  Last alphabetical name - {{max}}."
  }
}

ROLLUP: {
  columns: {
    "Quantity": "
      Every Third Value: 
      {% for value in values if value is disibleby 3 %}
	{% if not loop.first %}, {% endif %}
	{{ value }}
      {% endfor %}
    "
  }
}

Options Header

The Options header lets you change miscellaneous report options that change the behavior in some way.

Formatting Header

The Formatting header lets you change how datasets are formatted.

Info Header

This Info header is used to set meta data about the report. This includes name, description, type, and timestamp.

Class Filter

The Class filter is used to add a CSS class to all table cells in a column. This is useful for things like right aligning numeric columns or changing the color or font.

The classes left, right, and center can be used to align text. In addition, any of the Twitter Bootstrap classes can be used as well (e.g. muted to make the text light gray).

You can define your own CSS classes in public/css/report.css.

Examples

FILTER: {
  filter: "class",
  column: "Number Correct",
  params: {
    class: "right"
  }
}

FILTER: {
  filter: "class",
  column: "Errors",
  params: {
    class: "text-error"
  }
}

Date Filter

The Date filter is used to mark a column as a date.

Date columns will do timezone adjustments automatically. This is useful if your database server is in a different timezone than your users. For this to work, you need to specify a time offset for each database server in your config.

You can also set the global date format for all date columns in your config. The default is "Y-m-d H:i:s".

Examples

FILTER: {
  filter: "date",
  column: "Start Date"
}

FILTER: {
  filter: "date",
  column: "End Date",
  params: {
    "format": "F j, i"
  }
}

Hide Filter

The Hide filter removes a column from a report. This is useful if you want to use a column in a chart, but don't want it actually appearing in the report.

Examples

FILTER: {
  filter: "hide",
  column: "Some Column"
}

HTML Filter

The HTML filter renders the cells as HTML. Without this filter, all values will be HTML escaped.

Examples

FILTER: {
  filter: "html",
  column: "HTML Preview"
}

GeoIP Filter

The GeoIP filter replaces an ip address with either "City, State" or "City, Country".

In addition, it allows an ip address column to be used in a GeoChart.

Examples

FILTER: {
  filter: "geoip",
  column: "Ip Address"
}

The following example shows the GeoIP filter being used with a GeoChart. The GeoChart will show a map with markers located at the latitude and longitude of the ip addresses. The size and color of the marker will indicate the revenue.

FILTER: {
  filter: "geoip",
  column: "Ip Address"
}
CHART: {
  columns: ["Ip Address", "Revenue"],
  type: "GeoChart",
  markers: true
}

Link Filter

The Link filter can be used to turn the contents of the cell into a clickable link.

Examples

FILTER: {
  filter: "link",
  column: "Article Url"
}

FILTER: {
  filter: "link",
  column: "Admin Link",
  params: {
    "display": "view admin",
    "blank": true
  }
}

Drilldown Filter

The Drilldown filter is used to link a column to another report. An example is linking the "Customer Id" field in a report to an "Account Overview" report for that customer.

Examples

In the following example, the "Customer Name" column wil be linked to the account-history.sql report and will pass in the macros in the format Id=1543&Type=customer.

FILTER: {
  filter: "drilldown",
  column: "Customer Name",
  params: {
    report: "account-history.sql",
    macros: {
      "Type": "customer",
      "Id": {
	"column": "Customer Id"
      }
    }
  }
}

Pre Filter

The Pre Filter is for preformatted text and renders the value inside of <pre></pre> tags.

Examples

FILTER: {
  filter: "pre",
  column: "Customer Address"
}

FILTER: {
  filter: "twig",
  column: "admin_link",
  params: {
    template: "<a href='http://example.com/admin/?id={{ row.product_id }}&edit=true'>{{ value }}</a>"
  }
}

FILTER: {
  filter: "bar",
  column: "Number of Orders"
}

FILTER: {
  filter: "bar",
  column: "Customer Rating",
  params: {
    "width": 50
  }
}

{
  "title": "Timezone Dashboard",
  "description": "Shows off the dashboard feature of Php Reports",
  "reports": [
    {
      "report": "php/timezones.php",
      "title": "ACST Timezone",
      "macros": {
        "region": "acst"
      },
      "format": "html",
      "newRow": false,
      "style": "max-height: 400px; overflow-y: auto; overflow-x: hidden;",
      "class": "col-md-6"
    },
    {
      "report": "php/timezones.php",
      "title": "CDT Timezone",
      "macros": {
        "region": "cdt"
      },
      "format": "chart",
      "newRow": false,
      "class": "col-md-6"
    }
  ]
}