Report Linking

One of the most powerful features of myDBR is the ability to link reports together. A linked report is a standard myDBR report with its own parameters and permissions. Parameters for a linked report can be derived from the parent report's input parameters, its result set data, or both. A linked report may also include parameters that are requested from the user at runtime.

Linking to Column Values

Consider a scenario where you have a film list and want to retrieve more details for a specific film.

The film list is generated by a simple report:

create procedure sp_DBR_report()

begin

select Title, fid
from mydb.film_list;

end

We want to allow the user to select a row and pass the film ID as a parameter to a linked report (sp_DBR_film) that displays the details. This report has a single parameter, inFilmID.

create procedure sp_DBR_film ( inFilmID int )
begin

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

end

To link these reports, use the dbr.report command in the parent report. We map the inFilmID parameter to the film_id column from the result set:

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 mapping 'inFilmID=film_id' instructs myDBR to retrieve the value from the film_id column of the selected row and pass it to the linked report. You can attach the link to any column by providing a column reference. If no reference is specified, as in this example, a dedicated menu icon is added as the final column.

When the report is executed, a link indicator () appears. Clicking it displays a list of available linked reports. Note that a linked report must be registered in myDBR to appear in this list, and the menu displays only the reports for which the user has permissions.

Linking to Parameters

Sometimes, you need to forward the parent report's own input parameters to a linked report. For example, if a report filters data by date, any subsequent drill-down reports should likely use the same date range. To reference input parameters, enclose their names in parentheses.

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

In this case, 'inStart=(inStart)' tells myDBR to take the value of the inStart parameter from sp_DBR_report_dates and pass it to the inStart parameter of sp_DBR_film_locations.

Passing Multiple Parameters

Linked reports can accept multiple parameters, combining both result set data and parent report parameters. This is essential for creating complex drill-down hierarchies.

The parent report:

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:

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

To link sp_DBR_rentaldetail to sp_DBR_rentals:

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

Editable Parameters

Using the format 'inStartDate<=column' (instead of 'inStartDate=column') allows the user to edit the passed parameter value in a popup before the linked report executes. For more details, see Creating editable reports.

Specifying the Link Location

By default, the link is attached to a dedicated icon. However, you can also attach it to a specific column or to the entire row. If multiple reports are linked to the same element, a popup menu allows the user to choose between them.

To attach a link to the 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 entire row, use the [row] notation:

select 'dbr.report', 'sp_DBR_rentaldetail', '[row]', ...;

Output Destination

By default, a linked report opens in a new page. However, you can also direct the output to a separate element in the parent report, a popup dialog, or an inline container.

Generally, you should select the output destination that provides the best user experience, ensuring users do not lose context when requesting more information. For example, showing details in a popup window or an inline element allows users to view extra information while remaining on the main report page. For more options, see Output destinations.