Joined Report Case Study Analysis

1. Joining Data with Different Ranges

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

• Core requirement: View monthly, quarterly, and yearly data together.
• Source tables setup: Three similar statistic tables from BI/Chart with the same dimension Product Name and the same metrics Sales Amount and Sales Quantity. Each table’s data scope is limited by Owner’s Primary Dept.; their date filters 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 in BI/Chart that serve as the inputs for the Joined Report.
• Join types: how source tables are combined when creating the Joined Report:
  • 1 Left join: left join — preserve rows from the left source table.
  • 2 Inner join: inner join — keep the intersection.
  • 3 Outer join: outer join — keep the union.
  • 4 Vertical concatenation: merge all data rows.
• Case note: In Example 1, “Source1 - This Year Sales” contains the most complete set of dimension values (Product Name). Quarterly and monthly data are subsets. We recommend using a left join with the most complete table as the left source and the others as right sources.

1.1.1.2 Select Common Dimensions

• Common dimensions: fields with the same meaning across source tables used as the join/grouping keys. Click the matching field in each source table and add them to the right to form a group of common dimensions. These will be the Joined Report’s grouping/dimension fields and will display under the renamed field label.
• Case note: All three tables use Product Name as the dimension. To display it as “Product”: click Product Name in Source1/2/3, add them to Common Dimensions, rename to “Product”, and confirm.

1.1.1.3 Select Data Scopes (Filters)

• Add merged data scopes: Similar to Common Dimensions, shared filters across sources can be added from the left to the right as a group. Click “Merge” to combine them into a single filter used by the Joined Report; the merged filter will display with the renamed field name. You can also modify the default filter value here.
• Add individual data scopes: filters that are not merged but should still be exposed on the Joined Report. Filters not added will not appear on the Joined Report; they will continue to apply according to each source table’s default settings.
• Case note: Add Owner’s Primary Dept. as a merged data scope and rename it “Dept.” The filter will then apply to all three source tables simultaneously. Date filters are not exposed and remain fixed per source table (This Year / This Quarter / This Month).

• Adding data scopes: controls which non-common filters appear on the Joined Report. Filters not added remain effective per source defaults.

1.1.1.4 Column Headers and Order

• Column header names: Common Dimensions show the labels set in step 2 and cannot be edited; other column names can be renamed as needed.
• Reorder: drag to reorder columns. Common Dimensions must be placed first and cannot be moved.
• Add/Hide columns: you may add calculated metrics as new columns or hide columns that are not needed in the Joined Report.
• Case note: reorder columns to show Monthly, Quarterly, Yearly in that sequence. Enable Totals to show a final totals row in the Joined Report.

1.1.1.5 Composite Headers (Multi-row Headers)

• Composite headers: display the adjusted column names and order from the previous step. You can add multiple header rows above.
• Operations:
  • Add row: insert a new header row above.
  • Delete row: delete the selected header row.
  • Merge cells: select multiple cells with the mouse to merge them.
  • Split cells: split merged cells back to originals.
  • Compact mode: enable compact layout.
  • Undo: revert the last action.
  • Adjust column width: click original header to change width.
  • Edit display name: click added cell to rename.
• Case note: add a header row above and merge cells to group columns by time granularity (This Month, This Quarter, This Year).

1.1.2 Joined Result

1.2 Example 2: Joining Different Target Rules

• Core requirement: Compare completion across different target rules and compute a consolidated KPI: Salesperson Visits weight 20%, Customer Orders weight 30%, Customer Payment Collections weight 50%.
• Source tables setup: three different target-rule statistic Charts.

1.2.1 Configuration Steps

1.2.1.1 Select Source Tables and Join Type

• Source tables: base tables prepared in BI/Chart.
• Join types:
  • 1 Left join: left join — preserve rows from the left source.
  • 2 Inner join: inner join — keep the intersection.
  • 3 Outer join: outer join — keep the union.
  • 4 Vertical concatenation: merge all data rows.
