Referencing Columns and Parameters or Passing Constants

Many of the myDBR commands can receive parameters or reference columns from the following result set or refer to the report's own parameters. You can refer 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 consider an example of the dbr.sum command, which creates a summary row for a given column(s). The command takes one or more column references as parameters. These column references pertain to columns in the following result set. There are three different syntaxes for referencing the column:

Referencing by tag (recommended)
Referencing by column name
Referencing by column number

Referencing Columns by ColumnReference

ColumnReferences are texts enclosed in brackets, added both in column reference and at the end of a column name. myDBR automatically removes the ColumnReferences from the column names when creating the result. This is the recommended way of referencing columns. You can use brackets or leave them off when referring to myDBR commands. Brackets are only required in dbr.summary.calc and dbr.calc calculations 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 actual column names. No extra code is needed, but it may require 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. Column number reference is best suited for small queries that you only change occasionally. However, when complex queries change, this can lead 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 (such as 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:

Referencing by parameter name (recommended)
Referencing by parameter position

Referencing Parameters by Name

When referencing parameters by name, the parameter name is enclosed in parentheses.

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 that refers to the original report's parameter (e.g., value -2 refers to the 2nd parameter in the 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 the 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".

Passing on Constants

One can also pass constants to linked reports/URLs by enclosing the constant value in quotation marks. The value enclosed needs to be in a format that the database understands (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's Total Columns

Cross-tabulation 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 using the summary values as parameters.

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.