Business Activity Query Export

This program lets you export published SYSPRO Business Activity Queries for importation into other SYSPRO environments.

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.

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)

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

Business Activity Queries

This listview displays all SYSPRO Business Activity Queries that have been published within your current SYSPRO environment.

Column	Description
Selection	This lets you select which SYSPRO Business Activity Queries within the listview you want to export.
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 SYSPRO Business Activity Query when it was published.
Db Version	This indicates the database version when the SYSPRO Business Activity Query was published.
Published	This indicates the date and time of the SYSPRO Business Activity Query's publication.

Export BAQ Target

Only the Target Zip file field is displayed in the SYSPRO Web UI (Avanti) version, as once exported, the zip file will be located in the Downloads folder specified in the browser settings.

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

Field	Description
Output format	Separate files This creates a separate file for each SYSPRO Business Activity Query selected. Single ZIP file This creates one ZIP file containing all of the SYSPRO Business Activity Queries selected.
Client or server	This lets you specify where you want the files to be exported to, either Client or Server (in a client-server environment).
Target folder	Specify the name of the target folder when exporting separate files. The default server target folder is \Work\BAQ_Import_Export. This only applies if you selected Separate files against the Output format.
Target ZIP file	Specify the name of the target folder when exporting a single zip file. The default server target folder is BAQ_Package.ZIP. This only applies if you selected Single ZIP file against the Output format.

* product-authoring@syspro.com