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.


11 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.

      • 3
        Seshubabu on July 23, 2019 Reply

        Hi Rikke thanks for the guide.
        I’m also facing the same issue mentioned above by Lucian. Even after putting the SOQL code after “Steps”:{ as mentioned by you, i’m getting the error. Plz help me how to resolve this?

  • 4
    Roberto Moreno on September 14, 2018 Reply

    Hello, can I display the profile photo based on the logged-in? Thanks in advance!

  • 5
    Jordan Baucke on April 29, 2019 Reply

    @rikke,

    Thanks for the guide – guide was very helpful adding filters based on SAQL (especially for the currently logged in user!)

    I’m curious if there is ANY documentation on functions like “.asEquality()” “.asObject()” or “column[(query).result]”

    I’m trying to basically use the same effect on a dashboard wide filter (https://developer.salesforce.com/docs/atlas.en-us.bi_dev_guide_json.meta/bi_dev_guide_json/bi_dbjson_filters_parameters.htm), or compact filter syntax within a step!

    Thanks,

    Jordan

  • 7
    Vance Lopez on August 17, 2019 Reply

    Awesome! Thanks for this.

  • 8
    Debbie Jensen on August 27, 2019 Reply

    Hi Rikke – your blogs are sooo helpful to me! Question regarding doing this for a global filter? Seems I should be able to set the value of the locked Global filter for the dashboard to the running user. I have this:
    “filters”: [
    {
    “dataset”: {
    “id”: “0Fb0z000000g8xZCAQ”,
    “name”: “AllInvoicesByDealer”,
    “url”: “/services/data/v46.0/wave/datasets/0Fb0z000000g8xZCAQ”
    },
    “dependent”: false,
    “fields”: [
    “Account.AccountOwner.Name”
    ],
    “label”: “Account Owner”,
    “locked”: false,
    “operator”: “in”
    }
    Should be able to set “value”: [] to something that contains a value that equals “$User.Name”, but I’m unable to find the correct syntax. Any suggestions? (The same dataset is used throughout the dashboard, so I don’t want to filter at each step). Thanks in advance!!

    • 9
      Rikke on August 30, 2019 Reply

      Well, you would set a start value first but I’m pretty sure bindings in global filters are not supported.

  • 10
    Lutfur Rashid Russell on November 14, 2019 Reply

    Hi Everyone,
    I am new to SAQL and verymuch needed support if i am missing some basic.

    What i was planning to do is, i want to specify few users who would be using the dashboard and it would filter the territory as it would be assigned, rest will see the dashboad as blank.

    I have used the SOQL to pull the email address to ensure unique value. but every time i use filter, it gives error “Unknown IDTOKEN: AccountId.L4Name__c” but dataset has that fileds. I tried using same dataset twice for union but same error.

    if i dont use grouping for market (AccountId.L4Name__c), it doesn’t give me error and return the value in ‘Territory’.

    Here is the code:
    q = load “OIF”;
    q = group q by ‘AccountId.L4Name__c’;
    q = foreach q generate “{{cell(UserData.result,0,”Email”).asObject()}}” as ‘Status_Q’;
    q = foreach q generate case
    when ‘Status_Q’ == “name1@abc.com” then “Market-1”
    when ‘Status_Q’ == “name2@abc.com” then “Market-2”
    when ‘Status_Q’ == “name3@abc.com” then “Market-3”
    when ‘Status_Q’ == “name4@abc.com” then “Market-4”
    else “No Market” end as ‘Territory’;
    q = filter q by ‘AccountId.L4Name__c’ == ‘Territory’;

    Thanks so much in advance!

    • 11
      Lutfur Rashid Russell on November 17, 2019 Reply

      Already fixed it, by adding ‘AccountId.L4Name__c’ in foreach generate and first().

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.