Business Activity Query Builder

This program lets you manage SYSPRO Business Activity Queries (i.e. defined SQL views for information retrieval and custom reporting).

Exploring

Things you can do in this program:

  • Create a new SYSPRO Business Activity Query

  • Maintain an existing SYSPRO Business Activity Query

  • Define WHERE and JOIN clauses against a SYSPRO Business Activity Query

  • Remove an existing SYSPRO Business Activity Query

  • Publish a SYSPRO Business Activity Query to SQL

  • Make a SYSPRO Business Activity Query available for use throughout SYSPRO (e.g. within Business Insights)

Once you save and publish a SYSPRO Business Activity Query, it's created within the SQL database and accessible within the business insight tiles and SYSPRO OData Feed. You can also reference it from within the SYSPRO Data Dictionary, which provides access from the SYSPRO Report Writer once the data has been imported into the Report Writer Data Dictionary.

The Data Dictionary is also used by a number of business objects available to third parties (so they can access and retrieve data for customization purposes):

Because the query interrogates critical business data, security and controlled access are vital to protect the business. Administrators can, therefore, define the business views of SYSPRO data and allow access to appropriate users in a controlled way.

This program is accessed from the Program List of the SYSPRO menu:

  • Program List > Administration > General Setup

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)

OData (i.e Open Data Protocol) is an ISO/IEC approved, OASIS standard that defines a set of best practices for building and consuming RESTful APIs. It's a REST-based protocol for querying and updating data. It is built on standardized technologies such as HTTP, Atom/XML, and JSON and is different from other REST-based web services in that it provides a uniform way to describe both the data and the data model.

OData helps you focus on your business logic while building RESTful APIs without having to worry about the various approaches to define request and response headers, status codes, HTTP methods, URL conventions, media types, payload formats, query options, etc.

It also provides guidance for tracking changes, defining functions/actions for reusable procedures and sending asynchronous/batch requests.

The OData metadata (a machine-readable description of the data model of the APIs) enables the creation of powerful generic client proxies and tools.

OData is considered to be a flexible technology for enabling interoperability between disparate data sources, applications, services and clients.

Visit https://www.odata.org for more information.

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.

WYSIWYG is a system in which editing software allows content to be edited in a form that resembles its appearance when printed or displayed as a finished product.

Topics

SYSPRO Business Activity Queries

Starting

To use this feature, your SQL Admin login must either be a database owner or a member of the DBO role so that they have sufficient permissions to CREATE, ALTER and DROP views in SQL across all your company databases. This ensures that SYSPRO Business Activity Queries are created consistently across all companies so that they can be utilized by entities such as the Report Writer and SYSPRO OData.

Microsoft defines these permissions as follows:

  • Create view - Requires CREATE VIEW permission in the database and ALTER permission on the schema in which the view is being created.

  • Alter view - To execute ALTER VIEW (at a minimum) ALTER permission on OBJECT is required.

  • Drop view - Requires CONTROL permission on the view, ALTER permission on the schema containing the view, or membership in the db_ddladmin fixed server role.

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

This message is displayed if you attempt to use an existing but unpublished SYSPRO Business Activity Query as the data source for a new view within the Business Activity Query Builder program.

You can't use an existing SYSPRO Business Activity Query as the data source for another view if it has not yet been published or made available for use in SYSPRO.

Therefore, to proceed, you need to first publish the SYSPRO Business Activity Query that you want to use and ensure that the Available to use option of the View Properties pane is enabled.

Once this is done, you can then use that view as the data source within another SYSPRO Business Activity Query.

This message is displayed if you attempt to use a data source within the Business Activity Query Builder program that doesn't exist in the current company database or SYSPRO data dictionary.

Select the correct data source to proceed.

This message is displayed when you attempt to delete a data source that is currently referenced within the WHERE clause.

All references to the data source that you want to delete must be removed from the associated WHERE clauses before you attempt to delete the source.

You can edit the current WHERE clause by selecting the Edit SQL where clause option of the WHERE clause field within the View Properties pane.

This message is displayed when you attempt to delete a data source that is currently referenced within the JOIN clause.

All references to the data source that you want to delete must be removed from the associated JOIN clauses before you attempt to delete the source.

You can edit the current JOIN clause by selecting the Edit hyperlink of the Join details column within the Data Sources pane.

This message is displayed if you enter a column within the Columns, Where Clause or Join Clause panes that doesn't exist against the selected data source.

Ensure that you select the correct column associated with the data source defined.

