Joining Dashboard Data

Users of InetSoft's award-winning dashboard software can customize their reports fully by joining dashboard data in an interactive real-time data mashup. View the example below to learn more about the Style Intelligence solution.

When you join two tables, the default join type created is an equi-join. This join type preserves only the rows of the two tables that have matching values on the join columns.

Sometimes, in addition to preserving the matching rows, you may also want to preserve all the rows of one or both base tables. A join which preserves all rows of one or both base tables is called an outer join.

Tables that do not share the same data model or query can be joined together with a cross join. A cross-joined table contains the cross-product (Cartesian product) of the data in the base tables. This means that every row in the first table is paired together with every row in the second table. The length of the new joined table is the product of the two base table lengths.

To cross-join two tables in a Data Worksheet, follow these steps:

1. Drag the first table alongside the second table.

2. Position the dragged table so that its border touches the border of the second table. The join icon should appear.

3. When the cross-join icon appears, release the dragged table.

The two tables are cross-joined to create a new table. Filtering conditions from both base tables are applied to the cross-joined table.

If the two base tables are mergeable (i.e., share the same data model or query), the steps above will produce a merged table rather than a cross-joined table.

Say we have two queries, 'Salesperson' and 'Sales by Employee'. The 'Salesperson' query returns the names of the sales people and their sales quotas. Cross joining the tables corresponding to these two queries will create a joined table with a number of rows equal to the product of the rows of the two base tables.

“Flexible product with great training and support. The product has been very useful for quickly creating dashboards and data views. Support and training has always been available to us and quick to respond. - George R, Information Technology Specialist at Sonepar USA

1. Create a new Worksheet by clicking on the 'New Worksheet' button.

2. Expand the 'Query node, then the 'Orders' node, and the 'Order Model' node.

3. Drag the 'Salesperson' entity on to one of the empty cells in the Data Worksheet. This creates a new table, 'Salesperson1'.

4. Drag the 'Sales by Employee' query from the tree on to one of the empty cells in the Data Worksheet. This creates a new table, 'Sales by Employee1'.

5. Now click on the title row of the 'Sales by Employee1' table, hold down the left mouse button and drag it close to the 'Salesperson1' table so that the two tables are laid side-by-side.

6. When the 'merge' icon appears, release the mouse to create the join. A new joined table, 'Query1', is created.

7. Preview the 'Query1' table by right-clicking on the title row and selecting 'Preview' from the context menu. Notice that the 'Query1' table contains the cross product of the data from the 'Salesperson1' and 'Sales by Employee1' tables.

Read why choosing InetSoft's cloud-flexible BI provides advantages over other BI options.