Query Definition dialog

You can use the Definition dialog to define a query for Summary Links, Reference Links, Summary Reports, and Detail Reports.

Queries are defined by selecting items to return values for output, and specifying how those values are calculated.

For Summary Reports and Detail Reports, the dialog contains a second tab, Report Style, where you define the output style of the report.

Filter

In the Filter pane, the required filter items are displayed. These are determined by the Q&A DataLink Server through which Q&A Cloud links to the data source. For example, for SunSystems, they are Product, Business Unit and Table. You must specify values for these items, for all query types.

To select a mandatory filter item, right-click the item and select Lookup. From the Lookup dialog, select the item and click OK. Your selections populate the Selection List. After specifying the initial mandatory filter items, extra filter items may be displayed, but these depend on the Q&A DataLink Server and on the type of query. You must specify values for these items. You can also add extra filter items by dragging them from the Selection List into the Filter pane.

Some items accept multiple values. Hold down the SHIFT key to select contiguous values. Or, hold down the CTRL key to select non-contiguous values.

To specify a period, the Period Selection dialog is displayed.

To specify a filter instead of a value, right-click and select Options.

You can delete items added from the Selection List. Right-click the item and select Delete.

Selection List

The items in the Selection List are populated after you select a table. You must select at least one item from the Selection List for output. Right-click the item and select Add To Output.

You can also use the items in the Selection List as additional filter items, by dragging the item from the Selection List to the Filter section.

The items in the selection list can be added to favorites. Right-click the item and select Add to Favorite. The selected items are listed in the Favorites tab. To remove the selected item, right-click the item and select Delete.

To check recently used items in the Selection List, click the Last used tab. To remove one of the recently used items, right-click the item and select Delete.

To sort items available in the Selection List, click the A->Z tab. You can also specify between sorting alphabetically and in reverse order. Right-click the A->Z tab and select between A->Z or Z->A.

Output Field

The Output Field lists items that you select from the Selection List, for which the query will return values.

To change the extraction type, right-click the item and select Extraction Type. Edit the extraction type, and click OK.

To remove an item from the Output field, right-click it and select Delete.

Sorting output items

To sort the output items, right-click any item and select Sorting, to open the Sorting dialog. Make your changes to the sorting order, and click OK.

You can reorder the output items, by dragging them above or below the other items. The order of items in the Output field specifies the output column sequence. The first item that is listed, is displayed as the first output column. The second item that is listed is displayed as the second output column, and so on.

Note: The Sorting item is only available for Summary Report and Detail Report queries.

Querying multiple Business Units

Output fields for more than one Business Unit can be included in a query definition if the database structure is the same in each Business Unit. However, the output field descriptions in the Business Units included in a query could be different. In this situation Query & Analysis Cloud uses the field descriptions from the first Business Unit.

Options

To enable generating aggregated formulas for further drill down functionality, select the Enable Drilldown Functionality check box.

For example, the field description for an income account might be Sales Revenue in the first Business Unit and Sales Income in the second Business Unit. In this situation Query & Analysis Cloud displays the field description for the first Business Unit. So the field description used is Sales Revenue.