CRM Analytics Dynamic Dashboard Filter Options with Data Cloud

You’ve harmonized, transformed, and unified your data in Data Cloud and are finally ready to create a dashboard in CRM Analytics to deliver jaw-dropping insights. You’ve read the previous blog about how to Query data from Data Cloud in CRM Analytics but still have questions about ensuring logged-in users only see data relevant to their role.

Building Dashboards connected to Data Cloud in CRM Analytics allows you to query data directly from Data Cloud, similar to the Salesforce Direct feature. While the Salesforce Direct feature allows you to query directly from Salesforce objects, querying directly from Data Cloud allows you to visualize data from almost any external source: CRM, Marketing Cloud, Ingestion API, Amazon S3, or any of the sources listed on the Data Sources page.

Logically, the diversity of this data adds an additional layer of complexity in determining row-level security for the logged-in user. And because CRM Analytics directly queries Data Cloud, there is not an option for creating security predicates as that option is only available for CRM Analytics datasets. As Data Cloud continues to evolve, more features will be added to solve for this row-level security challenge (see Data Spaces for capabilities at the time of this blog’s publication). But how do you solve for this challenge in the interim?

Luckily, CRM Analytics has the ability to filter a dashboard by a logged-in user when building queries from Data Cloud. Follow along as we walk you through how to create this solution using filters and interactions (formerly known as bindings) to ensure that logged-in users view the right data from Data Cloud in your CRM Analytics dashboards. Let’s get started!

Create a Direct Query to the User Object

The first thing we have to do is create a Direct Query to the Salesforce User object to return the ID of the logged-in user. Simply click Create Query and select Salesforce Objects > User to open a query on the User SObject. We only need the User ID value, but if you’d like to validate your data, you can capture First Name, Last Name, or other fields.

Next, click on the Filters tab to filter by User ID. It doesn’t matter what value is selected. We are going to use this as a way to get the correct filter syntax.

From there, navigate to Query Mode where you can manually update the query syntax to further customize. Now, replace the User ID value from your filter with ‘!{User.Id}’ and change the Limit from 100 to 1.

Your query will transform from something like this:

SELECT Id, LastName, FirstName, Country FROM User WHERE Id = '005123123123123123' LIMIT 100

To this:

SELECT Id, LastName, FirstName, Country FROM User WHERE Id = '!{User.Id}'  LIMIT 1

Save this query as Logged In User. You will use it later.

Create Your Data Cloud Query

Lets start with a very simple example – you have been asked to create a dashboard for each Sales Rep that displays details about their Accounts and Opportunities. One of the charts will show their Open Pipeline by Account. Complete the following steps to create this simple bar chart:

1. Click Create Query in the Dashboard Designer
2. Click Salesforce Data Cloud then the Data Models tab and locate the Opportunity (ssot__Opportunity__dlm) Data Model Object (DMO).
3. Join the Account and User DMOs by clicking Manage then select the checkbox next to the rows for the Account and User related objects. Click Apply.

4. Change the Chart Type to Stacked Bar
5. Set the Bar Length to Sum of Total Amount
6. Select the Down Arrow on the Sum of Total Amount Bar Length and select Sort Descending
7. Set the Bars to Account > Account Name
8. Set the Bar Segments to Opportunity > Opportunity Stage
9. Give your Query a name like Pipeline by Account and Stage
10. Click Done

Drag and drop this new chart onto your Dashboard.

Apply a Filter to Your Data Cloud Query

Just like how you created a filter for your User Query, you will also create a filter for the Data Cloud Query. This adds the correct query syntax for filtering.

On the Filters tab, click the + button to add a new filter for User ID. You can select any of the ID values that appear. Remember, you are using this as a placeholder to add the logged-in user value. Click Done to close this Query.

Modify the Data Cloud Query with an Interaction

Now you are ready to put all the pieces together and add an Interaction (fka Binding) to your Query so that it will only display Accounts owned by the logged-in user. You are going to use the Advanced Interaction Editor to build an interaction that inserts the logged-in user ID into your existing filter. If you know a little bit about interaction functions, you’ll recognize this is a ‘result’ instead of a ‘selection’ interaction. This means that this interaction happens without any manual user interference so that the logged-in user ID is automatically dynamically inserted into your dashboard filters.

