Pages

Sunday, July 14, 2019

Einstein Analytics: Using XMD (Extended Metadata) for Percentage

Getting Started
When we configure Einstein Analytics Dataset, we not realize have been playing around with XMD (Extended Metadata).

When you explore dataset to a lens, and click "Fields" for:
- Rename
- Edit Values
- Number Format
Save the changes will update Dataset XMD.

Let us load a CSV file

Here is the blank structure of XMD file:
{
  "dataset": {},
  "dates": [],
  "derivedDimensions": [],
  "derivedMeasures": [],
  "dimensions": [],
  "measures": [],
  "organizations": [],
  "showDetailsDefaultFields": []
}


Let us create a dashboard based on that CSV file



Now, let us do "Fields" edit from lens with Rename, Edit Values, and Number Format. Here is the updated XMD file
{
  "dataset": {},
  "dates": [],
  "derivedDimensions": [],
  "derivedMeasures": [],
  "dimensions": [
    {
      "conditionalFormatting": {},
      "customActions": [],
      "field": "Column1",
      "label": "Name",
      "members": [
        {
          "label": "John",
          "member": "Lee"
        }
      ],
      "recordDisplayFields": [],
      "salesforceActions": []
    }
  ],
  "measures": [
    {
      "conditionalFormatting": {},
      "field": "Data_1",
      "format": {
        "customFormat": "[\"0.00%\",100]"
      }
    },
    {
      "conditionalFormatting": {},
      "field": "Data_2",
      "format": {
        "customFormat": "[\"#,###\",1]"
      }
    }
  ],
  "organizations": [],
  "showDetailsDefaultFields": []
}

Highlight notes:
- Yellow = rename
- Green = edit value
- Aqua = number format as Percent
- Fuchsia = number format as Number


Now, open back the dashboard (you need to close it first if the dashboard still open).




Usage in SAQL field
Let us do a simple SAQL to the widget to produce total Data-1 + Data-2 and Data-1 / Data-2.

q = load "percent_data";
q = group q by 'Column1';
q = foreach q generate 'Column1' as 'Column1', sum('Data_1') as 'sum_Data_1', sum('Data_2') as 'sum_Data_2';
q = foreach q generate 'Column1' as 'Column1', 'sum_Data_1' + 'sum_Data_2' as 'Total', 'sum_Data_1' / 'sum_Data_2' as 'Result_1';
q = order q by 'Column1' asc;

* notice that we still use Column1 as this is referred to the field API name

The SAQL produce two new fields which do not exist in the dataset: Total and Result_1.



However, we need to show them in the percentage format, so we need to add in the XMD file, because this is a measure field and it does not exist in the dataset, we should add it into derivedMeasures.

{
  "dataset": {},
  "dates": [],
  "derivedDimensions": [],
  "derivedMeasures": [
    {
      "conditionalFormatting": {},
      "field": "Total",
      "format": {
        "customFormat": "[\"#.##%\",1]"
      },
      "label": "Total",
      "showInExplorer": false
    },
    {
      "conditionalFormatting": {},
      "field": "Result_1",
      "format": {
        "customFormat": "[\"#.##%\",1]"
      },
      "label": "Result",
      "showInExplorer": true
    }  
  ],
  "dimensions": [
    {
      "conditionalFormatting": {},
      "customActions": [],
      "field": "Column1",
      "label": "Name",
      "members": [
        {
          "label": "John",
          "member": "Lee"
        }
      ],
      "recordDisplayFields": [],
      "salesforceActions": []
    }
  ],
  "measures": [
    {
      "conditionalFormatting": {},
      "field": "Data_1",
      "format": {
        "customFormat": "[\"0.00%\",100]"
      }
    },
    {
      "conditionalFormatting": {},
      "field": "Data_2",
      "format": {
        "customFormat": "[\"#,###\",1]"
      }
    }
  ],
  "organizations": [],
  "showDetailsDefaultFields": []
}

Notes:
1. add those field under derivedMeasures
2. field name can be different with label
3. you can set the field visibility in explorer


