Joined Report Case Study Analysis

I. Joining Different Data Ranges

1.1 Case 1: Monthly, Quarterly, and Annual Data Joining

• Core Requirement: View monthly, quarterly, and annual data together.
• Source Table Configuration: Three similar statistic tables with dimensions as product names, metrics as sales amount and sales quantity, and data ranges including the primary department of the person in charge. The date ranges are selected as this month, this quarter, and this year.

1.1.1 Configuration Process

1.1.1.1 Select Source Table and Join Type

• Source Table: Base tables configured with BI Reports and Statistic Charts, serving as the source for Joined Reports.
• Join Type: The method of joining each source table during the Joined Report creation.
  • 1. Left Join: Based on the left source table.
  • 1. Inner Join: Intersection.
  • 1. Outer Join: Union.
  • 1. Vertical Join: Merging all data rows.
• Case Explanation: The “Case 1-Source Table 1-Annual Sales” table contains the most data, with the most complete dimension values (product names). The quarterly and monthly data are included within it. In this situation, it is recommended to use “Left Join,” using the table with the most complete dimension values as the left source table, and the others as the right source tables.

1.1.1.2 Select Common Dimensions

• Common Dimensions: Used as the basis for joining multiple source tables, typically fields with the same meaning in each source table. Click on fields with the same meaning in each source table and add them to the right to form a set of common dimensions, which will be displayed as the grouping/dimension fields of the Joined Report, showing the renamed field names.
• Case Explanation: The dimensions of the three tables are all “Product Name,” and we want to display them as “Products.” Therefore, click on “Product Name” in Source Tables 1, 2, and 3, add them to the right common dimensions, rename them to “Products,” and click confirm.

1.1.1.3 Select Data Range

• Add Merged Data Range: Similar to common dimensions, click on the left to add to the right as a group. Click “Merge,” which means it will eventually be merged into a filter condition used in the data range of the Joined Report, displayed with the renamed field name. The default filter value can also be modified here.

• Add Data Range: Refers to filter conditions other than the common data range that need to be exposed in the data range after joining. Unadded filter conditions will not be displayed in the data range of the Joined Report and will act according to the default configuration of the source table.
• Case Explanation: Use the primary department of the person in charge as the common data range after joining, rename it to “Departments,” and apply it to all three source tables during filtering. Date filters are not displayed and are fixed according to the source table configuration to filter data by this year, this quarter, and this month.

1.1.1.4 Header Name and Order

• Header Name: The field name of the common dimension is displayed according to the settings in the second step and cannot be modified here. Other field names can be modified as needed.
• Adjust Order: Drag to adjust the order of columns, with common dimensions always at the front and not adjustable.
• Add/Hide Columns: You can add calculated metrics as new columns; you can hide columns that do not need to be displayed in the Joined Report.
• Case Explanation: Adjust the order of columns to display in the order of monthly, quarterly, and annual. Check to display totals, and the Joined Report will have a total row at the end.

1.1.1.5 Composite Header

• Composite Header: Displayed according to the adjusted header name and order from the previous step, you can add multiple layers of composite headers above.
• Operations: Add Row: Add a new row above; Delete Row: Delete the selected row; Merge Cells: Merge multiple cells selected by the mouse; Split Cells: Split merged cells back to original cells; Compact Mode: Set compact mode; Undo: Undo the previous step; Modify Column Width: Click the original header to modify column width; Modify Display Name: Click the added cell to modify the display name;
• Case Explanation: Add a row above, merge cells separately, and divide the header into monthly, quarterly, and annual composite headers based on time granularity.

1.1.2 Joined Report Result

1.2 Case 2: Joining Different Target Rules

• Core Requirement: View the completion status of different target rules together and calculate KPI comprehensively: Account visit weight 20%, Account order weight 30%, Account payment collection weight 50%.
• Source Table Configuration: Three target statistic charts with different target rules.

1.2.1 Configuration Process

1.2.1.1 Select Source Table and Join Type

