Active Directory Reports

I'm not sure this is an actual reporting services question but has someone else created reports for active directory in Reporting Services

I want to create a report with users and their respective manager. I have this working in reporting services but I just want the manager name how could I strip out all the other information in the manager field?

SELECT personalTitle, manager, name, employeeid, distinguishedName
FROM 'LDAP://dc=xxx,dc=xxx,dc=xxxt'
WHERE objectClass = 'user' AND objectCategory = 'Person'
ORDER BY name

I get this as the manager name, I just want his name

CN=Smith\, Kurt,OU=Financial,OU=DataControl,OU=Users-Groups,DC=xxx,DC=xxx,DC=xxx

Thank you,

There should an AD property that gives you the name as entered. As a last resort, you can call a custom function to parse the name, e.g.

= Code.MyFunction(Field!name.Value)

|||

Hi thank you, I had tried a function in the bids tool by calculating a new member using this

=Mid(Fields!manager.Value,4,(InStr(Fields!manager.Value,",OU=")-4)) and this works fine on my desk top but when I attemtp to deploy it I get an error

Warning 1 [rsRuntimeErrorInExpression] The Value expression for the field ‘ManagerName’ contains an error: Argument 'Length' must be greater or equal to zero. C:\mydocs\SQL Server Management Studio\Projects\Active Directory\Active Directory\Active Dir Manager Report.rdl 0 0
Managername is my new calculated column

|||Could you prepare and send a sample report that demonstrates the issue? You can hardcode the dataset if you wish, e.g. using SELECT...UNION.|||

Thank you for the response. It was a data issue with the manager, some of my manager names were blank I had to search for them and replace with values prior to using the other functions: in case your interested first I use IIF

I put in the same values as I plan to strip off with the next calculated member

new field LookforNoManager

=IIF(Fields!manager.Value Is Nothing,"CN=Not Available,OU=",Fields!manager.Value)

then I created this new ManagerName

=Mid(Fields!LookForNoManager.Value,4,(InStr(Fields!LookForNoManager.Value,",OU=")-4))

one way around it I guess.

Now after I upload them I can't connect to the either the OLE DB active directory datasource directly or my linkserver ADSI datasource from the webserver? is there any trick to this definition

Here is my connection string for the linkedserver

Provider=ADsDSOObject;Data Source=lifeeai602\devsqlservices

here is my connecting sting when I tried to define the datasource directly to active directory

Provider=ADsDSOObject

Do I need to change something to point to LDAP?

|||Not sure how you connect. If it works in VS.NET but it doesn't on the server it is probably security. What credentials do you specify on the data source and what provider do you use?|||

Hi,

I use windows integrated security in the development tool and I set that up on the server to access the datasource as well, here is part of the error from the RS logfile

aspnet_wp!processing!5!3/30/2007-14:25:08:: e ERROR: An exception has occurred in data source 'NewDataSource'. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'NewDataSource'. > System.Data.OleDb.OleDbException: 'ADsDSOObject' failed with no error message available, result code: DB_E_NOTABLE(0x80040E37).
aspnet_wp!processing!5!3/30/2007-14:25:08:: i INFO: Merge abort handler called for ID=-1. Aborting data sources ...
aspnet_wp!dataextension!5!3/30/2007-14:25:08:: w WARN: CommandWrapper.Cancel not called, connection is not valid
aspnet_wp!processing!5!3/30/2007-14:25:08:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing., ;
Info: Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. > Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'NewDataSource'. > System.Data.OleDb.OleDbException: 'ADsDSOObject' failed with no error message available, result code: DB_E_NOTABLE(0x80040E37).
End of inner exception stack trace
aspnet_wp!processing!5!3/30/2007-14:25:08:: w WARN: Data source 'NewDataSource': Report processing has been aborted.
aspnet_wp!processing!5!3/30/2007-14:25:08:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing., ;
Info: Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. > Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'NewDataSource'. > System.Data.OleDb.OleDbException: 'ADsDSOObject' failed with no error message available, result code: DB_E_NOTABLE(0x80040E37).
End of inner exception stack trace

***************************************************************************************

Connection type:

OLE DB

Connection string:Provider=ADsDSOObject;Data Source=lifeeai602\devsqlservices

Windows integrated security