Designing Report using Semantic Model

Applies to: Kyvos Reporting

If you are using Kyvos as a data source, you can create queries using semantic models created in Kyvos.

To design a query using a semantic model, perform the following steps.

1. On the SQL Report Wizard window, select the Semantic Model radio button.
   
   

   Open

   

2. In the Select Semantic Model dropdown, select the required semantic model.
   
   

   Open

   

3. Click Parameters to add parameters, if required.

4. Click Next or the Fields tab to specify the fields to include in the report.

5. Move the required dimensions or measures from the Available Fields column to the required Selected Fields shelf (Filters, Rows, Columns, or Values) on the right.

   When you add a hierarchy level or attribute to the Columns shelf, select how the columns should be generated:

   • Only Selected: Generates columns only for the members explicitly selected by the user. This is the default option.

   • Descendent: Generates a column for each distinct member available at the selected hierarchy level.

   • Children: Generates columns for the immediate children of the selected member.

   You can configure the generation mode independently for each hierarchy level or attribute added to the Columns shelf.

   If Descendent or Children is selected, member selection for that column is disabled, and any previously selected values are cleared.

6. Click Select Members to select the required members for the selected fields. Click OK.

   • For Only Selected, select one or more members to generate columns for the selected members.

   • For Children, select a single parent member. The report generates columns for the immediate children of the selected member.

   • For Descendent, member selection is not required because columns are generated automatically for the members available at the selected hierarchy level.

7. If you have moved any fields to the Filters shelf, click Filter Criteria to specify the filter criteria.

   1. Select the Fields, Criteria, and Value as required.

   2. Kyvos pre-generates a Parameter Name for the filter. You can edit the parameter name if required.

   3. You can add multiple filter conditions. Click OK.

8. If you are using a Tree View parameter, select the required node ordering option:

   • Top Down: Processes nodes from the root toward the lower levels of the hierarchy, from left to right.

   • Bottom Up: Processes nodes from the lower levels toward their parent nodes.

   For example, for the following tree structure:

   1 ├── 2 │ ├── 4 │ └── 5 └── 3 ├── 6 └── 7

   Top Down returns the nodes in the following order:

   1, 2, 4, 5, 3, 6, 7

   Bottom Up returns the nodes in the following order:

   4, 5, 2, 6, 7, 3, 1

   These options control the order in which values are returned from a hierarchical Tree View. They do not control the left-to-right arrangement of report columns.

9. If you have selected Children for a column, configure the child-descendent behavior in the SQL query.

   You can configure Children in either of the following ways:

   • Dynamic: The parent member, parent level, and child level are obtained dynamically using report parameters. The generated columns can therefore change based on the user's selection.

   • Static: The parent and child levels or values are defined directly in the query. The generated columns remain fixed until the query is manually modified.

10. Click SQL Editor at the bottom-right of the window to view the automatically generated SQL query.

    You can modify the query to configure dynamic or static Children behavior.

    The Children configuration uses placeholders to identify the following information:

    • Parent level: The hierarchy level containing the selected parent member.

    • Parent value: The member for which the children must be retrieved.

    • Child level: The hierarchy level containing the child members to be returned.

    Static Children configuration

    Use a static configuration when the parent and child hierarchy information is fixed and should not change based on user selection.

    The following example shows a generic static configuration:

    /* _PARENT_DSND_LVL_START entity_level_01, period_level_03 _PARENT_DSND_LVL_END _PARENT_DSND_VAL_START Country, First Quarter _PARENT_DSND_VAL_END _CHILD_DSND_LVL_START entity_level_02, period_level_04 _CHILD_DSND_LVL_END */ SELECT entity_level_01, entity_level_02, period_level_03, period_level_04, SUM(Amount) AS Amount FROM SampleTable GROUP BY entity_level_01, entity_level_02, period_level_03, period_level_04

    In this example:

    • _PARENT_DSND_LVL_START and _PARENT_DSND_LVL_END define the parent hierarchy levels.

    • _PARENT_DSND_VAL_START and _PARENT_DSND_VAL_END define the parent members for which child members must be retrieved.

    • _CHILD_DSND_LVL_START and _CHILD_DSND_LVL_END define the corresponding child hierarchy levels.

    • Multiple parent and child levels can be specified as comma-separated values.

    • The values remain fixed unless the SQL query is manually modified.

    Use this approach when the report must always retrieve children for predefined members.

    Dynamic Children configuration

    Use a dynamic configuration when the parent member or hierarchy level is determined by a report parameter or Tree View selection.

    The following example shows a generic dynamic configuration:

    /* _PARENT_DSND_LVL_START <%prm_parent_level%> _PARENT_DSND_LVL_END _PARENT_DSND_VAL_START <%prm_parent_value%> _PARENT_DSND_VAL_END _CHILD_DSND_LVL_START <%prm_child_level%> _CHILD_DSND_LVL_END */ SELECT category, <%prm_parent_level%>, <%prm_child_level%>, SUM(Amount) AS Amount FROM SampleTable WHERE parent_field IN (<%prm_parent_value%>) GROUP BY category, <%prm_parent_level%>, <%prm_child_level%>

    In this example:

    • <%prm_parent_level%> identifies the hierarchy level containing the selected parent.

    • <%prm_parent_value%> contains the value selected by the user, such as a Tree View member.

    • <%prm_child_level%> identifies the hierarchy level from which the children are retrieved.

    • The parameters allow the query to dynamically determine the parent and child levels and the selected parent value.

    • When the user changes the Tree View selection, the generated child columns can change accordingly.

    Note: When using Children, the parent member must be a single selection. Multiple parent members cannot be selected for a single Children configuration.

11. If multiple hierarchy levels are added to the Columns shelf, configure the required column ordering.

    • Top Down: Arranges generated columns from the parent level toward the child level.

    • Bottom Up: Arranges generated columns from the child level toward the parent level.

    This setting controls the left-to-right arrangement of generated report columns. It does not affect the row-level sort order.

12. Review the generated SQL query and make any additional changes required for the report.

    For members generated automatically through Descendent or Children, the member order is based on the order defined in the semantic model. For members explicitly selected by the user, the existing global sort order behavior is retained.