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 unless the parameter is set not to remember user's choises.
Parameters can be any of the following:
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 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 '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 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.
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 used is defined in 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 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 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 datatype contains time elements (datetime).
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 (unless the parameter preference is set to not to remember user's choises). 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.
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 select list, 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 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:
In order 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 consist of a list of ID's and visual values for the.
A Select list parameter query can return following result set with some optional columns:
ID [, Visible value to user[, Grouping [, 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 user. If the group for the row is returned the values belonging to same group will be grouped with the HTML selectlist's 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 select 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 select list with predefined values is shown:
The radio button-parameter works exactly the same way as in the select lists. 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 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 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;
When user selects multiple choises, a comma separated string of ID's is sent to report procedure ("1,2,4,10"). If the ID is a string (or date/datetime) the ID's 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 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 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 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 a 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 same way as in select lists and the radio buttons:
When we now run the report, the parameter fields autocompletes the user input:
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 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
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, 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 can be made using the following methods:
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.
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
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 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 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
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 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.
myDBR offers automatic parameters that can be used in reports, defaults and parameters queries.
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
inAutoTheme- User's theme
additional user defined SSO automatic parameters- See Single Sign-On for additional automatic parameters
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, 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(30) ) 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 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
inCustomerID will be filled with the result set data referenced with the
cid column reference. The
inStartDate=(inStartDate) means that
inStartDate will be filled with the value of the
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.