Data Sources

This program lets you view all data sources available within the current SYSPRO company, as well as select an applicable data source or column when creating a Business insight tile or SYSPRO Business Activity Query.

Exploring

The functions available from this program depend on from where it is called:

  • Tile Builder - SQL Definition:

    • (Edit Tile Detail)

      When accessed via the Assign columns function of the Columns field or the Select columns function of the Sequence columns field in the SQL Select pane:

      This program lets you select multiple columns to use within the SQL statement for the tile.

    • (Edit Tile Summary)

      When accessed via the Select column function of the Subtitle, Value, or Footer fields in the SQL Select pane:

      This program lets you select a single column to use within the SQL statement for the tile.

    • (Edit Tile Summary or Edit Tile Detail)

      When accessed via the Select data source function of the Primary data source field in the SQL Select pane:

      This program lets you select a single data source to use as the anchor for the Business insight tile.

  • Business Activity Query Builder:

    • When accessed via the View toolbar function:

      This program displays the available SYSPRO Business Views in the Data Source pane from which you can select a single view to maintain.

    • When accessed via the Primary Data Source field in the View Properties pane:

      This program displays all data sources available for use, from which you can select a single data source to use as the anchor for the view.

    • When accessed via one of the data source hyperlinks within the Data Sources pane:

      This program displays all of the linked data sources from which you can select multiple linked tables at once.

    • When accessed via the Columns pane:

      This program lets you select multiple columns from the passed data source.

    • When accessed via the Column/Literal 1 or Column/Literal 2 columns within the JOIN or WHERE clause standard definition:

      This program lets you select a single column to use within the clause definition.

This program is accessed from various locations in SYSPRO:

  • Tile Builder - SQL Definition

  • Business Activity Query Builder

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)

In SQL, a view is a virtual table whose contents are defined by a query. Like a table, a view consists of a set of named columns and rows of data.

Unless indexed, a view does not exist as a stored set of data values in a database. The rows and columns of data come from tables referenced in the query defining the view and are produced dynamically when the view is referenced.

A view acts as a filter on the underlying tables referenced in the view. The query that defines the view can be from one or more tables or from other views in the current or other databases.

Examples of what views can be used for:

  • To focus, simplify and customize the perception each user has of the database.

  • As security mechanisms by letting users access data through the view, without granting the users permissions to directly access the underlying base tables of the view.

  • To provide a backward compatible interface to emulate a table that used to exist but whose schema has changed.

Topics

SYSPRO Business Activity Queries

Starting

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

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:

  1. Open the Insight Tile Definition program (SYSPRO Programs > Administration > General Setup).

  2. To create a new tile definition, select the New Tile function.

    The Tile Builder program is displayed:

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

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

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

    4. Preview the tile in the Tile Builder program.

    5. Select the Save and Close function to return to the Insight Tile Definition program.

  3. 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:

  1. Open the Insight Tile Definition program (SYSPRO Programs > Administration > General Setup).

  2. To create a new chart tile, select the New Tile function.

    The Tile Builder program is displayed:

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

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

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

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

    5. Select the Save and Close function to return to the Insight Tile Definition program.

  3. 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:

  1. Open the Insight Tile Definition program (SYSPRO Programs > Administration > General Setup).

  2. To copy an existing text tile:

    1. Select the applicable tile in the Tiles pane. This launches the Tile Builder program.

    2. Select the Create From function from the toolbar.

  3. The New Tile window of the Tile Builder program is displayed:

    1. Configure the following tile details and select OK:

      • Tile description

      • File name

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

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

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

    5. Select the Save and Close function to return to the Insight Tile Definition program.

  4. 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:

  1. Open the Insight Tile Definition program (SYSPRO Programs > Administration > General Setup).

  2. To copy an existing chart tile:

    1. Select the applicable tile in the Tiles pane. This launches the Tile Builder program.

    2. Select the Create From function from the toolbar.

  3. The New Tile window of the Tile Builder program is displayed:

    1. Configure the following tile details and select OK:

      • Tile description

      • File name

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

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

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

    5. Select the Save and Close function to return to the Insight Tile Definition program.

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

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

  2. From the Visual Designer program, select the Add layout section function.

  3. From the Configure new layout section window, select Tile Section.

  4. Optionally enter a Section title at the Widget title placeholder and assign an icon to the tile if required.

  5. Select the Add section function.

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

  7. Drill down into the relevant category and select the tile you want to deploy.

  8. (Optional) Define the following for the tile:

    • Width

    • Text

    • Tile background color

    • Icon

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

  10. Select Save and Exit.

    Your tile will be displayed in the web view, immediately showing the appropriate insight information.