• Source Table: Base tables configured with BI Reports and Statistic Charts, serving as the source for Joined Reports.
• Join Type: The method of joining each source table during the Joined Report creation.
  • 1. Left Join: Based on the left source table.
  • 1. Inner Join: Intersection.
  • 1. Outer Join: Union.
  • 1. Vertical Join: Merging all data rows.
• Case Explanation: The dimension value “Person in Charge” is completely consistent across source tables. In this case, the join type can be selected as Inner Join, and the intersection of the three source tables will still be the employees that need to be counted.

1.2.1.2 Select Common Dimensions

• Common Dimensions: Used as the basis for joining multiple source tables, typically fields with the same meaning in each source table. Click on fields with the same meaning in each source table and add them to the right to form a set of common dimensions, which will be displayed as the grouping/dimension fields of the Joined Report, showing the renamed field names.
• Case Explanation: The dimensions of the three tables are all “Person in Charge,” and the final dimension of the Joined Report is also “Person in Charge.” Therefore, click on “Person in Charge” in Source Tables 1, 2, and 3, add them to the right common dimensions, and click confirm.

1.2.1.3 Select Data Range

• Add Merged Data Range: Similar to common dimensions, click on the left to add to the right as a group. Click “Merge,” which means it will eventually be merged into a filter condition used in the data range of the Joined Report, displayed with the renamed field name. The default filter value can also be modified here.

• Add Data Range: Refers to filter conditions other than the common data range that need to be exposed in the data range after joining. Unadded filter conditions will not be displayed in the data range of the Joined Report and will act according to the default configuration of the source table.
• Case Explanation: Add two merged data ranges: Date and primary department as the common data range after joining, applying to all three source tables during filtering. Target rule filters are not displayed and are fixed according to the source table configuration to filter data by specified target rules.

1.2.1.4 Header Name and Order

• Header Name: The field name of the common dimension is displayed according to the settings in the second step and cannot be modified here. Other field names can be modified as needed.
• Adjust Order: Drag to adjust the order of columns, with common dimensions always at the front and not adjustable.
• Add/Hide Columns: You can add calculated metrics as new columns; you can hide columns that do not need to be displayed in the Joined Report.
• Case Explanation: Add a calculated metric, multiply the three target rules by weight, and drag it to the end for display.

1.2.1.5 Composite Header

• Composite Header: Displayed according to the adjusted header name and order from the previous step, you can add multiple layers of composite headers above.
• Operations: Add Row: Add a new row above; Delete Row: Delete the selected row; Merge Cells: Merge multiple cells selected by the mouse; Split Cells: Split merged cells back to original cells; Compact Mode: Set compact mode; Undo: Undo the previous step; Modify Column Width: Click the original header to modify column width; Modify Display Name: Click the added cell to modify the display name;
• Case Explanation: Add a row above, merge cells separately, and divide the header into composite headers based on target rules: Account payment collection, Account transaction, Account visit. Adjust each column width to 100 pixels and set compact mode for easy viewing on one screen.

1.2.2 Joined Report Result

II. Multi-Table Joining

2.1 Case 3: Joining Different Subjects

• Core Requirement: Use employee subject to count sales visit volume, number of employees with visits, total number of employees, and use account subject to count the number of visited accounts and total number of responsible accounts. Further calculate the fieldwork rate of sales personnel and the visit coverage rate of accounts.
• Source Table Configuration:

2.1.1 Configuration Process

2.1.1.1 Select Source Table and Join Type

• Source Table: Base tables configured with BI Reports and Statistic Charts, serving as the source for Joined Reports.
• Join Type: The method of joining each source table during the Joined Report creation.
  • 1. Left Join: Based on the left source table.
  • 1. Inner Join: Intersection.
  • 1. Outer Join: Union.
  • 1. Vertical Join: Merging all data rows.
• Case Explanation: The dimension value “Primary Department” in Source Table 1 and “Person in Charge Primary Department” in Source Table 2 have the same meaning. Since the source table has selected to display dimensions with a value of 0, the join type can be selected as Inner Join, and the intersection of the two source tables will still be the departments that need to be counted.

