sql server reporting services: RS2005+ADAM+User!Userid -- sql server reporting services

You're in the

sql server reporting services

group:

RS2005+ADAM+User!Userid

jordi_ramis NO[at]SPAM hotmail.com
11/21/2006 8:56:53 AM

sql server reporting services:

Hi,

I'm trying to obtain the value of some attributes of the current user,
this is made using an OLEDB connection to an ADAM server. Then I use a
query like this:

Select RS-SGRE-OFFICE,RS-SGRE-CUSTOMER from 'LDAP://myserver/OU=Users
ESP,DC=ibsesp,DC=loc' where userPrincipalName='jordi.ramis@domain.loc'
and objectclass='user'

There is not problem with this, RS succesfully gets the corrent
information wich can be saved in a report parameter to filter my main
query.

The problem is that 'jordi.ramis@domain.loc' must be obiously be
replaced with the current user, I've tried to simply use a report
parameter that is assigned to User!Userid, that's the error:

"The data extension OLE DB does not support named parameters, use
unnammed parameters instead"

I've tried to use something like an ? for a variable but I'm unable to
getting it to work. Can someone explain how to use report variables on
OLEDB to query an ADAM directory? Is the dynamic sql the solution? How
can I do this?

Thanks to all!

Re: RS2005+ADAM+User!Userid

Chris Conner
11/21/2006 2:58:34 PM

It was my understanding that User!UserID returns the format: DOMAIN\User
when you want the user@domain format - I don't see how this would even
work - have you tried to see if it returns you a record if you change your
select to this:

Select RS-SGRE-OFFICE,RS-SGRE-CUSTOMER from 'LDAP://myserver/OU=Users
ESP,DC=ibsesp,DC=loc' where userPrincipalName='domain.loc\jordi.ramis'
and objectclass='user'

??

=-Chris

[quoted text, click to view]

Re: RS2005+ADAM+User!Userid

Kaisa M. Lindahl Lervik
11/21/2006 9:58:10 PM

This is about the silly Pre-Windows 2000 name, a.k.a. sAMAccountName in
Active Directory.

User!UserID gives you domain\username
Your query wants to have the userPrincipalName, which looks like
username@domain
sAMAccountName looks like domain\username

You could try to change your where clause to look for the sAMAccountName,
which looks like domainUsername. Try something like this
Select RS-SGRE-OFFICE,RS-SGRE-CUSTOMER from 'LDAP://myserver/OU=Users
ESP,DC=ibsesp,DC=loc'

where sAMAccountName='domain\jorramis'
and objectclass='user'

Scropp down to the tech section at this page to see a list of AD attributes:
http://www.minasi.com/newsletters/nws0503.htm

Kaisa M. Lindahl Lervik

[quoted text, click to view]

Re: RS2005+ADAM+User!Userid

jordi_ramis NO[at]SPAM hotmail.com
11/22/2006 12:16:40 AM

Hi,

Thanks for all the replies, they will be sure useful but the real
problem is that I'm unable to put any parameter to the report. I can't
make:

Select RS-SGRE-OFFICE,RS-SGRE-CUSTOMER from 'LDAP://myserver/OU=Users
ESP,DC=ibsesp,DC=loc'
where sAMAccountName=@my_parameter
and objectclass='user'

due to restrictions in OLEDB extension. Has anyone been able to
acomplish this?. Thanks a lot.

Kaisa M. Lindahl Lervik ha escrito:

[quoted text, click to view]

Re: RS2005+ADAM+User!Userid

jordi_ramis NO[at]SPAM hotmail.com
11/22/2006 1:47:39 AM

Hi,

I finally found the solution so I'm answering myself.

You have to write the query dinamically:

="Select RS-SGRE-OFFICE,RS-SGRE-CUSTOMER
from 'LDAP://myserver/OU=Users ESP,DC=ibsesp,DC=loc'
where sAMAccountName='"+User!UserId+"' and objectclass='user'"

I never though that I could write the query dynamically in RS2005,
that's fantastic and solves many of my problems.

Thanks to all.

jordi_ramis@hotmail.com ha escrito:

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.

home · blog · groups

Questions? Comments? Contact the dn