dbr.report - Create a link to another report
dbr.url - Create a link to an external URL
dbr.button - Create a button containing a link
dbr.list - Create a list containing a link to a report
dbr.embed_object - Create a placeholder or a popup window for linked report's content
dbr.selectlist - Create a select list for linked reports
dbr.searchable - Create a search field to get dynamic content
select 'dbr.report', procedure_name, [ColumnReference, ] [embed_target, ] [parameter_name=ColumnReference/ParameterReference, ...] [parameter_name*=ColumnReference/ParameterReference, ...] [parameter_name<=ColumnReference/ParameterReference, ...]
[parameter_name="constant", ...] [,event=click|dblclick] [,callbefore=callbefore_function] [,append=url_parameter] [,'"menu title"'], [,show_link=expression] [html5-data-attribute]
select 'dbr.url', external_url_base, 'URL name', [ColumnReference, ] [embed_target, ] [<=ColumnReference/ParameterReference to be added to base URL] [parameter_name=ColumnReference/ParameterReference, ...]
select 'dbr.button', [button text / ColumnReference ], [button class], [button wrapper class]
select 'dbr.list', [list_class], [menutitle]
select 'dbr.embed_object', embed_target [, embed_class]
select 'dbr.selectlist', default_selection, title[, 'find' [, select_class]]
select 'dbr.searchable', procedure_name[, 'placeholder text' [parameter_name=ColumnReference/ParameterReference, ...]]
parameter_name=ColumnReference/ParameterReferenceThe user is not allowed to change the parameter nor is it shown in parameter query form
parameter_name<=ColumnReference/ParameterReferenceThe parameter becomes editable to the user in the parameter query form
parameter_name*=ColumnReference/ParameterReferenceThe parameter is visible in the parameter query form, but the user is now allowed to change the parameter value
parameter_name="Constant"Pass a constant value to the parameter
One of the powerful features of myDBR is the ability to link reports together allowing drill-down reports. A linked report can be attached to any object in a report (column, table, chart, graph). Report links can point to another report or to an external web-page.
The linked report can also be embedded in an existing report by using an embedded element or a popup window. This allows
quick drill-down content to be shown to the user without the need for the user to jump between the reports. The content of the linked
report is fetched dynamically. To embed a linked report, the
embed_target-parameter needs to be set.
Linking starts with the
dbr.url if linking to external source) followed by
a normal result set. When the
dbr.report-command is shown, the linked report is attached to the next result set.
dbr.report-commands parameters will define the linked report, including where the link is attached to, where the content
of linked report is shown and also the required parameters.
Linking to an external URL using
dbr.url works similarly to
The only difference is that the command requires the URL's visible name to be defined.
Basic use of linking:
select 'dbr.report', 'sp_DBR_film'; select Title, fid from mydb.film_list
The linked report itself is a stored procedure that might have parameters. Parameters can be taken from the original report's incoming parameters and or the actual data shown in the report. If the linked report has parameters that are not populated during the linking, these parameters are asked from the user.
Adding a parameter to linked report. (In this case the 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 attached to the object via an link-menu (the user sees an
With tabular reports, the link can also be attached directly to a column by giving the
column-number as a parameter.
A linked report is attached to 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 the new report and the user's browser will show the new report. The user can navigate between the reports using browsers back and forward buttons.
The linked report content can also be embedded to the report by using fixed embed element or a popup window user is able to move. Benefit of doing this is that the user does not need to jump between the reports if more information is needed.
The output destination can also be a new browser window when a predefined value of 'new_window' is used. A dynamic popup can be used when a predefined value of 'new_popup' is used.
embed_target-parameter can also contain column references (using [ColumnReference] notation).
This allows multiple embedded elements to be shown dynamically at the same time.
embed_target-parameter has the following formats:
dbr.embed_object-command. When the user clicks the link, content of the 'mytarget' is replaced with the new content
popupwill open a popup dialog for content
new_popupwill open a new popup dialog for each individual linked window
new_windowwill open a link into new browser window
dbr.embed_object, the target will be created dynamically to the end of the report. With
dbr.embed_objectone can determine the placement.
inlinewill display the linked report in a row below the linked row. A new row is added to the table report where the content is shown. This link type is available in table reports. Use this when you quickly want to open additional information from a row without losing the user context from the main report.
popup_parameter. If a linked report has user enterable parameters, parameters will be asked using a popup window. The linked result will be shown in new page. Use only with report with parameters. If used with a linked report with no parameters, result will be shown in popup.
popup_parameter_new_window-prefix. If the linked report contains parameters that needs to be asked from the user, the parameters are asked in a popup-window and the linked report is opened in a new window. Use this when you know the user might cancel the report after viewing the paramerter dialog so user remains in the main report.
embedded_report_-prefix. The linked report is embedded into the current report. If the linked report has parameters that needs to be asked from the user, the parameter form is shown in the report. Use this when you want to include another report into an existing report in a way that user can re-run the embedded report with different parameters.
Links can be made optional by adding 'show_link' parameter to the command. A show_link parameter can be any valid PHP expression that can also contain ColumnReferences. if the expression evaluates to true, the link is shown, otherwise not.
Examples of show_link are: 'show_link=[myColumn]=="disable"', 'show_link=[myColumn]' or 'show_link=[myColumn]>10'.
Links can also be made optional by adding rowclass / cellclass
no_link to the row / cell.
Rows / cells with this class will not have links attached. no_link-will disable all links to the defined row.
We have a film list from which we want to pick one film and obtain more details from it.
The film list is shown with a simple query:
select Title, fid from mydb.film_list
The report (sp_DBR_film) showing the details and the one we wish to link existing report to has one 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
Then we can link the sp_DBR_film-report to the existing report with the
dbr.report-command and give the parameter 'inFilmID'
value which will be taken from the user's selected row's second column.
select 'dbr.report', 'sp_DBR_film','inFilmID=fid'; select Title, film_id from mydb.film;
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 to.
Linked reports can have multiple parameters and also values from the 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 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
The 'inStaff_id'-parameter will get it's value from 'staff_id'-column from result set whereas the dates a carried over from original parameters (parameter name in parentheses).
When using an embedded element a
to be used to create the placeholder for the linked report's content.
The content of the linked report will be shown in an embedded element placed after the original data.
select 'dbr.embed_object','shared_embed'; select 'dbr.embed_object','embed_with_id'; -- Will open the link to predefined 'popup' select 'dbr.report', 'sp_DBR_link_location_drill', 'popup', '[shared_popup]', 'inID=ID'; -- Will open the link always to new popup. No need to declare the new_popup select 'dbr.report', 'sp_DBR_link_location_drill', 'new_popup', '[new_popup]', 'inID=ID'; -- Will open the link to popup with ID. No need to declare the popup select 'dbr.report', 'sp_DBR_link_location_drill', 'popup[ID]', '[popup_id]', 'inID=ID'; -- Will open the link always to same embedded object. We declared shared_embed select 'dbr.report', 'sp_DBR_link_location_drill', 'shared_embed', '[shared_embedded]', 'inID=ID'; -- Will open the link inside an embedded object (ID). We declared the placeholder embed_id select 'dbr.report', 'sp_DBR_link_location_drill', 'embed_id.target[ID].kwn', '[embedded_id]', 'inID=ID'; -- Will open the link always to new window select 'dbr.report', 'sp_DBR_link_location_drill', 'new_window', '[new_window]', 'inID=ID'; -- Will open the parameter form in a popup and open redirect the user to the linked report select 'dbr.report', 'sp_DBR_film_detail', 'popup_parameter', '[popup_redirect]', 'inID=ID'; -- Will open the parameter form in a popup and open the linked report in a new window select 'dbr.report', 'sp_DBR_film_detail', 'popup_new', '[popup_new]', 'inID=ID'; -- Will embed the report (including parameter form into the current report) select 'dbr.report', 'sp_DBR_film_detail', 'embedded_report_film_detail', '[embed_report]', 'inID=ID'; select film_id as 'ID', 'Shared popup' as 'Shared popup[shared_popup]', 'Always new popup' as 'New popup[new_popup]', 'Popup by ID' as 'Popup by ID[popup_id]', 'Shared embedded' as 'Shared embedded[shared_embedded]', 'Embedded by ID' as 'Embedded by ID[embedded_id]', 'New window' as 'New window[new_window]', 'Ask parameters in this report - redirect to new' as 'Popup - Redirect[popup_redirect]', 'Ask parameters in this report - open in new window' as 'Popup - New[popup_new]', 'Embedded report' as 'Embedded report[embed_report]' from film limit 10; select 'dbr.embed_object','embedded_report_film_detail';
The syntax of dbr.url is similar to dbr.report. The difference in usage is that external URL's do not have permissions so all external links are available to all users.
We'll assign a URL to email-column (third parameter for the dbr.url) of the query. Second parameter (URL name) is used when the URL is displayed in a menu (multiple linked reports attached to a column or no link position column is specified). Data from rows can be embedded into the url by adding ColumnReference inside a bracket in the external_url_base.
select 'dbr.url', 'https://www.mydbr/forums/topic.php?id=[topic_id]#post[topic_last_post_id]', 'Show topic', '[email]'; select u.topic_title as 'Title', u.user_email as 'email, t.topic_id, t.topic_last_post_id from forums.bb_topics t join forums.bb_users u on u.ID = t.topic_last_poster order by topic_time desc
Will produce URL: https://mydbr.com/forums/topic.php?id=158#post-686 when user clicks a email-column in a row where topic_id=158 and topic_last_post_id=686.
You can also assign linked reports to charts. The report is connected to a chart element (bar/line/node).
Flash-charts have a limit of a single linked report. If the report has multiple reports linked to a flash-chart, the first one is always used. The Premium version's image charts supports unlimited number of linked reports per chart.
The following table represents the supported linking in charts.
|Chart type||Chart command||Fusion Charts||Chart Director||Graphviz|
|Column||column||C L||C L|
|3D Column||column3d||C L||C L|
|Multi-Series column||mscolumn||C L||C L|
|3D Multi-Series column||mscolumn3d||C L||C L|
|Stacked Column||stackedcolumn||C L||C L|
|3D Stacked Column||stackedcolumn3d||C L||C L|
|Stacked Bar||stackedbar||C L||C L|
|3D Stacked Bar||stackedbar3d||C L|
|Stacked Area||stackedarea||C L||C L|
|3D Stacked Area||stackedarea3D||C L|
|Line||line||C L||C L|
|3D line||line3d||C L|
|Multi-Series Line||msline||C L||C L|
|Pie||pie||C L||C L|
|3D Pie||pie3d||C L||C L|
|Bar||bar||C L||C L|
|3D Bar||bar3d||C L||C L|
|Multi-Series Bar||msbar||C L||C L|
|3D Area||area3d||C L|
|Multi-Series Area||msarea||C L||C L|
|Donut||donut||C L||C L|
|3D donut||donut3d||C L|
|Multi-Series Column Line Double Y||mscolumnlinedy||C L||C L|
|3D Multi-Series Column Line Double Y||mscolumnlinedy3d||C L||C L|
|C ... Chart supported, L ... Linking supported|
Charts are always based on a single selection set. The linked reports are linked to this result sets particular row.
Let's examine a simple case where a report is linked to a pie chart. The report itself is a simple one:
select 'dbr.report', 'sp_DBR_LinkedReport', 'vCode=code'; select 'dbr.hidecolumns', 'code'; select 'dbr.chart', 'pie'; select "First", 10, 1 as '[code]' union select "Second", 21, 2 union select "Third", 35, 3 union select "Fourth", 44, 4;
The report has a result set with three columns: text expression, value and a key ID. > The linked report is called with the invisible key ID (column 'code').
Report will produce a pie chart with a linked chart attached to the slices. Column references (like vCode=code), refer to the particular row the user clicks ("Second" -> vCode's value becomes 2)
Sometimes when designing a report network and there is a need to link to a single report,
a direct link to another report is easiest done with a button link.
A button link acts as a standard HTML button and will contain a direct link to the desired report with predefined parameters.
The syntax of the button link is similar to normal linked report with addition of the
The button text can contain the linked reports name, a free text defined in
dbr.button-command or a column
reference when multiple buttons are generated.
The default css-class for the button is 'button'. You can add/replace this with your own class definition
via a second parameter for
dbr.button-command. A button element is wrapped into a wrapper div, which you can style with the third wrapper class-parameter.
Make a button-link to a customer given as parameter.
select 'dbr.report', 'sp_DBR_Customer', 'inCustomerID=1'; select 'dbr.button', 'Show customer info'; select vCustomerID;
This will generate a button-link to the sp_DBR_Customer report and passes the 'vCustomerID' on as an input (inCustomerID parameter for sp_DBR_Customer-report). You can see an example use of this in the demo-database.
A linked report can also be attached to a select list. In the example a linked report is called when 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 generate a select list with attached linked report. sp_DBR_show_continent-report will be run when user selects an item from the list.
A list link is a link used mostly with embedded reports. The list visible text will be the first column of the result set.
A list link can be styled by a user defined CSS class in user/userstyle.css. myDBR comes with two example styles: 'hlist' for horizontal lists and 'vlist' for vertical lists. These can also be used as a basis for the user's own definitions.
Make a list-link and show the list of continents. The second column (sum(Population)) in the example is used to define the sorting order for 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.
This is an example with the list shown as a menu. By providing the menu title as a second parameter, the list is shown 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.
When the user clicks the menu, it will be expanded as a selection:
dbr.searchable creates a searchable field and the report output is dynamically fetched when user types the search term.
The example shows how to use dbr.searchable to fetch dynamic content. You can also use dbr.searchable to find items to be added to 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. Un 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
Link can be made optional by adding a show_link parameter. The show_link parameter can contain any PHP expression with column references. If the expression evaluates to true, the link is shown, otherwise not.
You can make a download link which downloads the linked report as supported export format when you use the 'append'-parameter
Download an Excel report without any parameters
select 'dbr.report', 'sp_DBR_data', 'append=&export=xlsx'; select 'placeholder;
Download an Excel report and ask parameters in a popup window before downloading (a dynamic div idicates to myDBR to use Ajax to get the file)
SELECT 'dbr.report', 'sp_DBR_single', 'div', 'append=&export=xlsx'; select 'placeholder;