travislowdermilk.com

I should call this post “Part 2″ because I’ve covered a similar topic in the past.  However, this is a bit different.

More than likely, you will find yourself in a situation where you need some SharePoint data that is available on another site in the collection.

The Content Query Web Part (CQWP) allows you to do this, however, it can be somewhat limited.

In this example, I want to display appointments from a calendar list that is located on a parent site.

1. Select Site Actions > Edit Page
2. Select “Add a Web Part” within one of the available zones
3. Select the “Content Query Web Part” from the “Default” group
4. Select the edit button and choose “Modify Shared Web Part”
5. Under the Query properties, specify your conditions (in this case I’m going to select a calendar from the parent site)
6. You may want to set other properties such as the Title, Title Url, etc.
7. Click OK

Hey! There's only one column here!

You can see that this won’t quite work.  Sure the CQWP is displaying appointment from the calendar, however, there is no useful “at-a-glance” information.  We need to add information to the web part like what time the event starts, where the location of the event is, etc.

8. Select the edit menu again from the web part and choose “Export…”
9. Save the .webpart file to your computer.
10. Open the .webpart file in SharePoint Designer.

We need to add a couple of columns to be used in the web part. Look for the property “CommonViewFields” and change it to include some additional columns:

<property name="CommonViewFields" type="string">EventDate,DateTime;Title,Text;EndDate,DateTime;Location,Text</property>

In order to take advantage of these columns, we need to be able to modify the ItemStyle.xsl. In my case, the site collection was not a Publishing Site, so I couldn’t find the ItemStyle.xsl! No problem. Just modify the “ItemXslLink” property to include the path of a custom .xsl file (just make sure you create it):

<property name="ItemXslLink" type="string">/CustomStyles.xsl</property>

In this case, I made an XSL stylesheet named “CustomStyles” that I put in the root of the site collection.

Here’s the code I used in my stylesheet:

<xsl:stylesheet
  version="1.0"
  exclude-result-prefixes="x d xsl msxsl cmswrt"
  xmlns:x="http://www.w3.org/2001/XMLSchema"
  xmlns:d="http://schemas.microsoft.com/sharepoint/dsp"
  xmlns:cmswrt="http://schemas.microsoft.com/WebParts/v3/Publishing/runtime"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt"
  xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime">
  <xsl:param name="PageUrl" />
    <xsl:template name="Custom_CQWP_Calendar_Items" match="Row[@Style='Custom_CQWP_Calendar_Items']" mode="itemstyle">
        <xsl:variable name="DisplayTitle">
            <xsl:call-template name="OuterTemplate.GetTitle">
                <xsl:with-param name="Title" select="@Title"/>
            </xsl:call-template>
        </xsl:variable>
        <table style="width:100%;" cellpadding="0" cellspacing="0">
        <tr>
        	<td style="width:40%; padding:3px 0px 3px 0px; font-family:Tahoma; font-size:8pt;">
				<xsl:call-template name="OuterTemplate.CallPresenceStatusIconTemplate"/>
				<img src="/_layouts/images/square.gif" />&#xa0;&#xa0;
			    <a href="/cpoe/Lists/Calendar/DispForm.aspx?ID={@ID}&amp;source={$PageUrl}">
			    	<xsl:value-of select="$DisplayTitle"/>
				</a>
        	</td>
        	<td style="width:30%; padding:3px 0px 3px 0px; font-family:Tahoma; font-size:8pt; color:gray">
				Starts: <xsl:value-of select="ddwrt:FormatDate(string(@EventDate),1033,2)"/>
        	</td>
        	<td style="width:30%; padding:3px 0px 3px 0px; font-family:Tahoma; font-size:8pt; color:gray">
				Location: <xsl:value-of select="@Location" />
        	</td>
        </tr>
        </table>
    </xsl:template>
</xsl:stylesheet>

Notice that $PageUrl parameter? Yeah, I wish I could say I came up with that myself, however, I didn’t. The $PageUrl parameter makes sure we return back to wherever we were when we click on the Close button from the item display view.
Also, I’ve included the image for a bulleted list.
Here’s the finished product:

Now that's more like it!

Here are the remaining steps:

1. Go to the Web Part Gallery of the site collection and upload the .webpart file
2. Add the web part to your page
3. Select edit and “Modify Shared Web Part”
4. Under the “Presentation” group in the “Styles” heading select the available item style (the name of which is specified in your CustomStyles.xsl)

Another resource that was helpful was Brendon Schwartz’s blog

This is the final post in a series about creating a robust search U.I. for SharePoint.

We have successfully captured each search query submitted through our U.I. and stored them into a normalized schema in our database.

So the final question is: “How do we use this information to provide search suggestions to our users?”.

The first step starts with our queries.  Our schema has 3 tables:

Tag Table: This captures all the unique keywords a user might type into their search query e.g. “dress”, “code”, etc.
Context Table: Captures the date, time, and the user who performed the search.
Context_Tag: Combines keywords and context.

These three tables allow us create some nifty results.

For instance:

What are the top ten phrases? (Use this to make a tag cloud):

 DECLARE @tblContextTag TABLE(Row_Num int, ContextID int, Ordinal int, Tag nvarchar(255))
 DECLARE @tblContextPhrases TABLE (Phrase nvarchar(255))
 DECLARE @Tags int
 DECLARE @Tag n_varchar(255) --fix n_varchar to nvarchar [added due to issue with WordPress code plugin]
 DECLARE @Row_Num int
 DECLARE @ContextID int
 DECLARE @Ordinal int
 DECLARE @Phrase n_varchar(255) --fix n_varchar to nvarchar [added due to issue with WordPress code plugin]</pre>
