Create and run multi-template reports

Overview

Multi-template reports allow you to combine separate report templates into a single report, increasing the visibility of business performance while enhancing operational efficiency.

Things to know

• You can currently combine only two templates. 

• You can’t create multi-template reports from Legacy reports or extended reports. Most extended reports are labeled with “EXT” at the end of the name. 

• When you create multi-template reports, you join two templates through a related ID-based column called a join entity. You can only join templates that share join entities. For example, you can join the Invoices and Jobs templates because both templates contain the same ID-based Customer ID column. See the Joins through data fields for multi-template reports section for a list of all join entities.  

• There are a few instances where joins aren’t available:

  • The Join Template button is disabled when creating a report. This means the selected template has no columns defined as joinable or no other templates with a matching joinable column. 

  • The template is unavailable as a second template option for joining. In this case, you can’t join the initial template with the second one you selected. This occurs when the first template has one or more joinable columns, but none of these columns are present in the second template you want to join. 

  • The Customers and Customer Memberships templates tend to have many rows when running the report, so they may not run when attempting to join them to another template. 

• You may get unintended results in your reports when the join entities don’t match 100% in the templates you want to join. For example, if you’re joining two templates using the Item BU ID join entity. If in Template A, the Item BU ID is a 1-to-1 match, and in Template B, the Item BU ID is a 1-to-many match, the results may not be accurate. If this happens, you can change the join entity on the report to get a 1-to-1 match on your data.  

Use cases

• Creating a multi-template report to join the Jobs and Invoices templates lets you view job-related information, such as costs, alongside the invoice status.

• Creating a multi-template report to join the Technician Performance and Business Performance templates lets you see how well your technicians performed on jobs in each business unit. 

• Creating a multi-template report to join the Jobs and Equipment templates lets you see what equipment you keep visiting during a specific period. Additionally, you can see how many customers you’ve visited over a certain period have the same types of equipment. 

Create multi-template reports

1. Go to the navigation bar and click Reports. 

2. In the All Reports section, click Create Report.

3. On the Create Report screen that opens, choose a report type to see related templates.
   

4. Under Select a report template, click a template for your report.
   

5. Under Columns to be displayed in the report, select which columns you want to include.

   Note: You can always add and remove columns later.

   
   

6. If you’re unsure which columns you want to include in your report, use the Reporting Dictionary to search for appropriate data fields.

7. When you’re done selecting columns, click Join Template.
   

8. On the Join New Template screen that opens, in the Select another template section, click the Available templates dropdown to select a template to join with your first template.
   

   Note: Not all templates are joinable. See Joins through data fields for multi-template reports for more.

9. Select which columns you want to include in your report.
   

10. When you’re done selecting columns, click Next.

11. In the Set Details screen that opens, enter the name of your report in the Name field. 

12. From the Category dropdown, select a category where you want your report to be listed. 

13. (Optional) In the Description field, enter a report description.
    

14. When finished, click Save. 

You can see the report you created. 

Run and filter multi-template reports

1. Click the name of the report you want to run.

   Tip: Enter the first few letters of the report title in the search bar to filter the list of reports.
   

2. With multi-template reports, you must filter each template differently. Filters applied to each reporting template apply to that reporting template specifically. For example, if a multi-template report contains the Jobs and Invoices templates, you can filter the Jobs data by Job Completion Date while filtering the Invoices data by Invoice Date. In the default view of multi-template reports, there are two levels for filtering: common filters and template-specific filters. 

   1. Common filters are filters that are available in both the original report templates. For example, many reports include a Business Unit or a Date filter. These are common filters.
      

   2. Template-specific filters are top-level filters that only one of the two templates had originally. For example, in a report that combines the Jobs and Invoices templates, you can filter the Jobs columns by Jobs with Next Appt Start Date and also filter the Invoices columns by Last Paid On Date. In this example, the Jobs template-specific filter you select would only affect columns from the Jobs report template. The Invoices template-specific filter you select would only affect the columns from the Invoices report template.
      

3. You can also separate the filtering for each of the joined templates. To separate filtering, click Independent Filters and set the filters you wish. Using the Independent Filters toggle lets you set individual filters for each template instead of having the common and template-specific filter default view.
   

4. Once you have set your filters, click Run Report. 

