Hitachi Vantara Pentaho Community Wiki
Child pages
  • 4. Data Sources
Skip to end of metadata
Go to start of metadata

Data sources define how a client device can access a database. Report Designer includes three built-in data sources:

  • Data Source (formerly Pentaho Datasets) is used to access the relational, OLAP (Mondrian), Pentaho metadata, and XML data source types. These data sources are commonly used to fill the item band.
  • Custom Data Source (formerly Custom Datasets) is used to access data that cannot be accessed from JDBC or XQuery.  This data source type requires a developer to write Java code to access the data for the report.
  • Data Properties (formerly Properties Dataset) is used to supply additional values to the report such as a date, a company name, and so on. Data Properties are used if you want to pass parameters (inputs) into the report when using a Pentaho action sequence.
     

     

Note: Multiple data sources can be defined on report; however, only one data source can be used in a report for groups and items bands. If a report requires data from multiple data sources, use the Sub-Report objects in the report. See the section on Sub Reports for more information.

(minus) Important: You may need to contact your database administrator to get details (JDBC driver, connection string, user name and password) for configuring your database. The sample above displays the configuration for accessing a HypersonicSQL database that contains the Pentaho sample reports data.

Data Source Access

Relational data sources are accessed through JDBC (Java Database Connectivity), an interface that allows Java programs to access database management systems and JNDI (Java Naming and Directory Interface, an interface that allows a Web server to locate the database. SQL is used to query relational data sources.

An OLAP (Mondrian) Data Source is accessed through JDBC and require a valid Mondrian Schema file (.mondrian.xml). MDX is used to query this data source.

Pentaho metadata data sources are accessed through JDBC and require a valid metadata model (.xmi. The Pentaho MQL is used to query this data source.

XML Data Sources are accessed through XQuery and require that your data is in one xml file. XQuery is used to query this data source.

Note: Currently, Pentaho provides the following JDBC drivers with Report Designer; (the list below does not include all possible drivers):

  • Hypersonic
  • MySQL
  • Postgres
  • Derby
  • DB2
  • H2
  • Oracle

Configuration Properties for JNDI

The configuration properties for a JNDI connection are listed below:

Configuration

Description

JNDI Name

An easy-to-remember name that identifies the data you are accessing

Driver

Fully qualified Java class name of the JDBC driver to be used

Connection String

Connection URL to be passed to the JDBC driver

User Name

Database user name to be passed to the JDBC driver

Password

Database password to be passed to the JDBC driver

Query-Format for the Custom Data Source

The Custom Data Source uses Java-reflection to instantiate a Java-class or call a method on an instantiated class. The general syntax for the queries is:

<full-qualified-class-name><constructor-parameters>? ('#' <method-name><method-parameters>?)?

Example:
(1) The target class is a TableModel implementation and has a default constructor

org.jfree.report.test.base.basic.StaticDataSourceFactoryTestSupport

(2) The target class is a TableModel implementation and has a parametrized constructor

org.jfree.report.test.base.basic.StaticDataSourceFactoryTestSupport(parameter1,parameter2)

(3) The target class contains a unparameterized method (either static or not, does not matter)

org.jfree.report.test.base.basic.StaticDataSourceFactoryTestSupport#createTableModel

(4) The target class contains a parameterized method (either static or not, does not matter)

org.jfree.report.test.base.basic.StaticDataSourceFactoryTestSupport#createTableModel(parameter1,parameter2)

(5) The target class is a bean contains a unparameterized method (not static!) and the object itself uses a default constructor

org.jfree.report.test.base.basic.StaticDataSourceFactoryTestSupport()#createTableModel

(6) The target class contains a parameterized method (not static!) and the object is instantiated using a parameterized constructor

org.jfree.report.test.base.basic.StaticDataSourceFactoryTestSupport(parameter1,parameter2)#createTableModel(parameter1,parameter2)

"parameter1" and "parameter2" used in the query are names of a field from the current data-row or the initial report-parameters. The value of these fields is passed into the method. The algorithm does not check parameter types like a real compiler would do; it uses the first method that matches the number of parameters and tries to force the parameters in. If that fails, the data-source reports an error and the report-processing fails.

  • No labels

1 Comment

  1. Hi,

     In the Custom DS:

     - it's required to create a jar with the class?

     - to where is the jar file copied?

     - the method must return what?

     Thanks