I have worked with a number of Salesforce organizations (running CRM, force.com or database.com) looking to leverage the power of JasperSoft BI against their Salesforce data. JasperSoft bundles a demo postgres database to showcase the features and functionality of their BI solution. These are very impressive, but what if you could get similar JDBC access to your organization's relational database behind the Salesforce cloud with real time SQL-92 access similar to PostgreSQL, SQL Server or Oracle?
I met Matthew Geise, Senior Director, Community Development at JasperSoft last week and asked him about this connectivity and he replied, “Working with your JDBC drivers for Salesforce and other datasources is already possible. To connect to a datasource through a JDBC connection, the driver must only be JDBC 2.0/SQL 92 compliant.”
Connect JasperSoft Reports directly to Salesforce in real-time with SQL-92 access
The solution from Progress DataDirect is the Connect XE for JDBC Salesforce driver. This is a very capable driver engineered exclusively to access SaaS data (Salesforce) in the cloud without any staging database. Connectivity from JasperReports to Salesforce is becoming a common requirement from DataDirect prospects and in reading developer boards ranging from boards.developerforce.com to jasperforge.org.
Key features for JDBC connectivity to Salesforce from BI platforms
Start building a JasperReport with Salesforce data in 15 minutes
1. Download trial of JasperReports Server. For this article, I downloaded jasperreports-server-4.5-windows-x64-installer.exe.
2 . Install Progress DataDirect ConnectXE for JDBC using the instructions in the Progress DataDirect Connect for JDBC Installation Guide. Click here to dow1nload a free 15-day trial. (article assumes installation directory is C:\Jdbc\Connect_Series_for_JDBC_50)
3. Launch java cloud service for connectivity to Salesforce from command prompt:
C:\jdbc\Connect_Series_for_JDBC_50\lib>java -classpath .;sforce.jar com.ddtek.cloud.sql.Server
[Server@576a9d42]: Initiating startup sequence...
[Server@576a9d42]: Server socket opened successfully in 1045 ms.
[Server@576a9d42]: Listening at port: 19928
[Server@576a9d42]: Startup sequence completed in 1052 ms.
[Server@576a9d42]: 2012-06-18 21:05:27.579 DataDirect Connect Salesforce JDBC/OD
BC server is online
[Server@576a9d42]: Use [Ctrl]+[C] to terminate.
Note: Starting with JDBC 5.1.3, use the following command instead:
java -classpath .;sforce.jar com.ddtek.sforcecloud.sql.Server
4. Launch iReport Designer
5. Add installed Salesforce JDBC driver "sforce.jar" to classpath
6. Click Step 1 to create a new data source and choose "Database JDBC connection".
7. Enter JDBC Driver as com.ddtek.sforce.SForceDriver and JDBC URL as jdbc:datadirect:sforce://login.salesforce.com;SQLEngineMode=server
8. Navigate through report wizard and query designer to leverage SQL-92 access by writing an inner join query for report use. This is especially useful for reporting against custom objects and fields. The query in my screenshot is against a developer account and not that complex, so I challenge you to do better than me.
9. Create a report. Again, I challenge you to do better than me.
Maybe something nice like this report design from jaspersoft.com:
What else does JDBC enable for JasperSoft BI?
JasperAnalysis supports JDBC data sources for OLAP analysis.
Please remember to come back and share your feedback on the evaluation with other JasperSoft/Salesforce shops!
Technology researcher, thought leader and speaker working to enable enterprises to rapidly adopt new technologies that are adaptive, connected and cognitive. Sumit has been working in the data access infrastructure field for over 10 years servicing web/mobile developers, data engineers and data scientists. His primary areas of focus include cross platform app development, serverless architectures, and hybrid enterprise data management that supports open standards such as ODBC, JDBC, ADO.NET, GraphQL, OData/REST. He has presented dozens of technology sessions at conferences such as Dreamforce, Oracle OpenWorld, Strata Hadoop World, API World, Microstrategy World, MongoDB World, etc.
Let our experts teach you how to use Sitefinity's best-in-class features to deliver compelling digital experiences.Learn More
Subscribe to get all the news, info and tutorials you need to build better business apps and sites
You can also ask us not to share your Personal Information to third parties here: Do Not Sell or Share My Info
We see that you have already chosen to receive marketing materials from us. If you wish to change this at any time you may do so by clicking here.
Thank you for your continued interest in Progress. Based on either your previous activity on our websites or our ongoing relationship, we will keep you updated on our products, solutions, services, company news and events. If you decide that you want to be removed from our mailing lists at any time, you can change your contact preferences by clicking here.