Reporting: how to select a specific entity from related one-to-many as a datasource

this question might apply to other areas, but I encounter it in the scope of reporting

I have a datamodel that could like:
Restaurant 1 - * Employee - Person

The Employee is a link between restaurant and person and has (enum) a role attribute. Enum has values (cook, waiter, owner, …)

Image there is only a single owner for each restaurant. So functionally there’s a one-to-one. How do I get hold of this owner so it can be a entity datasource for reporting.

I tried/thought of multiple ways to do it, but none to work.

  • transient attribute on restaurant -> datamodeller does not allow transient one-to-one
  • expression -> dont see any support for this?
  • jpql -> jpql dataset is based on attributes, not on entities
  • some kind of preprocessing?

I don’t want the user to have to select that entity, the information is already there.

Anu suggestions?

Hi Tom,

I understand your data model, but I don’t quite understand what you want to achieve.

To select a restaurant owner, you can execute the following query:
select e from sample$Employee e where e.role = :role and = :restId.
You can put this entity to a datasource and, for example, bind to UI components.

Hi Konstantin,
let me clarify.

My question is regarding the reporting plugin.
I want to make a report about the restaurant. Let’s say there’s a one-to-many to the dishes that are served and i want to generate a menu.

So i make a new report with a report band based on the restaurant entity (entity dataset).
I can make a second band Collection-of-entities by selecting the dishes restaraunt#dishes (collection of entities dataset)

Now i want to add some other information that linked to the owner of the restaurant.
I don’t want to list all the employees, I want a specific employee with that role.

I don’t see how to achieve this in the scope of reporting. I could add another input parameter for that, but I don’t want to add it, the link is already there.

jpql dataset queries return (according to the documentation) attributes, not entities.

basically, given the restaurant as an input parameter i want to get to the owner entity as a entity dataset.

hope this clarifies things.

Hi Tom,

JPQL datasets can return entity and you can access entity attributes by BandName.fieldName
I’ve created a sample report that shows Admin role for selected user. Report executes JPQL for User parameter and selects role entity.

select r from sec$Role r, sec$UserRole ur 
where = 
and = ${entity} and = 'Administrators' 

Report: Report for entity (8.5 KB)

Thank you,