Tip: Displaying Multi Parameter Values with Pentaho Report Designer.

Posted under Pentaho, Report Designer, Tips, Tutorials

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:

Pentaho Report Designer Preview

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:

Parameter Options

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:

Assigned the productSelected query to the sub-report

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.

Sub-report Parameters Window

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:




Report Header with Parameter Values

After the previewing the report I can now see that the parameters from the multi value list are displayed in a friendly format:

Preview

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 PDF Released 14/04/2010

Do you use a different method? Let me know by posting a comment!

Enjoy.

Posted by Prashant Raju on Apr 15, 2010 — 13 CommentsShare

13 Comments for “Tip: Displaying Multi Parameter Values with Pentaho Report Designer.”

  1. 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

  2. Posted by Lynn Yarbrough on Apr 22, 2010

    Great website !

    Lynn Yarbrough
    Manager of Training
    Pentaho

  3. Posted by Prashant Raju on Apr 22, 2010

    Thanks Lynn!

  4. 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.

  5. 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

  6. 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

  7. 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());

  8. 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.

  9. 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.

  10. 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!

  11. Posted by Complete Pentaho Installation on Ubuntu, Part 10 | Interesting IT Tip's on Oct 27, 2011

    [...] Showing multiparameter values on prashantaju.com [...]

  12. 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

  13. 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!


Post a Comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>