INSERT INTO @tblContextTag (Row_Num, ContextID,  Ordinal, Tag)
 (SELECT ROW_NUMBER() OVER(ORDER BY ContextID, Ordinal) AS 'Row_Num', ContextID, Ordinal, Tag
 FROM Context_Tag A INNER JOIN
 Context ON A.ContextID = Context.ID INNER JOIN
 Tag ON A.TagID = Tag.ID
 GROUP BY ContextID, Ordinal, Tag, TagID)

 SET @Tags = 0
 SET @Row_Num = 1
 SET @ContextID = (SELECT TOP 1 ContextID FROM @tblContextTag WHERE Row_Num = @Row_Num ORDER BY ContextID)
 WHILE @Tags <= (SELECT COUNT(*) FROM Context) - 1
 BEGIN
 SET @Ordinal = 0
 SET @Phrase = ''
 WHILE @Row_Num <= (SELECT TOP 1 Row_Num FROM @tblContextTag WHERE ContextID = @ContextID ORDER BY Row_Num DESC)
 BEGIN
 SET @Tag = (SELECT TOP 1 Tag FROM @tblContextTag WHERE Row_Num = @Row_Num)
 SET @Phrase = @Phrase + ' ' + @Tag
 SET @Ordinal = @Ordinal + 1
 SET @Row_Num = @Row_Num + 1
 END
 INSERT INTO @tblContextPhrases (Phrase) VALUES (LTRIM(@Phrase))
 SET @ContextID = (SELECT ContextID FROM @tblContextTag WHERE Row_Num = @Row_Num)
 SET @Tags = @Tags + 1
 END

 SELECT TOP 10 Phrase, COUNT(Phrase) AS Weight
 FROM @tblContextPhrases
 GROUP BY Phrase
 ORDER BY COUNT(Phrase) DESC, Phrase

Or, how about:

What are the top 20 suggestions for <specified network login> based on what this user has search for in the past?  Plus I want to include what is popular among other users’ searches.  And please filter by <specified first letter of the query>.

-- Add the parameters for the stored procedure here
 @value n_varchar(1), --fix n_varchar to nvarchar [added due to issue with WordPress code plugin]
 @networkid n_varchar(10) --fix n_varchar to nvarchar [added due to issue with WordPress code plugin]
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 -- Insert statements for procedure here
 DECLARE @tblContextTag TABLE(Row_Num int, ContextID int, Ordinal int, Tag nvarchar(255))
 DECLARE @tblContextPhrases TABLE (Phrase nvarchar(255))
 DECLARE @tblContextTOPPhrases TABLE (Phrase nvarchar(255))
 DECLARE @Tags int
 DECLARE @Tag n_varchar(255) --fix n_varchar to nvarchar [added due to issue with WordPress code plugin]
 DECLARE @Row_Num int
 DECLARE @ContextID int
 DECLARE @Ordinal int
 DECLARE @Phrase n_varchar(255) --fix n_varchar to nvarchar [added due to issue with WordPress code plugin]

 INSERT INTO @tblContextTag (Row_Num, ContextID,  Ordinal, Tag)
 (SELECT ROW_NUMBER() OVER(ORDER BY ContextID DESC, Ordinal) AS 'Row_Num', ContextID, Ordinal, Tag
 FROM Context_Tag A INNER JOIN
 Context ON A.ContextID = Context.ID INNER JOIN
 Tag ON A.TagID = Tag.ID
 WHERE NetworkID = @networkid AND
 Date_Time >= DATEADD(mm, -6, GETDATE()) AND Date_Time <= GETDATE()
 GROUP BY ContextID, Ordinal, Tag, TagID)

 SET @Tags = 0
 SET @Row_Num = 1
 SET @ContextID = (SELECT TOP 1 ContextID FROM @tblContextTag WHERE Row_Num = @Row_Num ORDER BY ContextID)
 WHILE @Tags <= (SELECT COUNT(*) FROM Context WHERE NetworkID = @networkid AND Date_Time >= DATEADD(mm, -6, GETDATE()) AND Date_Time <= GETDATE()) - 1
 BEGIN
 SET @Ordinal = 0
 SET @Phrase = ''
 WHILE @Row_Num <= (SELECT TOP 1 Row_Num FROM @tblContextTag WHERE ContextID = @ContextID ORDER BY Row_Num DESC)
 BEGIN
 SET @Tag = (SELECT TOP 1 Tag FROM @tblContextTag WHERE Row_Num = @Row_Num)
 SET @Phrase = @Phrase + ' ' + @Tag
 SET @Ordinal = @Ordinal + 1
 SET @Row_Num = @Row_Num + 1
 END
 INSERT INTO @tblContextPhrases (Phrase) VALUES (LTRIM(@Phrase))
 SET @ContextID = (SELECT ContextID FROM @tblContextTag WHERE Row_Num = @Row_Num)
 SET @Tags = @Tags + 1
 END
 --Now get overall popular results
 SET @Tags = 0
 SET @Row_Num = 1
 SET @ContextID = 0
 SET @Tags = 0
 DELETE @tblContextTag

 INSERT INTO @tblContextTag (Row_Num, ContextID,  Ordinal, Tag)
 (SELECT ROW_NUMBER() OVER(ORDER BY ContextID, Ordinal) AS 'Row_Num', ContextID, Ordinal, Tag
 FROM Context_Tag A INNER JOIN
 Context ON A.ContextID = Context.ID INNER JOIN
 Tag ON A.TagID = Tag.ID
 WHERE Date_Time >= DATEADD(mm, -6, GETDATE()) AND Date_Time <= GETDATE()
 GROUP BY ContextID, Ordinal, Tag, TagID)

 SET @Tags = 0
 SET @Row_Num = 1
 SET @ContextID = (SELECT TOP 1 ContextID FROM @tblContextTag WHERE Row_Num = @Row_Num ORDER BY ContextID)
 WHILE @Tags <= (SELECT COUNT(*) FROM Context WHERE Date_Time >= DATEADD(mm, -6, GETDATE()) AND Date_Time <= GETDATE()) - 1
 BEGIN
 SET @Ordinal = 0
 SET @Phrase = ''
 WHILE @Row_Num <= (SELECT TOP 1 Row_Num FROM @tblContextTag WHERE ContextID = @ContextID ORDER BY Row_Num DESC)
 BEGIN
 SET @Tag = (SELECT TOP 1 Tag FROM @tblContextTag WHERE Row_Num = @Row_Num)
 SET @Phrase = @Phrase + ' ' + @Tag
 SET @Ordinal = @Ordinal + 1
 SET @Row_Num = @Row_Num + 1
 END
 INSERT INTO @tblContextTOPPhrases (Phrase) VALUES (LTRIM(@Phrase))
 SET @ContextID = (SELECT ContextID FROM @tblContextTag WHERE Row_Num = @Row_Num)
 SET @Tags = @Tags + 1
 END

 --Insert overall popular results
 INSERT INTO @tblContextPhrases (Phrase)
 SELECT TOP 20 Phrase FROM @tblContextTOPPhrases
 GROUP BY Phrase HAVING Count(Phrase) > 5
 ORDER BY COUNT(Phrase) DESC, Phrase

 SELECT DISTINCT Phrase AS value
 FROM @tblContextPhrases
 WHERE Phrase LIKE @value + '%'

