AD Information Via Linked Server using Parameter

The main purpose of this document is how to pass parameters from RDL Report to Active Directory.

1.Create new linked server in “sql server management studio” and set appropriate connection to ActiveDirectory in linked server.
2.Create new Report Server project and add new rdl report to the project.
3.In rdl Report create new Dataset and select “Microsoft sql server” as DataSource and in connection string select sqlserver name in which linked server is created.
4.Here I am retrieving BankBranch,BankZone,BankRegion values based on username from ActiveDirectory .Here I am passing username as parameter.

DECLARE @variable1 VARCHAR(8000)
DECLARE @variable2 VARCHAR(8000)
DECLARE @variable3 VARCHAR(8000)
DECLARE @variable4 VARCHAR(8000)
DECLARE @variable5 VARCHAR(8000)
DECLARE @variable6 VARCHAR(8000)
DECLARE @str VARCHAR(max)

SET @variable1 = 'Select * from openquery(ADSI,'
SET @variable2 = '''select Branch,Region,Zone from '
SET @variable3 = '''''LDAP://xxx.xxx.xxx.xxx'''''
SET @variable4 = 'where samaccountname='
SET @variable5 = '''''' + @username + ''''''
SET @variable6 = '''' + ')'
SET @str=@variable1+@variable2+@variable3+@variable4+@variable5+@variable6
EXEC(@str)

i) In the above query we pass linked server which we have created, inside openquery.

Note:
The difference between normally passing parameters and above method is ,here we are passing the select statement as string where @username has been concatinated into the string.

5. Under Report tab select ReportParameters and create new parameter “username” and declare as a string.
6.Run the above query then it prompts for username value.
7. After providing the username value the query will retrieve the attribute values from activedirectory.