Join Data Sources on SIERA Report Builder

1. Feature Overview

The SIERA Report Builder supports advanced data modeling capabilities through Joins and Sub-Query Joins. These features allow users to combine multiple data sources, apply cross-entity filters, and build complex expressions across nested queries.

Use Joins to merge fields across related entities such as Leads, Tasks, Activities, Opportunities, and more.
Use Sub-Query Joins to go a step further—filter, aggregate, and group data within independent sub-queries before linking the result to the main report.

This article covers both standard joins and the sub-query join capability, with practical examples and configuration steps.

2. Prerequisites

SIERA is a paid feature. To enable it for your account, contact your account manager or siera.support@leadsquared.com.
For details on Report Builder Data Sources, refer to this article.

3. Join Functionality

SIERA Report Builder allows you to combine data from multiple entities using the Join feature. This is useful for generating consolidated reports across related data sources like Leads, Opportunities, Tasks, Activities, etc.

There are four types of joins available:

Left Outer Join: Returns all records from the left entity and matching records from the right.
Inner Join: Returns only records that match in both entities.
Right Outer Join: Returns all records from the right entity and matching records from the left.
Full Outer Join: Returns all records where there’s a match in either entity.

Use-Case

Here are some common use cases for SIERA’s Join feature –

Use Case	Tables/Data Sources	Join	Join Condition	Expected Output
Find all closed-won opportunities and their associated accounts.	Opportunities (Opportunity ID, Name, Account ID, Status, Amount) Accounts (Account ID, Account Name, Industry)	Inner Join	Opportunities INNER JOIN Accounts ON Opportunities.AccountID = Accounts.AccountID	Lists only won opportunities along with the associated account details.
Show all tasks, even those not assigned to a user.	Tasks (Task ID, Title, Due Date, Assigned User ID) Users (User ID, Name, Role)	Left Join	Tasks LEFT JOIN Users ON Tasks.AssignedUserID = Users.UserID	Includes all tasks. Tasks without assigned users will have NULL values in the “AssignedUser” column.
Show all contacts, even if they haven’t attended a meeting.	Meetings (Meeting ID, Subject, Contact ID, Date) Contacts (Contact ID, Name, Email, Company)	Right Join	Meetings RIGHT JOIN Contacts ON Meetings.RelatedProspectId = Contacts.RelatedProspectId	Every contact appears in the report. Contacts who haven’t attended any meetings will have NULL values for meetings.
Get a full view of all invoices and payments, including unpaid invoices and orphaned payments.	Invoices (Invoice ID, Customer ID, Amount, Due Date) Payments (Payment ID, Invoice ID, Amount Paid, Payment Date)	Full Outer Join	Invoices FULL OUTER JOIN Payments ON Invoices.RelatedProspectId = Payments.RelatedProspectId	Includes all invoices, whether paid or unpaid. Includes all payments, even if they aren’t linked to an invoice.

4. Creating a Join

For example, When you use a Left Outer Join between Leads and Activities, it retrieves all Leads that match a filter (e.g., Created On) but only includes Activities linked to these specific leads:

Click the icon against Data Source.
Click the icon.
Select the Join as: Leads Left Outer Join with All Activites.
The On function would be: ProspectID = RelatedProspectID.
- Here, we are matching the lead ID stored as ProspectID in the Lead Data Source with the lead ID stored as RelatedProspectID in the Activity Data Source. This ensures that activities are correctly associated with their respective leads. For more details on Report Builder Data Sources, refer to this article.
Click Save.

Now, you can add the related activity fields to your report.

Note:

You can create up to three joins between different entities.
Once an entity is joined, you can also utilize its related fields to build custom values, enabling more refined data analysis and reporting.

Report builder data sources

5. Data Source Join Fields for Accurate Data Retrieval

This table illustrates the appropriate join fields for different data sources to ensure accurate data retrieval.