END

The above stored procedure will give you the result set you need in order to inject meaningful suggestions into your U.I. using the jQuery autocomplete function.  Just call the stored procedure from the web service that is connected to your jquery plugin.  Here is some more info on how the jquery autocomplete code functions.  Also, if you’re wondering how you might get the user’s login from SharePoint to pass to the stored procedure, check this post out.

And the final result is a rich U.I. with personalized search suggestions. Right in SharePoint!

This is a continuation of a series about creating a robust search U.I. for SharePoint.

We’ve created our U.I. elements and embedded them into the master page. We’ve also implemented JQuery and connected our U.I. to a web service to populate our auto completion.

The next questions is: “How do I populate it the auto completion with suggestions?”

Well, before we can answer that, we have to figure out how we are going to store the search queries in the first place. The challenge, as you might well imagine, is that we can’t just store every string value a user might type into the search box.

For instance, if several users type in the keywords “dress code”, we don’t want to store that phrase, multiple times, into the database.

After some lengthy discussions with my brother and a couple of napkin drafts, we came up with a rather simple solution.

Rather than store the entire phrase “dress code”, couldn’t we just parse the words “dress” and “code” and store them into a table with an identifier?

So, instead of this:

We get this:

I decided to create a stored procedure that would collect a search phrase, parse it into “tags”, and INSERT them into the “Tag” table. If the record already existed, then an error would be caught and I would move on.

Note: I won’t go into detail about how I connected the stored procedure to the user control. Suffice to say, I created my connections and parameters in my web service. The U.I. passes the phrase to the web service when the user executes a search. The web service then makes a connection to the database, collects the phrase into a parameter, and calls the stored procedure. This is all standard .NET data stuff and there are a plethora of articles on this subject.

Here is the SQL code for my stored procedure “spStoreSearch”:

-- Add the parameters for the stored procedure here
	@TagList nvarchar(255),
	@NetworkID nvarchar(10)
	AS
	BEGIN
 --Please refer to attached code.

Note:For some reason, my blog article wont post with the SQL code in it. Here’s a link to the script.

You might have noticed, I’m also storing a value of “NetworkID” into a table called “Context” and values of “ContextID”, “Ordinal”, and “TagID” in a table called “Context_Tag”. As you might’ve imagined at this point, I cannot just store the individual tags into the database. They would lose their context. In other words, I would only be able to say: “Guess what guys!!! The word ‘dress’ is the most popular search word!!!! WOOO HOOO!!! YEAH!” They’d look at me like I had lost my marbles. The word “dress” could have so many different contexts: “dress code”, “dress policy”, “informal dress”, “dress code violation”…You get the idea.

So I have to be able to store the contextual information in a meaningful way.

Also, I would like to know who’s completing the search.

Thankfully, SharePoint provides that information by way of collecting your network credentials. I wrote a blog post about how you can go about getting that kind of user information from SharePoint. I used the same methodology to get my “NetworkID” here.

Combined, the information would get stored like this:

