The SharePoint Farmer's Almanac

Using the dataview web part and the profile database in SPS 2003 to create a company directory.

My apologies to those of you that have been waiting MONTHS for this. I wrote this a long time ago but could never get the free time to actually polish and publish it. Well here it is now. Enjoy. Thanks goes to Jeff Cate of SharePoint Solutions who had the original idea for this article and helped me work out the details early on. Ok, on to the info.

Have you ever wondered how you could easily display a company directory within your SharePoint 2003 portal? Let's say we want to display name, email address, department, and phone number. You know that you are already importing that information to the database from active directory when you do a profile import. So where is it you ask? In the _PROF database of course. Now you could create an aspx page with some tables, some looping, and a couple of sql queries and get a real nice view of the database. Then you could use the page viewer web part to display that aspx file. But you are using SharePoint and to tell the truth you aren't really a very good programmer so let's look for a simpler method. How about using FrontPage 2003 and the dataviewer webpart?

The Tables

First we'll look at the 2 tables in the _PROF database that hold the profile information. The userprofile table is just a quick summary for those times when all you need is name, email, and manager. While this doesn't really provide the detail we are looking for it will make a good example. Because it has so little information (only 10 columns) it is quick and easy to read if you are not familiar with databases and it is very easy to query because each users data is in one row. No complex queries are needed here to get the data we want. Let's do a simple query on the table for user 10 and see what kind of information it has about him.

select * from UserProfile where recordid = '10'

• The UserID and SID values have been changed to … for readability.

This is the table that SharePoint uses for most of its information because it has all the info that SharePoint needs to handle users. If you went to SPS site administration -> Manage User Profiles -> View User Profiles you will see that the 3 attributes displayed there all come from this table.

That table was nice but not terribly useful because we need phone numbers and departments also. So let's move on to the non normalized table userprofilevalue. This table holds all of the other user profile information. Let's try the same query on this table and see what we get.

select * from UserProfileValue where recordid = '10'

Now we can see that from the default SharePoint profile import it captured SID (2), Account Name(3), First Name (4), Last Name (5), Manager(6), Display Name(7), Telephone Number(8), Email Address(9), Office(11), Title(12), Department(14), and User Name(17) from Active Directory. If you look at the DataServicePropMap table you can get a cross reference from the PropertyID to the corresponding property name in active directory.

The Query

So, now we have found the information (Display Name, Email Address, Telephone, and Department) that we need to display. The next step is to figure out how to display it. The big problem is that we want our table to be easy to read. We want to see the view below not the pile of data we saw above.

For our small organization we are going to keep it simple and just join the table on itself a few times and then display the results. If we were going to provide this functionality for a company with anything more than 100 users we would recommend creating a stored procedure to create a new table on a nightly basis organized as above and then just using a simple select * query in your dataviewer web part.

So back to our small company with 50 users. Let's look at the query we will be using.

use OurPortal_prof;

SELECT au1.propertyval AS 'Name' , au2.propertyval AS 'Phone_Number', au3.propertyval AS 'Email_Address', au4.propertyval AS 'Department'

FROM userprofilevalue au1

INNER JOIN userprofilevalue au2 ON au1.recordid = au2.recordid

INNER JOIN userprofilevalue au3 ON au1.recordid = au3.recordid

INNER JOIN userprofilevalue au4 ON au1.recordid = au4.recordid

WHERE au1.propertyid = '7'

AND au2.propertyid = '8'

AND au3.propertyid = '9'

AND au4.propertyid = '14';

Now , this query will only return users who have all 4 of the items we are looking for. Any users without a phone number in active directory will not be returned for example. Something to consider if you see unexpected results after we plug this query into our web part.

The Dataviewer Web Part

We start off by finding the page within our portal that we want to add our directory to. We then open that page with Front Page.

Once the page is fully loaded within SharePoint we then choose data from the menu bar and click on "insert data view…".

On the right side of the page we then see the Data Source Catalog. We expand the tree under "Database Connections" and click Add to Catalog

Now we click Configure Database Connection…

Now we enter the database server name and tell it to use Windows authentication

Check use custom query and click the Edit button.

Now we insert our entire query. Making sure to change the first line to reflect the name of your portal_prof database. Click OK.

Now click Finish.

Click OK.

Now under database connections we have custom query. Insert that web part on your page.

If when you drag the part on the page you get this error message below then you can follow the instructions at http://www.microsoft.com/resources/documentation/wss/2/all/adminguide/en-us/stse16.mspx to "enable update query support".

Now that you have added the part to the page you should see this.

Save the page and you are done!

You can now feel free to do any type of customization to the web part (such as target audiences) that you do with other web parts.

What I am really hoping for is this inspires someone to do the same thing in 2007. I am really struggling with using the new dataview/form web part in SharePoint Designer.

Shane Young – SharePoint Help