Export commands

myDBR allows report data to be exported to various formats: Excel, PDF, CSV, JSON, and PHP serialized. Export options exist for extra functionality for the exports.

Exporting a report requires adding &export=format to the URL or using a magic report parameter inExportFormatSet. Supported export formats are:

For JSON data, additional URL parameters can be used to define the JSON format used.


dbr.export.options - Set export option
dbr.wkhtmltopdf - Pass command line parameters to wkhtmltopdf command
dbr.calc.excel - Define an Excel native formula for a column
dbr.blob - Read file from the database
dbr.export.json - Keeps the JSON formatted column as JSON in JSON export


select 'dbr.export.options', option, value
select 'dbr.wkhtmltopdf', command_line_parameters
select 'dbr.calc.excel', ColumnReference, excel_formula
select 'dbr.blob', file_extension, filename
select 'dbr.export.json', ColRef [, ColRef...]]

Export options

See also defaults.php for more options.

Available page sizes for PDF (wkhtmltopdf) export:

Note that you can also use wkhtmltopdf options --page-height and --page-width to define a custom page size.

Available page sizes for Excel export:

PDF With wkhtmltopdf

myDBR supports wkhtmltopdf if installed on the server. wkhtmltopdf uses the Webkit rendering engine to convert content to PDF. Please see Optional installations on how to install wkhtmltopdf into your server.

When wkhtmltopdf is installed, PDF exports will support all HTML and JavaScript code generated (even with dbr.html).

By default, wkhtmltopdf export uses the header defined by the mydbr/user/export_header.php as '--header-html' option in wkhtmltopdf. If this option is overridden with a parameter to 'dbr.wkhtmltopdf', a defined export header will be used. If you leave the '--header-html' -option as empty, no header is used.

PDF With WeasyPrint

myDBR supports WeasyPrint if installed on the server. WeasyPrint is visual rendering engine for HTML and CSS that can export to PDF. Please see the WeasyPrint documentation on how to install WeasyPrint into your server.

WeasyPrint handled purely the HTML to PDF conversion. It does not run the JavaScript code.

WeasyPrint headers and footers are determined by a CSS file (by default mydbr/user/weasyprint.css, you can override it with the stylesheet option).

Options example

select 'dbr.export.options', 'orientation', 'landscape';
select 'dbr.export.options', 'paper_size', 'A3_EXTRA_PAPER';
select 'dbr.export.options', 'zoom_scale', 50;
select 'dbr.export.options', 'font', 'Verdana';
select 'dbr.export.options', 'font_size', 11;
select 'dbr.export.options', 'autosize', 0; /* Turn off autosize */
select 'dbr.export.options', 'pagebreak', 1;
select 'dbr.export.options', 'disable', 'pdf,excel';
select 'dbr.wkhtmltopdf', "--header-html ''"; /* Do not use header in export using wkhtmltopdf */

Using export format parameter

The report format can also be controlled with the magic parameter inExportFormatSet. This will let the user to choose the report output format. No need for any changes in the report code.