Example: At 0750 on 03/16/2010, user “tlowderm (a.k.a Travis Lowdermilk) searched the phrase “dress code”
Tag Table

Context Table

Context_Tag

As you can see, this is as normalized as it’s gonna get (unless you can suggest otherwise?). You’ll also notice the column “Ordinal” in the Context_Tag table.
This is used to reference the order a sequence of tags were entered.

For example, if I were to query the phrase “dress code”, the result would be “code dress” if I were to ORDER BY tag alone.

The next challenge is putting together queries that present meaningful search data. Answering questions like: “What’s the most popular search phrase?” and “What searches has Travis requested?”.

In the next post, SharePoint Search on Steroids Part 4 – Retrieving Search Queries, I’ll detail how to retrieve this kind of information and how to enrich the user search experience by
injecting suggestions based on popular search phrases.

This is a continuation of a series about creating a robust search U.I. for SharePoint.

Connecting the U.I. to Search Results

We now have our user control embedded on the master page of our site. First we should have the U.I. forwarding the user to the search results upon clicking “Search” or submitting a query.

This is simple enough. In reality, we are just redirecting them to the appropriate search results page and placing their query in a query string parameter:

//This would be for a general query
Response.Redirect("/SearchCenter/Pages/results.aspx?k=" + txtSearch.Text.Replace(" ", "%20"));
//This would be for a people search
Response.Redirect("/SearchCenter/Pages/peopleresults.aspx?k=" + txtSearch.Text.Replace(" ", "%20") + "&s=People");
//This would be for a custom scope
Response.Redirect("/SearchCenter/Pages/Results.aspx?k=" + txtSearch.Text.Replace(" ", "%20") + "&s=This isMyCustomScope");

You can create a U.I. that stores the selected search type into a session variable. I created tabs upon the top of the search. If the user clicks on “People”, for instance, I store a session variable of the value “people” and direct them to the appropriate page (peopleresults.aspx) upon the search click or search submit event:

Each tab stores a value in a session variable. The variable redirects the user to the appropriate search result page.

Enhancing search with JQuery and WebServices

We first need to integrate JQuery into our SharePoint site.  I imagine there are many ways to do this, however, this approach seemed the most straightforward to me:

1. Download the JQuery code.  It’s also available on the Microsoft CDN, but for an Intranet site, I prefer offering it from our own servers.

2. Open up SharePoint Designer and create a folder in the root called “_scripts” and place the jquery.js file there.

3. Open up your masterpage in SharePoint Designer and in the <head> section add:

<script type="text/javascript" src="/_scripts/jquery-1.3.2.js"></script>

4. You’ll also want to download the autocomplete plugin from JQuery.  This plugin allows us to present suggestions (very much like Google) as the user begins to type their query.

5. Follow steps 2 and 3 to add your autocomplete.js file

For brevity, I won’t go  into specifics about how the autocomplete js file works.  There are plenty of other sources on the Internet.  However, in order for our suggestions to be produced dynamically we do need to connect our user control to a web service.  In SharePoint this may not be as easy as it seems.  I chose to integrate the web service code into my existing site.  I’m sure there are many other methods, but this seemed the most straight-forward way:

1. Add a “New Item…” to your Visual Studio Web Project (the same project that contains your user control)

2. Choose “Web Service” to create a new .asmx file.

3. Write the necessary web method(s) to provide data to your autocomplete script (We’ll cover this in more detail in Part 3)

Now, when we compile our web project, the .dll will include code for our web service as well.  The .dll will go into our bin folder of our site, we will also need to create a space for our .asmx file.  I found that creating a folder in the location of your site’s file system is easiest (\\<front-end web server>\<drive-letter>$\inetpub\wwwroot\wss\VirtualDirectories\<site name and port>).

I created a folder called “_webservices” and placed the .asmx file in there.

I then used a ScriptManagerProxy control inside the user control to create a script proxy to be used by the autocomplete jquery plugin:

<asp:ScriptManagerProxy ID="SMP" runat="server">
<Services>
<asp:ServiceReference Path="/_webservices/<web service name>.asmx" InlineScript="false" />
</Services>
</asp:ScriptManagerProxy>

You will need to give “Everyone” or “Domain Users” access to this folder because the service actually provides a .js file to be embedded into your site at load time.

We now have the “plumbing” in order.  Let’s review:

1. We have a user control on the master page for all of our U.I. elements

2. We’ve added the jquery and autocomplete .js files, using SharePoint Designer, to a “_scripts” folder in the root.  We created references to those files in the <head> section of the master page.

3. We added a web service (.asmx) item to our Visual Studio project and added the necessary code to make our autocomplete plugin work.

4. The user control creates a proxy to connect to a web service that is stored in our site’s folder structure (\\<front-end web server>\<drive-letter>$\inetpub\wwwroot\wss\VirtualDirectories\<site name and port>\_webservices\webservice.asmx)

The next step will be to capture and store search queries so that we can provide things like tag clouds and suggestions.

Coming up… SharePoint Search on Steroids Part 3 – Storing Search Queries

Probably the greatest out-of-the-box offering from the SharePoint product is search. However, the U.I. for search within SharePoint sites leaves a bit to be desired. The various site templates place the search box in areas on the page, I felt, weren’t prominent enough. Plus, I wanted to offer things like suggestions when searching for people and tracking/trending search topics.

