Obviously, Salesforce has strong built-in reporting tools. And I'm a big fan of the dashboards, especially filtered dashboards and dynamic dashboards. WAY better than the old approach of creating a unique set of reports and a corresponding dashboard for every conceivable view.
Still, I often want to pull my Salesforce data into Excel. This might be for further manipulation / processing, aggregating data that doesn't live in Salesforce, or distribution to non-Salesforce users (hey, those licenses are expensive!).
Reporting Tool - XL-Connector: I'm using a new (to me) tool when I have to repeatedly pull Salesforce data into Excel: XL-Connector (fka Enabler 4 Excel) from Xappex. There's a lot you can do with XL-Connector. My primary use is just to extract data from Salesforce, and this can be done in two ways: reports or SOQL queries. The great part is that once you've captured data into an Excel file, refreshing the data is trivial. I've stored my credentials with the program, so logging in takes five clicks (or you can have it log in automatically when you start Excel). Then I can refresh the data on each sheet, or refresh all. Easy and quick. You can also use XL-Connector with VBA, allowing even further automation; I'll revisit using XL-Connector with VBA in a later post.
So I have an easy way to get my Salesforce data into Excel. But I've still got a couple of challenges and a few tips to address them.
Tip - Salesforce's CaseSafeID function: One challenge is the difference in case-sensitivity between Salesforce and Excel. In Salesforce, 0067000000BPjlV and 0067000000BPjlv represent two different opportunities; the lower-case 'v' in the second id makes it a different record identifier. But if I have both of these ids in an Excel worksheet, they'd be considered equal, for instance in a match or vlookup formula. This isn't an issue if you extract your data via SOQL; that will return 18-digit ids which ARE unique even with Excel's indifference to case. But in a report, the standard id fields will be 15-digit ids, subject to invalid matches. What to do?
You might use a combination of id and name, or perhaps use an auto-number field. Instead, I create a custom formula field returning a text value, and use a built-in Salesforce function to make the 18-digit id available for reports. The function is CaseSafeID, meant
exactly for this purpose! Here's the tip from the Help & Training page: "Convert to 18-character IDs for better compatibility with Excel." So I add a custom field like this on all the objects where I want to perform lookups after exporting a report. This definitely includes accounts and opportunities.
There are two big downsides to pulling your Salesforce data into Excel. The first is simply that you've made a point-in-time snapshot; as soon as changes are made in Salesforce, your Excel file is outdated. (That may not always be a bad thing... one sales leader I supported would go nuts before our forecast calls as he tried to incorporate every last update; a point-in-time snapshot was exactly what we needed, as long as it was recent!). The other downside, and the second challenge I want to address, is that you've separated the report from the Salesforce record. If you want more detail, you have to go back to Salesforce.
Tip - Excel's Hyperlink function: Excel offers a nice solution to this, the hyperlink function. In almost any report I export, I include both the name and the record id. Then I use Excel's hyperlink function to provide a one-click means to get from the report directly to the Salesforce record of interest. The general form of the hyperlink function is =hyperlink(URL,visible text). With my record id in cell in A2 and name in B2, my formula is =HYPERLINK("https://na5.salesforce.com/" & A2, B2). I copy that formula through all rows of my table, and now all that additional Salesforce data is again one click away, much as if the report were in Salesforce!
Still, I often want to pull my Salesforce data into Excel. This might be for further manipulation / processing, aggregating data that doesn't live in Salesforce, or distribution to non-Salesforce users (hey, those licenses are expensive!).
Reporting Tool - XL-Connector: I'm using a new (to me) tool when I have to repeatedly pull Salesforce data into Excel: XL-Connector (fka Enabler 4 Excel) from Xappex. There's a lot you can do with XL-Connector. My primary use is just to extract data from Salesforce, and this can be done in two ways: reports or SOQL queries. The great part is that once you've captured data into an Excel file, refreshing the data is trivial. I've stored my credentials with the program, so logging in takes five clicks (or you can have it log in automatically when you start Excel). Then I can refresh the data on each sheet, or refresh all. Easy and quick. You can also use XL-Connector with VBA, allowing even further automation; I'll revisit using XL-Connector with VBA in a later post.So I have an easy way to get my Salesforce data into Excel. But I've still got a couple of challenges and a few tips to address them.
Tip - Salesforce's CaseSafeID function: One challenge is the difference in case-sensitivity between Salesforce and Excel. In Salesforce, 0067000000BPjlV and 0067000000BPjlv represent two different opportunities; the lower-case 'v' in the second id makes it a different record identifier. But if I have both of these ids in an Excel worksheet, they'd be considered equal, for instance in a match or vlookup formula. This isn't an issue if you extract your data via SOQL; that will return 18-digit ids which ARE unique even with Excel's indifference to case. But in a report, the standard id fields will be 15-digit ids, subject to invalid matches. What to do?
You might use a combination of id and name, or perhaps use an auto-number field. Instead, I create a custom formula field returning a text value, and use a built-in Salesforce function to make the 18-digit id available for reports. The function is CaseSafeID, meant
exactly for this purpose! Here's the tip from the Help & Training page: "Convert to 18-character IDs for better compatibility with Excel." So I add a custom field like this on all the objects where I want to perform lookups after exporting a report. This definitely includes accounts and opportunities.
There are two big downsides to pulling your Salesforce data into Excel. The first is simply that you've made a point-in-time snapshot; as soon as changes are made in Salesforce, your Excel file is outdated. (That may not always be a bad thing... one sales leader I supported would go nuts before our forecast calls as he tried to incorporate every last update; a point-in-time snapshot was exactly what we needed, as long as it was recent!). The other downside, and the second challenge I want to address, is that you've separated the report from the Salesforce record. If you want more detail, you have to go back to Salesforce.
Tip - Excel's Hyperlink function: Excel offers a nice solution to this, the hyperlink function. In almost any report I export, I include both the name and the record id. Then I use Excel's hyperlink function to provide a one-click means to get from the report directly to the Salesforce record of interest. The general form of the hyperlink function is =hyperlink(URL,visible text). With my record id in cell in A2 and name in B2, my formula is =HYPERLINK("https://na5.salesforce.com/" & A2, B2). I copy that formula through all rows of my table, and now all that additional Salesforce data is again one click away, much as if the report were in Salesforce!
Comments
Post a Comment