myDBR reports are database-native stored procedures that you can create using the built-in SQL Editor (or any SQL editor you prefer). For more details, see myDBR SQL Editor.
To create a report, you will create a stored procedure in the myDBR database. The report stored procedure has a prefix ('sp_DBR') which helps myDBR to differentiate report procedures from other procedures in the database. Once the procedure has been created, it will be attached to the myDBR application's report hierarchy via the 'New report' function. When attached, the report is ready to run.
Once the report has been attached to the myDBR structure, you can edit (or drop/recreate) the stored procedure behind it. This allows maximum flexibility when developing your reports.
In addition to normal SQL data queries, myDBR reports include myDBR commands, which are interpreted by myDBR when the report is run.
For example, we'll write a simple report and go through the steps needed to use myDBR. The setup used in the sample is the default setup where the user has the data in his/her own database (called 'mydb') and myDBR uses its own reporting database ('mydbr'). The examples are partly based on MySQL sample database Sakila. If you wish to try out myDBR with Sakila-database, please follow the link and download it.
The first report will list films from the table 'film' from the user database 'mydb'. (We'll add up features to the report later).
We'll write a stored procedure called sp_DBR_FilmList that will show the films in the database. 'sp_DBR' is the default prefix for the report procedures. myDBR will use this prefix to assist you when you attach the stored procedure as a report in the myDBR structure (i.e. distinguishes the report from other stored procedures in the database). In the example, we'll create the report by using the built-in SQL editor and store it in the mydbr database.
1. Click 'New report' from the main screen's Admin tools.
If no unassigned stored procedures (procedures starting with 'sp_DBR' that are not used in a report) can be found, myDBR asks for the procedure name to be created:
2. Enter the procedure name and click OK.
myDBR will create a template report into which you will add your queries and logic.
If you want to browse the database structure or get help writing the SQL code, you can open the Query Builder by clicking the "Show Query Builder"-button. You can pick the database, table, and columns you wish to be included in the report. Please see more info on myDBR SQL Editor.
3. Create a new procedure using the Query Browser or manually editing the code
- Select the database
- Select the table
- Select the columns
- Click 'Generate SQL into selection'
- Edit the procedure if necessary and click 'Execute' to save the procedure
You'll notice that we give each column a name that the user sees as a column header in the report when the report is run. If you do not specify the name, the name is derived from the column/data used.
Click 'Execute' to save the report procedure to the database. If there were any errors, fix them and click 'Execute' again. Once the report's stored procedure is successfully saved into the database, click the "New report to myDBR" button to attach the procedure as a report in myDBR's report structure.
myDBR will fetch all the procedures from the database which a) match the prefix mentioned earlier and b) that have not yet been assigned as a report. The report name is the name visible to the user, and the description shows up in the report listings. After we accept the data, the report will be assigned to myDBR and will be ready to be used.
For your convenience, myDBR opens up a screen where you can name the input parameters and grant privileges to the report. We'll skip this for now and go back to the main screen.
You should now be able to see the report on the main screen and run it.
The report has a title, which, by default, is the same as the report's name. If you wish to change the title in the report (for example, add dynamic data/parameters to it), you can use the command dbr.title to achieve this. By default, myDBR makes columns sortable. Just click on a header column to sort the data.
We'll extend the report by adding parameters to the report. Report parameters are stored procedure input parameters. By default, myDBR uses stored procedure parameters 'as is', but myDBR can also further extend the parameters by:
- Naming the parameters
- Providing assistance based on data type (date pickers)
- Using predefined lists as parameters (as radio buttons, select lists)
- Using live autocomplete fields from the database (AJAX autocomplete)
- With linked reports automatically fill in parameters based on user input/report flow
In this example, we'll set a film name (or part of it) as a parameter. We'll introduce 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 to myDBR, we do not need to reassign it. Edit the procedure, and you are ready to run it. Now when you re-run the report, the parameter is asked from us as an input:
The variable name is not necessarily the name you want the user to see. Let's create a more descriptive name for it. We'll go back to the
main screen and edit the report's data by clicking the 'edit'-link below the report.
You'll go to 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 give a name to our parameter.
When we re-run the report we'll see that the new name for the parameter is used.
To learn how to use more advanced features on parameters, please consult the Managing Reports/Report Parameter for further info.