An Aggregation Case Study

Let’s look at the use case introduced in the aggregate models topic: a sales manager wants to quickly see the value of all opportunities in a team’s pipeline, broken down by stage, plus an average by stage. The sales manager also wanted to filter by owner, close date, and industry. So, let’s build this page.

Note

Aggregate models are available for Salesforce and SQL data source types.

Assumptions

Note

This tutorial assumes you have a Skuid page on a Salesforce data source and that the page has:

  • a model on the Salesforce Opportunity object

  • a model on the Salesforce User object with the following configuration:

    • Property settings

      • Max # of records (limit): Blank.
      • Fields to Order by: Name.

    • Fields included in the model

      • Name (Full Name)
      • ID (User ID)

Aggregating Sales Opportunities by Stage

Create an aggregate model [[]]

In the Elements pane:

  1. Click the opportunity model and make the following changes:

    • Model behavior: Aggregate.
    • Max # of records (limit): Remove number to make this field blank.

Create the first aggregation [[]]

When the model behavior is changed to aggregate, the model features listed under the model name in the Elements pane will change:

from

Fields, Conditions, and Actions

to

Aggregations, Conditions, Groupings, and Actions.

  1. Click Aggregations. The property pane displays a list of fields. We want to aggregate on the Amount field to get a total amount for all opportunity records.

  2. Click add Add next to the Amount field to create this new aggregation.

  3. Under Aggregations, click the Amount aggregation.

    Note

    The aggregation may be prepended with one of the function options, such as “sum,” “avg” or “count.”

  4. In the Aggregation properties pane, select:

    • Function: SUM
    • Alias name: Skuid auto-populates this field.

Create the second aggregation [[]]

Now that we’ve got an aggregation for the sum of the opportunity amounts, let’s do a second one for the average of the opportunity amounts.

Note

We’ll be using the same field (Amount) as used in the previous step, but with a different aggregate function.

  1. Click Aggregations to see the list of fields again.

  2. Click add Add next to the Amount field to create a second aggregation.

  3. In the Elements pane, under Aggregations, click the new Amount aggregation.

  4. In the Aggregation properties pane, select:

    • Function: AVG
    • Alias name: Skuid will auto-populate this field.

Now the model knows to aggregate opportunity amounts into totals and into averages. But this use case calls for a grouping to govern how the data will be displayed within Skuid components.

Create a grouping [[]]

  1. Click Groupings:

    • Grouping method: Simple
    • In the Properties pane, search for and check Stage. (This is the field we want to group the opportunities by.) Skuid displays a new grouping under Groupings.

  2. Click Save.

Now the page has an aggregate model with a grouping. Time to add a component to the page, to display the data.

Add a Table component [[]]

  1. Drag and drop a Table component into the canvas and configure:

    • Model: The opportunity aggregate model created in the first step.
    • Default mode: Read

  2. In the Table component, click Add fields > Model fields. (Because this is an aggregate model, the only fields available will be those used in the aggregations and the grouping.)

    • Check the following fields in this order (because that’s how they will be added to the table): StageName, SUM(Amount), and AVG(Amount). Click Apply.

    • Click on the SUM(Amount) field in the table and edit the field properties:

      • Custom label: Total Amount
      • Column summaries: Sum. This will add a total of all records at the bottom of the “Total Amount” column in the table.

    • Click on the AVG(Amount) field in the table and edit the field properties:

      • Custom label: Average Amount
      • Column summaries: Average. This will add an average of all records at the bottom of the “Average Amount” column in the table.

  3. Click Save and then Preview.

The table displays the opportunity stages, with a Total Amount and an Average Amount for each stage.

Add conditions to the model [[]]

First condition: limit dates [[]]

Rather than seeing all opportunities created since the team started tracking them, let’s limit what comes into the model by a close date using a condition.

  1. In the Elements pane, click the name of the Opportunity model.

  2. Under the model name, click Conditions.

  3. Click add Add to add a condition.

    • In the condition, set:

      • Field: CloseDate

      • Operator: is

      • Value:

        • Content: Single specified value
        • Value: Specific Date

      • State: Filterable default off

