Templates

Commands

dbr.template - Defines a template being used for the subsequent result set

Syntax

select 'dbr.template', '#template' | 'row template code' | 'header template code', 'row template code', 'footer template code', [ 'template_class' | 'none' ]

Explanation

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

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

A template section can contain myDBR localization references (like #{title}). The parameter to dbr.template-command can be a reference to a stored template (syntax: {#template}, syntax #template kept for compatibility), all three template sections, or just the row-section.

The row-template code can contain template variables, 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.

A template variable can also be replaced with a full myDBR result set. To do this, the first record a result set using 'dbr.record' command with the template variable as a parameter. Next template used will use the recorded result set as a value of the template variable.

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

Examples

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' which 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 form

We'll create an invoice form 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/apppic.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 form

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 header as is 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. 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