Creating Editable Reports

myDRR allows you to edit your data directly in myDBR. myDBR offers a different kind of data editing:

The editing is done by linking another report (an editing report) to the original report to which myDBR passes the editable data. Access rights to the editable report define who of the users sees the report as editable and who as a normal non-editable report. If a user has access rights to the editable report, the fields are editable; otherwise, they are not.

Sample editing functionality

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:

  • Allow creating new rows
  • Allow editing Description (text) directly in the list
  • Allow editing Category (parameter from another table) directly in the list using a selectlist
  • Allow editing a row at the same time using a linked report attached to 'Edit'-column
  • Allow deleting a row using a linked report attached to 'Del'-column

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;

Creating new database entities (row)

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:

  • dbr.button will style the report link to be a button
  • Report sp_DBR_Film_new will be used as an action
  • newfilm[] will place the output of the sp_DBR_Film_new into a dynamic DIV ([]) with id newfilm. Reports parameters will be asked in a popup so that the user stays on the same page.

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, all columns at once

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.

'Delete a row'-example, use Javascript to enhance editing

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';
callbefore
Defines the JavaScript function to be called before the linked report is executed
confirmdel
A JavaScript function that is called before the sp_DBR_film_delete-report is called. If it returns false, the report will not be called.
$(obj).parent().children().eq(0).text()
A jQuery call that gets the film title. (parent() is the table row, children().eq(0) is the first column, text() is the content of the column)
[Del]
The report is attached to the 'Del' column.
scriptdiv[]
When the sp_DBR_film_delete-report returns a value (in this case, a JavaScript code block), it will be put to this embedded object. Adding [] to the end of the object name makes myDBR create the object dynamically when needed, and there is no need to create the object by using dbr.embed_object. As the JavaScript is not shown, the location of the embedded object is not important.
event=click
Optional addition that causes the whole 'Del' table cell to accept the click instead of just the text.

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

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:

  • id
    The column to be edited
  • sp_DBR_update_crosstable
    The myDBR report the edited value is passed to
  • inCategory=cat
    sp_DBR_update_crosstable's inCategory-parameter is passed the value for selected 'cat' column value (Category)
  • inWeek=Week
    sp_DBR_update_crosstable's inWeek-parameter is passed the value for selected 'Week' column value (payment_week)

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