Creating editable reports

myDRR allows for you to edit your data directly in myDBR. myDBR offers 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 not.

Sample editing functionality

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

What we will do is 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 in 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 action
  • newfilm[] will place the output of the sp_DBR_Film_new into a dynamic DIV ([]) called newfilm. Reports parameters will be asked in a popup so that user stays on 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 normal 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 similarily to new row creation. Only difference being 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 in 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 user clicks on the 'Edit' link, the values are passed into the popup and 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 parameter is due to the fact that the dialog used autocomplete value to fetch the id when user enters 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 for you to execute a JavaScript call before the linked report is called.

As an 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 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 to 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 additon, causes the whole 'Del' table cell to accept the click instead of just the text.

Now when the user clicks the 'Delete' column, the 'confirmdel'-JavaScript function is called with reference to 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 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 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.

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 a last parameter to 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 that is doing the actual updating can either reject the update and return the original value or simply 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 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