This message is displayed if you don't define the correct linkage against your WHERE or JOIN clause.

Ensure that each line of the standard conditions are linked together with one of the following:

  • AND

  • OR

This message is displayed if you attempt to select columns within the WHERE or JOIN clause without first defining the associated data source.

Ensure that you select the applicable data source before assigning columns to the clause condition.

This message is displayed if you don't define the correct opening and closing brackets against the condition line for your WHERE or JOIN clause.

When using brackets within condition lines for a WHERE or JOIN clause, ensure that you apply the correct opening and closing brackets.

Opening brackets:

  • ( = one

  • (( = two

Closing brackets:

  • ) = one

  • )) = two

This message is displayed if you enter an existing SYSPRO Business Activity Query within the View toolbar field of the Business Activity Query Builder program.

This indicates that the view is flagged as published but doesn't yet exist within the database.

If you select to proceed, you can maintain the view's definition and then re-publish it to SQL.

Alternatively, select No and enter a view code that doesn't yet exist to create a new SYSPRO Business Activity Query.

When you enter an existing SYSPRO Business Activity Query (within the View toolbar field of the Business Activity Query Builder program) the system checks the view's published date in SQL versus the published date in SYSPRO.

This message is therefore displayed if these dates differ, as it indicates that the view in SQL has been maintained outside of SYSPRO (i.e. via Microsoft SQL Server Management Studio) and published with the ALTER statement.

You can select to continue and then maintain the view as required, however this will then overwrite any changes which have occurred outside of SYSPRO when you re-publish the SYSPRO Business Activity Query.

This message is displayed when you select the Standard toolbar function within the Join Clause or Where Clause windows, if you have already entered details within the custom syntax editor.

When you switch from entering a custom SQL expression back to using the standard definition, any information already defined within the custom entry is not transposed back to the standard entry list view.

Therefore, you can select Yes to continue and then redefine all the requirements for the clause using the standard definition entry. Alternatively, you can select No to continue using the clause within the custom syntax editor.

This message is displayed when you select the Custom toolbar function within the Join Clause or Where Clause windows, if you have already entered details within the standard definition entry list view.

When you switch from entering the standard clause conditions to using a custom SQL expression and have already begun capturing details within the list view, the custom clause is initially built using those entries.

Therefore, you can either select Yes to continue within the custom syntax editor, or select No to continue using the standard definition entry list view.

This message is displayed if you attempt to change the Primary Data Source field within the Business Activity Query Builder program.

This is because the program will revert to it's defaults when you change this entry, as you are changing the anchor point for the SYSPRO Business Activity Query.

You can either select No to retain the existing data source and associated definitions, or you can select Yes to proceed with changing the main data source for the SYSPRO Business Activity Query. The data sources and columns that were already defined are then removed.

This message is displayed when you attempt to delete a data source (i.e. you select the Delete function of the Data Sources pane) if custom columns have been defined against the data source for the SYSPRO Business Activity Query.

Select No to keep the selected data source and any column definition against it.

Alternatively, select Yes to remove the data source from the SYSPRO Business Activity Query, along with any linked columns.

If you previously defined any custom column definitions, ensure that they are still valid after performing this removal.

This warning is displayed when you select to delete an existing SYSPRO Business Activity Query that has been published to SQL.

Select No to keep the view.

Alternatively, select Yes to remove the SYSPRO Business Activity Query from SYSPRO and SQL.

This message is displayed when you select the Save and Publish function for a SYSPRO Business Activity Query that already exists in SQL.

Select No to cancel the publishing and avoid affecting any objects which currently make use of the SYSPRO Business Activity Query.

Alternatively, select Yes to proceed with saving the amended view and publishing to SQL.

This message is displayed after you select the Save and Publish function, prompting you to confirm if the SYSPRO Business Activity Query must be made available within SYSPRO.

Select No to proceed with publishing to SQL, without making the view available to use in SYSPRO yet.

Alternatively, select Yes to proceed with the publish process and immediately make the view accessible within SYSPRO (e.g. within SYSPRO Business Activity Query). This then automatically enables the Available to use field against the SYSPRO Business Activity Query within the View Properties pane.

Yes. Using the {custom} option within the Columns pane of the Business Activity Query Builder program, you can define free-format SQL snippets (up to 3,000 characters) that will be built into the SQL statement for the custom form definition.

Don't include any top-level aggregations or the column alias within the custom column definition snippet. You can rather define these in the Aggregate and Alias columns of the Columns list view.