2.1.1.2 Select Common Dimensions

• Common Dimensions: Used as the basis for joining multiple source tables, typically fields with the same meaning in each source table. Click on fields with the same meaning in each source table and add them to the right to form a set of common dimensions, which will be displayed as the grouping/dimension fields of the Joined Report, showing the renamed field names.
• Case Explanation: The dimension value “Primary Department” in Source Table 1 and “Person in Charge Primary Department” in Source Table 2, and we want to display them as “Person in Charge Primary Department.” Therefore, click on “Primary Department” in Source Table 1 and “Person in Charge Primary Department” in Source Table 2, add them to the right common dimensions, rename them to “Person in Charge Primary Department,” and click confirm.

2.1.1.3 Select Data Range

• Add Merged Data Range: Similar to common dimensions, click on the left to add to the right as a group. Click “Merge,” which means it will eventually be merged into a filter condition used in the data range of the Joined Report, displayed with the renamed field name. The default filter value can also be modified here.

• Add Data Range: Refers to filter conditions other than the common data range that need to be exposed in the data range after joining. Unadded filter conditions will not be displayed in the data range of the Joined Report and will act according to the default configuration of the source table.
• Case Explanation: Use date and person in charge primary department as the common data range after joining, applying to both source tables.

2.1.1.4 Header Name and Order

• Header Name: The field name of the common dimension is displayed according to the settings in the second step and cannot be modified here. Other field names can be modified as needed.
• Adjust Order: Drag to adjust the order of columns, with common dimensions always at the front and not adjustable.
• Add/Hide Columns: You can add calculated metrics as new columns; you can hide columns that do not need to be displayed in the Joined Report.
• Case Explanation: Add two calculated metrics: Employee Fieldwork Rate = Number of Fieldwork / Total Number, Account Visit Coverage Rate = Number of Visited Accounts / Total Number of Responsible Accounts. Then set the metrics that do not need to be displayed in the Joined Report (only used for calculation) to hidden.

2.1.1.5 Composite Header

• Composite Header: Displayed according to the adjusted header name and order from the previous step, you can add multiple layers of composite headers above.
• Operations: Add Row: Add a new row above; Delete Row: Delete the selected row; Merge Cells: Merge multiple cells selected by the mouse; Split Cells: Split merged cells back to original cells; Compact Mode: Set compact mode; Undo: Undo the previous step; Modify Column Width: Click the original header to modify column width; Modify Display Name: Click the added cell to modify the display name;
• Case Explanation: The final header will not display hidden columns, no need to add composite headers, just save directly.

2.1.2 Joined Report Result

2.2 Case 4: Joining with No Relationship but Same Business Meaning

• Core Requirement: The account name on the account object refers to the doctor’s name, the superior account refers to the department where the doctor is located, the order object is associated with the account, and the doctor custom object has the same meaning as the doctor and department fields, but the two objects have no relationship. The expense object is associated with the doctor custom object, and it is hoped to count the order amount and expense reimbursement amount from the dimensions of department and doctor.
• Source Table Configuration:

2.2.1 Configuration Process

2.2.1.1 Select Source Table and Join Type

• Source Table: Base tables configured with BI Reports and Statistic Charts, serving as the source for Joined Reports.
• Join Type: The method of joining each source table during the Joined Report creation.
  • 1. Left Join: Based on the left source table.
  • 1. Inner Join: Intersection.
  • 1. Outer Join: Union.
  • 1. Vertical Join: Merging all data rows.
• Case Explanation: Some doctors (accounts) have orders but no reimbursements, some have reimbursements but no orders, and some have both. The final Joined Report needs to cover these scenarios, and if a doctor is counted in any source table, they should appear in the final Joined Report, meaning the source tables should be unioned. Therefore, the join type is selected as Outer Join.

2.2.1.2 Select Common Dimensions

