Developing Qx Reports

Creating Reports and publishing them to the Query Express server is a simple process. This site is running a full installation of Qx along with several sample databases -- so you are welcome to create your own report and publish it!

 

Steps for Creating Reports

Step 1. Understand your Data

Before creating any report you must understand the data you are reporting on. To learn of the data structures used in this sample, you should start with the data model described in the Qx Demo Document. It contains the data models of the three sample systems:

Human Resources Sample Schema

News Feeds Sample Schema

Order Entry Sample Schema

You can also explore the data using the Qx Data Browser tool This tool allows you to see the tables that are available to you in each system and scan their data.

 

Step 2. Create the SQL

At the heart of every Query Express Report is the SQL (Structured Query Language). You must be familiar with SQL SELECT statements to extract the data for your final report. An Introduction to SQL is available on-line if you wish to learn more about SQL. While SQL is also used to create or change data, Query Express will not allow such SQL to run.

The Qx SQL Freehand tool can be used to create and test your SQL. That tool does not provide the debugging or performance tuning features necessary for building complex SQL queries -- but it is very useful for creating simple SQL queries or testing any final SQL prior to publishing to the Qx Library.

 

Step 3. Test your SQL using Qx SQL Freehand

As mentioned, all SQL created using external tools should be tested using the Qx SQL Freehand tool. This ensures the environment used to create the SQL is totally compatible with that used by Query Express. For example, the access rights are the same for both User accounts.

 

Step 4. Publish your Report

Once you have built and tested your SQL, you may use the Qx Library Publisher to publish your report in the Public or Private Libraries.

 

When publishing your Report, in addition to the SQL you must identify the Report name and Library location as well as descriptions. You may optionally provide author information and a data classification (such as "Company Confidential").

 

Using the Qx Library Publisher

 

Anyone with experience with SQL will want to use Query Express to turn their SQL into Qx Reports that are available to the rest of their organization. Whether you are a Qx Developer or Qx Administrator, you will need to know the basics of the Qx Library Publisher for the final step of publishing your report.

Title: A short name for the report. This will appear in the left-hand navigation frame. If you want to edit or delete an existing report, click on the Browse button.

Library: The destination directory where the report will be located. This mimics the hierarchy of the left-hand navigation frame.

Full Explanation: A full description of what the report is and any instructions to its use. This will be placed at the top of every report. While each report can contain more than one query (explained next), this description will be shown for each one in a report.

Query #x: A single Qx Report may have a single SQL sub-query or it may have several dozen sub-queries. You can add more sub-queries to a single Report by clicking on the Add Another Query link. You delete unwanted sub-queries from your report by selecting the Delete this Query link. Query #0 is the default sub-query and is run when no query is specifically requested. Refer to Using Hyperlinks, Parameters, and Sub-queries for information on using Sub-queries and how to link from one sub-query to another.

Description #x: (Optional) Place any special descriptions or instructions regarding this sub-query here. Unlike the main description, this will only appear on the report when this sub-query is selected.

SQL #x: The final SQL belongs here. Be sure it has been fully tested and does not make any attempts to change the data or data structures.

Class: Choose the class of systems that this sub-query is to run against. The Qx Administrator sets up the choice of available Classes. A single Class represents a group of database systems that have the same data structures and login permissions. For example, the "OE" class may consist of four separate systems; Production Order Entry, QA Order Entry, Training Order Entry, and Development Order Entry. Your query should be written such that it will run on any of the system in the same class. Query Express will prevent your query from running on a system of a different class.

Locked: YES or NO. This prevents the SQL from being viewed at run-time.

Autorun: YES or NO. This determines if the sub-query should be executed immediately when first selected, or wait until the User explicitly runs the query to display the results in the Browser or in Excel. Sub-queries that return large tables should not be set to autorun as the User may prefer to have the results in Excel.

Returned Results: TABLE, BINARY or XML (RAW is not currently implemented) This determines the format of data returned by your sub-query. If TABLE was selected when storing the query, Qx will return the data in tablular format (by default). At run-time the user may alternatively chose to have the tabular data returned in Microsoft Excel.

When selecting BINARY, the SQL must be written to return a single row of three specific columns:

 

BINARY Results must return a single row of three fields:

1. Filename (string)
2. Mime type (string)
3. Binary Data (LOB)

 

Example:

 
/*  Example from Order Entry - Product Multimedia
*/
select 
  filename, 
  mime_type, 
  media_content
from oe.product_media
where media_id = &media_id

Author: Optionally identifies whom the author of the report is. This is provided in the information worksheet of the Excel spreadsheet.

Data Classification: Optionally identifies the level of security confidentiality of the report results. Any statement provided in this section will be displayed in the results screen, and in the downloaded Excel spreadsheet.

 

Warning Messages

If you attempt to save your Report, you may still receive a message high-lighted in Red stating your request could not be completed.

This will result if you attempt to save a report without completing all of the required fields discussed above. Simply fill in the correct information and save your report again.

 

Detecting a Parameter in the SQL

You will also receive the error message if your SQL contains Parameters, and if you have not provided the additional information required for each Parameter.

Parameters are identified by the & symbol followed by the parameter name (such as &custid in the sample SQL). These are variables placed in the SQL by the report developer that are to be substituted with input provided by the User at run-time. They are also used to pass information from one sub-query to another, either within the same report or to other reports. See Using Hyperlinks Parameters and Sub-queries for further information.

When the Qx Library Publisher detects a Parameter in the SQL, it requires the developer to provide additional information:

Question: A Question that will be asked at Run-time when prompting for User input for the Parameter.

Nullable: YES or NO. If the sub-query will be allowed to execute if the no value is provided at run-time.

Type: TEXT, PASSWORD, HIDDEN, CHECKBOX, RADIO, LISTBOX or POPUP. This is the method of input Qx will use when prompting the User at run-time.

Selecting a Checkbox, Radio, Listbox or Popup type will result in further prompting to enter one or more parameter Options as shown below.

These values are used to generate the contents of the Checkbox, Radio, Listbox or Popup option used for the parameter substitution into the query. Your browser will present the "Caption" part of the options, but the resulting SQL executed by the database will contain the "Short Name" portion of the selected option.