A data source is a resource in the repository that defines how and where to obtain the data displayed by reports or OLAP views. Typically, it includes the location of the data and the details you need to access it, such as a user name and password. For example, to access an RDBMS (Relational Database Management System) that stores your data, you would create a data source that included the URI of the database server and user credentials.
JasperReports Server can access any database using the JDBC (Java DataBase Connectivity) API. Most database vendors provide a JDBC driver to access their product, for example DB2, MySQL, Oracle, PostgreSQL, and Vertica (to name but a few). In this case, you can configure two types of data sources in the repository:
| • | JDBC data source – Establishes a direct connection to the database server using its JDBC driver. JasperReports Server configures and manages the connections to the database. By default, the maximum number of simultaneous connections for each data source is five. If the driver is not installed, the server provides an interface for the system administrator to upload and manage JDBC drivers. |
| • | JNDI data source – Calls the JNDI (Java Naming and Directory Interface) service of the application server to locate a database connection. The application server manages the connections to the database and shares them between all applications that it hosts. The configuration of the application server determines the number of connections that are shared. Note that the application server connects to the database using JDBC, meaning that JNDI data sources return results in the same format as JDBC data sources. |
JasperReports Server also supports additional data source types:
| • | Amazon Web Services (AWS) data sources – Accesses data stored in your AWS data store using JasperReports Server, either on-premises or in the cloud. |
| • | Hadoop-Hive data source – Accesses big data in Apache Hadoop clusters accessed through Apache Hive. Because Hive uses the Hive Query Language (HiveQL) that is similar to SQL but distinct, it has its own data source type. |
| • | Mongo DB data source – MongoDB is another big data product that the server can access through a custom data source type. |
| • | Virtual data source – Allows you to combine multiple JDBC and/or JNDI data sources into a single JasperReports Server data source. |
| • | Bean data source – Allows you to access custom-made report data sources in the form of JavaBeans-based data sources. |
| • | Internal diagnostic data source – A custom type that always creates a data source for the server’s own diagnostic data. The diagnostic information is only available to system admins (superuser by default). For more information, see Using the Diagnostic Data In Reports. |
This section discusses JDBC, JNDI, AWS, Hadoop-Hive, MongoDB, virtual, and bean data sources.
In the case of analysis data, JasperReports Server supports OLAP data sources (such as Mondrian and XML/A connections). For information about analysis data sources, refer to the Jaspersoft OLAP Ultimate Guide.
|  | You can extend JasperReports Server to support any custom data source. Custom data sources consist of Java implementation classes, a message catalog, and a Spring bean definition. For more information about custom data sources, see the JasperReports Server Ultimate Guide. |
JDBC Data Sources
To access an RDBMS from JasperReports Server using JDBC you must have a driver, which must be accessible in the server’s classpath. Jaspersoft provides a number of JDBC drivers for popular databases. These drivers are available directly in the installed product and do not require further configuration.
As of JasperReports Server 5.1, you can add and update JDBC drivers through the user interface, without needing to restart the server. Only the system administrator can manage the JDBC drivers, but once they are uploaded, they are available to all administrators who create data sources. For more information on JDBC drivers, see Managing JDBC Drivers.
To create a JDBC data source:
| 1. | Log on as an administrator. |
| 2. | Click View > Repository, expand the folder tree, and right-click a folder to select Add Resource > Data Source from the context menu. Alternatively, you can select Create > Data Source from the main menu on any page and specify a folder location later. If you installed the sample data, the suggested folder is Data Sources. |
The Add Data Source page appears.
| 3. | In the Type field, select JDBC Data Source. |
The page refreshes to show the fields necessary for a JDBC data source.
| 4. | Enter a name and optional description for the data source. The resource ID is filled in automatically based on the name. |
| |
| Setting the Data Source Type |
| 5. | Select the JDBC driver for your database. If your driver is listed as NOT INSTALLED, see Managing JDBC Drivers. |
| |
| Setting the JDBC Driver |
| 6. | Enter the hostname, port, and database name for your database. The default hostname is the localhost, and the default port is the typical port for the specified database vendor. The three fields are combined automatically to create the JDBC URL that the server will use to access the database. |
For more information about JDBC URLs, including optional parameters, see the troubleshooting JDBC Database URLs.
| 7. | Fill in the database user name and password. These are the credentials that the server will use to access the database. |
| |
| Testing the JDBC Connection |
| | Set the Time Zone field when the datetime values stored in the target RDBMS do not indicate a time zone. When datetime values are stored in a format other than local time zone offset relative to Greenwich Mean time (GMT), you must specify a time zone so that the server can convert datetime values read from the target database properly. Set the Time Zone field to the correct time zone for the data in the data base. When in doubt, leave the Time Zone field blank. The list of time zones is configurable, as described in appendix section Specifying Additional Time Zones. |
| 8. | Click Test Connection to validate the data source. If the validation fails, ensure that the values you entered are correct and that the database is running. To diagnose JDBC connection issues, you can turn on logging as described in the troubleshooting JDBC Database URLs. |
| 9. | When the test is successful, click Save. The data source appears in the repository. |
Managing JDBC Drivers
As of JasperReports Server 5.1, you can manage JDBC drivers through the user interface so that they are available immediately without needing to restart the server.
To add or update a JDBC driver:
| 1. | Log on as the system administrator (superuser on commercial editions, jasperadmin on community editions). |
| 2. | Select Create > Data Source from the main menu. |
| 3. | In the Type field, select JDBC Data Source. |
The page refreshes to show the fields necessary for a JDBC data source.
| 4. | The dropdown selector for the JDBC Driver field shows the JDBC driver that are available and those that are not installed. |
| |
| Viewing the List of Available JDBC Drivers |
| 5. | If you want to add a driver that is not installed, select it from the list, then click Add Driver. |
| |
| Adding a JDBC Driver |
| 6. | In the Select Driver dialog that appears, click Browse to locate the appropriate driver JAR file. |
| 7. | Click Upload to install the driver and make it available immediately. |
| 8. | If you want to update a driver that is already installed, select it from the list, then click Edit Driver. |
| |
| Updating a JDBC Driver |
| 9. | In the Select Driver dialog that appears, click Browse to locate the appropriate driver JAR file. |
| 10. | Click Upload to replace the existing driver and make it available immediately. |
To remove an uploaded JDBC driver:
| 1. | Log on as the system administrator (superuser on commercial editions, jasperadmin on community editions). |
| 2. | Select View > Repository and open the System Properties folder at the root. |
| 3. | Right click the GlobalPropertiesList resource and select Edit from the context menu. |
| 4. | Locate the driver you uploaded in the list of properties. The drivers with the value [sYSTEM] are the default drivers configured at installation time. |
| 5. | Click Remove beside the driver you want to remove. |
| |
| Removing an Uploaded JDBC Driver |
| 6. | Click Submit to save your changes. |
| | If the JDBC driver you remove was one that updated a default driver, the default driver will reappear as a [sYSTEM] driver in the GlobalPropertiesList after the next time you use the Add Data Source wizard. |
JNDI Data Sources
Adding a JNDI data source is very similar to adding a JDBC data source. The JNDI data source points to an existing connection that is defined in the application server and published as a JNDI resource or service. Instead of specifying a driver and database as you do with JDBC data sources, you only need to specify the JNDI service name in your application server.
| | Application servers use JDBC connections themselves to expose a database through JNDI. You must specify the JNDI service name of a JDBC connection. |
For information about setting up a JNDI connection in your application server, see the following sections:
To create a JNDI data source:
| 1. | Log on as an administrator. |
| 2. | Click View > Repository, expand the folder tree, and right-click a folder to select Add Resource > Data Source from the context menu. Alternatively, you can select Create > Data Source from the main menu on any page and specify a folder location later. If you installed the sample data, the suggested folder is Data Sources. |
The Add Data Source page appears.
| 3. | In the Type field, select JNDI Data Source. |
The information on the page changes to reflect what’s needed to define a JNDI data source.
| 4. | Fill in the required fields, along with any optional information. |
The service name is the name that the application server exposes through JNDI. The following figure shows values for connecting to the JNDI service for the Foodmart database included in the sample data.
| |
| JNDI Data Source Page |
| | Set the Time Zone field when the datetime values stored in the target RDBMS do not indicate a time zone. When datetime values are stored in a format other than local time zone offset relative to Greenwich Mean time (GMT), you must specify a time zone so that the server can convert datetime values read from the target database properly. Set the Time Zone field to the correct time zone for the data in the data base. When in doubt, leave the field blank. The list of available time zones is configurable, as described in Specifying Additional Locales. |
| 5. | Click Test Connection to validate the data source. If the validation fails, ensure that the values you entered are correct, that the database is exposed through JNDI, and that the database is running. Also, see the troubleshooting JNDI Services on Apache Tomcat. |
| 6. | When the test is successful, click Save. The data source appears in the repository. |
For details about configuring a JNDI database connection at the application server level and making it available to the server’s applications, refer to the documentation provided with your application server.
AWS Data Sources
Amazon Web Services (AWS) is a set of products that provide computation and data storage on demand in the cloud. Jaspersoft partners with Amazon to deliver business intelligence solution based on AWS.
JasperReports Server supports two of the AWS database services as data sources for reporting:
| • | Amazon Relational Database Service (RDS) |
| • | Amazon Redshift data warehouse |
JasperReports Server can access either of these services when you define a data source with the correct configuration information and credentials. The AWS data source wizard uses the AWS credentials you provide to discover RDS and Redshift data sources. Then it uses those credentials to properly configure security groups to maintain the connection between JasperReports Server and the AWS data source, even when the IP address changes. You can access AWS data sources from both stand-alone server instances that you maintain on your own computers and virtual server instances that you run on Amazon’s Elastic Compute Cloud (EC2). For more information, see https://www.jaspersoft.com/cloud.
To create an AWS Data Source:
| 1. | Log into JasperReports Server as an administrator. |
| 2. | Click View > Repository, expand the folder tree, and right-click a folder to select Add Resource > Data Source from the context menu. Alternatively, you can select Create > Data Source from the main menu on any page and specify a folder location later. If you installed the sample data, the suggested folder is Data Sources. |
The Add Data Source page appears.
| 3. | In the Type field, select AWS Data Source. |
The information on the page changes to reflect what’s needed to define an AWS data source.
| 4. | Enter a name, optional description and location in the repository to store the data source. |
| Set Data Source Type and Properties Page |
| |
| 5. | Under the AWS Settings heading, specify your Amazon credentials in one of the following ways: |
| Selecting AWS Credentials |
| |
| • | If your JasperReports Server is running in Amazon’s EC2 service, and it has the proper instance role assigned, the server will detect this and automatically use your EC2 credentials. Using the EC2 instance credentials requires that the role was properly set up and assigned when the instance was created. If you are using the EC2 service, Jaspersoft strongly recommends that you use the EC2 credentials. |
| • | If your JasperReports Server is not running on Amazon’s EC2, enter the AWS credentials associated with the RDS or Redshift service. If you don’t have AWS keys, click Generate credentials, then look for them on the Outputs tab for your Stack on the Amazon console: |
| AWS Access and Secret Keys |
| |
| 6. | Under the Select an AWS Data Source heading, specify the connection details of the AWS data source that you want to connect to: |
| a. | Select your AWS Region from the drop-down. |
| b. | Click the Find My AWS Data Sources button. |
The AWS data source queries your environment and displays your available data sources. See Select an AWS Data Source Section.
| c. | Select your data source. |
| d. | Enter your user name, password, and database name. |
The AWS data source queries your environment and adds the appropriate driver and URL.
| Select an AWS Data Source Section |
| |
| 7. | When you’ve entered all the information, click Test Connection. |
If your connection is successful, a message appears at the top of the screen. Sometimes the process takes a few minutes. In that case you will see an alert. Try the test again after one or two minutes. The test performs the following actions:
| • | Validates the user name and password. |
| • | Creates a database security group. |
| • | Adds the IP address of your JasperReports Server instance to the security group to authorize ingress to the data service (RDS or Redshift). |
If you want to control details of the security group name or specify the IP address manually because you have a complex VPC Topology, see Configuring Amazon Web Services. You can also change the default JDBC driver through the configuration.
The new data source appears in the repository.
Hadoop-Hive Data Sources
Unlike traditional databases, Hadoop systems support huge amounts of data, often called big data. But this capability has a cost: high latency with access times on the order of 30 seconds up to 2 minutes. As a result, Hadoop-Hive data sources have certain limitations and guidelines for use in JasperReports Server:
| • | Hadoop-Hive data sources are not supported for OLAP connections, Domains, or virtual data sources. |
| • | Hadoop-Hive data sources are not suitable for creating reports interactively in the Ad Hoc Editor. |
| • | Reports based on Hadoop-Hive are not suitable for dashboards. |
| • | Filters and query-based input controls that rely on Hadoop-Hive data sources will be slow to populate the list of choices. |
| • | You must configure your query limits and timeout to handle latency (see Configuring Ad Hoc. |
| • | You must configure your JVM memory to handle the expected data (see the JasperReports Server Installation Guide). |
In general, reports based on Hadoop-Hive data sources are best suited to be run in the background from the repository. For very large reports, consider scheduling them to run at night so the output is available immediately when you need it during the day.
To create a Hadoop-Hive data source:
| 1. | Log on as an administrator. |
| 2. | Click View > Repository, expand the folder tree, and right-click a folder to select Add Resource > Data Source from the context menu. Alternatively, you can select Create > Data Source from the main menu on any page and specify a folder location later. If you installed the sample data, the suggested folder is Data Sources. |
The Add Data Source page appears.
| 3. | In the Type field, select Hadoop-Hive Data Source. |
The information on the page changes to reflect what’s needed to define a Hadoop-Hive data source.
| |
| Hadoop-Hive Data Source Page |
| 4. | Fill in the required fields, along with any optional information. |
The Hive JDBC URL has the form: jdbc:hive://<hostname>:10000/default
| 5. | When done, click Save. The data source appears in the repository. |
MongoDB Data Sources
MongoDB is a big data architecture based on the NoSQL model that is neither relational nor SQL-based. Jaspersoft provides a connector that allows reports to use MongoDB as a data source. Reports based on a MongoDB data source can be used as Topics that allow users to create Ad Hoc views based on the fields returned by the MongoDB query. However, Domains require relational data sources, and therefore MongoDB data sources cannot be used in Domains.
To create a MongoDB data source:
| 1. | Log on as an administrator. |
| 2. | Click View > Repository, expand the folder tree, and right-click a folder to select Add Resource > Data Source from the context menu. Alternatively, you can select Create > Data Source from the main menu on any page and specify a folder location later. If you installed the sample data, the suggested folder is Data Sources. |
The Add Data Source page appears.
| 3. | In the Type field, select MongoDB Data Source. |
The information on the page changes to reflect what’s needed to define a MongoDB data source.
| |
| MongoDB Data Source Page |
| 4. | Fill in the required fields, along with any optional information. |
The MongoDB URL has the form: mongodb://<hostname>:27017/<database>
| 5. | When done, click Save. The data source appears in the repository. |
MongoDB is designed to be accessed through API calls in an application or a command shell. As a consequence, it does not have a defined query language. In order to write queries for MongoDB data sources, Jaspersoft developed a query language based on the JSON-like objects upon which MongoDB operates. JSON is the JavaScript Object Notation, a textual representation of data structures that is both human- and machine-readable.
The Jaspersoft MongoDB Query Language is a declarative language for specifying what data to retrieve from MongoDB. The connector converts this query into the appropriate API calls and uses the MongoDB Java connector to query the MongoDB instance. The following examples give an overview of the Jaspersoft MongoDB Query Language, with SQL-equivalent terms in parentheses:
| • | Retrieve all documents (rows) in the given collection (table): |
| • | From all documents in the given collection, select the named fields (columns) and sort the results: |
| • | Retrieve only the documents (rows) in the given collection (table) that match the query (where clause). In this case, the date is greater-than-or-equal to the input parameter, and the name matches a string (starts with N): |
The Jaspersoft MongoDB Query Language also supports advanced features of MongoDB such as map-reduce functions and aggregation that are beyond the scope of this document. For more information, see the language reference on Jaspersoft's community website.
Virtual Data Sources
Virtual data sources allow you to combine data residing in multiple JDBC or JNDI data sources into a single data source that can query the combined data. You can combine any number of JDBC or JNDI data sources, including schemas from different databases, databases from different vendors, and different schemas within the same database into a single object. Once you have created a virtual data source, you create Domains that join tables across the data sources to define the relationships between the data sources and allow you to design views and reports based on the combined data. For more information about Domains, see the chapter Creating Domains in the JasperReports Server User Guide.
| Virtual Data Source Scenario |
| |
When configuring a new virtual data source, you should make sure that each data source you want to include has been configured as a JDBC or JNDI data source, as described in sections JDBC Data Sources and JNDI Data Sources. Virtual data source do not support Hadoop-Hive data sources.
When you combine data sources into a virtual data source, you select an alias for each data source you include; this alias is added as a prefix to the tables in the original data source to ensure that table names are unique across the virtual data source.
To create a virtual data source:
| 1. | Log on as an administrator. |
| 2. | Click View > Repository, expand the folder tree, and right-click a folder to select Add Resource > Data Source from the context menu. Alternatively, you can select Create > Data Source from the main menu on any page and specify a folder location later. If you installed the sample data, the suggested folder is Data Sources. |
The Add Data Source page appears.
| 3. | In the Type field, select Virtual Data Source. |
| 4. | Fill in the other required fields, along with any optional information. |
| 5. | Locate the data sources you want to use in the Available Data Sources pane. Double-click each desired data source to select it. The data source is shown in the Selected Data Sources pane. |
| 6. | Change the Aliases by editing them directly, if desired. The Alias is used to identify the selected data source within the virtual data source; it is also added as a prefix to the name of each table in that data source. Spaces are not allowed. |
| 7. | Click Save. The data source appears in the repository. |
The following figure shows values for creating a virtual data source by combining two of the databases included in the sample data: the Foodmart database and the SugarCRM database.
| |
| Creating a Virtual Data Source |
| | Virtual data sources cannot use the Time Zone field that may be set on individual data sources. If used in a virtual data source, a target data source with a time zone will not return the expected date/time values. Therefore, Jaspersoft recommends that you do not use data sources with time zone settings in a virtual data source. |
You can edit a virtual data source to add or remove the data sources it uses. If the virtual data source is used by a Domain, you can add data sources, but you cannot remove them. Removing a data source from a virtual data source modifies only the virtual data source; the data source you removed remains in the repository.
To edit a virtual data source:
| 1. | Log on as an administrator. |
| 2. | Click View > Repository and expand the folder tree to locate the folder containing the data source. |
| 3. | Right-click the data source and select Edit from the context menu. |
| 4. | To add a data source, locate the data source in the Available Data Sources pane and double-click. To remove a data source, select it in the Selected Data Sources pane and click the left arrow. |
| 5. | Click Save. If you are attempting to delete a data source from a virtual data source that is used by a Domain, you receive a warning and the data source is unchanged. Otherwise, the data source is updated in the repository. |
Bean Data Sources
The bean data source type is a key extension because it allows you to make use of any custom or exotic data that you might need to report on. Bean data sources serve as a bridge between a Spring-defined bean and a JasperReport. The Spring bean is responsible for providing the data or parameters that fill the report.
To use a bean data source, you must first configure the underlying Spring bean and make it available in the server’s web application context. For example, you would add a bean definition to one of the WEB-INF/applicationContext*.xml files.
The bean must resolve to a ReportDataSourceService instance, either directly or by way of a factory no-argument method. You can use any Spring instantiation method (for example, a constructor or factory) and bean scope (for example, singleton or prototype) for the data source service bean.
The ReportDataSourceService instance is responsible for supplying data source parameters to the JasperReport. Custom ReportDataSourceService implementations can follow two approaches:
| • | If the implementation can provide the data to be used to fill a report, it needs to wrap the data into a suitable JRDataSource implementation and pass the data using the REPORT_DATA_SOURCE report parameter. |
| • | If the data comes from the report query by way of a JasperReports query executor, the data source service must set values for the connection parameters defined by the query executor. The connection parameters are usually obtained from the properties of the data source service instance. |
For example, you could implement a Hibernate data source service that would be injected in a session factory. The factory would create a Hibernate session that would be passed as a value for the HIBERNATE_SESSION parameter. The JasperReports Hibernate query executor then uses the parameter to run the HQL report query.
The ReportDataSourceService interface contains two methods: setReportParameterValues and closeConnection. The former provides data and connection parameter values; the latter is required to close and release any resources or connections created during the call to setReportParameterValues.
Once the data source service bean is available through Spring, you can add the bean data source to the repository.
To create a bean data source:
| 1. | Log on as an administrator. |
| 2. | Click View > Repository, expand the folder tree, and right-click a folder to select Add Resource > Data Source from the context menu. Alternatively, you can select Create > Data Source from the main menu on any page and specify a folder location later. If you installed the sample data, the suggested folder is Data Sources. |
The Add Data Source page appears, as shown in Bean Data Source Page.
| 3. | In the Type field, select Bean Data Source. |
The information on the page changes to reflect what’s needed to define a bean data source.
| 4. | Fill in the required fields, along with any optional information. |
If the data source service is to be instantiated through a factory method of the Spring bean, you should also enter the name of the method.
| 5. | Click Test Connection to validate the data source. |
If the validation fails, ensure that the values you entered are correct and that the bean is in the classpath.
| 6. | When the test is successful, click Save. |
| |
| Bean Data Source Page |
Recommended Comments
There are no comments to display.