Apex + CRM Analytics. Demo with bells and whistles
This blog is about using Apex query types in CRM Analytics. There isn’t a lot of documentation on the topic, but it’s a useful feature—especially when you hit SAQL limits or need to show live data from external sources. My example is exactly about that - how to use apex class results in CRM Analytics.
The problem
I want to greet users based on the time of day. Since our users are located all over the world, the greeting needs to adjust to their local time zones. It also needs to work automatically, accounting for daylight saving time and any changes to time zone policies.
In CRM Analytics, UTC is the standard, plus dashboards can be adjusted to specific time zones, but that doesn’t fully solve my use case. Using currently logged-in user's time zone is not possible using standard CRMA functionalities.
The solution
I decided to use an Apex class. I’ll pull all time zone values (from the 'TimeZoneSidKey' field on the User object) and convert it into a format I can use to create personalized greetings that update based on the current time of day. I'd like to greet my users in the following way:
1. 04:01 - 12:00 user's local time - with a 'Good morning'
2. 12:01 - 18:00 user's local time - with a 'Good afternoon'
3. 18:01 - 04:00 user's local time - with a 'Good evening'
I'll call this Apex class in my CRM Analytics dashboard, so it shows a personalized message based on who's logged in.
Here you can have a look how the messages morning/afternoon/evening look like:
Let's take a look at the code first. This Apex class is designed to create a table showing all the available time zone options from a picklist. The list is automatically managed by Salesforce, and more specifically, it's kept up to date by Java to reflect all possible time changes.
@RestResource(urlMapping='/timezones')
global with sharing class TimeZoneData {
@HttpPost
global static String data_val(String timeZone) {
return JSON.serialize(new PackagedReturnItem(getTimeZones(timeZone)));
}
private static List<Map<String, String>> getTimeZones(String timeZone) {
List<Map<String, String>> timeZonesList = new List<Map<String, String>>();
for (Schema.PicklistEntry entry : Schema.SObjectType.User.fields.TimeZoneSidKey.picklistValues) {
String label = entry.getValue();
if (timeZone == null || label.toLowerCase().contains(timeZone.toLowerCase())) {
timeZonesList.add(buildTimeZoneMap(entry));
}
}
return timeZonesList;
}
private static Map<String, String> buildTimeZoneMap(Schema.PicklistEntry entry) {
String label = entry.getLabel();
String offset = extractOffset(label);
String localTime = calculateLocalTime(offset);
String greeting = determineGreeting(localTime);
String subtitle = determineSubtitle(greeting);
return new Map<String, String> {
'value' => entry.getValue(),
'label' => label,
'offset' => offset,
'localTime' => localTime,
'greeting' => greeting,
'subtitle' => subtitle
};
}
private static String extractOffset(String label) {
return label.contains('GMT') ? label.substringBetween('(GMT', ')').trim() : 'Unknown';
}
private static String calculateLocalTime(String offset) {
if (offset == 'Unknown') return 'Unknown';
List<String> parts = offset.split(':');
Integer hours = Integer.valueOf(parts[0]);
Integer minutes = parts.size() > 1 ? Integer.valueOf(parts[1]) : 0;
Datetime utcNow = System.now().addSeconds(-UserInfo.getTimezone().getOffset(System.now()) / 1000);
return utcNow.addHours(hours).addMinutes(minutes).format('HH:mm:ss');
}
private static String determineGreeting(String localTime) {
if (localTime == 'Unknown') return 'Unknown';
Integer hour = Integer.valueOf(localTime.substring(0, 2));
if (hour < 4) {
return 'Good evening';
} else if (hour < 12) {
return 'Good morning';
} else if (hour < 18) {
return 'Good afternoon';
} else {
return 'Good evening';
}
}
private static String determineSubtitle(String greeting) {
if (greeting == 'Unknown') return 'Unknown';
if (greeting == 'Good morning') return 'Let\'s start the day with some insights!';
if (greeting == 'Good afternoon') return 'Here\'s what\'s happening so far today.';
return 'Let\'s review the key trends from today.';
}
public class PackagedReturnItem {
public List<Map<String, String>> data;
public ReturnMetadata metadata = new ReturnMetadata();
public PackagedReturnItem(List<Map<String, String>> data) {
this.data = data;
}
}
public class ReturnMetadata {
public List<String> strings = new List<String>{'value', 'label', 'offset', 'localTime', 'greeting', 'subtitle'};
}
}We need the following columns:
- Time zone picklist value – like “Europe/Warsaw”. This is the value stored on the user record and will be used for filtering.
- Time zone label – this will be used to calculate the time difference from GMT.
- Time zone offset – the actual difference from GMT in hours.
- Greeting – a friendly message (like “Good morning”).
- Subtitle – a supporting text - subtitle in our dashboard.
Let’s move to Analytics Studio
After finishing your Apex class, switch to Analytics Studio
Step 1: Get the current user's time zone
To do this, run the SQL query indicated below. We can’t just use a merge field (!{User.TimeZoneSidKey}) here because the 'TimeZoneSidKey' field isn’t supported that way (only a few specific fields are).
SQL query to retrieve currently logged-in user’s time zone
Step 2: Create an Apex-type query You have two options: - Write the query directly in JSON (if you know "steps" structure), or - Create a temporary query, then go to the “Query” tab and replace its content with the code I provided below. Make sure to connect this query to your “current user” data by using the 'TimeZoneSidKey' field as binding from the SQL query.
Apex query in advanced editor
Preview of the apex query
The query should return just one row — the time zone of the currently logged-in user. You can now use this in text widgets (as bindings or merge fields) to personalize what users see based on the time of day. This is just a small example of how Apex can be used in a CRMA dashboard, but I’m sure it’s already sparked some ideas for how you could use this feature to take your dashboards to the next level!
