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 a special data element (data-reportlink="columnReference") to the template element's 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>

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

The report uses two regular myDBR queries and inserts a template between them to create a unified report object.

We'll define a template that generates a row containing a single text element.

<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 wrap template into a div ('none'-parameter) 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:

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

We'll create a template called "bi_box" that will serve as 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 utilize the template variable "bi_box" 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

Templates can be used to create custom forms. A form can range from a custom report parameter page to more complex structures such as feedback or questionnaire forms.

For example, we will create a feedback form. This form will follow the Post/Redirect/Get pattern, where the form posts its content to a myDBR submit-report that stores the entered data. Subsequently, the user is redirected to a "Thank You" page. This pattern prevents the issue of resubmitting the same information if the user refreshes the page or uses the browser's back button.

The form includes added functionality:

The form template will include a template variable that is populated 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 (for example, in_license) that correspond to the variables in the submit report. When the form is submitted, the variables from the POST request 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 specifies 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 file will contain 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-report's parameters to "Optional" and "Do not remember", so the submit will proceed even if the user does not answer all questions. If you need 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.

The sample form itself is a localized myDBR template. Additional code at the end of the template handles the Session Storage. The FormStorage function is included in myDBR. The preserve() function stores the user values to the Session Storage when the user leaves the page. The restore() function retrieves 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 ID to the Session Storage entry ID. You can view 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());
  } );
});

You can use a recorded variable directly 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';

You can use templates to create your own parameter form. This gives you full control over how the parameter form for your report looks and behaves. You can view the template used on the report info page. For the parameter form, myDBR creates necessary elements and provides recorded variables for the input elements, labels, and errors if you choose to use them.

The variables available are listed below. Replace NAME with the parameter name.

If you have a report that shows a contact form, it can have following parameters:

create procedure sp_DBR_contact_form_report(
inFirstName varchar(40),
inLastName varchar(40),
inEmail varchar(40),
inWebsite varchar(40),
inMessage text
)
begin

Here's a sample template foe the report:

<style>
.form-wrapper {
  margin: 0;
  padding: 0;
  outline: none;
  box-sizing: border-box;
  display: flex;
  align-items: center;
  justify-content: center;
  min-height: 100vh;
  padding: 10px;
}
.myform{
  max-width: 800px;
  background: #fff;
  width: 800px;
  padding: 25px 40px 10px 40px;
  box-shadow: 0px 0px 10px rgba(0,0,0,0.1);
}
.myform .text{
  text-align: center;
  font-size: 41px;
  font-weight: 600;
}
.myform form{
  padding: 30px 0 0 0;
}
.myform form .form-row{
  display: flex;
  margin: 32px 0;
}
form .form-row input{
  width: 100%;
  height: 40px;
  margin: 0 20px;
  position: relative;
}
.input-data {
  margin-top: 5px;
}
input,
.textarea textarea{
  display: block;
  width: 100%;
  height: 100%;
  border: none;
  font-size: 17px;
  border-bottom: 2px solid rgba(0,0,0, 0.12);
  outline:none;
}
.textarea textarea{
  resize: none;
  padding-top: 10px;
  margin-left: 20px;
}
label{
  bottom: 10px;
  margin-left: 20px;
  font-size: 16px;
  color: #444;
}
.textarea label{
  width: 100%;
  bottom: 40px;
  background: #fff;
}
.input-data .underline{
  position: absolute;
  bottom: 0;
  height: 2px;
  width: 100%;
}
</style>
<div class="form-wrapper">
<div class="myform">
  <div class="text">
    Contact us Form
  </div>
  <div class="form-row">
    <div class="input-data">
      <label class="{#error_inFirstName}" for="{#parameter_id_inFirstName}">{#label_inFirstName}</label>
      {#inFirstName}
      <div class="underline"></div>
    </div>
    <div class="input-data">
      <label for="{#parameter_id_inLastName}">{#label_inLastName}</label>
      {#inLastName}
      <div class="underline"></div>
    </div>
  </div>
  <div class="form-row">
    <div class="input-data">
      <label for="{#parameter_id_inEmail}">{#label_inEmail}</label>
      {#inEmail}
      <div class="underline"></div>
    </div>
    <div class="input-data">
      <label for="{#parameter_id_inWebsite}">{#label_inWebsite}</label>
      {#inWebsite}
      <div class="underline"></div>
    </div>
  </div>
  <div class="form-row">
    <div class="input-data textarea">
      <label for="{#label_inMessage}">Write your message</label>
      {#inMessage}<br>
      <div class="underline"></div><br>
      <div class="form-row submit-btn">
        <div>
          <div class="inner"></div>
          {#submit}
        </div>
      </div>
    </div>
  </div>
</div>
</div>