A usual use case is that the user is shown a list of data, and the user is offered an option to add a new row to the list. Additionally, the user can edit the data in the list, either directly or row by row. This is an example of this.

What we will do is the following:

The list is generated using a query:

select 'dbr.hidecolumns', 'film_id';

select  
  f.title as 'Title',
  f.description as 'Description',
  f.release_year as 'Year',
  c.name as 'Category',
  d.Name as 'Director',
  'Edit',
  'Del',
  f.film_id,
  f.category_id,
  f.director_id
from mydata.film f 
  join mydata.category c on c.category_id = f.category_id
  join mydata.directors d on f.director_id = d.director_id
order by f.film_id;

We'll add a button to the bottom of the list which will bring up a dialog with all the required data fields. The linked report will be attached using dbr.report and it will insert the user data into the table and return a refresh command to refresh the original list.

select 'dbr.button', 'New film';
select 'dbr.report', 'sp_DBR_Film_new', 'newfilm[]';

select 'dummy result set for the button';

Where:

Report code for the sp_DBR_Film_new:

create procedure sp_DBR_Film_new(
inTitle varchar(69),
inDescription text,
inRelease_year int,
inCategory tinyint,
inDirector_name varchar(80),
inDirector int
)
begin


insert into mydata.film ( title, description, release_year, category_id, director_id )
values (inTitle, inDescription, inRelease_year, inCategory, inDirector );

select 'dbr.refresh';

end

As the sp_DBR_Film_new is just a regular report, we can attach functionality to the parameters: popup for the category and make the director name an autocomplete field.

The dbr.refresh command at the end of the embedded linked report is returned to the original report causing the refresh and showing the new item.

Editing a row works similarly to new row creation. The only difference is that we will pass the ID for the edited row to the linked report, and instead of doing an insert, we will do an update.

First, we will attach the edit report to the 'Edit' column by adding the dbr.report-command before the select-clause, which shows the data to the user.

select 'dbr.report', 'sp_DBR_Film_upd', '[Edit]', 'popup', 'inFilm_id=film_id', 
  'inTitle<=Title', 'inDescription<=Description', 'inRelease_year<=Year', 'inCategory_id<=category_id', 
  'inDirector<=Director', 'inDirector_id<=director_id';

select  
  f.title as 'Title',
  f.description as 'Description',
  f.release_year as 'Year',
  c.name as 'Category',
  d.Name as 'Director',
  'Edit',
  'Del',
  f.film_id,
  f.category_id,
  f.director_id
from mydata.film f 
  join mydata.category c on c.category_id = f.category_id
  join mydata.directors d on f.director_id = d.director_id
order by f.film_id;

The linked report to be called is sp_DBR_Film_upd, and it is attached to the 'Edit'-column. As we want the user to stay on the same page, we will put the linked report in a popup window. The edited row's ID (film_id) is passed in as a non-editable field ("="), whereas all other fields will be user-editable ("<=") in the parameter popup.

When the user clicks on the 'Edit' link, the values are passed into the popup, and the user can change them:

The code for the sp_DBR_Film_upd looks like this:

create procedure sp_DBR_Film_upd(
inFilm_id int,
inTitle varchar(80),
inDescription text,
inRelease_year int,
inCategory_id int,
inDirector varchar(80),
inDirector_id int
)
begin

update mydata.film
set title = inTitle, description = inDescription, release_year=inRelease_year, category_id=inCategory_id, director_id=inDirector_id
where film_id = inFilm_id;

select 'dbr.refresh';

end

Having both director name and id as a parameter is due to the fact that the dialog used autocomplete value to fetch the id when the user enters the director's name.

You can add additional logic to your editable report by utilizing the dbr.report's 'callbefore'-function. This will allow you to execute a JavaScript call before the linked report is called.

For example, we'll create a basic delete functionality where the user selects the row to be deleted, and myDBR will ask for confirmation about the delete, deletes the row from the database, and when finished, removes the deleted row from the report. All will be done using Ajax without the need to refresh the page.

We will create a report which will delete a given row from the database

create procedure sp_DBR_film_delete( in_film_id int )
begin

delete
from sakila.films
where film_id = in_film_id;

end

We'll modify the original report to include the link to delete-report and embed a delete confirmation javascript-function.

select 'dbr.javascript',  
"
function confirmdel(obj)
{
return confirm('Delete film \\''+$(obj).parent().children().eq(0).text()+'\\'?');
}";

select 'dbr.report', 'sp_DBR_film_delete', '[Del]', 'scriptdiv[]', 'in_film_id=film_id', 'event=click', 'callbefore=confirmdel';

When the user clicks the 'Delete' column, the 'confirmdel'-JavaScript function is called with reference to the selected cell. If the user accepts the delete, the linked report is called, and the output of the linked report is placed into dynamically created 'scriptdiv'-div.

As a result, we'll get a confirmation dialog asking the user permission to delete the row:

We can further improve the delete-report, by making it delete the selected row from the report once it is deleted from the database. We do this by embedding a small JavaScript-script to the delete-report. The linked report's JavaScript context will have a predefined JavaScript variable, 'mydbr_selected_cell', which will contain the cell the user clicked.

create procedure sp_DBR_film_delete( in_film_id int )
begin

delete
from sakila.films
where film_id = in_film_id;

select 'dbr.javascript', "$(mydbr_selected_cell).parent().remove();";

end;

$(mydbr_selected_cell) will make the table cell a jQuery object, the parent().remove(), will delete it's parent row.

In-place editing allows you to mark selected data so it will be editable directly in the report. You can specify which column(s) are editable and define the actual editing report for it.

In the example, we'll define in-place editing to be available in the following cross-table report. We'll use the dbr.editable command to mark column 4 (Budget) to be updatable with the procedure sp_DBR_update_crosstable.

The parameters for the command in the example are:

You do not need to pass the edited column value as it is automatically added as the last parameter to the editing report procedure

create procedure sp_DBR_InPlaceEditing
begin

select 'dbr.editable', 'id', 'sp_DBR_update_crosstable', 'inCategory=cat', 'inWeek=Week';

select 'dbr.crosstab', 'Week';
select 'dbr.count', 'cat';
select 'dbr.sum', 'Total';
select 'dbr.hsum', 'Total';
select 'dbr.hidecolumns', 1;

select category as 'Film category[cat]', 
       payment_week 'Week', 
       amount as 'Total', 
       budget as 'Budget', 
       category_id as 'id'
from film_budget;

end

The report doing the actual updating can either reject the update and return the original value or update the new value. The report gets all the parameters specified in dbr.editable-call and an additional parameter for the value.

The last parameter for the editing procedure is automatically populated by the value of the edited field. You can name the parameter as you like (inValue in the example).

create procedure sp_DBR_update_crosstable(
inCategory int,
inWeek int,
inValue float
)
begin

/* Check the data */
if (inValue>0) then
  /* Accept the update, no need to return a value */
  update film_budget
  set budget = inValue
  where category_id=inCategory and payment_week=inWeek;
else 
  /* Reject the update, return the original value */
  select budget
  from film_budget
  where category_id=inCategory and payment_week=inWeek;
end if;

end