Multi list parameters (multi list values and multi selection buttons) are a great way of letting a user filter a report on one or more values at once. Recently I came across a situation where I had to display these values in a friendly format. This tip will outline a method I used to display friendly formatted parameter values of a multi list parameter inside a report using Pentaho Report Designer 3.6.
My current report (based on the Steel Wheels sample data) displays the product code and product name based on multiple selections of product codes using a multi value list parameter type:
To populate the above report I use the following queries:
-- Name: product -- Description: Populates the main report SELECT PRODUCTCODE, PRODUCTNAME FROM PRODUCTS WHERE PRODUCTCODE IN (${productCodes}) -- Name: productsList -- Description: Populates the product code parameter SELECT DISTINCT PRODUCTCODE FROM PRODUCTS |
The options for the product code multi value list parameter are:
Now I need to display the multi list parameter values (product codes) in the report header but formatted like this:
S10_1678, S10_1949, S10_2016 |
To create the above format I’m going to use MySQL’s GROUP_CONCAT function (there are similar functions out there for PostgreSQL, Oracle, SQL Server etc.) in a new query. The query which uses the GROUP_CONCAT function looks like this:
-- Name: productsSelected -- Description: Displays a friendly formatted list of parameters inside the sub-report using the productCodes parameter SELECT GROUP_CONCAT(PRODUCTCODE, ', ') FROM PRODUCTS WHERE PRODUCTCODE IN ({$productCodes}) |
As this is a separate query I will need to create an inline sub-report. The first step is to drag and drop the sub-report object from the left pane onto the report-header and assign the productsSelected query:
The next step is to double click on the sub-report to work inside it. Once the sub-report has opened under the Data tab right click on the Parameters item and select the Edit Sub-report Parameters… option. From the Sub-report parameters window add in the productCode parameter under the Import Parameters area (Outer and Inner) and click OK to save.
To display the multi value list field (GROUP_CONCAT(PRODUCTCODE, ‘, ‘)), I dragged and dropped the field from the productsSelected query under the Data tab onto the report header section and also added a label Selected Product Codes:
After the previewing the report I can now see that the parameters from the multi value list are displayed in a friendly format:
This method does have a downfall as it runs another query but it is a simple and clean way of outputting the values of multi value list parameters.
| Filename | Type | Notes |
|---|---|---|
| MultiValueListParameters | PRPT | Released 14/04/2010 |
| MultiValueListParametersPreview | Released 14/04/2010 |
Do you use a different method? Let me know by posting a comment!
Enjoy.
Posted by Rafael Veloso on Apr 15, 2010
Hi Prashant,
As you know, we were using the sub-report method to display the selected parameters. We were also not very happy with the result as it returned a table and we couldn’t format it accordingly. Actually, using a xaction file seemed to be the next step to take.
I’m very happy to see how you simply solved it by using a mySQL function (GROUP_CONCAT which I had no knowledge about it’s existence).
Thank you for posting this and kudus for yet another great post for all pentaho/PRD users.
Cheers,
Rafael
Posted by Lynn Yarbrough on Apr 22, 2010
Great website !
Lynn Yarbrough
Manager of Training
Pentaho
Posted by Prashant Raju on Apr 22, 2010
Thanks Lynn!
Posted by Prashant Raju on May 12, 2010
@Nilesh
Oracle have some tutorials on an equivalent to GROUP_CONCAT here or even this may help. Google is your friend.
Prashant.
Posted by Joel on Jun 4, 2010
Prashant,
Unless I’m misunderstanding the problem, there’s an easier way to display filter selections without the extra query and subreport. You can use the CSVTEXT function to display a delimited list of selected filter values in a message field or a text field. See Inventory.prpt in the Steel-Wheels sample reports.
The technique you described may still be useful for displaying a column other than the key. For example, you need to use ProductCode in your queries but you want to display ProductName in the filter list for readability.
Thanks for the helpful articles!
Joel
Posted by Prashant Raju on Jun 5, 2010
Joel
You are correct the CSVTEXT function can do that fine however I was trying to demonstrate the latter i.e. ProductName(s) instead of ProdutCode(s) – probably did the post up too quickly!
Thanks for your input!
Prashant
Posted by Agatha on Jul 24, 2010
Prashant,
I tried programming your example with Pentaho Reportings Java API.
Unfortunately, I get (to see in the debug-mode) an empty table model. Would you have idea why?
Please find attached my code.
In advance thank you!
Agatha
——————————————
SQLReportDataFactory factory = new SQLReportDataFactory(connection);
factory.setQuery(“productsList”, productsListSQL);
factory.setQuery(“product”, productSQL);
DefaultListParameter entry = new DefaultListParameter(“productsList”, “productCodes”, “productCodes”, “productCodes”, true);
ModifiableReportParameterDefinition paramDef = (ModifiableReportParameterDefinition)masterReport.getParameterDefinition();
paramDef.addParameterDefinition(entry);
masterReport.setParameterDefinition(paramDef);
SubReport subReport = new SubReport();
subReport.setQuery(“product”);
subReport.addInputParameter(“productCodes”, “productCodes”);
masterReport.getItemBand().addSubReport(subReport);
AbstractTableModel tableModel = (AbstractTableModel)factory.queryData(“product”, masterReport.getParameterValues());
Posted by Aleya on Oct 12, 2010
Hi,
This is you explain about how to display multiple result using a pentaho.
But.. if you have the original form that have 8 lines to display the multiple data and we need to displayed all 8 lines on it even there is only 2 data exist.
Mean 1 line = 1 Data only should be displayed on it.
Based what I am try from your explaination, if only 2 data exist , there is only 2 lines exist in the form.
But, the other 6 lines from the 8 lines not able to displayed. How we want to displayed all 8 line even there is no data or have the data?.
Please explain and help me to solve this issues.
Posted by Anisha on Mar 30, 2011
Hi Prashant..
I have a problem working with Parameters in PRD3.6..Plz help me out.
I have 2 reports.I wan to pass the parameter called “OrderNumber” from 1st report to 2nd report thru hyperlink.and in the 2nd report it should filter on this parameter..below is the description about wat i have done.
In my 1st report I have created a hyperlink on column “OrderNumber” and here is the URL(=”http://localhost:18080/pentaho/content/reporting/reportviewer/report.html?solution=EFT_Reporting&path=reports&name=SubReport.prpt &OrderNumber= ” &PARAMETERTEXT([ V_ORDER_NUMBER]) & “&StoreKey=”&PARAMETERTEXT([I_STORE_KEY])
) in which I am passing the parameters “OrderNumber” and “StoreKey”
In my 2nd report I have created 2 parameters “OrderNumber” and “StoreKey” and the value it is picking from respective query “Order_Number” and “Store_key”.In the Query ”Transaction HistoryecommerceSubReport” I have included a where clause as(where OrderNumber=${ OrderNumber}.And I have published this 2nd report.
Posted by simon on Jun 15, 2011
Hi!
Can anyone tell me how can I do a similar thing?
I have table like:
[code] [display]
------------------------
X1 Ford Focus
X2 Fiat Bravo
X3 Renault Clio
X4 Opel Corsa
There is a multi select parameter that displays the pretty names but the query behind uses the codes.
All that I can manage with Pentaho Report 3.8 but then I want to have a string-field in the report's header with the user selections, but showing the "display" values and not the "code" values. Something like: "Selected Models: Ford Focus, Operl Corsa"
I can get a comma separated list with the "code" but not the "display" if I insert a string-filed and select the parameter.
Any links, post or hints on how to get the "display" rather than the "code" for the user selections would be a life saver! Thanks!
Posted by Complete Pentaho Installation on Ubuntu, Part 10 | Interesting IT Tip's on Oct 27, 2011
[...] Showing multiparameter values on prashantaju.com [...]
Posted by kranthi on Nov 10, 2012
Hi Prashant
is there a way that i can have a “select All” option for my prompts. assume i have 30 values in my prompts,is there an option to select all values as default. i am using the latest version of prd and oracle db. i tried using a union in my parameter query but it is not working.
any help would be appreciated.
thanks
kranthi
Posted by Jee on Feb 5, 2013
Hi Prashant,
Is there any way on how to make a multivalue paramater work in Pentaho CDE using MDX?
Thanks!