When you cannot build a report in Salesforce: supporting your customer support team

Salesforce.com is a powerful platform. The power of it comes from careful selection of features made available in the multi-tenant environment. As with any engineering decision for any technical system, there's a list of trade-offs that are made to ensure the best performance possible in the most common scenarios. But what if you need something, which is just slightly different from these 'most common scenarios'?

There are no arbitrary joins between the entities in SFDC and for a good reason: you don't want to build all indexes possible for all of your tenants. While it's fine in 'most common cases' during normal user interactions with entities in UI, it may cause major headaches when your users ask for reports on their very own data.

I have one stunning example of such situation. Imaging, the director of your customer support team comes to you and asks for a weekly report showing open-vs-closed cases side-by-side. Something like this:
Should be easy to do, right?

Your first thought would be to use a joined report to provide, and you're right, a joined report would definitely do here! But how do you join it? You will be reporting the same entity – Cases – for both parts of the report, but you have to group on different fields in these to parts – Date/Time Created and Date/Time Closed. How would you group on two separate fields at the same time? Well, you can try a formula field, which will show the created date if the case is still open or the closed date if it's resolved. Unfortunately, this will not work. Consider a support case, which was opened on Tuesday last week and resolved next day on Wednesday. This case should be accounted for in both columns for the last week, for both opened and closed cases. Any other options?

In our environment, we ended up employing DBAmp for this job. Essentially, we calculate separately the number of opened and the number of closed cases for each week, then upload these numbers to an auxiliary entity in SFDC, which has three fields: reporting week, number of opened cases and number of closed cases. Then the resulting joined report is built based on that entity and, since the date field is the same for both counters, you don't have a problem grouping on the reporting week field.

Check out the DBAmp Cookbook repository for this and other recipes.

No comments: