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(&quot;selfURL&quot;)) &amp; [&quot;::parameter&quot;]</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.

The front end code has the purpose to allow an user to create a DRILLDOWN formula function. For that the UI has to select a "drilldown-config", possibly ask the user for a "path" and then collect the parameter name-value pairs.

The UI is pluggable. This allows system integrators to define new drilldown profiles in the backend and to integrate them into the existing user interface. When a user links to Pentaho-Platform link-targets, the system must offer the existing parameter on the target object to the user.

2.2.1 Platform Parameter service

In most cases, the Pentaho Platform already knows the parameter of their objects. XActions, XAnalyzer and PRPT objects already contain parameter declarations. Since BI-Server 3.7, the Pentaho Reporting Plugin's parameter format is the standard format to describe parameter information in the platform.

The solution repository service knows the location of a "parameter-service-url" for each object that supports parameter. A external programm like PRD can use this URL to request parameter information from the Pentaho Platform.

The format of the parameter document is defined in the Parameter Definition XML-Schema.

A BI-Server plugin declares that it has a parameter service by declaring a content-type
operation with the id "PARAMETER":

<plugin title="Pentaho Reporting Plugin">
..
&nbsp;&nbsp;&nbsp; <content-types>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <content-type type="prpt" mime-type="application/zip">
..
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <operations>
..
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <operation>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <id>PARAMETER</id>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <command>content/reporting/?renderMode=PARAMETER&amp;solution={solution}&amp;path={path}&amp;name={name}</command>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </operation>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </operations>
..
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </content-type>
&nbsp;&nbsp;&nbsp; </content-types>

A call to this URL must result in a XML document that compiles to the parameter-definition schema given above.

The parameter XML should contain both the user defined parameter as well as any system level parameter. Parameter that are not meant to be seen by the user in any parameter entry UI should be marked as hidden by setting the appropriate parameter attribute.

2.2.2 PRD extension

The drill-linking UI is integrated into the core of the Pentaho Report Designer. Each drilldown-group can have a corresponding "drilldown-ui-plugin".

The UI takes all user input and produces a valid openformula formula expression with a single "DRILLDOWN" function in it. When the user hits OK on the "Edit Hyperlink" dialog, this drill-down function is added to as "href" style expression.

If the expression on the "href" style cannot be interpreted as a DRILLDOWN function, the report designer falls back to the legacy editor and allows users to edit the formula function in the Formula-editor.

At the moment, there are three UI plugins defined:

(1) Self-Linking

A special linking mode that takes all declared parameter and creates a URL that calls the same report with possibly changed parameter values. This linking mode facilitates self-service reporting solutions. An example would be a report where users alter the sorting of columns or browse through a paginated result set.

(2) Generic URL linking

Allows to link to any url. This mode allows to construct links to external services that do not provide parameter information. Generic-URL linking assists in the error-prone process of computing parameter sets for URLs.

(3) Pentaho Content Linking

This Drilldown-UI-Plugin connects the drill-down function to the Pentaho BI-Server. It assumes that all linkable content has a parameter-service that returns proper parameter description documents.

A typical edit session

When editing the user either enters a server address to create a remote link (a link that drills to a report stored on a different server) or leaves the server field empty to create a local link.

The user hits login to login to the server. If the server field is empty, the report's current authentication data is used. A report that was opened from a Pentaho repository already has valid login information, which will be used. Otherwise we prompt the user for the server adress and username and password.

The user browses the solution repository for a target report. If the user has not logged in, the login is done before the browsing starts. The browse-dialog only shows files for which a drill-down profile exists. The files are selected by their extension, which has to match a "extension" attribute from one of the drill-down profiles used.

When the user selected a file, the parameter for this file are queried. The file location is stored in the system parameters "solution", "path" and "name" ("action" for xaction and xanalyzer).

The Drill-Down-UI-Profile fills the available parameter into the parameter table. The user can then set values or add additional parameter that are not available in the parameter-description. The parameter table allows users to enter formula fragments or static values.

The Pentaho-Drilldown-UI profile contains extra logic to handle a set of PRPT specific drilldown profile selections. The PRPT component can be executed with or without the parameter UI. In those cases, separate URLs need to be generated.

Each Pentaho profile can either be a local profile (linking to content on the same server) or remote content (linking to a different server). For this condition two separate drill-down profiles exist, one prefixed with "local-" and one prefixed with "remote-". The UI dynamically selects the appropriate one based on the selection of a "Include Server URL in Link" checkbox.

2.2.3 Architecture of the UI code

As PRD is a Swing application, the UI code is heavily dependent on Swing concepts. For each DrillDown-Group, the drilldown-ui-registry contains a corresponding entry. DrillDownUIProfile must have the same name as the drilldown-group for which it provides a UI.

The DrillDownUiProfile acts as configuration holder and template. It is a factory that produces DrillDownUi instances. DrillDownUiProfiles are immutable and encapsulate the metadata for the available DrillDownUI implementations.

A DrillDownUi is a stateful editor for the DrillDown-formula function. It shares a common DrillDownModel with all active DrillDownUI implementations. The DrillDownModel encapsulates the editor state. DrillDownUI editor should update the DrillDownModel as soon as possible.

After the Ui was instantiated, it will be immediately initialized via a call to the "init" method. The UI has to fully initialize itself from the given model. The DrillDownUi provides a Swing component to the editor dialog via the "getEditorPanel" method. At the end of the editor's life-cycle, the "deactivate" method is called. The XulDrillDownUi classes can be used to separate the UI layout definition from the actual UI-handling and model code.

3. Implementing additional DrillDown-Profiles

Backend:

Create a new entry in the drilldown-profiles.xml.
Add the metadata and the resource-bundle information
Add a formula or pattern (depending on the customizer used) that creates the URL for you.

Frontend: Implementing a Pentaho Service:

Implement a parameter service for your content generator plugin in the server and register it as content-type operation "PARAMETER".
Add the "extension" property to the backend definition and ensure that you define both a "local" and a "remote" profile for your service.

Frontend: Implement a 3rd Party Parameter Service

Implement a parameter service for your content so that your UI can query parameter information. Alternatively you can hardcode the available parameter into your UI or you can reuse the parameter-xml code from the pentaho-drilldown-ui to read a xml file from your server.

Implement a UI plugin. I would recommend to use the "Generic URL" UI-plugin as starting point. With this code as basis you only have to implement the browse and code that populates the parameter-table.

Browser not supported