Here is the end result:



When you browse the dataset into a lens and open the Fields



JSON Online Editor
When you download XMD JSON file from Einstein Analytics and open in Notepad++, it would be show as one row only, where it is impossible for you to read.edit it. You can use JSON Online Editor to show as tree format, by copy and paste the JSON text into the left text area, click the right arrow to show as tree format.

Then to get human eye friendly JSON format, click left arrow and the text at the right will reformat.




Reference:


Wednesday, July 10, 2019

Einstein Analytics: Default Widget Style and Transparent Widget

Default Widget Style
When you drag a new widget into dashboard designer, in many times we need to change the widget style, from: background color, border, border color, border width, border radius. You also need to make sure to have a consistent style across the widgets. It is not an issue if you have only a few widgets, but when you have more and more widgets, this manual setting takes time and you may choose the wrong color or style.

Make use of Default style
When you expand Widget Style property, the 1st option and it is ticked by default is "Use defaults".


When this option is selected, all properties defined for Widget Style will be ignored.

Defined Default Widget Style
From Dashboard Properties (click  ... icon at top right)



Then you will found "Widget Default Properties", so configure the background color, border, border color, border width, border radius here.



So when you drag a new widget, the style will be applied, if not, make sure "Use default" is selected. If you change the default style from dashboard properties, all the existing widget will be adjusted to the new style.


Layout Properties
Just to make it clear, dashboard properties is not the same with layout properties, even both applied to the whole pages (if you have multiple pages in the dashboard). You can set layout properties by clicking gear icon with layout name (by default is called "Default"), at the top left of dashboard designer.


In layout properties, you can define:
- Name
- Number of Columns
- Row Height
- Cell Spacing: Horizontal and Vertical
- Max Dashboard Width
- Background Color: Grid and Gutter


Transparent Widget 
Transparent widget means, no background color for the widget, it will follow layout properties grid color. You can define the transparent background color as default widget style or to for independent widget.

For default widget style, from dashboard properties, click the background color, then select Custom scroll the bar to the left



You can implement the same for an individual widget, just make sure "Use defaults" is not selected. You can compare the following 2 widgets below, the upper one with transparent background, and the lower one with white background.





Monday, July 1, 2019

Salesforce: ForecastingQuota and ForecastingItem object

ForecastingQuota, this object stores an individual user’s or territory’s quota for a specified time period. The “Manage Quotas” user permission is required for creating, updating, or deleting quotas.  The “View All Forecasts” permission is required to View any user's forecast, regardless of the forecast hierarchy. Available in API versions 25 and greater. Object Id prefix is 0J9.

Once the quota added from the setup menu or Data Loader, it will be stored in this object and you can query it.



Query ForecastingQuota
SELECT Id, ForecastingTypeId, QuotaAmount, QuotaOwnerId, QuotaOwner.Name, StartDate FROM ForecastingQuota ORDER BY StartDate DESC



ForecastingItem, this is a read-only object used for individual forecast amounts. Users see amounts based on their perspectives and forecast roles. Available in API versions 26 and greater. Object Id prefix is 0G3.

The amounts users see include one of the following when forecasting in revenue: AmountWithoutAdjustments, AmountWithoutManagerAdjustment, ForecastAmount, OwnerOnlyAmount.

The amounts users see include one of the following when forecasting in quantity: QuantityWithoutAdjustments, QuantityWithoutManagerAdjustment, ForecastQuantity, OwnerOnlyQuantity.

Additionally, note that users:
- with the “View All Forecasts” permission have access to all ForecastingItem fields.
- without the “View All Forecasts” permission have access to all fields for their own subordinates and child territories.
Other users can see the ForecastingItem object, but not its records.

Let us see a sample of how the data is stored
SELECT Id, OwnerId, Owner.Name, ForecastAmount, ForecastCategoryName, ForecastingTypeId, PeriodId FROM ForecastingItem WHERE Period.StartDate = 2019-07-01 ORDER BY ForecastingTypeId, Owner.Name, ForecastCategoryName