If this sounds complicated, don’t worry! Just follow the step-by-step instructions below.

1. Select the chart in Dashboard Designer and then click the Advanced Editor button at the bottom of the screen.
2. In the left sidebar under Source Query, select the Logged In User Query you created at the beginning. Remember, this will display the User ID of the logged-in user.
3. Under Source Data, click Choose Data and then select the following options:
   1. Data Selection: Cell
   2. Row Index: 0
   3. Column: Id
4. Notice that the Created Interaction is displayed on the screen as

cell(Logged_In_User.result, 0, \"Id\").asString()

5. Click the Copy button to copy this text
6. Select the Query tab at the top of the screen and scroll down until your find the filter you created.
7. Paste the copied text between the quotations and click Save.

Filter by Logged-In User Attribute – an Optional Step

In addition to simply filtering records that are owned by the logged-in user, it’s possible to use the same logic above but modified to attributes about the logged-in user. For example, you may want to display only Accounts with associated Opportunities in the same country or region as the user. That’s simple to do as long as you have that attribute stored in your data model in Data Cloud.

In this example, as a prerequisite, make sure that you have mapped the Billing Country field on Account to the Country field in the Address DMO. You will also want to validate that a Relationship exists between the Account and Address DMOs. If this relationship does not exist, simply create it manually.

Now you are ready to begin building the query.

1. Click Create Query in the Dashboard Designer.
2. Click Salesforce Data Cloud then the Data Models tab and locate the Opportunity (ssot__Opportunity__dlm) Data Model Object (DMO).
3. Join the Account and User DMOs by clicking Manage then select the checkbox next to the rows for the Account and User related objects. Click Apply.
4. Click Manage again and select the checkbox next to the Address related object. Click Apply.
5. Change the Chart Type to Stacked Bar.
6. Set the Bar Length to Sum of Total Amount.
7. Set the Bars to Address > Country Name.
8. Click the + button under Bars to add Account > Account Name.
9. Set the Bar Segments to Opportunity > Opportunity Stage.
10. Select the Filter tab and add a filter for Address > Country Name, then select any value.
11. Give your Query a name like Pipeline by Account and Stage (Country Filter).
12. Click Done.

13. Drag and drop your chart onto the Dashboard Designer.
14. Select the chart in Dashboard Designer and then click the Advanced Editor button at the bottom of the screen.
15. In the left sidebar under Source Query, select the Logged In User Query you created at the beginning. Remember, this will display the User ID of the logged-in user.
16. Under Source Data, click Choose Data and then select the following options:
    1. Data Selection: Cell
    2. Row Index: 0
    3. Column: Country
17. Notice that the Created Interaction is displayed on the screen as

cell(Logged_In_User.result, 0, \"Country\").asString()

18. Click the Copy button to copy this text.
19. Select the Query tab at the top of the screen and scroll down until you find the filter you created.
20. Paste the copied text between the quotations and click Save.

Wrapping Up

You can see that there are a variety of options to control filtering by the logged-in user with CRM Analytics and Data Cloud.

Before replicating this solution in your own CRM Analytics dashboards, it is important to make sure that the logged-in user filter approach works with your organization’s security guidelines. For the majority of use cases, filtering by the logged-in user will satisfy security needs as long as the viewing user is not given permission to bypass filters by editing the dashboard or accessing Data Cloud data directly through other means.

If you simply need to control the information displayed to a logged-in user in a pre-filtered CRM Analytics dashboard, this method could satisfy row level requirements until additional capabilities are made available in Data Cloud.

Additional References

To learn more about Interactions, previously called bindings, check out the resources below:

• All you need to know about bindings (Salesforceblogger)
• How to filter dashboard by logged-in user (Salesforceblogger)
• Query data from Data Cloud in CRM Analytics (Salesforceblogger)
• Analytics Dashboard JSON Developer Guide
• Trailhead Module: Use Bindings to Compare Data

Written by

Kathryn Baker Parks

See author's posts

Simon Lai

Principal Solution Engineer at Salesforce working with CRM Analytics since 2015.

See author's posts