Business Activity Query Import

This program lets you import and publish Syspro Business Activity Queries.

Exploring

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

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.

Publishing

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(30)
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(30)
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(30)
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(30)
TableSequence	Sequence of tables	decimal(3,0)
ColumnSequence	Sequence of columns	decimal(3,0)
ColumnName	Column	varchar(30)
ColumnAlias	Column alias	varchar(30)
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(30)
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(30)
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(30)
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(30)
ColumnAlias	Column alias	varchar(30)
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(30)
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)

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)

Referencing

Import BAQ Source

This pane is displayed when you first open the program as you need to indicate from where you are importing your Business Activity Queries.

Only the Source Zip file and Publish BAQs to SQL fields are displayed in the Syspro Web UI (Avanti) version, as the zip file containing the exported tiles will be located in the Downloads folder specified in the browser settings.

The input format and environment can't be selected, as only single zip files can be imported from a client device. This is because only single zip files are exported in the Syspro Web UI (Avanti) version and the device typically won't have a server connection.

Field	Description
Input format	Indicate the format you require for the input: Separate files This lets you import separate Business Activity Queries. Single ZIP file This lets you import a ZIP file containing all the Business Activity Queries. This defaults to the selection made during the export process in the Business Activity Query Export program.
Client or server	Specify from where you want to import the files, either Client or Server (in a client-server environment).
Source folder	Specify the source folder where the separate Business Activity Queries are located. This only applies if you selected Separate files against the Input format.
Source ZIP file	Specify the source folder name where the zip file containing Business Activity Queries is located. This only applies if you selected Single ZIP file against the Input format.
Options
Publish BAQs to SQL	When this is enabled, Business Activity Queries are published to Microsoft SQL Server once copied or extracted from the ZIP file. If this is disabled, only the definitions of the Business Activity Queries are imported.

Business Activity Queries

Column	Description
Selection	This lets you select which Syspro Business Activity Queries within the listview you want to import.
Name	This indicates the name of the Syspro Business Activity Query.
Description	This indicates the description of the Syspro Business Activity Query.
Version	This indicates the version of the published Syspro Business Activity Queries as it was within its source location before being exported.
Db Version	This indicates the database version when the Syspro Business Activity Queries was published.
File name	This indicates the name of the file from where the Syspro Business Activity Query was imported.
Date exported	This indicates the date on which the Syspro Business Activity Query was exported.
Exists	This indicates if the Syspro Business Activity Query exists in the current SYSPRO environment.
Current Version	This indicates the version of the Syspro Business Activity Query in the current system.

Companies to Publish View

This window is displayed if you selected to publish the Syspro Business Activity Queries after importing them and have multiple companies in your SYSPRO environment. This is so that you can indicate to which companies you want to import the Syspro Business Activity Queries.

Column	Description
Selection	This lets you select the company to which you want to publish the Syspro Business Activity Queries. Considerations You can't deselect publication to the current company. If you deselect a company in which an existing view was previously published, then the view is deleted from that company’s SQL database.
Company	This indicates the company code.
Name	This indicates the name of the company.
Reference	This indicates the reference assigned to the company, if defined within the Setup Options program (Setup Options > Company > Address).

* product-authoring@syspro.com