Monday, September 10, 2018

Einstein Analytics: Embed Dashboard to Salesforce Classic

As you see in the previous blog Embed Report Chart to Page Layout, adding report charts to record detail page is pretty simple. But, embedding Einstein Analytics dashboard to record detail page is different.

You can add Einstein Analytics dashboard to record detail page similar with Report Chart in Classic, but it will not show when you open the same record in Lightning. In this blog, I'll share on adding Einstein Analytics dashboard to record detail page in Classic only.

When you edit the page layout, notice "Wave Analytics Assets" and all available dashboard can be added to the page layout.

But, not as simple as adding report charts to the page layout, when adding Einstein Analytics dashboard to the page layout to show relevant record being viewed, you need to play a bit with JSON to link record Id to Einstein Analytics dashboard.

Here is the complete format of JSON to add in the field mapping, all words in yellow highlight are keywords.
"datasets": {
    "dataset1": [
        {"fields": ["field1"],
           "selection": ["$selection1", "$selection2"],
           "filter": {"operator": "operator1", "values": [["$value1, $value2"], ["$value3", "$value4"]]}
        {"fields": ["field2", "field3"],
           "selection": ["$selection3", "$selection4"]
    "dataset2": [
        {"fields": ["field4"],
           "filter": {"operator": "operator2", "values": ["$value5"]}

Sample: when open account page, auto filter Einstein Analytics dashboard with Account being viewed. Add the JSON into Field mapping of Asset Properties.

Let us analyze the JSON code:
1:  {  
2:   "datasets": {  
3:     "OptyWithAcct": [  
4:      { "fields": ["AccountId"],  
5:        "selection": ["$Id"]  
6:      }  
7:     ]  
8:   }  
9:  }  
- line 2: datasets is the keyword, nothing to change
- line 3: OptyWithAcct is the dataset name
- line 4: fields is a keyword, AccountId is API field name in the dataset
- line 5: selection is a keyword, $Id is the API field name from the object, not from the dataset

Let us see the result:

1. The dashboard is automatically filtered with current Account Id.
2. You need to have Account Id as list filter in the dashboard, otherwise, we can't filter the dashboard.

ReferenceEmbed Analytics Dashboards in Salesforce Classic Pages

Salesforce: Embed Report Chart to Page Layout

Adding a chart from report to record page is pretty simple, as long as you have permissions to create reports, store report to the public folder, and permission to edit record page layout.

Here are few items to check when embedding report chart to object page:
- the report is in a folder that’s shared with users who need access, not in private folder.
- the source report format is summary or matrix.
- the source report has a chart.

You can follow this document to add report chart to a page layout Add a Report Chart to a Page Layout.

Usually, when you embed report charts to page layout, you want to show the relevant data to the current record, such as Total Opportunity Amount by Stage for current Account, for this case, you can use Opportunity standard report type, as Account Id is available in Opportunity report type.

Note: for this scenario, Account Id or Account Name does not need to be present in the report.

When editing the page layout to add report chart, you can select the chart size, appearance, data refresh, and the data filter. Check out this document Filtering Report Charts to Show Data Relevant to the Page.

However, you can add only up to two report charts on a page. The good news, embedded Report Chart in page layout will work in both Classic and Lightning, even you can't see the Refresh button in Lightning, so the best option is to enable "Refresh each time a user opens the page" in the chart properties.


Sunday, September 9, 2018

Einstein Analytics: How to rename Dataflow

You can change dashboard label/title, by open the dashboard click edit, then click the pencil icon next to the title to change it.

Or, by open the dashboard in JSON mode, then change the "label", by default, it would be at the 2nd row, but to get the dashboard API name, follow this blog Dashboard API Name.

Remember you need to save the dashboard for both above options. Another method to change dashboard label is, when you click Save icon to save dashboard, the system will pop up a window and you can change dashboard title from here (this will rename dashboard, not save as a new dashboard).

To change lens label, open the Lens in JSON mode, you will find them at the top, by default, it would be at 3rd row from the top with "label", remember to save the lens. When you edit Lens in JSON, you will see Lens name too, but unfortunately, you can't save it.

Same as the dashboard, you can rename lens title when click Save icon, change lens title will rename the lens, not save as a new lens.

Similar to the dashboard, to rename Dataset label, edit the dataset, then click the pencil icon next to the title to change it. When you edit the dataset, you also can move the dataset to the new app.

To rename an app, simply open the app, then click the pencil icon next to the title to change it. 

As of Winter '19 release, we cannot rename dataflows. When edit a dataflow, it will not give us the option to change dataflow name/title. You can vote this idea in IdeaExchange.

However, if you really need to change the dataflow label, here is the workaround:
- Download Dataflow JSON to your local computer
- Create new Dataflow
- Click "Upload JSON" button and upload downloaded JSON file
- Click "Update Dataflow" button to save
- Once the new dataflow is verified, delete the old dataflow.

Wednesday, September 5, 2018

Case-sensitive VLOOKUP for Salesforce Record Id

Salesforce record Id will always return 15 characters across all objects, and the Id is case sensitive. Of course, if you have admin access, you can create a formula field CASESAFEID(Id) and this will return 18 characters which are case-insensitive.

But, if you have to work with case-sensitive 15 characters and do VLOOKUP in Excel, you need to do extra work, as you probably know that one of the limitations of VLOOKUP is case-insensitive, it means the case is not a consideration in the lookup process, example: 0038000001c9YsM is essentially the same as 0038000001c9Ysm. Hence, VLOOKUP will consider that they are the same and return the first matched, which is totally wrong.

There are a few options you can do for VLOOKUP:

1. Convert 15 to 18 characters
We have shared this in a blog Convert ID from 15 to 18 characters with Excel formula, so you need to have an additional column in the source and lookup data, and use that column for VLOOKUP.

2. Convert to ASCII
This is pretty similar to option 1 above, instead of converting 15 characters case-sensitive to 18 characters case-insensitive, we convert the record Id into ASCII using CODE()Microsoft Excel CODE function returns the ASCII value of a character or the first character in a cell.

=CODE(MID(A2,1,1)) & CODE(MID(A2,2,1)) & CODE(MID(A2,3,1)) & CODE(MID(A2,4,1)) & CODE(MID(A2,5,1)) & CODE(MID(A2,6,1)) & CODE(MID(A2,7,1)) & CODE(MID(A2,8,1)) & CODE(MID(A2,9,1)) & CODE(MID(A2,10,1)) & CODE(MID(A2,11,1)) & CODE(MID(A2,12,1)) & CODE(MID(A2,13,1)) & CODE(MID(A2,14,1)) & CODE(MID(A2,15,1))

You need to convert both source and column data Record Id to ASCII, then use it for VLOOKUP().