Advanced topics (archive)

Archived. Content merged into "Pentaho Analyzer".

Use these topics to extend your Analyzer knowledge beyond basic setup and use.

In this article

• Defining hyperlinks

• Add query parameters to Analyzer reports

• Applying conditional formatting to measures

• Use calculated measures in Analyzer reports

• Export an Analyzer report through a URL

Defining hyperlinks

Manage the amount of information displayed in a report by hyperlinking from one report to other related reports, charts, dashboards, and URLs. For example, you can present basic information in an easy-to-comprehend report with hyperlinks to reports that contain details.

For charts, hyperlinks take precedence over the drill-down chart feature. For example, when readers click a bar in a chart, it displays data related to the hyperlink you define, not the drill-down chart.

For reports, you can define a hyperlink on any row label or column header. When you define a hyperlink, the link is applied to all members within the row or column. In this source report, hyperlinks have been defined for the Positions row level and the Region column level. Notice how each of the row and column members have a blue underlined hyperlink.

Source report

When defining hyperlinks to a destination report that has parameters, you can map the row and column levels in the source report to parameters in the destination report. With this function, you can constrain the hyperlink result to display only data for the mapped parameters. If you do not constrain the results, then no filter applies and all the data appears.

If you renamed a level in the report, Analyzer continues to display the level caption or name in the header.

For example, you can create a hyperlink in the source report for all the members in the Position row labels, and constrain the displayed data to only that related to each position and its department. To do this, you map the Department and Positions row levels in the source report to the Business Unit and Job Title parameters in this destination report.

Target report

When the reader clicks on the Administrative Assistant position within the Finance department in the source report, it looks like the example below:

Administrative report results

Each parameter added to the mapping further constrains the data. You can map any row levels that appear to the left, and column levels that appear above the member data.

If you do not constrain the data with parameters, readers see data for all Administrative Assistant positions in all departments.

Create hyperlinks to a report in the Pentaho Repository

1. Create an Analyzer report, or open an existing report.

2. Right-click a row or column level, then select Hyperlink.

   The Link On dialog box appears.

3. Select Enable Link.

   To disable linking, clear the Enable Link check box.

4. In the Link To drop-down list, select Pentaho Repository File.

5. Select Browse, locate a report, chart, or dashboard in the Pentaho Repository, then select Open.

   • If the destination report has parameters, they appear in the Destination Parameter list. Map row and column levels to parameters by selecting the parameter check box. Then enter the row or column level names in curly brackets ({}).

   • If the destination report does not have parameters, the Destination Parameter list does not appear.

6. In Open in, select New Tab, New Window, or Current Window.

7. In Tool Tip, enter the text to show on hover, then select OK.

8. Test the hyperlinks, then save the report.

Create hyperlinks to URLs

1. Create an Analyzer report, or open an existing report.

2. Right-click a row or column level, then select Hyperlink.

   The Link On dialog box appears.

3. Select Enable Link.

   To disable linking, clear the Enable Link check box.

4. In the Link To drop-down list, select URL.

5. In URL, enter the full address to open.

   Example: http://www.example.com

6. In Open in, select New Tab, New Window, or Current Window.

7. In Tool Tip, enter the text to show on hover, then select OK.

8. Test the hyperlinks, then save the report.

Default hyperlinks

Default hyperlinks let you set links in the schema. You do not need to enable hyperlinks per report.

Once set in the schema, you cannot disable default hyperlinks in Analyzer. You can still edit them.

Create default hyperlinks

1. Open the Mondrian schema file where you want hyperlinks.

   Use Schema Workbench to edit schemas.

   

   Schema workbench
2. In the dimension, add an annotation named AnalyzerHyperlink.

3. Add the hyperlink as the CDATA value.

4. Save the schema file.

Dimension values are case-sensitive. Dynamic values must match the dimension name exactly.

