User Parameters

myDBR reports can include parameters that prompt the user when the report is executed. These parameters correspond to the stored procedure containing the report. Parameters may take any of the following forms:

Parameters can have default values, which are utilized when the user runs the report for the first time. In subsequent runs, parameters retain the last-used value unless the option is set not to remember the user's choices.

Parameters may encompass any of the following:

Basic fields
Date picker
Default values
Select lists
Multiselects
Radio buttons
Checkboxes
Autocomplete fields
Range
File content
Connected parameters
Automatic parameters
Parameters in linked reports

If no pre-defined values are specified, myDBR retrieves the parameters (name and type) from the stored procedure and prompts the user for input. myDBR automatically adapts the input field based on the parameter's data type.

Basic Fields (Text/Number)

Basic parameters in the report are directly requested from the user. myDBR provides the flexibility to change the parameter name, allowing the admin to assign a title for the parameter rather than using the actual procedure parameter name.

In this example, we'll use a film name (or part of it) as a parameter, introducing the parameter 'in_film_name' into the stored procedure.

create procedure sp_DBR_FilmList( in_film_name varchar(30) )
begin

select title as 'Title',
       release_year as 'Release Year',
       rental_rate as 'Rental Rate',
       length as 'Length'
from mydb.film
where title like concat('%', in_film_name, '%');

end

Since the stored procedure has already been assigned as a report, there is no need to reassign it. Edit the procedure, and you are ready to run it. Now, when we rerun the report, the parameter is requested from us as input:

The variable name is not necessarily the most descriptive parameter name you want to use. Let's create a more fitting name for it. We'll go back to the main screen and edit the report's data by clicking the 'Edit' button. next to the report.

You'll be directed to a screen where you can edit the pre-filled basic data about the report, manage the parameters, and assign privileges to the report. We'll simply give a name to our parameter.

When we rerun the report, we'll see that the new name for the parameter is used.

Date Picker

When the report parameter is a date or datetime, myDBR automatically offers the user a date picker to facilitate data entry. The date format used is defined in the user's preferences.

If we have two consecutive dates as a parameter, one can treat them as date range (from-to), by checking the 'Keep the next parameter in the same row' checkbox for the first date's option (Edit report -> Report Parameters -> Other). myDBR will then automatically add a date range select list from which the user can select the most common date ranges (last week, last month, etc.).

create procedure sp_DBR_FilmUpdates( vStartDate date, vEndDate date )
begin

select title as 'Title',
       release_year as 'Release Year',
       rental_rate as 'Rental Rate',
       length as 'Length',
       last_update as 'Last Update'
from mydb.film
where last_update between vStartDate and vEndDate;

end

When we run the report, we are asked about the dates with the assistance of a picker:

myDBR shows the time in the picker if the data type contains time elements (datetime).

Default Values

A report's parameters can have default values. These values are used when the report is run for the first time. On subsequent runs, the default values are the ones the user used during the last run (unless the parameter preference is set not to remember the user's choices). Default values are defined in the 'Admin->Parameter queries'-page.

Let's examine the myDBR statistics report's defaults. The report has two date fields; one for the start time and one for the end time. By default, myDBR offers to run the statistics for the past month. The default query is defined on the 'Admin->Parameter queries' page:

The defined defaults are then associated with the query parameters in the 'Edit query' screen:

When the report is run for the first time, the report's date fields default to the set default dates.

Multiple values can be passed as default when defining default values for checkboxes. The default query can return multiple rows (one for each checked checkbox), or a query can return a comma-separated list of default values. If the parameter is not numeric, a comma-separated list of values is enclosed in single quotation marks ('A','B','C').

Predefined/Dynamic Parameters

In addition to simply requesting parameters from the user, myDBR allows one to present choices to the user. These choices can be static or dynamic. In the latter case, the choices are read from the live data. You can present these choices to the user via a select list, radio buttons, or an Ajax-style autocomplete field. The choice depends on the data and the number of choices available. A radio button is most suitable for limited numbers of choices, a select list when there are more but still a limited number of choices, and finally, the autocomplete, which can initially have a bigger selection.

Predefined/dynamic parameters are defined in the myDBR preferences (Admin->Parameter queries). These are parameter queries that can be shared among all the reports. Parameter queries should return:

  • The select list - id and user-visible value
  • The radio button - id and user-visible value
  • The autocomplete field - visible value
  • The checkbox - id and user-visible value

Select Lists

To convert a report parameter into a select list with different choices, we'll define the select list parameter query in the 'Admin->Parameter queries'-page. The select list consists of a list of IDs and visual values.

A Select list parameter query can return the following result set with some optional columns:

