Jaspersoft iReport and JasperReports real-time connection to Salesforce using JDBC

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

• Full support for custom fields and data types from latest Salesforce APIs
• Advanced caching technology built into driver
• SQL-92 compliance includes support for JOINs
• Real time access to formula and roll-up summary fields
• JDBC connectivity makes Salesforce look like Oracle, SQL Server, or Postgres.
• Absolutely no staging database is required
• Unlike on-premise OLTP sources, you can go "query wild" against Salesforce.com without impacting the application.
• Distribute reports/analytics company wide or embed within Salesforce as an iFrame
• It's fast!
• It's from Progress DataDirect!

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!