Get concrete value in Pentaho Report Designer

Question

Let me explain my problem,
in Pentaho Report Designer I want to build such a report, where I have one data set, i.e. one request to database

SELECT code, name FROM EMPLOYEES

and show result of this request not in the form of list, but put every result in appropriate place. i.e. like in below picture
Where field "code" is unique, just one result can be put into red label in picture.

How can I do this in Pentaho Report Designer?

I can solve this problem by creating 3 data set, for each of caption. But what if captions like this will be much more, creating requests for each of this caption will not be so effective.

Hope I could explain my question.

Answer 1

I don't know if I got it right, but If you want to use the values returned by your query, you could create a new formula called "your_formula":

 =MULTIVALUEQUERY("name_of_your_query")

This will give you an array with the result of the query. Then you could use another formula to get the 1st result of the array. For example:

 =ARRAYLEFT([your_formula])

and get the first result of your query. This way let you use your_formula as parameter. This means you can drag and drop it on your report and use it.

I hope it helps.

Regards, Tatan.