Appearance

Commands

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
dbr.accordion.style - Define your own accordion style
dbr.column.filter - Add a client side filter for a column
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 more compact view
dbr.collapse.template - Use template for collapsed data
dbr.colsort - Alias for dbr.sortcol
dbr.compare - Show database results side by side
dbr.compare.options - Change compapare behavior
dbr.divify - Use DIV's instead of tables in output
dbr.header.group - Add additional header row in order 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
dbr.no_data - Define a placeholder for queries that do not return any data
dbr.nosort - Disable sorting on specified columns
dbr.parameters.show - Make parameter input visible in report
dbr.pageview - Show a row from the database in a page view
dbr.refresh - Make 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
dbr.search - Disable/enable search & export for a given result set
dbr.scrollable - Make result set scrollable with fixed number of rows
dbr.sort - Set initial sorting order
dbr.sortcol - Sort a column based a 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 user's sort order
dbr.header.tooltip - Define tooltip for the header
dbr.tooltip - Define cell's tooltip
dbr.pager - Attach the pager component to a table report
dbr.tab - Organize report elements with tabs
dbr.tab.class - Define your own tab style with class
dbr.tab.close - Mark the tab element as closed
dbr.tab.next - Switch to next unpopulated tab
dbr.tab.selected - Set's the default tab by name or by tab order number (0=first, 1=second)
dbr.tab.style - Define your own tab style

Syntax

select 'dbr.accordion', 'accordion name' [, 'jQuery accordion options']
select 'dbr.accordion.close'
select 'dbr.accordion.class', 'css_classname'
select 'dbr.accordion.style', 'css style definition'
select 'dbr.column.filter', ColumnReference[, 'select' | 'text']
select 'dbr.column.title', ColumnReference, title
select 'dbr.collapse', ColumnReference [,ColumnReference... ]
select 'dbr.collapse.template', template_name
select 'dbr.colsort', ColumnReferenceTo, ColumnReferenceFrom
select 'dbr.compare'
select 'dbr.compare.options' [no_compare ColumnRef, ColumnRef... | compare_column ColumnRef_Target, ColumnRef_Compare ]
select 'dbr.divify' [, 'css_classname']
select 'dbr.header.group', '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 'dbr.parameters.show'
select 'dbr.pageview'
select 'dbr.refresh' [[,seconds], ['counter', ['no_toggle']]]
select 'dbr.resultset.options', 'skip_header_footer' | 'skip_header' | 'skip_footer'
select 'dbr.rownum', data from table
select 'dbr.rownum.set', value
select 'dbr.search', 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.tooltip', ColumnReferenceTo, ColumnReferenceFrom
select 'dbr.pager' [, nbr of rows per page, [pager text] ]
select 'dbr.tab', 'tab name' [, ajax_call_for_content | myDBR report ]
select 'dbr.tab.class', 'css_classname'
select 'dbr.tab.close'
select 'dbr.tab.next'
select 'dbr.tab.selected', tab
select 'dbr.tab.style', 'css style definition'

Pageview

dbr.pageview

Explanation

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

Example

Select a single row as a result set:

select title, description, release_year, special_features, last_update 
from mydb.film 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 mydb.film f
where film_id=1;

Collapsible columns

dbr.collapse

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

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

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

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

  select 
      Title, 
      Description, 
      ID, 
      Year,
      Date,
      Image
  from mydb.data;
  

Placing elements side by side

dbr.keepwithnext

Explanation

By default myDBR lays out the elements each on their own line. If you wish to place elements next to each other use the dbr.keepwithnext-command. Optional parameters define the space around the elements. See more information at See layouts for details.

Creating an automatic row number

dbr.rownum

Explanation

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

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

Adding additional column header group

dbr.header.group

Explanation

Group normal query (not crosstab) columns with additional header row by using dbr.header.group-command

select 'dbr.header.group', 'Used', 'hours_used', 'cost_used';
select 'dbr.header.group', 'Budget', 'hours_budget', 'cost_budget';

select 
    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]'
from mydb.data;

Set column sorting and override the default sorting method for a column

dbr.sort, dbr.sortmethod, dbr.colsort

Explanation

By default myDBR 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 an 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.

Example

Set the predefined sorting order (ascending based on amount and ascending based on ip_address) and make sure the first column which contains both ip_addresses and hostnames are sorted alphabetically. The ip_address column is sorted based on the 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'

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

Disabling sorting on selected columns

dbr.nosort

Explanation

