Report Linking

One of the powerful features of myDBR is the ability to link reports together. A linked report is just another report in myDBR structure with its parameters and permissions. Parameters can be taken from an original report's parameters and or the report's data. A linked report may also have parameters that are requested from the user at runtime.

Linking to Column Values

We have a film list out of which we want to pick one film and get more details.

The film list is shown with a simple report:

create procedure sp_DBR_report()
begin

select Title, fid
from mydb.film_list;

end

We want the user to be able to select a row from the list and pass the film id as a parameter to a linked report. The report (sp_DBR_film) showing the details and the one we wish to link the existing report to has a parameter 'inFilmID', which is the film's ID.

create procedure sp_DBR_film ( inFilmID int )
begin

select description, release_year
from mydb.film
where film_id = inFilmID;

end

We can link the sp_DBR_film-report to the existing report with the dbr.report-command and give the parameter inFilmID a value that will be taken from the user's selected row's second column. We can then add the resulting code to the original report:

create procedure sp_DBR_report()
begin

select 'dbr.report', 'sp_DBR_film','inFilmID=film_id';

select Title, film_id
from mydb.film_list;

end

The parameter 'inFilmID=film_id' tells myDBR to get the film_id-value from the row the user has selected and pass it onto the sp_DBR_film-report's inFilmID parameter. The linked report can be assigned to any column by passing the column reference to the dbr.report-command. If no reference is given, as in the example, an extra menu icon is placed as the last column.

When the original report is executed, the user will see a mark indicating a linked report and when opened, presents a list of linked reports. The name shown in the list is the name given to the report when it was added to myDBR. The linked report needs to be added to myDBR in order it to show in the linked report list.

The list of linked reports shown in the pull-down menu depends on the user's permissions. The list shows only those reports the user has access.

Linking to Parameters

Sometimes the original report's parameters need to be forwarded to a linked report. For example, if we have a report that takes dates as parameters and produces rows based on the dates, and the user wants to have additional information (using a linked report) from the rows, the dates need to be passed along with the linked report. To refer to the parameters, the parameter names can be put in parenthesis.

create procedure sp_DBR_report_dates(
inStart date,
inEnd date
)
begin
select 'dbr.report', 'sp_DBR_film_locations', 'inFilmID=film_id', 'inStart=(inStart)', 'inEnd=(inEnd)';

select f.film_id, f.description, count(*)
from mydb.films_shown s
  join mydb.film f on f.film_id=s.film_id
where s.shown_date between inStart and inEnd
group by f.film_id, f.description;

end

The 'inStart=(inStart)' parameter tells myDBR to take the value of inStart-parameter from the original report (sp_DBR_report_dates) and pass it to the sp_DBR_film_locations-report's inStart-value.

Passing on Multiple Parameters

Linked reports can have multiple parameters and also values from an original report's own parameters. In many cases when doing drill-down reports, some parameters need to be carried over to the next report. The following example illustrates this situation:

The original report has a couple of parameters:

create procedure sp_DBR_rentals ( inStartDate date, inEndDate date)
begin

select s.first_name, s.last_name, count(*), s.staff_id
from mydb.rental r
  join mydb.staff s on s.staff_id=r.staff_id
where r.rental_date between inStartDate and inEndDate
group by s.first_name, s.last_name, s.staff_id;

end

The linked report has also parameters:

create procedure sp_DBR_rentaldetail ( 
inStaff_id int, 
inStartDate date, 
inEndDate date
)
begin

select r.customer_id, r.inventory_id, r.rental_date
from mydb.rental r
where r.rental_date between inStartDate and inEndDate and r.staff_id = inStaff_id;

end

In order to link the report sp_DBR_rentaldetail to be called from sp_DBR_rentals we need to add that link and define the parameters:

create procedure sp_DBR_rentals ( inStartDate date, inEndDate date)
begin
select 'dbr.report', 'sp_DBR_rentaldetail','inStaff_id=staff_id', 'inStartDate=(inStartDate)', 'inEndDate=(inEndDate)';
	
select s.first_name, s.last_name, count(*), s.staff_id
from mydb.rental r
  join mydb.staff s on s.staff_id=r.staff_id
where r.rental_date between inStartDate and inEndDate
group by s.first_name, s.last_name, s.staff_id;

end

Letting the User Edit the Passed Parameters

If you use format 'inStartDate<=-1' (instead of standard format 'inStartDate=-1'), the parameter becomes editable by the user. See Creating editable reports.

Specifying the Link Location

If no link location (parameter in the dbr.report-command) is specified, the linked report will be attached to linked report mark.

You can also specify the link location to be a specific column or the full row itself. For example, if the user clicks ProductID, (s)he will get more information about the product. Clicking on the delivery date could show more information about the delivery status.

If multiple reports are attached to the same element (column, row etc), a popup menu is shown for user to choose the report.

The link location is specified in the dbr.report command. For example, you can attach the link to 'Name'-column:

create procedure sp_DBR_rentals ( inStartDate date, inEndDate date)
begin

select 'dbr.report', 'sp_DBR_rentaldetail', 'Name', 'inStaff_id=staff_id', 'inStartDate=(inStartDate)', 'inEndDate=(inEndDate)';
	
select concat(s.first_name,' ', s.last_name) as 'Name', count(*), s.staff_id
from mydb.rental r
  join mydb.staff s on s.staff_id=r.staff_id
where r.rental_date between inStartDate and inEndDate
group by s.first_name, s.last_name, s.staff_id;

end

To attach the report to the row, use notation [row]

create procedure sp_DBR_rentals ( inStartDate date, inEndDate date)
begin

select 'dbr.report', 'sp_DBR_rentaldetail', '[row]', 'inStaff_id=staff_id', 'inStartDate=(inStartDate)', 'inEndDate=(inEndDate)';
	
select concat(s.first_name,' ', s.last_name) as 'Name', count(*), s.staff_id
from mydb.rental r
  join mydb.staff s on s.staff_id=r.staff_id
where r.rental_date between inStartDate and inEndDate
group by s.first_name, s.last_name, s.staff_id;

end

Output Destination of the Linked Report

By default, the output destination of the linked report is a new report page. The output destination can also be a separate element in the original report, a popup or an inline element. Which one to choose, depends on the report itself. As a rule of thumb, you should select an output destination which is easiest to the user so that the user does not the loose context when clicking on more info. For example, if you have a report or rows user goes through, and the user can get more information about a row. You could then show the extra information in a popup window or as an inline element allowing the user to keep the context and still get more information. See more options from Output destination of the linked report