Second condition: limit opportunity owners [[]]

Now that the records can be limited by date, let’s also limit whose opportunities are displayed.

  1. Click add Add to add a second condition.

    • In the condition, set:

      • Field: OwnerID

      • Operator: is

      • Value:

        • Content: Single specified value
        • Value: Leave blank

      • State: Filterable default off

Create filters [[]]

With conditions set on the aggregate model, it’s now possible to set up filters on the Table component to give the end user control over which closing date and which owners to filter by.

Closing Date filter [[]]

  1. In the Table component, click Add Feature > Add Table Filter.

  2. Click on the new filter and edit:

    • Filter type: Select option
    • Pick options and conditions: Manually
    • Filter label: Close Date
    • Model condition to affect: CloseDate
    • Create None Selected Option: Checked
    • “None Selected” option text: Any Close Date
    • Display as: Dropdown.

  3. Click the Sources tab, then click add Add Option Source (Any Close Date - CloseDate).

    • Source type: Manual.

  4. On the new option source, click add Add Option three times and then configure the three options:

    • First option:

      • What Condition(s) will this Option affect?: Affect the default condition
      • Label: Closing: This Year
      • Value: THIS_YEAR

    • Second option:

      • What Condition(s) will this Option affect?: Affect the default condition
      • Label: Closing: Last Year
      • Value: LAST_YEAR

    • Third option:

      • What Condition(s) will this Option affect?: Affect the default condition
      • Label: Closing: This Fiscal Year
      • Value: THIS_FISCAL_YEAR

  5. Click Save, and click Preview.

Now the table has three closing-date filter options.

User Filter [[]]

This will filter for a specific user.

Add a condition to the user model [[]]

First, to see only the users that actually own an opportunity (versus all the users in the org), limit the page’s user model with a condition:

  1. In the Elements pane, under the User object model, click Conditions.

  2. Click add Add Condition and edit the condition:

    • Field: Id

    • Operator: is

    • Value:

      • Content: Result of a subquery
      • Join object: Opportunity
      • Join field: OwnerId

    • State: always on

  3. Click Save.

Create the user filter [[]]

Now, let’s add another filter to the Table component:

  1. In the Table, click Add Feature > Add Table Filter.

  2. Click on the new filter and edit:

    • Filter type: Select option
    • Pick options and conditions: Manually Select Conditions
    • Field Label: Opportunity Owner
    • Model condition to affect: The “limit opportunity owners” condition created here
    • Create None Selected Option: Checked.
    • “None Selected” option text: Any Owner
    • Display as: Dropdown

We also need to tell the filter who the possible owners are. We do this by adding an option source to the filter:

  1. In the Properties pane, click the Sources tab, then click add Add Option Source on Opportunity Owner.

    • Source type: Rows in a model
    • Merge source: The user model.
    • Option label template: Owner: {{{Name}}}
    • Which Conditions will this Source’s Options affect: Just the default condition
    • Value to Inject into Default Condition: {{id}}

Note

This is the field (from the user model) that will be injected into the owner condition (on the opportunity model), enabling end users to see owner names in the filter.

  1. Click Save, and click Preview.

Industry Filter [[]]

  1. In the Table component, click Add Feature > Add Table Filter.

  2. Click on the new filter and edit:

    • Filter type: Select option

    • Pick options and conditions: Automatically

    • Filter field: Industry

      Note

      Click the arrow next to Account ID. This navigates to the Account fields, where the Industry field is located.

    • Show Label above Filter: Yes, use Field’s label

    • “None Selected” option text: All Industries.

    • Display as: Dropdown

  3. Click Save, and click Preview.

The result

A page that:

  • Displays the value of all opportunities in a team’s pipeline, broken down by stage, plus an average by stage.
  • Contains filters allowing the end user to see opportunities limited by owner, close date, and industry.

image0