ID [, Visible value to the user[, Grouping, [ 'selected' [, optional columns for sorting]]]]

If the query returns just one column, the same column will be used for both ID and as the value visible to the user. If the group for the row is returned, the values belonging to the same group will be grouped with the HTML selectlist's optgroup. If the fourth column of the parameter query is 'selected', that item will be chosen as the default. This is especially useful if one places the parameter query into a stored procedure and uses automatic parameters to customize the query.

As an example, we'll create a report listing films based on a selected film category. In this example, the user can also choose to show the full film list. We'll use the parameter inCategory so that when it equals zero, we'll show all films. In other cases we'll show the selected category.

create procedure sp_DBR_FilmsByCategory( inCategory tinyint )
begin

select f.title as 'Title', 
       f.description as 'Description', 
       f.release_year as 'Release year', 
       f.rating as 'Rating'
from mydb.film f
where f.film_id in (
	select film_id
	from film_category
	where category_id = inCategory or inCategory = 0 
);

end

We'll then prepare the select list with a query that returns the id and the user-visible value.

The parameter query is then attached to the report in the 'Edit query' screen. We can name the parameter at the same time:

When we now run the report, a select list with predefined values is shown:

If a select list parameter query returns a column (fourth column or greater) with the title 'parameter_info', the data for that column will be shown as info text for that selection. The column can contain HTML.

create procedure sp_param_info()
begin

select 1, 'First', '', 'Internal price: 200<br>External price: 300' as 'parameter_info'
union
select 2, 'Second', '',  'Internal price: 250<br>External price: 320'
union
select 3, 'Third', '', 'Internal price: 300<br>External price: 380';

end

Multiselects

A multiselect is a dynamic (fetched via Ajax) select list where the user can pick multiple items. myDBR passes the selected items to a report by passing a comma-separated list of the selected ids to the report's parameter.

A Select list parameter query can return the following result set with some optional columns:

