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 a report can make it too wide and difficult to read. With collapse functionality, you can hide some columns from the initial display but still allow users to show them by clicking the row.

The collapse functionality is similar to the 'inline' linked report, but unlike the collapsible feature, it doesn't require a linked report. The 'inline' linked report offers more layout flexibility.

Adding the CSS class collapse_open to a row will open the row by default. Alternatively, you can use a template with collapsible rows using 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;
  

The dbr.keepwithnext command.

By default, a myDBR report lays out elements on separate lines. To place elements next to each other, use dbr.keepwithnext. Optional parameters can define the space around the elements. For more details, refer to layouts.

The dbr.rownum command.

You can instruct myDBR to generate automatic row numbers using dbr.rownum. In the following example, the report outputs two columns, with the first column displaying row numbers.

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

The dbr.header.group command.

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

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, myDBR reports make columns sortable, allowing users to sort by clicking the header. myDBR supports multilevel sorting and offers various sorting methods that intelligently handle different data types. The dbr.sort command specifies a column and its sorting order. Multiple dbr.sort commands enable multilevel sorting.

Example: Define predefined sorting orders, such as ascending by amount and ascending by IP address. Ensure that the first column, containing both IP addresses and hostnames, is sorted alphabetically. Sort the IP address column based on its actual numerical value.

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 determine that a specific column or the entire result set should not be sortable, you can disable sorting accordingly. The dbr.nosort command allows you to disable sorting on selected columns or altogether. If no columns are specified or the column number is set to zero, sorting is disabled for the entire report. Disabling sorting can be beneficial for large result sets because sorting is performed via JavaScript in the browser. This optimization can improve report initialization speed, especially when dealing with large datasets on slower machines or browsers.

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 places the objects export and search/filter functionality behind a small triangle. You can modify this behavior using the dbr.search command. Setting it to 1 will display the export/filter area by default, while setting it to 0 hides the filter altogether.

  select 'dbr.search', 1;

Additionally, you can configure the export and search/filter functionality to be visible by default by adding the following rule to your userstyle.css: div.search { display: block; }

You can also set the search/export feature to be on by default by adding $mydbr_defaults['search'] = true; to 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 elapsed seconds will be shown at the top of the report, and the user can 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 when 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. The dbr.divify command allows using DIVs instead. This enables more flexible formatting with CSS and post-processing with 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 provides a client-side pager for browsing larger result sets.

This pager allows you to embed larger table elements into the report while maintaining control over the element's size. It displays a pager component above the result set, enabling users to navigate through the results efficiently.

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 designed for managing manageable result set sizes on the client side. For very large result sets, it's advisable to limit the number of rows or utilize linked reports, as users typically struggle to handle very large sets.

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

Tabs in myDBR allow you to divide report content into multiple sections that can be switched to save space or organize information more effectively. Each tab can either contain static elements or fetch content dynamically via Ajax when clicked.

To create tabs, start by using the dbr.tab command. The first parameter is the tab's name, and the optional second parameter can be a URL for an Ajax call or the name of a myDBR report (including parameters) that will be fetched when the tab is clicked. If no second parameter is provided, the tab's content is expected to follow later in the report.

After declaring tabs, define the content for each tab that does not fetch content dynamically (via Ajax or myDBR report). Use the dbr.tab.next command to select the next tab that hasn't been assigned an Ajax URL or myDBR report.

Customize the appearance of tabs using dbr.tab.style and dbr.tab.class. These commands are useful for setting custom styles, such as fixed tab widths while allowing variable content widths between tabs.

/* 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 user interface element, similar to a horizontal tab, designed to display a limited number of items at once.

To create an accordion in myDBR, start with the dbr.accordion command. Each subsequent dbr.accordion command adds a new accordion section to the existing accordion. Place the content for each accordion section immediately after the corresponding dbr.accordion command. Close the accordion with dbr.accordion.close once all sections are defined.

Customize the behavior of the accordion using jQuery accordion options as the second parameter to dbr.accordion. For example, use the 'active' option to define the default open section (starting from zero). Use 'active:false' to have all sections closed by default. Enable multiple sections to be open simultaneously with 'multiopen:true'. Separate multiple options with commas, like 'active:false, multiopen:true'.

Adjust the accordion's style using dbr.accordion.style and dbr.accordion.class. The former applies CSS styles directly, while the latter assigns a CSS class 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. By default, myDBR outputs the query header without any rows, and for charts, it creates a placeholder chart element. Use the dbr.no_data command to display a custom message instead.

If no CSS class is specified, myDBR applies the default style defined by 'div.no_data'. To use a custom CSS class, include it as the second parameter of the command.

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 table headers stay fixed on the screen as the user scrolls the document. You can disable this behavior by adding the CSS class fixedheader to the result set.

dbr.lockcolumns

When dealing with a wide result set and scrolling horizontally, you may want to keep the leftmost columns always visible. The dbr.lockcolumns command allows you to achieve this. Its first parameter specifies the last column to remain locked. An optional second parameter, 'fixedrow', maintains a constant row height, which is useful when cell data spans multiple rows.

select 'dbr.lockcolumns', 'title';

select 
    title,
    description,
    release_year
from film f;

dbr.columnfilter

Column filters enable you to filter rows based on the data in each column. A column filter can be implemented as a select list, a multiselect list, or a text field. For select lists, you can optionally sort the values in descending order using the parameter desc.

By default, select and multiselect filters display all possible values from the column. You can append additional values, useful when editing reports add new entries. Default values for filters can be set using the default=value syntax for select and text filters, while multiselect filters use a JSON array format like default=["First","Second"].

By default, the column filter maintains a fixed table width to ensure a consistent user interface. If you prefer a dynamic table width based on content, add the resultclass attribute 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 users to toggle only 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

Result set options allow you to combine multiple result sets into one table. A result set can originate from a query or a template inserted into the report element. This feature is also utilized 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 rows of result sets side-by-side and optionally format values such as minimum, maximum, and equal from different rows.

By default, each cell is assigned a CSS class (compare_min, compare_max, compare_same). You can exclude a column from comparison using dbr.compare.options with 'no_compare', 'ColumnRef'. Additionally, you can specify a column for comparison for a selected column by using dbr.compare.options with '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);