locked
Active Directory Reports RRS feed

  • Question

  • 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,

    Thursday, March 29, 2007 5:30 PM

All replies

  • 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)

    Thursday, March 29, 2007 8:45 PM
  • 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

    Friday, March 30, 2007 6:32 PM
  • 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.
    Friday, March 30, 2007 8:04 PM
  • 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?

    Friday, March 30, 2007 9:29 PM
  • 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?
    Saturday, March 31, 2007 1:05 AM
  • 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

     

    Monday, April 2, 2007 5:52 PM