Export Commands

Commands

dbr.export.options - Sets export options
dbr.wkhtmltopdf - Passes command-line parameters to the wkhtmltopdf command
dbr.calc.excel - Defines a native Excel formula for a column
dbr.blob - Reads a file from the database
dbr.export.json - Keeps a JSON-formatted column as JSON in JSON export

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
select 'dbr.export.json', ColRef [, ColRef...]]

PDF With wkhtmltopdf

myDBR supports wkhtmltopdf if installed on the server. wkhtmltopdf uses the Webkit rendering engine to convert content to PDF. Please refer to the Optional installations section for instructions on installing wkhtmltopdf on your server.

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

By default, wkhtmltopdf exports use a header defined by mydbr/user/export_header.php as the '--header-html' option in wkhtmltopdf. You can override this option using the parameter dbr.wkhtmltopdf to define a custom export header. Leaving the '--header-html' option empty will result in no header being used.

PDF With WeasyPrint

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

WeasyPrint handles pure HTML to PDF conversion and does not execute JavaScript code.

WeasyPrint headers and footers are defined by a CSS file (by default mydbr/user/weasyprint.css), which can be overridden using 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 using the magic parameter inExportFormatSet. This allows the user to choose the output format for the report without requiring any changes to 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 may consume a significant amount of memory, especially when exporting complex Excel documents. If you encounter issues with the export functionality, consider increasing the 'memory_limit' parameter in php.ini. Refer to the error message indicating the required memory for guidance.

XML Export

The generated XML file includes all result sets from the query. Below is an example XML report and its 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 using the dbr.subtitle command. If none is provided, myDBR will generate one automatically. Column attribute names and data types are defined by the report. For instance, 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 arrays as the JSON format. You can change the default format using the extra URL parameters json_force_object and json_object_array. To change the default JSON format to object format, add the following line:

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

Adding &json_object_array=1 to the URL changes the format to JSON object format:

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

Adding &json_force_object=1 to the URL changes the format to JSON object array:

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

myDBR retains JSON columns in the query, enabling the creation of hierarchical JSON structures. For example, in MySQL/MariaDB:

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;

This produces the following 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 SAP ASE do not have native JSON data types, but they can store and convert data to JSON format. To take advantage of JSON functionality, you need to inform myDBR that the result set will be in JSON format. Optionally, the 'format' parameter for the json option can be used to format the JSON when displaying it 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

This produces the following 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 the formula option, you can add an Excel formula to a cell for exports. This command accepts either a direct cell reference or a ColumnReference for an existing result set column.

A direct cell reference formula takes an Excel cell reference and a formula as parameters. An optional columnstyle can also be applied.

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 performing large exports (>10,000 rows), using CSV export is generally faster than Excel. Excel documents, which are internally zipped XML files, are more resource-intensive.

If your report involves a simple export of a table, you can optimize performance by using the direct_mode option. This skips most of the myDBR code checks (like crosstabs), resulting in faster exports.

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

Using dbr.blob, you can read files stored in a blob/varbinary column.

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

select data
from document_blob
where type='pdf';