Referencing columns and parameters or passing on constants

Many of the myDBR commands can get parameters or reference columns of the next result set or refer to the report's own parameters. You can reference to these by number or by name. Referencing columns/parameters by name makes the code more readable and easier to maintain.

Column reference

Let's take example of dbr.sum-command which will create a summary row for given column(s). Teh command takes one or more column references as parameters. These column references refer to columns of the next result set. There are three different syntaxes for how one can reference the column:

  1. Referencing by tag (recommended)
  2. Referencing by column name
  3. Referencing by column number

Referencing columns by tag

Tags are texts you add enclosed in brackets both in column reference and at the end of a column name. myDBR automatically strips the tags from column names when creating the result. This is the recommended way of referencing columns. You can either use the brackets or leave them off when making a reference. Brackets are only required in dbr.summary.calc and dbr.calc calculation and in the dbr.report's column parameter.

select 'dbr.sum', 'length' ,'weigth';
select 'dbr.report', 'sp_DBR_show_product_detail', '[product]', 'popup', 'inProduct_id=product_id';
select 'dbr.calc', 'volume', '[length]*[weigth]*[height]';

select product_id,
       product as 'Product[product]',
       length as 'Length[length]',
       weight as 'Weight[weigth]',
       height as 'Height[height]',,
       null as 'volume'
from mydb.products;

Referencing columns by column name

The column name reference uses the column names. No extra code needed, but may need changes if creating multilingual reports or if more than one column shares the same name.

select 'dbr.sum', 'Length' ,'Weight';
	
select product as 'Product',
       length as 'Length',
       weight as 'Weight'
from mydb.products;

Referencing columns by column number

The column number reference uses the column order number to reference the column. This is best suited for small queries which you do not change that often. When complex queries change, this usually leads to renumbering adding extra work.

select 'dbr.sum', 2,3;
	
select product as 'Product',
       length as 'Length',
       weight as 'Weight'
from mydb.products;

Parameter reference

Some commands (dbr.report, dbr.url and dbr.editable) allow using the report parameters as values to be passed on as a command parameter. There are two different syntaxes for referencing the column:

  1. Referencing by parameter name (recommended)
  2. Referencing by parameter position

Referencing parameters by name

When referencing parameters by name the parameter name is put in parenthesis.

create procedure sp_DBR_myreport(
inCategory int
)
begin
select 'dbr.url', 'http://myserver.com/myservice', '', 1, 'c=(inCategory)', 'p=product_id';
	
select name, product_id
from mydb.products
where category=inCategory;

When a report is called with inCategory=2 and the user selects a product row with id=200 the resulting URL will be "http://myserver.com/myservice?c=2&p=200"

Referencing parameters by parameter position

When referencing parameters by parameter position the position is a negative number which refers to the original report's parameter (e.g value -2 refers to 2nd parameter in original report). This is a compatibility option.

create procedure sp_DBR_myreport(
inCategory int
)
begin
select 'dbr.url', 'http://myserver.com/myservice', '', 1, 'c=-1', 'p=product_id';
	
select name, product_id
from mydb.products
where category=inCategory;

When report is called with inCategory=2 and user selects a product row with id=200 the resulting url will be "http://myserver.com/myservice?c=2&p=200"

Passing on constants

One can also pass constants to linked reports / URLs by enclosing the constant value into quotation marks. The value enclosed needs to be in formatting numbers with decimal point (12.12) and dates with format YYYY-MM-DD.

In the example the inExtra-parameter gets a constant value "extra".

create procedure sp_DBR_myreport(
inCategory int
)
begin
select 'dbr.report', 'sp_DBR_show_product_detail', '[product]', 'popup', 'inProduct_id=product_id', 'inExtra="extra"';

select product_id,
       product as 'Product[product]',
       length as 'Length[length]',
       weight as 'Weight[weigth]',
       height as 'Height[height]',
       null as 'volume'
from mydb.products;

When report is called with inCategory=2 and user selects a product row with id=200 the resulting URL will be "http://myserver.com/myservice?c=2&p=200"

Referencing crosstab total columns

Cross tabluation total columns are dynamically created to hold values for horizontal sum/min/max. One can reference these columns by adding ".h" to the column reference. This allows report links to be placed into summary columns and it also allows to use the summary values as parameters.

In the example the inExtra-parameter gets a constant value "extra".

create procedure sp_DBR_myreport()
begin
select 'dbr.report', 'sp_DBR_show_items', '[Items.h]', 'in_items_total=Items.h';

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

select Name, Quarter, Items as '[Items]'
from mydata;

The second parameter for dbr.report ([Items.h]), places the report link to totals column for Items and sp_DBR_show_items's in_items_total will get the value for horizontal summary for the Items column.