Filter Employees Datasource (Users Datasource) by Role

You may want to start with the filter webpart if using portal engine. Not sure your exact requirements, but that could be a good place to begin. What is your employees table? Is it a page type, custom table, module class, etc? This may help figure out what your options are.

I should have clarified that I am using the Intranet Portal - Employees template. This is not our own Employees database. I know we need to filter but I don't know what to filter it on. There is no detail in the online documentation for the Users database.

Hi Kim, not sure if the requirement is to have a selector based filter or just static condition based on some context value.

Here is how you can create custom filter if you need a selector based one - https://docs.kentico.com/display/K9/Developing+custom+filters

If you need just static condition based on context, you just add it to data source where condition

The relation in database is

• CMS_User (Employees) - CMS_UserRole (Role membership) - CMS_Role

So your where condition might look like:

UserID IN (SELECT UserID FROM CMS_UserRole WHERE RoleID IN (SELECT RoleID FROM CMS_Role WHERE RoleName = 'SilverPartners' AND SiteID = (SELECT SiteID FROM CMS_Site WHERE SiteName = 'CorporateSite')))

HI Martin,

So to confirm what you posted, I place the code you have posted in the Where condition of my web part and would change the RoleName to the desired Role name that I wanted it filtered on. Your example would render only the SilverPartners role.

So, Kim, you would need to develop a webpart maybe with a simple dropdown list that brings in the roles so that you could filter based on that. Martin is showing you the where clause that you would need to generate, and you would fill in the blank with your controls value. The custom filter is very easy to create, I would suggest cloning the base kentico filter webpart and customizing that. Then all you need to do is change the textbox to a dropdown or whatever control you want, and filter based on that control. Add that to your controls where clause for the filter and then add the filtercode name to your datasource, so that they datasource uses that filter.

Where do I find the structure of the User list so I know what to filter on? This is the Kentico User list.