SYSPRO Business Activity Queries

SYSPRO Business Activity Queries provides a low-code dataset creation tool based on business logic that solves custom dataset requirements across a number of areas of enterprise reporting in SYSPRO.

These include:

Business insight tiles
Crystal Sub Reports
Artificial Intelligence and Machine Learning Dataset Inputs
Business Intelligence Tools
SYSPRO Report Writer (via the Data Dictionary)
SYSPRO OData Custom Datasets

PDF version

Exploring

Where it fits in?

Data access

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.

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.

Terminology

Data source

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

SYSPRO base table (e.g. InvMaster)
SYSPRO custom form table (e.g. InvMaster+)
SYSPRO Business View
User defined base table (i.e. any base table in the current database that is not part of the standard SYSPRO database)
User defined custom form table (i.e. any base table defined with a trailing + sign that doesn't form part of the standard custom table definitions)
User defined views (i.e. any view in the company database that is not defined as a SYSPRO Business View)

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

Starting

Solving

FAQs

Creation and maintenance

What restrictions apply to custom SQL statement column calculations?

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

Publication

How does the SYSPRO Business Activity Query appear in SQL once published?

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

Auditing

Where can I view an audit of the SYSPRO Business Activity Queries published to SQL?

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

Where is SYSPRO Business Activity Query information stored in SQL?

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.

Admin Business View Master

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)
SysproStdBaq	Standard SYSPRO BAQ	char(1)

Admin Business View Tables

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)

Admin Business View Columns

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)
CustomAlpNumDate	Field type if a custom column	char(1)

Admin Published Business View Master

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)
SysproStdBaq	Standard SYSPRO BAQ	char(1)

Admin Published Business View Tables

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)

Admin Published Business View Columns

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)
CustomAlpNumDate	Field type if a custom column	char(1)

Admin Business View Audit

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)

Exporting and Importing

How do I export Business Activity Queries and import them into another environment?

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

Open the Business Activity Query Export program (Program List > Administration > Enterprise Reporting):
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.
Open the Business Activity Query Import program (Program List > Administration > Enterprise Reporting):
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 listview 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.

Business insight tiles

How do I create a new text-type business insight tile?

You can either create a new text-type business insight tile from scratch, or copy an existing one and then change the tile details.

In this example, we are concentrating on creating a new text-type business insight tile:

Open the Insight Tile Definition program (SYSPRO Programs > Administration > Enterprise Reporting).
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 listview. 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.
Assign KPIs by selecting the Add hyperlink of the applicable column in the Tiles listview.

Once the business insight tile has been created, it can be assigned to a workspace using the Visual Designer program.

How do I deploy a business insight tile?

Select the Design Web View (or Edit Web View) option from the Customize and control this application icon ().

This icon is available on the toolbar of the main SYSPRO window, as well as on each SYSPRO program's toolbar.
From the Visual Designer program, select the Add layout section function.
From the Configure new layout section window, select Tile Section.
Optionally enter a Section title at the Widget title placeholder and assign an icon to the tile if required.
Select the Add section function.
Click in the Add tile block (designated by the icon).

A list of KPI and Metric categories are displayed.

The number of available insights are indicated by a number in brackets alongside each tile category.
Drill down into the relevant category and select the tile you want to deploy.
(Optional) Define the following for the tile:
- Width
- Text
- Tile background color
- Icon
Select the Add tile function to assign the tile to the current layout section.

Continue adding tiles to the current layout section or to a new layout section.
Select Save and Exit.

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

Using

Supported Variables in BAQ Statements

You can define your queries using variables as opposed to actual values.

This enables you to define a SYSPRO Business Activity Query in one environment (e.g. where shared Inventory and General Ledger is not in use) and then import it into an environment where one or both are used.

The following indicates the variables that are supported within SYSPRO Business Activity Query statements:

Variable	Description
${Company}	Company ID (e.g. ‘EDU1’)
${CompanyName}	Company name
${CompanyReference}	Company reference
${CompanyDb}	Company database
${SharedInventoryDb}	Shared Inventory database
${SharedGlDb}	Shared General Ledger database
${SystemDb}	System database

These variables are then replaced for each company at the time of publishing to Microsoft SQL Server.

The following indicates a sample SQL script using the above variables:

Copy

${Company} – is part of the join on GenMaster. This will be replaced by the current company when published to SQL
${SharedGlDb} 
${SystemDb}
${SharedInventoryDb}

SELECT                                
     [InvJournalCtl].[YearPost] AS [YearPost]
    ,[InvJournalCtl].[MonthPost] AS [MonthPost]
    ,[InvJournalCtl].[Warehouse] AS [Warehouse]
    ,[InvJournalCtl].[LedgWarehouse] AS [LedgWarehouse]
    ,[InvJournalCtl].[Operator] AS [Operator]
    ,[AdmOperator].[Name] AS [Operator_Name]
    ,[GenMaster].[Description] AS [Ldg_Desc]
    ,[InvWhControl].[Description] AS [Whs_Desc]
FROM [InvJournalCtl] AS [InvJournalCtl]
LEFT JOIN [${SystemDb}]..[AdmOperator] AS [AdmOperator] ON ([InvJournalCtl].[Operator] = [AdmOperator].[Operator] COLLATE DATABASE_DEFAULT)
LEFT JOIN [${SharedGlDb}]..[GenMaster] AS [GenMaster] ON ([GenMaster].[Company] COLLATE DATABASE_DEFAULT = '${Company}' AND [GenMaster].[GlCode] COLLATE DATABASE_DEFAULT = [InvJournalCtl].[LedgWarehouse])
LEFT JOIN [${SharedInventoryDb}]..[InvWhControl] AS [InvWhControl] ON ([InvJournalCtl].[Warehouse] = [InvWhControl].[Warehouse] COLLATE DATABASE_DEFAULT)

* product-authoring@syspro.com