How to filter dashboard by logged-in user

The Summer17 Release of Salesforce Wave has given us some enhanced bindings as mentioned in my blog on the new release. I know, I will be enjoying this enhancement a lot. So I thought why not share how you could use the enhanced binding to filter dashboards based on the logged in user.

I have created a sample dashboard that I want to modify using some of the new binding options.

In my sample dashboard, I want to do the following:

  • Add a list filter of Opportunity Owner and set the initial filter to logged-in user
  • Add a list filter of Account Billing Country and set the initial filter to be the country of the logged-in user
  • Add a filter to the number widget to only show the won opportunities of the logged-in user.

Logged-in user step

The first thing we have to do is create a SOQL step to look up who the logged-in user is. So go to your dashboard JSON (command+E/control+E) and add the following.

"UserData": {
 "groups": [],
 "numbers": [],
 "query": "SELECT Name, Country, Id FROM User WHERE Id = '!{User.Id}'",
 "selectMode": "single",
 "strings": [
 "Name",
 "Country",
 "Id"
 ],
 "type": "soql"
 },

If you add the step to a values table, you will see how only your user appears with the columns you have selected and added to the string parameter.

In the Summer17 Release documentation, they do not use !{User.Id} instead they use !{User.Name}. I found that if you have multiple users with the same name, then they will appear. By switching name out with the ID you are sure that only one user will appear as ID is the unique identifier.

Opportunity Owner List Filter

First, we need to create a step that groups by Opportunity Owner Name. Make sure you convert your step into SAQL format.

Before clicking done make sure you run the query to activate the SAQL step.

Now add the newly created step as a list filter.

In order to select the initial filter, we need to modify the Dashboard JSON. In the JSON find the step used in your list selector and add the start parameter (in bold).

 "OwnerFilter_1": {
 "type": "saql",
 "query": "q = load \"Oppty_Modified\";\nq = group q by 'OwnerId.Name';\nq = foreach q generate 'OwnerId.Name' as 'OwnerId.Name', count() as 'count';\nq = order q by 'OwnerId.Name' asc;",
 "isFacet": true,
 "useGlobal": true,
 "numbers": [],
 "groups": [],
 "strings": [],
 "label": "OwnerFilter",
 "visualizationParameters": {
 "type": "chart",
 "parameters": {
 "visualizationType": "hbar",
 "autoFitMode": "none",
 "title": {
 "label": "",
 "subtitleLabel": "",
 "align": "center"
 },
 "theme": "wave",
 "showValues": true,
 "axisMode": "multi",
 "binValues": false,
 "bins": {
 "breakpoints": {
 "low": 0,
 "high": 100
 },
 "bands": {
 "low": {
 "label": "",
 "color": "#B22222"
 },
 "medium": {
 "label": "",
 "color": "#ffa500"
 },
 "high": {
 "label": "",
 "color": "#008000"
 }
 }
 },
 "dimensionAxis": {
 "showAxis": true,
 "showTitle": true,
 "title": "",
 "customSize": "auto",
 "icons": {
 "useIcons": false,
 "iconProps": {
 "column": "",
 "fit": "cover",
 "type": "round"
 }
 }
 },
 "measureAxis1": {
 "sqrtScale": false,
 "showAxis": true,
 "customDomain": {
 "showDomain": false,
 "low": null,
 "high": null
 },
 "showTitle": true,
 "title": "",
 "referenceLine": null
 },
 "measureAxis2": {
 "sqrtScale": false,
 "showAxis": true,
 "customDomain": {
 "showDomain": false,
 "low": null,
 "high": null
 },
 "showTitle": true,
 "title": "",
 "referenceLine": null
 },
 "legend": {
 "show": true,
 "showHeader": true,
 "inside": false,
 "position": "right-top",
 "customSize": "auto"
 },
 "trellis": {
 "enable": false,
 "showGridLines": true,
 "flipLabels": false,
 "type": "x",
 "chartsPerLine": 4
 }
 }
 },
 "start": ["!{User.Name}"]
 },

The dashboard now sets the initial filter to the logged-in user.