• Case note: All source tables share the same dimension values for Owner; choose inner join to keep only employees present in all three sources.

1.2.1.2 Select Common Dimensions

• Common dimensions: select the same grouping/dimension field from each source and add them to the right. They become the Joined Report’s grouping dimension.
• Case note: All three tables use Owner as the dimension. Click Owner in Source1/2/3, add to Common Dimensions, and confirm.

1.2.1.3 Select Data Scopes

• Add merged data scopes: add common filters from the left to the right and click “Merge” to combine them into a single Joined Report filter.
• Add individual data scopes: pick which non-common filters to surface on the Joined Report; others remain as source defaults.
• Case note: Add two merged filters: Date and Owner’s Primary Dept. These filters apply to all three sources. Target rule filters remain hidden and use each source’s target-rule-specific settings.

1.2.1.4 Column Headers and Order

• Column header names: Common Dimensions use the name defined earlier and are not editable; other headers are renameable.
• Reorder: drag columns; Common Dimensions must remain first.
• Add/Hide columns: add calculated metrics or hide unneeded columns.
• Case note: add a calculated metric that multiplies each target-rule completion by its weight and place it at the end.

1.2.1.5 Composite Headers

• Composite headers: you can add multi-row headers above the configured columns.
• Operations:
  • Add row
  • Delete row
  • Merge cells
  • Split cells
  • Compact mode
  • Undo
  • Adjust column width
  • Edit display name
• Case note: add a header row and merge cells to group columns by target rule (Payment Collection, Orders, Visits). Set each column width to 100px and enable compact mode for single-screen viewing.

1.2.2 Joined Result

2. Multi-Table Joins

2.1 Example 3: Joining Different Subjects

• Core requirement: Use Employee subject to count Sales Visits, number of employees with visits, and total employee count; use Account subject to count visited customers and total managed customers. Then compute Salesperson Fieldwork Rate and Customer Visit Coverage Rate.
• Source tables setup: see below.

2.1.1 Configuration Steps

2.1.1.1 Select Source Tables and Join Type

• Source tables: base tables from BI/Chart.
• Join types:
  • 1 Left join: left join.
  • 2 Inner join: inner join.
  • 3 Outer join: outer join.
  • 4 Vertical concatenation.
• Case note: Source1’s dimension is Primary Dept and Source2’s dimension is Owner’s Primary Dept — they have the same meaning. Since the source tables include zero-value dimensions, choose inner join; the intersection still represents the departments to report.

2.1.1.2 Select Common Dimensions

• Common dimensions: pick fields with same meaning from each source to form the group used by the Joined Report.
• Case note: Add Source1’s Primary Dept and Source2’s Owner’s Primary Dept as a common dimension and rename to “Owner’s Primary Dept”, then confirm.

2.1.1.3 Select Data Scopes

• Add merged data scopes by moving common filters to the right and clicking “Merge”; merged filters become a single filter on the Joined Report.
• Add individual data scopes to expose non-common filters; others remain as source defaults.
• Case note: Add Date and Owner’s Primary Dept as merged filters so they apply to both sources.

• Adding data scopes: choose which source-specific filters to surface on the Joined Report.

2.1.1.4 Column Headers and Order

• Column header names: Common Dimensions display the name set earlier and are not editable; other headers may be renamed.
• Reorder: drag columns; Common Dimensions must be first.
• Add/Hide columns: add calculated metrics or hide columns used only for intermediate calculations.
• Case note: add two calculated metrics:
  • Employee Fieldwork Rate = Fieldwork Count / Total Employees
  • Customer Visit Coverage = Visited Customers / Total Managed Customers Hide intermediate columns that are only used for calculation.

2.1.1.5 Composite Headers

• Composite headers: add multi-row headers as needed.
• Operations: Add row, Delete row, Merge cells, Split cells, Compact mode, Undo, Adjust column width, Edit display name.
• Case note: No composite header required for the final report; save directly.

2.1.2 Joined Result

2.2 Example 4: Same Business Meaning but No Object Relationship

