Using Salesforce Data in a Report
Using data retrieved from Salesforce is very easy and straightforward. In the following example, we'll create a simple report that displays data from the sample Contact database on Salesforce:
- In the Definition Editor, in the _Settings definition, add a Connection.Salesforce element, as shown above.
- Set its attributes as shown, providing your Salesforce account credentials as required. Your Salesforce password and Salesforce security token are entered as a concatenated string. For example, if XXXXX is your password and
FiSg46Rkrp3FmbVzl is your token, the Password attribute value is: XXXXXFiSg46Rkrp3FmbVzl.
- In your report definition, add a Data Table and DataLayer.SQL element, as shown above. Set their attributes as shown.
- Set the DataLayer element's Source attribute as follows:
SELECT AccountId, FirstName, LastName, Salutation, Phone, Email, Title FROM Contact
WHERE AccountId <> '' and LastName <> 'Unknown'
Salesforce uses a special variant of SQL, the Salesforce Object Query Language (SOQL), which has some important differences from standard SQL (for example, it doesn't include DISTINCT). More information about it can be found in the SOQL Language Reference at the Salesforce website.
Because SOQL is sufficiently different from standard SQL, Logi Studio's SQL Query Builder tool does not support it.
- Set the Connection ID attribute to the ID of the Connection.Salesforce element from Step 1, as shown.
- In this example, we'll save a few keystrokes by adding an Auto Columns element instead of all of the necessary Data Table Column and Label elements. In other report definitions that don't use Auto Columns, the Salesforce data in the datalayer is available, as usual, using @Data tokens.
- Preview the report.
The preview should look something like the example shown above.