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

Navigation

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.

Terminology

Business Activity Query (BAQ)

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

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.

Data source

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)

Related Information

Topics

Business Insights

SYSPRO Business Activity Queries

Starting

Security

Activities

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

VBScript editing / Design Web Views / Design Customized Panes

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

FAQs

How do I define the data source for a business insight tile?

Open the Insight Tile Definition program (SYSPRO Programs > Administration > Enterprise Reporting).
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.
Exit the Insight Tile Definition program.

How do I specify the data to be used for the business insight tile value?

Open the Insight Tile Definition program (SYSPRO Programs > Administration > Enterprise Reporting).
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.
Exit the Insight Tile Definition program.

Using

Hints and Tips

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

Menu and Toolbar

Validate SQL and Close

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

Validate SQL

This lets you validate the SQL query.

Custom 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.

SQL Select

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.

SQL Preamble

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).

SQL Query Script

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.

* product-authoring@syspro.com