Business Need:

 We need a page that shows everyone's name, email address, cell phone (work), and work phone.

 Solution:

Well, the first "knee jerk" reaction is just to create a page, put a custom user list on the page, and fill in the information.  But that kinda defeats some of what SharePoint is best at...empowering users.  I don't want everyone updating the list, just viewing it.  I would really like to not have an administrative assistant doing the work either.  Besides, the information is already in everyone's "mySite" area.  A separate list is just a duplication of information...and a high probability that both pieces of information will not be changed at the same time.

Unfortunately, SharePoint does not have a easy to use web part that get everyone's mySite information (as far as I know).  This means that there are 2 options.  First, buy or use a free third party web part.  Second, develop something on my own.  Since I REALLY don't want to go back to my boss about another SharePoint purchase...Option 2 is my direction.

Since I'm developing my own solution, I have a couple more options.  First, I can use the SharePoint Object Model to get the information (Microsoft recommended) or I can go directly to the database (not recommended - see previous blogs).  My past experience has me not inclined to take the Microsoft recommended path of using the SharePoint Object Model.  Without all the geek talk, in the end, it takes too long to get the data from all the web sites and display it to the user.  User impatience has always moved me away from the SharePoint Object Model method of gathering data across multiple sites.

So I'm back into the database.  My first look took me to the AllUserData table...a place that I have become comfortable with.  However, no luck...No "mySite" information.  It took a while, but I finally found the information I was looking for in a different database altogether.  The database name for me was "SharedServices1_DB_b023319b-89b0-4a54-adac-c9e06aa22502"...but yours will have a different name.  The last part of the name seems to be a GUID, so make the adjustment accordingly.  The first part of the name may be the same, depending on how you configured your server at initial installation.  I think the important part is the "DB" part.

Anyway, in this database, there are two tables that need to be utilized to get the information...UserProfile_Full and UserProfileValue.

UserProfileFull has the names of everyone who has a "mySite".  UserProfileValue has the information contained in everyone's "mySite".

The down and dirty...

Here is the query that i user to get the information:

SELECT     TOP (100) PERCENT RecordID, '<a href="http://reve/MySite/Person.aspx?accountname=' + NTName + '">' + PreferredName + '</a>' AS LinkName,
                          (SELECT     PropertyVal
                            FROM          dbo.UserProfileValue
                            WHERE      (RecordID = dbo.UserProfile_Full.RecordID) AND (PropertyID = 8)) AS [Work Phone],
                          (SELECT     PropertyVal
                            FROM          dbo.UserProfileValue AS UserProfileValue_2
                            WHERE      (RecordID = dbo.UserProfile_Full.RecordID) AND (PropertyID = 19)) AS [Cell Phone],
                          (SELECT     PropertyVal
                            FROM          dbo.UserProfileValue AS UserProfileValue_1
                            WHERE      (RecordID = dbo.UserProfile_Full.RecordID) AND (PropertyID = 9)) AS EMail, PreferredName
FROM         dbo.UserProfile_Full
WHERE     (RecordID <> 1)
ORDER BY CAST(PreferredName AS char(64))

You'll notice that I exclude RecordID number 1.  That is the local administrator account.  You may need to exclude different users from the return data.  Run the query and make the adjustments accordingly.

Also notice that there are three sub-queries.  These return the individual values.  The UserProfileValue table stores each user's field based on a PropertyID number.  Look at the UserProfileValue table and you'll start to see the trend.

Additionally, I return the RecordID from the UserProfile_Full table.  It is a key field and is needed when we start the BDC stuff later on. 

Finally notice that the first field I bring back is a "built" field.  I creating the HTML that is actually a link to the individual's profile page in SharePoint.  I'm displaying the user's PreferredName, and using the NTName as part of the actual link.  The PreferredName is also used to sort the list.

From here, I used BDCMetaMan and created the BDC Application Definition, uploaded the created Application Definition through the Central Administration panel, created the page, and added a BDC list web part to the page. On the BDC list web part, I selected the BDC application and clicked the Apply button.  This is necessary to have the XSL initially generated.

The XSL will need to be modified a bit.  First an foremost, save a copy of the original XSL.  If you mess up the XSL, the page may not display anything and you'll need to go back to the original stuff to get things working again.  Additionally, I highly recommend that anytime you past back your XSL to the web page, you save a copy of the XSL as an interim checkpoint.  Again, if you mess up the XSL and the page gets weird, it may be easier to go back to the last known working version rather than the original...I'll get off my soap box now. 

I open the default XSL editor box and copy/paste the information to a regular editor.  I prefer UltraEdit because I like the XML stuff it is capable of, but use any text editor that you normally use. You probably want to remove the referenced to column 1.  There is reference at the initialization section, a reference at the record detail section, and a reference at the table header section.  You'll just have to plow through this to find what I'm talking about...sorry.  Additionally, you'll probably want to change the number of records displayed from the default.  I normally just set it to 9999.  Finally, you'll want to mess with the sort stuff.  The generated XSL is set to display based on the RecordID field...which we removed.  We want the information displayed based on the PreferredName column.  The next time I mess with XSL, I'll make notes and post the steps in detail, but again, you'll just have to get into it and figure it out.  If your saving the original XSL and the interim versions, don't be afraid to experiment and find out what works.

Once the page is working, Publish the page, set the security and enjoy