Business Insights
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.
Exploring
Business insight tiles display text or charts and can be used in various places, including:
-
Main SYSPRO menu: to surface company information (e.g. the total backorders for the company)
-
Specific programs: to provide a key field as the context (e.g. the total backorders for a customer)
Additionally, when you click on a business insight tile, you can drill down to more detailed information displayed in a list view that derives its context from the specific tile measurement parameters.
You can even create administration insights such as failed transactions, users logged in, etc.
Insight tiles can be imported using the Tile Builder - Import program and exported using the Tile Builder - Export program (accessible from within the Insight Tile Definition program).
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.
Therefore, the Insight Tile Definition program lets you create and deploy business insight tiles, as well as define specific thresholds for targets or key performance indicators (KPIs) and highlight these values within the tiles.
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.
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)
Starting
To use this feature, the following setup option(s) must be enabled:
Setup Options > System Setup > E.Net Service Details
- Server name
- SOAP port
- REST port
The SYSPRO 8 e.net Communications Load Balancer service must be installed and running.
You can secure this feature by implementing a range of controls against the affected programs. Although not all these controls are applicable to each feature, they include the following:
- You restrict operator access to activities within a program using the Operator Maintenance program.
- You can restrict operator access to the fields within a program (configured using the Operator Maintenance program).
- You can restrict operator access to functions within a program using passwords (configured using the Password Definition program). When defined, the password must be entered before you can access the function.
- You can restrict access to the eSignature transactions within a program at operator, group, role or company level (configured using the eSignature Setup program). You can restrict access to the eSignature transactions within a program at operator, group, role or company level (configured using the Electronic Signature Configuration Setup program). Electronic Signatures provide security access, transaction logging and event triggering that gives you greater control over your system changes.
- You can restrict operator access to programs by assigning them to groups and applying access control against the group (configured using the Operator Groups program).
- You can restrict operator access to programs by assigning them to roles and applying access control against the role (configured using the Role Management program).
-
Tile definition file names must end with .SQL and can only include the characters A-Z, a-z, 0-9 and _ (underscore).
Solving
You can either create a new text-type business insight tile from scratch, or copy an existing one and then change the tile details.
In this example, we are concentrating on creating a new text-type business insight tile:
-
Open the Insight Tile Definition program (SYSPRO Programs > Administration > General Setup).
-
To create a new tile definition, select the New Tile function.
The Tile Builder program is displayed:
-
From the New Tile window, configure the following tile details and select OK:
-
Tile type - select Text
You can only select the tile type if you are creating a new tile, as it is inherited from the copied tile.
-
Tile description
-
File name
-
-
From the Tile Properties pane, configure the following sections:
-
Tile header and parameters
In the SYSPRO Web UI (Avanti) version, the Configure parameters link forms part of the Tile header section; whereas in the SYSPRO Desktop version, the parameters can be configured at the Parameters section.
-
Summary tile
-
Tile preview simulated values
The variables will be replaced at runtime using the output of the tile summary SQL script.
This functionality is only available in the SYSPRO Desktop version.
-
-
Configure your SQL script:
-
Select the Edit Tile Summary function to configure the SQL script that is used to render the summary tile values – including the main tile value, together with optional subtitle and footer values.
Or
-
Select the Edit Tile Detail function to configure the SQL script that is used to render the column values of the detail list view. These values are displayed when clicking on the tile to zoom into the details.
You can use the Edit Tile Summary option to generate the SQL statement using a simple-to-use interface, or you can use the Edit Tile Detail option to define a custom SQL script which provides more flexibility when presenting sophisticated information.
Select the Validate SQL and Close function to validate and save the SQL script, and return to the Tile Builder program.
-
-
Preview the tile in the Tile Builder program.
-
Select the Save and Close function to return to the Insight Tile Definition program.
-
-
Assign KPIs by selecting the Add hyperlink of the applicable column in the Tiles list view.
Once the business insight tile has been created, it can be assigned to a workspace using the Visual Designer program.
You can either create a new chart-type business insight tile from scratch, or copy an existing one and then change the tile details.
In this example, we are concentrating on creating a new chart-type business insight tile:
-
Open the Insight Tile Definition program (SYSPRO Programs > Administration > General Setup).
-
To create a new chart tile, select the New Tile function.
The Tile Builder program is displayed:
-
From the New Tile window, configure the following tile details and select OK:
-
Tile type - select Bar chart or Line chart
You can only select the tile type if you are creating a new tile, as it is inherited from the copied tile.
-
Tile description
-
File name
-
-
From the Tile Properties pane, configure the following sections:
-
Tile header and parameters
In the SYSPRO Web UI (Avanti) version, the Configure parameters link forms part of the Tile header section; whereas in the SYSPRO Desktop version, the parameters can be configured at the Parameters section.
-
Chart tile
-
Tile preview simulated values
The variables will be replaced at runtime using the output of the tile summary SQL script.
This functionality is only available in the SYSPRO Desktop version.
-
-
Select the Edit Tile Chart function to configure the SQL script that is used to render the summary tile values – including the main tile value, together with optional subtitle and footer values.
-
Select the Validate SQL and Close function to validate and save the SQL script, and return to the Tile Builder program.
You can then preview the tile.
-
Select the Save and Close function to return to the Insight Tile Definition program.
-
-
Exit the Insight Tile Definition program.
Once the business insight tile has been created, it can be assigned to a workspace using the Visual Designer program.
You can either create a new text-type business insight tile from scratch, or copy an existing one and then change the tile details.
In this example, we are concentrating on copying an existing text-type business insight tile:
-
Open the Insight Tile Definition program (SYSPRO Programs > Administration > General Setup).
-
To copy an existing text tile:
-
Select the applicable tile in the Tiles pane. This launches the Tile Builder program.
-
Select the Create From function from the toolbar.
-
-
The New Tile window of the Tile Builder program is displayed:
-
Configure the following tile details and select OK:
-
Tile description
-
File name
-
-
Configure the following sections in the Tile Builder program:
-
Tile header and parameters
In the SYSPRO Web UI (Avanti) version, the Configure parameters link forms part of the Tile header section; whereas in the SYSPRO Desktop version, the parameters can be configured at the Parameters section.
-
Summary tile
-
Tile preview simulated values
The variables will be replaced at runtime using the output of the tile summary SQL script.
This functionality is only available in the SYSPRO Desktop version.
-
-
Select the Edit Tile Summary function to configure the SQL script that is used to render the summary tile values – including the main tile value, together with optional subtitle and footer values.
Select the Edit Tile Detail function to configure the SQL script that is used to render the column values of the detail list view. These values are displayed when clicking on the tile to zoom into the details.
You can use the Edit Tile Summary option to generate the SQL statement using a simple-to-use interface, or you can use the Edit Tile Detail option to define a custom SQL script which provides more flexibility when presenting sophisticated information.
-
Select the Validate SQL and Close function to validate and save the SQL script, and return to the Tile Builder program.
You can then preview the tile.
-
Select the Save and Close function to return to the Insight Tile Definition program.
-
-
Exit the Insight Tile Definition program.
Once the business insight tile has been created, it can be assigned to a workspace using the Visual Designer program.
You can either create a new chart-type business insight tile from scratch, or copy an existing one and then change the tile details.
In this example, we are concentrating on copying an existing chart type business insight tile:
-
Open the Insight Tile Definition program (SYSPRO Programs > Administration > General Setup).
-
To copy an existing chart tile:
-
Select the applicable tile in the Tiles pane. This launches the Tile Builder program.
-
Select the Create From function from the toolbar.
-
-
The New Tile window of the Tile Builder program is displayed:
-
Configure the following tile details and select OK:
-
Tile description
-
File name
-
- Configure the following sections:
Tile header and parameters
In the SYSPRO Web UI (Avanti) version, the Configure parameters link forms part of the Tile header section; whereas in the SYSPRO Desktop version, the parameters can be configured at the Parameters section.
Chart tile
Tile preview simulated values
The variables will be replaced at runtime using the output of the tile summary SQL script.
This functionality is only available in the SYSPRO Desktop version.
-
Select the Edit Tile Chart function to configure the SQL script that is used to render the summary tile values – including the main tile value, together with optional subtitle and footer values.
You can use the Edit Tile Summary option to generate the SQL statement using a simple-to-use interface, or you can use the Edit Tile Detail option to define a custom SQL script which provides more flexibility when presenting sophisticated information.
-
Select the Validate SQL and Close function to validate and save the SQL script, and return to the Tile Builder program.
You can then preview the tile.
-
Select the Save and Close function to return to the Insight Tile Definition program.
-
-
Exit the Insight Tile Definition program.
Once the business insight tile has been created, it can be assigned to a workspace using the Visual Designer program.
-
Open the Insight Tile Definition program (SYSPRO Programs > Administration > General Setup).
-
Select the New Tile function to create a custom tile.
The Tile Builder program is launched:
-
Enter the tile details within the New Tile window.
-
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:
-
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.
-
Highlight the relevant table from the Data Sources list view 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.
-
Select the Validate SQL and Close function to exit the Tile Builder - SQL Definition program.
-
-
Select the Save and Close function to exit the Tile Builder program.
-
-
Exit the Insight Tile Definition program.
-
Open the Insight Tile Definition program (SYSPRO Programs > Administration > General Setup).
-
Create a new custom tile (or select an existing business insight tile) to launch the Tile Builder program:
-
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.
-
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.
-
Once the main data source has been defined, select the Select column function at the Value field.
-
The Data Sources program is launched, from where you can select the relevant column from the Columnslist view.
Once you have selected the column, the column name is displayed next to the Value field in the Tile Builder - SQL Definition program.
-
Select the Validate SQL and Close function to exit the Tile Builder - SQL Definition program.
-
-
Select the Save and Close function to exit the Tile Builder program.
-
-
Exit the Insight Tile Definition program.
Certain business insight tiles (e.g. Number of users logged into SYSPRO or Total Accounts Receivable balance for the company) only display holistic information and therefore don't require any context (i.e. defined parameters or keys).
While other tile types are context driven and therefore require additional information (such as keys or parameters) to display the appropriate values.
For example:
-
Customer outstanding balance
When a business insight tile is added to the Customer Query program to display the current customer balance, the customer key (from the ArCustomer table) is required so that the relevant condition can be included in the SQL statement.
The SYSPRO user interface gathers a list of the key fields provided in the various forms and toolbars. Therefore, when a user selects a tile, the list of keys (together with their current values) are then passed to the tile.
You can access the key value and use it within your SQL query by defining an appropriate key type field against the tile.
For example:
For the Customer balance for a customer business insight tile, you could do the following:
-
Define the Key type field as Customer.
This means, that if there is a customer key available at run time then the value can be used by the SQL query of the tile to select the current customer code.
Sample SQL Preamble:
declare @Customer varchar(${Customer.Length})
set @Customer = '$Customer'
The ${Customer.Length} variable will be replaced at run time with the length of the customer key in SYSPRO (currently 15 characters).
The $Customer variable that will be replaced at run time by the actual customer key.
SQL Condition:
Customer = @Customer
Sample SQL query script:
--SQL=Generated
--SQL=Preamble
declare @Customer varchar(${Customer.Length})
set @Customer = '${Customer}'
--SQL=Select
select
@Customer as 'SubTitle',
CurrentBalance1 as 'Value'
from
[ArCustomerBal] with nolock
where
Customer = @Customer
GO
If you select a key type and then edit the tile summary's SQL statement, an appropriate SQL preamble is provided. The SQL preamble shows how to take the key of the item at run time and create a SQL variable that can be used in the SQL select statement condition (WHERE statement).
Due to this automatic SQL preamble creation, we recommend that you select the key type before defining your tile summary or tile detail SQL statement.
You can export standard and custom business insight tiles as follows:
-
Open the Insight Tile Definition program (SYSPRO Programs > Administration > General Setup).
-
Select the Export function to launch the Tile Builder - Export program:
-
Select the business insight tiles that you want to export by using the check box.
-
Select the Export Selected Tiles function.
The Export Tile Target window is displayed:
-
In the SYSPRO Web UI (Avanti) version, specify the name of the target folder for the zip file. The target file name is Tile_Package.zip by default.
In the SYSPRO Desktop version, specify the format and location of the tiles you want to export.
-
Select the Export Tiles function.
The Export Confirmation message that is displayed prompts you to confirm that you want to export the selected tiles.
-
-
-
Exit the Insight Tile Definition program.
You can export individual or multiple tiles in a consolidated ZIP file or as separate files (i.e. one file per tile).
Standard business insight tiles can't be imported, however you can import custom business insight tiles as follows:
-
Open the Insight Tile Definition program (SYSPRO Programs > Administration > General Setup).
-
Select the Import function to launch the Tile Builder - Import program:
-
In the SYSPRO Web UI (Avanti) version, specify the source folder name where the zip file containing business insight tiles is located.
In the SYSPRO Desktop version, specify the format and location of the tiles that you want to import.
-
Select the Import Tiles button.
The copied or extracted business insight tiles are then displayed within the Tiles list view.
-
Select the business insight tiles you want to import and select the Import Selected Tiles function.
The Import Confirmation message that is displayed prompts you to confirm that you want to import the selected tiles.
-
-
Exit the Insight Tile Definition program.
You can import individual or multiple tiles using a consolidated ZIP file or separate files (i.e. one file per tile).
This depends on which SYSPRO User Interface is used:
-
When using the SYSPRO Desktop version:
When exporting single files, the default server target folder is ...\WORK\Tile_Import_Export (this folder is created if it doesn't exist at the time of the export).
If you are exporting to a ZIP file, then the default server target file name is Tile_Package.ZIP.
Alternatively, you can select different folders and file names if required.
In a client-server environment, the files are exported to the temporary folder on the client computer by default, but this can be changed if required.
-
When using the SYSPRO Web UI (Avanti) version:
Only single files are exported to the Downloads folder specified in the browser settings.
You can specify this folder when exporting the files if you enable the Ask where to save each file when downloading option in your browser's Downloads settings.
The following explains how to add key performance indicators to a business insight tile:
-
Open the Insight Tile Definition program (SYSPRO Programs > Administration > General Setup).
-
From the Tiles pane, highlight the tile for which you want to add KPIs.
-
Depending on the level at which the tile must be deployed, select the Add or Edit KPI hyperlink of the appropriate column:
-
System KPI
This lets you add or maintain the tile at system-wide level (i.e. all operators will be able to see the tile).
-
Company KPI
This lets you add or maintain the tile at company level (i.e. all operators that have access to the defined company will be able to see the tile).
-
Role KPI
This lets you add or maintain the tile at role level (i.e. operators within the defined role(s) will be able to see the tile).
-
Operator KPI
This lets you add or maintain the tile at operator level (i.e. only the operator for whom the tile is created will be able to see the tile).
The Tile KPI Maintenance program is launched:
-
From the Items pane, select the Add or Edit function of the Edit column.
The KPI Properties pane is then available for editing.
-
Edit the KPI properties as required.
-
Select the Save or Save KPI function.
-
Exit the Tile KPI Maintenance program.
-
-
Exit the Insight Tile Definition program.
You can't select a target value for Minimizing or Maximizing type tile goals. You can only use a fixed target value if the selected type goal is defined as Target Value.
If you change the icon on an existing tile, then the warning icon overrides the existing icon when the warning value has been reached or exceeded.
Similarly, the critical icon will override the warning icon if the critical threshold is reached or exceeded.
-
Select the Design Web View (or Edit Web View) option from the Customize and control this application icon ().
This icon is available on the toolbar of the main SYSPRO window, as well as on each SYSPRO program's toolbar.
-
From the Visual Designer program, select the Add layout section function.
-
From the Configure new layout section window, select Tile Section.
-
Optionally enter a Section title at the Widget title placeholder and assign an icon to the tile if required.
-
Select the Add section function.
-
Click in the Add tile block (designated by the icon).
A list of KPI and Metric categories are displayed.
The number of available insights are indicated by a number in brackets alongside each tile category.
-
Drill down into the relevant category and select the tile you want to deploy.
-
(Optional) Define the following for the tile:
-
Width
-
Text
-
Tile background color
-
Icon
-
-
Select the Add tile function to assign the tile to the current layout section.
Continue adding tiles to the current layout section or to a new layout section.
-
Select Save and Exit.
Your tile will be displayed in the web view, immediately showing the appropriate insight information.
In SYSPRO 7 Update 1 and SYSPRO 8 standard tiles are shipped in the \Base\Samples folder and custom tiles are designed to be located in \Plugin\CustomStore folder.
Some custom tiles may have been created by copying and renaming existing tiles in \Base\Samples folder instead of moving the custom tile to the \Plugin\CustomStore folder.
During the minor database upgrade to SYSPRO 8 2021 R2 (or later) these non-standard tiles are detected and automatically migrated to the \Plugin\CustomStore folder with the appropriate file prefix. Standard tiles located in \Base\Samples and custom tiles in \Plugin\CustomStore are also uploaded to the new system-wide AdmTileDefinition table.
In addition, once each custom tile has been uploaded to SQL, the custom tile definition files in \Plugin\CustomStore are moved to the \Plugin\CustomStore\Tile_Backup folder.
If any problems are found during the tile upload process, the tile definition file is moved to the \Plugin\CustomStore\Tile_Backup_Upload_Failed folder. An explanation of the failure is stored in a separate file with the file suffix _Upload_Failed_Reason.TXT.
Using
Standard SYSPRO Business Insights (i.e. those shipped with the product) include a number of categories, each comprising a list of specific insights:
- Average Days to Pay by Branch
- Average Days to Pay by Company
- Average Days to Pay by Customer
- Average Value of Invoices by Branch
- Average Value of Invoices by Company
- Average Value of Invoices by Customer
- Average Value of Invoices by Customer Class
- Average Value of Invoices by Geographic Area
- Average YTD Invoice Value by Company
- Average YTD Value of Invoices By Branch
- Average YTD Value of Invoices By Customer
- Average YTD Value of Invoices By Salesperson
- Customer Days Outstanding by Branch
- Customer Days Outstanding by Company
- Customer Days Outstanding by Customer
- Customers on Hold By Branch
- Customers on Hold by Company
- Customers on Hold by Customer Class
- Days Since Last Sale by Company
- GP Margin as % of Sales by Branch
- GP Margin as % of Sales by Company
- GP Margin as % of Sales by Customer
- Return on Asset by Company
- YTD Sales vs Target for Customers
- Number of Backorders for Company
-
Average days to pay for branch
-
Average days to pay for customer
-
Receivables days outstanding for branch
-
Receivables days outstanding for customer
-
Average YTD invoice value for branch
-
Average age of stock based on FIFO buckets
-
Average age of stock based on inventory movements
-
Average days to pay for company
-
Customer balance as a percentage of total debtors balance
-
Days cover for stock code
-
Inventory FIFO current month value for stock code
-
Inventory FIFO previous month 1 value for stock code
-
Inventory FIFO previous month 2 value for stock code
-
Inventory current cost current month value for stock code
-
Inventory current cost previous month 1 value for stock code
-
Inventory current cost previous month 2 value for stock code
-
Inventory last cost current month value for stock code
-
Inventory last cost previous month 1 value for stock code
-
Inventory last cost previous month 2 value for stock code
-
Inventory warehouse costing current month value for stock code
-
Inventory warehouse costing previous month 1 value for stock code
-
Inventory warehouse costing previous month 2 value for stock code
-
Percentage of total debtors balance for branch
-
Receivables days outstanding for company
-
Return on assets for company
-
Value of stock on hand for warehouse
-
WIP Ledger balance summary
- Average YTD invoice value for company
- Average YTD invoice value for salesperson
- Average invoice value for branch
- Average invoice value for company
- Average invoice value for customer
- Average invoice value for customer class
- Average invoice value for geographic area
- Days since last sale for company
- GP margin as percentage of sales for branch
- GP margin as percentage of sales for company
- GP margin as percentage of sales for customer
- WIP at a glance labor cost
- WIP at a glance labor issued
- WIP at a glance material cost
- WIP at a glance material issued
- WIP at a glance total WIP
- WIP at a glance total hours booked
- Average YTD invoice value for customer
- Customers on hold for a branch
- Customers on hold for company
- Customers on hold for customer class
- Number of lots nearing expiry for company
- Number of lots nearing expiry for stock code
- Number of lots nearing expiry for warehouse
- Number of lots on hold for company
- Number of lots on hold for stock code
- Number of lots on hold for warehouse
- Cycle time customer order processing
- Number of backorder lines for branch
- Number of backorder lines for customer
- Number of backorder lines for salesperson
- Number of backorder lines for stock code
- Number of backorders for branch
- Number of backorders for company
- Number of backorders for customer
- Number of backorders for customer selection
- Outstanding orders for customer
- Number of active jobs
- Number of active jobs for customer
- Number of active jobs for stock code
Standard tiles shipped as part of SYSPRO are stored in the \Base\Samples folder and named UX_Tile_??????_xxxxx.SQL, where:
- ?????? is a unique 6-character TileID.
- xxxxx is a short description of the tile (no spaces), e.g. UX_Tile_SOR001_Backorders_For_Company.SQL.
From SYSPRO 8 2021 R2 onwards, custom tile definitions reside in the system-wide AdmTileDefinition table.
During the first minor database upgrade performed once SYSPRO 8 2021 R2 (or later) is installed, any custom tile definition files stored in the \Plugin\CustomStore folder will be uploaded to the SQL table and the tile definition file will nbe moved to \Plugin\CustomStore\Tile_Backup folder.
If any problems are found during the tile upload process, the tile definition file is moved to the \Plugin\CustomStore\Tile_Backup_Upload_Failed folder. An explanation of the failure is stored in a separate file with the file suffix _Upload_Failed_Reason.TXT.
The following indicates areas in the product that may be affected by implementing this feature:
SYSPRO Programs > Administration > General Setup
This program is the platform from where you can:
-
View existing business insight tiles
-
Create new tiles
-
Assign KPIs
-
Manage SQL scripts
-
Import and export tiles
SYSPRO Programs > Administration > General Setup
This program lets you create and maintain chart and text type business insight tiles.
Accessible from the Assign parameters hyperlink at the Configure parameters field of the Tile Builder program (Tile Properties pane).
This program lets you define parameters for business insight tiles.
Accessible from the Edit Tile Summary or Edit Tile Detail function within the Tile Builder program.
This program lets you indicate the data source and data columns to be used for the SQL script of custom business insight tiles.
Accessible from the Import function within the Tile Builder program.
This program can be accessed from within the Insight Tile Definition program and is used to import business insight tiles.
Accessible from the Export function within the Tile Builder program.
This program can be accessed from within the Insight Tile Definition program and is used to export business insight tiles.
Program List > Administration > General Setup
This program lets you view changes made to business insight tiles, which are stored in the AdmTileAudit table.
Copyright © 2022 SYSPRO PTY Ltd.