• Common Dimensions: Used as the basis for joining multiple source tables, typically fields with the same meaning in each source table. Click on fields with the same meaning in each source table and add them to the right to form a set of common dimensions, which will be displayed as the grouping/dimension fields of the Joined Report, showing the renamed field names.
• Case Explanation: Add two sets of common dimensions in order:
  • First set of common dimensions: “Superior Account” and “Department” finally displayed as “Department.”
  • Second set of common dimensions: “Account Name” and “Doctor” finally displayed as “Doctor.”

2.2.1.3 Select Data Range

• Add Merged Data Range: Similar to common dimensions, click on the left to add to the right as a group. Click “Merge,” which means it will eventually be merged into a filter condition used in the data range of the Joined Report, displayed with the renamed field name. The default filter value can also be modified here.

• Add Data Range: Refers to filter conditions other than the common data range that need to be exposed in the data range after joining. Unadded filter conditions will not be displayed in the data range of the Joined Report and will act according to the default configuration of the source table.
• Case Explanation: In this case, there is no common data range, so there is no need to add a merged data range. There is a filter for the superior account in Source Table 1, and it is hoped to retain this filter after joining. Therefore, directly click to add the data range, select the filter condition, and confirm. In the final Joined Report, this condition will only apply to Source Table 1.

2.2.1.4 Header Name and Order

• Header Name: The field name of the common dimension is displayed according to the settings in the second step and cannot be modified here. Other field names can be modified as needed.
• Adjust Order: Drag to adjust the order of columns, with common dimensions always at the front and not adjustable.
• Add/Hide Columns: You can add calculated metrics as new columns; you can hide columns that do not need to be displayed in the Joined Report.
• Case Explanation: When there are multiple sets of common dimensions, all common dimensions will be placed at the front. Other columns can be dragged to adjust the order and renamed. Check to display totals and subtotals, meaning that after joining, each department and doctor group will display the corresponding subtotal value, and there will be a total value at the end.

2.2.1.5 Composite Header

• Composite Header: Displayed according to the adjusted header name and order from the previous step, you can add multiple layers of composite headers above.
• Operations: Add Row: Add a new row above; Delete Row: Delete the selected row; Merge Cells: Merge multiple cells selected by the mouse; Split Cells: Split merged cells back to original cells; Compact Mode: Set compact mode; Undo: Undo the previous step; Modify Column Width: Click the original header to modify column width; Modify Display Name: Click the added cell to modify the display name;
• Case Explanation: Adjust each column width to 100 pixels and set compact mode for easy viewing on one screen.

2.2.2 Joined Report Result

III. Vertical Joining

3.1 Case 5: Vertical Joining

• Core Requirement: The expense reimbursement object is used for daily expense document submission, and a separate 618 event reimbursement object was created for convenience during the 618 event. The field configuration is the same as the expense reimbursement object. At the end of the year, it is hoped to count the overall expense data together.
• Source Table Configuration:

3.1.1 Configuration Process

3.1.1.1 Select Source Table and Join Type

• Source Table: Base tables configured with BI Reports and Statistic Charts, serving as the source for Joined Reports.
• Join Type: The method of joining each source table during the Joined Report creation.
  • 1. Left Join: Based on the left source table.
  • 1. Inner Join: Intersection.
  • 1. Outer Join: Union.
  • 1. Vertical Join: Merging all data rows.
• Case Explanation: The configuration method of the two source tables (number of header fields and order of grouping fields) needs to be the same, but they are taken from different objects. In this case, the join type needs to be selected as Vertical Join, accumulating the data of the two source tables according to a unified configuration.

3.1.1.2 Select Common Dimensions

• Common Dimensions: Used as the basis for joining multiple source tables, typically fields with the same meaning in each source table. Click on fields with the same meaning in each source table and add them to the right to form a set of common dimensions, which will be displayed as the grouping/dimension fields of the Joined Report, showing the renamed field names.
• Case Explanation: When the join type is Vertical Join, there is no need to specify common dimensions, proceed directly to the next step.

3.1.1.3 Select Data Range