• Core requirement: Account.Name stores Doctor Name, Account.Parent stores the Department. Orders link to Account, and a custom Doctor object contains Doctor and Department fields with the same meaning but no direct relationship to Account. Expense objects link to the Doctor custom object. The goal: from Dept and Doctor dimensions, report Order Amount and Expense Reimbursement Amount together.
• Source tables setup: see below.

2.2.1 Configuration Steps

2.2.1.1 Select Source Tables and Join Type

• Source tables: base tables from BI/Chart.
• Join types:
  • 1 Left join: left join.
  • 2 Inner join: inner join.
  • 3 Outer join: outer join.
  • 4 Vertical concatenation.
• Case note: Some doctors have Orders but no Reimbursements; some have Reimbursements but no Orders; some have both. To include all scenarios, choose outer join to union all source rows so any doctor appearing in any source appears in the final result.

2.2.1.2 Select Common Dimensions

• Common dimensions: choose fields with equivalent meaning across sources.
• Case note: add two groups of common dimensions in order:
  • First group: Account.Parent and Department → final label “Department”.
  • Second group: Account.Name and Doctor → final label “Doctor”.

2.2.1.3 Select Data Scopes

• Add merged data scopes: move shared filters to the right and click “Merge” to expose a single filter on the Joined Report.
• Add individual data scopes: pick non-common filters to expose on the Joined Report; others will follow source defaults.
• Case note: This example has no merged data scopes. If Source1 has a filter on Parent Account and you want to keep it on the Joined Report, add it as an individual data scope — it will apply only to Source1 in the final report.

2.2.1.4 Column Headers and Order

• Column header names: Common Dimensions display the name set earlier and cannot be changed; other headers may be renamed.
• Reorder: drag columns; Common Dimensions must be first.
• Add/Hide columns: add calculated metrics or hide columns not required for display.
• Case note: When multiple common dimensions exist, they appear first. Reorder and rename remaining columns as needed. Enable Subtotals and Totals to display subtotals per Department and Doctor, and a final totals row.

2.2.1.5 Composite Headers

• Composite headers: add multi-row headers if needed.
• Operations: Add row, Delete row, Merge cells, Split cells, Compact mode, Undo, Adjust column width, Edit display name.
• Case note: Set each column width to 100px and enable compact mode for single-screen viewing.

2.2.2 Joined Result

3. Vertical Concatenation

3.1 Example 5: Vertical Concatenation

• Core requirement: Expense Reimbursement is a daily object. For a special event (e.g., an “618” campaign), the team created a separate “618 Reimbursement” object with the same fields. At year-end, the business needs to report total expenses across both objects.
• Source tables setup: see below.

3.1.1 Configuration Steps

3.1.1.1 Select Source Tables and Join Type

• Source tables: base tables from BI/Chart.
• Join types:
  • 1 Left join
  • 2 Inner join
  • 3 Outer join
  • 4 Vertical concatenation
• Case note: For vertical concatenation, the two source tables must have identical column configurations (number of header fields and grouping field order) but may originate from different objects. Choose Vertical Concatenation to append the rows from both sources into a unified dataset.

3.1.1.2 Select Common Dimensions

• Common dimensions: normally used to join tables, but for Vertical Concatenation you can skip defining Common Dimensions and proceed to the next step.
• Case note: when using Vertical Concatenation, no Common Dimensions are required.

3.1.1.3 Select Data Scopes

• Add merged data scopes: move shared filters to the right then click “Merge” to form a single filter for the Joined Report.
• Add individual data scopes: choose non-common filters to expose on the Joined Report.
• Case note: Data scope configuration for Vertical Concatenation is similar to other join types. In this example, use Created Date as a merged data scope.

3.1.1.4 Column Headers and Order

• Column header names: Common Dimensions (if any) use the name as set earlier and cannot be changed; other headers are editable.
• Reorder: drag columns; Common Dimensions must be first.
• Add/Hide columns: add calculated metrics or hide unnecessary columns.
• Case note: For Vertical Concatenation, column order strictly follows the source table order and cannot be changed. Column names default to the first source table’s headers; rename them as desired.

