Projects:Reporting

Contents 1 Introduction 2 Main requirements 2.1 Business Documents 2.2 Transactional Reporting 2.3 Business Intelligence 3 Report Management & Visualization in the Openbravo UI 3.1 Report Manager 3.2 Report Editor UI 4 Report Model

Introduction

This document contains the design for a reporting solution for Openbravo.

We need to distinguish 3 classes of reports:

1) Pixel perfect documents Example: Invoice, Sales Order, etc. The objective here is to produce a professional looking document in PDF format and to enable end users to easily personalize it.

The approach here is:

• To propose a new technology -different from Jasper Reports- to achieve the "ease of use for end users" objective. Candidates here are:
• Do another pass to clean up the existing printed documents
• Encourage the community (and of course ourselves) to publish collection of report templates: sales order with a light background, sales order with a dark background, etc.

2) Transactional listings Example: sales order pending invoicing etc. Just for the sake of clarity, this does not include the dimensional reports.

The approach here is:

• Query List widgets are great for these type of reports
• Do another pass to clean up current reports of this type:
  • Discontinue all the ones that are too specific and not really neeeded (eg. Discount Invoice Report), by moving them to a compatibility module or at least by hiding from the menu.
  • When appropiate, replace them with Query List Widgets.
• Add new ones missing using Query List Widgets (prioritizing based on our target market -Retail-)

3) Business Intelligence Example: dimensional reports, cube analytics, charting. The product should have embedded business intelligence capabilities

The approach here is:

• Multi-dimensional reports: current multidimensionl reports are very complex and should be gradually discontinued. Options to replace them are SmarClient Cubes (it should be a professional module), Smartclient grid grouping capabilities (although it has some limitations) or other pivot-table opensource component.
• Charting: the content of a Query List Widget should have a charting representation that can be added to a Workspace.
• Develop a Retail Analysis Pack and generic Financial Analysis Pack, including a number of specific Query List Widgets for each purpose distributed as a commercial module.
• Integration with third party BI solutions such as Zoho, Pentaho, JasperServer, Vanilla, etc.
  • Zoho as the low cost solution, packaged as ready to use solution.
  • For the others, guidelines for integration at the beginning, later packaged solutions through collaborative development projects.

Main requirements

Business Documents

Transactional Reporting

Must have:

• End users must be able to create and edit reports, the user interface should be as intuitive as possible.
• End users should be able to flexibly select:
  • the main types to report on
  • be able to define filters
  • group by fields/columns
  • summary fields/columns with a function (sum/avg etc.)
  • displayed fields/columns.
  • The available fields/columns should be presented in an easy-to-use manner.
• Filtering should allow some form and/or expressions, the user should be able to filter on different types of fields and the fields type should be used to correctly present filter options and operators (date/numeric filters should allow different filter operators than string fields for example).
• Filter values should be changeable at runtime.
• Different types of reports should be supported: grid, summary and matrix
• The user should be able to select what to show in a report: the detail (grid), summary and/or chart.
• A report can be editable by one or more users and can be visible to one or more other users. Reports should be accessible through quick launch and should open in a main tab in Openbravo.
• A report must be exportable as csv.

Should have:

• A report should allow showing a chart. Different types of charts should be supported: vertical/horizontal bar, line, pie, funnel, radar (these are the most common ones)
• It should be possible to show a report in a workspace widget.
• A report should be exportable as PDF.

Nice-to-have:

• Should be possible to schedule report generation, the generated report is send by email (as pdf).
• Reports should be accessible through a REST-like api, so that the report data can be imported into excel.
• As a side effect of the ability to show a report in a work space widget is that it should be possible to have multiple workspaces per user (volume of widgets will grow quite a bit).

Business Intelligence

Report Management & Visualization in the Openbravo UI

This section discusses how we can visualize the reporting functionality within the Openbravo UI. The following should be possible:

• the user should be able to open a report manager which shows the current accessible and editable reports
• the report manager can be opened through quick launch, the menu or possibly through a navigation bar button
• from the report manager a report editor can be opened, to create a new report or edit an existing report
• reports should be available through quick launch

Report Manager

The report manager will show the current reports (in a grid) available to a user. From here the user can create new reports, delete his own reports and view reports created by others.

Reports are opened in a separate tab, the report editor is shown in a separate tab.

Report Editor UI

The report editor should support the following sections/actions:

1. Define the main report data: name, type of report
2. Define the filtering
3. Select the group by columns
4. Select the summary columns
5. Select the display columns
6. Setting chart options
7. Setting report access for viewing and editing
8. Report/scheduling options

These sections can be visualized in one page or in a wizard like approach.

Report Model

A report is stored in its own table with child tables. The following tables are envisioned to support the reporting function:

• Report: the main table with fields:
  • name
  • Type/Table
  • Owner
  • Filter definition as a JSON string (to support maximum flexibility)
  • Group by columns (maximum 3) with sorting
  • Report chart definition
• ReportColumns: the columns used in the report, both the summary as well as the displayed columns
• ReportAccess: define which roles have access to this report and if they can edit/delete the report.