Joined report example

Before you begin

[!IMPORTANT] Confirm that you have access to the relevant BI pages. For create, edit, or management operations, you also need the corresponding administrative permissions.

Join different data ranges

Case 1: Join monthly, quarterly, and yearly data

• Core need: view monthly, quarterly, and yearly data together.
• Source report setup: three similar statistical reports. The dimension for each is Product Name, the metrics are Sales Amount and Sales Quantity, and every data range includes Owner’s Primary Department. In the data range, the date is set to This Month, This Quarter, and This Year respectively.

Configuration flow

Select source reports and join type

• Source reports: the base reports configured from BI reports or charts that serve as the sources of the joined report.
• Join type: how each source report is combined when joining.
  • 1 Left join: left join, based on the left source report.
  • 2 Inner join: inner join, takes the intersection.
  • 3 Outer join: outer join, takes the union.
  • 4 Vertical join: merges all data rows.
• Case notes: the table “Case 1 - Source 1 - Yearly Sales” holds the most data and the most complete set of dimension values (Product Name). The quarterly and monthly data is included within it. In this situation, use a left join and put the source report with the most complete dimension values on the left. The remaining reports serve as right source reports.

Select common dimensions

• Common dimensions: the basis for joining multiple source reports. In general, fields with the same meaning across each source report serve as grouping or dimension fields. Click the fields with the same meaning in each source report and add them to the right to form one group of common dimensions. They become the grouping or dimension fields of the joined report and are displayed under the renamed field name.
• Case notes: the dimension of all three reports is Product Name, and you want it to display as Product. Click Product Name in source reports 1, 2, and 3, add them to the common dimensions on the right, rename to Product, and click OK.

Select data ranges

• Add merged data range: the shared data ranges of each source report work like common dimensions. Click items on the left to add them to the right as one group. Click Merge. This means they are merged into a single filter condition used in the data range of the joined report. They are displayed under the renamed field name. The default value of the filter can also be changed here.

• Add data range: refers to filter conditions other than the shared data ranges that should be exposed in the data range of the joined report. Filter conditions that are not added will not appear in the joined report data range and will keep working with the default configuration of the source report.
• Case notes: add Owner’s Primary Department as the shared data range of the joined report and rename it to Department. When filtering, it applies to all three source reports at once. The date filters are not displayed; the data stays filtered by This Year, This Quarter, and This Month according to the source report configuration.

Header names and order

• Header names: the field names of the common dimensions follow the settings from step two and cannot be changed here. All other field names can be renamed as needed.
• Adjust order: drag to reorder the columns. Common dimensions must remain at the front and cannot be moved.
• Add or hide columns: you can add calculated metrics as new columns, and you can hide columns that do not need to appear in the joined report.
• Case notes: adjust the column order so the data displays by month, quarter, and year. Select Show Total so the joined report ends with a total row.

Composite headers

• Composite headers: build on the header names and order from the previous step, and let you add multiple layers of composite headers above them.
• Operations: Add row: insert a row above; Delete row: remove the selected row; Merge cells: drag to select multiple cells and merge them; Split cells: split merged cells back to the original cells; Compact mode: toggle compact mode; Undo: revert to the previous step; Adjust column width: click an original header to change its width; Rename display: click an added cell to change its display name;
• Case notes: add a row above, merge cells respectively, and group the headers by time granularity into This Month, This Quarter, and This Year as merged headers.

Joined report result

Case 2: Join different Goal Rules

• Core need: view the completion of different Goal Rules together and compute a composite KPI — Customer Visit weighted 20%, Customer Order weighted 30%, Customer Payment weighted 50%.
• Source report setup: three target charts with different Goal Rules.

Configuration flow

Select source reports and join type

• Source reports: the base reports configured from BI reports or charts that serve as the sources of the joined report.
• Join type: how each source report is combined when joining.
  • 1 Left join: left join, based on the left source report.
  • 2 Inner join: inner join, takes the intersection.
  • 3 Outer join: outer join, takes the union.
  • 4 Vertical join: merges all data rows.
• Case notes: the dimension value Owner is identical across every source report, so the join type can be inner join. After the three source reports are intersected, the employees that need to be counted are still included.

Select common dimensions

