QAD BI Portal Designer > Queries > Query Designer
  
Query Designer
The query designer is used to build or modify queries. To access the query designer, click New or open an existing query.
The query designer contains three tabs:
Design. Contains the essential frames to build a query.
Options. Contains options to limit the number of results in a query.
Advanced. Displays the query SQL so that the designer can further fine-tune the query results using SQL.
Design Tab
The design tab, which contains the essential tools to build a query, includes the following elements:
Query Area drop-down list. Displays a list of fact tables.
Fact and Dimension navigation pane. Displays a list of the facts and dimensions associated with the selected query area. Facts, which can be analyzed, are at the top of the list while dimensions, or selection criteria, are at the bottom.
Show Tooltips check box. If selected, displays SQL names when the curser hovers over the dimension or fact.
Selections pane. Displays the facts and dimensions used to build the query. Drag and drop facts and dimensions into this pane to build the query.
Filters pane. Displays the filters used in the query. Filters are applied to the query in this pane.
Query Results pane. Displays the results of the query after you click Run or Test Run.
Excel icon. Exports the query to Microsoft Excel.

Query Designer
Options Tab
In the Options tab, you can limit the number of query results that are returned. This tab includes the following elements:
Top N. Enter a number in this field to set how many query results are returned.
Distinct Rows Only. Select this option if the dimensions used for the query result in multiple rows when a single row is needed.
Dimension Only. Select this option if you are displaying information that does not include any facts; for example, a list of cost centers and their related descriptions.

Query Designer Options Tab
Advanced Tab
The Advanced tab gives you the ability to directly edit the query SQL to further fine-tune the query results. Queries that are created in the Design tab can be refined in the Advanced tab to create SQL that the designer does not support.
Note: After you alter the SQL in the Advanced tab, maintain the query in the Advanced tab because the drag-and-drop features of the query designer are no longer functional.
The Advanced tab includes the following elements:
Query Area drop-down list. Displays a list of fact tables.
Fact and Dimension Navigation pane. Displays a list of the facts and dimensions associated with the selected Query Area. Facts, which can be analyzed, are at the top of the list while dimensions, or selection criteria, are at the bottom. Drag and drop a dimension or fact from the navigation pane to the Query SQL pane.
Query SQL pane. Displays the query in SQL language. If desired, edit the SQL as needed and click Next to see details on Measures, Levels, and Parameters for your query. Add/Edit any default values for your parameters. Modify the datatypes for your Measures and Levels if desired. Run to check SQL syntax and to run the query.
Query Results pane. Displays the results of the query after you click Run.

Query Designer Advanced Tab
Advanced Columns and Parameters panes. Click Next to view these panes. Displays the following elements:
Selected Columns. Displays the columns selected for the query. Select the data type from the drop-down list.
Parameters. Displays the parameters defined for the query. You can change the label and data type.

Advanced Column and Parameter Panes
The SQL used in the Advanced tab is enhanced to support display of query area names in a user’s desired language by using the language support built into the QAD Data Warehouse. Special lookup codes, which retrieve the appropriate translation of the column name, replace the SQL column names. Add these column names to the SQL in the standard Design tab by dragging and dropping the columns into the query before you edit it in Advanced mode. Manually entering the lookup codes does not display the desired translated names.
For more information on using SQL in the Advanced tab, review other queries supplied by QAD in the QAD Standard folder or consult Microsoft SQL Server documentation.