** unfortunately we cannot do relationship query for ForecastingType

figure 1

Notes from the above query and the result:
- ForecastCategoryName is Forecast Category which is mapped to Stage
- There is no forecast period in this object, but PeriodId, so we can use it to determine Forecast period
- ForecastingTypeId is referred to Forecast Type configured

Query ForecastingType
SELECT Id, DateType, DeveloperName, MasterLabel, RoleType FROM ForecastingType WHERE IsActive = true

soql result from ForecastingType


Now let us see the data by running an Opportunity report:

figure 2


Then let us also see the Forecast result:

figure 3

Now, let us compare how is the data stored in ForecastingItem object, remember this object is read-only, so we can't update it manually.

  • Figure 1; row 1; Charlie User C in Jul 2019; for Forecast Category = Pipeline with Forecast Amount = $55,000 -- if you see figure 3, it is $0 for Charlie User C, but since Dave User D is reported to Charlie in the role hierarchy, so amount from Dave roll-up to Charlie
  • Figure 1; row 2; Charlie User C in Jul 2019; for Forecast Category = Closed with Forecast Amount = $295,000 -- look at figure 3, this amount is sum of $120,000 (Dave's amount) + $139,500 (Charlie's amount). If we further drill Charlie's amount of $139,500 -- look at figure 2, it is the sum of row 1 ($50,000) and row 2 ($89,500)
  • Figure 1; row 3; Dave User D in Jul 2019; for Forecast Category = Pipeline with Forecast Amount =$55,000 -- as no one report to Dave in the role hierarchy, this number is only his opportunity, drill down to figure 2, it is row 3 ($15,000) + row 4 ($40,000) 
  • Figure 1; row 3; this is simply Dave User D amount for Forecast Category = Closed
  • Figure 2; row 6; this is not calculated into ForecastingItem, because forecasting setting in this example in monthly, while row 6 closed date is Aug 2019.

In this sample, we are using monthly forecast setting, Salesforce also supports option to use Quarter forecast period too.


Reference:


Sunday, June 30, 2019

Salesforce: Forecast attainment report

Forecast is always a big part in CRM, and of course, it is available in Salesforce. Forecast has been gone through 3 types in Salesforce, with the current Forecast called "Collaborative Forecast", even you will see it just as "Forecast" in the setup menu, tab, and label.

We have discussed collaborative forecast many times in this blog, and today will discuss Forecast attainment report. But, here are blogs related to collaborative forecast:

Some of the above blogs probably not updated, particularly since the Lightning experience, e.g. to set user quota, there is a setup menu in Lightning called "Forecasts Quotas", this menu is not visible in Classic setup menu, but of course, you can still use Data Loader to load user quotas.


Here is the step to create a quota attainment report:

Create Custom Report Type
1. For Primary Object, select Forecasting Quotas
2. For Report Type Label, enter Forecasting Details
3. Add description, such as "Forecast Items and Quota"
4. Store in Forecast category
5. Select Deployed and Next
6. Click to relate another object, select object Forecasting Items
7. Select "A records may or may not have related B records"
8. Click Save



Add Quota from the Setup menu
1. Make sure you are in Lightning and "Show quotas" has been enabled in Forecasts Settings.
2. Navigate to the setup menu and search for Forecasts Quotas.


3. Select a forecast period and forecast type, then click "Show Quotas".
4. Search by username or roles; then enter the Quota and Currency for each user within the selected period.


Forecast
Let us see a sample of Forecast with some adjustment made by the manager and the user.


As you see there are a few manual adjustments marked with orange triangles.


Quota Attainment Report
Now, let us create a quota attainment report using report type created above in this blog.


Notes:
- Maria Jones quota for Q2-2019 is USD 80,000.00 -- this is "Quota Amount"
- Maria Jones closed won opportunity for Q2-2019 is USD 30,000 -- this is "Forecast Amount"
- There is an adjustment of USD 5,000.00 made by Maria Jones's manager for Q2-2019 for Booked category