• Common dimensions: the basis for joining multiple source reports. In general, fields with the same meaning across each source report serve as grouping or dimension fields. Click the fields with the same meaning in each source report and add them to the right to form one group of common dimensions. They become the grouping or dimension fields of the joined report and are displayed under the renamed field name.
• Case notes: the dimension of all three reports is Owner, and the final joined report dimension is also Owner. Click Owner in source reports 1, 2, and 3, add them to the common dimensions on the right, and click OK.

Select data ranges

• Add merged data range: the shared data ranges of each source report work like common dimensions. Click items on the left to add them to the right as one group. Click Merge. This means they are merged into a single filter condition used in the data range of the joined report. They are displayed under the renamed field name. The default value of the filter can also be changed here.
• Add data range: refers to filter conditions other than the shared data ranges that should be exposed in the data range of the joined report. Filter conditions that are not added will not appear in the joined report data range and will keep working with the default configuration of the source report.

• Case notes: add two groups of merged data ranges — Date and Primary Department — as the shared data ranges of the joined report. When filtering, they apply to all three source reports at once. The Goal Rule filters are not displayed; the data stays filtered by the specified Goal Rules according to the source report configuration.

Header names and order

• Header names: the field names of the common dimensions follow the settings from step two and cannot be changed here. All other field names can be renamed as needed.
• Adjust order: drag to reorder the columns. Common dimensions must remain at the front and cannot be moved.
• Add or hide columns: you can add calculated metrics as new columns, and you can hide columns that do not need to appear in the joined report.
• Case notes: add a calculated metric that multiplies the three Goal Rules by their weights, and drag it to the end to display last.

Composite headers

• Composite headers: build on the header names and order from the previous step, and let you add multiple layers of composite headers above them.
• Operations: Add row: insert a row above; Delete row: remove the selected row; Merge cells: drag to select multiple cells and merge them; Split cells: split merged cells back to the original cells; Compact mode: toggle compact mode; Undo: revert to the previous step; Adjust column width: click an original header to change its width; Rename display: click an added cell to change its display name;
• Case notes: add a row above, merge cells respectively, and group the headers by Goal Rule into Customer Payment, Customer Order, and Customer Visit as merged headers. Adjust each column width to 100 pixels and turn on compact mode for easy viewing on one screen.

Joined report result

Multi-table join

Case 3: Join different themes

• Core need: use the Employee theme to count sales visits, the number of employees who made visits, and the total number of employees, and use the Customer theme to count the number of visited customers and the Account Total Number of customers owned. Then compute the proportion of sales staff who go out on visits, and the visit coverage of customers.
• Source report setup:

Configuration flow

Select source reports and join type

• Source reports: the base reports configured from BI reports or charts that serve as the sources of the joined report.
• Join type: how each source report is combined when joining.
  • 1 Left join: left join, based on the left source report.
  • 2 Inner join: inner join, takes the intersection.
  • 3 Outer join: outer join, takes the union.
  • 4 Vertical join: merges all data rows.
• Case notes: the dimension value Primary Department in source report 1 and the dimension value Owner’s Primary Department in source report 2 mean the same thing. Because the source reports have Show dimensions with zero metrics selected, the join type here can be inner join. After the two source reports are intersected, the departments that need to be counted are still included.

Select common dimensions

• Common dimensions: the basis for joining multiple source reports. In general, fields with the same meaning across each source report serve as grouping or dimension fields. Click the fields with the same meaning in each source report and add them to the right to form one group of common dimensions. They become the grouping or dimension fields of the joined report and are displayed under the renamed field name.
• Case notes: the dimension value Primary Department in source report 1 and the dimension value Owner’s Primary Department in source report 2 mean the same thing. To display it as Owner’s Primary Department, click Primary Department in source report 1 and Owner’s Primary Department in source report 2. Add them to the common dimensions on the right, rename to Owner’s Primary Department, and click OK.

Select data ranges

• Add merged data range: the shared data ranges of each source report work like common dimensions. Click items on the left to add them to the right as one group. Click Merge. This means they are merged into a single filter condition used in the data range of the joined report. They are displayed under the renamed field name. The default value of the filter can also be changed here.

• Add data range: refers to filter conditions other than the shared data ranges that should be exposed in the data range of the joined report. Filter conditions that are not added will not appear in the joined report data range and will keep working with the default configuration of the source report.
• Case notes: add Date and Owner’s Primary Department as the shared data ranges of the joined report. They apply to both source reports at once.

