Pages

Monday, September 9, 2019

Salesforce: Query Multi-Currency Field

When you have multi-currency enabled in your Salesforce org., by default you will see the converted value in the page layout, list view, and report.

Page Layout
My user currency is USD and Corporate currency is USD too.



If I change my currency to SGD



List View
The same goes to List View, it will show the amount in the opportunity currency and the value in your currency in brackets.


Report
In the report, Salesforce gives the option to get the values in the Opportunity currency and in the converted amount -- which is the corporate currency.



SOQL
When you query Salesforce currency fields, SOQL will always return currency values as defined in the CurrencyIsoCode.



Format() and convertCurrency()
We can use Format() and convertCurrency() functions for currency field in SOQL:
- Use FORMAT with the SELECT clause to apply localized formatting to standard and custom number, date, time, and currency fields, the format applied these fields reflect the appropriate format for the given user locale.
- Use convertCurrency() in the SELECT clause to convert currency fields to the user’s currency.

Looks at this sample: SELECT Id, CurrencyIsoCode, Amount, convertCurrency(Amount) UserAmount, FORMAT(amount) TextAmount, FORMAT(convertCurrency(amount)) convertedCurrency FROM Opportunity order by currencyisoCode



You can’t use the convertCurrency() function in a WHERE clause. If you do, an error is returned, but you can use the following syntax to convert a numeric value to the user’s currency from any active currency in your org. WHERE Object_name Operator ISO_CODEvalue

e.g. SELECT Id, Name FROM Opportunity WHERE Amount > USD5000
In this example, opportunity records are returned if the record’s currency Amount value is greater than the equivalent of USD5000. For example, an opportunity with an amount of USD5001 is returned, but not JPY7000.



How to get currency value in corporate currency using SOQL?
In Spring ’18 release, Salesforce introduces new formula functions ADDMONTHS, CURRENCYRATE, MCEILING, MFLOOR and WEEKDAY. So this purpose, we can make use of CurrencyRate() function.

CURRENCYRATE returns the conversion rate to the corporate currency for the given currency ISO code. If the currency is invalid, returns 1.0.

We can create a simple formula field
Amount / CURRENCYRATE(TEXT(CurrencyIsoCode))

Here is the result:


If you implement dated exchange rate, CurrencyRate() function do not support it yet, it will always use standard exchange rate, unless you always keep the conversion rate aligned.



Reference:


Tuesday, September 3, 2019

Einstein Analytics: Result Binding

There are 2 types of binding in Einstein Analytics:
1. Selection: the query result based on user selection
2. Result: the query result based on the changes in other steps

We have discussed selection binding in the previous blogs: here, here, and here. Now let us looks at result binding.

Here is the sample for this blog:
- We have 2 datasets which are not really linked, but both have user Id and Fiscal
- Opportunity Split dataset have Close Date, which can be used as a toggle


Steps:
1. Create Static Step to have Previous Quarter, Current Quarter and Next Quarter
                            "Static_Period_1": {
                "broadcastFacet": true,
                "label": "Static_Period",
                "selectMode": "singlerequired",
                "start": {
                    "display": [
                        "Current Quarter"
                    ]
                },
                "type": "staticflex",
                "values": [
                    {
                        "display": "Previous Quarter",
                        "value": -1
                    },
                    {
                        "display": "Current Quarter",
                        "value": 0
                    },
                    {
                        "display": "Next Quarter",
                        "value": 1
                    }
                ]
            }

2. For wizard for Opportunity Split dataset, add selection binding to filter the result based on the toggle.
           "query": {
                    "values": [
                        "Fiscal",
                        "Id",
                        "User_Id",
                        "Close_Date",
                        "Amount"
                    ],
                    "filters": [
                        [
                            "Close Date",
                            [
                                [
                                    [
                                        "fiscal_quarter",
                                        "{{cell(Static_Period_1.selection,0,\"value\").asString()}}"
                                    ],
                                    [
                                        "fiscal_quarter",
                                        "{{cell(Static_Period_1.selection,0,\"value\").asString()}}"
                                    ]
                                ]
                            ],
                            ">=<="
                        ]
                    ],
                    "order": [
                        [
                            "Fiscal",
                            {
                                "ascending": true
                            }
                        ]
                    ]
                }

* Static_Period_1 is the step name for static step, see (1)