The following describes how to create a new SYSPRO Business Activity Query:

  1. Open the Business Activity Query Builder program (Program List > Administration > General Setup).

  2. Within the View toolbar field:

    Indicate the unique name for the SYSPRO Business Activity Query.

    For example:

    bq_ActiveJobs

    You can enter up to 18 alphabetic, numeric or underscore characters for the view name but it must be prefixed with bq_ (i.e. the standard prefix for a SYSPRO Business Activity Query). However, the system automatically adds this prefix if you don't include it with your entry.

  3. Within the View Properties pane, enter the following fields:

    Indicate a description for the SYSPRO Business Activity Query.

    For example:

    A list of active jobs in SYSPRO

    This indicates the 'Anchor' data source around which the SYSPRO Business Activity Query is built (i.e. the main data source from which any linked data sources will be defined).

    For example:

    WipMaster

    You can search for this by selecting the Select data source function. This launches the Data Sources program from where you can select the main data source from the Data Sources pane.

    You can use the Ctrl+F keyboard shortcut to search for a particular entry.

    Once you select the main data source, you are returned to the Business Activity Query Builder program.

    This lets you specify the number of rows (or percentage of data) to return on the dataset.

    This is useful on large data sets in that it restricts the amount of data returned.

    This lets you remove any duplicate values from the dataset and return unique rows only.

  4. (Optional) Within the Data Sources pane, indicate any linked data sources required:

    This isn't required if you are only retrieving information from a single source.

    Once the Primary data source has been entered, it's loaded as the first entry in the Data Sources list view. You can then use the data source's hyperlink to indicate additional SQL tables that must be linked to the selected data source.

    For example:

    Primary data source: WipMaster

    Linked data source: WipJobClass

    After you select a linked data source, the system automatically creates the database table connection to join the information between these sources (i.e. the link between the parent and selected data source is automatically built based on the links defined in the SYSPRO data dictionary).

    If the data source selected has no standard link to the parent data source, then you must define the link.

    You may also want to refine the link information (e.g. limit job types).

    When using the SYSPRO Desktop, you can optionally change the sequence of the data sources (and columns) by dragging an entry up and down within the grid.

    However, take care when doing this as the data source sequence is important because SQL works in a top down sequence when validating join information.

  5. Within the Columns pane, indicate the table columns that you want returned in the SYSPRO Business Activity Query:

    Select the applicable data source (alias) in which the columns that you require reside.

    Select {custom} if you wish to define a custom column (e.g. complex maths formulas and CASE statements). The Edit hyperlink then opens the Custom Column window from where you can define the custom SQL statement for the column details.

    Enter the column you want to include from the data source. Alternatively, select the browse function to select multiple database columns at once.

    For example:

    Primary data source: WipMaster

    Columns: Job | JobDescription | JobClassification | JobType | StockCode | Customer | Complete | QtyToMake | QtyManufactured

    Repeat this process for each of the linked data sources to ensure that all related column information is included for the SYSPRO Business Activity Query.

    For example:

    Linked data source: WipJobClass

    Columns: ClassDescription

  6. Optional... Define a WHERE clause:

    Defining a WHERE clause against the result set is useful as it limits the dataset from returning unnecessary data.

    1. From the View Properties pane, select Edit SQL where clause within the WHERE clause field.

      This opens the Where Clause pane from where you can define the conditions for the SQL select statement to return rows with distinct column values.

    2. Enter the following fields as required for your clause:

      Select the opening brackets required.

      For example:

      - = none

      ( = one

      (( = two

      Select the applicable data source (or {literal} entry) from the drop-down.

      Enter (or browse for) the column or literal to be compared.

      Literals must be enclosed in single quotes if alphanumeric.

      Select the condition required:

      • = equal

      • <> not equal

      • < less than

      • > greater than

      • <= not greater than

      • >= not less than

      • Like

      Select the applicable second data source (or {literal} entry) from the drop-down.

      Enter (or browse for) the column or literal to compare against.

      If you are using a LIKE condition, literals must be set with percentage signs.

      For example:

      ‘Bob%’ = the entry must start with Bob

      ‘%Bob%’ = the entry must contain Bob

      ‘%Bob’ = the entry must end with Bob

      Select the closing brackets required.

      For example:

      - = none

      ) = one

      )) = two

      If required, select And or Or to continue the conditions.

      For example:

      To ensure that the results only include uncompleted jobs:

      • Open: -

      • Dts 1: WipMaster

      • Column/Literal 1: Complete

      • Condition: <>

      • Dts2: {Literal}

      • Column/Literal 2: 'Y'

      • Close: -

      • And/Or: -

  7. Select the Validate SQL function.

    This passes the statement to SQL for validation in the context of creating the view (i.e. it emulates creating a view in SQL).

    This is important as it ensures that the statement to be applied doesn't contain invalid statements (e.g. INSERT, UPDATE, DELETE, TRUNCATE, etc.).

    This validation is performed in the context of the current company and not across all companies where the SYSPRO Business Activity Query is to be published. Therefore, if your view contains a custom form column that is defined within the current company, but not within the other companies' databases, then the statement will only be valid in the current company when published (i.e. this can result in a failure during the publishing process to other companies).

  8. Optional... Select the Preview toolbar function.

    This lets you preview the data that will be returned before committing any changes to the database or creating the actual SYSPRO Business Activity Query.

    The SQL Statement pane is populated based on your selections once you close the preview.

  9. Select the Save and Publish function.

    This saves the SYSPRO Business Activity Query and creates (or alters) the SQL View within the SQL Databases.

    During the publishing process, you are prompted to confirm if you want to make the view available for use in SYSPRO. If you select yes, the Available to use property of the View Properties pane is automatically enabled. The SYSPRO Business Activity Query is then immediately accessible within business insight tiles and SYSPRO Data Dictionary.

