Pass SharePoint User Info To Reporting Services

[ccshadow]

Is it possible to pass SharePoint user info from an intranet into Reporting Services?

I'm creating a report with sales data. The report needs to be filtered based upon the user accessing the report. For example, if the user is a Director, she would be able to view all sales for all departments and locations. If the user is a Manager, they could view all sales for their department and location. If the user is sales agent, they could only see their sales.

Retreiving this info via a typical web application is a piece of cake, but I haven't been able to find any guidance on accomplishing this in Reporting Services from a SharePoint site. There are too many possible combinations to create a customized report for each situation (it could easily be hundreds).

We haven't been able to get RS to operate in integrated mode with multiple servers, so right now I'm working with native mode and WSS 3.0. Ultimately I'd like to get the NTLogin, which is how we grant access to the SharePoint sites, and pass it into the stored procedure that populates the report. Any help or direction would be greatly appreciated?

FYI - I'm a complete SharePoint programming novice.

 

[ccshadow]

Never mind. I believe I stumbled across a solution. For anyone else that might be having the same or similar problem... In the Expressions dialog box is a Global variable UserId. I haven't worked with RS much so I didn't even know it was there, but it works on the report server and in native mode should still pass the id correctly.

 

[xlbo]

Just seen this - globals!userid is one way to go - you can then query active directory to get job role etc...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[VolandTX]

Hi.
I have similar problem.
I can get user login from the Globals!UserID, but how I can use it as parameter for the AD query?

Query like this works fine if I have userID hard coded in it:
SELECT sAMAccountName,givenName,mail,sn,title, telephoneNumber From
'LDAP://DC=MyCompany, DC=local' WHERE
objectClass='user' AND objectCategory='Person' and sAMAccountName='jBlanks'

But I can not find the way to use variable parameter in this query. Have you solved this problem?
Thanks

 

[xlbo]

where is the query ? in RS ?

Set up a parameter called UserID and populate it using the globals!userid.value

SELECT sAMAccountName,givenName,mail,sn,title, telephoneNumber From
'LDAP://DC=MyCompany, DC=local' WHERE
objectClass='user' AND objectCategory='Person' and sAMAccountName=@UserID


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[VolandTX]

Yes, query is in RS. And it's exactly what I am trying to do.
But when I replace 'jBlanks' with @UserID I am receiving an error:
The ICommandWithParameters intarface is not supported by the 'ADsDSOObject' provider. Command parameters are unsupported with the current provider.

Is it any way to work it around?

 

[xlbo]

Do it as an expression:

="SELECT sAMAccountName,givenName,mail,sn,title, telephoneNumber From
'LDAP://DC=MyCompany, DC=local' WHERE
objectClass='user' AND objectCategory='Person' and sAMAccountName='" & Parameters!USerID.Value & "'"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[VolandTX]

Thank you!
This shoud work.