Templates

Commands

dbr.template - Defines a template being used for the subsequent result set
dbr.template.header - Declares the header's result set
dbr.template.footer - Declares the footer's result set
dbr.template.form - Use result set columnns to set values for form fields in template
dbr.record - Record report output into a record variable be used later

Syntax

select 'dbr.template', '#template' | 'row template code' | 'header template code', 'row template code', 'footer template code', [ 'template_class' | 'none' ]
select 'dbr.template.header', data1[, data2[...]]
select 'dbr.template.footer', data1[, data2[...]]
select 'dbr.template.form', formName, columnRef
select 'dbr.record', 'begin' [, 'template_variable' ] | 'end'

Explanation

By using a template, you can fully define the result set formatting. A template is an HTML code that can contain any valid HTML code including CSS and JavaScript.

A template consists of three sections: header, row, and footer. The header-section is applied before the result set's data is processed, the row-section is repeated for each result set row and the footer-section is applied after the result set's data is processed.

Header and footer can have a single result set with dbr.template.header and dbr.template.footer. Both header and footer values can be formatted with dbr.style. Footer and header items are not editable with dbr.editable like row items.

Row items can be made editable with dbr.editable.

A template can be localized via myDBR localization references (like #{title}).

The row-template code can contain column references,which will be replaced by the result set data for each row. The syntax of the reference is #ColumnRef, where the ColumnRef is the column reference in the result set. Template variables cannot be used in the header or in the footer sections.

The template code can also contain references to the recorded variable with syntax {#variable}. In this case the variable reference is replaced with the recoded code.

A recorded variable can also be used without templates. Record a variable and use syntax {#variable} in a query and myDBR will replace the variable with the recorded element.

The template will be, by default, enclosed in a DIV with class 'template'. An additional template class can be added

Examples

Use of recorded variables in a non-template report

Use of header, row, and footer

We'll create a simple list of DIV's where the first one (green) comes from the header, in the middle are white rows and at the end footer (blue).

The header template:

<div style="color:green">Header {#d}, {#f}</div>

The row template:

<div>Row {#Data}, {#i} {#date}</div>

The footer template:

<div style="color:blue">Footer {#l}, {#s}</div>

The actual report calls the template and makes the query:

create procedure sp_DBR_TemplateFull()
begin

select 'dbr.colstyle', 'f', '%.3f';
select 'dbr.template.header', now() as d, 3.1415 as f;
select 'dbr.template.footer', 12345 as l, 'myDBR' as s;

select 'dbr.template', '#with_hdr_ftr';

select 'Data 1' as 'Data', 4321 as 'i', current_date() as 'date'
union
select 'Data 2', 5432, cast('2019-10-01' as date);


end

And the output of the report will show the squares:

Simple square

We'll create a template that will show data in squares. The color of the square is determined by the report.

First, we'll create a template row called 'square' that contains a CSS style and a DIV. The template contains two parameters #title for the square text and #color to be used in the CSS style.

	
<style>
.mycenter_div { 
vertical-align: middle;
text-align: center;
width:70px;
height:70px;
line-height: 70px;
border:1px solid gray;
margin: 5px;
display: inline-block;
box-shadow: 10px 10px 5px #888;
}
</style>

<div class="mycenter_div" style="background-color:{#color}">{#title}</div>

The actual report calls the template and makes the query:

create procedure sp_DBR_Template()
begin

select 'dbr.template', '#square';

select title, color
from mydb.data;

end

And the output of the report will show the squares:

You could make the squares movable by adding a call to jQuery in the template's footer code

<script>$(function() { $( ".mycenter_div" ).draggable().css('cursor','pointer'); });</script>

An invoice template

We'll create an invoice template which contains three templates. an invoice header, invoice rows, and invoice footer.

The invoice header is an HTML table which will contain the basic invoice data. The table can be put fully into the template row as the result set contains only one row:

<table style="margin-top: 40px;margin-bottom: 30px">
<tr>
  <td rowspan=3>
    <img style="height:60px" src="https://mydbr.com/demo/mydbr/images/logo.png" />
  </td>
  <td style="padding-left:10px" rowspan=3>
    <span style="font-size:2em">{#company}</span><br />
    #address
  </td>
  <td colspan=3>&nbsp;</td>
</tr>


<tr>
  <td style="width:220px"></td>
  <td style="text-align:right"><b>#{DATE}:</b></td>
  <td style="text-align:right">{#date}</td>
</tr>

<tr style="vertical-align: top;">
  <td style="width:220px"></td>
  <td style="text-align:right"><b>#{INVOICE} #:</b></td>
  <td style="text-align:right">{#invoice_nbr}</td>
</tr>
</table>

The invoice rows will be printed also as an HTML table. Now that there will be multiple rows in the result set, we'll separate table rows from the header and footer.

The invoice row's template header contains the column titles. In the example, the titles are localized using myDBR's localization syntax. The invoice rows are formatted using CSS.

Invoice rows template header:

<style>
.inv_rows { border: 1px solid #555; border-collapse: collapse }
.inv_rows th { background-color: #E0E0E0; border-bottom: 1px solid #555 }
.inv_rows th, .inv_rows td { padding: 5px }
.inv_rows th.right, .inv_rows td.right { text-align: right }
.inv_rows th.left, .inv_rows td.left { text-align: left }
</style>

<table class="inv_rows">
  <thead>
  <tr>
    <th class="left" style="width: 40px">ID</th>
    <th class="left" style="width: 320px">Name</th>
    <th class="right" style="width: 80px">Quantity</th>
    <th class="right" style="width: 80px">Unit price</th>
    <th class="right" style="width: 80px">Total</th>
  </tr>
  <thead>

The invoice rows template row contains definitions for each row in the result set. Values to be replaced with result set content are prefixed with '#'.

<tr class="#alternate_row_color">
  <td>{#id}</td>
  <td class="left">{#title}</td>
  <td class="right">{#quantity}</td>
  <td class="right">{#unitprice}</td>
  <td class="right">{#total}</td>
</tr>

The invoice row's template footer just closes the created table:

</table>

The invoice footer is just one row in the result set and therefore we can define it as one template row-section:

<table class="inv_rows" style="border-top:none">
  <tr>
    <td style="width: 40px"></td>
    <td style="width: 320px"></td>
    <td style="width: 80px"></td>
    <td class="right" style="width: 80px">#{SUBTOTAL}</td>
    <td class="right" style="width: 80px">{#total_rows}</td>
  </tr>
  <tr>
    <td style="width: 40px"></td>
    <td style="width: 320px"></td>
    <td class="right" style="width: 80px">#{VAT}</td>
    <td class="right" style="width: 80px">{#vat_percent}</td>
    <td class="right" >{#vat_eur}</td>
  </tr>    
  <tr>
    <td style="width: 40px"></td>
    <td style="width: 320px"></td>
    <td style="width: 80px"></td>
    <td class="right" style="width: 80px"><b>#{TOTAL}</b></td>
    <td class="right" style="width: 80px">{#total}</td>
  </tr>
</table>

The actual report contains queries for each template

create procedure sp_DBR_invoice(
in_invoice_id int
)
begin


select 'dbr.template', '#square';

select 'dbr.title', '';

select 'dbr.template', '#invoice_header';

select  name as 'company',
	address as 'address',
	invoice_date as 'date', 
	invoice_number as 'invoice_nbr'
from mydb.invoice
where invoice_id=in_invoice_id;


select 'dbr.template', '#invoice_rows';

select  rowid as 'id', 
	title as 'title', 
	quantity as 'quantity',
	unit_price as 'unitprice',
	quantity * unit_price as 'total',
	'dbr.rownum%2 ? "odd" : "even"' as 'alternate_row_color'
from mydb.invoice_rows
where invoice_id=in_invoice_id
oder by rowid;


select 'dbr.template', '#invoice_footer';

select 'dbr.colstyle', 'vat_percent', '%d %';
select 'dbr.colstyle', 'total_rows', '%.2f €';
select 'dbr.colstyle', 'vat_eur', '%.2f €';
select 'dbr.colstyle', 'total', '%.2f €';

select  total_net as 'total_rows',
	vat_percent as 'vat_percent',
	vat as 'vat_eur',
	total_net+vat as 'total'
from mydb.invoice
where invoice_id=in_invoice_id;

end

The final result is the invoice template

Using linked repors in a template

You can add linked reports to template elements. If you attach multiple reports to the elements and need a report menu selection, you need to add special data element (data-reportlink="columnReference") to the template elements parent so that myDBR can attach a mousedown event to it.

create procedure sp_DBR_template_linked()
begin

select 'dbr.report', 'sp_DBR_linked_1', '[multi]';
select 'dbr.report', 'sp_DBR_linked_2', '[multi]';
select 'dbr.report', 'sp_DBR_linked_3', '[single]';

select 'dbr.template', '#linked_reports';
select 'Data with multiple reports' as 'multi', 'Single linked report' as 'single';

As the {#multi} may have multiple reports attached to it, we need to add data-reportlink="multi" to it's parent element. A single report does not require it.

<div data-reportlink="multi">{#multi}</div><div>{#single}</div>

Using full queries inside a template

We'll create a report that will create a single table using multiple templates. Each template constructs a separate element inside the table.

The report uses two normal myDBR queries and insert's a template between the queries and creates a single report object.

We'll create a template that will create a row in the template containing a single text.

<tr>
  <td colspan="7" style="padding:15px;text-align:center;border:1px solid #DDDDDD;background:#FFFCC9">{#text}</td>
</tr>

create procedure sp_DBR_Template()
begin

select 'dbr.crosstab', 'Quarter';
select 'dbr.sum', 'Items', 'Weight';
select 'dbr.hsum', 'Items', 'Weight';

-- Do not create the footer/close the table) for the table as we will add more rows
select 'dbr.resultset.options', 'skip_footer';

select 
	Name, 
	concat(year(InsertDate), ' Q',quarter(InsertDate)) as 'Quarter', 
	sum(Items) as 'Items',
	sum(Weight) as 'Weight'
from TestTable
where quarter(InsertDate)=1 and Name<'Cordex'
group by Name, concat(year(InsertDate), ' Q', quarter(InsertDate))
order by Name;


-- Insert a template row. Do not enclude template into a div ('none') as it will part of a table
select 'dbr.template', '#row_insert', 'none';
select 'This is the text in the middle' as 'text';


-- The rest of the table. We do not need the header as it is already created
select 'dbr.resultset.options', 'skip_header';

select 'dbr.crosstab', 'Quarter';
select 'dbr.sum', 'Items', 'Weight';
select 'dbr.hsum', 'Items', 'Weight';

select 
	Name, 
	concat(year(InsertDate), ' Q',quarter(InsertDate)) as 'Quarter', 
	sum(Items) as 'Items',
	sum(Weight) as 'Weight'
from TestTable
where quarter(InsertDate)=1 and Name>='Cordex'
group by Name, concat(year(InsertDate), ' Q', quarter(InsertDate))
order by Name;

end

And the output of the report will show a single table:

Recording template variables

You can also record full report sections using dbr.record and use the recorded report elements as template variables. The second parameter for dbr.record command is the template variable where the query result is stored.

We'll create a template "bi_box" that will be a dashboard element

  <div class="bi_box {#bi_width}">
    <div class="bi_header {#bi_width}">
      <div class="bi_header1">{#bi_header1}</div>
      <div class="bi_header2">{#bi_header2}</div>
    </div>
    <div class="bi_data {#bi_width} {#bi_dheight}">
      <div class="bi_green {#bi_show_green}"><div class="bi_green_data">{#bi_green}</div></div>
      {#bi_content}
    </div> 
  </div>

The report will use the template using a template variable with custom CSS.

create procedure sp_DBR_dasboard()
begin

select 'dbr.css', '
body { background: #efefef}

.dashboard {
display: flex;
flex-wrap: wrap;
justify-content: flex-start;
justify-content: center;
margin-top: 20px;
}

.bi_box { text-align: left; margin-left: 10px; margin-top: 10px; box-shadow: 2px 2px 3px 0px #CCC; }
.bi_box.narrow, .bi_box .bi_data.narrow { width: 220px;  }
.bi_header, .bi_data { background: white; border: 1px solid #CCC; padding: 10px;  }
.bi_data { display: table-cell; vertical-align: bottom; }
.bi_header1 { color: black; font-weight: bold; font-size: 1.3em}
.bi_header2 { color: gray; padding-top: 3px }
.bi_box .small { height: 100px }
.bi_box .text { font-size: 40px; text-align: center;line-height: 80px }
.bi_box .rstable {margin-bottom: 0px;}
.bi_green_data { font-size: 24px; color: green; text-align: right; line-height: 16px}
.bi_green.no { display:none}
';

-- Wrap the dashboard elements in a flex-div
select 'dbr.html', '<div class="dashboard">';

-- Start the recording. The following query will be put into the bi_content-variable

select 'dbr.record', 'begin', 'bi_content';

select 'dbr.chart', 'column', '', 190, 90;
select 'dbr.chart.options','padding_right', -30;
select 'dbr.chart.options','padding_left', -3;

select month, value
from mydata;

-- End the recording. The chart is now in the bi_content-variable
select 'dbr.record', 'end';

-- Use the template
select 'dbr.template', '#bi_box';

-- Pass oher variables to the template
select 
  'narrow' as 'bi_width',
  'Execution effiency' as 'bi_header1',
  'By Score' as 'bi_header2',
  'small' as 'bi_dheight',
  null as 'bi_green',
  'no' as  'bi_show_green';


-- Do the second dashboard element
select 'dbr.template', '#bi_box';
select 'dbr.colstyle', 'bi_content', '$%.2fM';
select 'dbr.colstyle', 'bi_green', '+$%.1f%';

select 
  revenue as 'bi_content',
  growth_percent as 'bi_green',
  'narrow' as 'bi_width',
  'Expected Revenue' as 'bi_header1',
  'This year' as 'bi_header2',
  'small text' as 'bi_dheight',
  'yes' as  'bi_show_green'
from mydata;


select 'dbr.html', '</div>';

end

The report will show two dashboard elements

Creating forms with templates

Templates can be used to create custom forms. A form can be a custom report parameter page or a more complex form like feedback forms or questionnaire forms.

As an example, we'll create a feedback form. The form uses the Post/Redirect/Get pattern where the form posts the content to a myDBR submit-report that stores the entered data and redirects the user to another "Thank You"-page. The purpose of the redirect is to avoid posting the same information more than once if the user refreshes the page or uses a browser back button.

The form as added functionality:

  • The report accepts optional parameter lang which is handled with the automatic parameter syntax GET_ (=GET_lang)
  • The report keeps the values user has entered by keeping the values in the browser's Session Storage while the user may choose another language (page is refreshed) or even leave the page and return back later. This is done via JavaScript attached to the template
  • The report uses custom CSS to style the page

The form definition in the template will contain a template variable which will be filled from the value of the dbr.report-command.

  <form action="{#form_action}" method="post" id="cr_form" accept-charset="utf-8">
  ...
  </form>

The input fields in the template will have names (sample in_license) that match the submit report's variables. The form will be posted and the variables from the POST are matched against the routine variables.

<select id="license" name="in_license">
  <option value=""></option>
  <option value="oem">OEM</option>
  <option value="premium">Premium</option>
  <option value="community">Community</option>
</select>

The actual report code will look like this. The plain_URL option makes the URL for the form's submit action. The user-chosen language is passed to the submit-report so it can be used in the "Thank You"-page.

The q.css will contain the optional custom CSS (available in myDBR demo site). The q.js script sets the language select list selection to match the language set either by the lang URL parameter or by the user's myDBR preference. If you do not need additional languages in your template, you do not need this.

CREATE PROCEDURE sp_DBR_feedback( GET_lang char(5) )
BEGIN
select 'dbr.head', '<link href="internal/css/q.css" rel="stylesheet" type="text/css">';
select 'dbr.head', '<script src="internal/js/q.js"></script>';

select 'dbr.title', '';

select 'dbr.report', 'sp_DBR_feedback_submit', '[form_action]', concat('append=&i=0',f_lang_append(GET_lang)), 'plain_URL=1';
select 'dbr.template', '#feedback';

select 
  '' as 'form_action',
  GET_lang as 'lang';

END
$$

You should set all the submit-reports parameters to be "Optional" and "Do not remember", so the submit will go through even if the user does not answer call questions. If you need the fields to be mandatory, you can use the HTML5 required-attribute. The submit-report stores the user values and redirects to the "Thank You"-page.

CREATE PROCEDURE sp_DBR_feedback_submit(
lang varchar(5),
in_how_long_used int,
in_database varchar(10),
in_license varchar(10),
in_product_quality tinyint,
in_documentation tinyint,
in_support tinyint,
in_value_for_money tinyint,
in_feature_suggestions text
)
begin

insert into feedback (
  language,
  how_long_used,
  db,
  license,
  product_quality,
  documentation,
  support,
  value_for_money,
  feature_suggestions
)
values ( 
  lang,
  in_how_long_used,
  in_database,
  in_license,
  in_product_quality,
  in_documentation,
  in_support,
  in_value_for_money,
  in_feature_suggestions
);

select 'dbr.redirect', 'sp_DBR_feedback_thank_you ', concat('append=&m=0&hdr=0',f_lang_append(lang));
select 'dummy';

end

The "Thank You"-report is just plain myDBR report.

Optional functionality: language handling

The sample form itself is a localized myDBR template. Additional code at the end of the template handles the Session Storage. The FormStorage is a function included in myDBR. The preserve()-function stores the user values to the Session Storage when the user leaves the page. The restore() function gets any saved values. Finally, when the form is submitted, the Session Storage entry is deleted with the remove()-function. When you use the code in your own template, the cr_form is your form id. The form_storage_id string will be an ID for the Session Storage entry. You can use either a constant (if there is just one form) or concatenate an additional IDF to the Session Storage entry ID. You can see the created storage ID in your browser's debugger.

<script>
var $form = $("#cr_form"), formSave = new FormStorage("form_storage_id");
formSave.restore($form);
window.onbeforeunload = function () {
  formSave.preserve($form);
}
$form.submit(function (e) {
  formSave.remove($form );
});
</script>

The q.js script looks like this:

$( document ).ready(function() {
  var i, langs, l = $('input[name=lang] ').val(), select = 'select[name=lang] option';
  // The URL does not have the lang-parameter
  if (l=='' || l == '{#lang}') {
    let multilang = {
      sv: 'sv_SE', 
      el: 'el_GR', 
      de: 'de_DE', 
      es: 'es_ES', 
      nl: 'nl_NL', 
      it: 'it_IT',
      fi: 'fi_FI'
    };
    // Default to lang set in myDBR, if that is not set use en_US
    l = (mydbr_sorting.lang!=='' && multilang[mydbr_sorting.lang]!==undefined)  ? multilang[mydbr_sorting.lang] : 'en_US';
  }
  langs = $.map($(select), e => $(e).val());
  i = langs.indexOf(l);

  // Set the select list language if a known language, otherwise default to en_US
  $('select[name=lang]')[0].value = ((i<0) ? 'en_US' : langs[i]);
  
  // Handle the language change in the select list
  $('select[name=lang]').change( function() {
    var url = new URL(window.location.href);
    var search_params = url.searchParams;
    search_params.set('lang', $(this).val());
    url.search = search_params.toString();
    window.location.replace(url.toString());
  } );
});

Use of recorded variable in a report without templated

A recorded variable can be used in a report without templates

select 'dbr.record', 'begin', 'chart';

select 'dbr.chart', 'column', '', 200, 100;

select 'dbr.title', '';
select 'First', 1
union
select 'Second',3
union
select 'Third',5;

select 'dbr.record', 'end';


select 'Chart in a row', '{#chart}' as 'Chart';