Create an SQL query data structure

While you build your data dictionary and you select SQL as your data dictionary type, the SQL query tab is enabled. Use this tab to enter the SQL data structure that describes the data you want to extract from your Sentinel database to populate your Web Dashboard.

  1. Select the SQL query tab.
  2. Select the SQL edition button button SQL.
  3. Enter SQL to select data from your database.
  4. SQL queries you enter must be comprehensible to the selected DBMS.
  5. (Optional) Add filters to your SQL.
    1. Add SQL to your query to represent the filters. You must use the correct syntax in your SQL. For example:
    2. SELECT CycleId, ObjectId

      FROM Sentinel

      WHERE CycleId = ?[1]

      AND ObjectId = ?[2]

    3. In this example, the ? character is interpreted as a filter. The string ?[1] represents the first filter, ?[2] the second filter, and so on. Each of these filters must be defined (by clicking the button).
    4. Select the filters button to define filters for the SQL query. You must define your filters before you can specify the columns or preview the query result. The list of possible report-level filters contains only filters defined for the data dictionary and is shown in this tab. You must enter the following details:
    5. FieldDescription
      NameThe filter name must be unique within the data dictionary. This name is used by certain objects (including reports, dynamic links and tasks) to identify the column that must be used. It is never visible to the user. It is recommended that you never change this name once the column has been used by another object.
      DescriptionThe description displayed for this filter throughout the GUI. This is the internationalized description in the current user's language. This description can be specified for other languages using the Internationalization tab.
      Data typeThe data type, as expected by the database.
      Default valueThis can be a constant, a personalized field or a system value.
      VisibleA filter declared as visible can be applied cumulatively at report level (setting the value and visibility in use). A filter that is declared as not visible will be hidden when the report is configured.
      Filter typeYou can define different types of filter to suit different purposes:
      • value: Specify either Simple value or Unique selection value list filter type at the Report level.
      • field and value: Required if you want to use expression filters in the reports based on this query. In this case, you must specify the field to which the filter applies in the following text box. For example, instead of having where year = ? in a query, you would have where ? and set the field parameter to year.
      • text value: The filter's value is processed as a string. This is useful when the filter is a parameter of a stored procedure.
      FieldSpecify the field name when you select field and value filter type.
  6. (Optional) Select columns. Click the add column button to display the column definition screen.
  7. To access this tab, the SQL query must be valid and any filters must have been specified.
  8. Use these fields to specify the names of the columns returned by the SQL query:
  9. Field Description
    SQL field The name of the column in the database.
    Name The name of the column to be used in the SQL queries.
    Description A user-friendly name for the field. This value is displayed in the GUI.
    Type The group of files with which the column is associated.
  10. (Optional) Select More details to display additional fields.
  11. Field Description
    Size The size in bytes of the data type.
    Scale The number of digits to the right of the decimal point in a number.
    Nullable Allows the field to be set to the value, NULL.
  12. (Optional) Select the Query help button to display a list of fields or values you can add to the query. Choose from the following:
  13. Field Description
    Catalog A list of all data managed by the same execution engine.
    Schema A list with the namespace within the server.
    Table A list of tables available in the selected schema.
    Field The list of fields from the selected table.
    String A list of scalar functions that perform an operation on a string input value.
    Numeric A list of SQL numeric functions used primarily for numeric manipulation and/or mathematical calculations.
    Date-Time A list of date and time functions.
    System A list of system functions.
    Field A list of personalized fields already defined.
  14. (Optional) Select the Preview button to display the first ten lines of the query result.

Related Links