One of the powerful features of myDBR is the ability to link reports, enabling drill-down functionality. Linked reports can be attached to any object in a report, such as a column, table, chart, or graph, and can point to another report or an external web page.

Linked reports can also be embedded in an existing report using an embedded element or a popup window. This allows for quick drill-down content without the need for users to switch between reports, and the linked report's content is fetched dynamically. To embed a linked report, you need to set the embed_target parameter.

The linking process begins with the dbr.report command (or dbr.url for external sources), followed by a standard result set. When the dbr.report command is used, the linked report is attached to the next result set. The parameters of the dbr.report command define the linked report, including where it's attached, where the content is displayed, and any required parameters.

Linking to an external URL using dbr.url follows a similar process to dbr.report with the distinction that it requires defining the visible name of the URL.

Basic use of linking:

select 'dbr.report', 'sp_DBR_film';

select Title, fid
from mydb.film_list

The linked report is a stored procedure that may have parameters. These parameters can be derived from the incoming parameters of the original report and/or the data displayed in the report. If the linked report has parameters that are not populated during the linking process, they will be prompted from the user.

Adding a parameter to a linked report: In this case, 'inFilmID' is a parameter for the 'sp_DBR_film' stored procedure.

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

select Title, fid
from mydb.film_list

By default, a linked report will be attached to the object via a link-menu (the user sees an icon). For tabular reports, you can also attach the link directly to a column by specifying the ColumnReference as a parameter."

A linked report is attached to the first column

select 'dbr.report', 'sp_DBR_film', '[Title]', 'inFilmID=fid';

select Title, fid
from mydb.film_list

By default, a linked report will execute and display in the user's browser. Users can navigate between reports using the browser's back and forward buttons.

The content of the linked report can also be embedded in the current report using either a fixed embed element or a movable popup window. This approach streamlines navigation for users who need additional information without switching between separate reports.

Alternatively, the output destination can be set to open in a new browser window using the predefined value 'new_window', or as a dynamic popup with 'new_popup'.

The embed_target parameter can also include column references using [ColumnReference] notation, enabling multiple dynamic embeds simultaneously.

The embed_target parameter supports the following formats:

Links can be made optional by adding a 'show_link' parameter to the command. This parameter can be any valid PHP expression, including ColumnReferences. If the expression evaluates to true, the link is shown; otherwise, it is not displayed.

Examples of show_link include: 'show_link=[myColumn]=="disable"', 'show_link=[myColumn]' or 'show_link=[myColumn]>10'.

Links can also be made optional by adding the no_link option to the 'rowclass' or 'cellclass'. Rows or cells with this class will not have links attached. Using 'no_link' will disable all links to the defined row.

Sometimes, when designing a report network and needing to link to a single report, using a button link is the most straightforward approach. A button link functions like a standard HTML button and provides a direct link to the desired report with predefined parameters. The syntax of a button link is similar to a normal linked report, with the addition of the dbr.button command. The button text can include the linked report's name, custom text defined in the dbr.button command, or a column reference when generating multiple buttons. By default, the CSS class for the button is 'button'. You can add or replace this with your own class definition using a second parameter for the dbr.button command. The button element is wrapped in a wrapper DIV, which can be styled using the third wrapper class parameter.

Here's an example of making a button link to a customer given as a parameter:

select 'dbr.report', 'sp_DBR_Customer', 'inCustomerID=1';
select 'dbr.button', 'Show customer info';

select vCustomerID;

This will create a button-link to the sp_DBR_Customer report, passing the 'vCustomerID' as input (mapped to the inCustomerID parameter in the sp_DBR_Customer report). An example of this can be seen in the demo database.

A linked report can also be associated with a select list. In this example, a linked report is triggered when the user selects an item from the list.

select 'dbr.report', 'sp_DBR_show_continent', 'inContinent=Continent';
select 'dbr.selectlist', 'Europe', 'Select title', 'find';


select distinct Continent
from demo_country;

This will create a select list with an attached linked report. The sp_DBR_show_continent report will be executed when the user selects an item from the list.

A list link is primarily used with embedded reports, where the visible text of each list item corresponds to the first column of the result set.

List links can be styled using user-defined CSS classes in user/userstyle.css. myDBR includes two example styles: 'hlist' for horizontal lists and 'vlist' for vertical lists. These styles can serve as a basis for users to create their own custom definitions.

Here's an example of creating a list-link to display the list of continents. In this example, the second column (sum(Population)) determines the sorting order of the list items.

select 'dbr.report', 'sp_DBR_list_example', 'continent', 'inContinent=Continent';
select 'dbr.list', 'hlist';

select Continent, sum(Population)
from demo_country
group by Continent
order by 2 desc;

select 'dbr.embed_object', 'continent';

This will generate a list of horizontal links and the content of the linked report will be shown in the 'continent' object.

Here's an example where the list is displayed as a menu. By providing the menu title as a second parameter, the list is formatted as a menu.

select 'dbr.report', 'sp_DBR_list_example', 'continent', 'inContinent=Continent';
select 'dbr.list', 'hlist', 'Select continent';

select Continent, sum(Population)
from demo_country
group by Continent
order by 2 desc;

select 'dbr.embed_object', 'continent';

This will generate a list of horizontal links and the content of the linked report will be shown in the 'continent' object.

Clicking the menu expands it for selection:

The dbr.searchable command creates a searchable field, allowing users to dynamically fetch report output by typing search terms.

Here's an example demonstrating the use of dbr.searchable to fetch dynamic content. The user's search term automatically populates the first parameter. Additionally, dbr.searchable can be used to find items to add to the current selection.

select 'dbr.searchable', 'sp_DBR_search_data', 'Filter text', 'in_id=id';

select 2 as 'id';

The search report can be a full myDBR report. In the example we just search for single sample data.

create procedure sp_DBR_search_data(
in_search varchar(30),
in_id int
)
begin

select 'dbr.title', '';
select 'dbr.search', 0;

select 
  title as 'Title', 
  description as 'Description' 
from film
where title like concat('%', ifnull(in_search,""), '%') or description like concat('%', ifnull(in_search,""), '%')
limit 10;

end

You can make a link optional by adding a show_link parameter. This parameter accepts any PHP expression with column references. If the expression evaluates to true, the link is displayed; otherwise, it is hidden.

select 'dbr.report', 'sp_DBR_show_data', 'inID=ID', 'show_link=[Value]>0';

select ID, Name, Value
from mydata;

The [Value] for the first row evaluates to 0 resulting in a false expression "0>0". The link is not shown for the first row.

You can create a download link that allows users to download the linked report in a supported export format by using the 'append' parameter.

Here's an example of downloading an Excel report without any additional parameters:

select 'dbr.report', 'sp_DBR_data', 'append=&export=xlsx';

select 'placeholder;

Create a download link for an Excel report that prompts for parameters in a popup window before initiating the download. Use a dynamic div[] to indicate myDBR to utilize Ajax for fetching the file.

SELECT 'dbr.report', 'sp_DBR_single', 'div[]', 'append=&export=xlsx';

select 'placeholder;