Advanced topics
Use these features to go beyond basic Dashboard Designer setup and use.
In this topic
Set dashboard parameters
Dashboard panels can pass parameters into reports, charts, URLs, and data tables.
Use parameters to filter data, drive prompts, and link panels together.
Understand where parameters come from
Some content defines its own parameters.
For example, an .xaction or .prpt can expose parameters and default values.
You’ll see them under Parameters in the dashboard Edit pane.
Parameter names in the source file are fixed.
You can’t rename them.
You can’t add or remove them.
Other panel types let you define your own parameters.
For example, charts created with Chart Designer, embedded URLs, and data tables.
For these, you can set both the parameter name and value.
Get started with the Query Editor
When you place a chart in a dashboard panel, you use the Query Editor.
The Query Editor retrieves data from a database for your chart or data table.
Add conditions to filter the data.
Use parameters if you plan to:
Use dashboard prompts.
Link one panel to another.
Example: link a pie slice selection to an adjoining data table.
The data table can update when someone clicks a pie slice.
You do this by using a parameter in the query condition.
Create chart or data table parameters
Create a parameter by entering a name in curly braces in the Value field.
Example: {TERRITORY}.
Set a default value (source) for the parameter.
Example: NA (North America).
When the chart or data table runs, it uses the current parameter value.
Dashboard consumers can change the value through prompts or interactions.
To define multiple default values, separate them with a pipe (|).
Example:
NA|EMEA|APAC
If you want a static query value, don’t use curly braces.
This disables Default (value) because there is no parameter.
Add dynamic dashboard titles
Dashboard panels can show their current parameter values in the panel title.
Open the Edit pane
Click the Edit (pencil) icon on the top toolbar.
Select the panel
Click the report or panel that has the parameters you want to show.
Add the parameter to the title
Click Add Parameter.
The parameter appears after the title.
Apply changes
Click Apply.
Exit edit mode
Close the Edit pane by clicking the Edit (pencil) icon again.
The title now shows the parameter values.
Limits
On Windows, URL parameters are limited to 2,048 characters.
This limit excludes the URL path itself.
Use prompts on dashboards
Prompts filter dashboard data based on user input.
Use prompts to show the right slice of data.
Example: show EMEA sales instead of NA.
To filter dashboard content, the target content must include at least one parameter.
In this section (prompts)
Get started with dashboard prompts
In the dashboard page, select Edit (the pencil icon).
The Objects pane appears.
Under General Settings, select Prompts.
The Prompts pane appears on the right.
To display a prompt toolbar to users of the dashboard, enable Show Prompt Toolbar.
The prompt toolbar appears at the top of the dashboard.
Select Add.
The Prompts dialog box appears.
Enter a display name for the control label, such as
Region.Optional: Enable Display Name as Control Label.
Select a control type:
Drop-down: Users select one value from a drop-down list.
List: Users select one or more values from a scrolling list.
Radio button: Users select one value from radio buttons.
Check box: Users select one or more values from check boxes.
Button: Users select one or more values from buttons.
Text field: Users enter a text string or number.
Date picker: Users select values based on calendar dates.
Your dashboard now has a prompt.
Edit prompts
Locate the prompts pane at the top of the panes in the dashboard.
Find the prompt you want to change.
Select an alternative prompt from the drop-down menu next to the prompt.
The dashboard updates to match the new prompt selection.
Other prompt types
In addition to standard dashboard prompts, you can use:
Toolbar prompts
Date prompts
List prompts
Create a toolbar prompt
Toolbar prompts let users refine report results in a dashboard.
Open a dashboard that contains at least one parameterized report.
Select Edit Content to open the editing options.
In the Objects pane, select Prompts.
Enable Show Prompt Toolbar.
Select Add.
The Prompt dialog box appears.
In the Name field, enter the title for the prompt toolbar.
In the Control box, select the control type.
Under Type, ensure Static List is selected.
Customize toolbar prompts
In the Data box, select Add.
The List Value dialog box appears.
In the Label field, enter the option name as you want it to appear.
In the Value field, enter the parameter source name.
If you are using the Steel Wheels sample, enter
Classic Cars.Add labels and values for each parameter you want to filter. Select Close.
If you are filtering an Analyzer report and using a static list, you can add the option All. This option drops the filter from the report and shows all values.
Under Control Properties > Initially Selected, choose which item appears first in the prompt list.
Choose Use First Value to default to the first list value. Choose Specify to set a specific value.
Select OK.
In the Objects pane, choose the report you want to filter. Select the Parameters tab and choose the correct Source for the parameter.
The source should match the name of your prompt.
Select Save.
Create a date prompt
The date picker prompt lets users select values based on calendar dates.
When you create a date picker prompt, you must set a date format. The default format is yyyy-MM-dd.
The date picker prompt uses Dojo date formats. In the past, this component was based on the jQuery datepicker. To support legacy formats, old formats are converted automatically.
Not all formats are supported or make sense when a date is selected. Anything with less granularity than a unit of "day" defaults to the Dojo format. Use formats from d to y that the date picker control supports. For example, yyyy-MM-dd, yy-M-d, and d/MM/yyyy are valid.
As a result, the pattern can be any combinations of the following patterns of years, quarters, months, and days:
G
era
AD
y or yy
year (two digit)
16
yyy or yyyy
year (four digit)
2016
q or Q
quarter (one digit)
1
M
month numeric (no leading zero)
1
MM
month numeric (two digit)
01
MMM
month name short
Jan
MMMM
month name long
January
d
day of month (no leading zero)
4
dd
day of month (two digit)
04
D
day of the year
4
E
day of the week
2
Create a list prompt
When you create a metadata list, you define a query to retrieve a list of display names and corresponding values from a metadata data source provided by your administrator.
You must have a data table or chart that contains at least one parameter. Otherwise, the prompt control will not work.
In the dashboard page, under General Settings, select Prompts.
The Prompt Editor appears on the right.
To display a prompt toolbar to users of the dashboard, enable Show Prompt Toolbar.
A placeholder for the prompt toolbar appears at the top of the dashboard.
Select Add to add a prompt.
The Prompts dialog box appears.
In the Prompts dialog box, enter a Name for your prompt.
Under Data Type, choose Metadata List.
Select Select to choose the data source that contains the content you need, then select OK.
The Query Editor opens.
In the Query Editor, build a query to choose either:
A single column, which represents both a name and a value
Two columns, representing the display names and corresponding values
If a single-column query is defined, the values of that column are used for both display names and values.
Select OK to exit the Query Editor.
Your options appear under Selected Items in the Prompts dialog box.
Under Control Properties, enter a default Label and Value for the initially selected option.
Select a Label to display in the prompt control.
This is the user-friendly name that users will see in the dashboard.
Select the Value.
This is the value in the database that is associated with the label.
Optional: Choose a Display type.
Some prompt controls let you choose the position of prompt options. If you have a long list of options, such as a list of cities, options may not appear correctly in the user console unless you change Display to Horizontal.
Select OK.
The list of values appears in the prompts toolbar in the dashboard.
Create a cascading list prompt
A cascading prompt changes based on the value a user selects. For example, when the value in the drop-down list Country changes, that prompt is automatically applied to the second prompt, City, which changes its values.
This task uses the example of creating a country-city cascading prompt.
For Type, select Metadata List to create the drop-down prompt
Country.
Filter from metadata list For Type, select SQL List, which includes a parameter, ${selected_country}, in its associated query for the check box prompt
City.
Filter from SQL list Set the Default Value for the
selected_countryparameter toUSAwith New York City (NYC) as the initially selected value for the check box prompt.
To link the City prompt to the Country prompt, choose another Source for the selected_country parameter. Choose the Country prompt as the alternate source. When the prompts are linked, users can choose a country, then choose one or more cities in that country.
Using this example, you can now add a data table, chart, or other content in the dashboard that can be driven by the prompt you created. If you add a pie chart to the dashboard, the pie chart can show the percentage of sales per city. In the Query Editor, create a parameter, {City}, with an extended default value: NYC|Las Vegas. The resulting pie chart displays values for New York City and Las Vegas. Under the Parameters tab associated with the pie chart, change the Source value for the City parameter to the City prompt.
When the report is saved, dashboard users can see results for a country and multiple cities in that country.
Link dashboard prompts to Analyzer parameters
The instructions below explain how to parameterize an Analyzer report.
Note: This process only applies to dashboards that include parameterized Analyzer reports. You must have a query parameter in the Analyzer report to proceed.
In your Analyzer report, right-click a field you want to link to, then select Filter.
The Filter dialog box opens. In this example, data will be filtered by Territory.
Analyzer filter Enter a name for the parameter in the Parameter Name field and select its checkbox to enable it.
Select the values you want associated with the parameter. Use the arrow buttons to add and remove values.
Select OK to save and close the Filter dialog box.
Be sure to save your report.
In the upper-left corner of the report, confirm that a filter is in use.
Optional: Select Edit (pencil) to change the filter, or Delete (X) to remove it.
Create a dashboard and drag the Analyzer report into a panel.
The parameter name appears under Parameters.
If a Between operator is parameterized, two parameters are automatically created with the suffixes
_STARTand_END.Add a filter to the dashboard based on the parameter you created in your Analyzer report.
The filter appears in the dashboard.
Use data tables in a dashboard
Use a data table to show query results in a dashboard panel.
Data tables are interactive. Viewers can sort, resize, and reorder columns.
Add a data table to a dashboard
Select a panel in the Dashboard Designer.
Select Insert > Data Table.
Select a Data Source opens.
Select a data source.
Select OK.
The Query Editor opens.
Build your query.
Expand a category to show its columns.
Select the column you want.
Select the yellow arrow to add it to Selected Columns.
Add conditions.
Use conditions to filter results. You can add multiple conditions.
Under Combine, select the operator: and, or, and not, or or not.
Under Comparisons, select the comparison operator, such as
=,<, or>.Optional: For numeric columns, select an aggregation type:
SUM: Sum of valuesCOUNT: Count of valuesAVG: Average of valuesMIN: Minimum valueMAX: Maximum value
Select Preview at any time to review results.
Add one or more Order By columns.
Order By controls sorting of the returned rows.
Select OK.
The data table appears in the selected dashboard panel.
Update the data table display
Edit the data table display directly in the dashboard panel.
Sort, show, or hide columns
In a column header, select the drop-down arrow.
Select Sort Ascending or Sort Descending.
Optional: Clear a column checkbox to hide that column.
Resize a column
Select the right edge of the column header.
Drag left or right.
Reorder columns
Drag a column header to the new position.
Use pagination
Use pagination to page through large result sets.
Use content linking to create interactive dashboards
Depending on your needs, you can create a static dashboard, which contains content in each panel that is separate but related. For example, you may provide users with a bar chart that contains total sales figures by region. Additionally, you may provide a data table that displays sales details for each state in a specific region. You may also want to provide sales data associated with each salesperson in a specific region. The content in your dashboard is useful to dashboard consumers, but to make it more interactive, you may want to consider using content linking.
The content linking features in dashboards allow you to associate (link) content in one dashboard panel to content on another dashboard panel as long as query parameters have been defined. These features are particularly helpful for drilling down or for dynamic filtering; for example, when dashboard consumers explode a single slice in a pie chart to launch content in a data table associated with that pie slice. In this instance, dashboard consumers are moving from a summary view to a detailed view interactively.
You can use content linking if your dashboard panel contains a data table, chart, .xaction, .prpt, and Analyzer report.
In this section (content linking)
Getting started with query parameters
Query parameters are required for content inside a dashboard panel to receive values used to filter data from a filter control.
They are also required when receiving values from content in other dashboard panels that are broadcasting values through content linking.
How you define query parameters depends on the content type in your dashboard panels:
You are defining parameters in an Analyzer report
You are defining parameters for a chart or data table
Use the Query Editor to define your parameterized query. Then see Link charts and data tables and Link columns in a data table to other dashboard panels.
Link charts and data tables
The instructions below explain how to create links to charts and data tables in a dashboard. Adjust the examples shown in these instructions, as necessary, when working with your own data.
Create a simple dashboard that contains a chart and a data table.
At this point, none of the content has been linked and you have a "static" dashboard.
Simple dashboard In the pie chart in this example, you want the data table on the right to update with the values associated with a pie chart slice when dashboard consumers click that slice (NA, APAC, Japan, and EMEA).
For example, if a report consumer clicks the EMEA slice, the data table will display values associated with EMEA and nothing else.
To get the correct filter display, you must first create a parameterized query that drives the content in the data table.
Click the Edit button to open the Edit pane at the bottom of the screen.
Within the Objects pane, choose the report you want to parameterize.
Parameterizing a query, as described here, allows you to pass values dynamically and update the chart based on events triggered by other elements of the dashboard such as a user choosing an item from a filter control or following links defined in content associated with another panel in the dashboard.
Click the {p} button next to the Title box.
The parameters will populate after the title in the Title box.
Click the Parameters tab and ensure that the parameter name is linking to the correct source.
Click Apply.
The new source for the parameter corresponds to the title of the dashboard panel that contains the chart as shown in the example above. This new source will now drive the display in the data table.
Click the Edit button (pencil icon) to exit edit mode.
The filters will appear after the panel titles.
Save your dashboard.
When users click a pie slice or bar in a chart, the data table displays content associated with that specific pie slice or bar.
The currently applied filters appear after the title.
Link columns in a data table to other dashboard panels
The instructions below show you how to link a chart to a column in a data table. You must adjust the instructions when working with your own data.
Create a simple dashboard that contains a data table and a bar chart.
At this point, none of the content has been linked and you have a "static" dashboard.
Notice the data table in the example here. You want dashboard consumers to click a product in the Product Line column and have the bar chart update with information about sales by territory for that specific product line.
Link on column Add a parameterized condition to the query for the bar chart by specifying a parameter name in curly braces in the Value text box. Then provide a default value for that parameter in the Default text box.
In the example here, a parameter called Productline with a default value of Classic Cars has been created.
Parameter query In the Chart Designer, set the data definitions for the series, category and values columns associated with your bar chart and click OK.
Under General Settings, choose the data table and click the Content Linking tab. Enable content linking on the column in your data table that will filter content in your chart.
Each of the columns in a data table can broadcast values to other dashboard components.
Value as link Under General Settings, choose the chart and click the Parameters tab. Click the down arrow in the Source text box to display another source for the parameter you created.
In the example below, notice that Order Details - Product Line (the name of the dashboard panel that contains the data table) can now be selected as a source for the Productline parameter.
Select a new source Save your dashboard.
In the data table, choose an item in the column that has content linking enabled.
The content in the chart updates in response to the item that was clicked in the data table.
In the example below, the Product Line column was enabled for content linking.
Link an Analyzer report
You can create content-to-content links from Analyzer content in a dashboard.
Use content linking to pass values into parameters on other dashboard panels.
Link from a chart (graphic element)
You can create content-to-content links between an Analyzer chart and any other parameterized report such as a Report Designer report, a data table, or another Analyzer report.
Below are general instructions for linking an Analyzer chart to a report. You must adjust the instructions when working with your own data.
Create a simple dashboard that contains an Analyzer chart and a parameterized report.
The example here displays an Analyzer chart and an Analyzer report displayed as a table view. At this point, none of the content has been linked and you have a "static" dashboard.
Hypothetically, if you want users to be able to click a bar in the bar chart and update the Analyzer table view, the table must contain at least one parameter. In the example below, there are two parameters (LINE and TERRITORY) associated with the Analyzer table.
Parameter created for LINE Under General Settings, choose the Analyzer chart, then click the Content Linking tab.
Click the check box (or check boxes) next to the field/column name you want enabled for content linking, and then click Apply.
Chart content linking Under General Settings, choose the Analyzer report (table view) and click the Parameters tab.
Click the down arrow in the Source text box to display another source for the parameters you created.
In the example below, notice that Sales by Line (the name of the dashboard panel that contains the chart) can now be selected as a source for both the TERRITORY and LINE parameters.
Select source on Parameters tab Save your dashboard.
See Saving Dashboards for steps.
In this example, content linking is applied when users double-click a bar in the bar chart.
The data table updates and displays sales details for a product line in a specific territory.
Link from a hyperlink in a table
Below are general instructions for creating content links in an Analyzer report (inside a dashboard) that can be used to drive the query parameter values of content in other dashboard panels. You must adjust the instructions when working with your own data.
Create a simple dashboard that contains an Analyzer report and a data table.
At this point, none of the content has been linked and you have a "static" dashboard.
In the example here, when content linking is achieved, the list of territories (APAC, EMEA, Japan, and NA) will become hyperlinks that, when clicked, will update a customer details data table.
To get the correct filter display, a parameterized query that drives the content in the data table must be created.
Content linking for a table Add a parameterized condition to the query for the data table by specifying a parameter name in curly braces in the Value text box. Then provide a default value for that parameter in the Default text box.
In the example here, a parameter called TERRITORY with a default value of NA has been created. Parameterizing a query, as described here, allows you to pass values dynamically and update the chart based on events triggered by other elements of the dashboard such as a user choosing an item from a filter control or following links defined in content associated with another panel in the dashboard.
Query on territory Under General Settings, choose the Analyzer report. Click the Content Linking tab then click the check box next to the field/column name you want enabled for content linking. Click Apply.
In the Analyzer report, the values under the Territory become hyperlinks.
Hyperlinks in Analyzer table Under General Settings, choose the data table and examine its available parameters. Click the drop-down arrow in the Source text box to display and choose a new source value for the available parameter, then click Apply.
Save your dashboard.
In the example below, content linking was applied. When users click a territory hyperlink in the Analyzer report, the data table updates and displays customer-related details associated with that specific territory exclusively.
Link a report from Report Designer
The instructions that follow show you how a link inside a Report Designer report (.prpt) can drive a parameter in content on another dashboard panel. You must adjust the instructions when working with your own data.
You must have a report (.prpt) that contains a hyperlink before you can complete this task. See Pentaho Report Designer for instructions about adding hyperlinks to a report.
Create a simple dashboard that contains a
.prptreport and a data table.At this point, none of the content has been linked and you have a "static" dashboard. Notice the report (
.prpt) in the example. You want dashboard consumers to click a territory (APAC, EMEA, etc.) hyperlink and update the data table with information about that specific territory.
Report Designer content linking Under General Settings, choose the report (
.prpt) and click the Content Linking tab.Click the checkbox next to the field you want used for content linking.
Add a parameterized condition to the query for the data table by specifying a parameter name in curly braces in the Value text box. Then provide a default value for that parameter in the Default text box.
In the example, a parameter called TERRITORY with a default value of NA has been created.
Filter parameter Parameterizing a query, as described here, allows you to pass values dynamically and update the data table based on events triggered by other elements of the dashboard such as a user choosing an item from a filter control or following links defined in content associated with another panel in the dashboard.
Under General Settings, choose the data table and click the Content Linking tab.
Click the drop-down arrow in the Source text box to display another source for the parameter you created.
In the example, notice that Product Line Share by Territory - Territory (the name of the dashboard panel that contains the
.prpt) is now selected as a source for the Territory parameter.
Select parameter Save your dashboard.
See Saving Dashboards.
When content linking is achieved, the data table updates when a link in the report (.prpt) is clicked as shown in the example.
Create Dashboard Designer templates
Follow these steps to create a new Dashboard Designer template.
You must migrate this template manually when you upgrade. The template files are stored in the Dashboard Designer plugin directory. The Pentaho upgrade procedure does not migrate custom templates.
Stop the Pentaho Server.
Navigate to the
/pentaho/server/pentaho-server/pentaho-solutions/system/dashboards/templates/xul/directory.If you want to remove templates that you will never use:
Delete their corresponding XUL files.
Remove the corresponding files from the HTML sibling directory.
Copy the existing XUL file that most closely resembles the template layout you want to create.
Give the new XUL file a name that starts with a two-digit number.
Add a short description of its dimensions.
All Dashboard Designer templates follow this naming convention.
Create a
.propertiesfile that corresponds to the one you copied.Put one item in it:
<name=><Description here>.Description here is the display name of this template.
Create a thumbnail graphic that fits the same dimensions as the other PNG thumbnails in this directory.
Give it the same name as the previous two files, with a PNG extension.
You should now have three new files with the same base name:
.xul.properties.png
Edit the new
.xulfile and change the box attributes to match your template specifications:A vbox node creates a column.
An hbox node creates a row.
A box element defines an individual panel in each row.
Both height and width define static widths in pixels.
The flex size attribute defines a percentage of the total width of the dashboard.
Ensure each box, vbox, and hbox node has a unique
id.For more details about XUL elements, see the official XUL documentation: http://developer.mozilla.org/en/XUL_Reference.
Save and close all open files, then start the Pentaho Server.
Test your new template and adjust its configuration.
You now have a custom Dashboard Designer template deployed to your Pentaho Server.
Back up your custom templates so you can reapply them after upgrades.
Secure SQL filter function access
The Dashboard Designer has a SQL filter for greater control over a database query. By default, this feature is restricted to administrative users.
Perform the following steps to change these settings:
Ensure the Pentaho Server is not currently running; if it is, run the
stop-pentahoscript.Open the
/pentaho-solutions/system/dashboards/settings.xmlfile with a text editor.Locate the following line and modify it accordingly:
Note: Values are separated by commas, with no spaces between roles.
Locate the following line and modify it accordingly:
Note: Values are separated by commas, with no spaces between user names.
Save and close the text editor.
Restart the Pentaho Server with the
start-pentahoscript.
The SQL filter function is now available in Dashboard Designer to the users and roles you specified.
Last updated
Was this helpful?