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’


RecordID

DocID

UserID

NTName

PreferredName

Email

SID

Manager

LastUpdate

bDeleted

10

11

ABC\syoung

Shane Young

shane@nospam.sharepoint911.local

ABC\jcate

4/24/05 15:45

0


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


RecordID

PropertyID

PropertyVal

Image

Text

MultiValTypeID

MultiValID

10

1

…userid

NULL

NULL

NULL

NULL

10

2

…SID

NULL

NULL

NULL

NULL

10

3

ABC\syoung

NULL

NULL

NULL

NULL

10

4

Shane

NULL

NULL

NULL

NULL

10

5

Young

NULL

NULL

NULL

NULL

10

6

ABC\jcate

NULL

NULL

NULL

NULL

10

7

Shane Young

NULL

NULL

NULL

NULL

10

8

513-236-6918

NULL

NULL

NULL

NULL

10

9

shane@nospam.sharepoint911.com

NULL

NULL

NULL

NULL

10

11

Office

NULL

NULL

NULL

NULL

10

13

Architect

NULL

NULL

NULL

NULL

10

14

Infrastructure

NULL

NULL

NULL

NULL

10

17

Syoung

NULL

NULL

NULL

NULL


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.


Name

Email

Phone #

Department

Shane Young

shane@nospam.sharepoint911.local

513-236-6918

Infrastructure


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

kick it on SharePointKicks.com

15 thoughts on “Using the dataview web part and the profile database in SPS 2003 to create a company directory.”

  1. I did this on my SPS03 and I can view it when it is open in FrontPage but when you go to the web page on Sharepoint is give this error “An Error occorred while processing the Data for this request. Contact the Server Administrator for more info”. Can anyone help me on this.. I had a look at just about everything..
    Thanks
    Chris

  2. Why are you writing this for SPS 2003 and not commenting upon how to modify this as necessary for WSS 3.0 and MOSS 2007??

    At least your ideas and thoughts would be helpful. For example, you could write about why and where you’re having trouble, then people could help out, since this is such a useful idea.

    SharePoint newbies like me will find this article frustrating and tantalizing. :)

    Thank you, Tom

    Thank you, Tom

  3. Cool deal! Thanks for this, but of course I can’t be completely satisfied. I also want to include a link to each users personal site or “My Site”. I added the user name to the query (id 17) and I know all I need to do is add “http://server/personal/” in front of each username to get a link to the personal site. But I don’t know how to do that. Any ideas on how I can get that link working? Thanks again!

  4. Works fine with 2007 too!

    Just use the table “UserProfile_Full” in the “SharedServicesX_DB” Database instead of “OurPortal_prof”.

  5. To add additional features such as mobile numbers, ipphones etc in MOSS 2007, enter the Shared Services Adminisration page, find User Profiles and Properties, click on View Profile Properties. Map out the relavant fields in here to point to the correct field, then review the data query in the designer.

    Dan Stewart
    PS Thanks for the initial tutorial, great work and i’ve been looking for somthing to do this for ages!!

  6. Awesome work, Most Microsoft SharePoint consultancy companies will only employ individuals who have passed a set of stringent examinations, which means they are well qualified and experienced in the very latest developments. Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>