Learn Pentaho - Reporting & Dashboarding

#Introduction

Once the data is neatly processed by ETL, it's time to present it. This episode covers reporting and dashboarding — how the data you prepare becomes tabular reports, charts, and dashboards that business users can read.

You'll get to know the Pentaho Report Designer for reports, then bring the results to dashboards in the Pentaho User Console. By the end of the episode, you'll understand the complete cycle: data ready in the warehouse, presented as reports, and shared with stakeholders.

#Introducing the Pentaho Report Designer

Pentaho Report Designer is a desktop editor for creating reports in the .prpt format. You can build reports with drag-and-drop: set up a data source, place fields on the report area, and format the display. On Linux, the application is launched with ./report-designer.sh from the installation folder.

Core concepts to understand early:

• Data source: a report is drawn from a database query or another source. Usually you select a connection and write SQL.
• Report bands: report areas — Page Header, Report Header, Details, Page Footer, and so on. Data fields are placed in the Details band, which automatically repeats for every row.
• Parameters: inputs that change the report contents, for example a date range or branch.

Before creating a report, prepare the data source. Report Designer asks for the database connection and the query that will be the data source:

SELECT tanggal, cabang, SUM(jumlah) AS total_penjualan
FROM fact_penjualan
WHERE tanggal BETWEEN ? AND ?
GROUP BY tanggal, cabang

The question marks are placeholders later filled by report parameters — this is the basis of parameterized reports.

Besides SQL, Report Designer supports data sources from already-stored ETL results — for example warehouse tables filled by PDI jobs. The most common production pattern: ETL prepares aggregate tables overnight, then the report only reads that table. Reports become fast because they don't compute heavy aggregations when opened.

#Creating Tabular and Chart Reports

A tabular report is the most basic report: rows and columns from the queried data. In Report Designer, drag fields from the data source into the Details band, set column labels in the Report Header, then run a preview to see the result.

A chart report shows a visual summary. Pentaho provides various chart types: bar, line, pie, and others. The pattern:

1. Add a chart element in the appropriate band.
2. Define the data for the chart — usually the result of a GROUP BY.
3. Choose the chart type and set labels and values.

For more interactivity, some charts can be configured to drill down or link to other reports. This feature makes static reports feel more alive and supports layered analysis.

#Creating Parameterized Reports

Parameters let one report serve many needs. For example: a single sales report can be filtered per branch or per date range without creating separate reports.

In Report Designer:

1. Define parameters, for example TGL_AWAL of type Date and CABANG of type String.
2. Use the parameters in the SQL query with the appropriate syntax.
3. When the report runs, the user is prompted to fill in the parameter values.

Parameters can also be filled automatically from a value list pulled from the database — for example the list of available branches. This prevents users from typing wrong values.

#Integrating Reports into Dashboards

Individual reports are useful, but a dashboard unites everything on one page. In the Pentaho User Console, a dashboard is a page combining several reports, charts, and text on one screen — so readers see the full picture without jumping around.

The steps:

1. Publish the .prpt report to the server repository.
2. Create a new dashboard in PUC.
3. Add elements — reports, charts, and others — then arrange their positions.
4. Link parameters between elements so one filter changes several components at once.

The result: one page showing trends, distributions, and detail tables simultaneously — a starting point for management decisions.

#Publishing and Distributing Reports

A finished report must reach its readers. There are several distribution paths:

• Stored in the repository: users with access rights can open the report any time from PUC.
• Scheduled: reports are generated automatically on a schedule — for example a weekly report every Monday morning.
• Sent by email: report results (PDF, Excel, CSV) are sent automatically to a recipient list.
• Public URL: in some cases, reports can be shared via a link.

This scheduling and email delivery is usually configured from the Schedule menu in PUC. The combination of ETL (episodes 5 and 10) with scheduled report distribution is what keeps operations running without manual intervention.

The available output formats during distribution — PDF for human reading, Excel/CSV for further processing — can be chosen per schedule. Schedule different formats for different audiences: management gets a concise PDF, the analyst team receives the complete Excel. That way, one report serves two different needs at once.

Before turning on a distribution schedule, always test the report manually first: open the result, check the data, and make sure the format displays correctly. A report sent automatically every morning with wrong content is an expensive reputation risk — far better to find the problem on the first test than after hundreds of recipients read incorrect numbers.

#Conclusion

In episode 11 you brought data to business users: getting to know the Pentaho Report Designer, creating tabular and chart reports, building parameterized reports, integrating them into dashboards in PUC, and publishing and distributing reports.

The key takeaways:

• Report Designer produces .prpt files built from data sources, bands, and parameters.
• Parameters turn one report into many views without duplicating files.
• Dashboards combine several reports and charts on one screen with shared filters.
• Publishing and scheduling deliver reports to readers without manual intervention.

In episode 12, we secure the entire platform: security, authentication & authorization — protecting the Pentaho Server with SSL/TLS, configuring internal, LDAP, and Active Directory authentication, implementing role-based access control, and securing data source credentials.