Header names and order

• Header names: the field names of the common dimensions follow the settings from step two and cannot be changed here. All other field names can be renamed as needed.
• Adjust order: drag to reorder the columns. Common dimensions must remain at the front and cannot be moved.
• Add or hide columns: you can add calculated metrics as new columns, and you can hide columns that do not need to appear in the joined report.
• Case notes: add two calculated metrics — Employee Outing Rate = employees on visits / total employees, and Customer Visit Coverage = visited customers / total customers owned. Then hide the metrics that are only used for calculation and should not appear in the final joined report.

Composite headers

• Composite headers: build on the header names and order from the previous step, and let you add multiple layers of composite headers above them.
• Operations: Add row: insert a row above; Delete row: remove the selected row; Merge cells: drag to select multiple cells and merge them; Split cells: split merged cells back to the original cells; Compact mode: toggle compact mode; Undo: revert to the previous step; Adjust column width: click an original header to change its width; Rename display: click an added cell to change its display name;
• Case notes: hidden columns do not appear in the final headers, so no composite headers are needed. Save directly.

Joined report result

Case 4: Join unrelated sources with the same business meaning

• Core need: on the Account object, Account Name refers to the doctor’s name and the Upper-Level Account refers to the department the doctor belongs to. The Order object is linked to Customer. On the Doctor custom object, the doctor and department fields mean the same thing, but the two objects have no relationship. The Expense object is linked to the Doctor custom object. The goal is to count order amount and expense reimbursement amount by department and doctor.
• Source report setup:

Configuration flow

Select source reports and join type

• Source reports: the base reports configured from BI reports or charts that serve as the sources of the joined report.
• Join type: how each source report is combined when joining.
  • 1 Left join: left join, based on the left source report.
  • 2 Inner join: inner join, takes the intersection.
  • 3 Outer join: outer join, takes the union.
  • 4 Vertical join: merges all data rows.
• Case notes: some doctors (customers) have orders but no reimbursements, some have reimbursements but no orders, and some have both. The final joined report needs to cover all these cases. As long as a doctor is counted in any source report, that doctor must appear in the final joined report — that is, the union of all source reports. So the join type is outer join.

Select common dimensions

• Common dimensions: the basis for joining multiple source reports. In general, fields with the same meaning across each source report serve as grouping or dimension fields. Click the fields with the same meaning in each source report and add them to the right to form one group of common dimensions. They become the grouping or dimension fields of the joined report and are displayed under the renamed field name.
• Case notes: add two groups of common dimensions in order.
  • First group: Upper-Level Account and Department, displayed as Department.
  • Second group: Account Name and Doctor, displayed as Doctor.

Select data ranges

• Add merged data range: the shared data ranges of each source report work like common dimensions. Click items on the left to add them to the right as one group. Click Merge. This means they are merged into a single filter condition used in the data range of the joined report. They are displayed under the renamed field name. The default value of the filter can also be changed here.
• Add data range: refers to filter conditions other than the shared data ranges that should be exposed in the data range of the joined report. Filter conditions that are not added will not appear in the joined report data range and will keep working with the default configuration of the source report.

• Case notes: this case has no shared data range, so no merged data range is needed. Source report 1 has a filter on the Upper-Level Account that should be retained after joining, so click Add Data Range directly. Select that filter condition and click OK. In the final joined report, that filter applies only to source report 1.

Header names and order

• Header names: the field names of the common dimensions follow the settings from step two and cannot be changed here. All other field names can be renamed as needed.
• Adjust order: drag to reorder the columns. Common dimensions must remain at the front and cannot be moved.
• Add or hide columns: you can add calculated metrics as new columns, and you can hide columns that do not need to appear in the joined report.
• Case notes: when there are multiple groups of common dimensions, all common dimensions are placed at the front. The remaining columns can be dragged to reorder and renamed. Select Show Total and Show Subtotal. This means that after joining, each department and each doctor group shows its corresponding subtotal, with a total row at the end.

Composite headers

• Composite headers: build on the header names and order from the previous step, and let you add multiple layers of composite headers above them.
• Operations: Add row: insert a row above; Delete row: remove the selected row; Merge cells: drag to select multiple cells and merge them; Split cells: split merged cells back to the original cells; Compact mode: toggle compact mode; Undo: revert to the previous step; Adjust column width: click an original header to change its width; Rename display: click an added cell to change its display name;
• Case notes: adjust each column width to 100 pixels and turn on compact mode for easy viewing on one screen.

