Improve Salesforce report performance

Salesforce.com report is a simple, yet very powerful feature. Out-of-the-box, it come with multiple type of reports, and admin can create more custom report type as needed. With report builder introduce few years back, it even easier to use and more user friendly.

But, sometimes you may found that report take sometimes to load, or time-out (although this is very rare happened).

What is the caused?
1. Too many columns in report
Just add columns as needed. More columns to display will take more time to run report. You also can hide details, when it needed, just click "Show Details" button.

2. Too many records
Set report filter criteria correctly and efficiently, you can add multiple criterias and time frame.

3. Inefficient filters
Contain and Does Not Contain take longer time to load report.
Report using AND will load faster than OR.

If you have tune everything above and it still slow, you can schedule report for future run and email it to you daily or weekly.

For more information, watch this YouTube video.

Salesforce: Calculate age for weekdays and weekends

In Salesforce, it is easy to calculate the number of days between 2 dates. Just create a formula field, for example: End_Date__c - Start_Date__c for date fields, or  Datevalue(End_Date__c) - Datevalue(Start_Date__c) for datetime fields and DONE!!!

But, is it possible to find out the number of days - only weekdays and only weekends between 2 dates?
Hmmm.... most of us will think about Apex Trigger. Yes, it is the correct solution, apex trigger is able to calculate without issue, but if you are not a developer, you need a developer for this.

Wait a minute.... Can we 'just' use a formula field to calculate weekdays and weekends?

YES, it is possible with complex formulas. Here we go:

To calculate the number of  Weekday

IF ((CASE(MOD( Request_Date__c - DATE(1900,1,1),7),

0, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),

1, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),

2, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),

3, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),

4, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),

5, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),

6, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),

999)

+ (FLOOR(( Execution_Date__c - Request_Date__c )/7)*5)

- 1) < 0,

0,

CASE(MOD( Request_Date__c - DATE(1900,1,1),7),

0, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),

1, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),

2, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),

3, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),

4, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),

5, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),

6, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),

999)

+ (FLOOR(( Execution_Date__c - Request_Date__c )/7)*5)

- 1)

To calculate number of  Weekend

CASE(MOD( Request_Date__c - DATE(1900,1,1),7),

0, CASE( MOD( Execution_Date__c - Request_Date__c, 7),1,0,2,0,3,0,4,0,5,1,6,2,0),
1, CASE( MOD( Execution_Date__c - Request_Date__c, 7),0,0,1,0,2,0,3,0,4,0,5,2,2),
2, CASE( MOD( Execution_Date__c - Request_Date__c, 7),0,0,1,0,2,0,3,1,2),
3, CASE( MOD( Execution_Date__c - Request_Date__c, 7),0,0,1,0,2,1,2),
4, CASE( MOD( Execution_Date__c - Request_Date__c, 7),0,0,1,1,2),
5, CASE( MOD( Execution_Date__c - Request_Date__c, 7),0,1,2),
6, CASE( MOD( Execution_Date__c - Request_Date__c, 7),6,2,1),
999)
+ (FLOOR(( Execution_Date__c - Request_Date__c )/7)*2)

Example:
Request Date = 1 Jan 2014
Execution Date = 10 Jan 2014
Total days on weekdays = 7
Total days on the weekend = 2

Request Date = 1 Mar 2014
Execution Date = 10 Mar 2014
Total days on weekdays = 5
Total days on the weekend = 4

If you see in the formula above, we have DATE(1900,1,1); this is referred to as 1-Jan-1900 is Monday. So, you can use any date which is Monday, for example, 1-Jan-2007

The rest is just the MOD() function, so have fun...


Another request is to calculate a date after X days from today (business day means weekday from Mon - Fri). Here is the formula; the result will be a formula in Date format, while Days__c is a number.

CASE( 
MOD(TODAY()- DATE(1900,1,7),7), 
0, (TODAY()) + Days__c+ FLOOR((Days__c-1)/5)*2, 
1, (TODAY()) + Days__c+ FLOOR((Days__c)/5)*2, 
2, (TODAY()) + Days__c+ FLOOR((Days__c+1)/5)*2, 
3, (TODAY()) + Days__c+ FLOOR((Days__c+2)/5)*2, 
4, (TODAY()) + Days__c+ FLOOR((Days__c+3)/5)*2, 
5, (TODAY()) + Days__c+ CEILING((Days__c)/5)*2, 
6, (TODAY()) - IF(Days__c > 0,1,0) + Days__c + CEILING((Days__c)/5)*2, NULL)

Result sample: today is 3 Oct 2016, and Days are 5, so the result would be 10 Oct 2016.

How to restrict sharing access in Salesforce?

We often heard that business would like to restrict x number of users to access Account, while open it to rest of users. How we can do that in Salesforce?

However in Salesforce, it is the other way round. You need to specify who can access an object, NOT about who cannot access an object.

If your company decide not to give access to anyone in company (to view or edit an object), Organization-Wide Defaults (OWD) should be set to Private, then add sharing rule for that object based on: Criterias, Public Groups, Roles or  'Roles and Subordinates'.

If OWD is set to Private or Public Read-Only. It will be shared in following rule:

