Friday, May 24, 2013

Pentaho Report Designer: How to show the parameter display name in your report when it is different from the parameter value

One of my blog's readers just asked me quite an interesting question: How can I show the parameter display name in my Pentaho report if it is different from the parameter value?


Note: Just to clarify, the scenario covered here is when the parameter value and display name are different. So in example when you set the parameter value on an id field and the name on the descriptive field. Because if parameter value and display name are set to the same field, then you can simply drag and drop the parameter name onto your report.


So in our case we defined a parameter called PARAM_OFFICECODE. We set the Parameter Value to OFFICECODE (which happens to be an id) and the Parameter Display Name is set to CITY. We want to use the OFFICECODE to constrain the result set of our main report query (in our case this works better because there happens to be an index on this database table column).

In the report we would like to show in the header the selected office name (CITY) ... but how do we do this? We can not just simply drag and drop the PARAM_OFFICECODE element onto the report header, because it would only display the id (OFFICECODE) and not the display name (CITY).

You might think there should be an easy solution to this … and right you are. It’s just not as easy as it could be, but quite close …


So I quickly put together a bare bone example (don’t expect any fancy report layout … we just want to see if we can solve this problem):


Our parameter:
So if we placed this parameter element on the main report, we would just see the OFFICECODE when we ran the report. So how do we get the display name?


  1. If it is possible to access the name field (in our case CITY) via the SQL query, we could change our main report SQL query and add it there as a new field. But this is not very efficient, right?
  2. We could create a new query which takes the code/id (in our case OFFICECODE) as a parameter and returns the name (CITY) and then run this query in a sub-report which could return the value to the main report (this is in fact what you had to do some years back). Well, not that neat either.
  3. Here comes the savior: The SINGLEVALUEQUERY formula function. You can find this one in the Open Formula section. Thomas posted some interesting details about it on his blog some time ago.


Basically for a very long time we had the restriction that we could only run one query to feed data to our report. With the SINGLEVALUEQUERY and MULTIVALUEQUERY formula functions you can run additional queries and return values to the main report.


So here we go … to retrieve the display value:
  1. We create an additional query called ds_office_chosen which is constrained by the code/id and returns the (display) name: SELECT city AS office_chosen FROM offices WHERE officecode = ${param_officecode}
  2. We create a new formula element called formula_office_chosen and reference the query ds_office_chosen: =SINGLEVALUEQUERY("ds_office_chosen")
  3. We can now use formula_office_chosen in our report:


Once this is set up, we can run the report and the display name of the chosen parameter value will be shown:
My very simple sample report can be downloaded from here.

12 comments:

  1. Hi,
    very nice post! Do you have any idea how to solve this situation: user must choose two or more countries from check box prompt. Report has grouped details by choosen countries. How can I set report to print one by one country for each group?
    Example: user checked USA, China, France... USA must be shown in first group header, China in second and France in third group header..

    Thank you in advance for your answer!
    BR!

    ReplyDelete
    Replies
    1. Thanks for your feedback! This can be achieve by multivalued parameters and creating report groups. You should be able to find some examples for this via Google. If your question is about how to show the report groups in the same order as the parameter values are listed, then sorting the main result set the same way as the parameter result set should do the trick.

      Delete
    2. Diethard, I am using checkbox which is multivalued parameter and I have groups in report, based on country! My problem is that in group header (which repeats for each new group/country) I want a country name to be written. I tried to use multivaluequery in formula but what result is all checked countries together, seperated with comma in every group header. I need them to be shown one by one!

      Delete
    3. Ok. As long as you put the country field in your group header that should just work then.

      Delete
    4. It seems like the solution was much more easier than I expected it to be! :) This works fine! Thank you for your help, appreciate it!

      Delete
    5. Hi Alen, excuse me a question, I put a checkbox parameter but when wanting to select multiple values will not let me, I can only select one at a time. How could you make a chekbox which to select some values?

      Delete
  2. Is it true, this only works in PRD 3.8 above? since i'm using PRD 3.7. I follow this example... and it's not working... :( none display... can anyone help for 3.7? or i did something wrong?

    ReplyDelete
    Replies
    1. If you find the SINGLEVALUEQUERY function in 3.7 then it should work, unless there is a bug. Does your report work in 3.8? If not, chances are that something went wrong with your report.

      Delete
  3. Hi Diethard, sorry for posting it due my desperate times... but now it's work fine..
    here's what i improve from your lesson , googling, and little luck...

    please allow me to use All options also for your PARAM_OFFICECODE query = offices :
    select 999 as OFFICECODE, '(ALL Offices)' as CITY from DUAL
    UNION ALL
    select OFFICECODE, CITY from T1

    additional_query :
    select NAME from (select 999 as OFFICECODE, '(ALL Offices)' as CITY from DUAL
    UNION ALL
    select OFFICECODE, CITY from T1) where OFFICECODE in ${param_officecode}

    Formula : not open formula, instead :
    Single Value Query: SingleValueQueryFunction0
    query name = additional_query
    Field name = [param_officecode]
    Result column = CITY

    calling the name = $(SingleValueQueryFunction0)
    and then it works fine in my 3.7

    ReplyDelete
    Replies
    1. Excellent. Good to hear that you got your report working!

      Delete
  4. Thanks, it works and it is very helpful :-.)

    ReplyDelete