Lab Coat Lesson: Building Better Analytic Snapshots
Analytic snapshots should be in every admins toolbox. The ability to report on historical data and track trends over time is beneficial to nearly all organizations. Salesforce Help & Training documentation is a good place to start for beginners learning to setup and configure analytic snapshots for their organization.
Here are some useful tips, best practices, and workarounds for the admin looking to make analytic snapshots more efficient:
Find out how Schema Builder can save you clicks and time
Map a lookup field
Use record types in analytic snapshots
Save and protect your working in a secure report folder
Use the Schema Builder to create your target object and fields!
Create your target object using the Schema Builder. You most likely will not be creating a tab for this object and will not need to use the Wizard for this object. This is the most efficient method for creating or target object to house the fields and data of the source report. You will save many clicks using the schema builder instead of adding fields to an object from the setup menu.
Click Setup > Schema Builder. This is a drag-and-drop environment you can use to create your new target object and all the necessary fields to map from the source report.
First, click on the Elements tab and drag Object into the work pane.
NOTE: Make sure you select Allow Reports or you will have trouble later!
It is recommended that you create your source report first. This way, you will know which fields you want to archive. You can quickly create the fields in the target object based on the fields in the source report.
Using the Schema Builder, drag a field type from the side panel and drop it in your new target object. The field types must be compatible. Most are pretty straightforward, you will usually use the same data type from the source field for your target field (for example, the Amount field maps to a currency field). Make sure you pay attention to field length and decimal places for currency, number, and percent fields. Make sure text fields have at least the same character length. There are some trickier scenarios which will we cover now…
Mapping a Lookup Field:
Following our example of creating an opportunity analytic snapshot, how would we archive the Account Name of the opportunity? This is a lookup field, so create a lookup field related to the Accounts in the target object.
To map a value to this lookup field, you must map the Account ID. Make sure Account ID is included in your source report.
Mapping Record Types
Unfortunately, and despite grumblings from the Salesforce community, you cannot map record types in an analytic snapshot. There is however, an easy workarounds using formula fields. We will store the record type name in a custom field, include this field in the source report, and map it to a text field on the target object.
Formula Field for Record Types
Here at CRM Science, we found an even easier way to map record types to analytic snapshots than this Salesforce knowledge article suggests: Can I map the record type in an Analytic Snapshot?
It recommends creating a text formula field and entering the following formula:
IF(RecordTypeId ="0126000000053ik", "Account Record Type",
IF(RecordTypeId ="0126000000056bP", "Last type", "None"))
You see, following this articles advice, you would have to actively manage your formula. What if you created new record types? Or changed the name of an existing record type? You would have to update the formula.
Follow this simple advice to create the formula and forget it! It will manage itself.
Create a Text Formula Field on the object
Name it “Record Type” or something similar. I do not use “Opportunity Record Type” in order to distinguish it from the standard's field name.
Enter the following into the formula: “RecordType.Name”
Save and forget it!
This formula will simply copy the record type name into the field. No need for all that extra code that you will have to remember to update later when record types change.
Create an “Analytic Snapshot” folder to protect all your hard work
The last thing you need is to setup, configure, and schedule the analytic snapshot only to have an end user make change to the source report and ruin everything!
I cannot stress this enough, save your source report to a folder that is Read Only, or grant only Viewer access if the new report sharing model is enabled, to all users except those trusted to make changes to analytic snapshots.
If non-admins have edit access to the source report, they can (and we all know, will) edit the report. This puts all the field mappings at risk of breaking and your analytic snapshot failing. So protect your hard work and create a Read Only report folder for your source report!
This advice should prove useful for seasoned analytic snapshot creators. If you are new to analytic snapshots, remember these tips! To get started, check out this page to learn how to setup and configure analytic snapshots: Report on Historical Data with Analytic Snapshots.