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.
- 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.
- 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.
- 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).
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
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:
- Define the main report data: name, type of report
- Define the filtering
- Select the group by columns
- Select the summary columns
- Select the display columns
- Setting chart options
- Setting report access for viewing and editing
- Report/scheduling options
These sections can be visualized in one page or in a wizard like approach.
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:
- 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.