- Mars Rover quota for Q2-2019 is USD 100,000.00 (Quota Amount)
- He has closed won opportunity USD 150,000.00 (Forecast Amount) which is 50% over his target
- There is an adjustment of USD 7,000.00 made by Mars Rover himself for Q2-2019 for Booked category



Reference: Trailhead module Create Forecasting Reports



Saturday, April 27, 2019

Einstein Analytics: Handling null values

This blog is not related to Null Handling in Measures. As per this article, null values in Dimensions are not completely supported in Einstein Analytics, however, we often deal with null values in many scenarios.

To prevent data quality issues, Einstein Analytics will disregard any fields in Salesforce (or columns in external data) that are entirely null.

Grouping with Null
Date field by default is null, when you use it in a chart for grouping, null value will not be shown. To overcome this, if you pull data from Salesforce, use defaultValue (e.g. 1900-01-01) to override in sfdcDigest.



Dimension field by default is null, when you use it in a chart for grouping, null value will not be shown. To overcome this, if you pull data from Salesforce, use defaultValue (e.g. NA, " is not needed) to override in sfdcDigest.

Measure field by default will become 0 for null value. We cannot use Measure field for grouping, but we can use it for filter.


Filter null records in SAQL
q = load "DTC_Opportunity_SAMPLE";
q = foreach q generate 'Account_Owner' as 'Account_Owner', 'Product_Name' as 'Product_Name', (case when 'Product_Name' is null then "" else 'Product_Name' end) as 'PM';
q = filter q by PM == "";
q = order q by 'Account_Owner';
q = limit q 10;

Count Not null records in SAQL
q = load "Lead";
q = foreach q generate (case when 'SFDC_Lead_ID__c' is null then "" else 'SFDC_Lead_ID__c' end) as 'SFDC_Lead_ID__c';
q = filter q by 'SFDC_Lead_ID__c' != "";
q = group q by all;
q = foreach q generate count() as 'count';

To group null as NA
q = load "DTC_Opportunity_SAMPLE";
q = foreach q generate coalesce('Product_Name',"NA") as 'Product_Name', count() as count ;
q = group q by 'Product_Name';
q = foreach q generate 'Product_Name' as 'Product_Name', count() as count;
q = order q by count desc;


Augment transformation cannot find the parent will cause Null


SAQL Expression in above computeExpression case when 'Acq.Industry__c' is null then "Parent not available" else 'Acq.Industry__c' end

Data in Salesforce

Result in Einstein Analytics

Notes from above screenshot:
  • 1st row for Parent Industry = NA, because we set the default value to NA in sfdcDigest.
  • 1st and 2nd row for Account Source = null, because we set the default value to null in sfdcDigest.
  • 1st row for Employees and 2nd row for Annual Revenue = 0, Einstein Analytics auto set 0 for Measure field with null value.
  • 3rd and 4th row for Parent Industry = Parent not available, this is because there is parent lookup value, we use computeExpression to set the value, this is different with 1st row, where the parent lookup value is available, but Industry for that parent record is null.


Reference:


Friday, April 26, 2019

Salesforce: Account Hierarchy columns & Recently Viewed columns

Can we customize the Account Hierarchy columns?

Classic - NO
This is the article and this is the idea.

Lightning - YES
Here is the article and here the steps:
  • From Setup, at the top of the page, select Object Manager.
  • In Account, click Hierarchy Columns and then click New button if never created, or Edit link to edit the columns.
  • You can include up to 15 columns.


When you create Hierarchy Columns, system will auto create a new list view called "Org_Account_Hierarchy" and added to the Accounts list view menu, you can rename it, but not to change the sharing setting, deleting this item resets the columns to the defaults.

By default -- no hierarchy column setup, account hierarchies display the same columns as the Recently Viewed Accounts standard list view. However, the list view columns don’t change when you customize the hierarchy columns.


Recently Viewed List
In Classic, when we click a tab, such as Accounts tab, by default it will show "Recent Accounts" with columns defined in Search Layouts - Tab



While switching to Lightning, click Accounts tab will bring open the "Recently Viewed" list view (if pinned list view has not changed), list view columns in "Recently Viewed" is defined in Search Layouts - Search Result. This view isn’t deletable, change the visibility, or rename.

However, you will found another list view with a similar name but include object name in Lightning, e.g. Recently Viewed Accounts, however, we cannot configure the columns for this view (until Summer '19 release), and unable to delete, change the visibility, or rename it. So the easier is just to ignore it.




Reference:


Wednesday, April 24, 2019

Einstein Analytics: SAQL in computeExpression with samples

computeExpression is one of the most powerful features in Dataflow in computeExpression, you can "add" fields without having to change the source data.



1. Get field value - TEXT
'CreatedBy.Role.Name'
 as the field name contains a dot, use ' before and after the field name

2. Set a text value - TEXT
"RoleName"
 always use " before and after the value for text

3. Get current date - DATE
now()

4. Get the first 18 characters - TEXT
substr('RECORD_ID', 1, 18)
 using substr() function, field name use enclosed by '

5. Get the first 18 characters with len() - TEXT
substr(UltimateParentPath, len(UltimateParentPath)-17,18)
using len() function

6. Combine text - TEXT
'CreatedDate_Year' + "-" + 'CreatedDate_Month' + "-" + 'CreatedDate_Day'

7. Combine text in case - TEXT
case when isDuplicate is null then 'Name' else 'Name' + " (" +'Username'+ ")" end
 using case function

8. Using multiple when in Case and compare Text - TEXT
case when 'Opportunity.Sales_Type__c' == "A" then "Type A" 
     when 'Opportunity.Sales_Type__c' == "B" then "Type B"  
     else "Type C" 
end

9. Check is Null - TEXT
case when 'Opportunity.Name' is null then "Yes" else "No" end
using is null keyword

10. Check is Not Null - TEXT
case when 'OptySplit.SplitOwnerId' is not null then 'OptySplit.SplitOwnerId' else 'OwnerId' end
using is not null keyword

11. Use && and ! as alternative - TEXT
case when 'Owner.Name' is null && !('Queue.Name' is null) then "Queue" 
     when !('Owner.Name' is null) then "User" 
     else "N/A" 
end

12. Use && and ! as alternative to get field value - TEXT
case when 'Owner.Name' is null && !('Queue.Name' is null) then 'Queue.Name' 
     when !('Owner.Name' is null) then 'Owner.Name' 
     else "N/A" 
end

13. Simple bucketing - TEXT
case 
  when Value == 0 then "[1] 0"
  when Value <= 1000000 then "[2] 0-1M"
  when Value <= 25000000 then "[3] 1M-25M"
  when Value <= 100000000 then "[4] 25M-100M"
  else "[5] 100+M"
end

14. Get numeric value from field - NUMERIC
case 
  when Type_Data is not null and Type__c = "Type A" then Annual_Data
  when Type_Value is not null and Type__c = "Type B" then Annual_Value
end
else keyword is not a must in case, ' is not a must if the field does not contain dot

15. Check Neglected Case - TEXT
case when DaysSinceLastActivity >= 60 then "true" else "false" end

16. Check Is Lost - TEXT
case when 'IsClosed' == "true" && 'IsWon' == "false" then "Yes" else "No" end
there is NO BOOLEAN type in Einstein Analytics, so always enclosed wth "

17. Using IN - TEXT
case when 'Opportunity.StageName' in ["Stage 1", "Stage 2", "Stage 3", "Stage 4"] then "true" else "false" end
 using in[] function

18. Check is Overdue - TEXT
case when ('IsClosed' == "false") && (daysBetween(toDate(substr('ActivityDate', 1, 10), "yyyy-MM-dd"), now()) > 0) then "true" else "false" end
using daysBetween() function

19. Get Days Overdue - NUMERIC
case when 'IsOverdue' == "true" then daysBetween(toDate(substr('ActivityDate', 1, 10), "yyyy-MM-dd"), now()) else 0 end

20. Check is between 2-30 days - TEXT
(case when date('TIMESTAMP_DERIVED_Year', 'TIMESTAMP_DERIVED_Month', 'TIMESTAMP_DERIVED_Day') in ["30 days ago".."2 days ago"] then "yes" else "no" end)
using date() funtion

21. Check is Yesterday - TEXT
(case when date('TIMESTAMP_DERIVED_Year', 'TIMESTAMP_DERIVED_Month', 'TIMESTAMP_DERIVED_Day') in ["1 day ago".."current day"] then "yes" else "no" end)

22. Check is Past Due - TEXT
case when IsClosed == "false" && (toDate(CloseDate_sec_epoch) < now()) then "true" else "false" end
using toDate() and _sec_epoch field

23. Duration in Second - NUMERIC
date_diff("second", toDate(ValidFromDate_sec_epoch), now())
using date_diff() function

24. Check Is Closed - TEXT
case when daysBetween(toDate(ActivityDate_sec_epoch), now()) >= 0 then "true" else "false" end

25. Get days since last activity - NUMERIC
case    
   when LastActivityDate is null then daysBetween(toDate(LastModifiedDate_sec_epoch), now())   
   when LastModifiedDate > LastActivityDate then daysBetween(toDate(LastModifiedDate_sec_epoch), now())   
   else daysBetween(toDate(LastActivityDate_sec_epoch), now()) 
end

26. Get Past Due Date - NUMERIC
case when IsClosed == "false" && (toDate(CloseDate_sec_epoch) < now()) then daysBetween(toDate(CloseDate_sec_epoch), now()) else 0 end

27. Get Opportunity Age - NUMERIC
case when IsClosed == "false" then daysBetween(toDate(CreatedDate_sec_epoch), now()) else daysBetween(toDate(CreatedDate_sec_epoch),toDate(CloseDate_sec_epoch)) end

28. Get Lead Age - NUMERIC
case when ('IsConverted' == "false") then daysBetween(toDate(CreatedDate_sec_epoch), now()) else daysBetween(toDate(ConvertedDate_day_epoch), toDate(CreatedDate_day_epoch)) end

29. Get Case Duration - NUMERIC
case when ('IsClosed' == "true") then ('ClosedDate_sec_epoch' - 'CreatedDate_sec_epoch')/86400 else ('CurrentDate_sec_epoch' - 'CreatedDate_sec_epoch')/86400 end

30. Get Opportunity Age - NUMERIC
case
   when ('ConvertedOpportunity.Name' is null) then 0 
   when ('ConvertedOpportunity.IsClosed' == "false") then ('CurrentDate_sec_epoch' - 'CreatedDate_sec_epoch')/86400  
   else ('ConvertedOpportunity.CloseDate_sec_epoch' - 'CreatedDate_sec_epoch')/86400 
end

31. Converting Created Date to PST - DATE
toDate('CreatedDate_sec_epoch'-3600*8)

32. Using starts_with(), ends_with, and lower() to compare string - TEXT
case
  when starts_with(lower(Subject),"call") then "Call"
  when ends_with(lower(Subject),"call") then "Call"
  else "Others"
end
 the one contain full string must be at left, it is case sensitive, so use lower() to help

33. Use matches() for contain - TEXT
case when "abcd" matches "abc" then "found" else "not found" end
 the full sentence must be at left, this operator is not case-sensitive, requires at least two characters

case when 'Product_Name' matches "cable" then "found" else "not found" end 
 this will work

case when "cable" matches 'Product_Name' then "found" else "not found" end 
 this is not allowed with error Invalid function argument: 'Product_Name', the second operand must be text.

case when !('Product_Name' matches "cable") then "a" else "b" end 
 use ! as not

  

Reference:

Einstein Analytics: using Allow disjoint schema to transform dataset

Here is the use case, we have multiple columns for each type to store value, this cause we can't really easily build a chart when the values are spread across many columns.

Solution: to transform the data source by splitting into many rows and using 1 column.



Dataflow



Inside computeExpression computeTYPE1 nodes:

Inside Type_TYPE1 Computed Field:
this is text, which is the field name

Inside Value_TYPE1 Computed Field:
this is numeric, which is the field value


Do the same for computeExpression Type 2 and Type 3. Then, combine all the data using append node


Once we have all the values spread across rows, use sliceDataset transformation to drop the original Type 1, Type 2, Type 3 fields.



Reference




Monday, April 8, 2019

Salesforce: User current app

Question: is there a way to check what is the user current app?

Answer: yes, but only for Lightning.


UserAppInfo
Since API version 38.0, Salesforce introduces an object called UserAppInfo, this object stores the last Lightning app users logged in to.

Sample query: SELECT Id, UserId, AppDefinitionId, FormFactor, CreatedById, CreatedDate, LastModifiedById, LastModifiedDate FROM UserAppInfo WHERE UserId = '00580000004JEfS'


Notes:
- AppDefinitionId: the ID of the last Lightning app that the user logged in to.
- FormFactor: The relative size of the app as displayed, values are:
     Small—suitable for a small device like a mobile phone
     Medium—suitable for a tablet
     Large—suitable for a large display device, like a monitor

Since AppDefinitionId is updateable, this means we can mass update the users' app.


AppDefinition
This object represents the metadata of an app and its navigation items. This object is available in API version 43.0 and later.

Sample query: SELECT DurableId, Label, UiType, Description, DeveloperName, MasterLabel, NavType, UtilityBar FROM AppDefinition ORDER BY Label



Notes:
- DurableId: instead of Id, DurableId represent App Id as in use for UserAppInfo
- UiType: option: Aloha for Classic, and Lightning
- NavType: option: Standard and Console
- UtilityBar: only available for Lightning


Reference:


Friday, March 29, 2019

Salesforce: Finding Reports and Dashboards from Private folder

Use case: unable to delete report because it used in dashboards.

When you try to delete the report, Salesforce returns the following error:

Report cannot be deleted
One or more dashboards depend on this report. Please delete the dashboard components referring to this report and try again. 

The issue is, it does not tell us which dashboard content report that we want to delete.

So, let us find the related dashboards.

1. Create Report Type 
Reports (A) with at least one related record from Dashboard Components (B)
You can add Dashboard information to this report, such as:
- Dashboard ID
- Dashboard Running User (run as specified user, or let authorized users change running user)
- Folder
- Running User (this is viewing user name)
- Running User Active
- Title

You may find in some of the reports, there is no Dashboard info, even the report type is Reports with at least one related record from Dashboard Components, this is pretty confusing, right?

Possibility (1)
The dashboard has been deleted, you are right, however, once the dashboard is deleted (in recycle bin), the system allows you to delete the report.


 If you see from the above screenshot, the first line does not have dashboard info, this because the dashboard is deleted, and system allows me to delete the report, so this does not fit our use case.

Possibility (2)
The dashboard is stored in someone Private folder.


The difference here, we can see dashboard Title and no other info. For this case, we cannot delete the report.




2. Query from Private folders
For the case of reports used as the source of dashboards that stored in someone private dashboard, you need to query from Private folder. You need to have this permission Manage all private reports and dashboards, then you can query dashboard and report in Private folder. You also need to add  'allPrivate' query scope to find Reports and Dashboards in private folders.

To return reports in private folders that haven't been run for more than one year:
SELECT Id, OwnerId FROM Report USING SCOPE allPrivate WHERE LastRunDate < LAST_N_DAYS:365

To query reports inside a specific User's private folder:
SELECT Id FROM Report USING SCOPE allPrivate WHERE OwnerId = '005A0000000Bc2deFG'

To query all dashboards stored in User's private folder:
SELECT Id, Title, FolderName, FolderId, CreatedById, LastModifiedById FROM Dashboard USING SCOPE allPrivate ORDER BY Title 



Note:
For Dashboard:
- You should look at FolderId - this is where the dashboard or report stored.
- The dashboard or report can be created by someone else, so don't look at CreatedById.

For Report:
- Looks for OwnerId, this will tell you who owned the report stored in the private folder.



ReferenceDelete Reports and Dashboards from personal or private folders



Tuesday, March 5, 2019

Einstein Analytics: deployment with Change Set

As Einstein Analytics is deeply integrated with the Salesforce platform, we can deploy Einstein Analytics asset as a Change Set from the Salesforce platform.



Here are a few finding related to Einstein Analytics asset deployment with Change Set:

1. Change Set able to deploy Dataflow to target org, event in the target org is not enable for sync. You need to enable sync for the ability to create dataflow manually in Data Manager.

2. For dashboard and lens deployment, if the app does not exist yet in the target org., you need to deploy the app as a component within the same Change Set, otherwise, the deployment will fail.

3. Change Set will deploy Dataset, but it will not move the data, you need to re-run dataflow or re-export the data, otherwise, the Dataset will not visible in Analytics Studio. However, dashboard and lens will visible in Analytics Studio, but you can't open them until the dataset is visible in Analytics Studio.

4. Change Set able to deploy Lens and Dashboard without Dataset.




ReferenceMigrate Analytics Assets with Change Sets



Sunday, March 3, 2019

Salesforce: Query Fields Permission

In the previous blog Using Permission Set to Query User Permission, we discussed query on PermissionSet and PermissionSetAssignment to query on permissions related to the user permission, at the end of the blog we also introduce query to ObjectPermissions object to get permission related to Object.

In this blog, we are going to introduce another object called FieldPermission. As you know, basic fields accessibility for a user is determined by the user Profile, then extra permission can be given to the user thorough Permission Set. So, a query to FieldPermission will give you an idea of why/how a user able to access a specific field, and what is the permission to that field (Read or Edit).

SELECT Id, ParentId, Parent.Name, SobjectType, Field,PermissionsEdit,PermissionsRead FROM FieldPermissions  WHERE SobjectType = 'Account' AND Field = 'Account.Active__c' ORDER BY Parent.Name

The sample result from the above query:


The main field from above query is ParentId, this field is referred to PermissionSet object, so you see the result of Parent.Name is PermissionSet.Name, the values are contained for both Profile and Permission Set.

For PermissionSet.Name value start with X00e, it is a Profile (includes Standard and Custom profile), while the one not starts with X00e is PermissionSet.

From the above screenshot, let us check if Activate_Contract_2 permission set gives extra permission for field Active__c in Account object:



ReferenceFieldPermissions



Sunday, February 24, 2019

Salesforce: Using Custom Field for Forecasts

This blog only applicable for Collaborative Forecast, as per latest announcement, Customizable Forecast is scheduled for retirement as of Summer ’20.

By default, Salesforce will forecast using Amount field from Opportunity for Revenue forecast. However, to fit your business needs, you can add additional forecast type using custom fields in Opportunity, and the fields must be a currency field.

1. Opportunity Split
In this blog, I will add a custom currency field from Opportunity called Extra Income. I'll select the field 'Extra Income' and type in 'Extra' as Split Label.


Tips: on the Split Type, the Totals 100% must be ticked, otherwise, forecast with the custom fields will not work.


2. Forecast Setting
Now, I need to configure forecast setting, click the link "+ Add another forecast type" then select Extra.


Then, you select a Forecast Measurement (Revenue or Quantity) and select fields to show in the Opportunity List.
You need to click Save button to save, otherwise, it will not save, even you have click OK button in many screens.


Forecasts tab
Now, let us see if this will work.

This is the default forecast based on Amount, it called Opportunity Revenue.



Now, let us flip to the new Extra forecast we just create, click the gear icon on the top right.



Now, let us forecast with our new forecast type "Extra"




ReferenceEnable Custom Field Forecasts in Collaborative Forecasts



Page-level ad