Example: An annotation on the Customer dimension uses http://search.yahoo.com/search?p={Customer}. Using {customer} does not work.

Enable default hyperlinks

After you add the annotation, reimport the schema:

1. In the Pentaho User Console, select Manage Data Sources.

2. In Manage data sources, open More actions and options, then select Import Analysis.

   

   Manage data sources dialog box
3. Select Browse, select your schema file, then select Import.

The specified dimensions show hyperlinks in new and existing reports associated with the updated cube.

Analysis report showing default hyperlinks

Remove default hyperlinks

Delete the annotation from the Mondrian schema file.

Add query parameters to Analyzer reports

To perform this task, log on to the User Console and open the Analyzer report you created in Add filters to an Analyzer report.

You can parameterize a query in Analyzer. Your parameter is a filter in Analyzer. Whenever this Analyzer report is run, users have a selection of columns to filter by.

1. Drag and drop the dimension member you want to create a parameter for onto the Filters canvas.

2. Select the level you want to set as the default parameter value, then click the right arrow to move it to the list on the right.

3. Click the check box at the bottom of the window, then enter a name for the parameter in the Parameter Name field.

Parameter types

• Include

  • Specifies values to include using the member name.

  • Use the MDX unique name on secondary or lower hierarchy levels.

  • This improves performance.

  • You can specify multiple values:

    • Report URL: repeat the parameter name and value.

    • Dashboard and scheduler: separate values using |.

      • If a value contains |, wrap the value in double quotation marks.

• Exclude

  • Specifies values to exclude.

  • The same multi-value rules apply as for Include.

• Contains

  • Supports multiple values.

  • Use the same multi-value rules as for Include.

• Does not contain

  • Supports multiple values.

  • Use the same multi-value rules as for Include.

• Current, Next, and Previous

  • Cannot be parameterized.

• Next N Years and N Years Ago

  • Accept integer values.

• Before and After

  • Accept a single member name.

• Between

  • Generates two query parameters:

    • <parameter name>_START

    • <parameter name>_END

  • Each accepts a single member name.

• Greater Than and Less Than

  • Accept integer values.

1. Click OK.

Parameter example

After you have created a parameter, you can generate a URL to share with users. You can use the following sample URL by customizing it with your server's name, user, report name, and parameter:

http://localhost:8080/pentaho/api/repos/%3Ahome%3Aadmin%3AAnalyzerFiltered.xanalyzer/editor?FYear_START=2003&FYear_END=2005

Applying conditional formatting to measures

Conditional formatting in the Analyzer data grid changes how cells look. Cells can change color or show icons based on their values. Analyzer includes built-in formatting options for numeric measures.

Apply conditional formatting to measures

1. Right-click a measure in the grid.

2. Select Conditional Formatting.

3. Select a conditional formatting type.

Analyzer refreshes and applies the formatting you selected.

Conditional formatting types

• Color scale: Shades the cell background based on the value range.

• Data bar: Fills the cell background proportionally to the value range.

• Trend arrow: Shows an up or down arrow for positive or negative values.

Apply advanced conditional formatting with MDX expressions

Use an MDX expression when the built-in options are not specific enough.

1. Right-click a measure in the grid.

2. Select Column Name and Format.

3. In Format, select Expression.

   Analyzer adds a default MDX expression:

   Case
   When [Measures].CurrentMember > 0
   Then '|#,##0|arrow=up'
   When [Measures].CurrentMember < 0
   Then '|#,##0|arrow=down'
   Else '|#,##0'
   End

4. Update the expression as needed.

   If the expression is invalid, Analyzer shows an invalid report definition error.

5. Select OK.

Analyzer refreshes and applies the formatting you specified.

Conditional formatting expressions

MDX format strings can include rendering properties for Analyzer HTML pivot tables.

Use this general pattern:

|#,###|style=red

The leading pipe (|) tells Analyzer the format includes properties. The #,### part sets the numeric display format. The style=red part is a key=value pair.

