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.
Written by
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) 😀
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.
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?
Hi Seshubabu,
Did you find the resolution for that? I am also facing the same issue.
Hi Rikke,
I am also getting the error even after adding the SOQL step after “steps”: {. Can you help?
Hello, can I display the profile photo based on the logged-in? Thanks in advance!
@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
I’ve been working on a blog series – not complete yet – but what you are asking about should be covered in the first 3 parts of the demystifying bindings series: https://www.salesforceblogger.com/all-about-bindings/
Awesome! Thanks for this.
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!!
Well, you would set a start value first but I’m pretty sure bindings in global filters are not supported.
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!
Already fixed it, by adding ‘AccountId.L4Name__c’ in foreach generate and first().
HI Rikki,
Thank you so much for the blog. There is lot of information in the blog. Is there any way that I can show a Saved Lens when clicking on the dashboard widget and should reference the same widget?
Thank you for your time
HI Rikki,
Thank you so much for the blog. There is lot of information in the blog. This blog is so helpful. Is there any way that I can show a Saved Lens when clicking on the dashboard widget and should reference the same widget?
Thank you for your time
Harry, kind of but not really. What most people do is just explore that widget at runtime and save it as a Lens. Then go back to the dashboard and create a Link widget and point it to the lens you just created.
Hello Rikke,
In the “values” section how is the syntax to show the value of “name” field just read ?
“values”: [
{
“User Name”: “I need the value here ”
}
Is there a way to disable the list filter ?
@rikke
Hi Rikke, Thanks for your guide.
I do have one question :
Is it possible to use binding to make a Page-in-Layout button dynamic?
What I am trying to do, is a dynamic button that redirects to one or another page according to the binding result. Is it doable?
For example If user’s country = USA , when they click it, it redirect to page 5 of the dashboard if not it goes to page 4.
i tried to bind the value “Name” with the link id but it doesnt work.
No, it won’t work. The moment you save the dashboard with the page binding it will not parse a value just the actual binding and that’s not an approved reference, so you’ll get an error.
@rikke
Thanks for your response. In fact I was trying every possible way…but I couldn’t make it work..
Do you might have any suggestion?
Essentially some user should see some pages (or visuals) and some others should not this is what I am trying to achieve
Thanks in advance
Kudos
Absolutely worthless, long, and difficult to understand. Not only is this a regurgitation of your code, but there is no explanation as to what ACTUALLY needs to be done. Many times you say list filter but show a chart. Many times you say use this but you don’t show how to setup the binding. Pasting in your code I keep getting JSON invalid. Thanks for nothing.
Hi Rikki,
Is it possible to display case data based on queue? Your input is greatly appreciated.