Data Source 1	Data Source 2	Join Field for Data Source 1	Join Field for Data Source 2	Comments
Leads	Activities/ Custom Activity	ProspectID	RelatedProspectId
Leads	Opportunity/ Custom Opportunity	ProspectID	RelatedProspectId
Leads	Task/ Custom Task	ProspectID	RelatedEntityId
Leads	LandingPage	RelatedLandingPageId	WebContentId
Leads	Accounts/ Custom Accounts	RelatedCompanyId	CompanyId
Leads	AccountsActivity/ Custom AccountsActivity	RelatedCompanyId	RelatedCompanyId
All Activities/ Custom Activities	Opportunity/ Custom Opportunity	OpportunityProspectActivityId	ProspectActivityId
All Activities/ Custom Activities	LandingPage	RelatedLandingPageId	WebContentId
All Activities/ Custom Activities	Task/ Custom Task	OpportunityProspectActivityId	OpportunityProspectActivityId	If Activity & Task are performed on the Opportunity.
		OpportunityRelatedProspectId	OpportunityRelatedProspectId	If Activity & Task are performed on the Lead.
		RelatedProspectId	RelatedEntityId	If Opportunity is not enabled.
Opportunity/ Custom Opportunity	Task/ Custom Task	ProspectActivityId	OpportunityProspectActivityId
Opportunity/ Custom Opportunity	LandingPage	RelatedLandingPageId	WebContentId
Accounts/ Custom Accounts	AccountsActivity/ Custom AccountsActivity	CompanyId	RelatedCompanyId

6. Sub-Query Joins (Advanced Joins)

Sub-Query Joins allow you to nest an entire query within your main report, enabling you to group, filter, and aggregate data across multiple entities before joining it back to the primary data source. This unlocks advanced reporting use cases that aren’t possible with standard joins—such as applying multi-stage filters, custom expressions, or deep aggregations across related datasets.

Key capabilities –

Up to three joins per sub-query.
Use filters, aggregations, and custom expressions inside sub-queries.
Join a sub-query back to the main report.
Use sub-query expressions in the main report (e.g., date differences).
Supports pagination to limit data returned from sub-queries.

Use Cases

Here are some use-cases for the Sub-Query feature –

Visitor to Customer Analysis: Analyze how many website visitors convert into customers, segmented by source.
Time Taken to Win a Customer: Calculate the time difference between lead creation to conversion date.
Leads With and Without Activities: Count leads with at least one activity and leads with no activities.

7. Rules for Creating a Sub-Query

A sub-query can include grouping, which can be used to join with another sub-query or the root query. The grouped field becomes available for use in the next step (if applicable).
A sub-query may exclude grouping, allowing it to be used as a NOT IN query.
A sub-query can include joins, meaning it can join with one or more tables or other queries.
A sub-query can include filters, which may appear in additional filters or as report-level filters based on configuration.
A sub-query can include measures, which can be reused in other queries and will become fields in subsequent steps (if applicable).

8. Creating a Sub Query Join

Let’s walk through a real example: counting leads with at least one activity and leads with no activities.

Example Use Case: Leads With and Without Activities

Configuration Steps

Edit the Data Source: From the main report builder, click the Edit icon next to your data source.
Create a New Query: Click New Query to define a sub-query. This query will process the necessary joins and aggregations.
Set Up Sub-Query Layers: Create the following sub-queries:
- Query 2 (Leads Layer): Select the Leads data source.
- Group by:
  - Owner
  - Prospect ID
- Query 3 (Activities Layer): Select the All Activities data source.
- Group by:
  - ActivityId
  - RelatedProspectId
  - ActivityEvent
Query 1 (Join Layer): Join Query 2 (Leads) with Query 3 (Activities).
- Join Type: Left Join
- Join Condition: Prospect ID = RelatedProspectID
- Group Query 1 by:
  - Owner
  - Prospect ID
- Create Custom Expressions in the Query to count activities
  - Activity Count: CountDistinctIf(`Query 3.ActivityId`, `Query 3.ActivityId` != '')
Click Save.

Creating subquery join

Use Query Output in the Main Builder

In the main custom expression builder, use the following expressions to segment leads:

Leads With Activities: CountDistinctIf(`Query 1.Prospect ID`, `Query 1.Activity Count` > 0)
Leads Without Activities: CountDistinctIf(`Query 1.Prospect ID`, `Query 1.Activity Count` < 1)

These expressions reference data already grouped and aggregated in the sub-query, making them far more powerful than what is possible using basic joins alone.

show activities for sub query join in expressions

Any Questions?

Did you find this article helpful? Please let us know any feedback you may have in the comments section below. We’d love to hear from you and help you out!

Submit a Ticket
First Name
Last Name
Email
Subject
Issue Description
Priority
Attachment	Attach files Each of your file(s) can be up to 20MB in size.
Captcha	Refresh

	powered by