• Role Hierarchy, any user in higher role hierarchy of record owner will be able to access the record
• Sharing Rule, any user and user in higher role hierarchy of users being shared in Sharing Rule will be able to access the record
• Manual Sharing, owner or user in higher role hierarchy of record owner, will be able to share records owned.

To investigate who can access a record, system admin can click on 'Sharing' button in record detail. Then, click 'Expand List' to get more details on user being shared and why they being shared.

Salesforce: Campaign field not show in Lead page layout

A user come to me and ask, "Why the Campaign field in Lead page disappear after create a new lead ?"

Hmmm... as system admin, what will you check?

• Make sure Campaign field is added in Lead page layout - checked
• Check Field-Level Security for particluar user profile - checked

User still do not see Campaign field in Lead after it saved or when user edit a Lead, but it is visible when user create new Lead. After some finding, it is designed by default from Salesforce.

Here is the explanation:
1. The Campaign lookup field on the lead page only available when user create new Lead.

2. It will disappears after a new lead is created, because the purpose of this field is to make it easier for the user when creating a new lead and to associate it to an existing campaign.

3. If a campaign is associated from this lookup field on Lead creation process, it will be shown in Campaign History related list. User can add more campaigns from Campaign History related list in Lead Page layout.

If we look to the data structure, Lead Id is a field in CampaignMember object, Setup | Customize | Campaigns | Campaign Members | Fields

When a Lead is converted, the most recently associated campaign is brought over to the Opportunity Campaign Source field upon conversion.


Reference: Why does the Campaign lookup field on the lead page disappear after a new lead is created?

Salesforce Global Variable

In previous blog, we talk about Custom Label, where System Administrator able to set values in Custom Label, it is accessible via formula field using $Label. $Label is one of Global Variable in Salesforce, you can use global variable to reference general information about the current user or your organization. Global variable can be easily identified with prefix $.

Components such as custom buttons, custom links, formulas, and Visualforce pages allow you to use global variables with special merge fields to reference the data in your organization. Please note, global variables accessible in formula field, validation rule and custom link / button are different. See screenshot below:

in formula field

in validation rule

in custom button / link

Here a few often used Global Variable merge field for validation rule or formula field:

$Profile -- A global merge field type to use when referencing information about the current user’s profile. Use profile merge fields to reference information about the user’s profile such as license type or name.

$User -- A global merge field type to use when referencing information about the current user. User merge fields can reference information about the user such as alias, title, and ID.

$RecordType -- A global merge field to use when referencing the record type of the current record.

Although this merge field is not visible in screenshot above, but it will work. You can use following merge fields: $RecordType.Id, $RecordType.Name

$Organization -- A global merge field type to use when referencing information about your company profile. Use organization merge fields to reference your organization’s city, fax, ID, or other details. If you use this merge field with $Organization.Id and reference to your production instance, when you create/refresh sandbox, Id value in the validation rule will change to new sandbox Org Id.

Some use cases using Global Variable:

1. To ensure that only the record owner or users with profile System Administrator can make changes to Opportunity Amount.

Create validation rule in Opportunity with following Error Condition Formula:

AND( ISCHANGED( Amount ), Owner <> $User.Id, $Profile.Name <> "System Administrator" ) 

2. Not allow users with profile System Administrator or users with first name = Steve to create or modify Account

Create validation rule in Account with following Error Condition Formula:

OR($User.FirstName = 'Steve', $Profile.Name = 'System Administrator')

3. To view all Account last modified by me (assume Account sharing Organization-Wide Defaults is Public)
Create a checkbox formula field "Last Modified by Me" in Account with formula:
$User.Id = LastModifiedById

"Last Modified by Me" value will be dynamic based on current login user. Then, create a List View with filter "Last Modified by Me" = True (the same can be applied for Report).

You can share this view to all users or certain groups of users without each user need to create their own view for the same purpose.

This kind of simple formula can be used for Created By Me or any user lookup custom field in any object.


Reference:

• Understanding Global Variables
• Global Variables

Salesforce Custom Label

Custom Label maybe not so popular among Salesforce administrator compare to other feature, such as: workflow, formula field and so on, even less popular compare to Custom Setting. Custom Label also enable admins or developers to show data/value in multi-language.

Custom Label may be more popular for developers to store a variable, but Custom Label actually also available for system administrator to be used in formula field, workflow rule, and validation rule. For a formula field that refer to custom label, value of the formula field will be depend on value from custom label referred, when display in page layout or report, it will be displayed based on language selected in user detail.

To create Custom Label, click Setup | Create | Custom Labels. You only can add translation language based on languages supported in your Translation Workbench.

Once created, you can find Custom Label in Global Variable start with $Label

Once added in formula field or other places, it will show with format $Label.Label_Name

Custom Label enable developers to create multilingual applications by automatically presenting information in a user's native language from Apex class or Visualforce page.

For Apex class, it would be just a simple as: String strMyLabel = Label.Hello;


Note: if you want to set Custom Label in multi-language, you need to enable Translation Workbench. To enable Translation Workbench from Setup | Translation Workbench | Translation Settings, then add and activate languages that you need.


Reference: Custom Labels