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. 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.

Examples

We have our data set:

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 */

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.header', '<publisher>myDBR System<publisher>';
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  
  film_id as 'id',
  title as 'Title[filmtitle]', 
  description, 
  release_year, 
  last_update,
  '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 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"?>
<publisher>myDBR System<publisher>
<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>
    </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.

With 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
   }
]

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';