Based on my research in user behavior, I knew that having a robust search U.I. would be vital in providing a successful Intranet site using SharePoint.

So, I set out to create a fully-functional user control to enhance the experience for our users. This is the first in a series of posts that will cover how it all got implemented.

Creating the User Control

I felt like search should be a tool that is readily available throughout the entire Intranet experience. I decided to create one user control that could be placed on the master page of the Intranet site. All the code would be developed into the user control and data would be provided by a web service.

1. Open Visual Studio and create a new Web Site Project
2. Add a new user control to the project
3. Add References to Microsoft.SharePoint and System.DirectoryServices (The Microsoft.SharePoint.dll can be located on your front-end web server if you develop on a separate workstation)
4. You may create a masterpage and attach it to the Default.aspx in your project. Then you can add the user control to the Default.aspx page. This will create an environment similar to your SharePoint site.

Now we have an environment similar to our SharePoint site. We can build design elements on our user control and bring them into our SharePoint site experience.

That’s great. But how do I get the user control working across my entire SharePoint site?

Because the search is so fundamental in our design, I wanted to add the user control to the master page of the SharePoint site. This would make the search experience prominent within the site (think Google or Yahoo).

1. Create a folder called “usercontrols” so that you can put the user control design (.ascx) file in a location the SharePoint site can find. (\\\C$\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\CONTROLTEMPLATES\usercontrols)
2. In the usercontols folder, place your .ascx file (after you build the project of course)
3. In the bin file of your site (\\$\inetpub\wwwroot\wss\VirtualDirectories\sharepointsite.domain.com80\bin) copy the compiled .dll from your project.
4. Open up SharePoint Designer and navigate to your master page (in my case I’m using “_catalogs\masterpage\default.master”)
5. Register the user control on the master page: <%@ Register src="~/_controltemplates/usercontrols/.ascx” tagname=”” tagprefix=”uc1″ %>

Of course, for this to work we need to mark certain assemblies as “safe” in the web.config of our site if they have not been deployed to the GAC. For instance, if you are using a ScriptManager you may want to add

<SafeControl Assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" Namespace="System.Web.UI" TypeName="*" Safe="True" AllowRemoteDesigner="True" />

to your web.config under “SafeControls”. Depending on any other assemblies (Telerik, Dundas, etc.) you may need to add those as well. There is plenty of information in Googleland to handle those types of questions.

You may also want to take a look at my post about controlling the styling of your master page. This will help you in getting your user control placed and designed exactly how you want it.

Next Part: SharePoint Search on Steroids Part 2 — Using JQuery and Web Services

SharePoint has pretty amazing “people” searching capabilities. However, if you are like our organization, your Active Directory data may not be as complete as you’d like it to be. Or maybe you manage identities using another system like PeopleSoft. So the question becomes: How do you get identity information into SharePoint?

I decided to develop a small console application that would insert user profile data, pulled from our PeopleSoft database, into the SharePoint User Profile Store:

What kind of dark magic must be peformed to get information in here!?

Also, we had employee photos, sitting in a web directory on the Central Admin, front-end web server, that I wanted to point to for the profile pictures.

All of this can be done using the SharePoint User Profile Manager class in the SharePoint API.

1. Start a Console Project and add references to System.Web, Microsoft.SharePoint, and Microsoft.Office.Server

(NOTE: If you are deving on a box that doesn’t have WSS or MOSS installed, you won’t be able to debug, however, you can still add the .dll files to your project for compiling.  Just go to your MOSS or WSS server and pull the Microsoft.Office.Server.dll and the Microsoft.SharePoint.dll [%Program Files%Common Files\Microsoft Shared\web server extensions\12\ISAPI] into your project)

Below is the code I used.  Obviously, there are many variants to this to make it work and no two data structures are the same.  However, I think the general concept could work for many different environments.

Essentially, what I did was iterate through a DataTable that had all my employee data in it.  I then pulled up the user profile, using the SharePoint Profile Manager, and inserted the profile property values.

All of the values I’m referencing (PhoneNo, JobTitle, Supervisor_NetworkID, etc.) were available in our PeopleSoft database. If you don’t have access to that level of data, or it’s not being collected in your organization…well…this will be kinda hard.

Finally, I committed the changes in SharePoint and moved on to the next record:

