0. Definitions
- Source-report: The report or document that contains the link.
- Target-report: the report or document we link to.
- Link-Text: The result string that is used as link target in the output. Either a relative path, a full URL or some JavaScript.
- Report: A document. This is a reporting implementation so I take the freedom to call everything a report.
1. Design Principles
Parameter values can either be static, field-references or can be calculated values.
When linking to existing reports, in many cases the existing parameter set on the target report will not match directly with the source report's data set.
When passing data from specialized reports to more generic reports, we usually have to agument the report dataset with static values, like the department or region, that are not available as columns in the source report.
Likewise, in many cases we have to transform values for the target report: E.G: Target report expects Sales threashold parameter as percentage, we only have dollar.
The Drill-Linking system must allow third-party integrators to plug in their own link targets with full parametrization support.
This rule enforces a clean and extensible API. Reports often link back to source systems (example: Sales report contains links to the article on the web-shop).
The system must not contain hardcoded values or Pentaho-platform specific assumptions. Such assumptions would make it hard, if
not impossible for third-party vendors to integrate their own systems into the Drill-Linking schema. And it is bad design.
The generation process must be independend from designtime data
Rationale: At runtime, a source report does not necessarily have access to the target report. A burst run that runs in a DMZ but contains links to internal web-sites is not allowed to query the target server for parameter information at runtime. In addition to that, the user that runs the report may not have permission to access the report he links to. And last but not least: This makes the runtime process independent of external sources and thus simplifies the process.
The link-definition must be a well-defined long-term storage format that is resilent to changes in the backend
The data format used for storing the link information must be a open format and must ensure that parsing and writing is type-safe and stores unly the minimal information set that is needed to get the job done. Volatile information that can be derived at runtime (base-url for local linking reports) must not be stored.
Rationale: A sane data format that is implementation independent ensures that changes to the backend do not cause changes to existing reports.
Factoring out the base-url makes sure that reports can be moved to other servers without referencing obsolete URLs. This is critical where a solution of interlinking reports gets moved from testing to production servers. If the reports contain the local base-url as hardcoded string, these reports must be manually ported and tested - a process that is against the idea of testing and production environments.
Data types must be well-defined to ensure that reports transfered between locales or different versions of the reporting tools can be parsed safely. The system must have explicit quoting rules for strings, and well-defined locale-independent formats for numbers, dates and arrays (for multi-selection parameter).
The system must integrate into existing reports and systems.
Rationale: Creating a complex system that creates ambugities between existing URL definitions and new definitions is bad for usability. Users would have a hard time guessing which definition is now used.
Likewise, implementing a own linking system (maybe on AJAX or other technology) will cause trouble for third party implementors. The system must be able to produce standard URLs that can be used in all output targets (HTML, PDF, Excel). It can optionally offer different generator types, where applicable.
The desired output of the drill-down system is a String, possibly containing a URL or JavaScript. All output targets accept URL strings (either relative paths or fully qualified URLs). HTML also accepts JavaScript "links", also given as String.
The drill-down system must be a independent module.
Rationale: A link is a additional property on a report element and does not influence the layout or any core behaviour. The code must be written as extension module with a clear interface on how it interacts with the other parts of the system. This is a safeguard against sloopy coding.
The drill-down process must take its environment into account and must allow administrators to configure the drill-link output.
Rationale: A linked report that runs in the Pentaho-Platform's dashboard required special JavaScript to influence the output of other dashboard components. Therefore we need at least two modes: A URL generation mode used in PRD preview and in Mantle's tabbed UI and a dashboard mode when a report is embedded in a dashboard.
Implementation Note: At the end we ended up with three modes. Plain URL for PRD-preview, Open-in-new-tab for Mantle and a dashboard linking mode.
2. Architectural Overview
The drill-linking code is split into two parts, a backend module in the Pentaho Reporting engine and front end (designtime) code in the Pentaho Report Designer and the BI-Server.
2.1 Backend Architecture
The backend library is responsible for storing the link-definition data and link parameter information. It is also used for generating a drill-link URL at runtime. The backend implemets this as a OpenFormula function using the LibFormula library of Pentaho Reporting. This approach solves the storage and typing problem (OpenFormula is well-defined). At the same time it also solves the first requirement of supporting static as well as dynamic parameter values.
When executed, the formula function looks up a "Drilldown-profile" that contains the information needed to convert the various parameter into the desired URL or other drill-down output.
2.1.1 DRILLDOWN formula function
The DRILLDOWN function is a standard OpenFormula function that produces a link text.
It is defined as:
DRILLDOWN ([string profilename]; [string path]; {parameter-array here])
Path and profile name must be strings. The front end code requires that these strings are static strings and not computed at runtime.
The parameter array is one dimensional list of two-element arrays. These two element arrays, called parameter-entry, contain the parameter name in the first value and the parameter value in the second value. The front end code requires that the parameter name must is a static text. The parameter value is a open formula expression.
Examples for parameter value expressions
A static text:
"static-text"
A number:
10.10
A Date:
DATEVALUE("2010-12-24T23:59:59.0000+0800")
A field reference:
[field]
A calculation:
IF((10 * [field]) > 200; [field]; 200)
Like all URL/Link computations, the drill-down function is stored as "style-expression" on the report's HTML::URL style entry. When the reporting engine runs the report, this formula function is evaluated as part the normal report processing.
When the OpenFormula system inside the reporting engine evaluates the DrillDown function, all parameter expressions are evaluated. The parameter are transformed into a Map of values, keyed by the parameter name. The function then looks up the "drill-down-profile" by the name given in the first function parameter. The profile then computes the URL using the parameter map and the path information. The resulting link-text is then included in the report output.
Implementation Detail
A formula with computed profile, path name or parameter-names will run in the backend, but cannot be edited by the Drill-Down UI in PRD. It is possible to edit such a drill-down function in a "legacy-mode-editor".
2.1.2 DrillDown profiles
A drill-down profile encapsulates the calculation rules that transform the map of parameter names and values and the optional "path" component into a link-text.
A DrillDown profile is defined as a entry in a "drilldown-profiles.xml" file. It provides all metadata and initialization for a "LinkCustomizer" to work. The LinkCustomizer is a Java-implementation that produces the link-text.
<drilldown-profiles xmlns="http://reporting.pentaho.org/namespaces/engine/classic/drilldown-profile/1.0">
<group name="self">
<drilldown-profile name="self"
class="org.pentaho.reporting.engine.classic.extensions.drilldown.FormulaLinkCustomizer"
bundle-name="org.pentaho.reporting.engine.classic.extensions.drilldown.drilldown-profile"
expert="false" hidden="false" deprecated="false" preferred="false">
<attribute name="formula">URLPARAMETERSEPARATOR(ENV("selfURL")) & ["::parameter"]</attribute>
</drilldown-profile>
</group>
</drilldown-profiles>
Each profile has a drill-down group to which it belongs. The front-end uses the group as anchor point for its user-interface definition. A single user interface can possibly handle several drill-down profiles at the same time.
Element: drilldown-profile
Attribute: name
A drilldown-profile has a unique name. If the engine finds multiple definitions for the same name during the boot process, later definition replace previously defined drill-down profiles. This allows users to override built-in definitions.
Attribute: class
The implementation of the drilldown profile. The reporting engine ships with two implementations. The formula link customizer executes a open-formula expression to produce the link-text, the pattern link customizer use a simple message-format pattern as way to create a link-text.
Attribute: bundle-name
A reference to a resource bundle that contains translations for the metadata, like the display name in the UI and sorting and tooltip information. The information in the bundle is currently not used, but the bundle must exist.
Attribute: expert
Defines whether this drilldown-profile is a expert module. Expert options should only be shown to power users.
Attribute: hidden
Defines whether this drilldown profile is hidden and should not be shown in a UI.
Attribute: deprecated
Defines whether the use of this drilldown profile is deprecated. Make sure you define
a deprecation warning in the metadata resource bundle to show to the user.
Attribute: preferred
Defines whether the use of this drilldown profile is preferred. Preferred options are very likely to be a default choice for the user and should be highlighted in a UI.
Element: attribute
Attributes can be used to configure a drilldown-profile implementation. All attribute values are strings. It is up to the implementation to parse these strings to make sense out of them.
The attribute value is given as CDATA content in the element.
The attributes are also available to the UI implementation. The Pentaho specific drilldown profiles store the known file extensions here to map the user's selected content file to the right drilldown profile.
Attribute: name
The attribute name. Must be unique within the same drill-down profile element.
Known Link-Customizer:
"org.pentaho.reporting.engine.classic.extensions.drilldown.PatternLinkCustomizer"
Supported attributes:
"pattern"
The message format pattern.
The following variables are available:
{0}
The content of the "path" parameter of the "drill-down" formula function
{1}
The parameters as single URL string, already URL encoded and separated by "&" as required by the URL standard.
"org.pentaho.reporting.engine.classic.extensions.drilldown.FormulaLinkCustomizer"
Supported attributes:
"formula"
Defines the formula that is executed to calculate the link text. The formula customizer can access all current fields on the report as well as the following
additional fields:
::path
The content of the "path" parameter of the "drill-down" formula function
::parameter
The parameters as single URL string, already URL encoded and separated by "&" as
required by the URL standard.
::config
The name of the drilldown profile.
::TabName
The tab name when opened in Mantle
::TabActive
Whether URLs open in a new tab or in a new window.
Handling complex parameter dependencies in the formula link customizer
When a calculation has a large number of conditions, the formulas tend to become complex and not easy to write. For these cases I do recommend to implement special purpose FormulaFunctions to encapsulate these rules.
Example: It is not always a good idea to Open a report in a Mantle-Tab. There are a couple of conditions that governs that:
- Mantle-Tab only works with HTML exports, but not if the HTML export creates a ZIP file.
- It only works outside of the dashboard mode
- We cannot create a tab if the user prohibits it
Instead of writing multiple nested IF-then-else functions, all that logic now sits in a OPENINMANTLETAB formula function that can be referenced inside the drilldown-profile. That code is maintainable while at the same time preserves the extensibility of the formula solution.
2.2 Frontend Architecture
The frontend code consists of a extension module for the Pentaho Report Designer
and a extension to the Pentaho BI-Server that generates Parameter information for
the known content types.