7.11.2 Combining Multiple Data Sources in a Formula Table
You can use a formula table to represent data from more than one data source. To bind more that one data source in a formula table, follow the steps below:
1. Create a formula table with a binding to a single data source, as you usually would. (The easiest way to do this is to convert a regular table. See Walkthrough: Creating a Formula Table for an example.)
2. Click on the formula table to select it. In the Data Source panel, press the 'Show All' button. This displays all available data sources.
3. Bind the second data source to the formula table. You can do this in one of two ways:
a. Insert a new column or columns into the table (see Inserting Rows and Columns into a Table), and then drag a field from the desired second data source in the Data Source panel directly into the formula table.

This adds the second data source to the Binding panel under the original data source, and makes all of the remaining fields in the second data source available for binding within the table.

b. Alternatively, drag the desired second data source from the Data Source panel into the Binding panel below the first data source. This will make the fields from the second data source available for binding within the formula table.

4. Repeat the above steps to bind additional data sources to the Table.
5. Press the 'Define Join Columns' button in the Binding panel.

This opens the 'Joins' dialog box. The 'Joins' dialog box allows you to specify the relationships between the multiple data sources so that corresponding rows from each source can be appropriately aligned in the table. To do this, in the following steps you will select one or more pairs of columns (“join columns”) that will be used to align rows from the different data sources.
6. In the 'Joins' dialog box, drag a desired join column from the first data source in the 'Available Columns' tree to the 'Columns To Join' region.
Look for the green highlight immediately below the existing column.
7. Drag a corresponding join column from the second data source in the 'Available Columns' tree to the 'Columns To Join' region. Drop the column immediately below the first join column.

8. (Optional) Repeat the above steps to add join columns from any additional available data sources to this join association.
By joining different data sources on a particular set of “join columns,” you specify that the records displayed in the formula table will be matched based on the values in these columns. For example, if the table contains columns 'company_name', 'address', and 'city' from query A, and contains columns 'Company', 'Employee', and 'Total' from query B, by joining the 'company_name' field to the 'Company' field, you ensure that records corresponding to the same company (from the two queries) are printed on the same table row.
9. Press the 'Union'/'Intersection' button to select the desired type of join.


The 'Union' option provides the equivalent of a full outer join. When this option is selected, all values that appear in a join column will be represented in the table, even if those values have no match in the other join columns. For example, if the 'Company' field from query B contains the value “InetSoft” but the 'company_name' field from query A does not contain this value, the 'Union' option will still display the “InetSoft” rows from query B in the table. This means that the corresponding rows from query A will appear empty in the table, because query A contains no data corresponding to the value “InetSoft”.

The 'Intersect' option provides the equivalent of an inner join. When this option is selected, only values that have a match in all join columns will be represented in the Table. For example, if the 'Company' field from query B contains the value “InetSoft” but the 'company_name' field from query A does not contain this value, the 'Intersect' option will not display the “InetSoft” rows from query B in the table. The 'Intersect' option therefore produces a table with no empty records.
10. (Optional) To specify another set of join columns, drag the first column of the new join set to an empty region within the 'Columns To Join' panel. Then proceed to add the additional columns and set the join types as described above.

By specifying multiple join sets, you introduce additional constraints on the records that the Table displays. For example, in the above illustration, the only complete rows displayed are those for which the value of 'company_name' matches the value of 'Company', and simultaneously the value of 'customer_id' matches the value of 'EmployeeID'. However, as mentioned above, the 'Union' option will generate partially empty rows where matching values do not exist in all join columns.
Join columns in the table must be grouped.
11. Assign grouping to the join column in the table. See Grouping Data in a Table for more details.

The formula table will now display data from the multiple data sources you specified in the Binding panel, and records from these various data sources will be coordinated according to the join relationships you specified in the 'Joins' dialog box. The example below provides additional guidance about formatting and display considerations.
In this example, you will create a formula table that displays data drawn from two different queries, the 'All Sales' query and the 'customers' query. The 'customers' query will provide company addresses, and the 'All Sales' query will provide the sales details.

To build this formula table, follow the steps below:
1. Create a new blank report.
2. From the Style Studio Data Source panel, drag the entire 'All Sales' query into the report.

This creates a new table based on the 'All Sales' query.

3. Click the 'Company' detail cell to select it. Right-click the cell, and select 'Region' > 'Insert Group Header' from the context menu. This creates 'GH1' header row.

4. Drag the 'Company' field from the 'Detail' row to the cell in the 'Employee' column of the new 'GH1' row. This creates a new grouping based on 'Company'.

5. Click any cell in the table to select it. Right-click, and select 'Table' > 'Convert to Formula Table' from the context menu. This changes the table to a formula table, which will allow you to perform advanced operations such as binding multiple data sources.

6. Click any cell in the middle row to select it. Right-click, and select 'Row' > 'Append Row' from the context menu. This adds a new row above the final row.

7. Shift-click to select all cells in the top row except for 'Company'. Drag these cells together into the new row that you created in the previous step.

8. Click any cell in the top row to select it. Right-click the cell, and select 'Row' > 'Delete Row' from the context menu. This deletes the top row of the table.

9. In the top-left corner cell, type the text “Customer:”.

10. In the Style Studio Data Source panel, press the 'Show All' button to display all available data sources. Expand the 'customers' query.
11. From the 'customers' query, drag the 'address' field into the table next to the 'Company' field.

12. Repeat the above step to bind the 'city' and 'state' fields from the 'customers' query.

The table now contains fields from the two different queries. However, because the queries have not yet been related to one another (“joined”), the table cannot yet represent the data correctly. In the next steps, you will specify the association between the two queries based on the company names.
13. In the Style Studio Binding panel, press the 'Define Join Columns' button. This opens the 'Joins' dialog box.

14. In the 'Joins' dialog box, drag the 'Company' field from the 'Available Columns' list into the 'Columns To Join' list.
Join fields that are displayed in the table must have grouping applied. (The 'Company' field was already specified as a grouping field in an earlier step.)
15. Drag the 'company_name' field from the 'Available Columns' list into the 'Columns To Join' list. Drop it immediately below the 'Company' field when you see the green highlight.

16. Press the 'Union/Intersect' button to select the 'Intersect' option (see above). This ensures that only those companies that appear in both queries will be represented in the table.
17. Press 'OK' to exit the 'Joins' dialog box.
18. Click in the left margin immediately next to the top row of the table. This selects the top row of the table. Use the Style Studio toolbar or Properties panel to apply any desired visual formatting to the top row, such as fonts or colors.

19. Select the non-empty cells in the middle row, and apply any desired additional desired formatting, such as background color or a data format (for example, a Date format for the 'Order Date' field).

20. Preview the report.

The table now displays data drawn from the two different queries. Records from the two queries are correctly synchronized by the join which you defined on the respective company name fields.
See Also
Advanced Formula Table Walkthrough, for example of binding multiple data sources using script (runQuery).
| << 7.11.1 Walkthrough: Creating a Formula Table | © 1996-2013 InetSoft Technology Corporation (v11.5) | 7.11.3 Creating a Formula Table Using Script (Advanced) >> |