Earlier this year, I had the opportunity to create a MOSS 2007 web part for a client which integrates information from the current user's profile with a MOSS Search Query in order to find information most relevant to the current user (similar to the Personalized Content Objects in CMS). For example, the client has offices all over the world, and one of the things they wanted to do was have a "Most recent content additions" page which would show the most recent information added to the portal, filtered by the region that the information was targeted to (e.g. North America, Europe, Asia, Middle East, Africa, etc.). I accomplished this through the implementation of a web part that could be placed on any page and configured by the content manager for the site. The web part turned out pretty well, and the other day a couple guys I've worked with over the years from MCS, Henry Winkler and Matt Fangman, asked if I'd be up for blogging about it, so that brings me here.
The Problem
At first glance, we thought we'd use audiences, which seems to be the obvious choice, to target the content as described above, but when we dove a bit deeper into the customer's requirements, it was apparent that as soon as we started adding additional filter criteria such as current user job role and primary technology of interest, that the number of audiences which would be needed increased exponentially, making correct categorization of content extremely difficult (because when you post content, you need to choose each and every audience that the content should be targeted to). Let me give you an example:
Let's say that we wanted to create a page which would be called "Recent Postings in your Region related to your Job". This page would display content with the following criteria:
- Region = current user's region (North America, Europe, Asia)
- Job Role = current user's job role (Project Manager, Engineer, Tester, Accounting)
- Primary Technology = current user's primary technology (MOSS, Microsoft Dynamics, BizTalk, Portfolio Server)
With just the example choices given above, if we wanted to use audiences to target this information, we would need to create 48 audiences (3*4*4) to cover all of the possible combinations. If we added just one more region, such as Africa, then the number or audiences would increase to 64 (4*4*4). In case this isn't entirely obvious, here are the first few audiences we would need:
- North America Project Managers for MOSS
- North America Project Managers for Dynamics
- North America Project Managers for BizTalk
- North America Project Managers for Portfolio Server
- North America Engineer for MOSS
- …
Taking this one step further, if we had 12 regions, 8 job roles and 15 technologies, we'd need 1440 audiences, not going to happen.
The Solution
So, we needed to come up with a different solution. Without much discussion, it was obvious that the best thing to do was going to be looking up information from current user's profile and combining this with a MOSS SQL Search query to find the relevant information. Luckily for us, there was a parallel Microsoft Identity Integration Server project (see http://www.microsoft.com/miis) being implemented which would provide all of the profile information we were going to need, so we didn't have to worry about populating this information within the scope of our project.
Now that we had a good approach, it was merely a matter of implementing the web part and providing the content manager a way to "match up" content metadata with the user's profile attributes. This was achieved relatively easily with the use of the MOSS Enterprise Search SQL Syntax (see http://msdn2.microsoft.com/en-us/library/ms493660.aspx) query capabilities and a little bit of code to structure the query appropriately before sending it off to the search server.
So, for the example above, we can get the region, job role and primary technology out of the user's profile (e.g. Asia, Engineer & MOSS), inject the values into the search query, execute the search and then display the results.
The resulting web part turned out very well and has been successfully deployed to a number of sites and site collections. Since initial deployment, the web part has been extended so that the content manager can further filter on things such as content type, search scope or keywords.
The Result
If you plan to read further through this blog, you'll probably want to download the Visual Studio Solution I've made available so you can follow my code examples (there's too much code to blog about everything, so I'm just going to focus on the things pertinent to this post). To download a Zip file of the Visual Studio 2005 Solution, go to my Office Live Site Download Page and click on the QueryForIt.img link (2 IMPORTANT NOTES: 1. I had to rename the file to have a *.img extension in order to get it to work, so after you download the QueryForIt.img file, rename it to QueryForIt.zip before opening. 2. When you build the solution, you must switch your build configuration to RELEASE or the post-build commands will fail – or you can go edit the DDF file).
The code is spread across several files, but this is primarily due to the fact that I used some existing custom toolparts plus one just for this web part as well as a couple of helper classes to do some dirty work. Here's a breakdown of the code:
manifest.xml – Manifest file for deployment (see very end of post)
Statera.Moss.Webparts.QueryForIt.ddf – input file for the makecab utility in order to create a SharePoint solution (see very end of post)
The Custom Toolparts
In order to make configuration of the web part as easy as possible, I create a few custom toolparts to be used when modifying the web part. These custom toolparts' functionality include:
- Ability to select which Content Types should be searched
- Ability to select which Search Scope to be searched
- Ability to further filter results with an additional Query Phrase (keyword search)
- Ability to select which columns are displayed in the search results
- The column which the results should initially be sorted by and which direction
- An advanced query phrase (this is where you can link in information from the user profile)
Here are some screenshots of what the toolparts look like:
The third image above titled "Filter & Customize Output" is where the user can input a keyword, select the columns to display, and finally enter an "Advanced Query". The "Advanced Query" textbox is where the content manager would write the code which will be injected into the SQL Syntax Search Query. The syntax for this "Advanced Query" textbox is very specific and is explained in the comment below the textbox. (I know… this isn't very user friendly, but I wasn't expecting the typical content administrator to be modifying this web part. If you were expecting this, then this custom toolpart could be extended to be much more user friendly).
Integrating User Profile Information with the Enterprise SQL Search
Once the toolparts were created, the next step was to implement the actual web part which would integrate the current user's profile information with a basic SQL Query. I started with a base web part with properties to hold the information provided by the toolparts above and then wrote the code to create and then execute the query. Here are a few code snippets of how the query is created and executed (if you want to make fun of my variable naming techniques, go ahead… habits are hard to break):
|
private PersonalizedQueryHelper GetPersonalizedQueryHelperClass()
{
DataSet mySearchQueryResultsDS = new DataSet("MyMetaDataDS");
try
{
//-----------------------------------------------------------
// Execute the query
//-----------------------------------------------------------
FullTextSqlQuery kRequest = new FullTextSqlQuery(ServerContext.Current);
kRequest.QueryText = SQLQueryToExecute;
kRequest.TrimDuplicates = false;
kRequest.ResultTypes |= ResultType.RelevantResults;
kRequest.RowLimit = _iResultLimit;
ResultTableCollection resultTbls = kRequest.Execute();
if ((int)ResultType.RelevantResults != 0)
{
ResultTable tblResult = resultTbls[ResultType.RelevantResults];
if (tblResult.TotalRows == 0)
{
// NO RESULTS
PersonalizedQueryHelper myPersonalizedQueryHelperEmpty = new PersonalizedQueryHelper();
return myPersonalizedQueryHelperEmpty;
}
else
{
// Get the results, put them in a Datatable and create an instance of MetadataBrowserHelper
DataTable relResultsTbl = new DataTable();
relResultsTbl.TableName = "TheSearchResults";
mySearchQueryResultsDS.Tables.Add(relResultsTbl);
mySearchQueryResultsDS.Load(tblResult, LoadOption.OverwriteChanges, relResultsTbl);
}
}
}
catch (Exception myExc)
{
_lblQueryResult.Text = String.Format("The following error occurred while trying to execute the search query for this web part. If the problem continues, please contact your administrator: {0}", myExc.Message);
}
…
}
|
|
public string SQLQueryToExecute
{
get
{
…
//-----------------------------------------------------------
// Create the Personal Query filter if applicable for the WHERE CLAUSE
//-----------------------------------------------------------
string sPersonalizedWhereClause = "";
if (_sPersonalFilters != null)
{
foreach (string sFilter in _sPersonalFilters)
{
if (sFilter.IndexOf(':') > 0)
{
string sMetaDataField = sFilter.Split(':')[0];
string sProfileField = sFilter.Split(':')[1];
string sProfileFieldValue = GetCurrentUserProfileProperty(sProfileField);
if (sProfileFieldValue.Length > 0)
{
if (sPersonalizedWhereClause.Length == 0)
sPersonalizedWhereClause += string.Format(" {0} = '{1}' ", sMetaDataField, sProfileFieldValue);
else
sPersonalizedWhereClause += string.Format(" AND {0} = '{1}' ", sMetaDataField, sProfileFieldValue);
}
}
}
}
string sKeywordsWhereClause = "";
if (_sKeywords.Length > 0)
{
sKeywordsWhereClause += string.Format(" CONTAINS('\"{0}\"') ", _sKeywords);
}
…
}
}
|
|
private string GetCurrentUserProfileProperty(string sKey)
{
try
{
UserProfileManager profileManager = new UserProfileManager(ServerContext.Current);
UserProfile myCurrentUserProfile = profileManager.GetUserProfile(SPContext.Current.Web.CurrentUser.LoginName);
return myCurrentUserProfile[sKey].Value.ToString();
}
catch
{
return string.Empty;
}
} |
The first box of code above contains the code used to actually execute the Enterprise Search using the SQL syntax. The second box of code above contains the code which is used to generate the part of the SQL where clause containing information from the user's profile. And the third box of code shows the few lines it takes to actually retrieve the info from the current user's profile.
Formatting of Search Results
If you've gotten this far through this post, then you're here for the long haul, so I should point out that the results returned from the SQL Search are not quite ready for being displayed. When result content contains either site columns of type "Option" or "Lookup", the results returned from search are not always textual, but sometimes are integers (representing the ID of the actual choice that was made). Furthermore, if the possible values are Multi-select, then the results returned from the Search Query are delimitated with ';#'. In order to convert these results to more friendly strings, I had to write the FixLookupAndMultiChoiceColumns() method which exists in the Util.cs file (not shown in this post). If you look at this method, you'll notice that I look through all of the results returned from the search and correct the columns that need correcting. Something to note: when I first wrote this method I was a little concerned with performance, but once I got to testing it, it appeared to be just fine.
Binding the Search Results to an SPGridView
Now that I have the results, I wanted to display them in a grid which had both sorting and paging functionality. I've written a lot of .NET code over the years and this use to be kind of a pain in the butt task that we all had to do. Luckily, though, with the implementation of the GridView class in .NET 2.0, and the extension of this component by WSS 3.0 to the SPGridView class, I'm able to do this without writing any paging or sorting code. However, this isn't so straight forward when you have to write all of the code within a web part and don't have use of code-in-front, but I'm not going to get into it in detail here, but you can read my posting titled My Memberships Web Part for WSS 3.0 to get a bit more information about how I accomplished this with the use of an the SPGridView and an ObjectDataSource.
Deploying the Web Part with a SharePoint Solution
In addition to writing the code for the web part, I also setup the Visual Studio solution so that a SharePoint solution (.wsp) file would be created for easy deployment to SharePoint whenever you run a build. This was accomplished by doing the following:
- Create a Manifest file and place it in the Visual Studio Project (manifest.xml)
- Create a DDF file for the makecab utility to use and place it in the Visual Studio Project (Statera.Moss.Webparts.QueryForIt.ddf)
- Add the following two lines to the post build event command line (Build Event tab of the project properties) so that the WSP file specified in the DDF file gets created with every build:
cd $(ProjectDir)
makecab /f Statera.Moss.Webparts.QueryForIt.ddf
Now, every time I run a build, the Statera.Moss.Webparts.QueryForIt.wsp file gets created.
To add and deploy this solution to a SharePoint 2007 web, you need to follow these steps:
- Copy the wsp file (provided in the ZIP file I posted) to the SharePoint server and then execute the following STSADM commands from the command line:
stsadm –o addsolution –filename Statera.Moss.Webparts.QueryForIt.wsp
stsadm –o deploysolution –name Statera.Moss.Webparts.QueryForIt.wsp –immediate –allcontenturls \
NOTE: for some reason, if you copy and paste the above two lines into a command prompt, it won't work. You need to type them out by hand.
NOTE: when you deploy this solution, the you have to deploy it to a specific Web App, and the DLLs go into the web app's bin directory instead of the GAC – long story there… still trying to figure out the problem… something to do with my Namespace is too deep - I think)
- Add the web part to the list of web parts for the Site Collection
- Add the web part to a page
- Modify the web part and setup the properties. Most of these properties are self explanatory except for the "Advanced Query" textbox, but I've provided some instructions below it (as mentioned before).
Note: in order to use a metadata property within the Advanced Query (e.g. "region" in the screenshots above), the metadata property must be added to the list of "Metadata Property Mappings" in the configuration of the Search Server for the SSP (go to "Shared Service Administration à Search Settings à Metadata property mappings" and add any and all properties you want to be able to query by to the list of properties).
Bonus: From Date & To Date Connected Web Parts
In addition to the functionality already mentioned, I also added the ability to connect up to two Date Filter web parts. You would do this if you wanted to limit the results to say, postings or modifications over the past 7 days. To do this, you would add two date filter web parts to the page, set their default values accordingly, and then connect them to the "FromDate" and "ToDate" properties on this web part.
Conclusion
Well… that pretty much covers it. Here's a screenshot of the final web part in use with the results sorted by the created date descending. Note that since I'm using the SPGridView, I didn't have to write any sort of formatting code within my web part, it all gets taken care of so the grid matches other grids within SharePoint (and if the CSS files are customized, those customizations will be applied to this web part as well).
If you download the code and make any cool additions to it, I'd love to get a copy of what you did. Just send me an email at: 
Check out Wade Wegner's blog for great information on Commerce Server 2007.
Tools 
Help
