dbr.accordion - Organize report elements with accordions
dbr.accordion.close - Marks the end of an accordion
dbr.accordion.class - Define your own accordion-style with a class - Define your own accordion-style
dbr.columnfilter - Add a filter to a column. The filter hides non-matching rows
dbr.columnfilter.remember - Remember the user filter choise between page refreshes
dbr.column.filter - An alias for the dbr.columnfilter
dbr.columnhighlite - Add a filter to a column. The filter highlites matching rows
dbr.column.title - Define the title for a column in cases where the content of the column is dynamic
dbr.collapse - Collapse selected columns to make a more compact view
dbr.collapse.template - Use template for collapsed data
dbr.colsort - Alias for dbr.sortcol - Show database results side by side - Change compare behavior
dbr.divify - Use DIV's instead of tables in the output - Add additional header row to group columns
dbr.keepwithnext - Makes next element to be placed side by side with the current one
dbr.lockcolumns - Lock leftmost columns when resultset does not fit into the screen. The 'fixedrow' options allows variable height columns
dbr.no_data - Define a placeholder for queries that do not return any data
dbr.nosort - Disable sorting on specified columns - Make parameter input visible in the report
dbr.pageview - Show a row from the database in a page view
dbr.refresh - Make the report automatically refresh itself at a given interval or just once if no parameters are given
dbr.resultset.options - Allows combining multiple result sets by skipping header/footer
dbr.rownum - myDBR replaces the command with the row number in the result set
dbr.rownum.set - Initialize the rownum-value - Disable/enable search & export for a given result set
dbr.scrollable - Make result set scrollable with a fixed number of rows
dbr.sort - Set initial sorting order
dbr.sortcol - Sort a column-based value from another column
dbr.sortmethod - Override the default sort method
dbr.sortorder - Override the default initial sort order. The default is 'intelligent'
dbr.sort.remember - Remember the user's sort order
dbr.header.tooltip - Define tooltip for the header
dbr.toggle - Set columns/rows togglable
dbr.toggle.options - Set toggle options
dbr.tooltip - Define the cell's tooltip
dbr.pager - Attach the pager component to a table report - Organize report elements with tabs - Define your own tab style with class - Mark the tab element as closed - Switch to the next unpopulated tab - Set's the default tab by name or by tab order number (0=first, 1=second) - Define your own tab style
dbr.localization - Override the default localization


