Pentaho Analyzer
Use Analyzer to create visualizations and analysis reports from your data. Analyzer has an easy-to-use, web-based, drag-and-drop design environment which can be used by anyone who wants to dynamically explore data and drill down to discover previously hidden details.
Before you can use Analyzer, you must have access to a data source. Only system administrators can create data sources. The data source for Analyzer is based on the Mondrian multidimensional data model. The Mondrian data model enables you to choose which dimensions and measures you want to explore in your data.
In this topic
Get started
Display and work with data in Analyzer through panels. For example, you can view and work with Steel Wheels data in the Analyzer panels by performing the following steps:
From the User Console Home page, click the Browse Files button. In the Browsing pane, click to expand the Public folder, then click to expand the Steel Wheels folder.
In the center pane, double-click on European Sales. The Opened page appears with the Analyzer report. Click the Add More Fields and Rearrange Fields icons on the toolbar to expand the Available Fields and Layout panels.
Getting started with Analyzer
1
Opened view
Displays quick access buttons across the top to create and save new Analyzer reports, Interactive Reports, and Dashboards. Opened reports and files show as a series of tabs across the page.
2
Available Fields and Layout panels
Use the Available Fields and Layout panels to drag levels and measures into a report.Your report displays changes in the Report Canvas as you drag items onto the Layout panel.
Delete a level or measure from your report by dragging it from the Layout panel toward the lower right corner of the Report Canvas. As you drag it, a trash can icon appears in the lower right corner of the canvas.
3
Report Canvas
Shows a dynamic view of your report as you work to build it. The look of your report changes constantly as you work with Available Fields and Layout panels to refine it.The Report Canvas shows different fields based on the chart type selected.
4
Analyzer Toolbar and Filters
Use the Analyzer Toolbar functions to undo or redo actions, hide lists of fields, add or hide filters, disable the auto-refresh function, adjust settings, and change the view of your report.Use the Filters panel to display a list of filters applied to the active report, or edit or delete filters.
Creating Analyzer reports
Creating reports in Pentaho Analyzer lets you compare data quickly. You can then refine the report with formatting and visualizations.
In this topic
Create an Analyzer report
Pentaho Analyzer automatically fetches data in real time as you add and remove fields, so you may find it easier to build a report with the Auto Refresh feature turned off. Then you can design your report layout first, including calculations and filtering, without querying the database automatically after each change. Just click the auto refresh icon in the toolbar to toggle Auto Refresh on or off, or you can click the Refresh Report button at any time.
To create a new report, perform the following steps:
From User Console Home, click Create New, then Analysis Report.
Choose a data source for the report from the Select Data Source dialog box, then click OK.
From the Available Fields pane on the left, click and drag an object to the Rows or Columns area in the Layout panel.
The data row or column appears in the table workspace.
Note: You can remove an object from a row or column by dragging it from the Layout panel back to the Available Fields list.
In the list of fields, click and drag a measure to the Measures area in the Layout pane.
The measure appears as a column in the table workspace.
If you want to rename or reformat your columns, right-click a column and choose Column Name and Format from the menu.
The Edit Column window appears.
Note: You can also sort the data in your columns by clicking and choosing a sort-order from the drop-down menu.
Choose a format from the Format drop-down box, or choose a visualization from the drop-down menu. Click Refresh Report if you need to, then click OK.
Click Save As. Type a file name for your report and choose a location to save it in, then click OK.
The new Analyzer report is created and saved in a location of your choice.
View and edit Analyzer reports
When you open a report from the Analyzer, by default the report opens in view mode. Click the Edit (pencil) icon to edit the report.
If you do not see the Edit icon, you do not have edit rights. Contact your administrator.
Word wrap in Analyzer reports
By default, when you view or edit reports in Analyzer, longer content in the table cells is truncated with an ellipsis, which can make the information difficult to read. You can wrap words in a specific column or the entire table to improve readability as shown in the following.
Note: To prevent misinterpretation of numeric data, you cannot wrap a Measures column.
Use column-specific wrapping
Complete the following steps to wrap the contents of a single column in an Analyzer report:
Open or create an Analyzer report.
Click View As and select Table.
Right-click the column header that you want to wrap.
From the menu, click Word Wrap.
The content in the cells of the selected column is wrapped.
Use table wrapping
Complete the following steps to wrap the contents of a table in an Analyzer report:
Open or create an Analyzer report.
Click View As and select Table.
Click More Actions and options.
Note: If you are using Edit mode, click Actions.
From the menu, click Report Word Wrap.
The content in the table is wrapped.
Visualizations for Analyzer
After you have created an analysis report, you will most likely want to make the report more visually pleasing and meaningful for your audience. Analyzer has quite a few built-in visualizations that give you an effective means for presenting your data in a way that people can intuitively understand. We also give you many different ways to customize your visualizations.
Both the Layout and Property panels in the Analyzer workspace dynamically change based on the visualization that you currently have selected. The Layout panel shows you what data is needed for that particular visualization, and the Property panel lets you add or change options such as data labels, color patterns, or bullet styles.
The optional properties for each visualization type are listed in each section. You can use any or all of these properties for each visualization to make your report more intuitive for your audience. The Chart Options for Analyzer reports dialog box contains more ways to customize your reports.
Continuous scale axis
Line, area, and scatter charts provide the number and time dimensions on a continuous scale axis. The continuous axis is helpful for identifying trends that occur over a period of time, such as financial growth, real estate sales, or test scores.
For a level of a time hierarchy to be plotted on a continuous scale, the level must be able to provide start date times for its members. Use either method to provide the start date times:
Base the level directly on a date-related database column. Set the level's key column data type to Date, Timestamp, or Time.
Specify the AnalyzerDateFormat annotation.
Prior to Pentaho version 8.1, line and area visualizations used a discrete axis. Data points displayed evenly spaced across the axis. To revert to the discrete axis behavior and preserve chart compatibility with earlier versions, change the web client configuration file. See Install Pentaho Data Integration and Analytics.
Column and bar charts
Column and bar charts are among the most recognizable visualizations for data. Column charts display their data vertically. Bar charts display the same data horizontally.
Column and bar charts work best when you do not have a large number of variables to display. Drill down into your data by double-clicking a column or bar.
Data Labels
Use this to label the data features of your report with what is represented. You can also choose to display no data labels, or specify where you want them to appear.
All column and bar charts
Column or Line Data Labels
Use this to put data labels on your columns or lines, or both.
Column/Line combo charts
Trend Type
Shows a trend type of none or linear. If you choose linear, you can name your trend line and set the width of it.
Column charts
Line, area, and dot charts
Line and area chart visualizations show data on a graph by connecting plotted points with a line. Each point represents a single value in your data. You can drill down in all three chart types by double-clicking a data point.
A line chart shows just the line connecting the dots on the graph. It works well if you have a large amount of data to analyze.
An area chart shows an aggregate of your data. It helps you spot trends quickly by showing totals and how they are moving relative to each other.
A dot chart displays the distribution of a data set as plotted points on a graph.
Optional properties for line, area, and dot charts:
Data Labels
Use this property to label the data features of your report and specify where you want them to appear. You can also choose not to display data labels.
Line, area, and dot charts
Bullet Style
Choose the shape that you want to use for your bullets.
Line charts
Line Width
Specify the width lines on your report.
Line charts
Trend Type
Shows a trend type of none or linear. If you choose linear, you can name your trend line and set the width of it.
Line charts
Pie, donut, and sunburst charts
Pie charts are round representations of your data, cut into slices. Each slice represents a piece of data, and the size of the slice is proportionate to the data that it represents. Double-clicking on a slice lets you drill down into your data.
Pie charts are a great way to show numerical or financial data, in other words, what something is worth relative to the whole group.
Donut charts are a type of pie chart that have a hole in the center. It is used to display data in a circular format, where each segment represents a category. The size of each segment is proportional to the value it represents.
You can drill down into your data by double-clicking a data slice. The donut then rearranges itself to show the more detailed information.
Sunburst visualizations organize and display your data in a series of colorful rings. Starting with the center ring, each ring going outward represents more detailed information relating to the inner ring to which it is connected.
Double-click a data slice to drill down into your data. The sunburst then rearranges itself to show the more detailed information.
Sunburst visualizations are particularly well-suited for numerical analysis of hierarchical data.
Optional properties for pie, donut, and sunburst charts:
Data Labels
Use this property to label the data features of your report and specify where you want them to appear. You can also choose not to display data labels.
Pie, donut, and sunburst charts
Exploded Slice Radius
Use this property to increment the radius of an exploded slice.
Donut charts
Slice Inner Radius
Use this property to change the inner radius of each slice.
Donut charts
Order By
Use this property to sort your data on the report.
Sunburst charts
Empty Slices check box
Select to show Empty Slices as gaps in the report.
Sunburst charts
Radar chart
A radar chart is used to compare multiple categories across several variables. It uses a series of connected points to form a polygon.
You can drill down into the data by double-clicking any data point within the chart. Radar charts are implemented using Apache Echarts and do not support exporting to PDF.
Optional properties for radar charts:
Bullet Style
Use this property to choose the shape that you want to use for your bullets.
Radar charts
Line Width
Use this property to specify the width of lines connecting bullets on your report.
Radar charts
Radar Shape
Use this property to display the radar in circular or polygonal shape.
Radar charts
Show Areas
Use this property to adjust the transparency of the area under the radar chart lines.
Radar charts
Show Axis Tick Labels
Use this property to display axis ticks on the radar chart.
Radar charts
Data Labels
Use this property to label the data features of your report and specify where you want them to appear. You can also choose not to display data labels.
Radar charts
Boxplot chart
A boxplot chart displays the distribution of a data set based on its five-number summary of data points. The five values are:
Minimum
First quartile
Median
Third quartile
Maximum
The chart is drawn as a box with a vertical line down the middle. Horizontal lines, known as whiskers, attach to each side. Double-click the boxplot chart to drill down into your data.
Create a five-number summary
Perform the following steps to create a five-number summary of data points for a boxplot chart:
Create a new or open an existing Analyzer report. Select Boxplot from the chart dropdown.
Right-click any Measures field. Select User Defined Measure > Create Calculated Measure.
The Calculated Measure dialog box displays. See Creating a calculated measure in a report.
Create a calculated measure that returns the median value.
In the Create Calculated Measure formula panel, enter
Median(<Set>, <Numeric Expression>).Enter an appropriate name. Click OK.
You can return a set of descendants of a set member at a specified level, optionally including or excluding descendants in other levels. Use
Descendants(<Member>, <Level>).Create a calculated measure that returns the lower quartile value.
In the Create Calculated Measure formula panel, enter
FirstQ(<Set>, <Numeric Expression>).Enter an appropriate name. Click OK.
Create a calculated measure that returns the upper quartile value.
In the Create Calculated Measure formula panel, enter
ThirdQ(<Set>, <Numeric Expression>).Enter an appropriate name. Click OK.
Create a calculated measure that returns the minimum value.
In the Create Calculated Measure formula panel, enter
Min(<Set>, <Numeric Expression>).Enter an appropriate name. Click OK.
Create a calculated measure that returns the maximum value.
In the Create Calculated Measure formula panel, enter
Max(<Set>, <Numeric Expression>).Enter an appropriate name. Click OK.
Boxplot chart example
The boxplot chart example shown in this section was created using the following steps:
Choose File > New > Analysis Report.
The Select Data Source dialog box opens.
Select SteelWheelsSales. Click OK.
The new report displays.
Select Boxplot in the chart list.
Add the Territory market to the Category layout. Add the Years time to the Series layout.
In the Available fields list, right-click any field in Measures. Select Create Calculated Measure.
Enter Median as the Display Name. Calculate the median of the set by entering the following MDX query in the Create Calculated Measure formula panel. Click OK:
Median(Descendants([Product].CurrentMember,[Product].[Line]), [Measures].[Quantity])
Create Calculated Measure dialog box Create a Lower Quartile calculated measure set. Enter the following MDX query in the Create Calculated Measure formula panel. Click OK:
FirstQ(Descendants([Product].CurrentMember,[Product].[Line]), [Measures].[Quantity])Create an Upper Quartile calculated measure set. Enter the following MDX query in the Create Calculated Measure formula panel. Click OK:
ThirdQ(Descendants([Product].CurrentMember,[Product].[Line]), [Measures].[Quantity])Create a Minimum calculated measure set. Enter the following MDX query in the Create Calculated Measure formula panel. Click OK:
Min(Descendants([Product].CurrentMember,[Product].[Line]), [Measures].[Quantity])Create a Maximum calculated measure set. Enter the following MDX query in the Create Calculated Measure formula panel. Click OK:
Max(Descendants([Product].CurrentMember,[Product].[Line]), [Measures].[Quantity])Right-click each of the five measures you have created. Select Add to Report.
Your boxplot chart will display as shown in this section.
Scatter chart visualization
Scatter charts are related to line and area charts, but show the intersection of x and y values at each data point. These values appear on the scatter chart as a series of bubbles on a graph, based on the attributes you used when you built your report.
You can then use a measure to color-code the bubble. You can also use a measure to specify the size of the bubble. Double-clicking on any data point drills down into your data.
Scatter charts work well to show the categorical relationship between two sets of numerical measures, such as between a budgeted amount and the actual amount of money spent.
Optional properties for scatter chart visualizations:
Data Labels
Use this property to label the data features of your report and specify where you want them to appear. You can also choose not to display data labels.
Scatter chart visualization
Trend Type
Shows a trend type of none or linear. If you choose linear, you can name your trend line and set the width of it.
Scatter charts visualization
Heat grid visualization
Heat grids color-code your results so you can view complex business analysis in an easy-to-understand way. Drill down into your data by double-clicking any square on the grid.
Heat grids are especially well-suited for comparing categorical data using color.
Optional properties for heat grid visualizations:
Data Labels
Use this property to label the data features of your report and specify where you want them to appear. You can also choose not to display data labels.
Heat grid visualization
Pattern
Lets you choose from Gradient, 3 Step, or 5 Step patterns.
Heat grid visualization
Color
Choose from different mixes of colors for your report.
Heat grid visualization
Reverse Colors check box
Reverses the order of colors in the report.
Heat grid visualization
Bullet Style
Choose the shape that you want to use for your bullets.
Heat grid visualization
Geo map visualization
Geo maps show a geographic summary of your data using size and color. This visualization type plots a pin on a map, based on the location attribute you used.
You can add a measure to specify the size of the pinpoints. You can then use the properties panel to change the color of the pinpoints. Double-clicking on a pinpoint drills down into your data.
If your data model has geographic annotations, then the location information will be retrieved by the geoservice automatically. Geo maps are especially useful for retail or sales data.
You must have a license from Google if you choose to use Google Maps with any Pentaho software.
Pattern
Lets you choose from Gradient, 3 Step, or 5 Step patterns.
Geo map visualization
Color
Choose from different mixes of colors for your report.
Geo map visualization
Reverse Colors check box
Reverses the order of colors in the report.
Geo map visualization
Waterfall chart
A waterfall chart is a bar chart that displays the cumulative effect of sequentially introduced positive or negative values. It is used to understand how an initial value is affected by a series of intermediate values. Double-click a bar to drill down into your data.
Optional properties for waterfall charts:
Water-Direction
Use this property to change the direction of the waterfall to up or down.
Waterfall charts
Data Labels
Use this property to label the data features of your report and specify where you want them to appear. You can also choose not to display data labels.
Waterfall charts
Treemap visualization
A Treemap chart displays hierarchical data using nested rectangles. Each rectangle represents a group or cluster of data. The size of the rectangle is proportional to the value of the data it represents. Double-click a rectangle to drill down into your data.
Layout Mode
Use this to select the rectangle layout algorithm used by a treemap plot.
Treemap visualization
Data Labels
Use this property to label the data features of your report and specify where you want them to appear. You can also choose not to display data labels.
Treemap visualization
Funnel chart
A funnel chart represents the flow of data through a process. It visualizes how a measure breaks out across a series of steps. Double-click any part of the funnel to drill down into the data.
Funnel charts are implemented using Apache Echarts and do not support exporting to PDF.
Optional properties for funnel charts:
Data Labels
Use this property to label the data features of your report and specify where you want them to appear. You can also choose not to display data labels.
Funnel charts
Order
Use this property to sort data on the report.
Funnel charts
Gauge chart
A gauge chart displays the progress or status of a specific metric or goal. It typically consists of a circular scale with needles or markers that move to indicate the current value or position. Double-click any dial to drill down into the data.
Gauge charts are implemented using Apache Echarts and do not support exporting to PDF.
Optional properties for gauge charts:
Show Measure Color Bands
Use this property to divide the circular scale into discrete colored partitions.
Gauge charts
Pattern
If the Show Measure Color Bands property is selected, use this property to choose from Gradient, 3 Step, or 5 Step patterns.
Gauge charts
Color
If the Show Measure Color Bands property is selected, use this property to choose from different mixes of colors for your report.
Gauge charts
Reverse Colors
If the Show Measure Color Bands property is selected, use this property to reverse the order of colors in the report.
Gauge charts
Chart Options for Analyzer reports
The Chart Options dialog box is available if you have a visualization applied to your report. Click Chart Options in the Properties panel to bring up the chart options for that report. You can click Apply to update your report with your changes up and continue working with the Chart Options. After setting options for your chart, click OK. The report will refresh to reflect the options.
General tab
The General tab is where you can set background colors, fonts, or the domain limit for the maximum number of plot values.
Fill in the following options:
Background
Fill type defines the background color of the chart and the fill colors used.
Select None, Solid, or Gradient with the drop-down menu.
Labels
Use the drop-down menus to choose a font for your data labels, specify the size and type for the font, and change the font color with the color picker.
Domain Limit
Max number of plot values: Use this to set the maximum number of plot values used in your report.
Axis tab
The Axis tab is where you define how the x-axis and y-axis appear.
Fill in the following options:
Primary Axis
Auto Range check box: Calculates the Start and Stop value automatically. If you want to specify the values, uncheck the box and fill out the Start and Stop fields.- Start: Use to set the starting value for the axis.
Stop: Use to set the stopping value for the axis.
Scale: Increase the scale for the primary axis, or leave it set to a default of None.
Secondary Axis
Available for Scatter and Column-Line Combo charts only. Secondary Axis has the same options as Primary Axis.
Legend tab
Use the Legend tab to customize the legend.
Fill in the following options:
Show legend
Clear this box to remove the legend from your report.
Position
Use the drop-down to move the legend to the Top, Right, Bottom, or Left of your report.
Background color
Use this to change the background color of the legend. Click on the Background Color box to bring up the color picker.
Font
Use the drop-down menus to choose a font for your data labels, specify the size and type for the font, and change the font color of the legend.
Other tab
Use the Other tab to tailor the look of small charts (multi-charts). You can also control how Analyzer shows empty cells.
Fill in the following options:
Multi-Charts
Charts Per Row: Select from the drop-down menu to specify how many multi-charts on each row appear.
Scale: Use the drop-down menu to set the axis range for your multi-charts. You set the same range for all of the small charts or set them independently.
Size By Measure
Treat negative values as: Use the dropdown menu to choose either Smallest Value or Absolute.
Empty cell treatment (Line and area charts only): Controls how empty cells appear in your Line and Area charts. Choose from Show Gap, Connect with Dotted Line, or Treat as Zero.
Advanced tab
Use the Advanced tab to configure visualization views at the report level using configuration rules.
Configuration rules use JSON syntax.
Click the
icon to open the Pentaho Analyzer document.Copy the sample code into View configuration rules, then modify it.
Click Apply, then click OK.
Set default chart options
As an administrator, you can set default chart options that apply whenever a new chart is created. Defaults do not apply to existing charts.
You can reset a chart back to default settings by clicking Reset to default on the Other tab of the Chart Options dialog box.
Role permissions are as follows:
Set
X
Reset
X
X
Remove
X
To set a default chart option:
Open the chart you want to modify.
Select More actions and options, then click Chart Options.
Set the options you want, then select Set as default for all charts.
Click OK.
Display member properties
When a number in parenthesis appears next to a dimension in a list of Available fields, that dimension is associated with specific member properties. You can use those properties to constrain dimensions. If you want to review available fields, click Add more fields onto the report on the report toolbar.
Perform the following steps to filter by member properties:
Locate a dimension in the Available fields list that includes a number in parenthesis, for example, Customer (6), or Product (3).
Locate the corresponding dimension on your report. Right-click the row or column header for that dimension, then click Show Properties.
A menu displays member properties you can choose.
Select or clear the member property you want in the report, then click OK.
Your analyzer report is filtered by the choices you made.
Adding filters to an Analyzer report
In Pentaho Analyzer, filters are used to restrict or limit data in a report, so you can build the report to show only the information that you want to view. For example, a typical report shows sales by product line. By applying a date filter on Quarter, you can restrict the data so that only sales for the one quarter are shown. If you add a string filter for Europe, the report would display data for the European sales that quarter. If you add a string filter on another field to exclude a product line, the report would display European sales data in that quarter minus the excluded product.
Filter types
Use these filter types to refine your report data:
Add a filter
Use these steps to add a filter with Select from a list.
Log in to the User Console.
Open an existing Analyzer report.
If you are creating a new report, do this:
Select the data source you want.
Click OK.
Add report fields in the Layout panel.
Click the Add A Filter icon.
A workspace for filters appears near the top of the report.
Drag a field into the filter workspace from the Available Fields pane.
The Filter dialog box opens. Select from a list is selected.
In Currently, select Included or Excluded.
Filter dialog box with the Select from a list option selected. In Add Selected, select one or more values.
Click the Arrow to move selected values to the right pane.
Click the Double Arrow to move all values.
Filter dialog box with values selected. Click OK.
Save your report.
Click Undo or Reset to return to the previous version.
Date range filters
Date range filters show only data that matches a time period.
For example, you can filter between 2010 and 2013. You can also filter after 2010.
Log in to the User Console.
Open an existing analysis report that includes a time dimension.
Or, create a new report:
Click Create New > New Analysis.
Select the data source you want.
Click OK.
Add a time dimension to the report, such as year or quarter.
Click the Add A Filter icon.
Drag the time dimension to the Filters board.
You can also right-click the dimension and add it as a filter.
Choose one of these options:
Choose a commonly used time period: Select a time period, then click OK.
This filter is dynamic. It changes with the current date. Other date filters are static.
Filter on Quarters dialog box Select from a list: Select values, then click OK.
Use the single and double arrows to move one or all values.
Select from a list option Select a range: Click Select from date picker. Select a start and end date. Click Apply, then OK.
Select a range option displays the date picker
Once you select a date, Analyzer validates the range.
Analyzer searches for the nearest time period, up to ±30 time periods.
You cannot save the filter if Analyzer cannot find a match.
The Choose a commonly used time period and Between, After, Before filters are available only if time dimension levels are set up with the AnalyzerDateFormat annotation.
If you create a date range filter with the Between (and incl.) operator and parameterize it (see Add query parameters to Analyzer reports), you specify one parameter name, but Analyzer creates two parameters:
<YourParameterName>_START<YourParameterName>_END
Comparison filters on numeric levels
Comparison filters show only data that matches a numeric condition.
For example, you can create Credit Score > 600.
The operator list does not include Equals, Not Equals, and Is Not Empty. Use Included and Excluded for the same behavior.
Log in to the User Console.
Open your Analyzer report.
Click the Add A Filter icon.
Drag a field into the Filters workspace.
The Filter dialog box opens.
Select Greater / Less Than, Between, etc..
Filter dialog box Select a comparison operator, such as Greater Than, Less Than, Greater Than or Equals, Less Than or Equals, or Between.
Enter the value or range.
Click OK.
Save your report.
Click Undo or Reset to return to the previous version.
String filters on levels
String filters match (or exclude) specific text in a field.
Log in to the User Console.
Open your Analyzer report.
Click the Add A Filter icon.
Drag a field into the filters workspace.
The Filter dialog box opens.
Select Match a specific string.
Match a specific string Select a match type, such as Contains or Doesn’t Contain.
Enter the string value.
Optional: Click Add another value to refine the filter.
Click OK.
Save your report.
Click Undo or Reset to return to the previous version.
Filters on measure values
Measure filters apply conditions to measure data.
You specify one or more measures and one level field. The level field defines the values to filter.
If you remove the level field from the report, Analyzer removes the measure filter.
Restrictions
You can have only one measure filter per report.
You cannot use calculated fields.
Analyzer applies the measure filter after other filters.
Measure filter types
Greater/Less Than, Equal to, etc.
Top 10, etc.
Log in to the User Console.
Open your Analyzer report.
Click the Add A Filter icon.
Drag a field into the Filters workspace.
The Filter dialog box opens.
Select Greater/Less Than, Equal to, etc. and/or Top 10, etc..
If you select both types, Analyzer applies Greater/Less Than, Equal to, etc. first.
Measure Filter dialog box If you selected Greater/Less Than, Equal to, etc., do this:
In the measure field, select a measure, such as Quantity or Sales.
In the filter field, select an operator, such as Greater Than or Equals.
In the level field, enter the value.
Measure filter selection
If you selected Top 10, etc., do this:
Select Top or Bottom.
Enter the number of items.
In by [measure], select the measure to rank by.
Measure filter selection for Top 10 filter
Click OK.
Save your report.
Click Undo or Reset to return to the previous version.
Set default filters for new reports
Default filters apply automatically when users create new reports.
Default filters are not applied to existing reports.
Add the filters you want as defaults to a report.
Click More actions.
Select Default Filters > Set for New Reports.
Other options are Remove for New Reports and Reset to default.
In the alert dialog box, click OK.
New reports based on the modified report will use the default filters.
Filters you add in chart view display in the filters workspace. They are not saved as default filters.
Working with Analyzer fields
An Analyzer report is a collection of fields and filters. It renders those fields in a chosen report format.
Think of a report like a spreadsheet file. Unlike a spreadsheet, Analyzer queries live data when you open it. It also queries live data when you change it.
Reports are stored in a repository. You can open the same report from any computer.
When you create a report, start by selecting a data source. The data source controls which fields you can use. For example, an Orders data source exposes order-related fields.
Where data comes from
Analyzer uses OLAP and multidimensional query expressions (MDX). It retrieves data from relational databases (RDBMS).
Analyzer commonly queries a data warehouse. A warehouse consolidates data from multiple source systems. It optimizes that data for reporting and analysis.
Pentaho Data Integration (PDI) is often used to build warehouses. It also populates data models for reporting. PDI loads from databases, apps, spreadsheets, and public data.
Your administrator usually builds and maintains data models.
Fields in Analyzer
Examples include Sales Revenue, Profit Margin, Product Name, Region Name, and Fiscal Year. Fields define what your report shows.
Types of fields
Analyzer exposes three field types. They map to how data is stored and aggregated.
Levels
Text-based dimensions.
Example:
Product Name.Level values are often called members.
Time period fields
A specialized level type used in most reports.
Examples:
Fiscal Year,Order Month.
Measures
Numeric metrics.
Examples:
Sales Revenue,Profit Margin.
Fields are color-coded in the report and the Layout panel:
Levels and time periods use a yellow background.
Measures use a blue background.
Field hierarchies
Some level fields belong to a hierarchy. Hierarchies define drill paths.
Examples:
Product Line→Product NameYear→Quarter→Month→Week→Day
Hierarchies help in two ways.
They make drilling faster:
Right-click a level name, then select Also Show.
Right-click a value, then select a drill option.
Example: Keep Only 2007 And Show Quarters.
If multiple hierarchy levels are in the report, you can drill up or down.
Example: Drill up to Year or Drill Down to Month.
They also reduce filter value lists:
If you filter
Product Line = Snow Sports, thenProduct Namevalues narrow.
Fields from the same hierarchy must be on the same axis. Analyzer enforces this when you arrange fields.
View details about a field
You can inspect a field from the Layout panel or the report. You can also inspect it from the Available Fields list.
View the definition of a field (“Tell me about”)
Use Tell me about to see how a field was defined.
In the Layout panel or the report, right-click the field name.
Click Tell me about.
Review the details.
You typically see:
Display Name
The name shown in Available Fields and the report.
If you renamed it in the report, you also see the original name.
Type
Level, time, or measure.
Description
If provided in the data model.
MDX
The underlying MDX expression.
Member Properties
If the field has member properties (for example,
Customer(6)).
View and edit field properties (“Properties”)
Most users can view field properties. Only users with Manage Data Sources can edit some properties.
For permission setup, see Manage Users and Roles in the User Console.
In Available Fields, right-click a field.
Click Properties.
View or edit the available properties.
Common properties:
Display Name
Editable with Manage Data Sources.
Report-only renames do not change the list display name.
See Rename a field.
Aggregation (measures only)
Determines how values roll up.
Common options include
SUM,AVERAGE,COUNT, andCOUNT_DISTINCT.
Format
Controls number and date formatting.
See Format field options.
Description, Type, MDX
Read-only.
Member Properties
Read-only list of member properties, if present.
View a level with member properties in a report
If a dimension shows a number in parentheses, it has member properties. Example: Customer (6).
In Available Fields, locate a dimension with a number in parentheses.
In the report, right-click that dimension header.
Click Show Properties.
Select or clear properties, then click OK.
Editing measure properties
Changing measure properties updates the data source. It can affect all users of that source.
You need Manage Data Sources to change measure properties.
Work with the Available Fields list
You can organize, search, and add fields from Available Fields.
Change how fields are grouped and sorted
In Available Fields, click View.
Select a sort option:
By Category (default). Set by an administrator.
By Type. Measures first, then levels.
A to Z. Alphabetical, no grouping.
By Schema. Uses cube schema grouping.
Find a field
In Available Fields, type part of the field name in Find.
Add a field to a report
You can add fields using several methods:
Drag a field into the report.
Drag a field into a drop zone in the Layout panel.
Right-click a field, then click Add to Report.
Double-click a field.
Rename a field
Renaming affects only the current report. It does not change the data model.
In the report, right-click the field you want to rename.
Click Edit or Column Name and Format.
In Name, enter the new name.
Optional: set Plural Name used within this report.
Click OK.
To remove a rename, open the edit dialog. Clear the name fields.
Move fields in a report
Sometimes you need to rearrange fields.
In a table report, drag the field to a new location.
In chart mode:
Open the Layout panel.
Drag fields within and between drop zones.
Move measures only within measure zones. Move levels only within level zones.
Remove fields in a report
Do one of the following:
Drag the field name to the trash area.
Right-click the field name, then click Remove from Report.
Hide and unhide fields
Hidden fields do not appear in Available Fields. They are not available for report design.
You need Manage Data Sources to hide fields. For permission setup, see Manage Users and Roles in the User Console.
The field count at the top of Available Fields depends on permissions:
With Manage Data Sources, you see all fields.
This includes hidden fields and model-added measures.
Without it, you see only visible fields.
Hide a field
In Available Fields, hover over a field.
Click the down arrow.
Click Set Hidden.
If the field is used in reports, it is removed from those reports. Use Undo to restore it before saving. After saving, you must unhide the field to use it again.
Hidden fields can also affect drill-through. See Set Analyzer report options.
View hidden fields
In Available Fields, click the down arrow next to View.
Click Show Hidden Fields.
Hidden fields appear in gray.
To hide them again, clear Show Hidden Fields.
Hidden measures can be used in calculated measure formulas. You still need Manage Data Sources to view them.
Unhide a field
Enable Show Hidden Fields.
Hover over the gray field, then click the down arrow.
Clear Set Hidden.
Format and units of measure
Editing units of measure in a report
Use units when you work with large numbers.
In the report, right-click a measure header.
Click Edit.
In Format, select Currency or General Number.
In Units, select the unit of measure.
Click OK.
Format field options
The Format property supports numeric and date formats. It applies to numbers and calendar dates.
For more details on format strings, see MDX format definitions.
Common numeric formats:
0
12345
0.00
12345.09
#,##0
12, 345
#,###.00
12, 345.09
-#,###.00
-12, 345.09
(#,###.00)
(12, 345.09)
$ #,##0
$ 12, 345
$ #,##0.00
$ 12, 345.09
$ -#,##0.00
$ -12, 345.09
$ (#,##0.00)
$ (12, 345.09)
$ #,##0.00;(#,##0.00)
$ 12, 345.09
0 %
1234509 %
0.00 %
1234509.00 %
#E+#
1E+4
0.00E+00
1.23E+04
##0.0E+0
1.2E+4
Common date and time formats:
M/d
4/1
M/d/yy
4/1/16
MM/dd/yy
04/01/16
d-MMM
1-Apr
d-MMM-yy
1-Apr-16
MMM-yy
Apr-16
MMMMM-yy
April-16
MMMMM d, yyyy
April 1, 2016
M/d/yy h:mm AM/PM
4/1/2016 8:09 PM
M/d/yy h:mm
4/1/2016 20:09
M/d/yyyy
4/1/2016
d-MMM-yyyy
1-Apr-2016
h:mm
20:09
h:mm AM/PM
8:09 PM
h:mm:ss
20:09:06
h:mm:ss AM/PM
8:09:06 PM
\[h]:mm:ss
\[20]:09:06
Managing fields in large reports
Large reports can be truncated. This is most common with fields that have many values.
Truncated table reports behave differently:
The status bar shows displayed rows and columns versus total.
Analyzer cuts cells until the total is at most 2000.
Your administrator can raise this limit.
Rows are cut first, down to at least 10 rows.
Columns are cut after rows.
Subtotals and grand totals do not display.
A message at the end of the report indicates truncation.
Charts also have plot point limits. The limit depends on the chart type. You can change it in Chart Options.
Troubleshooting: Your report does not display data
In some cases, a report returns no data. These are common causes and fixes.
You added or modified a filter.
The report returned blank.
The filter is too restrictive.
The filter includes year 1997, but you have revenue only for 2005.
Loosen filters, or show rows/columns where the number cell is blank.
You added a new number field.
The report returned blank.
The field has no values.
You added Quota, but no quota data exists.
Ask your administrator to load data or hide the field.
You added a new text field. You have no number fields.
The report returned blank.
Analyzer sometimes needs a measure to correlate level fields.
You added Account Name and Order Status.
Add a number field.
Working with Analyzer measures
In Analyzer, you can work with measures in several ways, such as renaming a measure, creating a calculated measure, and editing a calculated measure.
When you update a measure or add a calculated measure, you are changing the data model from Analyzer. When you save, the change becomes part of the data source. Other users will see the change immediately.
We call these changes inline modeling.
Before you start
You need the Manage Data Sources operation permission in Users and Roles. See Use Pentaho Security.
Inline modeling updates the data source. It can impact every report using that data source.
After you save a report that includes inline modeling changes, Undo and Redo are unavailable. Finish any “undo” or “redo” actions before you save.
Measure properties in Analyzer
You can view measure properties in Analyzer. With Manage Data Sources, you can also edit some properties.
Common updates include renaming a measure (for example, QTY to Quantity), changing aggregation, or adjusting the formula for a calculated measure.
What you can edit
Base measures
You can edit several properties, such as Display Name, Aggregation, and Format.
Calculated measures created in Analyzer
You can edit several properties, including the formula you defined.
Calculated measures created in PDI and published to Analyzer
You can only edit Display Name.
To change other properties, edit the measure in PDI.
Use hidden measures in formulas
You can use hidden measures when you create or update formulas. Turn on Show Hidden Fields from the View menu in the Available Fields list.
To see hidden measures, you need Manage Data Sources. See Hide and unhide fields.
View or update base measures
These steps apply to:
Measures
Calculated measures created in PDI and published to Analyzer
The Properties dialog box varies slightly between measures and calculated measures.
In the Available Fields list, select the measure, then select the down arrow next to it.
A shortcut menu appears.
Select Properties.
The Properties dialog box appears.
With Manage Data Sources, you can edit several fields. Otherwise, fields are read-only.
Measure properties View or edit these fields:
FieldDescriptionDisplay Name
The name of the measure as it displays in the model.
Aggregation
The aggregation type used to combine the data. Common options include SUM, AVERAGE, COUNT, COUNT_DISTINCT, MINIMUM, and MAXIMUM. This field only displays for measures, not calculated measures.
Format
Select a system-defined format, or enter a custom format. See Format field options.
Description
The description of the measure. This field is read-only.
Type
The field type (for example, measure). This field is read-only.
MDX
The MDX statement for the measure. This field is read-only.
Select OK to save changes, or Cancel to close without saving.
View or update inline calculated measures
These steps apply to calculated measures created in Analyzer using inline modeling. Inline calculated measures show an fx icon in the Available Fields list.
In the Available Fields list, select the calculated measure, then select the down arrow next to it.
A shortcut menu appears.
Measures shortcut menu Select Properties.
The Properties dialog box appears.
Editing a calculated measure View or edit these fields:
Some fields only display if you have Manage Data Sources.
FieldDescriptionName
The name of the measure as it displays in the model.
Format
Select a format, or enter a custom format. The field only displays for numeric and date values. If you do not set a format, Analyzer uses the format of the first base measure. See Format field options.
Decimal Places
(Optional) The number of decimal places in results.
Type
The field type (calculated measure). This field is read-only.
MDX
The MDX statement for the measure. This field is read-only.
Formula
Enter the formula. You can type it, or drag measures into the formula area.
Create subtotals using measure formula
Use this calculated measure when Analyzer calculates subtotals.
Select OK to save changes to the data source, or Cancel to close without saving.
Add a calculated measure to the data source
Calculated measures are user-defined measures based on formulas applied to existing measures. In Analyzer, the formula is an MDX statement.
If you save the calculated measure to the data source, other users can use it immediately.
If you want a calculated measure for a single report only, see Create a calculated measure in a report.
In the Available Fields list, select a measure, then select the down arrow next to it.
Select Create Calculated Measure.
The Create Calculated Measure dialog box appears.
Create Calculated Measure dialog box In Name, enter a name.
In Format, select how results should display.
(Optional) Set Decimal Places.
If you do not set a format, Analyzer uses the format of the first base measure.
In the formula area, enter the MDX statement.
You can type it, drag measures into the formula area, or use the symbol buttons.
(Optional) Select Calculate subtotals using measure formula.
Select OK.
The calculated measure is saved to the data model when you save the report.
(Optional) Select Cancel to close without saving.
Set Analyzer report options
In your report, you can modify how blank measures display, define drill-through columns, and show or hide totals for columns and rows.
To open the Report Options dialog box, select the More actions and options button, and then click Report Options.
Working with rows where the number cell is blank
Blank cells are values in your report that are null or empty. You can control how Analyzer displays blank values. You can also control when Analyzer shows rows or columns with blanks.
Set blank measure display
Analyzer reports break down number fields (like Sales) by text fields (like Product Name). If a product did not sell, the report may show 0, a blank, or a dash (-).
Blank and zero behave differently in calculations. For example, averages include zeros but exclude blanks.
To change how blank measures display:
In the Blank Cells section, set Blank measures display as.
Show rows or columns when measures are blank
Analyzer hides rows and columns when all measures in the row or column are blank. This default gives the best performance.
If you have calculated measures that return values without base measure data, you can show more rows or columns. For example, YTD Sales for a Month can exist even when monthly sales are blank.
To change when Analyzer shows rows or columns:
Select an option in Show rows or columns with.
The options are:
Measure data
Shows combinations with measure data defined by relationships in the database. This option is used most often.
Measure and calculated measure data
Shows Measure data combinations plus additional combinations created by MDX calculations. Use this option with Measure data.
Show all even blank measures
Shows all attribute combinations as a Cartesian join. Use this option to verify dimension combinations without fact data. For example, show
Product LineandSales Territorycombinations with no sales.
If you use Measure and calculated measure data or Show all even blank measures, Analyzer computes cross-joins in memory. Processing increases significantly. Changing these options prompts a performance warning.
This property can be enabled or disabled by an administrator. See the Administer Pentaho Data Integration and Analytics document for details.
What happens when there is no number field on the report?
The following rules apply:
If there is only one text field (for example,
Product Line) and no number field, Analyzer displays all values. For example, it shows product lines even when they have no data.If there is more than one text field (for example,
Product LineandRegion) and no number field, Analyzer may hide some values. This can happen when you show rows or columns with measure and/or calculated measure data. Do not draw conclusions in this state. Add a number field instead.
These rules reduce confusion when expected values do not appear. Also consider usability and performance when showing every combination.
Turn on drill-through links
You can use drill-through links in Analyzer to view all individual records that make up an aggregate value in your report.
Turning on drill-through links turns all non-calculated number fields into links. When you click a link, Analyzer opens a configurable data grid.
The drill-through grid shows all levels and non-calculated measures that are defined in the report cube by default.
If needed, you can select the columns you want to show in the grid so that report designers only see the selected columns. This is useful if your report cube contains many levels and measures and you want to show only specific data for analysis.
Drill-through links are not available under the following conditions:
Any calculated measures, including schema-defined calculated measures and user-defined measures, such as percentages, running sum, and trend measures.
Measures and levels set as hidden in the Available Fields list will not be visible in the Drill-Through view. For more information, see Hide and unhide fields.
Subtotaled cells.
Follow the directions below to turn on drill-through linking in the Report Options dialog box.
In the Cell drill-through section, select the Drill-through links on measures check box.
The number fields in your report will turn into links.
Click the Select drill-through columns to select the columns you want to appear.
Click OK.
The measure fields in your report will turn into links.
You now have drill-through links for numeric, non-calculated members.
If you choose, you can later disable the drill-through links by clearing the Drill-through links check box.
Options for totals
By default, grand totals do not display when you view a report as a pivot table. Also, totals never display when you view a report in chart format.
Below are the options for showing and hiding grand totals for rows and columns.
Grand totals for rows
Select to display grand totals for rows in your report.
Grand totals for columns
Select to display grand totals for columns in your report
Totals with filtered values
Select to display totals which include filtered values. By default, totals only display the sum of the values which are visible on the report. If you want to see a total that includes the values that are filtered out, right-click on the Grand Total cell in the report and select Display Totals that Include Filtered Out Values from the menu.
Totals on top/left
Select to display totals at the top for column totals and on the left for row totals in a pivot table. Typically, totals appear at the bottom of columns and at the end of rows.
To hide grand totals while viewing a report as a pivot table, right-click the Grand Total cell on the report, and select Hide Grand Total from the menu. This method does not allow you to show grand totals again once it is hidden.
Freeze column headers and row labels
If you have long columns and rows of data that extend beyond the viewable area of the report window, you can freeze the column headers and row labels. When you scroll through your report, your frozen column headers and row labels always remain visible.
Below are the options for freezing identifying labels for columns and rows.
Column headers
Select to freeze the column headers in your report. The column headers will remain visible when you scroll down.
Row labels
Select to freeze the row labels in your report. The row labels will remain visible when you scroll right.
Set default report options
As an administrator, you can add default report options that are applied whenever a new report is created. Adding default report options does not apply the changes to existing reports. You can modify the options on reports without affecting the default option settings. You can also set an existing report back to the default settings by clicking the Reset to default link on the Other tab of the Report Options dialog box.
Role permissions are as follows:
Set
X
Reset
X
X
Remove
X
Perform the following steps to set a default report option:
Open the report you want to modify.
Select the More actions and options button, then click Report Options.
The Report Options dialog box displays.
Enter the new default options in the Report Options dialog box and select Set as default for all reports.
The Alert dialog box displays and gives you the option of canceling your changes.
Click OK to apply your new defaults.
The default options are set for new reports.
Note: Default settings cannot be applied to the dimensions that are available on the Drill-Through Columns dialog box.
Export an Analyzer report
You can export a report as a PDF, CSV, or an Excel Workbook file.
In the Analyzer toolbar, click More actions and options > Export Report.
Select an output format from the menu. The Export dialog box appears.
If you are exporting to:
PDF
Specify how you want the page formatted.
CSV
Specify formatting options and the Separator character. The default separator is a comma (,).
Excel Workbook
Specify how you want the workbook formatted. You can select or clear the Merge pivot table cells option.
Select this option to keep nested pivot row and column headers grouped together.
Clear this option to separate nested pivot headers to individual rows for easier sorting.Note: Maps are not supported for export to Excel.
After specifying or modifying the export options, select one of the following actions:
Done
Saves any modifications to the export settings, but does not export.
Export
Applies the options and generates output. If your report includes a chart, it is included in the report along with the table view.
Cancel
Discards all changes.
4. \(Optional\) If you want it to keep the export settings, you must **Save** the report.
Metadata for report author, source file location, fields used and filter summary are included in the report.
Advanced topics
Use these topics to extend your Analyzer knowledge beyond basic setup and use.
In this article
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.
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.
When the reader clicks on the Administrative Assistant position within the Finance department in the source report, it looks like the example below:
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
Create an Analyzer report, or open an existing report.
Right-click a row or column level, then select Hyperlink.
The Link On dialog box appears.
Select Enable Link.
To disable linking, clear the Enable Link check box.
In the Link To drop-down list, select Pentaho Repository File.
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.
In Open in, select New Tab, New Window, or Current Window.
In Tool Tip, enter the text to show on hover, then select OK.
Test the hyperlinks, then save the report.
Create hyperlinks to URLs
Create an Analyzer report, or open an existing report.
Right-click a row or column level, then select Hyperlink.
The Link On dialog box appears.
Select Enable Link.
To disable linking, clear the Enable Link check box.
In the Link To drop-down list, select URL.
In URL, enter the full address to open.
Example:
http://www.example.comIn Open in, select New Tab, New Window, or Current Window.
In Tool Tip, enter the text to show on hover, then select OK.
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
Open the Mondrian schema file where you want hyperlinks.
Use Schema Workbench to edit schemas.
Schema workbench In the dimension, add an annotation named
AnalyzerHyperlink.Add the hyperlink as the CDATA value.
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:
In the Pentaho User Console, select Manage Data Sources.
In Manage data sources, open More actions and options, then select Import Analysis.
Manage data sources dialog box Select Browse, select your schema file, then select Import.
The specified dimensions show hyperlinks in new and existing reports associated with the updated cube.
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 a filter.
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.
Drag and drop the dimension member you want to create a parameter for onto the Filters canvas.
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.
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.
Click OK.
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:
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
Right-click a measure in the grid.
Select Conditional Formatting.
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.
Right-click a measure in the grid.
Select Column Name and Format.
In Format, select Expression.
Analyzer adds a default MDX expression:
Update the expression as needed.
If the expression is invalid, Analyzer shows an invalid report definition error.
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:
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,noneUse
nonewhen 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.
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. In the Display Name field, enter a name for your calculated measure.
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.
In the right panel, enter the formula for your calculated measure.
You can type the MDX statement or drag measures into the formula editor.
Select Calculate subtotals using measure formula to use the calculated measure for subtotals.
(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.
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.
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. View or edit the fields.
Some fields only display if you have the Manage Data Sources permission.
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.
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/exportCSV export
http://localhost:8080/pentaho/api/repos/<repository path to report with xanalyzer extension>/service/export?format=CSVEXCEL export
http://localhost:8080/pentaho/api/repos/<repository path to report with xanalyzer extension>/service/export?format=EXCELJSON export
http://localhost:8080/pentaho/api/repos/<repository path to report with xanalyzer extension>/service/export?format=JSONThe 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.jsoninanalyzer.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>_STARTand 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=CSVTo 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.
Troubleshooting
See our list of common problems and resolutions.
Last updated
Was this helpful?