Pentaho Reporting

download Pentaho Reporting

of 17

description

This document specifies various formats of reporting in Pentaho.

Transcript of Pentaho Reporting

  • 5/25/2018 Pentaho Reporting

    1/17

    CREATING A REPORT USING DIFFERENT TYPES OF DATA-SOURCES

    TABLE:First, define a dataset that you want to report on. Enter some example data into a table

    dataset. Click on the Data tabon the right-hand side of Report Designer. Now, right-click on the

    Data Sets tree itemand select Table. The following dialog will appear:

    Click the add query image button, and then enter the Name as default for the table. This name

    will be referenced in your report as the main source of data. Click the add columnimage button

    to add a third column. Double-click and edit the column headers to be Library Name, Library

    Description, andLibrary Size. Double-click on the Library Size column header table cell and

    selectjava.lang.Integer as the data type for this column.

    Enter the data into the table cells. Once you've entered data in your table, the Table

    Datasource Editor dialog should look similar to this..

  • 5/25/2018 Pentaho Reporting

    2/17

    After entering data in your table, the Table Datasource Editordialog should look similar to this:

    Data quoted in this example is:

    Library Name Library Description Library Size

    LibBase Library containing common

    functions

    121745

    LibLoader Loading and caching library 122900

    LibSerializer Java serialization utility library 25689

    LibRepository Hierarchical storage library 63655

    After creating table, the right side Data-panel shows the columns of the table created, shown in

    the following diagram:

  • 5/25/2018 Pentaho Reporting

    3/17

    The respective columns, from the Data-tree can be dragged over respective parts of the report

    design. The subsequent properties of the Labels and text boxes can be altered to give a user-

    defined design.

    Once the report-design is created, we can view the executed from through either of the

    following options:

    - Print-preview- PDF- HTML- Excel- Excel 2007- RTF- Text- CSV

  • 5/25/2018 Pentaho Reporting

    4/17

    PRINT PREVIEW:

    PDF :

  • 5/25/2018 Pentaho Reporting

    5/17

    HTML :

    EXCEL :

  • 5/25/2018 Pentaho Reporting

    6/17

    RTF:

    TEXT:

  • 5/25/2018 Pentaho Reporting

    7/17

    The Metadata SourceMetadata allows a designer to describe database fields, give them default formats, join tables in a

    meaningful way, and calculate new values amongst other things. Each metadata data source can holdmultiple queries. The metadata is shared between all queries. You can use meaningful names for the

    queries in the metadata source.

    It allows the dry, technical database structure that may only be understood by a few people to be

    transformed into an easy to understand business model that can reliably be used by many report

    designers.

    fields are named using business language meaningful table joins have already been created authorization controls for critical data elements have already been applied meaningful formats for data values have already been applied

    Adding the Metadata Source

    I.

  • 5/25/2018 Pentaho Reporting

    8/17

    II. Select the "Add Data Sources" button in the toolbar, or right click with your mouse on the data-

    sources node to open up the context menu.

  • 5/25/2018 Pentaho Reporting

    9/17

    III. Select the "Metadata" option. The Metadata source editor opens up.

    Each metadata data source can hold multiple queries. The metadata is shared between all queries. You

    can use meaningful names for the queries in the metadata source.

    Instead of typing SQL to create the query a query editor will open.

  • 5/25/2018 Pentaho Reporting

    10/17

    Select the columns you want to use to select ythe data for your report and move these to the

    "Conditions" section using the yellow arrow next to that section.

    IV .

    Browse to find the "metadata.xmi" file

  • 5/25/2018 Pentaho Reporting

    11/17

    Click on the Preview button to see what gets returned. You should see this dialog:

  • 5/25/2018 Pentaho Reporting

    12/17

    The column headings / field names are more readable - they can contain spaces and upper and lower

    case characters. In this case they are almost the same as the original field names, but are more

    readable.

    V. Creating Reports:

    The available fields and functions can be seen in the data source window and dragged to the report.

    Select the "Business Model" that will best support your report. The "Categories / Columns" window will

    populate with the available fields.

    Select each column which you need from this window and move it into the "Selected Columns" section

    by clicking on the yellow arrow next to that section.

    For each column you move into this section you may choose from the aggregations that the metadata

    definition allows. For instance a column containing a number

    may allow the "Sum", "Average", "Minimum", "Maximum" and "Count" aggregations, while a columncontaining a string may only allow "Count".

    Similarly select the columns you want to use to select the data for your report and move these to the

    "Conditions" section using the yellow arrow next to that section.

  • 5/25/2018 Pentaho Reporting

    13/17

    Creating a report in pdf format:

  • 5/25/2018 Pentaho Reporting

    14/17

    JDBC CONNECTION :I.

    First, define a dataset that you want to report on. Enter some example data into a table

    dataset. Click on the Data tabon the right-hand side of Report Designer. Now, right-click on the

    Data Sets tree itemand selectJDBC. The following dialog will appear:

    II .)

    Click the add query image button, and then enter the Name as query1 /default for the table.

    This name will be referenced in your report as the main source of data.

    SQL Query designer window pops up.

    III. ) Drag and drop the tables required, into the blank panel.

  • 5/25/2018 Pentaho Reporting

    15/17

    IV .) The tables dragged into the panel can be joined by just dragging and clicking on the

    respective fields, as shown in the diagram.

    V .)Define the query in the static query panel.

  • 5/25/2018 Pentaho Reporting

    16/17

    Following Query has been used in this text:

    SELECT

    "PAYMENTS"."CUSTOMERNUMBER",

    "CUSTOMERS"."CUSTOMERNAME","PAYMENTS"."PAYMENTDATE",

    "PAYMENTS"."AMOUNT",

    "CUSTOMERS"."PHONE",

    "CUSTOMERS"."ADDRESSLINE1",

    "CUSTOMERS"."CITY",

    "CUSTOMERS"."CUSTOMERNUMBER"

    FROM "PAYMENTS" INNER JOIN "CUSTOMERS"

    ON "PAYMENTS"."CUSTOMERNUMBER" = "CUSTOMERS"."CUSTOMERNUMBER" ;

    VI .) The query can be previewed by clicking on the preview button, found in the last panel.

    Following is the output obtained.

  • 5/25/2018 Pentaho Reporting

    17/17