Linked Reports support the spawning of a new (child) report when the User clicks on a link in an existing (parent) report. To create a link, an existing Query and Layout is selected to be executed when a link is clicked. Creating a link in a report is accomplished in the Report Columns section of the  Layout  panel.

Data values in the parent report can be mapped to query variables that exist in the child query. Child reports can also spawn their own linked reports. A report can contain multiple links. A linked report does not have to be a "report". It might actually be a query that executes a command, such as INSERT or UPDATE. Any valid existing query can be executed as a linked report.

Links are "portable", meaning that they can be sent in e-mails, downloaded to HTML files, or be present in a Report Object. QLR Manager encodes into the link the database connection values of the User ID creating the link. The link connects to the database as though it is the creator User ID connecting. A unique encryption key is created for each installation of QLR Manager, so links created in one installed instance of QLR Manager cannot be altered to access another installed instance of QLR Manager. Although this method is very secure, it is recommended that links intended for external access be created using a User ID with limited access authority. This eliminates the risk of exposing the "root" or other Power User's ID and password. Given enough time and effort, anything that can be encoded, can be decoded.

The link encryption key resides in QLR Manager's qlr_info table. If there is a need to change the encryption key, simply delete the "link key" entry from the qlr_info table. QLR Manager will automatically recreate a new link key the next time a link is created. Links created with the old key will be invalid and no longer work.

Report links are defined by clicking on the Create/Edit Report Links icon in the Report Columns section of the  Layout  panel. In general, a link defines what the link looks like, the browser window behavior for the child report, as well as which query is executed when the link is pressed.

The height and width (in pixels) of a newly opened link window can be specified. If either value is left blank, the blank dimension will default to the size of the parent window, also called opener window. The Window features allow for further customization of the new window. The window's "chrome" features can be selectively added or omitted, along with control over whether the window should have scrollbars and can be resized.

Begin by creating and saving a query that will produce the desired parent report. It is recommended that the tables used in the query be fully qualified by prefixing the table names with the appropriate database/schema. The reason for this is that the database/schema that is in use at the time the link is created may be different than the database/schema being used when the query which will be used as the basis for the link was created. Next, determine which column(s) will be linked to child report(s). Author and save the child queries. It may be desirable to include query variables in the child queries that can be mapped to data in the parent report. It is possible, but not required, to map the data from a column in the parent report to the query variables. This allows for the linked reports to present dynamic data.

Execute the parent query and access the  Layout  panel. Click the Create/Edit Report Links icon in the Report Columns section of the  Layout  panel. This will open the Report Links window where link characteristics are defined. It is necessary to select an existing query to be executed when a link is clicked. Specifying a layout is optional. After a query has been selected, the  Examine  button can be clicked to display the query variables associated with the query. If query variables exist and are displayed, it is possible to map the data from a column in the report to the query variables. In order to perform the mapping, the Layout's report column number must be entered into the space provided. If the linked query contains additional query variables that are not mapped to a data column in the report, the query variable data collection panel will be presented to collect values for the additional variables when the link is executed.

Note: Any changes made to a report layout or a report link must be applied to the report. This requires the report to be rerun by clicking either the  Apply Layout  button in the  Layout  panel or the  Report  tab in the Header.

A column in a report becomes a link when a valid Link owner and Link name is assigned to the corresponding Col # in the report. If an invalid Link owner or Link name is assigned, or the Col # is omitted, the creation of a link is ignored. An error message will appear in the report title and the data in the report remains as is (without links). If report links have been previously defined, they can be accessed by clicking the button. To remove a link, both the Link Owner and Link Name fields should be blanked out.

You can conditionally create links with the use of an evaluated formula. This is done by writing a formula that assigns the keyword qlr_omit_link to the value in the link column. Using the link report example below, if the following evaluated formula was written for report column 1, links would not be created for the Billings brothers.

if ($val[3]=='Billings') $value='qlr_omit_link'

The following is an example of defining and using two Linked Reports. The example makes use of both available linking styles... A form button and a hypertext link with font attributes applied. The hypertext link also has custom link and hover colors defined.

This scenario involves an initial report that lists a subset of customers that have product orders. The objective is to be able to display a list of orders for a particular customer and also offer a link to the Customer profile. The fully functional example can be experienced at our Online Demo by logging onto the Query Editor with ID and password of guest, then retrieving the query baitshop customer list and clicking the  Run Query  button.

The initial report is created by executing the following query:

select c.custnum, c.first_name, c.last_name, 'customer profile'
from qlrmanager_baitshop.customer c, qlrmanager_baitshop.orders o
where c.custnum=o.custnum
group by c.custnum, c.first_name, c.last_name
limit 10

The following report is produced:

The first column's Linked Report allows a user to click on the customer number and load a new report, configured to open in the QLR Manager window and display all of the selected customer's orders. A query to generate the desired report already exists for frank as the owner and is called baitshop customer orders. It uses a query variable called [custnum] that is passed to the query when the button is clicked.

The query associated with the form buttons in column #1:

select o.ordnum, o.ord_date, c.custnum, c.first_name, c.last_name
from qlrmanager_baitshop.orders o, qlrmanager_baitshop.customer c
where o.custnum  = [custnum] and o.custnum=c.custnum
order by o.ordnum

The second Linked Report in column #4 allows a user to click the hyperlink and view the Customer's profile. The query for this link is also owned by frank and is called customer profile. Instead of loading into the QLR Manager window, this link is configured to open in a new window.

The query associated with the hypertext links in column #4:

select *
from qlrmanager_baitshop.customer
where custnum = [custnum]

Notice how [custnum] is also used as the query variable for the customer profile query. Even though the link is in column #4, the Report Link is mapped to column #1 so the custnum value is passed when clicked.

Multiple levels of "drill down" can be achieved by employing the same techniques described above to child reports. When viewing the child reports, either Report tools or Output tools will be presented in the Header to allow for downloading various formats or e-mailing the output. Since linked reports only work within the QLR environment, all linked columns within the report are converted back to plain text for downloads and e-mails.