Yes. If the standard condition entries available in the Where or Join clause definition of the Business Activity Query Builder program don’t cover what you require, then you can select the Custom function to enter a free-format SQL expression of up to 3,000 characters.

You can remove a data source from the Data Sources list view of the Business Activity Query Builder program by highlighting the line and selecting the Delete toolbar function (or by pressing DEL on your keyboard).

If you delete a data source that has other data sources linked to it, those tables are also removed. As well as any columns associated with these data sources.

An error is displayed if any of the data sources being deleted are referenced in either the Join or Where clauses. In this scenario, you must amend the clauses to remove the references before you can remove the data source.

If you change an alias on a data source in the Business Activity Query Builder program (e.g. changing SorMaster to SM) then the system searches through both the Join and Where clauses and replaces the applicable entries with the new alias. However, if you have a custom join within the SYSPRO Business Activity Query, the system only replaces the aliases if they are enclosed in square brackets (e.g. Entries defined as [SorMaster] will be replaced with [SM]).

The SYSPRO architecture supports a maximum numeric column size of 20 integers and 6 decimals. Therefore, its quite easy for the implied result to exceed one or both of these limits when a calculation is defined in a SQL statement, as often these are calculated according to the maximum value (i.e. 38 digits).

An error message similar to the following is displayed when you attempt to view the data, if you create a SQL statement with calculated columns that exceeds these limits:

To overcome this, you need to wrap the calculation in either a CAST or CONVERT function, as per the sample SQL statement below:

Copy 
SELECT
       StockCode, 
       CAST(AVG(QtyOnHand) as decimal(20,6)) as AverageOnHand,
       CONVERT(decimal(20,6),SUM(QtyOnHand * UnitCost)) as OnHandValue
FROM InvWarehouse
GROUP BY StockCode

The following process occurs when you complete the SYSPRO Business Activity Query's definition and select the Save and Publish function:

To ensure successful publishing across all required companies, the tables and columns referenced by the SYSPRO Business Activity Query must exist in all the company databases that you want to publish the view to.

  1. The Companies to Publish View window is displayed if the SYSPRO environment has multiple companies, from where you can indicate the companies in which the view must be accessible.

    Considerations:

    • The SYSPRO Business Activity Query is always published to the current company.

    • For changes to an existing SYSPRO Business Activity Query, the selected entries default to the companies in which the view was previously published.

    • If you deselect a company in which an existing view was previously published, then the view is deleted from that company’s SQL database.

  2. The system validates the SQL statement within the context of the current company to ensure that it doesn't contain invalid statements (e.g. INSERT, UPDATE, DELETE, TRUNCATE, etc.).

    The publishing process halts and the relevant error message is displayed if this validation fails for any reason.

  3. The definition of the SYSPRO Business Activity Query is saved to the relevant SQL tables.

  4. 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 and the SYSPRO Business Activity Query is accessible within business insight tiles and SYSPRO Data Dictionary once the publish process has completed.

  5. The SYSPRO Business Activity Query is created (or altered if it already exists) against the current company.

  6. If the publishing process is successful, the version along with the published and available flags are updated within the Admin Business View Master table (AdmViewMaster).

  7. A copy of the view's definition is written to the following SQL tables:

    • Admin Published Business View Master (AdmViewMasterPub)

    • Admin Published Business View Tables (AdmViewTablesPub)

    • Admin Published Business View Columns (AdmViewColumnsPub)

    • Admin Business View Audit (AdmViewAudit)

  8. When publishing to multiple companies, the SYSPRO Business Activity Query is built in the context of each company (considering the collation, shared inventory, shared general ledger, etc.) and is published accordingly.

    If the publication fails against a particular company, then the system ignores that company and moves onto the next company for publication.

    This can occur if objects (e.g. custom form columns) are not defined in within that particular company.

  9. Once all required companies have been processed, the SYSPRO Business Activity Query is added (or updated) against the SYSPRO Data Dictionary for use within applications (such as the Report Writer or business objects like COM Generic Fetch or COM Generic Browse).

The SYSPRO Business Activity Query's header block within SQL contains the following information:

  • Name

  • Description

  • View version and database version

  • Which operator created the view and when

  • Which operator last updated the view and when

There are no locking hints defined within the view (e.g. NOLOCK) as a view relies on lock inheritance (i.e. the lock at the parent scope is inherited by all resources within that scope). This means that you should define your lock hint when you submit your SELECT statement for the view.