create procedure sp_DBR_production_summary`( 
inCategory tinyint,
inExportFormatSet varchar(4)

-- ...report content


Excel calculation

You can use Excel calculations in Excel export.

select 'dbr.calc', 'total', '[A]*[B]';
select 'dbr.calc.excel', 'total', '=indirect(address(row(),column()-2))*indirect(address(row(),column()-1))';

select 10 as 'A', 20 as 'B', null as 'total';

Memory usage for export

The export feature may use a lot of memory, especially with complex Excel documents. If you run into problems with export functionality, try to increase the 'memory_limit'-parameter in php.ini. See the error message about the required memory.

XML export

The generated XML file will include all the result sets of the query. An example XML report and output:

select 'dbr.export.options', 'xml.declaration', '<?xml version="1.0" encoding="utf-8"?>';
select 'dbr.export.options', 'xml.root_tag', 'dataroot xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance" xsi:noNamespaceSchemaLocation="filmlist.xsd"';

/* Will define the result set name in XML */
select 'dbr.resultclass', 'films';
/* Use these two columns as attributes */
select 'dbr.export.options', 'xml.tag_attributes', 'id', 'release_year';
/* Name the row in XML */
select 'dbr.rowclass', 'rowclass';
/* Do not show the rowname as data */
select 'dbr.hidecolumn', 'rowclass';
select 'dbr.export.options', 'xml.element_wrapper', 'pr_phone', 'phone', 'type="PRPhone"', 'phonenumber';
select 'dbr.export.options', 'xml.element_wrapper', 'production_phone', 'phone', 'type="ProductionPhone"', 'phonenumber';

  film_id as 'id',
  title as 'Title[filmtitle]', 
  'film' as 'rowclass',
from mydata.film;

You can define the result set's name with the dbr.subtitle command. If none is given, myDBR will generate one. Column attribute names and data types are defined by the report. In the example, the column name 'title' has been changed to 'filmtitle'.

<?xml version="1.0" encoding="utf-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance" xsi:noNamespaceSchemaLocation="filmlist.xsd">
  <film id="1" release_year="2006">
    <filmtitle>ACADEMY DINOSAUR</filmtitle>
      A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockeless
  <film id="2" release_year="2007">
    <filmtitle>AMERICAN BACON</filmtitle>
    <description>Newly released film</description>
    <phone type="PRPhone">
    <phone type="ProductionPhone">

JSON formats

By default, a myDBR report uses JSON array as JSON format. You can use change the default format with extra URL parameters json_force_object and json_object_array. To change the default JSON format to object format add the following line to .

$mydbr_defaults['export']['json']['force_object'] = true;

Sample report:

select film_id, title, release_year
from film f
limit 0,2;

The default JSON export format is a JSON array:


With &json_object_array=1 is added to the URL, the format is JSON object format:

  "0": {
    "title":"ALAMO VIDEOTAPE",
  "1": {
    "title":"ALASKA PHANTOM",

With &json_force_object=1 is added to the URL, the format is JSON object array format:

      "title":"ALAMO VIDEOTAPE",
      "title":"ALASKA PHANTOM",

myDBR keeps the JSON columns in the query allowing to create hierarchical JSON. The MySQL/MariaDB query:

select f.film_id, f.title, JSON_ARRAYAGG(JSON_OBJECT('id', fa.actor_id, 'name', concat(a.first_name,' ', a.last_name)))  as 'actors'
from film f 
  join film_actor fa on fa.film_id=f.film_id
  join actor a on a.actor_id=fa.actor_id
where f.film_id between 2 and 3
group by f.film_id, f.title;

Will produce JSON:

  "0": {
    "film_id": 2,
    "title": "AMERICAN BACON'abc",
    "actors": {
      "0": {
        "id": 19,
        "name": "BOB FAWCETT"
      "1": {
        "id": 85,
        "name": "MINNIE ZELLWEGER"
  "1": {
    "film_id": 3,
    "title": "ADAPTATION HOLES",
    "actors": {
      "0": {
        "id": 2,
        "name": "NICK WAHLBERG"
      "1": {
        "id": 19,
        "name": "BOB FAWCETT"
      "2": {
        "id": 24,
        "name": "CAMERON STREEP"

SQL Server and Sybase do not have JSON data types but can use JSON and convert data to JSON format. To take advantage of JSON functionality, one needs to tell myDBR that the result set will be JSON. (The optional 'format' parameter for json-option formats the JSON when displayed in HTML).

select 'dbr.resultset.options', 'json', 'format'
select co.*, ci.*
from country co 
  join city ci on ci.CountryCode=co.Code
where co.name in ('United States', 'Netherlands')

Will produce JSON:

        "Code": "NLD",
        "Name": "Netherlands",
        "Continent": "Europe",
        "Region": "Western Europe",
        "SurfaceArea": 41526,
        "IndepYear": 1581,
        "Population": 15864000,
        "LifeExpectancy": 78.3,
        "GNP": 371362,
        "GNPOld": 360478,
        "LocalName": "Nederland",
        "GovernmentForm": "Constitutional Monarchy",
        "HeadOfState": "Beatrix",
        "Capital": 5,
        "Code2": "NL",
        "ci": [
                "ID": 5,
                "Name": "Amsterdam",
                "CountryCode": "NLD",
                "District": "Noord-Holland",
                "Population": 731200
                "ID": 6,
                "Name": "Rotterdam",

Excel formula

With formula-option, you can add an Excel-formula into a cell for exports. Command accepts both direct cell references or it can be attached to the existing query column via ColumnReference.

The direct cell reference formula takes an Excel cell reference as a parameter and a formula to calculate. Optional columnstyle can be used.

select 'dbr.export.options', 'formula:A10','=A5*2', '# ##0';

With ColumnReference a calculation can be added to an existing result set.

/* For Excel output */
select 'dbr.export.options', 'formula:[Calculated]','=INDIRECT(ADDRESS(ROW(),COLUMN()-1))*2', '# ##0';

/* For HTML output */
select 'dbr.calc', 'Calculated', '[value]*2';

select ID, value, null as 'Calculated';
from mydata;

Large exports in Excel/CSV

When you do large exports (>10,000 rows), it is faster to use CSV export than Excel. Excel document (which internally is zipped XML-files) is much more resource intensive.

If your report contains a simple export of a table, you can take advantage of the direct_mode-option which will skip most of the myDBR code checks (like crosstabs etc), resulting with faster export.

The direct cell reference formula takes an Excel cell reference as a parameter and a formula to calculate. Optional columnstyle can be used.

select 'dbr.export.options', 'direct_mode',1;

Sample export times on a large export (50,000 rows with 12 columns):

Format Time
Excel 2m 51s
Excel direct_mode 2m 14s
CSV 35s
CSV direct_mode 11s

Reading files from the database

With dbr.blob you can read files stored in blob/varbinary column.

select 'dbr.blob', 'pdf', 'document.pdf';

select data
from document_blob
where type='pdf';