myDBR reports are database native stored procedures that you can create using the built-in SQL Editor (or any SQL editor that 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 'Add report' function. When attached, the report is ready to run.
Once the report has been attached to the myDBR structure, you are free to 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.
As an example, we'll write a simple report and go through the steps needed to use myDBR. The setup used in the example is the default setup where the user has the data in his/her's own database (called 'mydb') and myDBR is using 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 to the mydbr database.
1. Click 'Add report' from main screens 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 with 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 by using the Query Browser or by editing the code manually.
You'll notice that we are giving each column a name which 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 "Add 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 match the prefix mentioned earlier and 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 is 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 normally stored procedure input parameters. By default, myDBR uses stored procedure parameters 'as is', but myDBR can also further extend the parameters by:
In this example, we'll set 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 assigned to myDBR, we do not need to reassign it. Just 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 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.
In order to learn how to use more advanced features on parameters, please consult the Managing Reports/Report Parameter for further info.