February 2007 - Posts

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
Posted by Shane | 16 comment(s)
Filed under:

If you have upgraded your SharePoint Portal Server 2003 environment to MOSS 2007 you may have found a nuisance. Whenever you are in a publishing site (which replaced areas from 2003) and you click site actions > create site your only choice is Publishing site. Now if you have installed MOSS 2007 fresh you will find that when you click create site you have the option for any type of site.

Why? The reason for this behavior is the upgrade. Everything that you called an area or sub-area in v2 will be upgraded to a Publishing site in v3. And if you remember all the way back to SPS 2003 (you know 2 months ago) then you will recall that when you were in a portal area the only option you had was to create a sub area. So when the upgrade took place the behavior got brought over. Luckily now in MOSS we are not locked into such a regimented policy.

How do I fix it? So you want it to behave as it did in 2003? You came to the right place. To fix the problem:

  1. Go to the home of your portal.
  2. Click Site Actions > Site Settings > Modify all site settings.
  3. Under Look and Feel click Page layouts and site templates
  4. You will be taken to this screen:

  1. Now choose Subsites can use any site template
  2. Check the box for Reset all subsites to inherit these preferred subsite template settings
  3. Click OK

Now you are in business.

Shane SharePoint Help

Posted by Shane | 6 comment(s)
Filed under: ,