If you decide that a column or the result set as whole should not be sortable, you can disable the 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, you can disable the sorting to speed up things as the sorting is done via JavaScript in the browser and a large dataset with a slow machine / browser may slow the report initialization.

Example

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="http://www.mysite.com/parts/WDC-2345-1.jpg">' as 'Preview'
union
select 'Part 2', 
       'WDC-2345-2', 
       'dbr.html:<img src="http://www.mysite.com/parts/WDC-2345-2.jpg">'
union
select 'Part 3', 
       'WDC-2345-3', 
       'dbr.html:<img src="http://www.mysite.com/parts/WDC-2345-3.jpg">';

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

Search / export visibility

dbr.search

Explanation

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

select 'dbr.search', 1;

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

Note that you can set the search / export feature to be on by default by setting search = true in the defaults

Auto refresh

dbr.refresh

Explanation

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

The first optional parameter for the command is 'seconds' 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 report will be displayed.

The second optional parameter is constant 'counter'. When applied, the calculated seconds will be shown in 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 is not able to 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

dbr.column.title

Explanation

When a column contains dynamic data the column title can also be dynamic


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


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

Show parameters within report

dbr.parameters.show

Explanation

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

select 'dbr.parameters.show';

Use DIV's instead of tables

dbr.divify

Explanation

By default myDBR shows results as HTML tables. With dbr.divify-command you can use DIV's. This allows 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>
  <div class="divrow">
    <div class="name">Brunei</div>
    <div class="population">328 000</div>
    <div class="code">BRN</div>
  </div>
</div>

Paging result sets

dbr.pager

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

With the pager you can 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. With this the tser is able to navigate the result set using the pager.

Example

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 component, so it is intended to be used in cases where the size of the result set is manageable in the client. For very large result sets consider limiting the number of rows or use linked reports. (Users are seldom able to handle very large sets anyway).

Using tabs to organize your report content

dbr.tab, dbr.tab.next, dbr.tab.style, dbr.tab.class

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 dbr.tab-command. The first parameter for the dbr.tab is the tab's name. The second optional parameter is an URL for 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 the tab needs to be provided. For each non Ajax/myDBR report tab, the content needs to be defined. Once the content of the tab is defined, next tab is selected with the dbr.tab.next-command. The dbr.tab.next-command selects the next tab which has not been Ajax URL or myDBR report defined.

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

Example

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

select 'dbr.tab', 'First tab';
select 'dbr.tab', 'myDBR report tab', 'sp_DBR_linked_report', v_param_1;
select 'dbr.tab', 'Ajax tab', 'report.php?r=548&m=1&h=55589f83ec7daa84432118425499bd5875f6d6fa&embedi=1';
select 'dbr.tab', '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;

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

call sp_DBR_another_report( inMyParam );

select 'dbr.tab.close';

Using accordions to organize your report content

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

An accordion is a UI element (much like a horizontal tab), which 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 dbr.accordion.close-command.

You can customize the accordion behavior by passing the jQuery accordion options as 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 'active:false', option. To enable multiple accordion sections to be open at same time, you can use the 'multiopen:true' option . When defining multiple options, you can separate them with comma: 'active:false,multiopen:true'.

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

Example

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

select 'dbr.accordion.style', '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
from mydb.film;

select 'dbr.accordion.close';

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

Placeholder for nonexistent data

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

Example


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

select ID, Title
from mydb.film
where film_creation>'2020-01-01';

Sticky headers

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

Lock leftmost columns

dbr.lockcolumns

When you have a wide resultset and you scroll to the right, you can lock the leftmost columns to be always visible. 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.

Example

select 'dbr.lockcolumns', 'title';

select 
    title,
    description,
    release_year
from film f;

Using column filters

dbr.columnfilter

Column filters allow filtering rows based on columns data. A column filter can be either a select list or a text field.

Example

select 'dbr.column.filter', 'Category', 'select';
select 'dbr.column.filter', 'Title', 'text';

select 
    g.name 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;

Resultset options

dbr.resultset.options

With resultset options you can combine multiple result sets into one table. Resultset can be a result of a query or a template that you insert into the report element.

Compare

dbr.compare

With dbr.compare 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 dbr.compare.options, 'no_compare', 'columnRef. Additionally, you can choose the column which is used for compare for a selected column: select 'dbr.compare.options', 'compare_column', 'ColumnRef_Target', 'ColumnRef_Compare';

Example


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 'dbr.compare';

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

-- Compare Description-column based on film_id
select 'dbr.compare.options', 'compare_column', 'Description', 'film_id';

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