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.”

 table1

Enter the Connection Name, path to the Sage 100 data directory, appropriate company code and Sage 100 user ID and password.

table2

Click “Add.”

  1. Add a new Data Container to your Connection. Select the Container Type.
    table3

Select “Graphical Join” if you are going to use more than one table.

table4

  • 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.”

table5

Check the tables for the report. Click “OK.”

table6

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.

table7

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.

  1. Adding Expressions

Right-click on the data container and select “Add Expressions” or click the data container and select “Add Expressions” from the toolbar.

table8

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.

table9

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.

table10

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.

Colleen Gutirrez

Colleen, a senior consultant with BKD Technologies, has more than 18 years of experience as a technology consultant, performing needs assessments and analyses and providing system selections, implementations, installations, training and support for a variety of industries. She also implements, supports and provides training on Sage ERP 100 and 500 accounting applications and Sage Intelligence.

Colleen Gutirrez – who has written posts on Sage ERP Insights.


Tagged on:

Leave a Reply

Your email address will not be published. Required fields are marked *