]po[ Developers: Reporting, Indicators & DashboardsFrank Bergmann, 2013-04-06

This tutorial explains various options on how to extract and display information from ]project-open[.

Different Types of Reports

Reporting is one of the most basic requirements for a business application.

]project-open[ provides a large variety of different ways to display data with

differing degrees of sophistication and complexity:

Simple SQL Query in pgAdminIII or PSQL:Simply execute a SQL query using a PostgreSQL client outside of ]po[

MS-Access Report:You can store MS-Access reports in a ]po[ filestorage or even link to it using a “menu”

MS-Excel Report:You can define the ]po[ database as a data-source for MS-Excel.

im_ad_hoc_query $sql in /ds/shell:Useful for ad-hoc queries and for development.

im_ad_hoc_query $sql in a TCL page:Easy way to deploy an ad-hoc query in a production system. You can also add a menu to the reporting section.

Dynamic Report (Reporting -> New Report):Similar to ad-hoc query, but with built-in deployment.

]po[ HTML Reporting Engine:The ]po[ reporting engine allows for grouping, aggregation, sub-totals and grand totals.

]po[ OpenOffice “PowerPoint” Reporting Engine:This reporting engine allows to create OpenOffice Impress (PowerPoint) slides based on templates.

Indicator:A special type of report that returns only a single numeric value. ]po[ takes care to regularly evaluate the SQL query and to store the values to create a timeline.

Histogram Chart:A histogram is a Portlet that takes a SQL query as an argument. The query needs to return rows with Name – Value pairs.

Data-Warehouse Cube:DW-Cubes may already cover your reporting needs. You can aggregate by drop-down type of DynFields.

Pie Chart based on DW-Cube:You can show a pie chart of the summary of a DW cube.

Sencha Charts:You can setup a “cron” script to save report results as a static HTML page at night.

Nightly Evaluation of Long Running Reports:You can setup a “cron” script to save report results as a static HTML page at night.

Simple SQL Query in pgAdminIII or PSQL

pgAdminIII allows you to execute SQL queries manually.

pgAdminIII allows to save SQL queries in .sql files

Advantages:– Quick development– Integrated with tree-view to

the database, tables, views etc. Disadvantages:

– Requires manual execution

MS-Access Report

You can use Microsoft Access as a reporting engine:

MS-Access can access the ]project-open[ PostgreSQL database without problems

You can use all available MS-Access tools to develop your reports

Advantages:– Comfortable development tools– Easy entry for beginners

Disadvantages– Security issues if every computer has full access

to the database. As an alternative, you could report on certain views only with restricted permissions.

– Every computer needs a PostgreSQL ODBC installed locally

MS-Excel Report

You can use Microsoft Excel as a reporting engine:

MS-Excel can access the ]project-open[ PostgreSQL database without problems

You can use all available MS-Excel tools to develop your reports, including pivot tables and Excel macros.

Advantages:– Comfortable development tools– Easy entry for beginners– Excel allows for complex post-processing of data

in separate sheets– Pivot tables allow for data-warehouse like reports

Disadvantages– Limitation of the number of rows– Security issues if every computer has full access

to the database. As an alternative, you could report on certain views only with restricted permissions.

– Every computer needs a PostgreSQL ODBC installed locally

im_ad_hoc_query $sql in /ds/shell

The ]project-open[/OpenACS /ds/shell page allows developers to execute TCL commands interactively

The “im_ad_hoc_query $sql” TCL command takes a SQL query and returns the results in “html”, “csv” or “plain” format

Advantages:– Well suitable for ad-hoc queries– Integrated into ]project-open[– Working queries can be easily deployed

(see later reporting options)

Disadvantages:– /ds/shell requires manual execution– im_ad_hoc_query does not support

aggregation and parameters.

im_ad_hoc_query $sql in a TCL page

Once your query is running in the /ds/shell environment, you can easily deploy the report as a ]project-open[ page.

The example below shows how to include the “im_ad_hoc_query” statement into a “.ADP” page.

You may then create a new menu item in the ]po[ reporting section (Admin -> Menus -> Create New Menu) in order to integrate the new report into the rest of the system.

Advantage:– Easy deployment of reports developed in

/ds/shell– Integrated with the rest of

the ]po[ environment

Disadvantages:– im_ad_hoc_query doesn’t support grouping

and sub-totals– No security. You will have to add security

checks manually.

Dynamic Report (Reporting -> New Report)

Administrators can create new reports in Reporting -> New Report

These “dynamic reports” behave just like im_ad_hoc_query.

Advantages:– Dynamic Reports automatically create a

menu item for you that links to the report and allow to manage permissions

– Dynamic Reports allow you to export the data in CSV format.

Disadvantages:– Like all im_ad_hoc_query based reports: No

grouping and sub-totals.

]po[ HTML Reporting Engine

The ]po[ reporting engine allows to create arbitrarily complex reports in a style similar to MS-Access and Crystal Reports.

Advantages:– Allows for group, sub-totals and

totals– Allows for parameters– Allows for different “Levels of

Detail”– Allows for “drill-down”. Clicking

on one of the [+] icons will show a more details view

Disadvantages:– Requires considerable TCL

programming skills

Indicators

Indicators are a special type of “report” that only returns a single numeric value.

]po[ takes care to regularly evaluate the SQL query and to store the values to create a timeline.

Indicators allow for permissions per indicator, so you can limit the visibility to certain user groups.

Advantages:– Allows to build management

dashboards, balanced scorecards etc.

Disadvantages:– Only suitable for certain reporting

purposes– It is very difficult to develop

indicators that are really relevant to a business.

Histogram Chart

A histogram chart is a simple “Portlet Component” that takes a SQL query as a parameter.

The query needs to return two columns only:– A name (here: ticket_type)– A number (here: count)

Advantages:– Easy to develop– Easy to integrate into existing pages

in ]po[

Disadvantages:– Limited reporting capabilities

Data-Warehouse Cube

]po[ includes an integrated Data-Warehouse as a commercial add-on package.

“Cubes” are capable of aggregating data along many “dimensions”.

Advantages:– One DW-cube can replace tens of

separate reports.– DW-cubes allow the user to “slice and

dice” data– DynFields (drop-down) can be used

as “dimensions” in a DW. For example, a “Business Sector” DynField per customer will automatically appear in the Finance cube.

– Seamlessly integrated into ]po[

Disadvantages:– Commercial package, license costs.– Only numeric values can be shown in

a DW

Pie Chart Based on DW-Cube

Pie charts can take a “sum” column of a data-warehouse cube as a data-source.

Advantages:– Easy creation of pie

charts

Disadvantages:– Data can only be

extracted from existing DW-cubes.

Nightly Evaluation of Long Running Reports

Reports frequently can take several seconds or even minutes to execute.

One way to provide users with a more agile reply is to pre-evaluate reports (at night) and to provide the results as a static HTML page to the user.

The following command will execute the “report.adp” and store the results as a static HTML page:– wget http://localhost/report -o ~/www/report.html

Advantages:– Fast response time– The static HTML page still provides drill-in

capabilities.

Disadvantages:– Extra effort to schedule the evaluation– Scheduling many reports may lead to overlaps

at night

Frank Bergmannfrank.bergmann@project-open.com

www.project-open.com