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
Exploring
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.
-
Reduced risk of inappropriate access to SYSPRO data.
-
Increased flexibility of creating custom linked data, including custom form fields.
-
SQL views no longer need to be created outside of SYSPRO (i.e. using a tool like Microsoft SQL Server Management Studio).
-
Less reliance on custom SQL objects for data access (for both reporting and integrations).
-
Reduced need for SQL knowledge in creating custom datasets.
The programs related to this feature are 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.
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.
Solving
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:
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 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) |
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 > General Setup):
-
Using the check-boxes withing the selection column, indicate which Business Activity Queries you want to export.
-
Select the Export Selected Queries toolbar function.
-
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.
-
Select the Export toolbar function.
-
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 > General Setup):
-
From the Import BAQ Source window that is displayed, indicate from where you want to import the files.
-
Select the Import toolbar function.
The Business Activity Queries list view is then displayed and populated with details of the files to be imported.
-
Using the check-boxes withing the selection column, indicate which Business Activity Queries you want to import.
-
Select the Import Selected toolbar function.
-
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).
-
Indicate to which companies you want to import the SYSPRO Business Activity Queries.
-
Select the Continue toolbar function.
The program is then automatically closed and a toast notification is displayed confirming the import.
-
-
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.
You can either create a new text-type business insight tile from scratch, or copy an existing one and then change the tile details.
In this example, we are concentrating on creating a new text-type business insight tile:
-
Open the Insight Tile Definition program (SYSPRO Programs > Administration > General Setup).
-
To create a new tile definition, select the New Tile function.
The Tile Builder program is displayed:
-
From the New Tile window, configure the following tile details and select OK:
-
Tile type - select Text
You can only select the tile type if you are creating a new tile, as it is inherited from the copied tile.
-
Tile description
-
File name
-
-
From the Tile Properties pane, configure the following sections:
-
Tile header and parameters
In the SYSPRO Web UI (Avanti) version, the Configure parameters link forms part of the Tile header section; whereas in the SYSPRO Desktop version, the parameters can be configured at the Parameters section.
-
Summary tile
-
Tile preview simulated values
The variables will be replaced at runtime using the output of the tile summary SQL script.
This functionality is only available in the SYSPRO Desktop version.
-
-
Configure your SQL script:
-
Select the Edit Tile Summary function to configure the SQL script that is used to render the summary tile values – including the main tile value, together with optional subtitle and footer values.
Or
-
Select the Edit Tile Detail function to configure the SQL script that is used to render the column values of the detail list view. These values are displayed when clicking on the tile to zoom into the details.
You can use the Edit Tile Summary option to generate the SQL statement using a simple-to-use interface, or you can use the Edit Tile Detail option to define a custom SQL script which provides more flexibility when presenting sophisticated information.
Select the Validate SQL and Close function to validate and save the SQL script, and return to the Tile Builder program.
-
-
Preview the tile in the Tile Builder program.
-
Select the Save and Close function to return to the Insight Tile Definition program.
-
-
Assign KPIs by selecting the Add hyperlink of the applicable column in the Tiles list view.
Once the business insight tile has been created, it can be assigned to a workspace using the Visual Designer program.
-
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
The following describes how to create a new SYSPRO Business Activity Query:
-
Open the Business Activity Query Builder program (Program List > Administration > General Setup).
-
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.
-
Within the View Properties pane, enter the following fields:
DescriptionIndicate a description for the SYSPRO Business Activity Query.
For example:
A list of active jobs in SYSPRO
Primary data sourceThis 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.
(Optional) TOP clause and TOP numberThis 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.
(Optional) Distinct valuesThis lets you remove any duplicate values from the dataset and return unique rows only.
-
(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.
-
Within the Columns pane, indicate the table columns that you want returned in the SYSPRO Business Activity Query:
Data SourceSelect 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.
ColumnsEnter 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
-
Optional... Define a WHERE clause:
Defining a WHERE clause against the result set is useful as it limits the dataset from returning unnecessary data.
-
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.
-
Enter the following fields as required for your clause:
OpenSelect the opening brackets required.
For example:
- = none
( = one
(( = two
Dts1Select the applicable data source (or {literal} entry) from the drop-down.
Column/Literal 1Enter (or browse for) the column or literal to be compared.
Literals must be enclosed in single quotes if alphanumeric.
ConditionSelect the condition required:
-
= equal
-
<> not equal
-
< less than
-
> greater than
-
<= not greater than
-
>= not less than
-
Like
Dts2Select the applicable second data source (or {literal} entry) from the drop-down.
Column/Literal 2Enter (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
CloseSelect the closing brackets required.
For example:
- = none
) = one
)) = two
And/OrIf 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: -
-
-
-
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).
-
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.
-
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.
-
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.
-
-
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.
-
The definition of the SYSPRO Business Activity Query is saved to the relevant SQL tables.
-
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.
-
The SYSPRO Business Activity Query is created (or altered if it already exists) against the current company.
-
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).
-
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)
-
-
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.
-
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 following indicates areas in the product that may be affected by implementing this feature:
Program List > Administration > General Setup
This program lets you manage SYSPRO Business Activity Queries (i.e. defined SQL views for information retrieval and custom reporting).
Accessible from the View data function of the Details column in the Data Sources pane of the Data Sources program
This program lets you view the data contained within a selected data source and is dynamically built based on the data source (e.g. InvMaster) or SQL SELECT statement it receives from the calling program.
Accessible from the Select data source function of the Primary data source field (or when browsing for linked data sources or columns) within the Business Activity Query Builder program
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.
Copyright © 2022 SYSPRO PTY Ltd.