Telephone directory of the organization - print version

I envy my English-speaking colleagues, they don’t have to reinvent the wheel, they’re all in the same layout, and nobody calls the other person by their first name, which is very important in our official letters.

Here is a simple task - you need an up-to-date telephone directory of the organization that can be printed out, which contains the full name in Russian and the displayed name in English (as in the postal address book) and has the main contact numbers. But rarely in any organization you will find a normal solution.

Everyone is familiar with the situation when an employee of the reception desk sends by mail a crookedly filled Word file at every personnel change or change by a cell phone employee? I think many smiled ...

I share the solution how to connect the Active Directory directory to SQL Reporting Server.

If you have order in AD, skip this step, for the rest I give an example of filling in user fields in AD.



Advantages of such a filling : you can search for a person in other reference books in Russian.
Cons : by the last name in Latin users will not be found.

Alternatively, you can use additional attributes of AD for example: middleName or Display-Name-Printable to store the full name or middle name in Russian.

Download and run Report Builder

Choose Matrix Wizard -> Create a dataset
New Connection Name - AD
Connection Type - OLE DB
In the Credentials tab, select the current user authority
Click the "Build" button
Ole DB Provider choose: OLE DB Provider for Microsoft Directory Services

In the server name we fill in any available domain controller, it should turn out like this:



Next, we make the connection string:

SELECT givenName, SN, telephoneNumber,department, displayName,title, mail, mobile FROM 'LDAP://OU=Users,OU=HOLDING,DC=domain,DC=kz'WHERE objectCategory='user' 

Change your LDAP path to the OU where your users are located

Test the connection:



Next, drag all the required fields into the “values” of the report:



Everything. Next, format the report as you like. I, for example, grouped the report by department and adjusted it to A4 size.

You can sort and filter data in the query by adding the operator: ORDER BY column1, column2, ... ASC | DESC; or in the Tablix property



It remains only to place the report on the Reporting Server and publish it, adding to the end of the URL
keys? rs: embed = true

Here is my report:



Download it here.

The advantages of this solution:

The reference book can be placed on the SQL Reporting Server, it is now completely free, it can be downloaded separately with the Express version , or you can place the rdl report on any aspx village on the intranet.

Additionally, you get information about AD users and the correctness of filling departments. Everything is relevant and beautiful, the main thing is to move the dismissed users in time to any other OU.

By the way, the user's department number is specified for the correct building of the hierarchy, if you need strict sorting by posts, you can use any additional attribute AD.

Fields can be dynamically combined, for example, “First Name” and “Last Name” in the “Full Name” field, and quickly customized the reference book for corporate colors.

Best regards, Sergei