using Microsoft.SharePoint;
using Microsoft.Office.Server;
using Microsoft.Office.Server.UserProfiles;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
namespace SharePoint_Employee_Photos
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                //Get usernames
                //Retrieve E-Mail Addresses
                SqlConnection sqlconn1 = new SqlConnection("Data Source=Server;Initial Catalog=Database;User Id=dbuser;Password=dbpassword;");
                SqlCommand sqlcomm1 = new SqlCommand("Stored_Procedure", sqlconn1);
                sqlcomm1.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter sda = new SqlDataAdapter(sqlcomm1);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                using (SPSite spSite = new SPSite(@"http://sharepoint.site.org"))
                {
                    ServerContext siteContext = ServerContext.GetContext(spSite);
                    UserProfileManager pManager = new UserProfileManager(siteContext);

                    foreach (DataRow dr in dt.Rows)
                    {
                        String strUsername = "Domain\\" + dr["NetworkID"].ToString();
                        UserProfile userProfile = null;
                        //Check to see if the profile exists in SharePoint
                        if (pManager.UserExists(strUsername))
                        {
                            userProfile = pManager.GetUserProfile(strUsername);
                            Console.WriteLine("User Success: Domain\\" + dr["NetworkID"].ToString());

                            //Update any of the user profile property values

                            //Photos are made available through a separate SSIS task that queries the PeopleSoft database and outputs
                            //the images to a web folder.  The web folder is served on my Central Admin, web front end server.
                            //Let's make sure the file is there first.
                            if (File.Exists(@"[C:\PhotoWebsite\photos\" + dr["EmpID"].ToString() + ".jpg"))
                            {
                                userProfile[PropertyConstants.PictureUrl].Value = "http://website.photos.org/photos/" + dr["EmpID"].ToString() + ".jpg";
                            }
                            if (!dr["PhoneNo"].Equals(DBNull.Value))
                            {
                                if (!dr["PhoneNo"].Equals(String.Empty))
                                {
                                    userProfile[PropertyConstants.WorkPhone].Value = dr["PhoneNo"].ToString();
                                }
                            }
                            if (!dr["DeptName"].Equals(DBNull.Value))
                            {
                                if (!dr["DeptName"].Equals(String.Empty))
                                {
                                    userProfile[PropertyConstants.Department].Value = dr["DeptName"].ToString();
                                }
                            }
                            if (!dr["JobTitle"].Equals(DBNull.Value))
                            {
                                if (!dr["JobTitle"].Equals(String.Empty))
                                {
                                    userProfile[PropertyConstants.Title].Value = dr["JobTitle"].ToString();
                                }
                            }
                            if (!dr["Supervisor_NetworkID"].Equals(DBNull.Value))
                            {
                                if (!dr["Supervisor_NetworkID"].Equals(String.Empty))
                                {
                                    String strSupervisorUsername = "Domain\\" + dr["Supervisor_NetworkID"].ToString();
                                    if (pManager.UserExists(strSupervisorUsername))
                                    {
                                        userProfile[PropertyConstants.Manager].Value = strSupervisorUsername;
                                        Console.WriteLine("Supervisor Success: Domain\\" + dr["Supervisor_NetworkID"].ToString());
                                    }
                                }
                            }

                            userProfile[PropertyConstants.WorkEmail].Value = dr["Email"].ToString();

                            //Commit the changes
                            userProfile.Commit();
                            Console.WriteLine("Import Success!");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                Console.ReadLine();
            }
        }
    }
}

I found myself in a situation, the other day, where I needed to display items from a Links List on one of my subsites.

The trouble was that the Links List resided on the parent site.  Well, that should be easy right?  Wrong.

I started by using the Content Query Web Part (CQWP).   I selected to “Show items from the following list” and selected the Links list on the parent site.

The list resolved, however, this is what it looked like:

Hmmmm...That dont look right

It turns out that the CQWP doesn’t know what to do with the URL and Title fields on the Links List.

Also, when you click on the “(Blank)” link, it takes you to the item view of the link and not the page you’re linking to.

So I started Googling and found Ishai Sagi’s clever little tip.

Basically, he exports the web part and makes some modifications.  Essentially, creating a custom web part.

1. I would start by configuring any settings you will want to carry over to your custom web part (Query, Appearance, Title, Sorting, Audience Targeting, etc.)  This will save you from having to make those adjustments after importing the web part to your pages.

2. Click the “edit” button on the web part and select “Export…”

Select "Export..." from the edit menu on the web part

3.  Save the .webpart file to your hard disk.  Then open it using your favorite XML editor.

4.  You’re going to be looking for a property “CommonViewFields”:

<property name="CommonViewFields" type="string" />

Change it to this:

<property name="CommonViewFields" type="string">URL,text</property>

5. Now we need to add a custom “Item Style”, so the web part will understand what to do with the new “CommonViewFields” property. This will require editing the item style XSL of the site.

6. Open up SharePoint Designer and navigate to your site or parent site.

7. Check out and open Style Library > XSL Style Sheets > ItemStyle.xsl

8. We are now looking for the template for the style “NoImage”:

<xsl:template name="NoImage" match="Row[@Style='NoImage']" mode="itemstyle">

9. Make a copy of the complete template:

from

<xsl:template name="NoImage" match="Row[@Style='NoImage']" mode="itemstyle">

to

<xsl:template name="NoImage" match="Row[@Style='NoImage']" mode="itemstyle">

and paste it under the “NoImage” template.

1. Rename “NoImage” to “LinkList”.
2. Change the “href” attribute to point to the URL column, the text before the column.
3. Change the link text to point to the URL column, the text after the column.

10.  Here’s the complete code:

<xsl:template name="LinkList" match="Row[@Style='LinkList']" mode="itemstyle">
 <xsl:variable name="DisplayTitle">
        <xsl:call-template name="OuterTemplate.GetTitle">
            <xsl:with-param name="Title" select="@URL"/>
            <xsl:with-param name="UrlColumnName" select="'URL'"/>
        </xsl:call-template>
    </xsl:variable>
 <xsl:variable name="LinkTarget">
        <xsl:if test="@OpenInNewWindow = 'True'" >_blank</xsl:if>
    </xsl:variable>
 <div id="linkitem" class="item" >
  <div class="bullet link-item">
   <xsl:call-template name="OuterTemplate.CallPresenceStatusIconTemplate"/>
   <a>
    <xsl:attribute name="href">
     <xsl:value-of select="substring-before($DisplayTitle,', ')">
     </xsl:value-of>
    </xsl:attribute>
    <xsl:attribute name="title">
     <xsl:value-of select="@Description">
     </xsl:value-of>
    </xsl:attribute>
    <xsl:value-of select="substring-after($DisplayTitle,', ')">
    </xsl:value-of>
   </a>
  </div>
 </div>
</xsl:template>

If you make any mistakes with this part of the styling, it will affect ANY other CQWP you might have deployed on other pages.  Might be a good idea to check on those right now.

11. If everything looks gravy, save the changes to the file and go to the Site Settings of your site or parent site.

12. Under “Galleries”, select “Web Parts”

13. Upload the .webpart file.  Some other settings will come up. Give it a file name, Title, Description. I also like to specify a “Custom” Group value. That way the web part is easy to find in the “Add Web Parts” dialog.

14. Go to your site page or subsite page and Add Web Part.  Add the custom web part to the page.

15. Modify the web part and under Presentation > Styles > Item Style,  select “LinkList” from the dropdown menu:

The links should be showing correctly.  Plus, when you click on them, they go directly to the page you are linking to.

Nice work Ishai!

Even though most organizations deal with Adobe Acrobat and .pdf documents, it doesn’t mean Microsoft added this file type in SharePoint. Sure you can upload .pdf documents to document libraries, but you can forget about crawling those documents and returning results from your search queries.

Thankfully, there are a few resources to address this issue. I will document those resources below, however, here is a condensed list of actions:

1. You have to get your hands on an IFilter from Adobe. Essentially, the IFilter is the link that gets SharePoint to understand what a .pdf document is and how to crawl it. The easiest way to install the IFilter is to download the latest version of Adobe Acrobat Reader on your Central Administration server. The latest versions (8.0 and up) of Reader include the IFilter.

2.  You then need to add the extension to your SharePoint extension list:

1. Click Start, click Run, type regedit, and then click OK.
2. Locate and then click the following registry subkey:
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.0\Search\Applications\GUID\Gather\Search\Extensions\ExtensionList
3. On the Edit menu, point to New, and then click String Value.
4. Type 38, and then press ENTER.
5. Right-click the registry entry that you created, and then click Modify.
6. In the Value data box, type pdf, and then click OK.

3.  Add the PDF file type to the Extensions List for WSS search by editing the registry:

1. Start regedit
2. Open the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.0\Search\Applications\{Random GUID}\Gather\Search\Extensions\ExtensionList
3. Add PDF to the list as a new String Value. Use a new high value e.g. if 37 is the highest value, use “38″ as the key with the value “pdf”

4.  Make a graphic for the .pdf icon to be displayed in the search results and add it to SharePoint files to be referenced:

1. Create a 16X16 pixel image of the Acrobat file icon.
2. Save the image as pdf16.gif
3. Add the Acrobat PDF picture to the SharePoint templates directory. Copy the Acrobat PDF picture called pdf16.gif in the 12 Hive\TEMPLATE\IMAGES folder, e.g. %programfiles%\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\IMAGES.
4. Bind the Acrobat PDF picture to the PDF file type
5. Open the 12 Hive\TEMPLATE\XML\DOCICON.XML file
6. Find the <DocIcons.ByExtension> part
7. Add the following mapping: <mapping Key=”pdf” Value=”pdf16.gif” OpenControl=”" />

5.  Run IISReset

6. For MOSS 2007 users, Add the file type to Central Administration:

1. Go to your SSP site
2. Click on Search Settings > File Types > New File Type
3. Add pdf as a file type

7. Complete a Full Crawl of your content sources.  This will re-crawl pdf documents that may already be in libraries.

Some of you might see the following warning in your crawl logs:

The file reached the maximum download limit. Check that the full text of the document can be meaningfully crawled.

This is because some of your .pdf files may be too large.  Also, SharePoint wont crawl .pdf documents that have other .pdf documents contained within them.

There doesn’t seem to be a fix for complex .pdf documents, however the size issue can be resolved by adjusting the MaxDownloadSize key in the registry:

1. Start Registry Editor (Regedit.exe).
2. Locate the following key in the registry: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\12.0\Search\Global\Gathering Manager
3. Open Edit – New – DWORD Value. Name it MaxDownloadSize. Double-click, change the value to Decimal, and type the maximum size (in MB) for files that the gatherer downloads.

You may also want to adjust the timeout period for SharePoint to crawl the documents as you have just adjusted the amount of data that can be crawled and it may take longer:

1. In Central Administration, on the Application Management tab, in the Search section, click Manage search service.
2. On the Manage Search Service page, in the Farm-Level Search Settings section, click Farm-level search settings.
3. In the Timeout Settings section change Connection and Request acknowledgement time.

1. For WSS3 users: The registry key for is HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\
   Web Server Extensions\12.0\Search\Global\Gathering Manager

Default would be MaxDownloadSize (16MB) * MaxGrowFactor (4MB) = 16 * 4 = 64MB of text can be indexed.

After all of your changes, you should restart the CA server and run another full crawl.

Resources:

http://support.microsoft.com/default.aspx/kb/927675

http://blog.tylerholmes.com/2008/04/walkthrough-installing-adobe-v6-pdf.html

http://bloggingabout.net/blogs/harold/archive/2008/10/02/index-pdf-documents-on-sharepoint-using-adobe-pdf-ifilter-9.aspx

http://grounding.co.za/blogs/neil/archive/2008/12/02/working-with-pdf-s-and-sharepoint.aspx

http://fmuntean.wordpress.com/2008/05/22/increase-file-size-crawling/

One of my favorite features to demo on SharePoint is its incredible searching capabilities. It is, by far, the feature that elicits the greatest reaction.

More specifically, I just plain LOVE the way SharePoint handles searching for people within the organization.

However, the data comes from our Active Directory schema and if your organization is anything like ours, it’s not the cleanest set of data.

For instance, I was finding devices, voicemail boxes, security and distribution groups, and all sorts of other objects showing up in my people results.

I did some binging around the Internet and discovered Mindsharp’s blog.
They had a couple of posts regarding filtering profile data within SharePoint.

This one written by Wayne Hall (archived) and
this one written by Daniel A. Galant.

Here’s the breakdown:

1. Go to your Central Administration site and select your Shared Service Provider. It may require you to log in under the farm administrator account.
2. Under User Profiles and My Sites, select: “User profiles and properties”.
3. On the User Profiles and Properties page, select: “View import connections”.
4. You can select “Create New Connection” or edit an existing connection.

You’ll have to specify your own domain controller information (most of it is already auto detected). The most important item is “User filter”.

The user filter allows you to specify criteria you would like included or excluded from the import. There is a variety of options to explore using LDAP query filters. There are SO many resources out there to explain writing LDAP filter queries. Here’s one to get you started.

The query that I decided to use, was this:

This, essentially, filters the Active Directory schema to only import those objects that:

1. (objectCategory=person) = are under category “person”.

AND

2. (objectClass=user) = are of class “user”.

AND

3. (sn=*) = have a surname [Last Name].

AND

4. (mail=*) = have a valid e-mail address.

AND

5. (showInAddressBook=*) = are available in our corporate address book.

AND

6. (!(userAccountControl:1.2.840.113556.1.4.803:=2)) = is an account that is active.

This filter eliminated over 9,000 unnecessary items in our user profile list making our people search far more effective.

NOTE : One area that messed me up was setting the Scope to “One level”. This was producing an error:

A test import based on current input returned no results. Please re-check the search base and filter.

This was occurring because the filter was searching for items at the root level. Because I had no items that matched my criteria, at the root level, it was returning no results.
Once I selected the option “Subtree” it searched the entire schema and I was all aboard the Gravy Train Express.

I have currently been tasked with the duty of providing a web part that displays patient satisfaction scores for the hospital. However, there was one stipulation:

“Can you make the web part reflect the unit that the nurse is working on?”.

In other words, could I create a web part that would observe the user’s login and display appropriate information based on where, in the hospital, that employee worked. In this case, the requester (ahem, my boss) wanted the scores to display for the nurse’s unit (think of a unit as a “wing” or “floor” in a hospital).

Provocative.

Right off the bat, I figured the best way to access the nurse’s unit was by pulling the “Department” property from our Active Directory schema.

But then I thought: “Why not build a function that would return any property I might want from Active Directory?” That way, from here on out, as I am building my web parts I could make decisions of the data to display based on a user’s department, job title, director, etc.

Essentially, creating web parts that are “user-aware”.

Good idea self!

Below is a namespace (ActiveDirectoryInfo) , class (UserInfo), and function (GetPropertyValue) that I created to do just this:

using Microsoft.Office.Server.Audience;

using Microsoft.SharePoint;

using Microsoft.SharePoint.WebControls;

using System;

using System.Collections.Generic;

using System.DirectoryServices;

using System.Text;

using System.Web;

namespace ActiveDirectoryInfo

{

class UserInfo

{

public String GetPropertyValue(string PropKey)

{

//Get user’s Department from Active Directory

SPWeb spw = SPControl.GetContextWeb(HttpContext.Current); //Establishes the current SharePoint site.

SPUser spu = spw.CurrentUser; //Grabs user information from the logged in state of the user.

DirectoryEntry dentry = new DirectoryEntry(@”LDAP://DC=<hidden>,DC=<hidden>”, “<domain_user>”, “<domain_user_password>”);

DirectorySearcher dsearch = new DirectorySearcher(dentry);

String retUserName= “”;

dsearch.Filter = “(objectSid=” + spu.Sid + “)”; //Filters the search result based on the user’s SID.

//dsearch.Filter = “(sAMAccountName=” + spu.LoginName.Replace(@”Domain\”, “”) + “)”; //Filters the search result based on the user’s login (without Domain\).

dsearch.PropertiesToLoad.Add(PropKey); //Loads the specific property to be viewed.

SearchResult result = dsearch.FindOne(); //Peforms the search for one matching record.

if (null == result)

{

//Nothing

}

else

{

retUserName = result.Properties[PropKey][0].ToString();

}

return retUserName;

}

}

}

So here is how we might call upon this function within a web part:

protected override void Render(System.Web.UI.HtmlTextWriter writer)

{

UserInfo ui = new ActiveDirectoryInfo.UserInfo();

writer.Write(ui.GetPropertyValue(“cn”)); //Returns user’s full name

writer.Write(ui.GetPropertyValue(“mail”)); //Returns user’s e-mail

writer.Write(ui.GetPropertyValue(“Department”)); //Returns user’s Department

}