Vertica Integration with TIBCO Jaspersoft: Connection Guide

About Vertica Connection Guides

Vertica connection guides provide basic instructions for connecting a third-party partner product to Vertica. Connection guides are based on our testing with specific versions of Vertica and the partner product.

Vertica and TIBCO Jaspersoft: Latest Versions Tested

Software Version
JasperReports Server 7.1.0
Jaspersoft Studio

6.17.0

Desktop platform

Windows Server 2019

Vertica client

Vertica JDBC client driver 11.0.0

Vertica Server Vertica Analytical Database 11.0.0

TIBCO Jaspersoft Overview

TIBCO Jaspersoft is a business intelligence tool that provides insights for fast visual dashboards. The JasperReports Server embeds the JasperReports engine and provides a user-friendly, web-based interface for end-users to run and view reports. Jaspersoft Studio is an open-source graphical design tool that allows users to create reports. JasperSoft reports are defined in an XML file format, called JRXML, which can be hand-coded, generated, or designed using a tool.

Installing TIBCO Jaspersoft

To download a community edition of the TIBCO Jaspersoft software, see Download Jaspersoft Business Intelligence Suite. We installed JasperReports Server and Jaspersoft Studio for our testing.

Installing the Vertica Client Driver

Before you can connect to Vertica using TIBCO Jaspersoft, you must install the Vertica JDBC client driver. Follow these steps:

  1. Go to the Vertica Client Drivers page.
  2. Download the JDBC driver package.

Note For details about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.

Connecting TIBCO Jaspersoft with Vertica

  1. Log in to the JasperReports server using your credentials.
  2. On the left panel, right-click Data Source > Add Resource > Data Source.

    The New Data Source window appears.

  3. Enter the following details:
    • Type: Select JDBC.
    • JDBC Driver: Select Vertica(com.vertica.jdbc.Driver).
      If you see the NOT INSTALLED: Vertica(com.vertica.jdbc.Driver) option, click Select Driver to browse and upload the jar file.
    • Host: Enter the IP address of the Vertica Server.
    • Port: 5433
    • Database: Name of the Vertica database.
    • URL: Appears by default.
    • User Name: Vertica server user name.
    • Password: Vertica server password.
  4. Click Test Connection.

    When the connection is successful, a confirmation appears.

  5. Click Save.
  6. In the Save window, add a Data Source Name and click Save.

The Vertica connection is now displayed in the data sources list:

Visualizing Data in TIBCO Jaspersoft

You can prepare reports using Jaspersoft Studio and then publish the reports to the JasperReports server. Follow these steps to create the reports:

  1. Open Jaspersoft Studio.
  2. In the left panel, select Data Adapter > Create Data Adapter.

  3. Select Database JDBC Connection and click Next.

    The Data Adapter Wizard appears.

  4. From the JDBC drop-down list, select com.vertica.jdbc.Driver.
  5. Enter your credentials for the Vertica server.
  6. Click the Driver Classpath tab.
  7. Add the Vertica JDBC jar file.
  8. Click Test to test the connection.
  9. Click Finish.
  10. From the main panel, click File > New > Jasper Report.

    The New Report Wizard opens.

  11. Select a template and click Next.
  12. Enter or select the Parent Folder, which will be the container for the report.
  13. Click Next.
  14. From the data adapter drop-down menu, select VerticaDS - Database JDBC connection.
  15. Click Next.
  16. Write the SELECT statement for the report and click Next.

  17. Select your dataset fields and click Finish.

The report is created and saved in the workspace in your local file system.

Publishing Reports

To publish your reports, you must use JasperReports, as follows:

  1. Open JasperReports.
  2. In the left panel, right-click Reports > Add Resource > JasperReport.
  3. Enter the report name.
  4. Click Browse and select the JRXML file you created.
  5. In the left panel, select Data Source.

    The Select Data Source from Repository window opens.

  6. Click Browse and select VerticaDS.
  7. Click Submit.

The report is generated and displayed in the Reports folder.

Known Limitations

  • BINARY, VARBINARY, and LONG VARBINARY data types are not supported.
  • For TIME, TIMESTAMP, TIMETZ, and TIMESTAMPTZ data types, seconds and milliseconds are not supported. For TIME and TIMESTAMPTZ, timezone is truncated.
  • INTERVAL YEAR TO MONTH, INTERVAL HOUR TO SECOND, INTERVAL HOUR TO MINUTE, or INTERVAL data types are not supported and the error There was an error on the server is displayed.

For More Information