The following is a sample definition of a SYSPRO Business Activity Query published in SQL (based on the requirement to view the sales order book by customer):

When a SYSPRO Business Activity Query is published to SQL (i.e. the view is created or altered in the relevant SQL database) an audit entry is created within the AdmViewAudit table for each company in which the view has been created.

Therefore, the AdmViewAudit table holds the following information:

Column Description DataType

Company

Company where view is defined

char(4)

SchemaName

Schema where view is defined

varchar(20)

ViewName

View name

varchar(18)

SaveDateTime

Date and time saved

datetime

VersionView

Version for published view

decimal(6,0)

DatabaseVersion

Database version when published

varchar(20)

Operator

Operator code

varchar(20)

MaintFlag

Function

char(1)

In addition to this auditing capability, the details of the SYSPRO Business Activity Query published (i.e. the definition) are held within the following tables:

  • AdmViewMasterPub

  • AdmViewTablesPub

  • AdmViewColumnsPub

The following tables associated with the SYSPRO Business Activity Queries feature are retained within the system database and are defined as MAX tables (i.e. they don't have associated file handlers and are only accessible via optimized SQL):

The Company ID in these tables (with the exception of the AdmViewAudit table) is not set, as the SYSPRO Data Dictionary is company agnostic.

AdmViewMaster is the header (or control) table for SYSPRO Business Activity Queries and contains the following information:

Column Description DataType

Company

Company where view is defined

char(4)

SchemaName

Schema where view is defined

varchar(20)

ViewName

View name

varchar(18)

Description

Description of the view

varchar(100)

PrimaryDataSource

Primary data source

varchar(19)

ManualEntry

Y - Hand crafted SQL statement

char(1)

Published

Y - View created in SQL

char(1)

Available

Y - View available for use

char(1)

DistintValues

View is defined to return

char(1)

TopType

Top expression required

char(1)

TopNum

Number in TOP expression

decimal(6,0)

SqlWhere

Where expression

varchar(max)

SqlStatement

Stored SQL statement

varchar(max)

VersionView

Version for published view

decimal(6,0)

FileCode

File code for data dictionary

varchar(6)

AdmViewTables contains the data source(s) information that is used in the SYSPRO Business Activity Query and contains the following information:

This table only holds a single dummy entry if the view is hand-crafted (i.e. custom).

Column Description DataType

Company

Company where view is defined

char(4)

SchemaName

Schema where view is defined

varchar(20)

ViewName

View name

varchar(18)

TableSequence

Sequence of tables

decimal(3,0)

DataSource

Data Source

varchar(19)

DataSourceAlias

Data source alias

varchar(19)

Description

Data source description

varchar(40)

DatabaseFlag

Company/database flag

char(1)

JoinType

SQL Join type

char(1)

ParentSequence

Parent data source

decimal(3,0)

SqlJoin

SQL join condition

varchar(max)

AdmViewColumns contains the column information to be retrieved when using the SYSPRO Business Activity Query and contains the following information:

This table is populated by the column names/aliases that are defined in the statement if the view is hand-crafted (i.e. custom).

Column Description DataType

Company

Company where view is defined

char(4)

SchemaName

Schema where view is defined

varchar(20)

ViewName

View name

varchar(18)

TableSequence

Sequence of tables

decimal(3,0)

ColumnSequence

Sequence of columns

decimal(3,0)

ColumnName

Column

varchar(18)

ColumnAlias

Column alias

varchar(18)

Description

Column Description

varchar(35)

AlphaNumericDate

Field type flag

char(1)

ColumnDataType

SYSPRO data type

varchar(18)

SqlDataType

SQL data type e.g. varchar(10)

varchar(16)

AggType

Aggregation types

varchar(10)

OrderSeq

Order by sequence

decimal(2,0)

OrderAscDsc

Order by sequence

char(1)

CustomColumn

SQL Custom column definition

varchar(max)

When a SYSPRO Business Activity Query is published to SQL, the entries are copied from the relevant definitions tables to AdmViewMasterPub (i.e. equivalent published table). This is then used to hold the information around the table based on what is actually in SQL.

Therefore, the AdmViewMasterPub table holds the following information:

Column Description DataType

Company

Company where view is defined

char(4)

SchemaName

Schema where view is defined

varchar(20)

ViewName

View name

varchar(18)

VersionView

Version for published view

decimal(6,0)

Description

Description of the view

varchar(100)

PrimaryDataSource

Primary data source

varchar(19)

ManualEntry

Y - Hand crafted SQL statement

char(1)

Published

Y - View created in SQL

char(1)

Available

Y - View available for use

char(1)

DistintValues

View is defined to return

char(1)

TopType

Top expression required

char(1)

TopNum

Number in TOP expression

decimal(6,0)

ManualWhere

Y - Manual condition

char(1)

SqlWhere

Where expression

varchar(max)

SqlStatement

Stored SQL statement

varchar(max)

FileCode

File code for data dictionary

varchar(6)

AdmViewTablesPub contains the details of the data sources used in the published SYSPRO Business Activity Query:

Column Description DataType

Company

Company where view is defined

char(4)

SchemaName

Schema where view is defined

varchar(20)

ViewName

View name

varchar(18)

VersionView

Version for published view

decimal(6,0)

TableSequence

Sequence of tables

decimal(3,0)
DataSource Data source varchar(19)

DataSourceAlias

Data source alias

varchar(19)

Description

Data source description

varchar(40)

DatabaseFlag

Company / database flag

char(1)

JoinType

SQL Join type

char(1)

ParentSequence

Parent data source

decimal(3,0)

SqlJoin

SQL join condition

varchar(max)

AdmViewColumnsPub contains the details of the columns available in the published SYSPRO Business Activity Query.

This table is used to populate the SYSPRO Data Dictionary for use.

Column Description DataType

Company

Company where view is defined

char(4)

SchemaName

Schema where view is defined

varchar(20)

ViewName

View name

varchar(18)

VersionView

Version for published view

decimal(6,0)

TableSequence

Sequence of tables

decimal(3,0)
ColumnSequence Sequence of columns decimal(3,0)
ColumnName Column varchar(18)

ColumnAlias

Column alias

varchar(18)

Description

Column Description

varchar(35)

AlphaNumericDate

Field type flag

char(1)

ColumnDataType

SYSPRO data type

varchar(18)

SqlDataType

SQL datatype e.g. varchar(10)

varchar(16)

AggType

Aggregation types

varchar(10)

OrderSeq

Order by sequence

decimal(2,0)

OrderAscDsc

Order by sequence

char(1)

CustomColumn

SQL Custom column definition

varchar(max)

When a SYSPRO Business Activity Query is published to SQL (i.e. the view is created or altered in the relevant SQL database) an audit entry is created within the AdmViewAudit table for each company in which the view has been created.

Therefore, the AdmViewAudit table holds the following information:

Column Description DataType

Company

Company where view is defined

char(4)

SchemaName

Schema where view is defined

varchar(20)

ViewName

View name

varchar(18)

SaveDateTime

Date and time saved

datetime

VersionView

Version for published view

decimal(6,0)

DatabaseVersion

Database version when published

varchar(20)

Operator

Operator code

varchar(20)

MaintFlag

Function

char(1)

The reason you can only export SYSPRO Business Activity Queries that have been published to the current system is to ensure that the BAQ is valid and performs correctly as a SQL view.

To export published Business Activity Queries and then import them into another SYSPRO environment, proceed as follows:

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

    1. Using the check-boxes withing the selection column, indicate which Business Activity Queries you want to export.

    2. Select the Export Selected Queries toolbar function.

    3. From the Export BAQ Target window that is displayed, indicate to where you want to export the files; either as single files or consolidated within a ZIP file.

    4. Select the Export toolbar function.

    5. A confirmation message is displayed, from where you can confirm the export.

      The program is then automatically closed and a toast notification is displayed indicating the export location.

  2. Open the Business Activity Query Import program (Program List > Administration > General Setup):

    1. From the Import BAQ Source window that is displayed, indicate from where you want to import the files.

    2. Select the Import toolbar function.

      The Business Activity Queries list view is then displayed and populated with details of the files to be imported.

    3. Using the check-boxes withing the selection column, indicate which Business Activity Queries you want to import.

    4. Select the Import Selected toolbar function.

    5. The Companies to Publish View window is displayed if you have multiple companies in your SYSPRO environment and selected to publish the SYSPRO Business Activity Queries after importing them (i.e. you enabled the Publish BAQ's to SQL option of the Import BAQ Source window).

      1. Indicate to which companies you want to import the SYSPRO Business Activity Queries.

      2. Select the Continue toolbar function.

        The program is then automatically closed and a toast notification is displayed confirming the import.

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.

Using

  • The security access to the SQL views themselves is the same as that for the standard SYSPRO Base Tables. Therefore, any SQL user with db_datareader permissions can retrieve data using the views themselves.

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

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.

The following process occurs when you complete the SYSPRO Business Activity Query's definition and select the Save and Publish function:

To ensure successful publishing across all required companies, the tables and columns referenced by the SYSPRO Business Activity Query must exist in all the company databases that you want to publish the view to.

  1. The Companies to Publish View window is displayed if the SYSPRO environment has multiple companies, from where you can indicate the companies in which the view must be accessible.

    Considerations:

    • The SYSPRO Business Activity Query is always published to the current company.

    • For changes to an existing SYSPRO Business Activity Query, the selected entries default to the companies in which the view was previously published.

    • If you deselect a company in which an existing view was previously published, then the view is deleted from that company’s SQL database.

  2. The system validates the SQL statement within the context of the current company to ensure that it doesn't contain invalid statements (e.g. INSERT, UPDATE, DELETE, TRUNCATE, etc.).

    The publishing process halts and the relevant error message is displayed if this validation fails for any reason.

  3. The definition of the SYSPRO Business Activity Query is saved to the relevant SQL tables.

  4. 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 and the SYSPRO Business Activity Query is accessible within business insight tiles and SYSPRO Data Dictionary once the publish process has completed.

  5. The SYSPRO Business Activity Query is created (or altered if it already exists) against the current company.

  6. If the publishing process is successful, the version along with the published and available flags are updated within the Admin Business View Master table (AdmViewMaster).

  7. A copy of the view's definition is written to the following SQL tables:

    • Admin Published Business View Master (AdmViewMasterPub)

    • Admin Published Business View Tables (AdmViewTablesPub)

    • Admin Published Business View Columns (AdmViewColumnsPub)

    • Admin Business View Audit (AdmViewAudit)

  8. When publishing to multiple companies, the SYSPRO Business Activity Query is built in the context of each company (considering the collation, shared inventory, shared general ledger, etc.) and is published accordingly.

    If the publication fails against a particular company, then the system ignores that company and moves onto the next company for publication.

    This can occur if objects (e.g. custom form columns) are not defined in within that particular company.

  9. Once all required companies have been processed, the SYSPRO Business Activity Query is added (or updated) against the SYSPRO Data Dictionary for use within applications (such as the Report Writer or business objects like COM Generic Fetch or COM Generic Browse).

Referencing

This lets you capture a new SYSPRO Business Activity Query.

This deletes the selected SYSPRO Business Activity Query.

The following occurs when you select this function:

  • The view's definition is removed from the relevant SYSPRO tables.

  • The SQL view is deleted from each of the company databases where it has been defined.

  • An audit entry is output to the AdmViewAudit table recording the view's deletion.

This saves the SYSPRO Business Activity Query and writes the definition to the SQL tables in the system-wide database, without committing the entire view to SQL.

This is useful if you haven't yet completed the view's definition and want to complete it later, or if you need to confirm your changes to an existing view before committing them to SQL.

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.

To ensure successful publishing across all required companies, the tables and columns referenced by the SYSPRO Business Activity Query must exist in all the company databases that you want to publish the view to.

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.

Alternatively, select the browse function to select an existing SYSPRO Business Activity Query using the Data Sources program.

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

This is achieved by using the NOEXEC function followed by a T-SQL command execution to create a view with the SQL statement. When you set NOEXEC as ON, then SQL compiles the query in the context in which it will be used (in this case creating a view) but not actually executing the statement.

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.

When using the preview function, we recommend first applying the TOP clause (for testing purposes) which you can then remove before saving the view. This is because the preview will attempt to load all rows from the data source, which may be impractical when trying to view information on a large dataset (e.g. InvMovements).

Field Description

View

This indicates the name of the SYSPRO Business Activity Query, as defined within the View toolbar field.

Description

Indicate a description for the SYSPRO Business Activity Query.

For example:

A list of active jobs in SYSPRO

This free-format field allows up to 100 characters.

Custom SQL statement

This lets you enter a free format SQL statement for the SYSPRO Business Activity Query instead of defining it using the WYSIWIG interface (i.e. the Data Source and Columns panes).

When you enable this option, the syntax editor in the SQL Statement pane becomes available for you to manually enter the SQL statement to be used in the SYSPRO Business Activity Query.

When entering a custom SQL statement:

  • The aliases for the columns must follow the same rules as the WYSIWIG entry to ensure you can use them within SYSPRO. Therefore, column aliases must be 18 characters or less as this is the maximum size supported in the Data Dictionary.

  • The maximum numeric size is limited to 20 integers and 6 decimals (i.e. decimal(26,6) in SQL terms).

  • Any aggregation function on numeric columns must be enclosed using a CAST or CONVERT clause as the maximum size of a numeric field in SYSPRO is limited to 20.6.

This is useful if you want to use complex T-SQL (such as Common Table Expressions (CTE), sub selects, etc.) or clauses which are not catered for in the WYSIWIG entry (e.g. CROSS APPLY, HAVING and PIVOT clauses).

Primary data source

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

This launches the Data Sources program from where you can select the main data source from the Data Sources pane.

If you change this entry, any data sources and columns that were already defined are removed.

TOP clause

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.

The options available include:

  • None

  • Rows

  • Rows with ties

  • Percent

  • Percent with ties

TOP number

If you enabled the TOP clause option, this lets you indicate the number of rows or percentage of data to return.

This field is limited to the value of 100 if the TOP clause is defined as Percent.

Distinct values

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

WHERE clause

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.

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

Properties

 

Published in SQL

This indicates whether the view has been published to SQL against the company database.

This read-only field is automatically updated by the system when you publish the SYSPRO Business Activity Query.

Available to use

This lets you indicate whether the SYSPRO Business Activity Query is available for use in SYSPRO (e.g. Business Insights).

This window is displayed when you select the Edit SQL where clause hyperlink from the WHERE clause field of the View Properties pane.

This deletes the highlighted line.

This saves your changes and returns you to the previous screen.

This discards your changes and returns you to the previous screen.

This changes the window from a list view to a syntax editor from where you can define the free-format SQL expression for a custom WHERE definition.

Standard clause definition

The list view in this window lets you define up to 20 WHERE conditions between the selected data sources.

Column Description

Open

Select the opening brackets required.

For example:

- = none

( = one

(( = two

Dts 1

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

Column/Literal1

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

Literals must be enclosed in single quotes if alphanumeric.

Condition

Select the condition required:

  • = equal

  • <> not equal

  • < less than

  • > greater than

  • <= not greater than

  • >= not less than

  • Like

Dts 2

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

Column/Literal 2

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

Close

Select the closing brackets required.

For example:

- = none

) = one

)) = two

And/Or

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

Custom clause definition

If the standard condition entries don't cover what you require in your clause, then you can select the Custom toolbar function to enter a free-format SQL expression of up to 3,000 characters.

Considerations:

  • If you have already defined standard conditions using the standard definition entry list view and then switch to a custom SQL statement, the custom clause is initially built using those entries.

  • If you select the Standard toolbar function to return to the standard entry, any information defined within the custom entry is not transposed back to the standard entry list view.

This pane is not available if the Custom SQL statement option is enabled within the View Properties pane.

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.

This deletes the selected data source.

Column Description

Name

This indicates a tree view of the data sources defined.

Selecting the hyperlink of the data source(s) launches the Data Sources program from where you can indicate any additional tables that contain related data required for the SYSPRO Business Activity Query.

Description

This indicates the description of the data source selected and is hidden by default.

Alias

This indicates the unique data source alias used in the SQL statement and defaults to the data source, but can be changed if required.

This is useful if you are linking to the same table multiple times.

For example:

You can link the InvMaster table twice to the BomStructure table to retrieve descriptions for both the parent and component items.

Join

This indicates the join between data sources.

The components of the join clause are automatically built when a linked data source is selected, but can be changed if required.

  • A join clause in SQL is hierarchical. Therefore, you can only join on tables defined above the current entry.

  • If required, you can select a data source that is not recognized as linked (by deselecting the linked flag option in the Data Sources program) and then define the join clause from scratch.

The options available include:

  • LEFT

    Returns all entries from the left table and any matched entries from the right table.

  • RIGHT

    Returns all entries from the right table and any matched entries from the left table.

  • INNER

    Returns entries with matching values in both tables.

  • CROSS

    Combines each row from the left table with each row from the right table and has no join conditions.

Join details

The Edit hyperlink in this column lets you define the join conditions between the selected data source and the preceding data sources.

This window is displayed when you select the Edit hyperlink from the Join details column of the Data Sources list view.

This deletes the highlighted line.

This saves your changes and returns you to the previous screen.

This discards your changes and returns you to the previous screen.

This changes the window from a list view to a syntax editor from where you can define the free-format SQL expression for a custom JOIN definition.

Standard clause definition

The list view in this window lets you define up to 20 JOIN conditions between the selected data source and the preceding data sources.

You can drag the data sources in the list view if you need to move a data source in the list so that you can use it within a join.

Column Description

Open

Select the opening brackets required.

For example:

- = none

( = one

(( = two

Dts 1

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

Column/Literal1

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

Literals must be enclosed in single quotes if alphanumeric.

Condition

Select the condition required:

  • = equal

  • <> not equal

  • < less than

  • > greater than

  • <= not greater than

  • >= not less than

  • Like

Dts 2

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

Column/Literal 2

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

Close

Select the closing brackets required.

For example:

- = none

) = one

)) = two

And/Or

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

Custom clause definition

If the standard condition entries don't cover what you require in your clause, then you can select the Custom toolbar function to enter a free-format SQL expression of up to 3,000 characters.

Considerations:

  • If you have already defined standard conditions using the standard definition entry list view and then switch to a custom SQL statement, the custom clause is initially built using those entries.

  • If you select the Standard toolbar function to return to the standard entry, any information defined within the custom entry is not transposed back to the standard entry list view.

This pane is not available if the Custom SQL statement option is enabled within the View Properties pane.

Once you have defined your data sources within the Data Sources pane, you use this window to select the columns you want to return in the view.

This deletes the selected column.

Column Description

Data Source

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.

Column

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

Alias

This defaults to the name of the column selected, but can changed if required.

  • This field is limited to 18 characters and can't be spacey or contain any characters other than A – Z, a - z, 0 - 9 and underscore (_). This ensures that the alias can be imported into the data dictionary and, in effect, become the column name when used in other areas of SYSPRO.

  • Aliases must all be unique as they become the column names in the SYSPRO Business Activity Query itself which you can then select when using the view.

Description

Indicate a description for the column.

If the column is part of the standard SYSPRO tables (including custom forms) or view definitions, this defaults to the description of the column selected.

If the column is from a user-defined table or view, then the column name itself is used.

Although not mandatory, this field is very useful as it adds a meaningful description to the column, especially when referenced in places such as the business insight tiles.

Aggregate

This lets you indicate an aggregate function, if required:

  • GROUP

    If aggregation functions are in use then you must select this for columns not aggregated.

  • COUNT

    Return a count of the entity.

  • MIN

    Minimum value.

  • MAX

    Maximum value.

  • SUM

    Add the column values together.

  • AVG

    Average value.

  • VAR

    Variance between values.

  • STDDEV

    Standard deviation of values.

  • The options available in the drop-down depend on whether the column is alphanumeric, numeric or datetime.

  • The same aggregate functions available to a numeric column (except for GROUP) are available to custom columns.

  • Any aggregate entries (and alias) defined here are applied if you have a custom column.

  • If you use an aggregate function (e.g. SUM, AVG, VAR, STDDEV, etc.) on a numeric value, then these are treated as decimal(26,6) – as per the SYSPRO limit.

Order by

If you selected to include a TOP clause, this column lets you define the column order.

Therefore, enter a non-zero value if you want the column to be included in order.

For example:

0 – Not included in Order by

1+ - order sequence

The order sequence is loaded in the column sequence if the same value is entered for multiple columns.

Asc/Desc

This lets you indicate whether the column order is ascending (i.e. lowest to highest) or descending (i.e. highest to lowest).

This is only available if the order sequence is non-zero.

Type

This indicates if the column type is alphanumeric, numeric or date time.

This pane displays the SQL statement that will be used in the SQL view and is read-only if you define the SYSPRO Business Activity Query using the View Properties, Data Source and Columns panes.

If however you are defining a custom SQL statement (i.e. the Custom SQL statement option is enabled within the View Properties pane) then this pane is available and lets you manually enter the SQL statement to be used in the SYSPRO Business Activity Query.

This refreshes the SQL statement based on your changes in the View Properties, Data Source and Columns panes.

This window is displayed when you select the Save and Publish function if you have multiple companies in your SYSPRO environment.

Considerations:

  • The SYSPRO Business Activity Query is always published to the current company.

  • For changes to an existing SYSPRO Business Activity Query, the selected entries default to the companies in which the view was previously published.

  • If you deselect a company in which an existing view was previously published, then the view is deleted from that company’s SQL database.

This function accepts your company selection for the SYSPRO Business Activity Query and updates the relevant SQL databases.

Column Description

Company

This indicates the unique code assigned to the company.

Name

This indicates the company name.