• Add Merged Data Range: Similar to common dimensions, click on the left to add to the right as a group. Click “Merge,” which means it will eventually be merged into a filter condition used in the data range of the Joined Report, displayed with the renamed field name. The default filter value can also be modified here.

• Add Data Range: Refers to filter conditions other than the common data range that need to be exposed in the data range after joining. Unadded filter conditions will not be displayed in the data range of the Joined Report and will act according to the default configuration of the source table.
• Case Explanation: When vertically joining, the method of adding data ranges is similar to other join types. In this example, creation time is used as the common data range.

3.1.1.4 Header Name and Order

• Header Name: The field name of the common dimension is displayed according to the settings in the second step and cannot be modified here. Other field names can be modified as needed.
• Adjust Order: Drag to adjust the order of columns, with common dimensions always at the front and not adjustable.
• Add/Hide Columns: You can add calculated metrics as new columns; you can hide columns that do not need to be displayed in the Joined Report.
• Case Explanation: When vertically joining, the order of columns is strictly arranged according to the source table order and cannot be modified. The column name will default to the header name of the first source table, and can be modified to the final desired display name as needed.

3.1.1.5 Composite Header

• Composite Header: Displayed according to the adjusted header name and order from the previous step, you can add multiple layers of composite headers above.
• Operations: Add Row: Add a new row above; Delete Row: Delete the selected row; Merge Cells: Merge multiple cells selected by the mouse; Split Cells: Split merged cells back to original cells; Compact Mode: Set compact mode; Undo: Undo the previous step; Modify Column Width: Click the original header to modify column width; Modify Display Name: Click the added cell to modify the display name;
• Case Explanation: This case does not require adding composite headers, just save directly.

3.1.2 Joined Report Result

IV. Special Filter Conditions Joining

4.1 Case 6: Special Filter - Field Comparison

• Core Requirement: Sometimes we need to apply special filter conditions, not filtering based on the value of a field, but comparing whether the values of two fields are the same. In this case, in the existing Leads conversion report, it is hoped to add a comparison of whether the Leads owner and Account owner are the same person.
• Source Table Configuration:

4.1.1 Configuration Process

4.1.1.1 Select Source Table and Join Type

• Source Table: Base tables configured with BI Reports and Statistic Charts, serving as the source for Joined Reports.
• Join Type: The method of joining each source table during the Joined Report creation.
  • 1. Left Join: Based on the left source table.
  • 1. Inner Join: Intersection.
  • 1. Outer Join: Union.
  • 1. Vertical Join: Merging all data rows.
• Case Explanation: When there is only one source table, there is no need to specifically choose the join type, just use the default Left Join.

4.1.1.2 Select Common Dimensions

• Common Dimensions: Used as the basis for joining multiple source tables, typically fields with the same meaning in each source table. Click on fields with the same meaning in each source table and add them to the right to form a set of common dimensions, which will be displayed as the grouping/dimension fields of the Joined Report, showing the renamed field names.
• Case Explanation: When the join type is Vertical Join, there is no need to specify common dimensions, proceed directly to the next step.

4.1.1.3 Select Data Range

• Add Merged Data Range: Similar to common dimensions, click on the left to add to the right as a group. Click “Merge,” which means it will eventually be merged into a filter condition used in the data range of the Joined Report, displayed with the renamed field name. The default filter value can also be modified here.

• Add Data Range: Refers to filter conditions other than the common data range that need to be exposed in the data range after joining. Unadded filter conditions will not be displayed in the data range of the Joined Report and will act according to the default configuration of the source table.
• Special Filter Condition 1: Specify two fields to compare, specify the comparison method: equal, not equal, and data that does not meet the conditions will be filtered out.
• Case Explanation: Add conversion time and person in charge primary department as data ranges, and fields not added will be filtered according to the condition values configured in the source table.

4.1.1.4 Header Name and Order

