dbr.pageview

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

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

  select 'dbr.pageview';

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

dbr.collapse

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

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

dbr.keepwithnext

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.

dbr.rownum

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;

dbr.header.group

Group normal query columns (not crosstab) with an additional header row by using the 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;

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'

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;

dbr.nosort

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="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:

dbr.search

By default, myDBR's objects export and search/filter functionality are placed 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 filter 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 adding: div.search { 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.

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

dbr.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';
else 
  select 'dbr.column.title', 'grossnet', 'Net';
end if


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

dbr.parameters.show

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

select 'dbr.parameters.show';

dbr.divify

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>
  <div class="divrow">
    <div class="name">Brunei</div>
    <div class="population">328 000</div>
    <div class="code">BRN</div>
  </div>
</div>

dbr.pager

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

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 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 dbr.tab.next command. The dbr.tab.next 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 dbr.tab.style and dbr.tab.class 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 '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';

dbr.accordion, dbr.accordion.close, dbr.accordion.style, 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 'dbr.accordion.style' 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 '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';

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 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
from mydb.film
where film_creation>'2020-01-01';

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.

dbr.lockcolumns

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

select 
    title,
    description,
    release_year
from film f;

dbr.columnfilter

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

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

dbr.toggle

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;

-- 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",
   "class":"rowbutton",
   "resultclass":"toggledrows",
   "callback": "myfunc"
   "rows":["account"]
  }]
  ';
select 'dbr.toggle','[
  {
  "name":"Show/Hide columns",
   "class":"colbutton",
   "cols":["thisyear_ga", "thisyear_format", "lastyear", "chg"]
  }]
';

select ...
from pl_statement...

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

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 used for comparison for a selected column: select dbr.compare.options, '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 '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);