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.
If a Report Link is part of a plotted chart column, it will become a clickable link within a FusionChart. FusionCharts support links in Vertical Bar, Horizontal Bar and Pie charts.
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.
There are two different types of links that can be created. If QLR Manager output containing report links is intended to be e-mailed or downloaded in a format other than HTML, the Text link is recommended for wider compatibility.
|Text link||Produces a standard hypertext link. The font face and size is inherited from the Report Body section of the Layout panel. The Link color can be selected along with Bold, Underline and Italic text attributes. A Hover style can also be defined which is applied when the cursor is placed over the link.|
|Button link||Selects a standard form button. The font face and size is inherited from the Report Body section of the Layout panel. The button's foreground and background color can be specified, along with Bold, Underline and Italic text attributes. Rather than assigning colors to the button, it may be desirable to use the browser's default button style. This is accomplished by checking the "Use default button style" checkbox.|
|Custom CSS||Cascading Style Sheet (CSS) styles can be applied to both Text and Button links. This is accomplished by entering the CSS
syntax into the Custom CSS textarea. The CSS class name must be provided AND the checkbox to actually apply the custom CSS
must be checked. Every saved Report Link using custom CSS should be given it's own unique Custom CSS class name so if a report contains
more than one link with custom CSS, QLR Manager can apply the correct CSS to each link. For illustration purposes, the CSS class name
and custom CSS textarea fields have examples that can be used as a guide.
There are numerous websites that offer the capability of creating CSS to format buttons. A web search for "css button" will return hits to many of these sites.
Note: Most online button generators produce CSS to be applied to an <href> tag that emulates the behavior of a button. Therefore, Text link should be selected in most cases when applying this type of CSS. After making changes to the CSS, it is important to re-save the Report Link to see the changes applied in the Report.
When a link is clicked, a query is executed in either the existing QLR Report panel, or in a new browser Window. There are several options for selecting a target for the linked report output:
|New window for every link||This will open a new window when any link is clicked. If five links are clicked, then five new windows will be opened.|
|New window for each link column||This will create a new window when a report contains more than one linked report. If two columns in a report are links, then a maximum of two new windows will be opened. All links from within the same column will target the same window.|
|Same window for all links||All links that are clicked will open in a single window. In order for this to hold true for reports with multiple linked columns, all the links must use this selection.|
|Existing window or frame||The output will be sent to the QLR window or frame that launched the report. A "Return to Previous" link is provided under the Header to return to the parent report after spawning the child report. If used with a Report Object, this selection will open the report in the same window or frame that links to the report.|
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.
When a new window is specified, the New window position can be selected. The approximate position is over the opener window and can be launched upper left, upper center, upper right, middle left, middle center, middle right, lower left, lower center, lower right, or the browser's default popup window positioning.
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.
Conditional links can be created with the use of an evaluated formula. This is accomplished by writing a formula that assigns the keyword qlr_omit_link to the value in the link column. Using the linked report example below, if the following evaluated formula was written for report column 1, links would be omitted for the Billings brothers.
if ($val=='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:
|Baitshop customers with order information
- Columns 1 and 4 access Linked Reports -
|First name||Last name||Customer profile|
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.