Jitterbit – What to do when Salesforce Reporting doesn’t cut it…

by Karen McGee

Have you ever run into an issue when a user requested a report that seems simple to create but when you start to work on the report you realize it is not going to be that simple? I was recently asked by one of my users to create a report that displays all accounts, with an associated opportunity, quote with quote lines for each sales rep. The report would be used by a 3rd party tool to calculate commissions for each qualified sales representative. In order to create the report, a new report type needed to be created.

Therefore, I started down the path of creating a report type that would link accounts, opportunities, quotes, quote lines and users which would seem pretty simple to do. However, before I was able to join the last object I hit the dreaded error “Object Limit Reached You can associate up to four objects to a custom report type“! How can this be resolved? Surely, I am not the only person who has needed to join more than 4 objects within a report type, right? I am not sure about others but for us, the simple solution became Jitterbit to the rescue! Jitterbit is a wizard-based integration tool that provides a user-friendly interactive drag and drops SOQL (Salesforce.com object query language) query builder interface.

All you have to do is find the fields or objects you need from the pre-populated list and pull it over to the query canvas. Jitterbit will do the rest. Solution provided by Jitterbit! With Jitterbit a developer can create an SOQL query to join the accounts, opportunities, quotes, quote lines and users. Jitterbit was able to produce a report that contains certain fields from each object and provide the user with all of the fields that they were looking for. The beauty of it all was it only took 5-10 minutes for my developer to create the report.

Now you may ask the question “Do I really need to hire a developer that specializes in Jitterbit?” I don’t want to spend extra money on additional resources to just write a report. My answer to that question would be how do you plan on using the resource. If you are only looking for someone to build reports then no, you don’t need a Jitterbit resource as the report could have been created using data loader or dataloader.io (a more user friendly interface) but the report writer resource would still need to know how to write and join objects using SOQL to overcome the report type limitation of joining 4 objects. However, if you see a greater use of your resource other than a report writer then hiring a Jitterbit resource is money well spent.

Jitterbit is a flexible tool that allows you to integrate data across many applications. The tool is easy to learn for someone that has a basic technical background. We currently use it to pull information back and forth between our legacy systems (such as Oracle and Vantive) and our cloud-based applications like Salesforce and Workday. Jitterbit is not perfect but it is a flexible and powerful tool that can be used by any organization to perform any type of data extraction from many types of applications.

Now this article wouldn’t be complete if I didn’t explain how we resolved the issue. Remember our users wanted a report that displays all accounts, with an associated opportunity, quote with quote lines for each sales rep. In addition to that the wanted the fields on the report to be relabeled to exclude underscores in the name. Seems like a lot but Jitterbit was able to handle this without a problem! The screen shots below will show you how we handled it.

First let us illustrate the Salesforce report type creation and error to showcase the problem we encounter:

The Jitterbit Solution

Login to Jitterbit

Develop your query using a drag and drop UI. In our example below, the query “Last Day of the Month” is used to schedule the report on a monthly basis. Query quote lines is our main query used to generate our report. When the report is completed it is moved to an SFTP server. We then use another function (Writer header) to relabel the fields. In Jitterbit this is consider an operation.