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 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.

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).

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').

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:

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

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

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 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 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.

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 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';

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.

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:

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:

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.

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.

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)).

myDBR offers automatic parameters that can be used in reports, defaults and parameter queries.

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

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.