Joined report result

Vertical join

Case 5: Vertical join

• Core need: the Expense Reimbursement object is used for daily expense submissions. During the 618 campaign, a separate 618 Campaign Reimbursement object was created for business convenience. Its fields are configured the same way as the Expense Reimbursement object. At year-end, the goal is to combine the two types of expenses for overall statistics.
• Source report setup:

Configuration flow

Select source reports and join type

• Source reports: the base reports configured from BI reports or charts that serve as the sources of the joined report.
• Join type: how each source report is combined when joining.
  • 1 Left join: left join, based on the left source report.
  • 2 Inner join: inner join, takes the intersection.
  • 3 Outer join: outer join, takes the union.
  • 4 Vertical join: merges all data rows.
• Case notes: the two source reports must have the same configuration (the same number of header fields and the same order of grouping fields), only from different objects. The join type here is vertical join, which accumulates the data of both source reports under a unified configuration.

Select common dimensions

• Common dimensions: the basis for joining multiple source reports. In general, fields with the same meaning across each source report serve as grouping or dimension fields. Click the fields with the same meaning in each source report and add them to the right to form one group of common dimensions. They become the grouping or dimension fields of the joined report and are displayed under the renamed field name.
• Case notes: when the join type is vertical join, you do not need to specify common dimensions. Go directly to the next step.

Select data ranges

• Add merged data range: the shared data ranges of each source report work like common dimensions. Click items on the left to add them to the right as one group. Click Merge. This means they are merged into a single filter condition used in the data range of the joined report. They are displayed under the renamed field name. The default value of the filter can also be changed here.
• Add data range: refers to filter conditions other than the shared data ranges that should be exposed in the data range of the joined report. Filter conditions that are not added will not appear in the joined report data range and will keep working with the default configuration of the source report.
• Case notes: for a vertical join, data ranges are added in the same way as for other join types. In this example, Created On is used as the shared data range.

Header names and order

• Header names: the field names of the common dimensions follow the settings from step two and cannot be changed here. All other field names can be renamed as needed.
• Adjust order: drag to reorder the columns. Common dimensions must remain at the front and cannot be moved.
• Add or hide columns: you can add calculated metrics as new columns, and you can hide columns that do not need to appear in the joined report.
• Case notes: for a vertical join, the column order strictly follows the source report order and cannot be changed. The column names default to the header names of the first source report. Rename them as needed to the final display names.

Composite headers

• Composite headers: build on the header names and order from the previous step, and let you add multiple layers of composite headers above them.
• Operations: Add row: insert a row above; Delete row: remove the selected row; Merge cells: drag to select multiple cells and merge them; Split cells: split merged cells back to the original cells; Compact mode: toggle compact mode; Undo: revert to the previous step; Adjust column width: click an original header to change its width; Rename display: click an added cell to change its display name;
• Case notes: this case does not need composite headers. Save directly.

Joined report result

Special filter joins

Case 6: Special filter — field comparison

• Core need: sometimes you need a special filter that does not select what a field value is, but compares whether the values of two fields are equal. In this case, in an existing lead conversion report, add a comparison of whether the lead owner and the customer owner are the same person.
• Source report setup:

Configuration flow

Select source reports and join type

• Source reports: the base reports configured from BI reports or charts that serve as the sources of the joined report.
• Join type: how each source report is combined when joining.
  • 1 Left join: left join, based on the left source report.
  • 2 Inner join: inner join, takes the intersection.
  • 3 Outer join: outer join, takes the union.
  • 4 Vertical join: merges all data rows.
• Case notes: when there is only one source report, you do not need to pick a join type. The default left join is used.

Select common dimensions

• Common dimensions: the basis for joining multiple source reports. In general, fields with the same meaning across each source report serve as grouping or dimension fields. Click the fields with the same meaning in each source report and add them to the right to form one group of common dimensions. They become the grouping or dimension fields of the joined report and are displayed under the renamed field name.
• Case notes: when the join type is vertical join, you do not need to specify common dimensions. Go directly to the next step.

Select data ranges

