You don’t have to be a SQL expert to join tables using Sage Intelligence. The Graphical Join Tool in the Connector makes it easy.
Often the data we need for our reports comes from joining two or more tables. The following process lets you quickly and easily join tables without having to know SQL, simply by clicking and dragging a connection between the fields in a group of tables.
Before using the Graphical Join Tool, it’s best to have your data requirements and know the tables and fields to use. The process is as follows:
- In the Connector module, find the connection that you want to create your container under. This is specific to your ERP or the data source you are connecting to.
- Right-click on the connection and select Add Data Container from the menu that appears.
- Select Graphical Join from the Container Type menu. Then select
- A window will appear requiring you to give the container a name. Be sure to make it descriptive so that you understand its contents. The name should also relate to the report you intend to use it for. Your container will be added to the list of containers under the connection in the Object window.
- Next, select your new container and click on the Graphical Join Tool button in the container’s Properties window.
- A new window will appear listing all the tables available in your Sage ERP, accounting package or other data source. Select the tables you need and click OK. Each of the tables selected will be shown graphically as a list of the table’s fields.
- Find the fields that are common between two tables and click and drag the field from the first table onto its related field in the second table. A join will be created between the two tables represented by a colored line with a handle on either side.
- If needed, you can change the join type of a join by right-clicking on either of the join’s handles and selecting Set Join Type.
- Select Apply near the bottom right of the screen to save your changes. You will see the SQL syntax for your join in the Join SQL field of the container’s properties.
We recommend that you test your container after you’ve created your join. You can do this by right-clicking on the container and selecting Check/Test from the context menu. If your graphical join is correct, you’ll receive a message saying that your check succeeded. If not you should check your join, container and connection for errors. You can now add expressions to your container.
If you want to add more tables to your join you can do so by selecting the Graphical Join Tool button again, selecting the Add Table button and repeating the above process.
In our next tip we’ll teach you about Excel Formula expressions and how you can use them to add fields to your report source data that are calculated only once your report is run out into Excel.