3.1.1.5 Composite Headers

• Composite headers: optional multi-row headers.
• Operations: Add row, Delete row, Merge cells, Split cells, Compact mode, Undo, Adjust column width, Edit display name.
• Case note: No composite header required for this example; save directly.

3.1.2 Joined Result

4. Special Filter Conditions

4.1 Example 6: Special Filter — Field-to-Field Comparison

• Core requirement: Sometimes you need special filters that compare two fields rather than filter a single field value. In this example, within a Leads → Conversion report, add a comparison to check whether Lead Owner and Account Owner are the same person.
• Source tables setup: see below.

4.1.1 Configuration Steps

4.1.1.1 Select Source Tables and Join Type

• Source tables: base tables from BI/Chart.
• Join types: left join (default), inner join, outer join, vertical concatenation.
• Case note: With a single source table, the default left join is sufficient.

4.1.1.2 Select Common Dimensions

• Common dimensions: when multiple sources exist, add fields with identical meaning to form common dimensions. For single-source/left-join or vertical concatenation, you can proceed without setting common dimensions.
• Case note: when using vertical concatenation, skip Common Dimensions.

4.1.1.3 Select Data Scopes

• Add merged data scopes: move shared filters to the right and click “Merge” to create a single filter for the Joined Report.
• Add individual data scopes: select which additional filters should appear on the Joined Report.
• Special filter type 1: specify two fields to compare and choose comparison operator Equal / Not Equal. Rows that do not meet the condition will be filtered out.
• Case note: add Conversion Time and Owner’s Primary Dept. as data scopes. Fields not added will use source defaults.

4.1.1.4 Column Headers and Order

• Column header names: Common Dimensions show the name from step 2 and cannot be changed; other headers can be renamed.
• Reorder: drag columns; Common Dimensions must be first.
• Add/Hide columns: add calculated metrics or hide unused columns.
• Case note: No composite headers needed; save directly.

4.1.1.5 Composite Headers

• Composite headers: optional; operations include Add row, Delete row, Merge cells, Split cells, Compact mode, Undo, Adjust column width, Edit display name.
• Case note: Not required for this example; save directly.

4.1.2 Joined Result

4.2 Example 7: Special Filter — Most Recent Record per Group

• Core requirement: Another special filter is to retain only one record per group (e.g., for store visits a store may be visited multiple times in a period). Use the Joined Report to keep only the most recent visit per store within the selected date range.
• Source tables setup: see below.

4.2.1 Configuration Steps

4.2.1.1 Select Source Tables and Join Type

• Source tables: base tables from BI/Chart.
• Join types: left join (default), inner join, outer join, vertical concatenation.
• Case note: With a single source table, use the default left join.

4.2.1.2 Select Common Dimensions

• Common dimensions: not required when using left join with a single source; proceed to the next step.

4.2.1.3 Select Data Scopes

• Add merged data scopes and individual data scopes as described previously.
• Special filter type 2: specify the grouping field and a date field with a date range and choose to keep the Earliest or Latest record within each group.
• Case note: Keep only the most recent visit per customer (Store) within the selected month. Configure special filter type 2 as follows:
  • Group field: Account
  • Date field: Check-in Time
  • Selection: Latest

4.2.1.4 Column Headers and Order

• Column header names: Common Dimensions use the name set earlier and cannot be changed; other columns may be renamed.
• Reorder: drag columns; Common Dimensions must be first.
• Add/Hide columns: add calculated metrics or hide fields not needed for display.
• Case note: rename header to Fieldwork ID to improve clarity.

4.2.1.5 Composite Headers

• Composite headers: optional; operations include Add row, Delete row, Merge cells, Split cells, Compact mode, Undo, Adjust column width, Edit display name.
• Case note: Not required for this example; save directly.

4.2.2 Joined Result