• Add merged data range: the shared data ranges of each source report work like common dimensions. Click items on the left to add them to the right as one group. Click Merge. This means they are merged into a single filter condition used in the data range of the joined report. They are displayed under the renamed field name. The default value of the filter can also be changed here.
• Add data range: refers to filter conditions other than the shared data ranges that should be exposed in the data range of the joined report. Filter conditions that are not added will not appear in the joined report data range and will keep working with the default configuration of the source report.
• Special filter condition 1: specify the two fields to compare and the comparison method — equal to or not equal to. Data that does not meet the condition is filtered out.
• Case notes: add Conversion Time and Owner’s Primary Department as data ranges. Fields that are not added are filtered by the default condition values of the source report.

Header names and order

• Header names: the field names of the common dimensions follow the settings from step two and cannot be changed here. All other field names can be renamed as needed.
• Adjust order: drag to reorder the columns. Common dimensions must remain at the front and cannot be moved.
• Add or hide columns: you can add calculated metrics as new columns, and you can hide columns that do not need to appear in the joined report.
• Case notes: this case does not need composite headers. Save directly.

Composite headers

• Composite headers: build on the header names and order from the previous step, and let you add multiple layers of composite headers above them.
• Operations: Add row: insert a row above; Delete row: remove the selected row; Merge cells: drag to select multiple cells and merge them; Split cells: split merged cells back to the original cells; Compact mode: toggle compact mode; Undo: revert to the previous step; Adjust column width: click an original header to change its width; Rename display: click an added cell to change its display name;
• Case notes: this case does not need composite headers. Save directly.

Joined report result

Case 7: Special filter — most recent

• Core need: another kind of special filter keeps only one row among multiple rows in the same group. For example, when visiting stores, one store may be visited multiple times. The joined report platform can keep only the most recent visit for each store within the selected time range as needed.
• Source report setup:

Configuration flow

Select source reports and join type

• Source reports: the base reports configured from BI reports or charts that serve as the sources of the joined report.
• Join type: how each source report is combined when joining.
  • 1 Left join: left join, based on the left source report.
  • 2 Inner join: inner join, takes the intersection.
  • 3 Outer join: outer join, takes the union.
  • 4 Vertical join: merges all data rows.
• Case notes: when there is only one source report, you do not need to pick a join type. The default left join is used.

Select common dimensions

• Common dimensions: the basis for joining multiple source reports. In general, fields with the same meaning across each source report serve as grouping or dimension fields. Click the fields with the same meaning in each source report and add them to the right to form one group of common dimensions. They become the grouping or dimension fields of the joined report and are displayed under the renamed field name.
• Case notes: when the join type is left join, you do not need to specify common dimensions. Go directly to the next step.

Select data ranges

• Add merged data range: the shared data ranges of each source report work like common dimensions. Click items on the left to add them to the right as one group. Click Merge. This means they are merged into a single filter condition used in the data range of the joined report. They are displayed under the renamed field name. The default value of the filter can also be changed here.
• Add data range: refers to filter conditions other than the shared data ranges that should be exposed in the data range of the joined report. Filter conditions that are not added will not appear in the joined report data range and will keep working with the default configuration of the source report.
• Special filter condition 2: specify the grouping field, filter by a date range, and keep the earliest or most recent row in each group.
• Case notes: for each customer (store), keep only the most recent visit within this month. Set special filter condition 2: Grouping field: Customer Date field: Check-in Time Take: Most recent

Header names and order

• Header names: the field names of the common dimensions follow the settings from step two and cannot be changed here. All other field names can be renamed as needed.
• Adjust order: drag to reorder the columns. Common dimensions must remain at the front and cannot be moved.
• Add or hide columns: you can add calculated metrics as new columns, and you can hide columns that do not need to appear in the joined report.
• Case notes: rename the header to Fieldwork No. to make the information clearer and more intuitive.

Composite headers

• Composite headers: build on the header names and order from the previous step, and let you add multiple layers of composite headers above them.
• Operations: Add row: insert a row above; Delete row: remove the selected row; Merge cells: drag to select multiple cells and merge them; Split cells: split merged cells back to the original cells; Compact mode: toggle compact mode; Undo: revert to the previous step; Adjust column width: click an original header to change its width; Rename display: click an added cell to change its display name;
• Case notes: this case does not need composite headers. Save directly.

Joined report result

Verify the result

Related topics

• Create a joined report
• Joined report reference
• View and use joined reports