Joined Report Case Study Analysis

1. Combining Data from Different Ranges

1.1 Example 1: Combining Monthly, Quarterly, and Yearly Data

• Core requirement: View monthly, quarterly, and yearly data together.
• Source table configuration: Three similar statistic tables based on Products; measures are Sales Amount and Sales Quantity. Each table’s data scope uses the Owner’s Dept. The date ranges are set to This Month, This Quarter, and This Year respectively.

1.1.1 Configuration Steps

1.1.1.1 Select Source Tables and Join Type

• Source tables: Base tables prepared from BI Reports and Chart(s), used as inputs for the Joined Report.
• Join types: how source tables are combined in the Joined Report
  • 1 Left join: left join — keep all rows from the left source.
  • 2 Inner join: inner join — keep intersection.
  • 3 Outer join: outer join — keep union.
  • 4 Vertical concatenation: append all rows.
• Example guidance: If “Source1—This Year Sales” contains the most complete set of dimension values (Product names), and the quarterly/monthly data are subsets, choose Left join. Use the table with the most complete dimension set as the left source and the others as right sources.

1.1.1.2 Select Common Dimension

• Common dimension: The grouping/dimension field that matches in multiple source tables. Click the same-meaning field in each source table and add them to the right to form one common dimension group. This will be the grouping/dimension field in the Joined Report and display under the renamed field label.
• Example: All three tables use “Product Name” as the dimension and you want the final label to be “Product”. Click “Product Name” in source tables 1, 2 and 3, add them to the common dimensions panel, rename to “Product”, and confirm.

1.1.1.3 Select Data Scope (Filters)

• Add merged data scope: Similar to common dimensions, shared data filters across source tables can be added from the left to the right. Click “Merge” to combine them into a single filter used in the Joined Report; it will display under the renamed field name. You can also edit the filter default value here.

• Add data scope: Filters that are not part of the common (merged) scope but that you want to expose in the Joined Report’s filter panel. Filters not added here will not display in the Joined Report and will follow each source table’s default configuration.
• Example: Make Owner’s Dept. the common data scope and rename it “Dept.” When filtering, it will apply to all three source tables. Date filters are hidden and each source table remains fixed to its configured range (This Year / This Quarter / This Month).

1.1.1.4 Column Headers and Order

• Column header names: Common dimension field names follow the label set in step 2 and cannot be changed here. Other column names can be modified as needed.
• Reorder: Drag columns to change order. Common dimensions must be first and cannot be moved.
• Add / hide columns: You can add calculated metrics as new columns or hide columns that shouldn’t display in the Joined Report.
• Example: Arrange columns to display month, quarter, year in that order. Enable “Show totals” to add a totals row at the end.

1.1.1.5 Composite Column Headers

• Composite headers: Based on the adjusted header names and order, you can add multiple levels of composite headers above the columns.
• Actions:
  • Add row: insert a new header row above
  • Delete row: remove the selected header row
  • Merge cells: select multiple cells and merge
  • Split cells: split merged cells back to original
  • Compact mode: enable for tighter spacing
  • Undo: revert last action
  • Adjust column width: click a base header to change width
  • Edit display name: click an added cell to rename
• Example: Add one header row above and merge cells to group columns by time granularity into This Month / This Quarter / This Year composite headers.

1.1.2 Joined Report Result

1.2 Example 2: Combining Different Target Rules

• Core requirement: View completion across different target rules and compute a composite KPI: Salesperson Visits weight 20%, Customer Orders weight 30%, Payment Collections weight 50%.
• Source table configuration: Three target-statistic Charts with different target rules.

1.2.1 Configuration Steps

1.2.1.1 Select Source Tables and Join Type

• Source tables: Base tables prepared from BI Reports and Chart(s).
• Join types: same as 1.1.1.1
• Example guidance: If the dimension values “Owner” are identical across source tables, you can use Inner join; intersection still yields the employees you need to evaluate.

1.2.1.2 Select Common Dimension

• Common dimension: choose the same meaning grouping/dimension field in each source table and add to form the Joined Report’s dimension field.
• Example: If all three tables use “Owner”, add “Owner” from each source table to common dimensions and confirm.

1.2.1.3 Select Data Scope (Filters)

• Add merged data scope: add shared filters and click “Merge” to combine them into one filter in the Joined Report. Edit default values if needed.
• Add data scope: add other filters you want exposed in the Joined Report.
• Example: Add two merged data scopes: Date and Owner’s Dept. These filters apply to all three source tables. Target rule filters remain hidden and each source table uses its configured rule.

1.2.1.4 Column Headers and Order