Analyzer supports these properties:

• style: Changes the cell background color.

  • Values: red, yellow, green

• arrow: Shows a trend arrow.

  • Values: up, down, none

  • Use none when you want only one arrow direction.

• link: Creates a link that opens in a new window.

  • Value: a fully qualified URL in quotation marks

• image: Renders a custom image.

  • Value: an image file name with extension

  • Store images in: /pentaho-solutions/system/analyzer/resource/image/report/

Use calculated measures in Analyzer reports

In Pentaho Analyzer, calculated measures are user-defined measures. They use formulas, usually MDX, over base measures.

You can save a calculated measure to the data model from Analyzer. This makes it available to other users immediately.

Before you begin

To add or edit calculated measures in the data model, you need the Manage Data Sources permission. For details about roles and permissions, see Manage Users and Roles in the User Console.

Open the Create Calculated Measure dialog box

You can open the dialog from these locations:

• Layout panel: Click the down arrow next to a base measure. Then select User Defined Measure > Create Calculated Measure.

• Report panel: If the measure is in the report, right-click it. Then select User Defined Measure > Calculated Measure Properties.

• Available Fields panel: See Add a calculated measure to the data source.

Create a calculated measure in a report

To create a calculated measure in a report, create or open a report in Analyzer. Then add the base measure to the Layout panel.

To create a calculated measure directly in the data source, see Add a calculated measure to the data source.

1. Click the down arrow next to a base measure in the Layout panel. Then select User Defined Measure > Create Calculated Measure.

   The Create Calculated Measure dialog box appears.

   

   Select this check box to save your calculated measure to the data source.
2. In the Display Name field, enter a name for your calculated measure.

3. In the Format field, specify how you want results to display in the report.

   If you do not specify a format, Analyzer uses the format from the first base measure.

4. In the right panel, enter the formula for your calculated measure.

   You can type the MDX statement or drag measures into the formula editor.

5. Select Calculate subtotals using measure formula to use the calculated measure for subtotals.

6. (Optional) Select Apply to data source to save the calculated measure to the data model.

   When you save the report, the measure becomes available to other users of the data source.

7. Click OK to apply your calculated measure to your report. Click Cancel to close the dialog box without applying changes.

When you save your report, the calculated measure is saved with the report.

• If you have not saved the report yet, you can click Undo to remove the calculated measure, even if you applied it to the data source.

• You can use hidden fields to create calculated measures. Turn on Show Hidden Fields in the View menu for Available Fields. To view hidden measures, you need the Manage Data Sources permission. For details, see Hide and unhide fields.

Edit a calculated measure in a report

If you created a calculated measure but did not add it to the data source, you can edit it in the Layout panel or in the report.

1. Click the down arrow next to the calculated measure in the Layout panel. Then select User Defined Measure > Calculated Measure Properties.

   You can also open this dialog from the report. Double-click the calculated measure, or right-click it and select User Defined Measure > Calculated Measure Properties.

   The Properties dialog box appears.

   

   Select the calculated measure to display the drop-down menu.
2. View or edit the fields.

   Some fields only display if you have the Manage Data Sources permission.

Field	Description
Display Name	The name of the measure as it displays in the model.
Format	Choose how this measure should be formatted, such as currency, general number, or percentage. Select a format from the list or enter a custom format. The Format field shows only when the value is a number or a date. If you do not specify a format, Analyzer uses the format from the first base measure. For details, see Format field options.
Decimal Places	(Optional) Specify the number of decimal places for the results.
Type	The type of field, measure, such as calculated measure. This field is always read-only.
MDX Formula	In the right panel, enter the formula for your calculated measure. You can write the MDX statement, or you can use the list on the left to drag measures into the right panel. You can also use the symbol buttons below to help create your statement, or just use your keyboard to write the expression.
Calculate subtotals using measure formula	(Optional) Select this check box to use this calculated measure when adding up subtotals in your report. Clear this check box to not use this formula when calculating subtotals.
Apply to data source	(Optional) Select this check box to add the calculated measure to the data model. When you click OK to save this calculated measure, your calculated measure will also be saved to the data model. Once you save the report, the measure will now be available for other users to add to their reports.