3. For wizard for Forecast Quota dataset, add result binding to filter the result based on the column in the Opportunity Split step.
          "query": {
                    "values": [
                        "Fiscal",
                        "User_Id",
                        "Quota"
                    ],
                    "filters": [
                        [
                            "Fiscal",
                            [
                                "{{cell(lens_1.result,0,\"Fiscal\").asString()}}"
                            ],
                            "in"
                        ]
                    ],
                    "order": [
                        [
                            "Fiscal",
                            {
                                "ascending": true
                            }
                        ]
                    ]
                }

* Lens_1 is the step name contains selection binding, see (2)
* Fiscal is the column name in Lens_1 step


For this blog sample, when user selects "Next Quarter" in toggle, the Opportunity Split data will be filtered based on selection defined in static step, then Forecast Quota data will be filtered based on Fiscal changed in Opportunity Split step.



Here is the complete JSON file.



ReferenceResult Binding



Sunday, September 1, 2019

Salesforce: Forecast with Opportunity Split

This is the continuation of previous blog Setup Forecast Quota, in this blog, we will share about Forecast with Opportunity Split Overlay.

1. Opportunity Team and Opportunity Splits


Here is the scenario:
Two users, both users have been enabled for Forecast
1. Johan Forecast is the manager
2. George Mann report to Johan Forecast

Two closed-won opportunities with Overlay split:

opportunity #1

opportunity #2


2. Forecast
Let us the result see in the Forecast tab:


Highlight legend:
- Yellow: this is quota as we discussed in the previous blog
- Pink: this is the forecast item for Closed forecast
- Green: this is summary from all subordinates, including the manager
- Blue: USD 305,500.00 comes from opportunity #2 which is the total amount for the 2nd and 3rd; USD 75,000.00 is from opportunity #1


3. SOQL
SELECT Id, OwnerId, ForecastAmount, Owner.Name, PeriodId, ForecastingTypeId FROM ForecastingItem WHERE ForecastCategoryName = 'Closed' AND ForecastingTypeId = '0Db2v000005aTHcCAM'


Summary:
  • The Forecast Amount in ForecastingItem is auto-populated from Opportunity Split Amount based on the Forecast Type and Forecast Category.
  • The Period Id in ForecastingItem is auto-populated based on Opportunity Closed Date.
  • Each user will only have 1 line for ForecastingItem in a period for a Forecast Type.
  • If you have multi-currencies enabled, only 1 currency will be returned in the query result, this currency is based on defined Corporate Currency.
  • The Forecast Amount from ForecastingItem in SOQL is roll-up to the manager, and managers numbers will roll-up to the managers' manager.

Forecast Manager in Forecast Hierarchy

Sample:
There are 2 users under Staff 1: Song Lee & Free Man

Forecast tab

SOQL
SELECT Id, OwnerId, ForecastAmount, Owner.Name, PeriodId, ForecastingTypeId FROM ForecastingItem WHERE ForecastCategoryName = 'Closed' AND ForecastingTypeId = '0Db0k000000076kCAA' ORDER BY ForecastAmount



What will happen if we remove Maria Ann as Forecast Manager for the role hierarchy General Manager?

Forecast tab

Notice:
1. The Amount is no longer rollup from Maria Ann subordinates'. Forecast managers see forecast rollups from users below them in the forecast hierarchy.
2. Click the arrow next to label 'Maria Ann' will not drill down to the subordinates level.
3. Jack Bob numbers are only from Linda Yie as Maria Ann not contributing anything.
4. The number from Song Lee and Free Man are not roll-up to anyone.

SOQL
SELECT Id, OwnerId, ForecastAmount, Owner.Name, PeriodId, ForecastingTypeId FROM ForecastingItem WHERE ForecastCategoryName = 'Closed' AND ForecastingTypeId = '0Db0k000000076kCAA' ORDER BY ForecastAmount



If Maria Ann have her owned Opportunity Splits


Same for SOQL result, Maria Ann will have USD 18,000.00 for the Forecast Amount.


Now, let us remove Allow Forecasting from Maria Ann

Maria Ann does not appear in the Forecast tab at all.



SOQL:


Remember that Maria Ann still owned Opportunity Split, but she does not appear in both Forecast and SOQL.


Summary:
1. Allow Forecasting is a must for all forecast users.
2. Each manager role in the forecast hierarchy should have a user assigned as the Forecast Manager.
3. The API name for "Allow Forecasting" is ForecastEnabled, you can mass update it with API.



