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.
Posted by Winston on Mar 1, 2010
How are the multi value list box prompt values displayed in the report ?
I get java.lang error when I try to display the multi list prompt values. I tried using message filed, text box etc.
Thanks,
Winston
Posted by Prashant Raju on Mar 1, 2010
Winston,
Does your value type match the data type of your parameter value? For example, if you have a value for the parameter which is a string the value type must also be a string.
Prashant.
Posted by James on Mar 24, 2010
Prashant,
Thanks for the information and tutorials on your site.
I have a question, how can “hidden” report parameters throw errors and also have these errors be displayed on the bi-server? Errors in the Report Designer 3.6 display the errors in “preview mode” but after publishing the report to the bi-server, the report won’t run if there is an error to a “hidden” parameter but the error message is not displayed. How can we display the error of a “hidden” report parameter?
thanks,
James
Posted by Prashant Raju on Mar 24, 2010
@James,
I haven’t had a reason to use hidden paramters yet however it should throw an error in PRD? You might want to hit up the forum for some more detail on this issue.
Prashant
Posted by Giuseppe on Apr 1, 2010
Thank you for your post.
I’ve a simple question. How can I define the “set” parameters $enterProductLine?
I want to filter my query on multiple product line (from 1 to all product of my warehouse) in order to plot the specific chart.
Thank a lot.
Giuseppe (from Naples – Italy)
Posted by Giuseppe on Apr 1, 2010
I’m sorry, it was my mistaken on parameters import into a subreport.
But now the question moves on the multi checkbox selection parameters. If I want to check more product type to plot, how can I define it? There is just an exclusive check box parameter type.
Thanks again
Giuseppe (from Naples- Italy)
Posted by Prashant Raju on Apr 2, 2010
@Giuseppe,
I’m trying to understand what you need here… You would like to select more than one Product Type and after a selection occurs that chart i.e. Bar will be visible?
Prashant.
Posted by Banzai on Apr 6, 2010
I wonder how I can do to capture and change the date format (dd / MM / yyyy), then make the sql query
Posted by Banzai on Apr 6, 2010
I created a parameter whose information comes from a sql query, it returns two fields, name and code. In the “Edit Parameter” I place the field name in “Display Name” and the code in the “Value”. when you run the search report is filtered by the value that is stored in “Value.” This is correct. But when I generate the report I have a header where I put the parameters for which I conducted the search, then it shows the code, but I need to display the name. The question is. How I can do to the value selected (Value-> code) to make an sql query that I devuelba the respective name? Thanks
Posted by Prashant Raju on Apr 6, 2010
@Banzai
This is more of a SQL issue how about setting up the SQL statement to accept the date format of the date picker?
Prashant.
Posted by Prashant Raju on Apr 6, 2010
@Banzai
You could just drag the field which you are using the parameter on into the header so that you know what it is filtered to or alternatively you can create an open formula function which will equal the parameter i.e. = [parameterName]
Prashant.
Posted by Banzai on Apr 8, 2010
I will explain what he had done when I realized the previous question. First I created a SQL query in “DataSets” Where to Find the “name” according to the Code which was passed by the “Value” parameter, just as I explain in your response. Second, I created a function “Single Value Query, where you step in” Query Name “the name of the SQL query I created in the first step and” Field Name “parameter in the step where the elements are to be searched, the code in the “Value” and the name in the “Display Name”. Finally I put a TEXT-FIELD which will step in “field” created just before the function.
i’t right??
doing this does not work
Posted by Rafael Veloso on Apr 13, 2010
Hi Prashant,
Thanks for posting this. It’s really straightforward and very helpful.
I Have a question though:
How can I print the selected items from a multi-value select box into the report? I Want to know the parameters used to generate the values just by looking to the report output.
Thanks in advance.
Posted by Prashant Raju on Apr 14, 2010
Rafael
One method (which I mentioned on IRC) is to use sub-reports. Create another query i.e. SELECT col FROM table WHERE col IN ${param} and assign that query to the sub-report which is inline with your report header. Import the ${param} as a parameter in the sub-report and then just drug the col value onto the details section of the sub-report.
Just one method maybe you have figured out a better way?
Prashant
Posted by Rafael Veloso on Apr 14, 2010
Prashant
That’s a simple and very smart way to do it. I’m using that for now. But as you may agree, that does not give us the ability to format the results as we wish. The result set comes in one column and we cannot, for example, show the result in one line with the values divided by some kind of separator.
I think the solution lays on the creation of a xaction file. Will try it when I have some time.
Thanks for the help and time disposed.
Cheers,
Rafael
Posted by norlaysia on Apr 28, 2010
Prashant,
Thanks for your helpful website.
i have problem to create subreport.
Can you prepare tutorial step by step for creating subreport?
Thank you.
regards,
norlaysia
Posted by Prashant Raju on May 9, 2010
@norlaysia
Have you had a look at this post, it has a little bit about using sub-reports.
Prashant.
Posted by Sivakumar on May 27, 2010
your suggestions are very good and very helpful.
Posted by Alex Dumont on Jun 30, 2010
Great article, examples are really usefull.
I’ve set some “cascading parameters”, but I’d like to reorder them, because at the moment they appear in the order I created them, but it’s not the order I want them to appear to the user (it’s the exact opposite order).
Do you know if there is any way to do this?
Posted by Prashant Raju on Jun 30, 2010
@Alex
You can right click on the parameters under the Data tab and use the Bring Forward and Send Backwards menu options – this will affect the order they are executed and seen by the user.
Prashant
Posted by Alex Dumont on Jun 30, 2010
Posted by Luis Lopez on Aug 4, 2010
Hi Prashant,
I’ve been following your tutorials and they’ve been very helpful.
However, I’m still looking for a way to use a parameter as a partial or complete part of a table name in a SQL query. I mean, for example:
SELECT * FROM ${table} or
SELECT * FROM ta${ble}
Does PRD have a way to do this?
I’ve tried this query in several forms in the data set but it hasn’t worked.
Thanks.