select 'dbr.accordion', 'accordion name' [, 'jQuery accordion options']
select 'dbr.accordion.close'
select 'dbr.accordion.class', 'css_classname'
select '', 'css style definition'
select 'dbr.columnfilter', ColumnReference[[, 'select' [, 'desc', [list of values]] | 'multiselect' | 'text'], 'default="defaults"]
select 'dbr.columnfilter.remember', 1
select 'dbr.column.filter', ColumnReference[[, 'select' [, 'desc', [list of values]] | 'multiselect' | 'text'], 'default="defaults"]
select 'dbr.columnhighlite', ColumnReference[, 'select' ['desc'] | 'multiselect' | 'text']
select 'dbr.column.title', ColumnReference, title
select 'dbr.collapse', ColumnReference [,ColumnReference... ]
select 'dbr.collapse.template', template_name
select 'dbr.colsort', ColumnReferenceTo, ColumnReferenceFrom
select ''
select '' [no_compare ColumnRef, ColumnRef... | compare_column ColumnRef_Target, ColumnRef_Compare ]
select 'dbr.divify' [, 'css_classname']
select '', 'name', ColumnReferenceFrom, ColumnReferenceTo
select 'dbr.keepwithnext' [, pixels]
select 'dbr.lockcolumns', ColumnReferenceToLockTo [, 'fixedrow' ]
select 'dbr.no_data', 'message', 'css_classname'
select 'dbr.nosort', ColumnReference [,ColumnReference... ]
select ''
select 'dbr.pageview'
select 'dbr.refresh' [[,seconds], ['counter', ['no_toggle']]]
select 'dbr.resultset.options', 'skip_header_footer' | 'skip_header' | 'skip_footer' | 'json'
select 'dbr.rownum', data from table
select 'dbr.rownum.set', value
select '', 1
select 'dbr.scrollable' [, number_of_rows_to_show ]
select 'dbr.sort', ColumnReference, 'asc' | 'desc'
select 'dbr.sortcol', ColumnReferenceTo, ColumnReferenceFrom
select 'dbr.sortmethod', ColumnReference, 'text' | 'number' | 'currency' | 'ipaddress' | 'url' | 'percent' | 'date' | 'time' | 'rownum'
select 'dbr.sortorder', ColumnReference, 'intelligent' | 'desc' | 'asc'
select 'dbr.sort.remember'
select 'dbr.header.tooltip', ColumnReferenceTo, 'Tooltip text'
select 'dbr.toggle', [columnslist, JSON_toggle]
select 'dbr.toggle.options', 'speed' | 'columns' | 'scroll' | 'select_all', value
select 'dbr.tooltip', ColumnReferenceTo, ColumnReferenceFrom
select 'dbr.pager' [, nbr of rows per page, [pager text] ]
select '', 'tab name' [, ajax_call_for_content | myDBR report ]
select '', 'css_classname'
select ''
select ''
select '', tab
select '', 'css style definition'
select 'dbr.localization', 'thousand_separator' | 'decimal_separator' | 'date_format' | 'time_format', localization_format


JSON_toggle is JSON array with following items
  • name Checkbox title
  • class (optional) Checkbox CSS-class (in case you wish to toggle the item via JavaScript)
  • resultclass (optional) CSS-class that will be added to the table when a checkbox is clicked
  • callback (optional) A JavaScript function to be called when a checkbox is clicked. The parameter passed to the functon is the checkbox
  • cols (optional) An array of columns to be toggled when a checkbox is clicked
  • rows (optional) An array of rowclasses to be toggled when a checkbox is clicked
Toggle options
  • speed A string or a number determining how long the animation will run
  • columns How many columns will the toggle dialog show
  • scroll Determines if the toggle dialog shows a scrollbar (value=1)
  • select_all Determines if the toggle dialog shows a Select All/Deselect All checkbox in the header



The dbr.pageview command is used to show one row in a record-style format.


Select a single row as a result set:

  select title, description, release_year, special_features, last_update 
  from f
  where film_id=1;

Result is a single row.

when we apply the dbr.pageview command we get the result out in the following, more readable layout:

  select 'dbr.pageview';

  select title, description, release_year, special_features, last_update 
  from f
  where film_id=1;

Collapsible Columns


Sometimes, the number of columns in the report can make it too wide and difficult to read. With collapse functionality, you can hide some of the columns from the initial display but still allow the user to show them by clicking the row.

The collapse functionality is much like the 'inline' linked report, except that in collapsible, no linked report is required. The 'inline' linked report provides more flexibility in the layout.

By adding the CSS class collapse_open to a row, the row will be opened by default. You can also use a template with collapsible rows via the command dbr.collapse.template.

  select 'dbr.collapse', 'ID', 'Year', 'Date', 'Image';
  select 'dbr.colstyle', 'Image', '[width:150px]image';


Placing Elements Side by Side


By default, a myDBR report lays out the elements on separate lines. If you wish to place elements next to each other, use the dbr.keepwithnext command. Optional parameters define the space around the elements. For more information, refer to layouts.

Creating an Automatic Row Number


You can make myDBR create automatic row numbers for you with the dbr.rownum command. The following report will produce output for two columns, where the first column is a row number column.

select 'dbr.rownum' as 'Row number', title
from film;

Adding Additional Column Header Group

Group normal query columns (not crosstab) with an additional header row by using the command.

select '', 'Used', 'hours_used', 'cost_used';
select '', 'Budget', 'hours_budget', 'cost_budget';

    phase as 'Phase', 
    hours_used as 'Hours[hours_used]', 
    cost_used as 'Cost[cost_used]', 
    hours_budget 'Hours[hours_budget]', 
    cost_budget as 'Cost[cost_budget]'

Set Column Sorting and Override the Default Sorting Method for a Column

dbr.sort, dbr.sortmethod, dbr.colsort

By default, a myDBR report makes columns sortable. Users can sort the columns by clicking the header. myDBR offers multilevel support and a variety of sorting methods. A sorting method understands the data and carries out intelligent sorting based on the data. The dbr.sort command defines a column and a sorting order. If multiple dbr.sort commands are issued, a multilevel sort is performed.

Set the predefined sorting order (ascending based on the amount and ascending based on the IP address) and ensure that the first column, which contains both IP addresses and hostnames, is sorted alphabetically. The IP address column is sorted based on the actual IP address.

select 'dbr.sort', 'amount', 'desc';

select 'dbr.crosstab', 'Quarter';
select 'dbr.sum', 'amount';
select 'dbr.hsum', 'amount';
select 'dbr.hidecolumn', 'down'; // This is used for sorting

select 'dbr.sortmethod', 'Host', 'text';
select 'dbr.sort', 'amount', 'asc';
select 'dbr.sort', 'ip', 'asc';
select 'dbr.sortcol', 'Down since', 'down'; // Sort 'Down since'-column based on datetime from column 'down'

       status as 'Status',
       host_ip as 'Host', 
       ip_address as 'IP Address[ip]', 
       date_format(down, '%D %b, %H:%i') as 'Down since',
       quarter as 'Quarter', 
       amount as '[amount]'
from mydb.mytable;

Disabling Sorting on Selected Columns


If you decide that a column or the result set as a whole should not be sortable, you can disable sorting on selected or all columns. With no columns specified or the column number set to zero, dbr.nosort disables sorting altogether. If you have a very large result set, disabling sorting can speed up processing, as sorting is done via JavaScript in the browser, and a large dataset with a slow machine/browser may slow down report initialization.

Disable the sorting on image column:

select 'dbr.nosort', 'Preview';

select 'Part 1' as 'Part name', 
       'WDC-2345-1' as 'Code', 
       'dbr.html:<img src="">' as 'Preview'
select 'Part 2', 
       'dbr.html:<img src="">'
select 'Part 3', 
       'dbr.html:<img src="">';

Now user can sort columns 1 and 2, but sorting on column 3 is disabled:

Search/Export Visibility

By default, myDBR's objects export and search/filter functionality are placed behind a small triangle. You can change this behavior by using If set to a value of 1, the export/filter area is shown by default. If set to a value of 0, the filter is hidden altogether.

select '', 1;

Note that you can set the export and search/filter functionality to be on by default in userstyle.css by adding: { display: block; }

Note that you can set the search/export feature to be on by default by setting $mydbr_defaults['search'] = true; in the defaults.

Auto Refresh


When used, the report will automatically refresh itself at a given interval.

The first optional parameter for the command is 'seconds', indicating the interval after which the report is refreshed. When 'seconds' is not provided, the refresh is done immediately. An immediate refresh is used, for example, when an editable report returns the dbr.refresh command, after which the edited values in the report will be displayed.

The second optional parameter is the constant 'counter'. When applied, the calculated seconds will be shown at the top of the report, and the user is able to pause the counter by clicking on it.

The third optional parameter is the constant 'no_toggle'. When applied, a counter will be visible, but the user cannot stop the counter.

select 'dbr.refresh', 5; /* Refresh after 5 seconds */
select 'dbr.refresh', 50, 'counter'; /* Refresh after 50 seconds. User can stop the counter by clicking on it. */
select 'dbr.refresh', 50, 'counter', 'no_toggle'; /* Refresh after 50 seconds. User cannot stop the counter. */

Set Dynamic Column Title


If a column contains dynamic data, its title can also be dynamic.

if (in_Gross>0) then
  select 'dbr.column.title', 'grossnet', 'Gross';
  select 'dbr.column.title', 'grossnet', 'Net';
end if

select Name, if ( in_Gross>0, gross, net ) as '[grossnet]'
from mytable;

Show Parameters Within the Report

By default, a myDBR report allows parameters to be changed if the user clicks the "Change report parameters" button. The command makes the values visible by default.

select '';

Use DIV's Instead of Tables


By default, myDBR shows report results as HTML tables. With the dbr.divify command, you can use DIVs. This allows for more flexible formatting in CSS and post-processing in JavaScript.

An example query:

select 'dbr.divify', 'divifythis';
select 'dbr.hidecolumns', 'divrow';
select 'dbr.rowclass', 'divrow';

select name, population, code, 'divrow'
from countries;

This will produce the following HTML code:

<div class="divifythis">
  <div class="divrow">
    <div class="name">Maldives</div>
    <div class="population">286 000</div>
    <div class="code">MDV</div>
  <div class="divrow">
    <div class="name">Brunei</div>
    <div class="population">328 000</div>
    <div class="code">BRN</div>

Paging Result Sets


myDBR offers a client-side pager for browsing larger result sets.

The pager allows you to embed larger table elements into the report and still control the size of the element. It will display a pager component on top of the result set, enabling the user to navigate the result set using the pager.

select 'dbr.colstyle', 'release', '%d'; // Formatting the year -> will not use the thousand separator

select 'dbr.pager', 5;

select title as 'Title',
       release_year as 'Release Year[release]',
       rental_rate as 'Rental Rate',
       length as 'Length'
from film;

The pager component is a client-side feature, intended for use in cases where the size of the result set is manageable on the client. For very large result sets, consider limiting the number of rows or using linked reports. (Users are seldom able to handle very large sets anyway).

Using Tabs to Organize Your Report Content,,,

You can create tabs in your report to break content into multiple sections that can be swapped to save space. The content of the tab can either consist of static report elements, or the content can be fetched using Ajax when the tab is clicked.

To use tabs, you first declare each tab with the command. The first parameter for the is the tab's name. The second optional parameter is a URL for an Ajax call or a myDBR report name (with parameters) to be called when the tab is clicked. If no second parameters are given, the content of the tab is expected to come later in the report.

Once the tabs are declared, the content of each non-Ajax/myDBR report tab needs to be provided. Once the content of a tab is defined, the next tab is selected with the command. The command selects the next tab that has not been assigned an Ajax URL or a myDBR report.

If you wish to define your own styles for a tab, the commands and can be used. The most common use case for styles is to set the tab width to a fixed width while allowing the content width between tabs to vary.

/* Declare the tabs. The second tab is fetched via ajax when the user clicks the tab. In case of myDBR report add &embed=1 to the end of the URL */

select '', 'First tab';
select '', 'myDBR report tab', 'sp_DBR_linked_report', v_param_1;
select '', 'Ajax tab', 'report.php?r=548&m=1&h=55589f83ec7daa84432118425499bd5875f6d6fa&embedi=1';
select '', 'Fourth tab';

/* Now the tabs are declared, so the content of 'First tab' is populated */

select 'dbr.title', 'First tab title';

select ID, Title, release_year as 'Year'
from film;

/* moves to next unpopulated tab which is the 'Fourth tab' */
select '';

call sp_DBR_another_report( inMyParam );

select '';

Using Accordions to Organize Your Report Content

dbr.accordion, dbr.accordion.close,, dbr.accordion.class

An accordion is a UI element (similar to a horizontal tab) that is best suited for displaying a limited number of items.

To use an accordion, start a new accordion with the dbr.accordion command. Each subsequent dbr.accordion command adds a new accordion element to the existing one. The content of each accordion comes after the 'dbr.accordion' command. Once finished with the accordion, close the element with the dbr.accordion.close command.

You can customize the accordion behavior by passing the jQuery accordion options as the second parameter to the dbr.accordion command. The 'active' option defines the default open section (starting from zero). To have all accordion sections closed by default, you can use the 'active:false' option. To enable multiple accordion sections to be open at the same time, you can use the 'multiopen:true' option. When defining multiple options, separate them with a comma, for example, 'active:false, multiopen:true'.

You can modify the default accordion style with '' and 'dbr.accordion.class'. The former takes a CSS style as a parameter and the latter a CSS class. Any definition made here is added to the accordion wrapper div.

select 'dbr.text', "Before accordion", 'comment';

select '', 'width:700px';

select 'dbr.accordion', 'First accordion';

select 'dbr.text', "This is part of 'First accordion'-content", 'comment';

/* Some other content for first accordion */

select 'dbr.accordion', 'Second accordion';

call sp_DBR_accordion_2_content();

select 'dbr.accordion', 'Third accordion';

select ID, Title

select 'dbr.accordion.close';

select 'dbr.text', "After accordion", 'comment';

Placeholder for Nonexistent Data


You can define a placeholder for queries that return no data. The default behavior is that myDBR outputs the query header without any rows, and in the case of a chart, it will create a placeholder chart element. With the dbr.no_data command, you can display your own message instead.

If no CSS class is given, myDBR uses the default 'div.no_data' style. You can define your own by adding it as a second parameter.

select 'dbr.no_data', "No films were found", 'comment';

select ID, Title
where film_creation>'2020-01-01';

Sticky Headers

By default, a myDBR report uses sticky headers, where the report table element headers stay on the screen when the user scrolls the document. You can disable this by adding the class 'fixedheader' to the result set.

Lock Leftmost Columns


When you have a wide result set and you scroll to the right, you can lock the leftmost columns to be always visible. The dbr.lockcolumns command's first parameter defines the last column to be locked. The optional second parameter, 'fixedrow', keeps row height constant and can be used when data in a cell spans multiple rows.

select 'dbr.lockcolumns', 'title';

from film f;

Using Column Filters


Column filters allow filtering rows based on the column's data. A column filter can be either a select list, a multiselect list, or a text field. A select list can also be sorted in descending order using the optional parameter desc.

By default select and multiselect show possible values from the column. You can add additional valies (if your editing reports add new ones). A default setting can set the initial value for the filter. For select, text the default values are single values defined by default=value. multiselect filter's default are defined as a JSON array default=["First","Second"]

Column filter, by default, keeps the width of the table constant to keep the UI constant. If you want the table width to be dynamic (based on content), add resultclass flexible-width-columnfilter to the result set.

select 'dbr.columnfilter', 'Category', 'select';
select 'dbr.columnfilter', 'Title', 'text';

select as 'Category',
    title as 'Title'
from film f
    join film_category fg on f.film_id=fg.film_id
    join category g on g.category_id =fg.category_id;

Setting initial defalt values:

select 'dbr.columnfilter', 'Category', 'select', 'default=Classics';
select 'dbr.columnfilter', 'Title', 'text', 'default="The Shawshank Redemption"';
select 'dbr.columnfilter', 'Year', 'multiselect', 'default=["1994","1995"]';

select as 'Category',
    title as 'Title',
    release_year as 'Year'
from film f
    join film_category fg on f.film_id=fg.film_id
    join category g on g.category_id =fg.category_id;

Additional values in case it's a editable report adding new values

select 'dbr.columnfilter', 'Category', 'select', 'Classics', 'Horror', 'Additional category';

select as 'Category',
    title as 'Title',
    release_year as 'Year'
from film f
    join film_category fg on f.film_id=fg.film_id
    join category g on g.category_id =fg.category_id;

Toggle Columns/Rows


With dbr.toggle, users can toggle the visibility of selected columns/rows. When dbr.toggle is used without parameters, users are shown a list of columns whose visibility they can toggle. If you want the user to just toggle selected columns, list the ColumnReferences as parameters. This gives you full control over the items to be toggled/postprocessed.

select 'dbr.toggle';

select film_id as 'ID', title as 'Title', description as 'Description'
from film f;

Example With Complete Control of Rows/Columns/Styles

You can pass a JSON array as a parameter to dbr.toggle. Each element in the array represents one toggle. The first one toggles the rows with the 'account' CSS class. The checkbox will have a CSS class 'rowbutton', and a CSS class 'toggledrows' is added/removed to/from the table when toggled. Finally, the JavaScript function is called to allow full customization of the toggle. The other item, 'Show/Hide columns', simply toggles the selected columns.

Instead of showing the toggle menu for the user, buttons are displayed, and the checkboxes are clicked via JavaScript.

-- Hide the toggle icon, we'll use buttons instead
select 'dbr.css', '.toggle_img {display:none}';

select 'dbr.purehtml', '<input type="button" value="Hide/show detail rows" onclick="$(''.rowbutton'')[0].click();">';
select 'dbr.purehtml', '<input type="button" value="Hide/show extra columns" onclick="$(''.colbutton'')[0].click();">';

select 'dbr.javascript', 'function myfunc(checkbox) { alert("Callback"); }';
select 'dbr.toggle','[
   "name":"Show/Hide rows",
   "callback": "myfunc"
select 'dbr.toggle','[
  "name":"Show/Hide columns",
   "cols":["thisyear_ga", "thisyear_format", "lastyear", "chg"]

select ...
from pl_statement...

Initial report

Output when columns and rows are toggled, and the CSS class is applied to the result:

Resultset Options


With result set options, you can combine multiple result sets into one table. A result set can be the result of a query or a template that you insert into the report element. It is also used with the 'json' option in SQL Server and Sybase to indicate that the data will be JSON.


With, you can display result set rows side-by-side and optionally format the minimum, maximum, and equal values from different rows.

By default, a CSS class (compare_min, compare_max, compare_same) is applied to each cell. You can choose not to compare a column by using, 'no_compare', 'ColumnRef'. Additionally, you can choose the column used for comparison for a selected column: select, 'compare_column', 'ColumnRef_Target', 'ColumnRef_Compare'.

select 'dbr.css', '.compare_min {background: green; color:white;}';
select 'dbr.css', '.compare_max {background: red; color:white}';
select 'dbr.css', '.compare_same {background: #d0edfe;}';

select 'dbr.colstyle', 'Description', '[width:200px]';
select 'dbr.colstyle', 'Release year', '%d';

select '';

-- Do not compare Title and film_id columns
select '', 'no_compare', 'Title', 'film_id';

-- Compare Description-column based on film_id
select '', 'compare_column', 'Description', 'film_id';

  f.title as 'Title',
  f.description as 'Description',
  f.release_year as 'Release year', 
  f.rental_rate as 'Rental rate',
  f.length as 'Length',
from f
where f.film_id in (1, 3, 8);