RS2005+ADAM+User!Userid

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!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
<jordi_ramis@.hotmail.com> wrote in message
news:1164128212.934067.138640@.h54g2000cwb.googlegroups.com...
> 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!
>|||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
"Chris Conner" <Chris.Conner@.NOSPAMPolarisLibrary.com> wrote in message
news:%23%23IKvdaDHHA.1196@.TK2MSFTNGP02.phx.gbl...
> 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
> <jordi_ramis@.hotmail.com> wrote in message
> news:1164128212.934067.138640@.h54g2000cwb.googlegroups.com...
>> 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!
>|||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:
> 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
> "Chris Conner" <Chris.Conner@.NOSPAMPolarisLibrary.com> wrote in message
> news:%23%23IKvdaDHHA.1196@.TK2MSFTNGP02.phx.gbl...
> > 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
> >
> > <jordi_ramis@.hotmail.com> wrote in message
> > news:1164128212.934067.138640@.h54g2000cwb.googlegroups.com...
> >>
> >> 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!
> >>
> >
> >|||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:
> 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:
> > 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
> >
> > "Chris Conner" <Chris.Conner@.NOSPAMPolarisLibrary.com> wrote in message
> > news:%23%23IKvdaDHHA.1196@.TK2MSFTNGP02.phx.gbl...
> > > 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
> > >
> > > <jordi_ramis@.hotmail.com> wrote in message
> > > news:1164128212.934067.138640@.h54g2000cwb.googlegroups.com...
> > >>
> > >> 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!
> > >>
> > >
> > >

RS two seperate queries within a report

Hi all.
I was wondering if I have a report andI want to prompt the user to
choose say current inventory or all inventory...Can i do that within a
parameter? The problem I'm having is that if the user chooses current
inventory it runs a query but if the user chooses all inventory it
would have to run a seperate query. Is there any way to do this in RS?
Thanks in advance.
MissyModify you query a little bit will let the user select either all inventory
or a single inventory
Let's say you have 2 datasets:
Dataset Inventory
SELECT * FROM your_table where prod = @.prod OR @.prod = 'All'
and
Dataset Prod
SELECT 'ALL' as Prod UNION
SELECT Prod from your_table -- products list
while in Layout tab change Report Parameters so the Prod parameter will get
result from dataset Prod.
"aggiechick717" <mhoppe@.chkenergy.com> wrote in message
news:1159456164.936789.137990@.m7g2000cwm.googlegroups.com...
> Hi all.
> I was wondering if I have a report andI want to prompt the user to
> choose say current inventory or all inventory...Can i do that within a
> parameter? The problem I'm having is that if the user chooses current
> inventory it runs a query but if the user chooses all inventory it
> would have to run a seperate query. Is there any way to do this in RS?
>
> Thanks in advance.
> Missy
>