2

I need help for creating a report in SAP Hybris which essentially would be used for fetching out the names of "Employees" not "Customers" based on the "User Group" they're assigned. For example : X employee has usergroup : "admingroup". Like that I would need all the Employees with the respective usergroups. Could anyone please help me with a flexisearch query for the same to start with ? I would really appreciate that ! Thank you

I am still new to SAP Hybris and I have seen a lot of other reports but wanted to try this out.

minimalistic
  • 21
  • 1

2 Answers2

0

You can use the string_agg()-method to list up the user groups, it's mentioned here: How to concatenate multiple rows in flexibleSearch query in Hybris

If the method doesn't work you can try to replace it with group_concat.

You also need to join the Employee with the PrincipalGroup. You can access the employee's user groups with the attribute groups:

SELECT
{e:displayName},
string_agg({g:locName}, ', ') AS groupNames
FROM {Employee AS e}
JOIN {Employee.groups AS g} ON {g:pk} = {e:pk}
GROUP BY {e:pk}, {e:displayName}
Felix Schildmann
  • 574
  • 1
  • 7
  • 22
  • so the g:locnames would separate out the groups based on the geographic locations ? also flexisearch shows that the "Employee.groups" is invalid. Any idea on why that could be ? – minimalistic Jan 03 '23 at 06:09
  • If `Employee.groups` is invalid you need to join via the relation table. You can look up the name of the required relation in the *-items.xml. As far as I understood `locName` stands for the localized name of the user group – Felix Schildmann Jan 03 '23 at 11:12
0

for this report it require join with PrincipalGroupRelation as there is no direct relation with usergroup and employee.

select {user_group:uid},{user_group:locname[en]},{emp.uid},{emp.name} from {Employee as emp JOIN PrincipalGroupRelation as group_rel ON
{group_rel:source}={emp:pk} JOIN UserGroup as user_group ON {user_group:pk}={group_rel:target} } where {user_group.uid}='customersupportagentgroup'
Raushan Kumar
  • 1,195
  • 12
  • 21