June 24, 2011 at 10:56 am
Hello,
I'm attempting to use the IS_MEMBER internal function to drive values for a drop down parameter list. If the person who is running the report is a member of the ADMINS or the MGRS group, then the first SELECT statement would run and populate the drop down. If not, then the second SELECT would run and populate. Below is the query I'm using:
IF(IS_MEMBER(DOMAIN\ADMINS) = 1 OR IS_MEMBER(DOMAIN\MGRS) = 1)
BEGIN
SELECT loc FROM LOCATION ORDER BY 1
END
ELSE
BEGIN
SELECT office FROM rpt_loc ORDER BY 1
END
When I run this in Query Analyzer and in SSRS Preview, it runs fine (but that's because I'm a member of the Admins group). After deploying the report, when I run the report the parameter drop down is empty (which makes me think that it's not evaluating the IS_MEMBER properly).
I tested this by hardcoding two values (SELECT 'BOSTON' as the first, and SELECT 'NEW YORK' as the second like so:
IF(IS_MEMBER(DOMAIN\ADMINS) = 1 OR IS_MEMBER(DOMAIN\MGRS) = 1)
BEGIN
SELECT 'BOSTON'
END
ELSE
BEGIN
SELECT 'NEW YORK'
END
After deploying this, I received 'NEW YORK' as the only value in the dropdown, which I think confirms my thought on the IS_MEMBER.
Has anyone ran into this issue, and found a work around (or another method) to determine a parameter drop down list based on membership of an Active Directory group?
Thanks,
Jamey
June 24, 2011 at 2:59 pm
I would put your query in SSMS and then EXECUTE AS the user instance under which the Report Manager runs. You will probably get the same problem. Then work with your system folks to get the Report Manager user instance the proper permissions to query AD and continue to test in SSMS with the EXECUTE AS until it works.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply