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.

Commands

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

Syntax

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

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 export with built-in PDF export and 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.

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 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)
)
begin

-- ...report content

end

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 uses more memory that the rest of the application. 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';


select  
  film_id as 'id',
  title as 'Title[filmtitle]', 
  description, 
  release_year, 
  last_update,
  'film' as 'rowclass',
  pr_phone,
  production_phone
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 datatypes 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>
    <description>
      A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockeless
    </description>
    <last_update>2010-08-18T10:22:10</last_update>
  </film>
  <film id="2" release_year="2007">
    <filmtitle>AMERICAN BACON</filmtitle>
    <description>Newly released film</description>
    <last_update>2010-09-23T15:22:55</last_update>
    <phone type="PRPhone">
      <phonenumber>+1-202-555-0120</phonenumber>
    </phone>
    <phone type="ProductionPhone">
      <phonenumber>+1-202-555-0156</phonenumber>
    </phone>
  </film>
</dataroot>

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 following line to user/defaults.php.

$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:

[
  [
    1,
    "ALAMO VIDEOTAPE",
    2006
  ],
  [
    1,
    "ALASKA PHANTOM",
    2006
  ]
]

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

{
  "0": {
    "film_id":1,
    "title":"ALAMO VIDEOTAPE",
    "release_year":2006
  },
  "1": {
    "film_id":2,
    "title":"ALASKA PHANTOM",
    "release_year":2006
  }
}

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

[  
   {  
      "film_id":1,
      "title":"ALAMO VIDEOTAPE",
      "release_year":2006
   },
   {  
      "film_id":2,
      "title":"ALASKA PHANTOM",
      "release_year":2006
   }
]

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 datatypes, 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 wil 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')
for JSON AUTO

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 existing query column via ColumnReference.

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;

Reading files from 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';