Adding a Data Connection for New Table in Sage Intelligence
If you want to write a report using Sage Intelligence from the tables in the Work Order module, Work Order isn’t one of the available default tables from Sage. Follow these steps to add a new connection to a Sage table.
Adding a Data Connection
This is a three-part process:
1. Connect to a data source – Name connection | Location | User ID
2. Add a Container – Select Container Type | Select Tables | Name Container | Check/Sample Container
3. Add an Expression – Select Fields
1.Add new data Connection.
On the Home tab, select “Add connection.”
Enter the Connection Name, path to the Sage 100 data directory, appropriate company code and Sage 100 user ID and password.
2. Add a new Data Container to your Connection. Select the Container Type.
Select “Graphical Join” if you are going to use more than one table.
- Database Table – A single database table
- SQL Join – A join between two or more tables using SQL syntax
- View or Query – A view or query that exists in the database
- Graphical Join – A join between two or more tables using a graphical join tool
- Stored Procedure – The data comes from a database Stored Procedure
- SQL Query – Use free text SQL command
Enter a Container Name.
Select the Container and click on “Graphical Join Tool.”
Check the tables for the report. Click “OK.”
Join the tables by dragging primary fields in one table onto the secondary fields in the next table.
Once you have added the needed tables and joins, click “Apply.”
When creating a graphical join container, an Inner Join is created by default, which can be changed by right-clicking on the join box on either side of the Join and selecting “Outer Join.” The reverse polarity option on the shortcut menu switches the join between left and right.
The SQL syntax for the join can be viewed by selecting “Show SQL.”
Once joins have been created, they should be verified by using the Check/Test facility for the container.
After closing the Graphical Join Tool, the join will be displayed as a SQL syntax. When the SQL Join is verified, the system administrator can then add expressions. Once this is done, the container is ready to be used in reports.
3. Adding Expressions
Right-click on the data container and select “Add Expressions” or click the data container and select “Add Expressions” from the toolbar.
A data expression is the selection from a data container. A data expression can be:
- Data Field – A single database field, e.g., a field containing customer details such as surname or initials
- SQL Expression – Returns data from a field or combination of fields based on a SQL Expression
- Microsoft Excel Formula – Returns data based on a Microsoft Excel Function or Formula
Check all the expressions from the tables you want available in the report. These fields don’t have to be shown on the report.
Click “OK.” The data expressions are displayed below the data container.
To display the properties of your Data Expression, select a Data Expression and on the toolbar click the “Properties” button. The Data Expression properties window will appear.
The connection is now ready to be used in Report Manager.
If you have questions about this or other topics, please feel free to contact us.