Read report results

Your report results are displayed in a table. Some things to know about multi-template reports: 

• You can use the bottom scroll bar to view additional columns, or you can use the side scroll bar to view additional rows.

• You can drill down in reports to get additional details. For more information, see Drill down in reports.

• You can edit the columns on multi-template reports and change the column you join the reports through. See Edit columns and Join Via column for more. 

• You can change the join type and swap the template positions. See Change join type and swap positions for more. 

• You can customize your report's display and add column filters to narrow your results. For more information, see Filter and arrange report.

• You can export your report results to use in spreadsheets and other documents. For more information see Export a report.

• You may get unintended results in your reports when the join entities don’t match 100% in the templates you want to join. For example, if you’re joining two templates using the Item BU ID join entity. If in Template A, the Item BU ID is a 1-to-1 match, and in Template B, the Item BU ID is a 1-to-many match, the results may not be accurate. 

Edit columns and Join via column

With multi-template reports, you can:

• Edit the columns from both templates you want to include in your report results. 

• Edit the column you want to use to join the templates.

Edit columns 

1. Click on the name of the report you want to run. 

2. Click Edit Columns. 

3. On the Edit Columns screen that opens, check the box next to the columns you want to display in your report results.

   Tip: The columns for both report templates appear on the Edit Columns screen. Click next to the template name to hide the columns from that template after selecting the columns you want to display in your report results from that template.

   

4. When finished, click Apply.
   

Edit Join via column

1. Click on the name of the report you want to run.

2. Click Edit Columns. 

3. On the Edit Columns screen that opens, click Edit next to Join via column. 

4. Use the dropdown to select a new column to join the two report templates.
   

5. Click to save your changes. 

6. Repeat steps 2-4 for the second template if needed. 

7. When you’re finished, click Apply to apply your changes to the multi-template report. 

8. To see the report results, click Run Report.

Change join type and swap positions

With multi-template reports, you can edit how you want the data joined. You can also swap the positions of the templates.  

Change join type

1. Click on the name of the report you want to run.

2. Click Edit Columns. 

3. On the Edit Columns screen that opens, scroll to the Join Types & Swapping section. 

4. Select which join type you want to use for the multi-template report:

   1. Left Join: Using left join will return all rows of data from the first template and only the rows in the second template that match. Multi-template reports use left join by default. 

   2. Inner Join: Using inner join combines the two tables based on a shared key. 

      Join type example

      For example, you want to run a multi-template report combining the Jobs and Invoices templates. You’re joining these templates through the Job ID column.

      1. The Jobs template contains columns for Job ID and Customer Name.

      2. The Invoices template contains columns for Job ID and Invoice Amount.

      A left join returns all the jobs from the Jobs template, whether or not they have a matching invoice in the Invoices template. If there’s no invoice in the Invoices template for a job, it will still show the job details but with a blank or null value for the Invoice Amount.
      An inner join returns the jobs with a matching job number in both the Jobs and Invoices templates. You only get records for jobs that have an invoice associated with them.

      

5. When finished, click Apply. 

Swap template positions

Click between the two templates to switch template positions. This changes the position of the main or first selected template and the secondary template. 

For example, if you initially selected the Jobs template when creating the multi-template report, each row in the report shows data about a different job. If you want the report to show data about a different invoice, you can swap the positions of the templates instead of creating a brand-new report.

Note: The Join Type does not change if you swap the templates.

When you’re finished, click Apply.  

Joins through data fields for multi-template reports

In the tables below, you can see all data fields, or columns, available for creating multi-template reports and the data fields, or columns, you can use to join the templates. 

Business Unit ID

Customer ID

Export ID

Invoice ID

Location ID

Payroll ID

Job ID

Multi-template report examples

• You can create a multi-template report using the Jobs and Technician Performance templates because they share the same Business Unit ID data field. Even though the data field on the Technician Performance template is called Technician Business Unit ID, it is the same as Business Unit ID, with a different name. 

• You can’t create a multi-template report using the Jobs and Master Pay File template using the Business Unit ID data field. The Master Pay File template doesn’t contain that data field. However, if you select the Location ID data field, you can join the templates because both contain the Location ID data field. 

Want to learn more?

• See Create custom reports

• See Run, filter, and export reports

• See Schedule reports