Skip to main content

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

###Export options

  • orientation - Set page orientation ('landscape', 'portrait') in PDF and Excel. Use this in the first result set only. The export document can have only one orientation.
  • paper_size - Set page size for PDF and Excel. See the list below for available options.
  • zoom_scale - Set PDF/Excel zoom scale (value 50 is 50% zoom).
  • gridlines - Disable/enable Excel gridlines.
  • font - Set the font used in Excel.
  • font_siz- e - Set the font size in pixels used in Excel.
  • autosize - Turn Excel autosize on/off. Will try to fit the output into a cell.
  • pdf - With value 'weasyprint', myDBR uses WeasyPrint PDF generation instead of wkhtmltopdf.
  • stylesheet, file - Override the default user/weasyprint.css. File path is relative to the myDBR root.
  • pagebreak - Produces a page break in a PDF document and in an Excel worksheet.
  • worksheet - Creates a new worksheet with the given name (value) in Excel.
  • position - Places the object at a given position. Example position is "C6".
  • disable - Disable the chosen export types. Accepts a comma-separated list of disabled export types (pdf, excel).
  • direct_mode - Reduces memory consumption and speeds up export by using reduced functionality for Excel and CSV (suitable for plain tables).
  • debug - Displays the wkhtmltopdf command and the generated HTML file for debugging possible errors in user-generated HTML, as wkhtmltopdf may fail to produce a PDF from erroneous HTML.
  • header.title - Define the PDF export header title.
  • header.title.style - Define the PDF export header title CSS style.
  • include_image - Defines whether charts are included in the Excel export.
  • csv.delimiter - Allows setting a CSV delimiter on a per-report basis.
  • csv.decimal_point - Allows setting a CSV decimal point on a per-report basis.
  • csv.enclosure - Allows setting a CSV enclosure on a per-report basis.
  • csv.date_format - Allows setting a CSV date format on a per-report basis.
  • csv.time_format - Allows setting a CSV time format on a per-report basis.
  • csv.charset - Allows setting a CSV charset on a per-report basis.
  • csv.line_ending - Allows setting a CSV line ending on a per-report basis.
  • csv.header - Allows setting CSV header visibility on a per-report basis.
  • csv.enclose_string_with_leading_or_trailing_space - Allows setting CSV export to quote strings with leading or trailing spaces.
  • csv.linefeed_between_resultsets - If the report contains multiple result sets, this determines whether the result sets are separated with a line feed.
  • csv.use_bom_in_utf - Use BOM (Byte Order Mark) in the CSV file.
  • csv.skip_formatting - Ignore column formatting in CSV export.
  • csv.filename_extension - Change the CSV export file's extension.
  • csv.strip_tags - Strip tags in CSV export.
  • excel.aggregate_formula, 1/0 - Enable/disable Excel native formulas in aggregate summary rows.
  • xml.declaration - Define the XML declaration.
  • xml.header - Define additional header tags.
  • xml.root_tag - Defines the XML root tag. If not defined, a generic "report" is used.
  • xml.tag_attributes comma_separated_colreflist - Turns values into data attributes. The parameter is a comma-separated list of column references.
  • ``xml.element_wrapper column_to_wrap, wrapper_tag, wrapper_attributes, replacement_tag` - Wraps an element with a new element.
  • xml.compatibility_mode, 1 Use old, pre-myDBR 4.0, XML format.
  • filename Define the export filename within the report.
  • colwidths Define the Excel column widths in pixels.
  • formula Define an Excel formula in a cell. Format: 'formula:A2'[, 'colstyle'] or 'formula:ColumnReference'[, 'colstyle'].
  • position.row row Set the Excel export row for a result set (select 'dbr.export.options', 'position.row', 1;).
  • position.column Column Set the Excel export column for a result set (select 'dbr.export.options', 'position.column', 'E';).
  • position.row.add nbr_of_rows Move the result set the specified number of rows down (select 'dbr.export.options', 'position.row.add', 3;).
  • position.column.add nbr_of_columns Move the result set the specified number of columns to the right (select 'dbr.export.options', 'position.column.add', 2;).
  • tab_title.hide Do not show tab titles in Excel export. Use this in the first result set only.
  • content-disposition.xxx, 'attachment' | 'inline' Set the export Content-Disposition header to either 'attachment' or 'inline'. You can also use the URL parameter 'content-disposition' with the same options. Determines whether the report will be shown in the browser (inline) or downloaded as a file (attachment).

See also defaults.php for more options.

Available page sizes for PDF (wkhtmltopdf) export:​

  • A0 (841 x 1189 mm)
  • A1 (594 x 841 mm)
  • A2 (420 x 594 mm)
  • A3 (297 x 420 mm)
  • A4 (210 x 297 mm, 8.26 x 11.69 inches)
  • A5 (148 x 210 mm)
  • A6 (105 x 148 mm)
  • A7 (74 x 105 mm)
  • A8 (52 x 74 mm)
  • A9 (37 x 52 mm)
  • B0 (1000 x 1414 mm)
  • B1 (707 x 1000 mm)
  • B2 (500 x 707 mm)
  • B3 (353 x 500 mm)
  • B4 (250 x 353 mm)
  • B5 (176 x 250 mm, 6.93 x 9.84 inches)
  • B6 (125 x 176 mm)
  • B7 (88 x 125 mm)
  • B8 (62 x 88 mm)
  • B9 (33 x 62 mm)
  • B10 (31 x 44 mm)
  • C5E (163 x 229 mm)
  • Comm10E (105 x 241 mm, U.S. Common 10 Envelope)
  • DLE (110 x 220 mm)
  • Executive (7.5 x 10 inches, 190.5 x 254 mm)
  • Folio (210 x 330 mm)
  • Ledger (431.8 x 279.4 mm)
  • Legal (8.5 x 14 inches, 215.9 x 355.6 mm)
  • Letter (8.5 x 11 inches, 215.9 x 279.4 mm)
  • Tabloid (279.4 x 431.8 mm)

Note that you can also use wkhtmltopdf options like --page-height and --page-width to define custom page sizes.

Available page sizes for Excel export:

  • 6_3_4_ENVELOPE
  • A2_PAPER
  • A3
  • A3_EXTRA_PAPER
  • A3_EXTRA_TRANSVERSE_PAPER
  • A3_TRANSVERSE_PAPER
  • A4
  • A4_EXTRA_PAPER
  • A4_PLUS_PAPER
  • A4_SMALL
  • A4_TRANSVERSE_PAPER
  • A5
  • A5_EXTRA_PAPER
  • A5_TRANSVERSE_PAPER
  • B4
  • B4_ENVELOPE
  • B5
  • B5_ENVELOPE
  • B6_ENVELOPE
  • C3_ENVELOPE
  • C4_ENVELOPE
  • C5_ENVELOPE
  • C65_ENVELOPE
  • C6_ENVELOPE
  • C
  • D
  • DL_ENVELOPE
  • E
  • EXECUTIVE
  • FOLIO
  • GERMAN_LEGAL_FANFOLD
  • GERMAN_STANDARD_FANFOLD
  • INVITE_ENVELOPE
  • ISO_B4
  • ISO_B5_EXTRA_PAPER
  • ITALY_ENVELOPE
  • JAPANESE_DOUBLE_POSTCARD
  • JIS_B5_TRANSVERSE_PAPER
  • LEDGER
  • LEGAL
  • LEGAL_EXTRA_PAPER
  • LETTER
  • LETTER_EXTRA_PAPER
  • LETTER_EXTRA_TRANSVERSE_PAPER
  • LETTER_PLUS_PAPER
  • LETTER_SMALL
  • LETTER_TRANSVERSE_PAPER
  • MONARCH_ENVELOPE
  • NO10_ENVELOPE
  • NO11_ENVELOPE
  • NO12_ENVELOPE
  • NO14_ENVELOPE
  • NO9_ENVELOPE
  • NOTE
  • QUARTO
  • STANDARD_1
  • STANDARD_2
  • STANDARD_PAPER_1
  • STANDARD_PAPER_2
  • STANDARD_PAPER_3
  • STATEMENT
  • SUPERA_SUPERA_A4_PAPER
  • SUPERB_SUPERB_A3_PAPER
  • TABLOID
  • TABLOID_EXTRA_PAPER
  • US_STANDARD_FANFOLD

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';
© 2007 - 2026 myDBR.com. All Rights Reserved.