Tile Builder - SQL Definition

This program lets you indicate the data source and data columns to be used for the SQL script of custom business insight tiles.

Exploring

This program can't be run standalone and is accessed from the following program(s):

  • Tile Builder

    From the Edit Tile Summary or Edit Tile Detail function.

SYSPRO Business Activity Queries are SQL views that are created in the relevant company databases and (because they are defined in SYSPRO) are recognized as data sources. They provide a view into the business data commonly used during the typical business management process. The queries are built and secured in the core ERP system and then surfaced to users via their enterprise reporting and connectivity solutions.

Business insight tiles (also called insight tiles or just tiles) are essentially SQL scripts displayed in a tile format within a SYSPRO web view. These tiles are context-driven and let you act instantly on events, facilitating informed business decisions and sound business behavior.

KPIs on tiles can be used to highlight values approaching or exceeding pre-determined objectives or thresholds. This lets you proactively keep an eye on approaching targets. These targets could be a certain value, company-wide goal or a specific target agreed for a person or a role within the organization.

In the context of SYSPRO Business insight tiles and SYSPRO Business Activity Queries, a data source can be considered as one of the following:

  • SYSPRO base table (e.g. InvMaster)

  • SYSPRO custom form table (e.g. InvMaster+)

  • SYSPRO Business View

  • User defined base table (i.e. any base table in the current database that is not part of the standard SYSPRO database)

  • User defined custom form table (i.e. any base table defined with a trailing + sign that doesn't form part of the standard custom table definitions)

  • User defined views (i.e. any view in the company database that is not defined as a SYSPRO Business View)

Topics

Business Insights

SYSPRO Business Activity Queries

Starting

You restrict operator access to activities within a program using the Operator Maintenance program.

Controls whether an operator can edit VBScripts or change customized panes.

It also controls whether an operator can access the Visual Designer program (displayed from the sub menu of the icon).

This does not apply to importing customized panes, which is controlled by the Allow to import customized panes activity.

Solving

  1. Open the Insight Tile Definition program (SYSPRO Programs > Administration > Enterprise Reporting).

  2. Select the New Tile function to create a custom tile.

    The Tile Builder program is launched:

    1. Enter the tile details within the New Tile window.

    2. Select the Edit Tile Summary function.

      You can also manually edit the SQL statement by selecting the Edit Tile Detail button.

      The Tile Builder - SQL Definition program is displayed:

      1. Select the Select data source function at the Primary data source field in the From section to indicate the anchor data source. The Data Sources program is displayed.

      2. Highlight the relevant table from the Data Sources listview and click on Select.

        Once you have selected the data source it is displayed in square brackets next to the Primary data source field in the Tile Builder - SQL Definition program.

      3. Select the Validate SQL and Close function to exit the Tile Builder - SQL Definition program.

    3. Select the Save and Close function to exit the Tile Builder program.

  3. Exit the Insight Tile Definition program.

  1. Open the Insight Tile Definition program (SYSPRO Programs > Administration > Enterprise Reporting).

  2. Create a new custom tile (or select an existing business insight tile) to launch the Tile Builder program:

    1. Select the Edit Tile Summary function to launch the Tile Builder - SQL Definition program:

      You can also manually edit the SQL statement by selecting the Edit Tile Detail button.

      1. Indicate the anchor data source at the Primary data source field.

        You can use the Select data source function to search for and select the data source using the Data Sources program.

      2. Once the main data source has been defined, select the Select column function at the Value field.

      3. The Data Sources program is launched, from where you can select the relevant column from the Columnslistview.

        Once you have selected the column, the column name is displayed next to the Value field in the Tile Builder - SQL Definition program.

      4. Select the Validate SQL and Close function to exit the Tile Builder - SQL Definition program.

    2. Select the Save and Close function to exit the Tile Builder program.

  3. Exit the Insight Tile Definition program.

Using

  • Fields on a pane are sometimes removed by default. You can reinstate them using the Field Selector option from the context-sensitive menu (displayed by right-clicking any field in the pane). Select and drag the required fields onto the pane.

    The captions for fields are also sometimes hidden. You can select the Show Captions option from the context-sensitive menu to see a list of them. Click on the relevant caption to reinstate it to the form.

    If the Show Captions option is grayed out, it means no captions are hidden for that form.

  • Press Ctrl+F1 within a listview or form to view a complete list of functions available.

Referencing

This validates the SQL query and return to the Tile Builder program.

This lets you validate the SQL query.

This displays the SQL Query Script pane from where you can enter a free-format SQL statement up to 65,000 characters long.

Field Description

Select

Once you have defined the Primary data source, use the Select column hyperlinks within this section to select a column from the data source using the Data Sources program.

Subtitle

The column selected here is used to display the subtitle on the business insight tile.

Value

The column selected here is used to display the value on the business insight tile.

Footer

The column selected here is used to display the footer on the business insight tile.

From

 

Primary data source

The Select data source hyperlink launches the Data Sources program from where you can indicate the anchor data source (e.g. base table, SYSPRO Business View, etc.) around which the tile's SQL script will be built.

Where

 

Condition

This lets you specify a WHERE condition for the SQL select statement to return rows with distinct column values.

Order by

 

Sequence columns

This lets you define the column order.

This displays the SQL preamble (which you can edit within this pane) as per the selections made in the SQL Select pane.

The SQL Preamble is limited to 5,000 characters when building the SQL statement using the generated logic (i.e. the parameters within the SQL Select pane).

This displays the entire SQL query statement (which you can edit within this pane) as per the selections you have made in the SQL Select pane.

The maximum length of any one line within the SQL statement is 1,000 characters.