Country List Filter

Now create a list selector for Country using a SAQL step, just as with the list selector for Opportunity Owner.

The next thing is to set an initial selection for the country of the logged-in user. For this, we need to again modify the dashboard JSON.

Find the step used in the country list selector and modify and add the start parameter (in bold).

 "CountryFilter_1": {
 "type": "saql",
 "query": "q = load \"Oppty_Modified\";q = group q by 'AccountId.BillingCountry';q = foreach q generate 'AccountId.BillingCountry' as 'AccountId.BillingCountry', count() as 'count';q = order q by 'AccountId.BillingCountry' asc;",
 "isFacet": true,
 "useGlobal": true,
 "numbers": [],
 "groups": [],
 "strings": [],
 "label": "CountryFilter",
 "visualizationParameters": {
 "type": "chart",
 "parameters": {
 "visualizationType": "hbar",
 "autoFitMode": "none",
 "title": {
 "label": "",
 "subtitleLabel": "",
 "align": "center"
 },
 "theme": "wave",
 "showValues": true,
 "axisMode": "multi",
 "binValues": false,
 "bins": {
 "breakpoints": {
 "low": 0,
 "high": 100
 },
 "bands": {
 "low": {
 "label": "",
 "color": "#B22222"
 },
 "medium": {
 "label": "",
 "color": "#ffa500"
 },
 "high": {
 "label": "",
 "color": "#008000"
 }
 }
 },
 "dimensionAxis": {
 "showAxis": true,
 "showTitle": true,
 "title": "",
 "customSize": "auto",
 "icons": {
 "useIcons": false,
 "iconProps": {
 "column": "",
 "fit": "cover",
 "type": "round"
 }
 }
 },
 "measureAxis1": {
 "sqrtScale": false,
 "showAxis": true,
 "customDomain": {
 "showDomain": false,
 "low": null,
 "high": null
 },
 "showTitle": true,
 "title": "",
 "referenceLine": null
 },
 "measureAxis2": {
 "sqrtScale": false,
 "showAxis": true,
 "customDomain": {
 "showDomain": false,
 "low": null,
 "high": null
 },
 "showTitle": true,
 "title": "",
 "referenceLine": null
 },
 "legend": {
 "show": true,
 "showHeader": true,
 "inside": false,
 "position": "right-top",
 "customSize": "auto"
 },
 "trellis": {
 "enable": false,
 "showGridLines": true,
 "flipLabels": false,
 "type": "x",
 "chartsPerLine": 4
 }
 }
 },
 "start": "{{cell(UserData.result, 0, \"Country\").asObject()}}"
 },

The dashboard will now look at the UserData step and add the value of the country field into the country list selector.

Filter Won Opportunities

My number widget currently only filters on won opportunities. But since I want it to always just look at the won opportunities of the logged-in user I need to add another filter to the SAQL.

Find the query parameter and modify the filter to look like this:

"query": "q = load \"Oppty_Modified\";\nq = filter q by 'IsWon' == \"true\";\nq = filter q by {{column(UserData.result, [\"Id\"]).asEquality(\"OwnerId\")}};\nq = group q by 'all';\nq = foreach q generate sum('Amount') as 'sum_Amount';",

The number widget is now filtered by the result of the id from UserData step.

What the documentation does not say

Building this dashboard and testing out the binding I quickly found that it didn’t work. The reason being I build my steps using Aggregate Flex steps (default when building steps). As it turns out the start parameter only accepts bindings when using SAQL, Static Flex and SOQL step types. Big thanks to Sid Khoat with helping to debug that error.


2 thoughts on “How to filter dashboard by logged-in user”

  • 1
    Lucian on March 29, 2018 Reply

    Hi! Thanks for guide. Could you specify where exactly we add the lines? When I open up ctrl+e I am unsure where to place them. (I am also new to JSON and Analytics) 😀

    • 2
      Rikke on March 31, 2018 Reply

      For the SOQL step I think the easiest is to find (command+F) where it says “steps”: { put it in right after that. For the bindings, you need to find the step name and add it according to the description. Hope that helps.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.