Design

Applies to: Kyvos Reporting

The Design tab provides a visual interface to create simple SQL queries using drag-and-drop operations. You can build SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY clauses by selecting entities and attributes from the connected data source.

Open

Adding Entities to a Query

1. In the Entity Type drop-down, select Table, Procedure, View, or Synonym.

2. A list of available entities for the selected type appears.

3. Drag the desired entity from the list and drop it onto the design area.

   • The entity appears as a box in the design area.

   • The entity name is automatically added to the SELECT and FROM clauses.

4. Repeat the process to add more entities.

Adding Parameters

To include parameters in your SQL:

• Drag [PARAMETERS] from the entity list and drop it onto the design area.

• A parameter box appears in a different color.

Repositioning Entity Boxes

To reposition an entity on the design area:

• Click the title bar of the entity box and drag it to the desired location.

Creating Joins

To define a join between two entities:

1. Drag an attribute (field) from one entity box.

2. Drop it onto an attribute in another entity box.

   • A directional arrow connects the two attributes.

   • The join condition is automatically added to the WHERE clause.

   • The SELECT and FROM clauses are updated accordingly.

Deleting a Join

To remove an existing join:

1. Right-click the arrow representing the join.

2. Click Delete Link.

   • The join is removed from the design area and the WHERE clause is updated.

Working with Attributes

• Drag and drop an attribute from an entity onto the appropriate clause box: SELECT, WHERE, GROUP BY, or ORDER BY.

• Select or deselect an attribute to include or remove it from the SELECT clause.

WHERE Clause

To build filter conditions:

Adding Condition Rows

• To add a row at the top of the clause:

  • Click the Insert icon in the column header’s leftmost cell.

• To add a row below a specific condition:

  • Click the Insert icon in the target row.

Deleting Condition Rows

• Click the Delete icon in the desired condition row.

Defining Conditions

1. Choose values for Operand 1, Operator, and Operand 2.

2. To join two conditions, use the logical relation column (AND/OR) in the first row.

3. Use opening and closing parentheses to group conditions as needed.

GROUP BY Clause

Use this clause to group data by one or more attributes.

• Drag and drop an attribute from the entity list or design area into the Group By section.

HAVING Clause

The procedure to define conditions in the HAVING clause is similar to the WHERE clause.

ORDER BY Clause

Use this clause to define sorting criteria for your query results.

• Drag and drop the desired attribute from the design area to the Order By section.

• Choose sorting direction: Ascending or Descending.