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.

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.

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

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

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>

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;

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

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 the formula-option, you can add an Excel formula to a cell for exports. This command accepts both direct cell references or it can be attached to an existing query column via ColumnReference.

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;

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.

A direct cell reference formula takes an Excel cell reference and a formula as parameters. Optional column styles can also be applied.

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

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

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