Join Framework table data to Application table data using SQL

Yes, this is possible through a Data Adapter. We have some People Planning reports that pull register data from XFW_PLP_Register for users based on their security groups - if the current logged in user is in a specific security group (Framework database) to access departments in a PLP workflow profile (Application database), then the report returns data that user has access to from XFW_PLP_Register.

Example - the following SQL in a Data Adapter (with Command Type = SQL and Database Location = Application) will pull all data in the XFW_PLP_Register application table if the user is assigned the Administrators security role in the Framework security tables:

Select * from XFW_PLP_Register a
WHERE
(’|Username|’ IN (Select SUA.Name
From OneStream_Framework.dbo.SecGroup SGA With (NOLOCK)
INNER Join OneStream_Framework.dbo.SecGroupChild SGCA With (NOLOCK) On SGA.UniqueID = SGCA.GroupKey
LEFT OUTER Join OneStream_Framework.dbo.SecUser SUA With (NOLOCK) On SGCA.ChildKey = SUA.UniqueID
WHERE SGA.Name = ‘Administrators’))