ID [, Visible value to the user[, Grouping ]

If the query returns just one column, the same column will be used for both ID and as the value visible to the user. If the group for the row is returned, the values belonging to the same group will be grouped with the HTML selectlist's optgroup.

As an example, we'll create a report that lists countries the user has selected. See more at Checkboxes on how to handle the comma-separated list in SQL procedures. In this example, we'll use MySQL specific find_in_set-function.

create procedure sp_DBR_FilmsByCategory( in_countries text )
begin

select name
from countries
where find_in_set(id, in_countries);

end

The parameter query for multiselect needs to be a stored procedure that will take the user search string as a parameter. In a case where the query can return a large number of rows, use the LIMIT/TOP clause to limit the number of rows to be shown to the user (by typing a more exact search string user can narrow down the number of rows returned). The parameter query can return from one to three columns. The first one is the ID (or visible value as well if only one column is returned), the second is the visible value, and the third is the optgroup which groups the rows.

create procedure sp_ADBR_multiselect( in_search varchar(255) )
begin

select id, name, continent
from countries
where name like concat('%', in_search, '%')
limit 40;

end

Radio Buttons

The radio button parameter works the same way as in the select lists. We'll create radio buttons showing just simple Yes/No selection:

The radio button parameter query can return the following result set:

ID [[, Visible value to the user], uservalue, 'selected']

If the query returns just one column, the same column will be used for both ID and as the value visible to the user. You can give a different visible value in the second parameter. If the fourth parameter is 'selected', that will become the default choice.

Let's create a simple report procedure that uses the defined radio button:

create procedure sp_DBR_YesNoParam( inYesNo tinyint )
begin

select inYesNo;

end

When we now run the report, a radio button selection is shown:

Checkboxes

Checkboxes differ from other parameters in a way that multiple IDs are sent to the report. myDBR does this by delivering a comma-separated list of the selected IDs to the report. The report will parse the comma-separated list (examples below) and use the IDs as part of the query.

The checkbox parameter query can return the following result set:

ID [, Visible value to the user[, Grouping1, [Grouping2, ...[ 'checked' [, optional columns for sorting]]]]]

If a column contains 'checked', that choice will be checked by default. This is especially useful if one places the parameter query into a stored procedure and use automatic parameters to customize the query.

We'll start by defining the checkbox choices in the 'Settings->Parameter queries'.

select category_id, name
from mydb.category;

When a user selects multiple choices, a comma-separated string of IDs is sent to report procedure ("1,2,4,10"). If the ID is a string (or date/datetime) the IDs are strings separated by comma ("'First','Second','Last'").

The defined parameter query is attached to the following report. The report will convert the comma-separated list to a table of IDs which can then later in the report be joined to other objects. The conversion will be done by a prepared statement. This example is using MySQL and it takes advantage of MySQL's find_in_set-function.

create procedure sp_DBR_checkbox( 
inCheckbox varchar(255) 
)
begin

select category_id as 'User choice'
from category
where find_in_set(category_id, inValue);

end

comma-separated list parsing Microsoft SQL Server 2016 and above can be done with STRING_SPLIT-function. In older versions, one can use the sp_executesql-system procedure. In Sybase ASE and in SQL Anywhere you can use EXECUTE statement.

IF object_id('sp_DBR_checkbox','P') IS NOT NULL
DROP PROCEDURE [sp_DBR_checkbox]
GO
create procedure sp_DBR_checkbox
@inCB varchar(200)
AS
begin

select category_id
from category c
  join string_split(@json, ',') cb on cb.value=c.category_id
  
end
go

Checkboxes can be created as a hierarchical tree structure by utilizing the Grouping parameters. In the example the continent and the country columns will create a hierarchical structure:

select ci.city_id, ci.city, cc.continent, cc.country
from city ci
 join country co on co.country_id=ci.country_id
 join continent cc on cc.continent_id=co.continent_id

Autocomplete Fields

Autocomplete fields narrow the selection by performing live queries on the database based on user data entry. The user is presented with a simple entry field, and while the user enters the data into the field, suggestions based on actual data are offered to the user. The returned suggestions can be freely defined ('start of string'-match, 'contains'-match, etc.). The actual definition for fetching SQL is different from the select list or the radio button. The parameter defining the SQL must be a stored procedure and it will return the matching value and optionally an additional description and ID(s) of the matching row.

One-line Autocomplete

We'll create a report that returns film information. The selected film is fetched with an autocomplete film's field. First, we'll define the report showing the film info:

create procedure sp_DBR_FilmInfo( inFilmname varchar(30) )
begin

select 'dbr.pageview';

select f.title as 'Title', 
       f.description as 'Description', 
       f.release_year as 'Release year', 
       f.rating as 'Rating' 
from mydb.film f
where f.title = inFilmname;

end

Next, we'll create the autocomplete procedure, which takes one parameter (the user input) and it returns the matched film names. This procedure is called every time the user enters data into the parameter field. In this example, the autocomplete matches from the beginning of the film title:

create procedure sp_Autoc_DBR_Titles( inTitle varchar(255) )
begin

select Title
from mydb.film
where Title like concat( inTitle, '%');

end

The autocomplete procedure is then added as a parameter query:

Attaching the autocomplete parameter is done in the same way as in select lists and the radio buttons:

When we now run the report, the parameter fields autocomplete the user input:

Range

Range slider lets the user to specify a numeric value which must be no less than a given value, and no more than another given value. Alternatively, user can be shown a textual expression of the value. A range can be a single value range or a double-value range (option 'Multiple'). A single value range will return the selected value and the multiple range will return a string with format "min,max"

Parameter query for the range-parameter can be: single row with two integer numbers (min and max) or multiple rows consisting of the numeric value and the textual expression.

A parameter with numeric values (single and multiple):

  select 1, 20;

A parameter with textual values (single and multiple):

  select 1, 'Poor'
  union
  select 2, 'Satisfactory'
  union
  select 3, 'Good'
  union
  select 7, 'Excellent';

File Content

A file content parameter allows drag-and-drop files as a parameter. The file's content will be passed in as a parameter. This is suitable for text-documents (text, JSON, XML). When adding large files, one can use the inAutoUsePOST-automatic parameter so the parameter form is using POST instead of GET as form action. One can do dbr.redirect afterwards, so the report refresh will not do the POST again.

One-line Autocomplete

We'll create a report that returns film information. The selected film is fetched with an autocomplete film's field. First, we'll define the report showing the film info:

Two-line Autocomplete

It is possible to display two lines of information in the autocomplete list. The first line contains the data that is put into the input field after the user selects the row, the second line is there for more information. The second line is generated by altering the autocomplete stored procedure by adding another column to the result set.

create procedure sp_Autoc_DBR_Titles( inTitle varchar(255) )
begin

select Title, special_features
from mydb.film
where Title like concat( inTitle, '%');

end

When we now run the report, the parameter fields autocompletes the user input using both values:

Getting the IDs of the Selected Autocomplete-value

If the autocomplete stored procedure returns more than two columns (matching selection and description being the first two), the rest of the values are treated as IDs for the matching row. More than one ID can be assigned. The original report needs matching ID parameters after the autocomplete fields. The ID fields will be automatically hidden from the user.

We'll first define the report itself with two parameters. The inTitle-parameter will be the field we'll attach the autocomplete field to and will be the one visible to the user. The inFilmID-parameter will be the hidden parameter that will hold the ID of the selected film when the user selects the autocomplete suggestion. The ID parameters must follow the autocomplete filed in the parameter definition.

create procedure sp_DBR_FilmInfo( inFilmname varchar(30), inFilmID int )
begin

select 'dbr.pageview';

select f.title as 'Title', 
       f.description as 'Description', 
       f.release_year as 'Release year', 
       f.rating as 'Rating' 
from mydb.film f
where f.film_id = inFilmID;

end

In the autocomplete procedure the ID values are placed starting from the third column. If no description is wanted when using the ID values, use NULL-value in place of the second column.

create procedure sp_Autoc_DBR_Titles( inTitle varchar(255) )
begin

select Title, special_features, film_id
from mydb.film
where Title like concat( inTitle, '%');

end

Note 1: If the inFilmID-parameter is set as an optional parameter, the user is able to run the report even if (s)he has not selected a row from the autocomplete suggestion.

Note 2: If you use the automatic parameter inLogin in the autocomplete procedure to limit the options on a per-user basis, place the automatic parameter at the beginning of the parameter list.

As a security precaution, please also note that when limiting the options using the inLogin-parameter, you should check the validity of the ID inside the procedure as the user may change the browser DOM tree before submitting the parameters.

Connected Parameters

With connected parameters, you can create dynamic select list/radio button parameters that depend on each other. When the user selects an item on a select list, radio button, or from autocomplete field, the subsequent select lists/radio buttons that depend on the changed one will also change. Multiple select lists/radio buttons can be chained together to create hierarchies.

Automatic parameters are also available in connected parameters.

The reference to another select list/radio button is made by using the same input parameter name in the connected report as in the main report.

  • By using the same input variable name in the connected report as in the main report. This method works when the connected parameter query is defined as a stored procedure. This is usually the easiest method to use. The method works only in cases where the same connected parameter appears only once in parameters.
  • By using the relative address using keywords mydbr_param_prevX or mydbr_param_nextX, where prevX refers to previous parameters and nextX refers to next parameters. For example, mydbr_param_prev1 refers to parameter 2 if attached to connected parameter 3. mydbr_param_prev2 would refer to parameter 1 in the same case. This method can be used when the same connected parameter query is used more than once in the report. The keyword is used in defined parameter queries and can be used in either direct SQL or as a parameter for a stored procedure.
  • By using the fixed address using keyword mydbr_paramX, where prevX refers to previous parameters and nextX refers to next parameters, where the X refers to the order number of the parameter The keyword is used in defined parameter queries and can be used in either direct SQL or as a parameter for a stored procedure.

As an example, we'll create a report which has three-level select list as a parameter. First, the user selects a car manufacturer, then the car model, and finally a variant of the model.

First, we'll create the report with required parameters

create procedure sp_DBR_car(
inManufacturer int,
inModel int,
inVariant int
)
begin
....
end

Then we'll define the required parameter queries. The first one is a normal direct SQL query called 'car_manufacturers', which will be defined as a select list and attached to first 'inManufacturer' parameter.

select id, name 
from car_manufacturers

The parameter procedure for the car models is defined using a name reference, where the parameter inManufacturer refers to the parameter with the same name in the report's procedure (sp_DBR_car). The parameter procedure is named with the prefix sp_ADBR so we do not mix up it with reports with an sp_DBR prefix.

create procedure sp_ADBR_car_models(
inManufacturer int
)
begin

select id, name
from car_models
where manufacturer_id = inManufacturer;

end

The third parameter procedure, for car variant, depends both on first and second parameter and uses those parameter names respectively:

create procedure sp_ADBR_car_variants_name(
inManufacturer int,
inModel int
)
begin

select id, name
from car_variants
where manufacturer_id = inManufacturer and model_id= inModel;

end

The connected parameter definition in Admin Tools / Parameter queries contains the procedure name or the direct SQL query.

When parameter queries are attached to the report parameters, the connected parameters are available.

The connected parameter returned can be automatically selected by returning value of 'selected' in the fourth column (1st being the id, 2nd being the visible value for the user and 3rd being the optional optgroup in select/checkbox (null if none)).

Automatic Parameters

myDBR offers automatic parameters that can be used in reports, defaults and parameter queries. These parameters are filled in automatically by myDBR and are not visible to users.

  • inLogin - username
  • inIPAddress - Users IP address as reported by user's browser
  • inLocale - Users locale when report is run (en_US, de_DE etc.)
  • inUseragent - Browsers user agent
  • inExportFormat - URL's export parameter value
  • inAutoexecute - Makes myDBR accept default parameters and not wait for user confirmation.
  • inHTTP_ACCEPT_LANGUAGE - User's default browser language
  • inHTTP_REFERER - HTTP_REFERER, IP address of linked web page
  • inAutoReportURL - Report's execution URL
  • inAutoReportProcedure - Report procedure's name
  • inAutoTheme - User's theme
  • inAutoUIMode - UI mode ('mobile' / 'desktop')
  • inImportFilename - Filename chosen in dbr.import
  • inImportTotalRows - Number of total rows in import
  • inImportErrors - Number of errornous rows in import
  • inSessionIDHash - Hash value from user's Session ID
  • additional user defined SSO automatic parameters - See Single Sign-On for additional automatic parameters
  • GET_[param] - Allows including dynamic parameters to the report URL. A parameter like '&myparam' is trated as GET_myparam automatic parameter
  • in_MyDBR_OrganizationID - OEM version with myDBR authentication contains the user's organization ID
  • inAutoReplica - Shows in which server the report was run (OEM replica feature)
  • inAutoUsePOST - When defined in the report, the report is using POST instead of GET as form action. Allows posting large amounts of data (file content etc.)

Example: Identifying the User Running the Report

myDBR keeps track of which username/password each connection was made with to the database. If you need to know who the user is executing the report, you can add a special parameter "inLogin varchar(128)" to the report. myDBR will automatically set it to contain the user's username.

The variable name (inLogin) is defined in defaults.php if you wish to change it.

Following report outputs the user's login name. No parameters are requested when the user runs the report.

create procedure sp_DBR_WhoAmI( inLogin varchar(128) )
begin

select 'User running the report is: ', inLogin;

end

You can also use the inLogin parameter in parameter queries. If using procedures, the inLogin parameter needs to be the first parameter for the procedure.

create procedure sp_Autoc_DBR_Titles( inLogin varchar(128), inTitle varchar(255) )
begin

select Title
from mydb.film
where Title like concat( inTitle, '%') and owner=inLogin;

end

When using direct SQL in parameter queries, is is recommended to use parameter queries from stored procedures. This allows myDBR to parse the automatic parameters safely.

create procedure sp_par_my_films ( 
inLogin varchar(128)
)
begin

select film_id, Title from mydb.film where owner=inLogin;

end

Parameters in Linked Reports

When linking reports together, the user has a report (s)he has run. The calling report itself may have parameters that can be passed to the called report. Also, the context data from which the user clicks the linked report (a row from a result set or a chart part) can be passed onto the report.

Following is a simple example of parameter passing in linked reports. In the example, we have a report which lists films rented in a given timeframe. From the list, the user can click more details on the persons that have rented the movie passing along also the customer ID. So, from the first report, we'll pass on the report's own parameters and also the data from the user selection.

The report to be linked into has three parameters: dates (start and end date) and the customer ID:

create procedure sp_DBR_CustomerRentals( inCustomerID int, inStartDate date, inEndDate date )
begin

select f.title as 'Film',
      r.rental_date as 'Rental time', 
       f.rental_rate as 'Rental rate'
from mydb.rental r
  join mydb.inventory i on r.inventory_id = i.inventory_id
  join mydb.film f on i.film_id = f.film_id
where r.rental_date between inStartDate and inEndDate and r.customer_id=inCustomerID;

end

The report from which the linking is done defines the linked report with the dbr.report. The parameters referring to the report's result set are referred to using column references. The reference to the report's own parameters is done using the variable name in parentheses.

create procedure sp_DBR_RentalCustomers( inStartDate date, inEndDate date )
begin

select 'dbr.report', 'sp_DBR_CustomerRentals', 'inCustomerID=cid', 'inStartDate=(inStartDate)', 'inEndDate=(inEndDate)';

select concat(c.first_name, ' ', c.last_name) as 'Customer',
       count(*) as 'Rentals', 
       c.customer_id as 'Customer ID[cid]'
from mydb.customer c
  join mydb.rental r on r.customer_id=c.customer_id
where r.rental_date between inStartDate and inEndDate
group by c.customer_id, Customer;

end

In the example, the inCustomerID=cid means that sp_DBR_CustomerRentals-procedure's parameter inCustomerID will be filled with the result set data referenced with the cid column reference. The inStartDate=(inStartDate) means that sp_DBR_CustomerRentals-procedure's parameter inStartDate will be filled with the value of the inStartDate-parameter from sp_DBR_RentalCustomers parameter.

Now when we run the initial report and click for more info on the customer, the linked report is run without any questions on parameters since all the parameters have been filled with data from the initial report.

For more information on the linked reports see section Report Linking.