1. Click OK to apply your calculated measure to your report.

   When you save your report, your calculated measure is saved with the report.

   Click Cancel to close the dialog box without applying your changes.

• If you have not saved the report yet, you can click Undo to remove the calculated measure, even if you applied it to the data source.

• You can use hidden fields to create calculated measures. Turn on Show Hidden Fields in the View menu for Available Fields. To view hidden measures, you need the Manage Data Sources permission. For details, see Hide and unhide fields.

Tips for using calculated measures

Keep these guidelines in mind when working with calculated measures:

• If you add a calculated measure to the data source, it is available to anyone using that data source in Analyzer.

• If you do not add the calculated measure to the data source, it only appears in the report where you created it.

• If you first created a measure for your report, you can later add it to the data source:

  • Layout panel: Click the down arrow next to the calculated measure. Then select Add to Data Source.

  • Report panel: Right-click the calculated measure. Then select Add to Data Source.

  • Properties dialog box: Select Apply to data source. (See Edit a calculated measure in a report.)

• If you added a calculated measure to the data source but have not saved the report, you can click Undo to remove it.

  When you click Undo, Analyzer removes actions in reverse order.

• If you add a calculated measure to the data source, you can view and edit its properties from Available Fields. See Working with Analyzer measures.

Export an Analyzer report through a URL

You can export an Analyzer report as a PDF, CSV, Microsoft Excel, or JSON file from a Pentaho repository through a URL. This ability is useful when you want to export reports from a different scheduler.

In the URL, include a path to the repository containing your report. The default output is PDF. To specify a different output type, use the format parameter and specify either CSV, EXCEL, or JSON. To use the URL from a command line, include a call to the curl command with your <Analyzer user> and <password> before calling the URL.

The following examples show how to call Analyzer from an iframe, link, or button event in a third-party web application to export a file:

• Default PDF export

  http://localhost:8080/pentaho/api/repos/<repository path to report with xanalyzer extension>/service/export

• CSV export

  http://localhost:8080/pentaho/api/repos/<repository path to report with xanalyzer extension>/service/export?format=CSV

• EXCEL export

  http://localhost:8080/pentaho/api/repos/<repository path to report with xanalyzer extension>/service/export?format=EXCEL

• JSON export

  http://localhost:8080/pentaho/api/repos/<repository path to report with xanalyzer extension>/service/export?format=JSON

  The dimensional structure of an exported JSON data table is similar to a CSV export. Subtotals are not included in a JSON data table.

  By default, you can export up to 10,000 rows. To adjust the default setting, use renderer.export.max.rows.json in analyzer.properties.

Setting report parameters in the URL

You can also set report parameters while exporting an Analyzer report as a PDF, CSV, or Microsoft Excel file from a Pentaho repository through a URL.

• To set a range, specify the starting point as <parameter name>_START and an ending point as <parameter name>_END. For example, if your report has a YEAR parameter, the following sample URL exports a report from 2004 to 2005 as a CSV file:

  http://localhost:8080/pentaho/api/repos/<repository path to report with xanalyzer extension>/service/export?YEAR_START=2004&YEAR_END=2005&format=CSV

• To set multiple values for a given parameter, repeat the parameter in the call. For example, if your report has a TERRITORY parameter, the following sample URL exports a report for both NA and EMEA as a PDF file:

  http://localhost:8080/pentaho/api/repos/<repository path to report with xanalyzer extension>/service/export?TERRITORY=NA&TERRITORY=EMEA

See also

See Administer Pentaho Data Integration and Analytics for instructions on customizing Analyzer by editing a configuration properties file.