Create Google Cloud Storage Reports on JasperReports Server

The CData JDBC driver for Google Cloud Storage enables you to provide access to Google Cloud Storage data across the enterprise. This article shows how to deploy the driver on JasperReports server and create a simple report based on a reporting domain, a business view of Google Cloud Storage data.

Deploy the Driver JAR

Follow the steps below to deploy the driver JAR on JasperReports Server. The instructions below contain specifics for Google Cloud Storage and the Tomcat server bundled with JasperReports Server. If you are using JBoss AS 7 instead of Tomcat, you can follow the standard process to deploy the Google Cloud Storage JDBC Driver on JBoss.

1. Copy the driver JAR and .lic file, located in the lib subfolder of the installation directory, to the lib subfolder of the apache-tomcat folder, located in the JasperReports Server installation directory.

2. Navigate to apache-tomcat -> webapps -> jasperserver-pro -> META-INF and add the following resource entry to the context.xml file:

   <Resource name="jdbc/googlecloudstorage" auth="Container" type="javax.sql.DataSource" driverClassName="cdata.jdbc.googlecloudstorage.GoogleCloudStorageDriver" url="jdbc:googlecloudstorage:ProjectId='project1';InitiateOAuth=GETANDREFRESH;" maxActive="20" maxIdle="10" maxWait="-1" factory="com.jaspersoft.jasperserver.tomcat.jndi.JSCommonsBasicDataSourceFactory"/>
   

   Specify the required connection properties in the JDBC URL -- the url attribute.

   Authenticate with a User Account

   You can connect without setting any connection properties for your user credentials. After setting InitiateOAuth to GETANDREFRESH, you are ready to connect.

   When you connect, the Google Cloud Storage OAuth endpoint opens in your default browser. Log in and grant permissions, then the OAuth process completes

   Authenticate with a Service Account

   Service accounts have silent authentication, without user authentication in the browser. You can also use a service account to delegate enterprise-wide access scopes.

   You need to create an OAuth application in this flow. See the Help documentation for more information. After setting the following connection properties, you are ready to connect:

   • InitiateOAuth: Set this to GETANDREFRESH.
   • OAuthJWTCertType: Set this to "PFXFILE".
   • OAuthJWTCert: Set this to the path to the .p12 file you generated.
   • OAuthJWTCertPassword: Set this to the password of the .p12 file.
   • OAuthJWTCertSubject: Set this to "*" to pick the first certificate in the certificate store.
   • OAuthJWTIssuer: In the service accounts section, click Manage Service Accounts and set this field to the email address displayed in the service account Id field.
   • OAuthJWTSubject: Set this to your enterprise Id if your subject type is set to "enterprise" or your app user Id if your subject type is set to "user".
   • ProjectId: Set this to the Id of the project you want to connect to.

   The OAuth flow for a service account then completes.

   Built-in Connection String Designer

   For assistance in constructing the JDBC URL, use the connection string designer built into the Google Cloud Storage JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.

   java -jar cdata.jdbc.googlecloudstorage.jar
   

   Fill in the connection properties and copy the connection string to the clipboard.

   👁 Using the built-in connection string designer to generate a JDBC URL (Salesforce is shown.)
   

   When you configure the JDBC URL, you may also want to set the Max Rows connection property. This will limit the number of rows returned, which is especially helpful for improving performance when designing reports and visualizations.

3. Navigate to jasperserver-pro -> WEB-INF and add the following reference to the web.xml file:

    Google Cloud Storage data JSP
    jdbc/googlecloudstorage
    javax.sql.DataSource
    Container
   
   

4. Restart the server.

Create the Google Cloud Storage JDBC Data Source

Follow the steps below to map the Google Cloud Storage JDBC driver to a JDBC data source:

1. Log into JasperReports Server and click Create -> Data Source.
2. In the Type menu, select JNDI Data Source.
3. In the Service Name box, enter the JDNI lookup defined in the resource definition in the web.xml file. In the example, the lookup is "jdbc/googlecloudstorage".
4. Click Save to create the Data Source.

Create a Domain

A domain is a metadata and access-control layer that surfaces a relevant business view to report creators. Follow the steps below to select Google Cloud Storage columns, apply filters, and execute SQL to create a domain.

1. Click Create -> Domain. Enter an Id for the domain and select a save location.
2. Click Browse in the Data Source section and select the Google Cloud Storage data source you created.
3. In the Domain Design section, click Create with Domain Designer.
4. Select the schema containing your tables.
5. On the Tables tab, select a table in the Data Source pane and then click the arrow to add them to the Selected Tables pane. 👁 Tables to add on the Tables tab in the Domain Designer. (Salesforce is shown.)
   
6. On the Derived Tables tab, you can define domains based on SQL queries. For example,

    SELECT Name, OwnerId FROM Buckets WHERE Name = 'TestBucket'
    

7. On the Joins tab, you can join tables by selecting the columns you want to build the join condition and selecting the join type.
8. On the Pre-Filters tab, drag and drop columns to define search criteria that filters the data exposed through the domain.
9. On the Display tab, select Google Cloud Storage data into the data sets presented to the user. 👁 Resources to add on the Display tab in the Domain Designer. (Salesforce is shown.)
   
10. Click OK to accept your changes and click Submit to create the domain.

Explore Data in Real Time

After you have created a domain, you are ready to connect to data. Follow the steps below to create an Ad Hoc View in the Ad Hoc Editor.

1. Click Create -> Ad Hoc View.
2. In the resulting dialog, select the Google Cloud Storage domain you created. 👁 The domain to be used for the view. (Salesforce is shown.)
   
3. On the Choose Data page, select columns that you want to use in the view. In the Pre-Filters section, you can create filters based on the selected fields. In the Display section, you can modify column information as presented by the domain. 👁 The columns to be used in the report. (Salesforce is shown.)
   
4. After completing the wizard, select the view type and add columns to the view. JasperSoft uses the metadata of the driver to detect the available dimensions and measures, based on the data type. Drag dimensions and measures onto the Columns and Rows boxes. Right-click a measure to change the summary calculation. Drag the Columns slider to the the desired data level in the Filters section.
5. To access updates to the data as you design the view, change the menu selection from Sample Data to Full Data in the design mode toolbar. As you make changes, JasperSoft Server executes the underlying SQL queries to the driver.
6. Save the view.

Create a Real-Time Report

You can now create reports hosted on JasperSoft Server. To do so from the Ad Hoc Editor, click Save Ad Hoc View and click Create Report.