dbr.report
- Create a link to another reportdbr.url
- Create a link to an external URLdbr.button
- Create a button containing a linkdbr.list
- Create a list containing a link to a reportdbr.embed_object
- Create a placeholder or a popup window for the linked report's contentdbr.selectlist
- Create a select list for linked reportsdbr.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] [,http_method=post], [callpopupclose=popupclose_function], [callpopupsubmit=popupsubmit_function]
[plain_URL=1]
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, ...]]
Where:
row
. In that case the report is attached to whole row. Any cell, execpt those containing, other linked reports, will act as starting point for the report.
&cancel_button=1
to the link.
There are also special targets available, such as popup_parameter
(which opens the linked report's parameter in a popup before changing the page)
and popup_parameter_new_window
(which opens the linked report's parameter in a popup before opening the linked report in a new window).
For more information, please refer to the Output destination of the linked report.
parameter_name=ColumnReference/ParameterReference
The user is not allowed to change the parameter (p-parameter), nor is it shown in the parameter query form
parameter_name<=ColumnReference/ParameterReference
The parameter becomes editable (e-parameter) to the user in the parameter query form
parameter_name+=ColumnReference/ParameterReference
The parameter becomes editable (u-parameter) in the URL. The parameter is also editable in parameter form if there are parameters that trigger the parameter form (e-parameters or missing parameters)
parameter_name*=ColumnReference/ParameterReference
The parameter is visible in the parameter query form (s-parameter), but the user is now allowed to change the parameter value
parameter_name="Constant"
Pass a constant value to the parameter (p-parameter)
callbefore
is used when user confirmation is required before executing the linked report.
For instance, if the linked report deletes the row where the user clicked, the function can prompt a delete confirmation.
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 the new report, and the user's browser will display the new report. Users can navigate between the reports using the browser's back and forward buttons.
The linked report's content can also be embedded in the report using either a fixed embed element or a movable popup window. This approach benefits users by eliminating the need to switch between reports when additional information is required."
The output destination can also be a new browser window when the predefined value 'new_window' is used, or a dynamic popup can be used with the predefined value 'new_popup'.
The embed_target
-parameter can also contain column references (using [ColumnReference] notation).
This allows multiple embedded elements to be shown dynamically at the same time.
The embed_target
-parameter has the following formats:
dbr.embed_object
command.
When the user clicks the link, the content of the 'mytarget' is replaced with the new content
dbr.embed_object
command. Most suitable in cases where a linked report returns nothing or returns JavaScript not visible to the user.
popup
will open a popup dialog for content
new_popup
will open a new popup dialog for each individual linked window
new_window
will open a link into new browser window
parent_window
will open a link into the parent window (can be used with iframe)
top_window
will open a link into the topmost window (can be used with multiple iframes)
dbr.embed_object
, the target will be created dynamically to the end of the report.
With dbr.embed_object
one can determine the placement.
inline
will 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 a new page. Use only with a report with parameters. If used with a linked report with no parameters, the result will be shown in a popup.
popup_parameter_new_window
-prefix. If the linked report contains parameters that need 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 parameter dialog so the user remains in the main report.
embedded_report_
-prefix. The linked report is embedded in the current report. If the linked report has parameters that need 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 the user can re-run the embedded report with different parameters.
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 are: '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.
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 an 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 to it to show up 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, including values from the original report's parameters. In many drill-down report scenarios, some parameters need to be carried over to the next report. The following example illustrates this:
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
The 'inStaff_id'-parameter will get its value from 'staff_id'-column from the result set whereas the dates a carried over from original parameters (parameter name in parentheses).
When using an embedded element a dbr.embed_object
command needs
to be used to create the placeholder for the linked report's content. If the embed object contains a CSS class no_highlite
the default yellow flashing highlite is not used.
The content of the linked report will be shown in an embedded element placed after the original data.
-- Will not use yellow highlite select 'dbr.embed_object','shared_embed', 'no_highlite'; 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 the email-column (third parameter for the dbr.url) of the query. The 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).
The Premium/OEM version's charts support an unlimited number of linked reports per chart while
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 | |
Spline | spline | 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 | |
Area | area | C | 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 | |
Meter | meter | C | ||
Hierarchy | hierarchy | C L | ||
Diagram | diagram | 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 set's 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').
The 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 a normal linked report with the addition of the dbr.button
command.
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 a 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 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 generate a select list with the 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's 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:
Command dbr.searchable
creates a searchable field and the report output is dynamically fetched when the user types the search term.
The example shows how to use dbr.searchable to fetch dynamic content. The user search term populates the first parameter automatically. You can also use dbr.searchable to find items to be added 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
A 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.
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 make a download link that downloads the linked report as a 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;