Merge advanced reports

Atera's advanced report Explore capabilities let you merge datasets. Merging lets you translate valuable data across data sources into clear and informative insight. For more information, see Atera's advanced reports

How it works

• Merging datasets is a part of the Explore capabilities of Atera's advanced reports. It is available to Power users only.
• You can merge datasets only if the foreign key exists within the selected dataset. Learn more

Primary and foreign keys

Each dataset contains fields ('Agent ID', 'Alert Type', etc.). Each field is represented as a column when generating an advanced report. Certain fields can be classified as primary or secondary keys.

• A primary key refers to a dataset's primary field ID. For example, the primary key of the 'Alerts' dataset is the 'Alert ID'. 
• A foreign key refers to a foreign dataset's primary field ID that appears within a host's dataset. For example, foreign keys within the 'Alerts' dataset include, but are not limited to, the 'Agent ID', 'Customer ID', and 'Folder ID' — these exist as primary keys within their respective datasets.
• To make things easier for you, all keys have "ID" in their name.
• You can merge datasets only if the primary key exists as a foreign key within the 'host' dataset.

We've streamlined much of your merging needs by merging other datasets within each 'View' (table). For example, 'Alerts' includes the 'Agents', 'Folders', and 'Tickets' datasets. In these cases, you can connect two different datasets (e.g., 'Alerts' and 'Folders') by using the foreign key (i.e., Folder ID) within 'Alerts'. You're not really merging anything at all, as it's all done already. Simply select the data fields you'd like to include, and run the report.

Of course, individual needs vary, so you may want to merge other datasets. And that's exactly what the rest of this article details.

How to merge datasets

In the following example, we want to create a report showing a list of tickets and their statuses. We could create a report from the 'Tickets' dataset and include the 'Status ID' field, but this would return a numbered key-value pair, which isn't very helpful. To see the value of this pair, we need to merge the 'Tickets' dataset with the 'Ticket: Statuses' dataset. We can do this because the foreign key, 'Status ID' (within the 'Tickets' dataset) exists as a primary key within the 'Ticket: Statuses' dataset. Merging by this data point will allow us to fetch related information from the dataset (in this example, 'Name').

To merge datasets:

1. From Reports > Advanced Reports, click New advanced report. 

The New advanced report window appears.

2. Select Tickets.

The Tickets dataset appears.

3. Select the Ticket Title and the Status ID data fields.
Note: The 'Status ID' is the foreign key we'll use to merge the 'Tickets' dataset with the 'Ticket Statuses' dataset). 

4. Click Run.

5. Click the gear icon (). Then select Merge results.

The Merge Query screen appears.

6. Find and select Tickets: Statuses.

7. Select the ID and Name data fields.
Note: 'ID' is the primary key here (and is the foreign key within the 'Tickets' dataset).

8. Click Run to view the data. Then click Save. The Merged Results screen appears.

9. Confirm the merge rules. Then click Run.
Note: In the merge rules above, we're merging the 'ID' (primary key within 'Ticket: Statuses') with 'Status ID' (foreign key within 'Tickets'). They both point to the same object. From this shared pointing, we're able to fetch related data (i.e., 'Name'). 

10. Click the gear icon () on the top-right corner of the screen.

11. Select Save to Dashboard...

12. Enter a title and select a folder. Then click Save to Dashboard.

Oh yea! The advanced report appears in your Dashboard.

• In the image below, we see the initial advanced report we could have generated had we not merged datasets (on the left).
• On the right, we see the value of merging, as we can now understand the data presented.