A preferred choice of next-gen business leaders to take key business decision
Take full control of your business operations with Sage Enterprise Management.
Know your customers better with the most trusted CRM system in the country.
A comprehensive suite of cloud based payroll software to manage key HR processes.
Get comprehensive insights about industries, business and ERP Software.
Learn how to leverage maximum return and potential for ERP Software
Home » Sage 300cloud » How to configure SQL query to create BI report
Business Intelligence Reporting is an Excel-based reporting tool that gathers data from every module in your Sage 300 system which allows users to slice, dissect, and analyze the figures in a comfortable spreadsheet environment.
In this blog, we will show you how to configure the SQL query using a business intelligence report in Sage 300 to generate an Excel-based report.
Here we will take the example of a Customised ‘Sales report’ SQL query.
First, we have to execute the SQL query in the database so we can incorporate this SQL query with the Business intelligence report using the BI connector.
Open the BI connector in Sage 300 and click on the Enterprise to expand the menu and then click on the ODBC driver SQL server and then right-click on the Sage Accpac (Auto Connect) to add the data container i.e. the SQL query of the report which we will configure in BI connector.
⇒ Below is the image for your reference :
Now select the third option and click on OK
After that, locate the SQL query name which was created, in our case the name is ‘VW_sales_report’, and then click on OK to add it in the Sage Accpac (Auto Connect).
Now, expand the Sage Accpac (Auto Connect) by double click on it and then right-clicking on it to add the expression.
Click on the first option and then click the OK button to add all the columns present in the SQL query.
Select all the columns and click on the OK button.
All the Tables will be visible under the report which we have added through the expression.
Now we will open the BI Report Manager, here we will create one folder named ‘Custom report’ and add the report which we have incorporated in the BI connector.
Right-click on the folder which we have created and click on the ‘Add Report’ option to add the report
Select the first option i.e. Standard Report and click on OK.
Then we will give the name to the report i.e. Sales report and click on the OK button.
Then locate the SQL query name i.e. ‘VW_Sales_report’ and select the same and click on the OK button.
Select all the options and click on the OK button to add the report to the Custom Report folder.
Now the report has been added to the custom report folder and you can add the parameters and column sequence and many more as per your requirement for extracting the report in Excel. Here we will add the Date and Location criteria to extract the report.
Once all the configuration is done click on the Run button to run the report and give the values in the parameter and click on the OK button to generate the report.
⇒ Below is the sample report for your reference which is generated from the Sage 300 Business Intelligence report :
Fill in the details below and we would be glad to help you.