• Column header names: common dimension names follow step 2; others are editable.
• Reorder: drag columns; common dimension must be first.
• Add / hide columns: add calculated metrics or hide columns not needed in the display.
• Example: Add a calculated metric that multiplies each target’s completion by its weight and place it at the end.

1.2.1.5 Composite Column Headers

• Composite headers: same operations as 1.1.1.5
• Example: Add a header row, merge cells to group columns by target rule into Payment Collections / Customer Orders / Sales Visits. Set each column width to 100px and enable compact mode to fit on one screen.

1.2.2 Joined Report Result

2. Multi-table Joining

2.1 Example 3: Joining Different Subjects

• Core requirement: Use an Employee subject to count sales visits, number of employees with visits, and total employees; use a Customer subject to count visited customers and total assigned customers. Then compute employee Fieldwork rate and customer visit coverage.
• Source table configuration: [screenshots below]

2.1.1 Configuration Steps

2.1.1.1 Select Source Tables and Join Type

• Source tables: Base tables from Reports and Chart(s).
• Join types: same as earlier
• Example guidance: If Source1 uses dimension “Owner’s Dept.” and Source2 uses “Owner’s Dept.” (same meaning), and the source tables include zero-value dimensions, choose Inner join: the intersection still yields the departments you want to analyze.

2.1.1.2 Select Common Dimension

• Common dimension: select same-meaning grouping/dimension fields from each source table and add to form the Joined Report’s dimension.
• Example: Add Source1’s “Owner’s Dept.” and Source2’s “Owner’s Dept.”, then rename the common dimension to “Owner’s Dept.” and confirm.

2.1.1.3 Select Data Scope (Filters)

• Add merged data scope: same as earlier — merge shared filters into one filter in the Joined Report.

• Add data scope: choose which non-shared filters should be exposed in the Joined Report.
• Example: Add Date and Owner’s Dept. as merged data scopes so they apply to both source tables.

2.1.1.4 Column Headers and Order

• Column header names: common dimension label is fixed from step 2; other column names editable.
• Reorder: drag to change column order; common dimensions must remain first.
• Add / hide columns: add calculated metrics; hide intermediate columns that only serve calculations.
• Example: Add two calculated metrics:
  • Employee Fieldwork Rate = Number of employees who did Fieldwork / Total employees
  • Customer Visit Coverage = Number of visited customers / Total assigned customers Hide any intermediate columns that are only used for calculation.

2.1.1.5 Composite Column Headers

• Composite headers: same operations as previous sections.
• Example: No composite headers required for this case; save directly.

2.1.2 Joined Report Result

2.2 Example 4: No Object Relationship but Same Business Meaning

• Core requirement: On the Account object, Account Name represents Doctor name, Parent Account means Department. The Order object links to Account, while a custom Doctor object has Doctor and Department fields with the same business meaning but no relationship to Account. The Expense object links to the Doctor custom object. You want to report Order Amount and Expense Reimbursement Amount by Department and Doctor dimensions.
• Source table configuration: [screenshots below]

2.2.1 Configuration Steps

2.2.1.1 Select Source Tables and Join Type

• Source tables: Base tables from Reports and Chart(s).
• Join types: same as earlier
• Example guidance: Some doctors (Accounts) have Orders but no Expenses; some have Expenses but no Orders; some have both. To cover all scenarios, you need the union of all source tables, so choose Outer join.

2.2.1.2 Select Common Dimension

• Common dimension: select same-meaning grouping/dimension fields and add them to form the Joined Report’s dimension fields.
• Example: Add two groups of common dimensions in order:
  • First group: Parent Account and Department → final label “Department”
  • Second group: Account Name and Doctor → final label “Doctor”

2.2.1.3 Select Data Scope (Filters)

• Add merged data scope: same as earlier — merge shared filters into one filter used in the Joined Report.
• Add data scope: add filters you want available in the Joined Report.
• Example: This case has no common filters needing merge. If Source1 has a filter on Parent Account that you want to retain in the Joined Report, add that filter as a data scope; it will only apply to Source1.

2.2.1.4 Column Headers and Order

• Column header names: common dimension names follow step 2 and are fixed; other columns editable.
• Reorder: drag columns; all common dimensions appear first and cannot be moved.
• Add / hide columns: add calculated metrics; hide columns not intended for display.
• Example: With multiple common dimension groups, all common dimensions appear first. The rest of the columns can be renamed and reordered. Enable “Show subtotals” and “Show totals” so that the Joined Report shows subtotals per Department and per Doctor, and a grand total row.

2.2.1.5 Composite Column Headers

• Composite headers: same operations as previous sections.
• Example: Set each column width to 100px and enable compact mode to view on one screen.

2.2.2 Joined Report Result

3. Vertical Concatenation

3.1 Example 5: Vertical Concatenation

