Linked reports

Commands

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

Syntax

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]
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:

ColumnReference
Optional parameter which the linked report is attached to. If no column is specified, the linked report is shown in the link-menu that will be automatically attached to the object. Note that the ColumnReference must be using format "[column_number]".
embed_target
Target embed element for linked report content. embed_target can be a DIV created with 'dbr.embed_object'-command or one of the predefined names. (Do not use any special characters, just a-z.). If the object is visible and linked report fetches data into the object, browser by default scrolls to the object to show the new data. If object contains CSS class "no_scroll_to", no scrolling is done. If you add [] to the end of embed_target, the embed_targets is created automatically (no need to use dbr.embed_object-command). This is useful in cases where embed_target contains non-visible values, for example a report returns JavaScript to remove selected line. If a target is a popup, a Cancel-button can be added to parameter popup by appending '&cancel_button=1' into the link. Special targets are popup_parameter (opens linked report's parameter to popup before changing page) and popup_parameter_new_window (opens linked report's parameter to popup before opening the linked report to new window)
procedure_name
Refers to another myDBR report's stored procedure
parameter_name
Refers to linked stored procedure's / URL's parameter name to which we want to pass value to
ColumnReference/ParameterReference
Value to be passed to the parameter. Different options include:
  • parameter_name=ColumnReference/ParameterReference The user is not allowed to change the parameter nor is it shown in parameter query form
  • parameter_name<=ColumnReference/ParameterReference The parameter becomes editable to the user in the parameter query form
  • parameter_name*=ColumnReference/ParameterReference The 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
See more info on ColumnReference/ParameterReference at Referencing columns and parameters.
event=click|dblclick
The linked report can be attached directly to a report table cell so that clicking / double-clicking the cell will trigger the report execution directly. This option can only be used when 'column' is defined. Using this option uses the cell onclick/ondblclick-event instead of the href.
callbefore
A Javascript function which is executed before the linked report is launched. If the function returns true the linked report is executed, if it returns false, no linked report is executed. The function will get the clicked table cell as a parameter. Callbefore is used in cases where user confirmation is needed before the linked report execution. For example, if the linked report deletes the row where the user clicks, the function can ask confirmation for the delete.
append=url_parameter
If you want to add additional parameters to the generated URL, you can use the 'append'-parameter. For example if you want to make link reference to PDF document instead of a report add 'append=&export=pdf' as a parameter.
list_class
A CSS class defined in the user/userstyle.css file. Defines the HTML ul/li list style used. A special 'htlist' for horizontal lists and 'vlist' for vertical lists is pre-defined and used as the basis for the user's own styling.
embed_class
A CSS class defined in the user/userstyle.css file or in the report with dbr.html. If a special 'popup' value is used, the placeholder is a popup window allowing multiple separate popups to be used in a report.
default_selection
A default selection in the select list
'find'
Select list will be searchable if this constant is set.
"menu title"
If linked report is shown in a menu, the menu item can be defined here.
show_link=expression
If a a PHP expression evaluates to true, the linked report is placed on object. The expression is any valid PHP expression that can contain ColumnReferences. Defines if link is created based on the data.
http_method=post
Uses HTTP POST when running the linked report. Can be used to handle texts that exceed HTTP GET URL maximum length
html5-data-attribute
A link to a raport can be put in a html5 data-attribute with this. By setting string "html5-data-callback", the link to the report will be placed to a "data-callback" attribute. This allows scripts to access the link (for example editing callback).

Explanation

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.report-command (or 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. The 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 dbr.report. 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

Linked report parameters

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

Linked report placement

By default, a linked report will attached to the object via an link-menu (the user sees an icon). 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

Output destination of the linked report

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.

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:

mytarget
Will use the 'mytarget' (or whatever name you choose to use) DIV defined by dbr.embed_object-command. When the user clicks the link, content of the 'mytarget' is replaced with the new content
mytarget[]
myDBR will automatically create a DIV with id mytarget (or whatever name you choose to use) if one is not defined. No need to create one dbr.embed_object-command. Most suitable in cases where a linked report returns nothing or returns JavaScript not visible to user.
popup
A predefined name popup will open a popup dialog for content
new_popup
A predefined name new_popup will open a new popup dialog for each individual linked window
popup[_whatever][ColumnReference]
Will dynamically create a new popup window for each ID (you can specify multiple columns). Allows identifying the popups that when a user clicks the same linked report in a different row, each of them open up in a separate window. For example if the ColumnReference points to customerID 1 and 2, and the target would be popup_customer[CustID], the dynamically created popups, would get the names popup_customer1 and popup_customer2. This would allow the user for example to compare customers together in separate popups.
new_window
A predefined name new_window will open a link into new browser window
mytarget[ColumnReference1][ColumnReference2]
When a user clicks a row, where the column reference is '2012', it will use the 'mytarget2012' as the target (you can use multiple column references if you like). If the target has not been created using 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
A predefined name 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.
mytarget.myid[ColumnReference1]
When a user clicks a row, where the column reference is '2012', it will create a target myid2012 inside target mytarget. The 'myid' element is a text string making sure the id is unique. If the myid2012 element has not been created using dbr.embed_object, it will be created dynamically.
mytarget.myid[ColumnReference1].classname
This is the same as the previous, but with additional classname for myid2012 target. For example 'kwn'-class (keepwithnext) makes linked elements appear side by side inside the mytarget element.
popup_parameter
A predefined name 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
A predefined name 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_[_whatever][ColumnReference]
A predefined 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.

Optional links based on data

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.

Examples

Basics of linking

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.

Passing multiple parameters

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).

Output destination of the linked report example

When using an embedded element a dbr.embed_object-command needs 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';


Linking to external webpages with dbr.url

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.

Linked reports and charts

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  
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

Simple chart linking

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)

Buttons as links

Explanation

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 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.

Example

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.

Select lists as links

Example

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.

Lists as links

Explanation

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.

Example

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.

Example

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:

Searchable

Explanation

Command 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

Optional links

Explanation

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.

Example

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 false expression "0>0". The link is not shown for the first row.

Download links

Explanation

You can make a download link which downloads the linked report as supported export format when you use the 'append'-parameter

Example

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;