SIERA Report Builder

1. Feature Overview

The SIERA report builder allows admins to create customized reports using existing data (such as leads, activities, etc.) in their accounts, and determine which users should have access to them.

2. How it Works

In this article, we will demonstrate how to create SIERA reports using predefined templates. These templates include pre-joined data from various LeadSquared entities such as leads, activities, opportunities, tasks, and phone call activities.

To build your own reports, it is essential to determine which predefined template aligns with your specific use case. For example, if your goal is to track the number of Payment activities posted on all the leads within the past month, you will need to utilize a template that contains the Activity Data Source, where the Data Scope is set to Payment activities (Read this article for more information on Data Sources).

While your account provides nine default templates if Opportunities is enabled (or seven templates if it’s not enabled), if your use case does not fit within these templates’ scope, you can request a custom template to be created by contacting your account manager or emailing support@leadsquared.com.

3. Using the Report Builder

The Report Builder has the following sections –

• Data –This section lets you join data sources, add values, and add fields as rows and columns in your report. You can also choose a pre-defined value or create your own expression here.
• Filters –This section lets you add fields as filters in your report. You can also add default values to the filter. For each data source, there are a set of mandatory filters that need to be added.
• Visualise – This section lets you add charts to your report. The axes of the charts depend on the Groupings and Values.
• Drilldown – This section will be used to configure a custom column for the measures used in the report.

On the right-hand side of the Report Builder is the Preview section. This lets you view the report up till the first 10 rows.

4. Data

This section helps you join data sources, design the Rows, Columns, Values, Sorting, and Pagination, and add values for the report.

4.1 Data Sources

This feature allows you to combine multiple data sources using the Join functionality. 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.

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:

1. Click the  icon against Data Source.
2. Click the  icon.
3. Select the Join as: Leads Left Outer Join with All Activites.
4. 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.
5. 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.

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

4.1. Row Grouping

This option will let you select the row grouping of the report. Row grouping defines the fields that will occupy each row of the report. For instance, if we put Lead Stage as a Row Grouping, each value of Lead Stage will occupy a row.

The + Fields button opens all fields corresponding to the Data Source (For example, Activity Data Source will contain all Lead Fields and Standard Activity Fields, along with System User Fields.) Click on any field to add it to the row grouping and click on Preview to see the changes.

Note:

• Only Date and String (Dropdown + Text) fields are allowed.
• The minimum permissible number of row groupings is 1.
• The maximum permissible number of row groupings is 3.
• To define sorting on a row grouping, click on the sort icon beside the field.
• To remove a field from the row grouping, click on the cross icon.
• To add a Row grouping, click on the + Fields option.

4.2 Column Grouping

This option will let you select the column grouping of the report. You can make a pivot table with your data.

The + Fields button opens all fields corresponding to the Data Source (For example, Activity Data Source will contain all Lead Fields and Standard Activity Fields, along with System User Fields.) Click on any field to add it to the Column grouping and click on Preview to see the changes.

Note:

• Only Date and String (Dropdown + Text) fields are allowed.
• In pivot mode, up to 52 columns will be visible, after which all the other columns will be clubbed under the Others column.
• The minimum permissible number of row groupings is 0.
• The maximum permissible number of row groupings is 1.
• To remove a field from column grouping, click on the cross icon.
• To add a column grouping, click the + Fields option.

4.3 Values

This option will let you select the measures in the report. The + Fields button opens all fields corresponding to the Data Source (For example, Activity Data Source will contain all Lead Fields and Standard Activity Fields, along with System User Fields.) Click on any field to add it to the Values section and click on Preview to see the changes.

Note:

• Only Number fields are allowed to be added from the +Fields options. Values Name and aggregator can also be changed by clicking the edit button.
• Custom expressions can be added in the vales section by the expression editor.
• The Values section also has a pre-defined set of values to help create quick reports.
• Users can also write their own expressions using the Expression Builder. For more information, read this article on Expression Builder.
• The minimum permissible number of row groupings is 1.
• The maximum permissible number of row groupings is 25.
• To remove a field from column grouping, click on the cross icon.