Using

  • Columns in a list view are sometimes hidden by default. You can reinstate them using the Field Chooser option from the context-sensitive menu (displayed by right-clicking a header column header in the list view). Select and drag the required column to a position in the list view header.

  • 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 list view or form to view a complete list of functions available.

Referencing

This retrieves the currently highlighted entry into the calling program.

This list view details all data sources that are currently available within the SYSPRO company and (depending on from where the program was called) lets you perform one of the following actions:

  • Select the data source from which a business insight tile is built (when accessed from the Tile Builder - SQL Definition program)

  • Select the primary data source around which a SYSPRO Business Activity Query is built (when accessed from the Business Activity Query Builder program)

  • Select multiple linked data sources for use within a SYSPRO Business Activity Query (when accessed from the Business Activity Query Builder program)

You can use the Ctrl+F keyboard shortcut to search for a particular entry.

Column Description

Name

This indicates the name of the data source (i.e. alias).

Description

This indicates the description of the data source.

Type

This indicates the type of data source (e.g. SYSPRO base table, SYSPRO Business View, etc.).

Details

Select the View hyperlink to access the following functions:

  • Properties

    This lets you view the table properties of the selected data source using the Data Dictionary Table Properties program.

    This is only available for standard SYSPRO base tables.

  • View data

    This lets you view the data contained within the selected data source using the SYSPRO Data Viewer program.

Module

This indicates the module in which the data source resides.

This list view details all associated columns that make up the selected data source.

You can use the Ctrl+F keyboard shortcut to search for a particular entry.

Column Description

Name

This indicates the column name (i.e. alias).

Description

This indicates the description of the column.

DataType

This indicates the column type (e.g. decimal, char, varchar, datetime, etc.)

Seq

This indicates the sequence in which the columns are ordered.

This window lets you indicate criteria on which to filter the data displayed within the Data Sources pane.

This refreshes the Data Sources list view based on your filter criteria.

Field Description

Types

The options in this section let you define the data source types that you want to include in the Data Sources pane:

  • SYSPRO base tables

  • SYSPRO custom form tables

  • SYSPRO business views

  • User defined base tables

  • User defined custom form tables

  • User defined views

Modules

This section lets you indicate the modules for which you want to view associated data sources.

Linking

This section lets you limit the data sources displayed to only those tables linked to a specific primary data source.