• Header Name: The field name of the common dimension is displayed according to the settings in the second step and cannot be modified here. Other field names can be modified as needed.
• Adjust Order: Drag to adjust the order of columns, with common dimensions always at the front and not adjustable.
• Add/Hide Columns: You can add calculated metrics as new columns; you can hide columns that do not need to be displayed in the Joined Report.
• Case Explanation: This case does not require adding composite headers, just save directly.

4.1.1.5 Composite Header

• Composite Header: Displayed according to the adjusted header name and order from the previous step, you can add multiple layers of composite headers above.
• Operations: Add Row: Add a new row above; Delete Row: Delete the selected row; Merge Cells: Merge multiple cells selected by the mouse; Split Cells: Split merged cells back to original cells; Compact Mode: Set compact mode; Undo: Undo the previous step; Modify Column Width: Click the original header to modify column width; Modify Display Name: Click the added cell to modify the display name;
• Case Explanation: This case does not require adding composite headers, just save directly.

4.1.2 Joined Report Result

4.2 Case 7: Special Filter - Most Recent

• Core Requirement: Another special filter is to retain only one record within the same group of multiple records. For example, when visiting stores, a store may be visited multiple times, and the Joined Report platform can be used to retain only the most recent visit for each store within the selected time period.
• Source Table Configuration:

4.2.1 Configuration Process

4.2.1.1 Select Source Table and Join Type

• Source Table: Base tables configured with BI Reports and Statistic Charts, serving as the source for Joined Reports.
• Join Type: The method of joining each source table during the Joined Report creation.
  • 1. Left Join: Based on the left source table.
  • 1. Inner Join: Intersection.
  • 1. Outer Join: Union.
  • 1. Vertical Join: Merging all data rows.
• Case Explanation: When there is only one source table, there is no need to specifically choose the join type, just use the default Left Join.

4.2.1.2 Select Common Dimensions

• Common Dimensions: Used as the basis for joining multiple source tables, typically fields with the same meaning in each source table. Click on fields with the same meaning in each source table and add them to the right to form a set of common dimensions, which will be displayed as the grouping/dimension fields of the Joined Report, showing the renamed field names.
• Case Explanation: When the join type is Left Join, there is no need to specify common dimensions, proceed directly to the next step.

4.2.1.3 Select Data Range

• Add Merged Data Range: Similar to common dimensions, click on the left to add to the right as a group. Click “Merge,” which means it will eventually be merged into a filter condition used in the data range of the Joined Report, displayed with the renamed field name. The default filter value can also be modified here.

• Add Data Range: Refers to filter conditions other than the common data range that need to be exposed in the data range after joining. Unadded filter conditions will not be displayed in the data range of the Joined Report and will act according to the default configuration of the source table.
• Special Filter Condition 2: Specify grouping fields, filter by date range, and retain the earliest/latest record within each group.
• Case Explanation: Retain only the most recent visit data within this month for each account (store). Set Special Filter Condition 2: Grouping Field: Account Date Field: Check-in Time Value Method: Most Recent

4.2.1.4 Header Name and Order

• Header Name: The field name of the common dimension is displayed according to the settings in the second step and cannot be modified here. Other field names can be modified as needed.
• Adjust Order: Drag to adjust the order of columns, with common dimensions always at the front and not adjustable.
• Add/Hide Columns: You can add calculated metrics as new columns; you can hide columns that do not need to be displayed in the Joined Report.
• Case Explanation: Modify the header name to Fieldwork ID to make the information more intuitive and clear.

4.2.1.5 Composite Header

• Composite Header: Displayed according to the adjusted header name and order from the previous step, you can add multiple layers of composite headers above.
• Operations: Add Row: Add a new row above; Delete Row: Delete the selected row; Merge Cells: Merge multiple cells selected by the mouse; Split Cells: Split merged cells back to original cells; Compact Mode: Set compact mode; Undo: Undo the previous step; Modify Column Width: Click the original header to modify column width; Modify Display Name: Click the added cell to modify the display name;
• Case Explanation: This case does not require adding composite headers, just save directly.

4.2.2 Joined Report Result