4.4 Sorting

You can use the sort icon in the groupings and values to change the sorting order. In case of multiple row grouping or column grouping few fields will have mandatory sorting which can’t be changed.

4.5 Pagination

Pagination can be set for the report, which will load the number of records. Pagination can be selected from 10, 25, 50 and 100.

 

5. Filters

This section lets you add fields as filters in your report. For each data source, there are a set of mandatory filters that must be added.

• The + Fields button opens all fields corresponding to the Data Source. For example, the Activity Data Source will contain all Lead Fields and Standard Activity Fields, along with System User Fields.
• Only Date and String (Dropdown + Text) fields are allowed.
• Users can change the name of the filter by clicking on the dot menu.
• The Filter section also has a set of mandatory filters that come with the data source.
• The maximum permissible number of row groupings is 15.
• To remove a field from column grouping, click the cross icon.
• To add a column grouping, click on the + Filter option.
• Users can change the display name of the filter.

6. Visualise

7. Drilldown

The Drill-down Configurator allows admins to customize columns for measures used in reports. By default, the Drilldown section is enabled with default mappings. The admin can deselect any measure and add a custom drill-down for it by clicking + Configure. 

• Measures are grouped by entity, and selecting a measure disables columns from child entities (for example, for Lead measure, Activity columns cannot be configured).
• If no columns are mapped for a measure, users can’t drill down on it.
• Pagination of drill down can also be set by the admin while creating the report.
• Admins can sort columns, apply custom formatting to number and date-time fields, and change the display name of all the columns.
• For calculated measures (measures that use arithmetic operations), drill-down configuration is not allowed.

8. Preview

The Preview button will preview the report output. This will be limited to the first 10 results only.

9. Build a Report Using a Template

Here’s an example to illustrate how to create a report using a template in your account:

Use Case

You want to track the number of leads created within a specific time frame and determine how many of those leads have associated activities posted against them.

To generate this report, we’ll need to determine the –

• Data Scope – As this report focuses on lead data, we will utilize the Leads Data Source template. Next, we’ll join the Activity Data Source template to count the leads with activities. (Read this article for more information on Data Sources).

To create the report –

1. On the SIERA homepage, click Create Report, and from the options listed under Data Source, choose a relevant template, and click Next.
   • To solve our use case, we’ll be selecting Leads (System Fields + Custom Fields).
2. Alongside Create a new report, enter the following report details, and click Save –
   • Name – A display name for the report.
   • Description – Add a description for the report.
3. Under the Data tab, configure the Data Source, Rows, Columns, and Values that you want to display in the report.
   •  Under Data Source, click the Edit button to create a join. For our example, let’s: Leads Left Outer Join with All Activities.
   • Under Row Grouping, click the + Fields button to access all LeadSquared fields related to the Data Source associated with the template. Here, select the fields you want to display in the report rows.
   • Similarly, under Column Grouping, the + Fields button displays all LeadSquared fields corresponding to the Data Source associated with the template.
   • Under Values, the + Fields button displays all LeadSquared number fields part of the Data Source that’s associated with the report. Here, we’ll be creating a custom value to display Leads with activities.
4. Under the Filters tab, configure the filters you want to display on the report. Each template comes with a default filter, you can switch this filter with other available filters, but cannot be removed completely. In addition to this, to add other filters, click + Add. Learn more about SIERA’s Expression Builder.
   • Once a filter is added, you can rename the filter by clicking .
5. Under Visualise, from the list of options available (bar, column, pie, etc.) you can choose how you want to visually represent your report. We’re selecting a Column chart here.
6. Once you’ve configured the report, click Preview to check if the report looks as expected. Once you’re satisfied, click Save.

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!