Business Insights Explorer (BIE) tasks can quickly compile data using a SQL query from data existing in one or more Sage 500 tables. The user can personalize a BIE task by filtering, grouping, and subtotaling the data as needed.
One limitation of BIE tasks is that they cannot take advantage of SQL temporary tables (“temp tables”) which can sometimes provide a performance boost when the system needs to return a large number of records or perform multiple calculations. Temp tables can be used to build a subset of data in queries or perform calculations which are used only during the time of the ‘transaction’. Sage 500 frequently uses temp tables in many of its SQL stored procedures.
One challenge I came across was to build a BIE task that compiled the data for the Customer Ledger (Customer Status in Sage 500). Note: You can export the data from the Ledger in the Customer Status screen by right clicking on it and selecting the Export option. The request I received was not for a traditional BIE but became a good example to illustrate why and how you may use a SQL stored procedure to build the data for a BIE or SQL view. Sage calls this concept the SQL command override. The SQL command override feature has also been discussed in a previous post.
In this example, I had a great starting point because the customer ledger already had a standard Sage stored procedure with all the underlying temp tables and queries within the database. My objective was to reuse the majority of the standard code then send the results to a customized SQL view which could be exposed in the BIE.
Warning - do not attempt to overwrite standard Sage 500 stored procedures. Best practice dictates creating a new custom stored procedure rather than modifying a native Sage 500 stored procedure.
To successfully bind the stored procedure to the BIE we must follow these rules:
- A SQL view containing all the desired output columns must exist in the database, and users must be granted necessary permissions to the view.
- The stored procedure which will populate the SQL view must have a final SELECT statement to seed the columns of the view. The output data type for each column in the stored procedure must match the data type of its related column defined in the view.
- Use a SQL script or update the record in the Maintain Business Insights Views / Context Menus task to have the stored procedure override the related SQL view.
Stored Procedure:
The stored procedure was designed to do the heavy lifting and building of the temp tables and is called at the time of compiling the view. The view is compiled either when the BIE task is first launched by the user (if the BIE is set to “Allow Load at Startup”) or when the user clicks on the Refresh icon in the BIE toolbar.
The stored procedure must be able to accept input parameters for any column filters selected by the user in the BIE task. At a minimum the BIE tasks are typically filtered by the Sage 500 CompanyID from which the user launches the BIE task.
The last step in the custom stored procedure is to build out the SELECT statement including the WHERE clause to handle the user-selected filters in the BIE task. To do this, first make sure to use the following parameters to execute your stored procedure: @CompanyID, @SelectClause, @TopClause, @OrderBy, and @Filter.
Parameter definitions:
@CompanyID – 3 character alphanumeric company code
@SelectClause – Columns to be returned (if NULL, then all columns will be returned “*”)
@TopClause – Number of records to return (if NULL, then all records will be returned). This is translated as a number not a percentage.
@OrderBy – A custom sort in ascending or descending order. May define one or more specific column(s) to add sorting. If NULL, then the default sort order will be returned.
@Filter – Filter a subset of data with a specific condition. This is typically the “Where” condition in a select statement.
Include these parameters at the beginning of the stored procedure as illustrated below:
CREATE PROCEDURE [dbo].[spYOURPROCNAME]
@CompanyID VARCHAR(3),
@SelectClause VARCHAR(MAX),
@TopClause VARCHAR(255),
@OrderBy VARCHAR(MAX),
@Filter VARCHAR(MAX)
AS …
You will also need to declare the following variables and set their data type appropriately:
DECLARE @TempTable VARCHAR(255)
SELECT @TempTable = '#txxYOURTEMPTABLE' -- YOUR TEMP TABLE
DECLARE @OriginalView VARCHAR(255)
SELECT @OriginalView = 'vdvYOURSQLVIEW' -- YOUR SQL VIEW
After you’ve built all the data into #txxYOURTEMPTABLE, simply create the SELECT statement as the very last step in the stored procedure as follows:
DECLARE @SQL VARCHAR(MAX)
SELECT @Filter = COALESCE(@Filter, '')
SELECT @TopClause = COALESCE(@TopClause, '')
SELECT @OrderBy = COALESCE(@OrderBy, '')
SELECT @SelectClause = COALESCE(@SelectClause, '')
--Replace original view references with temp table
SELECT @SelectClause = REPLACE(@SelectClause, @OriginalView, @TempTable)
--Build query to get table from temp table using selection clause, top, order by
--and filter from original query
IF (LEN(@TopClause) > 0)
SELECT @SQL = 'SELECT ' + @TopClause + @SelectClause + ' FROM ' + @TempTable
ELSE
SELECT @SQL = 'SELECT ' + @SelectClause + ' FROM ' + @TempTable
IF (LEN(@Filter) > 0)
SELECT @SQL = @SQL + ' WHERE ' + @Filter
IF (LEN(@OrderBy) > 0)
SELECT @SQL = @SQL + ' ORDER BY ' + @OrderBy
--Get the data from the temp table
EXEC (@SQL)
Create a Task
Once your stored procedure and view have been created in the database, create a task for your BIE using the Sage 500 Task Editor (System Manager > Tools > Task Editor). This will allow the BIE to be added to one or more Task Menus and should be completed prior to the next step. Review the blog: Create a custom task in Sage 500 ERP for instructions on how to complete this.
Setting the SQL Command Override
Now navigate to System Manager > Maintenance > Maintain Business Insights Views / Context Menus to set the View Override for the BIE task. The “View Override” field is blank for most standard Sage 500 BIE tasks indicating that the BIE task will pull the data directly from a SQL view. For our custom view, we will instead insert the name of our custom stored procedure into the View Override field so that the BIE task calls the stored procedure first:
The final step is to add the BIE to the user task menu(s).
And don’t forget to save the change to the menu:
