User Parameters

myDBR reports can have parameters that will be asked from the user when the report is run. The parameters are the parameters for the stored procedure containing the report. Parameters can be any of the following:

Parameters can have default values which are used if user runs the report the first time. On subsequent runs parameters contain the last value used.

Parameters can be any of the following:

Basic fields
Date picker
Default values
Popups
Radio buttons
Checkboxes
Autocomplete fields
Connected parameters
Automatic parameter
Parameters in linked reports

If nothing is pre-defined myDBR reads the stored procedures parameters (name and type) and simply asks the values from the user. myDBR checks the datatype of the parameter and adapts the input field accordingly.

Basic fields (text/number)

Basic parameters in the report are requested as is from the user. myDBR allows the parameter name to be changed, so instead of using the actual procedure parameter name, admin can assign a title for the parameter.

In this example we'll use a film name (or part of it) as a parameter. We'll introduce a parameter 'vFilmName' into the stored procedure.

create procedure sp_DBR_FilmList( vFilmName 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('%', vFilmName, '%');

end

Since the stored procedure has already been introduced as a report, we do not need to reassign it. Just edit the procedure and you are ready to run it. Now when we re-run the report the parameter is asked from us as input:

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

You'll get the screen where you can edit the ready filled basic data about the report, handle the parameters and assign privileges to the report. We'll just give a name to our parameter.

When we re-run the report we'll see that the new name for the parameter is used.

Date picker

When the report parameter is a date or a datetime, myDBR automatically offers a date picker to the user to make up the data entry easier. The date format is defined in the myDBR preferences.

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 datatype contains time elements (datetime).

Default values

A report's parameters can have default values. These values are used when the report is run the first time. On subsequent runs the default values are the ones the user used during the last run. 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 with the start time by default to be one months back from now. The default query is defined in '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 defaults to the set default dates.

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. These choices can be presented to the user as via a popup, radio button or an Ajax-style autocomplete field. The one to choose depends on the data and the amount of choices available. A radio button is most suitable for limited numbers of choices, a popup 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 popup - 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

Popups

In order to convert a report parameter to a popup with different choices, we'll define the popup in the 'Admin->Parameter queries'-page. The popup list consist of a list of visual values for the user combined with the associated id.

Popup parameter query can return following result set with some optional columns:

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

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

As an example, we'll create a report which will list films based on a selected film category. In this example the user can also to choose to show the full film list. We'll use the parameter inCategory so that when it equals zero, well show all films, in other cases we'll show just 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 popup list with 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 at same time name the parameter:

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

Radio buttons

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

The radio button parameter query can return following result set:

ID [, Visible value to user]

If the query returns just one column, the same column will be used for both ID and as the value visible to user.

Let's create a very simple example procedure that uses the defined popup when we attach the radio button parameter into it:

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 so far as multiple ID's are sent to the report. myDBR does this by delivering a comma separated list of the selected ID's to the report. Report will parse the comma separated list (examples below) and use the ID's as part of query.

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

select category_id, name
from mydb.category;

The defined parameter query is attached to the following report. The report will convert the comma separated list to a table of ID's 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.

create procedure sp_DBR_checkbox( 
inCheckbox varchar(255) 
)
begin

/* Our ID's are integers, so we'll prepare a temporary table for those */
drop temporary table if exists data_tmp;
create temporary table data_tmp (
id int
);

/* 
We'll grab the exiting ID's by searching using a prepared statement
(Alternatively one could just parse the string in a loop)
*/
set @vSQL = concat('insert into data_tmp select category_id from category where category_id in (', inValue, ')');
prepare stmt from @vSQL;
execute stmt;
DEALLOCATE PREPARE stmt ;

/* We could use the temporary table as part of the report */
select id as 'User choice'
from data_tmp;

/* We are done with the temporary table */
drop temporary table if exists data_tmp;

end

Comma separated list parsing Microsoft SQL Server is done by using 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
declare @sql nvarchar(200)

create table #data_tmp (
id int
)

select @sql = 'insert into #data_tmp select category_id from category where category_id in ('+@inCB+')'

exec sp_executesql @sql

select id 
from #data_tmp

drop table #data_tmp

END
GO

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 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 popup or the radio button. The parameter defining the SQL must be a stored procedure and it will return the matching value and optionally a 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 same way as in Popup and Radio buttons:

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

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 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 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 in the beginning at 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 popup/radio button parameters that depend on each other. When the user selects an item on a popup/radio button, the subsequent popups/radio buttons that depend on the changed one will also change. Multiple popups/radio buttons can be chained together to create hierarchies.

Automatic parameters are also available in connected parameters.

The reference to another popup/radio button can be made using the following methods:

  • By using the same input variable name in 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 popup 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

DROP PROCEDURE IF EXISTS sp_DBR_car
$$
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 normal direct SQL query called 'car_manufacturers', which will be defined as a popup and attached to first 'inManufacturer' parameter.

select id, name from car_manufacturers

The second parameter, car model, depends on the first parameter, car manufacturer, and is therefore referred to as mydbr_param1. We'll use the stored procedure parameter query 'sp_ADBR_car_models' for this one.

The procedure is defined using a name reference, where the parameter inManufacturer is refers to the parameter with same name in the sp_DBR_car-procedure. The 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 same procedure using relative addressing would use mydbr_param_prev1 because the order number of inModel-parameter is 2 and the paramter it refers to is 1, hence 2-1 = 1.

CREATE PROCEDURE sp_ADBR_car_models(
mydbr_param_prev1 int
)
begin

select id, name
from car_models
where manufacturer_id = mydbr_param_prev1;

end

The same procedure using fixed addressing would use mydbr_param1 because the inModel-parameter refers to the 1st parameter (inManufacturer).

CREATE PROCEDURE sp_ADBR_car_models(
mydbr_param1 int
)
begin

select id, name
from car_models
where manufacturer_id = mydbr_param1;

end

The third parameter, car variant, depends both on first and second parameter and is defined as a plain parameter query:

select id, name
from car_variants
where manufacturer_id = mydbr_param_prev2 and model_id=mydbr_param_prev1

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.

Automatic parameter

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

  • 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
  • autoexecute - Makes myDBR accept default parameters and not wait for user confirmation.

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 special parameter "inLogin varchar(30)" to the report. myDBR will automatically set it to contain the user's username.

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

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

create procedure sp_DBR_WhoAmI( inLogin varchar(30) )
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 first parameter for the procedure.

create procedure sp_Autoc_DBR_Titles( inLogin varchar(30), 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, you can simply include inLogin in the parameter query. myDBR will handle the substitution.

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

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 list films rented in 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 reports 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, mydb.inventory i, mydb.film f
where r.rental_date between inStartDate and inEndDate and r.customer_id=inCustomerID and 
      r.inventory_id = i.inventory_id and i.film_id = f.film_id;

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 using column references. The reference to 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, mydb.rental r
where c.customer_id = r.customer_id and 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.