Sub Query Column

In Query Designer and Spreadsheet Server drill down panels, a Sub Query option exists, allowing users to drill down from the current drill down panel to a user-defined query. The Sub Queries menu contains a list of all the EDQ files containing column names in the Sub Query Column parameter fields, matching column names in the original drill down panel.

Note:

All subquery columns defined in the detail query must be present in the original drill down panel, for the query to be available in the Sub Queries menu.
Both the query and subquery need to be in the default Queries folder, for the subquery to be visible. The path to this folder is typically: C:\Users\Public\Documents\Global Software Inc\Spreadsheet Server\Query Designer\Queries.

Create a Sub Query

Create the related detail query(ies) to be associated with the GXL balance or journals drill down, or parent (original) query.

For example:

Detail query: Child_People in City.edq
Parent (original) query: RootQuery.edq

For each related detail query in the Parameters panel, click the ellipses button in the Sub Query Column for the associated parameter. The Select Sub Query Column panel appears.

Select either Built-in Fields or Query Fields.
For Query Fields, select the query for which to list the available column names. The system adds the column names from the selected query, to the list of available query fields.
The following standard columns are used for data passed to a subquery, when executed from a GXL balance or journals drill down. The values of these columns are the values of the original GXL formula, making it possible to design an EDQ as the journals subquery drill down.
- - %SS_KEY1%
  - %SS_KEY2%
  - %SS_KEY3%
  - %SS_YEAR%
  - %SS_FORMAT%
  - %SS_PERIOD%
  - %SS_FROMPERIOD%
  - %SS_TOPERIOD%
  - %SS_SEG##% (where ## is the account segment number)
- Standard columns %SPARM01% through %SPARM15% are used for data passed to a sub query when executed from a query (GEXQ, GEXD, etc.) drill down. The values of these columns are the values of the original formula's parameters.
Select the column name to be referenced. The column name is inserted in the Sub Query Column cell in the Parameters grid.

When executing a parent query in Query Designer, or when drilling down on a parent query, GXL balance, or journals drill down in Excel, select the desired row, right-click, and from the Sub Query menu, select the appropriate detail query for which to view results. The system displays the data for the selected detail query.

Limit Queries Listed in the Sub Query Submenu

Optionally, additional criteria may be defined for each detail query to determine which parent queries and/or column value criteria must be met in order for the detail query to be listed in the Sub Queries menu for a parent query. All additional criteria conditions must be met in order for the detail query to appear on the menu.

For each related detail query, click the Conditional Execution tab.

Use the following table to enter data in the Conditional Execution tab.

Field	Description
Filter by Queries	Specify, if desired, the folder(s) and/or subfolder(s) of the queries, and/or individual queries, to which the detail query is limited to, for subquery drill down processing. If not selected, the detail query may be available for all queries, provided all other criteria is met.
Display Only If Column	Specify, if desired, the name of the column which the parent query must contain in order for the detail query to be available for subquery drill down processing.
Value Equals	Specify, if desired, the column value which the selected parent query row must contain in order for the detail query to be available for subquery drill down processing.

Example 1

For the detail query, Child_People in City.edq, conditional execution information is defined as follows. The detail query will be available for subquery processing for any parent query in the subfolder, containing the FirstName column, with the value of Nancy.

Example 2

The detail query, Child_People in City.edq, is not included in the Sub Queries menu because the FirstName value of the selected row is not Nancy.

Example 3

The detail query, Child_People in City.edq, is included on the Sub Queries menu because the FirstName value of the selected row is Nancy.

QD_SubQuery6