Analyse This

Pentaho Report Designer 3.5 came with nine new pre built parameters. Below is a quick overview of the nine parameters. Each overview includes:

• Screenshot and description
• How to setup your parameter query
• How to setup your report query to accept the parameter

To directly go to a parameter type you are interested in click on a link below (every example uses the Steel Wheels sample data):

• Drop Down
• Single Value List
• Multi Value List
• Radio Button
• Check Box
• Single Selection Button
• Multi Selection Button
• Text Box
• Date Picker

Drop Down

• Similar to a select box in HTML
• Displays one active option at a time unless you click on the arrow to display all options
• Can only select one option at a time

Parameter Query

This query will display a unique list of Product Lines as options inside a drop down.

-- Name: enterProductLine
SELECT DISTINCT `products`. PRODUCTLINE
FROM   `products`

Report Query

This query will display all information for each product by the selected Product Line option from the drop down. As only one option can be selected at a time it is recommended to use a = (equal) in your WHERE clause.

-- Name: stockByProductLine
SELECT *
FROM   `products`
WHERE  `products`.PRODUCTLINE = ${enterProductLine}

Single Value List

• Similar to a list box in HTML
• Displays more than one option at once (including the active option)
• To see more than one option at once change the Visible Items count under the parameter’s options window
• Can only select one option at a time

Parameter Query

This query will display a unique list of Product Lines as options inside a single value list.

-- Name: enterProductLine
SELECT DISTINCT `products`. PRODUCTLINE
FROM   `products`

Report Query

This query will display all information for each product by the selected Product Line option from the single value list. As only one option can be selected it is recommended to use a = (equal) in your WHERE clause.

-- Name: stockByProductLine
SELECT *
FROM   `products`
WHERE  `products`.PRODUCTLINE = ${enterProductLine}

Multi Value List

• Similar to a multi list box in HTML
• Displays more than one option at once (including active options)
• To see more than one option at once change the Visible Items count under the parameter’s options window
• Can select more than one option at a time

Parameter Query

This query will display a unique list of Product Lines as options inside a multi value list.

-- Name: enterProductLine
SELECT DISTINCT `products`. PRODUCTLINE
FROM   `products`

Report Query

This query will display all information for each product by the selected Product Line options in the multi value list. As multiple options can be selected at a time make sure you use an IN() condition in your WHERE clause.

-- Name: stockByProductLine
SELECT *
FROM   `products`
WHERE  `products`.PRODUCTLINE IN (${enterProductLine})

Radio Button

• Similar to radio buttons in HTML
• Displays more than one option at once (including the active option)
• Can only select one option at a time

Parameter Query

This query will display a unique list of Product Lines options as radio buttons.

-- Name: enterProductLine
SELECT DISTINCT `products`. PRODUCTLINE
FROM   `products`

Report Query

This query will display all information for each product by the checked Product Line radio button. As only one option can be selected at a time it is recommended to use a = (equal) in your WHERE clause.

-- Name: stockByProductLine
SELECT *
FROM   `products`
WHERE  `products`.PRODUCTLINE = ${enterProductLine}

Check Box

• Similar to a check box in HTML
• Displays more than one option at once (including active options)
• Can select more than one option at a time

Parameter Query

This query will display a unique list of Product Lines options as check boxes.

-- Name: enterProductLine
SELECT DISTINCT `products`. PRODUCTLINE
FROM   `products`

Report Query

This query will display all information for each product by the checked Product Line check boxes. As multiple options can be selected at a time make sure you use an IN() condition in your WHERE clause.

-- Name: stockByProductLine
SELECT *
FROM   `products`
WHERE  `products`.PRODUCTLINE IN (${enterProductLine})

Single Selection Button

• Similar to a buttons in HTML
• Displays more than one option at once (including the active option)
• Can only select one option at a time

Parameter Query

This query will display a unique list of Product Lines as single selection buttons.

-- Name: enterProductLine
SELECT DISTINCT `products`. PRODUCTLINE
FROM   `products`

Report Query

This query will display all information for each product by the selected Product Line button. As only one option can be selected at a time it is recommended to use a = (equal) in your WHERE clause.

-- Name: stockByProductLine
SELECT *
FROM   `products`
WHERE  `products`.PRODUCTLINE = ${enterProductLine}

Multi Selection Button

• Similar to a button in HTML
• Displays more more than one option at once (including active options)
• Can select more than one option at a time

Parameter Query

This query will display a unique list of Product Lines options as multi selection buttons.

-- Name: enterProductLine
SELECT DISTINCT `products`. PRODUCTLINE
FROM   `products`

Report Query

This query will display all information for each product by the selected Product Line buttons. As multiple options can be selected at a time make sure you use an IN() condition in your WHERE clause.

-- Name: stockByProductLine
SELECT *
FROM   `products`
WHERE  `products`.PRODUCTLINE IN (${enterProductLine})

Text Box

• Similar to a text boxes in HTML
• Free text only
• Is case sensitive and must be exactly the same as the option

I have tried numerous ways on making the text box parameter more flexible, if you have any ideas please let me know.

Parameter Query

This query will display a unique list of Product Lines which the contents of the text box will match.

-- Name: enterProductLine
SELECT DISTINCT `products`. PRODUCTLINE
FROM   `products`

Report Query

This query will display all information for each product by the contents of the text box matching a Product Line option. As only one option can be selected at a time make sure you use a = (equal) in your WHERE clause.

-- Name: stockByProductLine
SELECT *
FROM   `products`
WHERE  `products`.PRODUCTLINE = ${enterProductLine}

Date Picker

• Similar to standard JavaScript date pickers
• No way of formatting the date picker output, for example displays date, time and timezone

Parameter Options

As the date picker is providing the user values to input you do not need to specify a parameter query so your parameter options should look similar to the screenshot below:

Parameter Query

No parameter query is needed as the date picker is generating the parameter value.

Report Query

This query will display all information for orders which are greater than the specified Order Date from the date pickers value. As only one option can be selected at a time make sure you use a = (equal), >, >=, <= or < condition in your WHERE clause.

-- Name: stockByProductLine
SELECT *
FROM   `orders`
WHERE  `orders`.ORDERDATE > ${enterOrderDate}

Hopefully these descriptions shed more light on the parameters available in Pentaho Report Designer 3.5.

Enjoy.