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 it's own parameters and permissions. Parameters can be taken from an original report's own parameters and/or the report's data. A linked report may also have parameters that are requested from the user at runtime.
We have a film list out of which we want to pick one film and get more details out of it.
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 which will be taken from the user's selected row's second column. The resulting code can then be added 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 that 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 a 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 user's permissions. The list shows only those reports the user has access to.
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 reference 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.
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 itself:
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
If you use format 'inStartDate<=-1' (instead of standard format 'inStartDate=-1'), the parameter becomes editable by the user. See Creating editable reports.
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, For example if user clicks ProductID, (s)he will get more information about the product. Clicking on delivery date could show more information about the delivery status.
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
By default, the output destination of the 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 itsef. As a rule of thumb, you should select an output destination which is easiest to the user so that user does not the lose context when clicking on more info. For example, if you have a report or rows user goes through, and 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 user to keep the context and still get more information. See more options from Output destination of the linked report