Salesforce: Setup Forecast Quota


1. Enable Opportunity Team
  



2. Enable Opportunity Split and Add Opportunity Split Type (optional)
For this blog, I am adding Overlay opportunity split type



 3. Enable Forecast, Add Forecast Type (optional), and Enable Quota

enable forecast

adding forecast type 

You may need to refresh your web browser to see the “Forecasts Quotas” tab to appear.


4. Enable user for “Allow Forecasting”
From the user detail page or Forecast Hierarchy.

user detail


forecast hierarchy

  
 5. Assign Quotas to Users
Make sure you select the right Forecast Type and select the right currency (if necessary)
Or use the Data Loader tool to load CSV into Forecasting Quota object



6. Set Forecast Manager for each Role Hierarchy
This is only necessary if user have subordinates as per role hierarchy, set up this from Forecast hierarchy.


7. Verify Quota

7A. Forecast Tab
Make sure to select correct Period, Forecast Type, and Currency



7B. SOQL
As mentioned in (5), we can use Data loader to load quota to Forecast Quota object. So, the same we can query from Forecast Quota object too.

SELECT Id, QuotaOwnerId, QuotaAmount, QuotaOwner.Name, ForecastingTypeId, PeriodId FROM ForecastingQuota



  • From the above query, this shows us that Quota Amount numbers are NOT roll-up from subordinates to the manager.
  • The currency here is based on currency defined when added the quota for each users.


Reference:

Monday, August 26, 2019

Salesforce: Get Folder Sharing Access

Report and dashboard accessibility are defined in the folder that stores the reports or dashboard, there are 3 levels of access:
1. Manage
2. Edit
3. View

You can share the folder by:
1. Users
2. Roles
3. Roles and Subordinates
4. Public Group
5. Territory (only when Territory is enabled)
6. Territory and Subordinates (only when Territory is enabled)
7. Partner Users (only when Partner community is enabled)
8. Partner Roles (only when Partner community is enabled)
8. Partner Roles and Subordinates (only when Partner community is enabled)
9. Customer Users (only when Customer community is enabled)
10. etc

Here a sample of report folder shared with users, roles, and etc.



If you see from the above screenshot, Group of GM and Staff 2 are from another screenshot, because of sharing window only able to show 5 lines at a time, so if you need to extract the list of users, roles, and etc., you need to get it from metadata.

Workbench
We can use REST Explorer from Workbench to retrieve this info. Let us walk through step by step:
1. Login to Workbench
2. Select REST Explorer under utilities menu
3. By default, the service would be /services/data/v45.0, click Execute
4. Click on folders: /services/data/v45.0/folders, click Execute
5. Click Expand All link, and search for the folder name, if you can't find it, click nextPageUrl link
6. Or, instead of looking for the report folder, get the folder Id and execute it, e.g.  /services/data/v45.0/folders/00l90000001MUVKAA4
7. Click sharesUrl: /services/data/v45.0/folders/00l90000001MUVKAA4/shares
8. Click Expand All link, now you get the list, but to put it into nice table file would need another effort

Convert JSON to CSV
8. Click "Show Raw Response" link
9. Copy the JSON file to http://www.convertcsv.com/json-to-csv.htm
10. Download the result into CSV file



Reference:



Monday, July 29, 2019

Einstein Analytics: Embed Analytics Dashboards with multiple fields filter

Here is the scenario:
Show all Opportunities with filter: compare Parent_Account_ID__c value from Opportunity page (a formula field CASESAFEID(Account.ParentId)) with 2 fields from a dataset: AccountId and Parent_Account_Id__c.

As per Summer '19 release, we can use Filter Builder or Filter String, but unfortunately, the filters do not support OR logic.

Here is the workaround for the scenario:
1. Create a field in Dataset using ComputeExpression to concatenate AccountId + Parent_Account_ID__c, let us say AccountIdnParentId.

2. Use the new field created from ComputeExpression with "Contains" operator.


In case if Parent Account Id is blank, this will cause the dashboard error, you can create a custom formula field in the Opportunity to check and make sure if Parent Account Id is blank, use Account Id.





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 from this object

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.

Make sure Charlie User C is enabled as Forecast Manager in Forecast Hierarchy, otherwise Dave number (and all other users under Charlie) will not roll-up to Charlie.


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



Reference:


Page-level ad