• Core requirement: The Expense Reimbursement object is used for everyday expense claims. For a special event (618 promotion), the business created a separate 618 Expense object with the same fields. At year-end you want to combine both expense types into one consolidated report.
• Source table configuration: [screenshots below]

3.1.1 Configuration Steps

3.1.1.1 Select Source Tables and Join Type

• Source tables: Base tables from Reports and Chart(s).
• Join types: same as earlier, including:
  • 4 Vertical concatenation: append all rows from sources.
• Example guidance: When both source tables use the same field configuration (same number of header fields and grouping order) but come from different objects, choose Vertical concatenation so the system appends data under the same column structure.

3.1.1.2 Select Common Dimension

• Common dimension: when Join Type is Vertical concatenation, you do not need to specify common dimensions — proceed to the next step.

3.1.1.3 Select Data Scope (Filters)

• Add merged data scope: same as earlier.
• Add data scope: add any non-shared filters you want visible in the Joined Report.
• Example: In vertical concatenation, filter addition works the same. In this case use Created Date as the merged data scope.

3.1.1.4 Column Headers and Order

• Column header names: when concatenating vertically, the column names default to the first source table’s header names; edit them to the desired display names.
• Reorder: column order must follow the source tables’ order and cannot be modified.
• Add / hide columns: add calculated metrics; hide columns not needed for display.
• Example: Column order follows source tables strictly and cannot be changed. Column names default to the first source table’s headers — rename as needed.

3.1.1.5 Composite Column Headers

• Composite headers: same operations as previous sections.
• Example: No composite headers required for this example; save directly.

3.1.2 Joined Report Result

4. Special Filter Conditions in Joining

4.1 Example 6: Special Filter — Field Comparison

• Core requirement: Sometimes you need special filters that compare two fields instead of checking a field against a constant. In this example, within a Leads-to-Account conversion report you want to add a comparison that checks whether the Lead Owner and the Account Owner are the same person.
• Source table configuration: [screenshot]

4.1.1 Configuration Steps

4.1.1.1 Select Source Tables and Join Type

• Source tables: Base tables from Reports and Chart(s).
• Join types: same as earlier.
• Example guidance: With only one source table, you can keep the default Left join and do not need to change join type.

4.1.1.2 Select Common Dimension

• Common dimension: when Join Type is vertical concatenation you can skip; otherwise select same-meaning grouping/dimension fields as needed.

4.1.1.3 Select Data Scope (Filters)

• Add merged data scope: merge shared filters into one filter if needed.
• Add data scope: expose other filters in the Joined Report as required.
• Special Filter 1: Specify two fields to compare and choose comparison operator: Equals or Not Equals. Rows that do not satisfy the comparison will be filtered out.
• Example: Add Converted Date and Owner’s Dept. as data scopes. Fields not added follow the source table’s default filter values.

4.1.1.4 Column Headers and Order

• Column header names: common dimension label fixed; other names editable.
• Reorder: drag columns; common dimensions must be first.
• Add / hide columns: add calculated metrics or hide columns not needed.
• Example: No composite headers required — save directly.

4.1.1.5 Composite Column Headers

• Composite headers: same operations as earlier.
• Example: Not needed for this case — save directly.

4.1.2 Joined Report Result

4.2 Example 7: Special Filter — Latest Record in Group

• Core requirement: Another special filtering need is to keep only one record per group (e.g., keep only the most recent visit per Store within a selected time range). The Joined Report platform can retain, for each store, only the latest visit in the selected date range.
• Source table configuration: [screenshot]

4.2.1 Configuration Steps

4.2.1.1 Select Source Tables and Join Type

• Source tables: Base tables from Reports and Chart(s).
• Join types: same as earlier.
• Example guidance: With only one source table, default Left join is fine.

4.2.1.2 Select Common Dimension

• Common dimension: not required when using Left join with a single source table.

4.2.1.3 Select Data Scope (Filters)

• Add merged data scope: merge shared filters as needed.
• Add data scope: expose other filters you want available in the Joined Report.
• Special Filter 2: Specify grouping field and date field, then choose to keep the earliest or the latest record per group.
• Example: To keep the most recent visit per Store during the selected month:
  • Grouping field: Account (Store)
  • Date field: Check-in Time
  • Selection: Latest

4.2.1.4 Column Headers and Order

• Column header names: common dimension label fixed; other names editable.
• Reorder: drag columns; common dimensions must be first.
• Add / hide columns: add calculated metrics; hide columns not needed.
• Example: Rename a header to “Fieldwork ID” (or similar) to make the information clearer.

4.2.1.5 Composite Column Headers

• Composite headers: same operations as earlier.
• Example: Not required for this case — save directly.

4.2.2 Joined Report Result