Apply Data Groups to Custom SQL Reports

When a report is created using custom SQL queries, the report does not automatically filter data as per the logged in user's Data Group.  Please be aware of the following:

• Reports created using FastTrack's standard reports will automatically have the Data Group filter applied.

• This requirement relates only to custom reports, and the data group filter can only be applied from the 11.37 release onward.

• This requirement utilises the Pass Parameter functionality also available from the 11.37 release onward.

To filter data by the logged in user's Data Group, see below for two sample queries below in bold underlined, where the candidates returned will be limited by the offices the user=fast has data group access to.  At the time of running the report the application will replace the @User variable with the logged in users ID and therefore limiting the candidates by office access:

SELECT * FROM fo.Candidates c

WHERE c.OfficeId in (SELECT Officeid FROM fo.vUserOfficeLookUp WHERE USERID = LoggedInUserID)

SELECT c.* FROM fo.Candidates C

INNER JOIN fo.vUserOfficeLookUp O ON C.OfficeId = O.OfficeId

WHERE O.UserId = LoggedInUserID

@user = 900000001

Please contact Professional Services if you require assistance to apply these queries to your custom SQL reports.