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.

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

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

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

-- ...report content

end

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

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.

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

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 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 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')
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",
        ...

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;

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

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