qxparmsandlinks

Using Hyperlinks, Parameters and Sub-Queries

Overview

Query Express uses hyperlinks to link and pass information between relevant queries -- just as Internet Web pages use links to other relevant web pages, which has been one of the primary forces driving the success of the Web. Similar to the Internet, this provides an easy and logical method for users to jump to related queries and it allows new queries to easily leverage existing ones.

Example:

A Customer Info Report could list all Customers and Addresses. For each Customer ID with an order, the report may also contain a hyperlink to a separate Customer Orders Report, passing it the Customer ID so the Customer Orders Report can run immediately and display all orders for that Customer. The Customer Orders Report may also contain links to a separate Product Information Report, passing the Product ID for each item in the order so that report can run immediately.

"Linking" of Qx Reports really consists of two standard mechanisms. First, you must include a standard HTML Hyperlink to so the browser recognizes it as such. In the hyperlink, you create an Anchor to tell the browser the destination URL that it should go to if selected. The destination URL will be Query Express itself. The second mechanism is to include variables in the destination URL that will be passed to Query Express as Parameter values.

Hyperlinks

Basic Hyperlink:

The structure of an HTML Hyperlink anchor is as follows;

<A HREF="http://qxserver.com/cgi/qexpress.fcgi?qxFile=Customer_Orders&customer_id=1234"> 
Customer Orders </A>

The Anchor tags start with <A> and end with </A>. The browser will only display the text between the two tags (i.e. Customer Orders)

The HREF element identifies the destination URL (i.e. http://qxserver.com/cgi/qexpress.pl )

Variables are separated from the destination using a question mark (?) for the first variable, and ampersands (&) for each subsequent variable. They are sent in name-value pairs,

Variable Name	Variable Value
qxFile	Customer_Orders
customer_id	1234

Essentially, the Hyperlink anchor shown above calls Query Express, passing it the name of the File (qxFile) which contains a report. Also passed is the variable (name and value) called customer_id, which the report is expecting to use as a Parameter (&customer_id) within its SQL.

Reserved Variables:

The Hyperlink's destination URL does not always have to be another Qx report. The Hyperlink can be to any valid Internet destination. If the destination is another Qx report, you are expected to provide certain variables so Qx can find your report.

Variable Name	Description	Required / Default
qxFile	The directory path and name of the Qx Report File, relative to the root of either private or public Libraries.	Required
qxDir	Library Directory Flag: 0 - Public Library R - User's Private Library A - Administrator's View of all Private Libraries	Optional (Default 0)
qxQuery	Desired Query # within the target Report which will be executed (example 0, 1, 2, 3, etc.)	Optional (Default 0)
qxSQL	The SQL to be executed when using Qx SQL Freehand (when no specific file is requested.) The SQL is provided by the text box as part of the Qx SQL Freehand form. If a Qx Report File is selected, the SQL stored within that query will be used instead of the value in qxSQL.	Required for Qx SQL Freehand only
qxSystem	The Target system which the query will be executed on. This system name is provide by the pull-down box as part of the Qx SQL Freehand form or any Qx Report File.	Requred
qxRun	The type of output that will be generated. The value for qxRun is normally provide by either "Run Query" or "Excel" form buttons. These valid values for qxRun are; Run: Normally an HTML table. If the Qx Report File was built to generate Binary or XML (or XSLT), then that output will be generated instead. Excel: A Microsoft Excel spreadsheet. XML: Raw XML output, or transformed output (i.e. HTML) if an XSLT was provided in the Qx Report File.	Required

Static Parameters:

Two Reserved Parameters are set automatically at run-time by the Query Express Server. They can be used within the Query SQL as shortcuts to the name of the Qx server, script and library file. This helps avoid hard-coding the names of servers and files within a script, only to find the file fails after moving the file or the entire server. As with all Reserved Parameters, these parameters always begin with &qx to prevent them from being confused with any user parameters. Developers can avoid creating conflicting parameters in their Query SQL simply by using parameter names that do not start with &qx.

Developers may still access some reserved parameters within the Query SQL. When detected, the Qx server will substitute these Parameters with the run-time values described below.

Parameter	Substitution Value
&qxURL	Expands to the Full URL of the server and Query Express script. Example: http://qxserver.com/cgi/qexpress.fcgi
&qxThisFile	Expands to include the directory ID and filename of the current Query. Example: qxDir=0&qxFile=Order_Entry/Customer_Info

Generating the Hyperlink:

For the Hyperlink code to be available on a report, the query's SQL must generate it. This is done by integrating HTML text with the data fields within the SELECT portion of the SQL statement. The following example shows how the data field, CUSTOMER_ID ,

SELECT DISTINCT
  '<A HREF="http://qxserver.com/cgi/qexpress.pl?qxFile=Customer_Orders\&customer_id=' 
  || CUSTOMER_ID || '"> Customer Orders </A>' AS HYPERLINK
FROM
  OE.ORDERS

Also notice that an extra back-slash \ was placed before the ampersand (i.e. \&customer_id ). This is required to prevent Qx from interpreting the string as a Parameter (i.e. &customer_id), explained later. Qx will strip out the back-slash prior to running the SQL so is not included in the returned text.

Forcing the Report to a New Browser Window:

By default, anytime you link from one document to another your browser will load the new document into the old document's window. In Query Express, you can force a new browser window to be created in order to conserve the previous report results. Adding the target option to the Anchor tag will accomplish this,

SELECT DISTINCT
'<A HREF="http://qxserver.com/cgi/qexpress.pl?qxFile=Customer_Orders\&customer_id=' 
  || CUSTOMER_ID || '" target="newWindow"> Customer Orders </A>' AS HYPERLINK
FROM
  OE.ORDERS

Parameters

Parameters are variables placed in the SQL of any query that are to be substituted with input provided by the User at run-time. Query Express will detect any parameters when parsing the SQL, and prompt the user to provide the input for each before submitting the SQL to the selected data source.

The example below shows a portion of the Qx SQL Freehand tool. The SQL contains the Parameter &cust_id and was detected by Qx, resulting in a prompt for user input.

In the example the CUSTOMER_ID field is a numeric type. Therefore, the Parameter must not be shown in any quotes. However if the field was a string (such as char, or varchar2) then the Parameter must be embraced in single quotes. Qx will substitute the parameter with the exact text entered by the user at runtime; it does not does not attempt to understand the data to determine if quotes are required.

In Developing Qx Reports, you noticed that the Qx Library Publisher also detects Parameters. The Library Publisher is not interested in the data itself, but in the Question that will be asked instead of the generic question above. It is also interested in the type of input method and any options that should be provided by the developer.

Sub-Queries

Query Express allows each Report stored in the Library to contain more than one query (the SQL), thus each query is actually referred to as a sub-query. This prevents the number of individual Reports in the Library from growing out of control by giving developers the ability to group related SQL queries into a single report.

Any number of sub-queries can be added to a report using the Qx Library Publisher. Click on Add Another Query, to add as many sub-queries as desired. You must identify the CLASS for each sub-query, and optionally if it is Autorun (run without further intervention by the user as long as all Parameter fields are completed), or if the SQL is to be Locked (available to be viewed in the report). Refer to the document Developing Qx Reports for further more information on the Qx Library Publisher.

While a report may contain several sub-queries, only one sub-query is be selected and run at any time. The sub-query to be run is decided by the value of the qxQuery Parameter, which must be passed to Qx using a hyperlink. If the qxQuery Parameter is omitted, then the default (Query #0) sub-query is assumed.