1217632 - How to report off Active Directory in Crystal Reports?

How can you report off Active Directory when using Crystal Reports (CR) 8.5 and later versions?

This article contains three methods for reporting off data contained in Active Directory.

To report off Active Directory data using CR 8.5:

====================================

1. Create a new blank report.

2. Choose 'More Data Sources' > 'Active Data (ADO)' > 'Make New Connection'.

3. In the 'Select Data Source' dialog box select the 'ADO and OLE DB' option and then click 'Build'.

4. In the 'Data Link Properties' dialog box choose 'OLE DB Provider for Microsoft Directory Services' and then click 'Next'.

5. In the 'Data Source' box enter the full computer name of the server where the connection is being made.

6. In the 'Location' text box enter the UNC path to the directory where the Active Directory ntds.dit file is located.

7. When prompted to enter information to log on to the server, select the option 'Use Windows NT Integrated security' and click 'OK'.

8. Click 'OK' to revert to the 'Data Explorer' dialog box.

9. The new data connection will appear under the 'Active Data (ADO)' folder in the 'Data Explorer' dialog box.

10. Select the newly created data object and click 'Add' to add it to your report.

11. Click 'SQL' in the 'Select Recordset' dialog box.

12. In the blank text area of the 'Select Recordset' dialog box, enter a query.

Example (where the domain is sports.xtremebikes.com):

SELECT ADsPath, cn FROM 'LDAP://OU=ou,DC=sports,DC=xtremebikes,DC=com'

13. This query now appears as a data object under the 'ADO' folder.

14. Expand the data object to show the Active Directory fields available for your report.

To report off Active Directory data using CR 9, 10, XI and 2008:

========================================

You will use a 'Command Object' to query for Active Directory data.

1. For Crystal Reports 10 apply hot fix dbex10win_en.zip available on the Downloads area of our web site. For Crystal Reports 9 apply hot fix cr90dbexwin_EN.zip available on the Downloads area of our web site.

2. Ensure that the computer is joined to the domain being queried and that you are logged in under the domain account.

3. Create a new blank report.

4. Choose 'Create New Connection' > 'OLE DB'.

5. In the 'OLE DB (ADO)' dialog box, create a new connection using the "OLE DB Provider for Microsoft Directory Services". Clear all fields in the 'Connection Information' dialog box and click 'Finish'. If generated, ignore the error "Current provider does not support schema record sets" and proceed to step 6.

6. Double-click the "Add Command" object. Inside the Add Command dialog, enter the case sensitive LDAP query.

LDAP query examples are shown below:

Query Example 1

----------------

This query will return group information from Active Directory where the domain equals 'sports.xtremebikes.com'.

'LDAP://DC=sports,DC=xtremebikes, DC=COM' WHERE objectCategory='group'

Query Example 2

----------------

This query will return group information from an organization unit.

'LDAP://OU=ou,DC=sports,DC=xtremebikes,DC=com'

Query Example 3

----------------

This query will return data from Active Directory where the domain equals 'xtremebikes.com'.

'LDAP://DC=xtremebikes, DC=COM'

7. Save the command object.

8. Add the command object to your report.

9. Design and run your report based on the fields available in the command object.

To report Active Directory data from a CSV file:

===================================

It is possible to export Active Directory data to a comma-separated values (CSV) file by running a script from a batch file or at the command prompt. Re-run the script to update the data in the CSV. The fields available in the CSV contain more detail than Active Directory data obtained by other methods.

To create a CSV file choose one of the options below:

• Using the command Prompt - A CSV can be created by entering the following script at the command prompt or in DOS on your domain controller.

CSVDE -f c:\temp\csv_file_name.csv -r "(&(objectClass=user))"

• Using a Batch File - A CSV can be created by entering the following script in a text editor.

CSVDE -f c:\temp\csv_file_name.csv -r "(&(objectClass=user))"

Save the file as "filename.bat". Run the batch file by double-clicking it.

Crystal Reports can report off this CSV file.

For more information on exporting Active Directory information to a CSV file, please refer to the CSVDE and LDIFDE technical article.

AD CR HOW TO REPORT OFF ACTIVE DATA CRYSTAL REPORTS Crystal Reports AD Connection , c2016008 , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

Crystal Reports 2008 V0 ; SAP Crystal Reports 10.0 ; SAP Crystal Reports 8.0 ; SAP Crystal Reports 9.0 ; SAP Crystal Reports XI ; SAP Crystal Reports XI R2