Use Multi-Template Reports in Enterprise Hub Rollup Reports

Overview

Multi-template reports allow you to combine separate report templates into a single report, increasing 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 Add Template or 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 can only join templates that share unique, ID-based columns. For example, You can join the Invoices and Jobs templates because both templates contain the same ID-based Customer ID column. 

• 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. In Enterprise Hub, click Rollup Reporting.
   

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

3. 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.
    

   Tip: The Tenant ID, Tenant Name, and Tenant Official Name columns are important for each template, so select them when creating a report.

   Note: You can always add and remove columns later.

   Tip: Enter a few letters of the column you’re looking for in the search bar to find it.
   
   

6. When you’re done selecting columns, click Add Template.
   The Join New Template screen opens. 

7. 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. 

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

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

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

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

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

13. When you’re done, click Next. 

14. In the Members & Access screen that opens, select members who can access your report.
    
    

    Note: You can always edit the sharing settings later and give individual permissions to each member. For more information, see Edit report sharing settings.

15. When you’re done, click Save. 

You can see the report you created. 

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 when you're done selecting the columns you want to display in your report results from that template.
   

4. When you’re 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 to be 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

1. Click   between the two templates. 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.

2. When you’re finished, click Apply.  

Monitoring report changes

If you create a multi-template report and allow other users to edit the report, we provide several options to check for changes. 

Report states

A report can exist in various states. 

• Current State: The state of the report after opening it or making changes but before running the report. This is also called the on-screen state. 

• Run State: The state of the report after running it but before saving the changes. This is also the Last Run state if you view the report less than 12 hours after it was run last. 

• Saved State: The state of the report after clicking Save Changes. This state displays if you view the report more than 12 hours after running it last. 

If the last time a report was run was over 12 hours from the current time, the system shows the latest report state. For example, if you open a report and modify the report’s state, run the report, and save the changes, you will see the state saved by the other user in the report 12 hours after your run.

If less than 12 hours have passed since the last run, Last Run state is used. Subsequently, if more than 12 hours have passed since the last run, Saved State is used. For example, you open a report, modify the filters or columns, run the report, and save the changes. Then, someone with access to the report changes the filters and saves it afterward. You will still see the state from your last run if you open the report within 12 hours of that last run. 

User notifications for report state changes

Report states changed by other users are displayed in a banner on the report overview screen. 

If another user modifies the report join definition since the last run, click See Changes in the notification banner to see what was modified. 

The following fields are monitored for changes in the join definition. If any of these fields are modified, the changes appear in the See Changes window. 

• Join Type

• Template 1

• Join via Column 1

• Template 2

• Join via Column 2

Run, filter, and export 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. Use the Network / Tenant, Business Unit, Filter by dropdowns, and date fields to create filters for your report.

   Note: The Date filter filters by date and time in UTC format. The Business Units filter filters by tenant and job types.

3. With multi-template reporting, 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 template, 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 filter. This would be considered a common filter.
      

   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 that came from the Jobs report template. The Invoices template-specific filter you select would only affect the columns from the Invoices report template.
      

      Tip: Once you have set your filters, you can collapse and expand the filter section for a better user experience.
      

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

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

Your report results are displayed in a table:

• 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.

• Your report results are displayed in a table. You can customize which columns you want to include your results. For more information, see Edit report columns.

• 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 the Export reports section.

• While the report is loading, the approximate time to generate the report displays. If your report takes too long to generate, you will be prompted to export the report. 

• If you try to run the report with too many tenants, the report may not load. You can use the report filters to run the report with smaller data. 

View run history

You can view the five most recent runs of the report by clicking Run History. 

The Run History window opens, where you can view the user who last ran the report, when the report was run, the number of tenants included in the report, and more. You can also download that report version by clicking Download .

Export reports

If your report takes too long to load or you want to use the report in spreadsheets and other documents, you can export the report.

1. Click Export.
   

2. Fill out the following selections:
   
   

   1. Exporting type:

      1. Export Current Result (Instant): Exports the current version of the report.

      2. Export New Result (max time Xm XXs): Runs the report again and exports a new version of the report. Max time is listed in minutes and seconds it takes to load the report. 

   2. Exporting Format:

      1. Export as XLSX (MS Excel)

      2. Export as PDF

      3. Export as CSV

   3. Exporting Options:

      1. Export only aggregated data: Downloads only the aggregated report data. 

3. When you’re done, click Export. 

Schedule multi-template reports

1. Find the report you want to schedule and click Schedule Report .
   

2. Select the report type:

   1. Simple Report: Use simple reports if you don’t need to customize your report or if you want to deliver individualized reports to recipients.

   2. Flexible Report: Use flexible reports if you want to customize the look and filters of the report before sending it. Each recipient receives the same report.
      

3. After you select the report type you want to schedule, enter the basic details of your report. Make sure to set the report filters so that the scheduled report returns the filtered results.
   

   Note: You can add basic filters based on dates, record types, and business units. The filter options change depending on the report type.

   1. Email Subject: Enter the subject line you want for the emailed report.

   2. Attach report as: Select the format for your report:

      1. PDF

      2. XLSX

      3. Export only aggregated data

   3. Date Range: Select the days to report on:

      Note: The date range included in the report and the time the report is sent will default to your time zone. 

      1. Today: Report data from 12:00 AM to the time the report is delivered

      2. Yesterday: The previous calendar day from 12:00 AM to 11:59 PM

      3. Tomorrow: The next calendar day from 12:00 AM to 11:59 PM

      4. Next 7 Days: The seven days after the delivery date

      5. Trailing 7 Days: The previous seven days, not including the report delivery day

      6. Week to Date: The most recent Monday through the report delivery day

      7. Previous Week: The full week, Monday through Sunday, before the week of the delivery day

      8. Month To Date: The first of the month to the report delivery day

      9. Trailing 30 Days: The previous 30 days, not including the report delivery day

      10. Previous Month: The entire month before the report is delivered. For example, if the report is scheduled for February 5, the report includes data for the entire month of January.

      11. Custom: Select Next to report on days in the future or Previous to report on days in the past. Enter how many days to report on in the Days field. Select Include the send date to include the delivery date in the report results.

   4. Network/Tenant: From the Network/Tenant dropdown, select the network you want to report on.

      Note: You can select multiple networks. 

   5. Business Unit: From the Business Unit dropdown, select the business units you want to report on.

      Note: You can select multiple business units. 

4. When you’re done, click Next.

5. On the Select Recipients screen, from the Search and add recipients dropdown, select individuals, business units, and teams to send the report to members within them.
   

6. When you’re done, click Next.

7. On the Select Cadence screen, in the Send report every field, enter a frequency and select a unit of time for your report. For example, if you enter 1 and select week, the report is delivered every week. From the dropdown, select the time you want the report delivered.

8. Select the day of the week you want the report delivered.

   Note: If you’re scheduling a flexible report, you can arrange columns and filter your report results before they’re sent. For more on reporting filters, see Filter and arrange a report.

9. When you’re done, click Schedule.

To view or duplicate scheduled reports, see View and duplicate scheduled reports.

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 Enterprise Hub

• See Rollup Reporting

• See Rollup Dashboards