Corey Roth - DotNetMafia.com - Tip of the Day

How to: Use the Chart Web Part with Excel Services

CoreyRoth — Thu, 02 Sep 2010 20:04:35 GMT

Hopefully, you were able to impress the boss with my last post on the Chart Web Part. I explained how to use the BCS with the Chart Web Part to display great looking charts using external data. Another option is to use Excel Services. Excel Services is pretty easy to setup now and you usually don’t have to do much configuration out of the box. Let’s see what we can do. This assumes you have Excel Services installed and configured to trust spreadsheets from your SharePoint server.

First, let’s start with my super awesome spreadsheet. It's a simple example, but hopefully you get the idea. If you have Excel Services working, you should have the View in Browser option when you are looking at a file in a document library. Viewing Office documents in the browser might even be your default if you have installed Office Web Apps.

Clicking on that link, shows us the Excel document we are working with.

SharePoint 2010 has much better support for a wide variety of Excel documents. For the most part it will render most documents even if they have unsupported features in them. This post has some details on what is supported and what is not.

To use Excel Services with the Chart Web Part, we need the URL to the Excel document. You can get this in a number of ways. Just be aware if you try to copy a link from the document library it might give you a link to the xlviewer.aspx which will not work in the Chart Web Part. Make sure you get a link that references the .xslx file directly from your SharePoint server.

For example, this link will not work in the Chart Web Part:

http://sp2010/ECM/_layouts/xlviewer.aspx?id=/ECM/Company%20Documents/2010%20Budget.xlsx&Source=http%3A%2F%2Fsp2010%2FECM%2FCompany%2520Documents%2FForms%2FAllItems%2Easpx&DefaultItemOpen=1

This link will work in the Chart Web Part:

http://sp2010/ECM/Company%20Documents/2010%20Budget.xlsx

Once you have the link to your spreadsheet, edit a page and add a Chart Web Part to it. If you don’t remember how, you can find the details on it on my previous Chart Web Part post. Now, click the Data & Appearance link and then Connect Chart to Data. Now choose Excel Services. You will then be presented with a screen prompting you for the Excel Web Service URL, Excel Workbook Path, and Range Name. The Excel Web Service URL should already be filled out for you. It will look something like this.

http://sp2010/_vti_bin/excelservice.asmx

Now you need to provide a path to the workbook. Use a complete URL like the one I used above. The last thing you need to provide is the range of the spreadsheet you want to use. Here you need to know some Excel basics. You start by specifying the sheet name followed by the first cell using the !, $, and : delimiters. In my case I want A1 through B5 on Sheet1, it looks like this:

Sheet1!$A$1:$B$5

If the first row of your spreadsheet has column names, check the box. Here is what it looks like completed.

Click Next and if Excel Services like your spreadsheet, it should give you a preview of the data. If it doesn’t like it, it might give you a user friendly error or it might give you a Exception has been thrown by the target of an invocation error. If that is the case, there is likely an unsupported feature such as a chart that you need to remove for the spreadsheet to work. Here is what the preview looks like.

Click next, and you will choose the Axis and other settings for the chart. Change any settings you want and then you are done.

Once you click finish you should be able to see your data using the default chart.

Of course if you want another type of chart, you can click Data & Appearance and pick another type from the Customize your Chart link.

One thing to note is that Excel Services does cache data for a while so if you change data in the spreadsheet, it might take a few minutes for the graph to reflect your changes. This was a simple example, but as you can see it’s not very complicated to get a nice looking chart up and running quickly. If you do run into issues, I recommend tearing your spreadsheet apart and look for unsupported features.

Querying Multiple Federated Locations in Enterprise Search with the QueryManager class

CoreyRoth — Wed, 25 Aug 2010 16:54:00 GMT

Last week, I showed you how to use the QueryManager class to query SharePoint Enterprise Search. In that post, I mentioned that we can actually use the QueryManger class to harness the true power of federated search and query multiple locations at once. As you will see today, it’s pretty easy to do, but you might have to do some work with the XML you get back. We’ll start with the code from last week, and then add the search results from the included Bing.com Internet Search Results provider. To add another location, we just add another location to the LocationList object.

Location internetLocation = new Location("InternetSearchResults", searchProxy);
locationList.Add(internetLocation);

Remember that the name of the Location comes from the Location Name field on the federated location. Refer to the table in the QueryManager post for more information. Using the code from last week, we put all this together and it looks like this:

// get the query and settings service proxy
SearchQueryAndSiteSettingsServiceProxy settingsProxy = SPFarm.Local.ServiceProxies.GetValue<SearchQueryAndSiteSettingsServiceProxy>();
// get the search service application proxy by name
SearchServiceApplicationProxy searchProxy = settingsProxy.ApplicationProxies.GetValue<SearchServiceApplicationProxy>("Search Service Application");
QueryManager queryManager = new QueryManager();
// add the federated location we want
LocationList locationList = new LocationList();
Location localSearchLocation = new Location("LocalSearchIndex", searchProxy);
locationList.Add(localSearchLocation);
// add results from bing
Location internetLocation = new Location("InternetSearchResults", searchProxy);
locationList.Add(internetLocation);
 
queryManager.UserQuery = "accounting";
queryManager.Add(locationList);
queryManager.IsTriggered(locationList);
XmlDocument xmlDocument = queryManager.GetResults(locationList);
System.IO.File.WriteAllText("results.xml", xmlDocument.InnerXml);
Console.WriteLine(xmlDocument.InnerXml);
Console.ReadLine();

As you can see, it’s pretty simple to add another location. What do the search results look like though? Well, the results may not be what you expected. It just appends the second location’s results to the XML document. What is bad is that the schema isn’t the same. Since Bing results are coming in via an OpenSearch provider, it just gives you the raw RSS feed of the results. I won’t post the entire XML document here, but you should get the idea of what I mean.

  <isdocument>False</isdocument>
  <picturethumbnailurl></picturethumbnailurl>
  <serverredirectedurl></serverredirectedurl>
</Result>
<TotalResults>24</TotalResults>
<NumberOfResults>10</NumberOfResults>
<channel>
  <title>Bing: </title>
  <link>http://www.bing.com:80/search?q=accounting</link>
  <description>Search results</description>
  <image>
    <url>http://www.bing.com:80/s/a/rsslogo.gif</url>
    <title>accounting</title>
    <link>http://www.bing.com:80/search?q=accounting</link>
  </image>
  <copyright>Copyright © 2010 Microsoft. All rights reserved. These XML results may not be used, reproduced or transmitted in any manner or for any purpose other than rendering Bing results within an RSS aggregator for your personal, non-commercial use. Any other use of these results requires express written permission from Microsoft Corporation. By accessing this web page or using these results in any manner whatsoever, you agree to be bound by the foregoing restrictions.</copyright>
  <item>
    <title>Accountancy - Wikipedia, the free encyclopedia</title>
    <link>http://en.wikipedia.org/wiki/Accounting</link>
    <description>Accountancy is the art of communicating financial information about a business entity to users such as shareholders and managers. [1] The communication is generally in the ...</description>
    <pubDate>Tue, 24 Aug 2010 08:47:00 GMT</pubDate>
  </item>

This may not be ideal, but if you need the data, you can get it. You may just have to work with the XML a bit. Luckily LINQ to XML makes that pretty easy. What about if you add a third location? Well it just appends it right onto the end. I added another SharePoint location and it simple adding Result elements right onto the document. What I don’t like about it is that there is nothing in the Result element that tells you what location it came from.

You know reading that copyright message on the Bing results makes me wonder. It specifically says that the results can only be used with an RSS aggregator for personal, non-commercial use. I would venture to say most uses of SharePoint are commercial. Does this mean we are not supposed to use Bing results there? Perhaps that is a question for the forums.

Even if you have to work with the XML a bit to get the results you need, I think this is very powerful. It’s nice that you can get results from more than one location so easily. I’ve attached an XML file to this post with the complete result set from three locations if you want to examine it further.

Adding a link to a document’s folder in SharePoint 2010 Enterprise Search

CoreyRoth — Mon, 23 Aug 2010 16:19:44 GMT

One request I have seen a lot from people since MOSS 2007 is the desire to add a link to the subfolder of a document on the search results page. This is useful when the user wants to see what else is in that folder or if they want to perform other operations on that document (such as viewing properties or compliance details). In SharePoint 2007, I wrote the Document Link Handler to help with this. It simply looked up the details on the item and redirected the user to the appropriate page. In SharePoint 2010, this is not necessary any more since they added the new sitename column in the search results XML document. Today we will customize the look of the search results page. All we have to do is use the new sitename column in the XSL that displays the results.

There are two ways to go about this. If you only want this new link to show up on certain results pages, you can edit the CoreResultsWebPart in a given search center. If you want the link to be global and to appear on all search centers, you can edit the Local Search Results federated location. Today we’ll demonstrate modifying the federated location, but if you want to just change a specific page, edit it, and then edit the Core Results Web Part. You will then click the XSL editor button and make the same changes as we are about to do in the federated location.

To edit the federated location go to your Search Service Application –> Federated Locations, then edit Local Search Results. Expand the Display Information and scroll down to the Core Search Results Display Metadata section.

Ensure Use Default Formatting is not checked and click the button next to the XSL textbox. I found that the best place to add the link is near the location where the View in Browser link normally appears. The place in the file is more than half way down. If you see the ViewInBrowser link you know you are near the right spot. Here is what it looks like.

Now, what do we add there? It’s pretty simple. We use XSL to create a link and pass the value of the sitename element. However, I recommend that you only show the link for documents. The sitename element is always populated but you might find that it has less relevance on things like non-SharePoint sites or for list items. It’s up to you if you want to include it or not though. Here is the code that you will want to add.

<xsl:if test="isdocument = 'True'">
  <a>
    <xsl:attribute name="href">
      <xsl:value-of select="sitename"/>
    </xsl:attribute>
    View Folder
  </a>
</xsl:if>

One thing to note is that in SharePoint 2007, you used to compare isdocument to a value of 1. That doesn’t work anymore. You have to compare it to True or False. That’s really all you have to do. Here is what it looks like together.

Once you have the change complete, click OK and then save your changes to the federated location. It usually takes about a minute for your changes to show up when you do search. So wait a bit and then go to your search center and give it a try. Here is what the link looks like on my results screen.

If the link doesn’t show up for you, try another search term. If you still have issues, try removing the isocument xsl:test element and see if the link shows up then. This link is pretty easy to add, so give it a try today. I think the link to the document’s folder is really handy and I think your users will appreciate it.

How to: Use the QueryManager class to query SharePoint 2010 Enterprise Search

CoreyRoth — Mon, 16 Aug 2010 02:46:00 GMT

Last week, I talked about the KeywordQuery class and how to use it in SharePoint 2010. I mentioned that a new way to query also existed using the QueryManager class. This class is extremely powerful and can allow us to issue federated queries to multiple locations at one time. For example, you could make one call and get results back simultaneously from Enterprise Search, FAST, and an OpenSearch provider like Bing. You can still use the KeywordQuery class but I think the best practice is likely to be the QueryManager class because of the added flexibility. Another thing I like about the QueryManager class is that the results are returned as XML instead of a ResutlsTableCollection. Now, in reality if you are just querying one source such as Local Search Results, QueryManager might add a little overhead since it turns around and calls the KeywordQuery class, but I think the the benefits that QueryManager brings are worth it.

Before we begin, let’s take a look at our Federated Locations in the Search Service Application. In a typical out-of-the-box SharePoint 2010 install, you will have five Federated Locations. We can query any of these locations using the QueryManager class. However, we have to know the proper name to pass to the QueryManager to specify the location. We’ll talk about that here in a bit. Here is what your federated locations might look like. In my case, I have an extra federated location that I created myself that queries search on DotNetMafia.com.

For our first example, we will just issue a simple query against the Local Search Results location. Our first couple of lines are just like the ones we used with the KeywordQuery claass. We need a reference to the Search Service Application. In my case, I have named my application Search Service Application. Change the name of the string to match the name of your service application.

// get the query and settings service proxy
SearchQueryAndSiteSettingsServiceProxy settingsProxy = SPFarm.Local.ServiceProxies.GetValue<SearchQueryAndSiteSettingsServiceProxy>();
 
// get the search service application proxy by name
SearchServiceApplicationProxy searchProxy = settingsProxy.ApplicationProxies.GetValue<SearchServiceApplicationProxy>("Search Service Application");

We now need to create an instance of the QueryManager object as well as an object to keep track of the locations we are searching (LocationList). The QueryManager is kind of an interesting object because it really is just a LocationList with a few extra properties. There are very few settings you can specify on it other than the query. Both of these just use default constructors, we pass in the proxy once we start creating new Location objects.

QueryManager queryManager = new QueryManager();
LocationList locationList = new LocationList();

Now, we want to create an instance of a Location object which takes the internal name of the location of the federated location as well as our SearchServiceApplicationProxy. The example in the SDK had a heap of code, that loops through all of the locations on the server. I found that it was kind of overkill and can simply be replaced with a single line of code to create the Location object.

Location localSearchLocation = new Location("LocalSearchIndex", searchProxy);

As you can see here, I specified LocalSearchIndex for the name of my Location. You might be wondering where I got that value. This is the internal name of the federated location (not to be confused with the display name). You can get this value by looking at the details of the location.

Here is a table with the internal names of all of the out-of-the-box federated locations for future reference.

Display Name	Internal Name
Internet Search Results	InternetSearchResults
Internet Search Suggestions	InternetSearchSuggestions
Local Search Results	LocalSearchIndex
Local People Search Results	LocalPeopleSearchIndex
Local FAST Search Results	FASTSearch

As you can see there is not a lot of consistency between the internal names so I thought this table would be useful. Remember, we can query FAST using the same API as we do Enterprise Search. We just specify FASTSearch as the location. We’ll talk about that more in a future post as well. Now, we need to add the location to the LocationList.

locationList.Add(localSearchLocation);

Once we have a location set, we can actually specify our query. We’ll stick with my usual example of querying for accounting documents.

queryManager.UserQuery = "accounting";

Now we just need to add our LocationList to our QueryManager and we’re ready to query. We also have to pass it to the IsTriggered method as well. I’m not fully sure of the reason behind this, but I think if you don’t set it, the location will be added but not executed.

queryManager.Add(locationList);
queryManager.IsTriggered(locationList); 

Now we execute the query using the GetResults method. It returns an XmlDocument class. I am kind of surprised this isn’t an XDocument, but I guess it’s easy enough to get from one to the other.

XmlDocument xmlDocument = queryManager.GetResults(locationList);

If you have everything configured successfully, you should get some XML with results. Let’s take a look at what one of the results looks like. I make use of the File object’s WriteAll method to quickly dump the results into an XML file so I can look at it. That was one of my first blog posts there more than five years ago. :-) This way I can open it with Visual Studio and use the Format Document (Ctrl+K, Ctrl+D) command to make it readable. Here is what one of the results looks like.

<Result>
  <id>2</id>
  <workid>125</workid>
  <rank>76221694</rank>
  <title>Accounting Procedures 2009</title>
  <author_multival>Craig Johnson</author_multival>
  <author_multival>Windows User</author_multival>
  <author>Craig Johnson;Windows User</author>
  <size>22341</size>
  <url>http://sp2010/ECM/Company Documents/Accounting Procedures 2009.docx</url>
  <urlEncoded>http%3A%2F%2Fsp2010%2FECM%2FCompany%20Documents%2FAccounting%20Procedures%202009%2Edocx</urlEncoded>
  <description></description>
  <write>7/20/2010</write>
  <sitename>http://sp2010/ECM/Company Documents</sitename>
  <collapsingstatus>0</collapsingstatus>
  <hithighlightedsummary>
    This document details all <c0>accounting</c0> policies and procedures for fiscal year 2009.
  </hithighlightedsummary>
  <hithighlightedproperties>
    <HHTitle>
      <c0>Accounting</c0> Procedures 2009
    </HHTitle>
    <HHUrl>
      http://sp2010/ECM/Company Documents/<c0>Accounting</c0> Procedures 2009.docx
    </HHUrl>
  </hithighlightedproperties>
  <contentclass>STS_ListItem_DocumentLibrary</contentclass>
  <isdocument>True</isdocument>
  <picturethumbnailurl></picturethumbnailurl>
  <serverredirectedurl>http://sp2010/ECM/_layouts/WordViewer.aspx?id=/ECM/Company%20Documents/Accounting%20Procedures%202009.docx&amp;DefaultItemOpen=1</serverredirectedurl>
</Result>

This is all of the data you get back without specifying any custom managed properties. If you are familiar with querying Enterprise Search from MOSS 2007, you will know that there is a lot more data returned. I’ll point out some of the interesting elements. When a document has multiple authors, we see them all in author element delimited by a semicolor (;). Each author is also available separately in the author_multival element. Of course, we have the usual things such as a URL, the size, modification date (write), whether it is a document or not, and it’s content class. The sitename element is particularly interesting. It actually gives us the folder that the file exists in. You don’t know how much I wanted that feature in MOSS 2007. That is why I wrote the document link handler to help get the folder name of documents. If you use Excel Services or Office Web Apps, the serverredirectedurl will provide the path to open the document using Office Web Apps.

I would be a bad blogger if I didn’t tell you how to specify your own managed properties. Unfortunately, this works exactly like it did in SharePoint 2007. If you specify one managed property, you lose all of the defaults. You’ll see what I mean here in a minute. You specify your managed properties on the Location object using the RequestedProperties StringCollection. Just set the property on the Location before adding it to the LocationList.

localSearchLocation.RequestedProperties = new System.Collections.Specialized.StringCollection() { "DocumentType" };

Since I didn’t specify any other managed properties, the only thing I get back is the Id and the DocumentType managed properties. See below.

<Result>
  <id>2</id>
  <documenttype>Accounting</documenttype>
</Result> 

Not very useful. So instead, you have to pass the values of all of the fields you want. I have found that most of the elements you see in the first XML document, you can pass to the RequestedProperties parameter but not all of them. For example, to get the value of the URL, you actually specify the manager property path, and it gives you both url and urlEncoded. Here is what the line might look to get all of your managed properties and the new one.

localSearchLocation.RequestedProperties = new System.Collections.Specialized.StringCollection()
{ "workid", "rank", "title", "author", "size", "write", "path", "sitename", "description",
    "CollapsingStatus", "HitHighlightedSummary", "HitHighlightedProperties", "ContentClass",
    "IsDocument", "PictureThumbnailURL", "PopularSocialTags", "PictureWidth", "PictureHeight",
    "DatePictureTaken", "ServerRedirectedURL", "DocumentType" };

How did I know all of those properties? Well the URL thing I remember from working with the KeywordQuery class in the past. The rest, I just got from the CoreResutlsWebPart. You can also look at the Local Search Results federated location.

I was planning on showing the code to do multiple locations too, but this post is already getting long, so I think I will include it in the next post. It’s cool enough to deserve its own post. :-) I will show you two other properties that are important to know about. These properties allow us to do paging. On the Location object we can set the ItemsPerPage and StartItem property to set the number of items per page and what item to start on. For example, to start on item #21 and show 20 items, we would add the following before the Location is added to the LocationList.

localSearchLocation.ItemsPerPage = 20;
localSearchLocation.StartItem = 21;

When you get results back, the XML contains the total number of results as well as the number of results returned. In my case I only had two results in my set since there were only 22 items in the entire set.

  <TotalResults>22</TotalResults>
  <NumberOfResults>2</NumberOfResults>
</All_Results>

I assume these number are estimates in Enterprise Search, but I could be wrong. I need to test more. If you know for sure, please leave a comment.

One more thing I will mention is that exceptions are returned by the Location object. So if you don’t get results back, check Location.Exception to see what the details are. It won’t actually throw an exception. It will just return null.

I know some people (including myself) like to see all of the code together, so here it is. This is the complete example with our DocumentType managed property included with paging.

// get the query and settings service proxy
SearchQueryAndSiteSettingsServiceProxy settingsProxy = SPFarm.Local.ServiceProxies.GetValue<SearchQueryAndSiteSettingsServiceProxy>();
 
// get the search service application proxy by name
SearchServiceApplicationProxy searchProxy = settingsProxy.ApplicationProxies.GetValue<SearchServiceApplicationProxy>("Search Service Application");
 
// create QueryManager and LocationList objects
QueryManager queryManager = new QueryManager();
LocationList locationList = new LocationList();
 
// add the federated location we want
Location localSearchLocation = new Location("LocalSearchIndex", searchProxy);
 
// set the start page and page size
localSearchLocation.ItemsPerPage = 20;
localSearchLocation.StartItem = 21;
 
// add our managed properties
localSearchLocation.RequestedProperties = new System.Collections.Specialized.StringCollection()
{ "workid", "rank", "title", "author", "size", "write", "path", "sitename", "description",
    "CollapsingStatus", "HitHighlightedSummary", "HitHighlightedProperties", "ContentClass",
    "IsDocument", "PictureThumbnailURL", "PopularSocialTags", "PictureWidth", "PictureHeight",
    "DatePictureTaken", "ServerRedirectedURL", "DocumentType" };
 
// add the Location to the LocationList
locationList.Add(localSearchLocation);
 
// set the query
queryManager.UserQuery = "accounting";
queryManager.Add(locationList);
queryManager.IsTriggered(locationList);
 
// make the call to search
XmlDocument xmlDocument = queryManager.GetResults(locationList);
 
// write out the results
System.IO.File.WriteAllText("results.xml", xmlDocument.InnerXml);
Console.WriteLine(xmlDocument.InnerXml);
Console.ReadLine();

The QueryManager class is very powerful for interacting with Enterprise Search. I hope you found this post useful. In my next post, we’ll query multiple locations at a time.

How to: Use the SharePoint 2010 Enterprise Search KeywordQuery Class

CoreyRoth — Thu, 12 Aug 2010 21:49:10 GMT

By now, I’m sure you know that there have been a ton of changes and improvements in SharePoint 2010 Enterprise Search. The underlying architecture of Enterprise Search has been ripped out of the old SSP model and is now based on Service Applications. Although, Microsoft abstracted a lot of this away for us so that our old code still works, it’s worth noting and being aware of the changes. When it came to querying search programmatically in SharePoint 2007, we had a choice of using the API, Web Services, or using the RSS feed. When using the search API, we typically used the KeywordQuery or FullTextSqlQuery classes. To this day, those two posts are still in the top 20 on DotNetMafia.com. This tells me that people must be pretty interested in querying search using their own code.

Today I am going to talk about the KeywordQuery class in SharePoint 2010. Your code from 2007 will still probably work, but I thought I would tell you about some of the changes. This also sets the ground for a series of future posts that are coming about querying Enterprise Search. We’ll talk about the federated query model using the QueryManager class as well as how to use FQL to do advanced queries in FAST. Today though, we’ll stick to the KeywordQuery class.

In SharePoint 2007, we often got a reference to the KeywordQuery class by passing the URL of a site collection to the constructor. We can still do that, however, I think the new best practice will be to pass a reference to the SearchServiceApplicationProxy. The trick of course is getting that reference. First, you need to determine the name of your Search Service Application. For a typical Enterprise Search installation it is called Search Service Application. However, it can be called anything depending on how you configured SharePoint. For FAST, it might be called something like FAST Content SSA. Go to Central Administration –> Service Applications and take a look.

The proxy will usually have the same name as the Service Application, so in my case here the name of my proxy is Search Service Application. Now we just can’t get a reference to the SearchServiceApplicationProxy directly. We have to go through the SearchQueryAndSiteSettingsServiceProxy class first. According to the SDK, the function of going through this service is to ensure queries are load balanced. Here is how you get a reference to the query and settings proxy. It also assumes this code is executing on one of the servers in the farm.

SearchQueryAndSiteSettingsServiceProxy settingsProxy = SPFarm.Local.ServiceProxies.GetValue<SearchQueryAndSiteSettingsServiceProxy>();

Now that we have a reference to the settings proxy, we can get a reference to the SearchServiceApplicationProxy with the name of the proxy that we saw above. Change the name to match whatever yours is called.

SearchServiceApplicationProxy searchProxy = settingsProxy.ApplicationProxies.GetValue<SearchServiceApplicationProxy>("Search Service Application");

Now you can pass this proxy to the constructor of the KeywordQuery.

KeywordQuery keywordQuery = new KeywordQuery(searchProxy);

The rest is pretty much the same. There is one new parameter that you may want to consider setting when you have multiple search providers (i.e.: FAST for documents and SharePoint Enterprise Search for People). This parameter is ResultsProvider. It’s an enum with a value of Default, FASTSearch, and SharePointSearch. However, I believe you can use this to switch between FAST and SharePoint Search when you don’t specify the SearchServiceApplicationProxy (i.e.: you used the site collection URL). So for example when you had FAST installed, if you wanted to query People, you might set it to SharePointSearch. The examples I have seen so far leave this to default. Here is what the rest looks like.

keywordQuery.QueryText = "accounting";
keywordQuery.ResultsProvider = SearchProvider.Default;
keywordQuery.ResultTypes = ResultType.RelevantResults;
ResultTableCollection resultsTableCollection = keywordQuery.Execute();
 
ResultTable searchResultsTable = resultsTableCollection[ResultType.RelevantResults];
DataTable resultsDataTable = new DataTable();
resultsDataTable.TableName = "Results";
resultsDataTable.Load(searchResultsTable, LoadOption.OverwriteChanges);

I can then use the data visualizer to see my results. There are a few new managed properties that you get by default in the search results. I’ll talk about these more when we look at using the QueryManager in an upcoming post.

There are a lot of new properties on the KeywordQuery class and I have only begun to explore them, but here are some of the ones I’ve looked at so far. The first is EnableFQL. This allows you to submit queries using FAST Query Language. That’s a whole series of posts by itself. Just know that you can submit FQL queries using the KeywordQuery class. Two other interesting properties are EnableNicknames and EnablePhonetic. This allows you to turn off the cool people search features that are so great at finding peoples names phonetically. I’ll talk about more options with the KeywordQuery class in the future. Anyhow, I hope this gets you started using it in SharePoint 2010.

Creating a Content Source Scope Rule using PowerShell

CoreyRoth — Wed, 11 Aug 2010 18:33:03 GMT

Two weeks ago, I posted about how to create scopes and scope rules using PowerShell. At the end of the post I mentioned that there was no rule type for creating scope rules by content type. I knew there had to be a way to do this, so I created a scope rule using the UI and then I used the Get-SPEnterpriseSearchQueryScopeRule cmdlet to return all the scope rules on my content source.

$searchapp = Get-SPEnterpriseSearchServiceApplication "Search Service Application"      
$scope = Get-SPEnterpriseSearchQueryScope -Identity "File Share" -SearchApplication $searchapp       
Get-SPEnterpriseSearchQueryScopeRule -Scope $scope

From this result, the answer was immediately obvious to me. ContentSource is a managed property and we already know how to create scope rules like that using the PropertyQuery rule type. Remember how we can query by content source? Let’s give it a try and see what happens. I am going to create a new scope and have it use my BCS content source called Products. Here is what the syntax looks like.

$searchapp = Get-SPEnterpriseSearchServiceApplication "Search Service Application"      
$scope = New-SPEnterpriseSearchQueryScope -Name "My Scope" -Description "My scope created in PowerShell" -SearchApplication $searchapp -DisplayInAdminUI $true       
New-SPEnterpriseSearchQueryScopeRule -RuleType PropertyQuery -ManagedProperty ContentSource -PropertyValue Products -FilterBehavior Include -url http://sp2010 -scope $scope -SearchApplication $searchapp

I’ll only mention the relevant parameters today. If you are curious about the other ones, see the previous post where I go into detail on each one. On the New-SPEnterpriseSearchQueryScopeRule method, we set the RuleType parameter to ContentSource. Then, we set the PropertyValue to the name of the content source. In my case, I set it to Products. Executing the script above, I get the following result.

It appears to have worked, but let’s verify it in the UI.

Excellent. The new scope was created with the content source rule and it has 43 items included. Now, I can pretty much script all of my search settings in PowerShell. Once you have your scopes created, if you want to create scope display groups be sure and check out the SharePoint PowerShell Community Toolkit.

SharePoint PowerShell Community Toolkit Updated

CoreyRoth — Wed, 11 Aug 2010 15:01:32 GMT

I’m excited to see that we’ve already got some community contribution to the SharePoint PowerShell Community Toolkit. Pete Cuttriss provided us with two new methods useful for working with scope display groups. I also have the source code checked into the CodePlex TFS server now. Source control is a good thing. :-) Version 1.0.1.1 was released last night with the following new cmdlets.

Get-SPEnterpriseSearchQueryScopeDisplayGroup – Returns a scope display group
Remove-SPEnterpriseSearchQueryScopeDisplayGroup – Removes a scope display group
Remove-SPEnterpriseSearchQueryScopeDisplayGroupScope – Removes a scope from a scope display group

If you try out any of our cmdlets, be sure sand let use know how they work for you. As always we welcome your contributions. Thanks and happy PowerShelling!

Release v1.0.1.1

Follow me on twitter.

Introducing the SharePoint PowerShell Community Toolkit

CoreyRoth — Mon, 09 Aug 2010 20:04:43 GMT

This weekend, I got inspired to create what I think could be a great new CodePlex community Project. Last week, I saw a post in the forums as well a comment on the blog asking if specific PowerShell commands existed in regards to Enterprise Search. In both cases, I could not find existing commands so I thought why not create these commands. Then I thought that I should go ahead and provide a framework for other people to implement cmdlets to add to the ones I created. I took a look out on CodePlex to see if anything existed already and from what I could tell, there is nothing like this yet, so here we are. I am excited to announce the SharePoint PowerShell Community Toolkit on CodePlex. SharePoint already has 500+ PowerShell commands, but the community always wants to add more. :-) I thought this would be a great way to do it. I started out with cmdlets to do a few things in Enterprise Search since I knew there were requests from people in the community out there already.

Here are the commands I have implemented.

New-SPEnterpriseSearchQueryScopeDisplayGroup – creates a new scope display group
New-SPEnterpriseSearchQueryScopeDisplayGroupScope – adds a scope to an existing scope display group
Reset-SPEnterpriseSearchIndex – resets an Enterprise Search content index
Start-SPEnterpriseSearchQueryScopesCompilation – starts Enterprise Search scope compilation

Ok, so I know that is not that many commands yet, but how many did you expect me to write over the weekend? :-) I have a ton of ideas though, so you can expect me to add more soon. If you want to start using the commands now in your environment, download the distributable from CodePlex and use the install.bat file to copy the assembly to the GAC. You can then register the snapin inside PowerShell. More detailed installation instructions are on CodePlex.

If there is a PowerShell collaboration project out there already and somehow I missed it, let me know and I’ll be happy to add my contributions. :-) So how can you help? Create cmdlets and send them in. I’m looking for people to help contribute to the project. Not sure how to create a cmdlet yet? No problem! Check this post out to help you get started. It’s easier than you think. :-)

SharePoint PowerShell Community Toolkit on CodePlex

Missing Tag Cloud Web Part (and other Social Web Parts) after SharePoint 2010 Upgrade

CoreyRoth — Fri, 06 Aug 2010 15:23:55 GMT

I noticed this little issue after doing my last SharePoint 2010 upgrade. The client was a fan of the Tag Cloud Web Part on his My Site and wanted to bring that functionality onto one of his team sites. I figured this would be no problem, so we went to the Team Site, clicked Add New Web Part, looked under Social Collaboration only to find the Tag Cloud missing from the list. In fact, Tag Cloud was not the only thing missing. Contact Details, Note Board, and Organization Browser were also missing. I checked the web part gallery and there was no sign of the Tag Cloud present. I verified that the SharePoint Standard and Enterprise features are activated, but still no sign of it.

At this point, I decide to do some digging. I wanted to find out which feature installed this web part. After looking at a working SharePoint 2010 server, I determined that the web part comes from the file TagCloud.dwp (a version 2 web part schema on a brand new web part? Really?). I head to the 14 hive and search the features folder and determine that, this web part is deployed by the PortalLayouts and MySiteLayouts hidden features. Then it all makes since (well sort of). Since I upgraded this site from MOSS 2007, the new version of PortalLayouts has not activated on my site collection.

At this point, I had a decision to make. I could force the PortalLayouts feature to activate again or I could leave well enough alone and just manually upload the .dwp file into the Web Parts gallery. I went with the latter so I grabbed TagCloud.dwp from 14\TEMPLATE\FEATURES\PortalLayouts\DWP and uploaded it to the web part gallery and the web part worked just fine. I wasn’t sure what the other unintended results might be on the site collection had I force activated the PortalLayouts feature again so this seemed like the right decision. If you are interested in the other web parts, you can upload contactwp.dwp, profilebrowser.dwp, and socialcomment.dwp to your gallery as well. There may be other web parts missing too, but I think this is it.

A tip to remember when calling custom web services in SharePoint from JavaScript (or jQuery)

CoreyRoth — Tue, 03 Aug 2010 18:31:12 GMT

I’ve been customizing rendering templates a lot lately using a wide variety of scripting technologies such as JavaScript, jQuery, and SPServices. One requirement I had with all of these rendering templates was to call a custom web service to populate a drop down list. This is relatively easy to do and there are plenty of resources out there on how to deploy web services with SharePoint. I thought I would make a point today though to talk about this issue since I seem to forget about it every time. :-) The issue is that you build and test your pages and they work great as an administrator. However, once an end user tries the page, the script on the page doesn’t work. With any scripting issue, I bust out FireBug in Firefox and take a look at the page. It shows the web service call which works great for the administrator but when the user tries it, the result is 403 forbidden.

Obviously, I need to grant permissions, but where? Since the end user is on the SharePoint site, the identity that the JavaScript is calling with is that of the user. This of course assumes, you are using NTLM or Kerberos and not doing forms authentication. The place we have to grant permissions may not make sense to you at all, but the general consensus is that you grant Read permission to the Authenticated Users group on the bin folder of your web application. This is the case at least when you are deploying your code to the bin folder (and using Code Access Security). I don’t believe that web services with binaries deployed to the GAC should have this issue but I could be wrong. Here is what the permissions look like on your bin folder.

Any how, I hope this helps should you run into this issue. I don’t foresee any potential security issues by granting this group access to the bin folder, but if you think there are any associated risks, I would like to hear about them.

Where did my Search Scopes list on the home page go in SharePoint 2010?

CoreyRoth — Mon, 02 Aug 2010 16:45:19 GMT

On a recent SharePoint 2010 migration I completed, this was one of the first questions I was asked. After seeing versions of SharePoint all these months, it never occurred to me that the dropdown list wasn’t there any more until someone pointed it out.

Luckily, it’s easy enough to change this and I found some new settings along the way. This box uses the built-in (not supposed to be customized) OSSSearchResults.aspx. However, to use scopes, we need to create a Search Center. To do this, we can go to Site Settings –> Search Settings to make some configuration changes. This page was here in 2007, but I was pleased to see some new settings here in 2010 that I had not noticed yet. We set the path to our search center just as we did before by specifying the Pages library of the search center.

There is a new setting to specify what the site collection search dropdown displays. Now there is a boat load of options for you to pick from. The default is Do not show scopes dropdown, and default to contextual scope. Here are all of the options you have to pick from.

The options are fairly self-explanatory with maybe the exception of default to ‘s’ URL parameter. I’ve talked about this in previous posts, but what this does is tells the drop down list to default to the scope that was passed via the ‘s’ query string parameter. This means that when you perform a search and are looking at the results page, the drop down will show the scope you just searched for. As for default to contextual scope, this refers to the This Site or This List search options that you might be used to seeing from SharePoint 2007. What option you pick here is largely up to you. I usually end up going with Show and default to contextual scope. However, I think Show, do not include contextual scopes, and default to ‘s’ URL parameter can also be a good choice at times.

We have one more setting that I want to mention on this page called Site Collection Search Results Page. This allows you to override the URL used by contextual search. In SharePoint 2007, all contextual searches went to the application page /_layouts/OSSSearchResults.aspx. If you ever wanted to customize this page, you had to modify a built-in page which of course is unsupported. Another side effect was that it changed the look of that page on every site on that server. This new setting is great because it means we can change the contextual search page on a per site collection basis and simply upload a new application page without affecting the original page.

To configure what scopes show up in the dropdown on the home page, you need to modify the Search Dropdown scope display group. Modify this group as you see fit and then you should see it on the home page. In this case, I added my file share scope to be displayed on all of my pages.

This is what my pages look like with the dropdown.

So I can kind of see why they changed the default option for this. The interface just looks cleaner without the dropdown box. However, I think a lot of users are used to seeing it there, so this shows you how to add it back in the event that you ever need to.

Creating Enterprise Search Scopes with PowerShell

CoreyRoth — Thu, 29 Jul 2010 19:47:21 GMT

I posted a while back on how to do your Managed Property mappings in PowerShell, so I wanted to follow up with how to add search scopes next. I have to give props to the SDK team because they have done a pretty good job documenting all of these PowerShell commands. They even provide examples which I like a lot. When trying out command sto create scopes and their associated rules, I ran into a few things that I wanted to share. You will want to put all of these commands into a .ps1 script file. You can create your script file in PowerShell-ISE or just use notepad. We start out by getting a reference to the search application.

$searchapp = Get-SPEnterpriseSearchServiceApplication "Search Service Application"

At this point, we are ready to create a new scope. It’s pretty simple. To create a scope, we use the New-SPEnterpriseSearchQueryScope command. Here is my command to create a scope called My Scope. One thing to note here is that DisplayInAdminUI is a required parameter. It turns out you can create hidden scopes with PowerShell even though you can’t do it through the UI.

$scope = New-SPEnterpriseSearchQueryScope -Name "My Scope" -Description "My scope created in PowerShell" -SearchApplication $searchapp -DisplayInAdminUI $true

We now want to create scope rules for this scope. I assign the resulting object from the above command into a variable called $scope so that we can pass it to the scope parameter of the New-SPEnterpriseSearchQueryScopeRule command. When creating a scope rule through the UI, you have four choices: All Content, Property Query, Content Source, and Web Address. We’ll start with the simplest one, All Content. For this, we just specify a RuleType value of AllContent. All commands to create new scope rules require a URL. It’s not entirely clear what this URL does since it’s not something you enter in the UI when you create a rule. From the SDK, it simply states that it “specifies the results URL that is associated with the query rule.” I guess you could give it a path to the results page in your search center. For now, I just specify the path to the server and it seems to work.

New-SPEnterpriseSearchQueryScopeRule -RuleType AllContent -url http://sp2010 -scope $scope

Next, we’ll create a scope rule using the PropertyQuery Rule Type. It requires a few more parameters. The ManagedProperty parameter specifies the name of your managed property. In my example, I am going to use a property called Color. The PropertyValue parameter specifies the value. I want to see products that are red, so I’ll specify Red here. With any scope rule, you can specify whether the results from the rule should be Included, Required, or Excluded. We specify this with the FilterBehavior parameter. This parameter is required when using this PropertyType (note the SDK says it is optional). Also, when using a property query, the SearchApplication parameter is required too so just pass it the value $searchapp and it will work fine. Here is the command.

New-SPEnterpriseSearchQueryScopeRule -RuleType PropertyQuery -ManagedProperty Color -PropertyValue Red -FilterBehavior Include -url http://sp2010 -scope $scope -SearchApplication $searchapp

When you execute the command it gives you some basic info about the rule you set up as well as an estimated count.

Setting up a scope with a RuleType of Url took me a bit longer to figure out. This is because there is a parameter called UrlScopeType and the SDK didn’t say what value was expected there. I had to do some digging. The SDK, didn’t say what values it was expecting nor did the Get-Help command. I did some reflecting and finally found an enum that had the answer. The values it wants are Folder, HostName, or Domain. This of course makes sense when you go back and look at the UI and see the parameters you specify there.

The other parameter you need to know about here is MatchingString. You specify the value to the folder, hostname, or domain you want to use. In this case I am setting up a rule for a particular subsite.

New-SPEnterpriseSearchQueryScopeRule -RuleType Url -MatchingString http://sp2010/bcs -UrlScopeRuleType Folder -FilterBehavior Include -url http://sp2010 -scope $scope

So now we can create rules for all content, a web address, and a property query. However, if you have ever set up a scope before, you know there is one more type. That type is a content source. The SDK, didn’t have this type listed so I looked around in reflector again and found that we could specify a value of ContentSource for the RuleType parameter. However, when I tried to specify that parameter, it didn’t work. I took a look at the code and discovered that there is no code implemented to create a content source scope rule. After doing some experimenting in PowerShell, I did discover the answer, but I’ll save that for the next post where I will show you how I figured it out.

Remember you can add multiple rules at a time to one scope. Just put them all in one script file and run it. Also if you need to delete your Scope, you can use the Remove-SPEnterpriseSearchQueryScope command but you have to pass it an actual scope object which you can get with the Get-SPEnterpriseSearchQueryScope command. Here is how I deleted my scopes as I was testing.

$searchapp = Get-SPEnterpriseSearchServiceApplication "Search Service Application"      
Get-SPEnterpriseSearchQueryScope -Identity "My Scope" -SearchApplication $searchapp | Remove-SPEnterpriseSearchQueryScope

You can do so much in SharePoint with PowerShell. This is just one more thing, I won’t have to manually configure any more. The SDK does a great job documenting all of the commands out there (although I would like to see that info on the UrlScopeType parameter added some time :) ). Try some of them out and you’ll be amazed at what you can accomplish.

How to: Change your namespace in a Silverlight application without breaking everything

CoreyRoth — Fri, 23 Jul 2010 20:16:41 GMT

The first thing I do when creating a new project in Visual Studio (regardless of type) is change the project name, assembly name, and default namespace. I like for the names of all these to be consistent. However, I have noticed in Silverlight, you must make changes in five different places for everything to work alright otherwise you will get errors. I’m no Silverlight expert, but I thought this post would be useful for people like me who only dabble in it from time to time.

When you create your new Silverlight project, right click on the project name and bring up its properties. Go ahead and change the default namespace and assembly name just like you would in any other project.

In my case, I change from a namespace of SilverlightApplication1 to DotNetMafia.Silverlight.Test. Here you can see the new assembly name and default namespace. There are two other options to set here, but we will have to come back to it.

At this point, we want to correct the namespace in the existing classes. Let’s start with App.xaml.

I have highlighted the section, I need to change, x:Class. I’ll change it to DotNetMafia.Silverlight.Test like we see below.

Now, we just need to change the namespace in the code behind file App.xaml.cs.

Now, we have to do the same thing to MainPage.xaml. Change the namespace here on x:Class as well.

Then, you will change the namespace of the code behind file MainPage.xaml.cs just liked we did before.

At this point, your code will compile. However, it will not run. If you try to debug it you will likely get a blank page and if you are using Internet Explorer, you will probably see a script error in the toolbar.

Clicking on the error, you can see the following details.

I have to say they have really improved the way you view script errors. Here is the text of the error.

Message: Unhandled Error in Silverlight Application
Code: 2103
Category: InitializeError
Message: Invalid or malformed application: Check manifest

Line: 54
Char: 13
Code: 0
URI: file:///C:/Code/SilverlightApplication1/Bin/Debug/SilverlightApplication1TestPage.html

Remember, I said we had to change an additional setting in the project properties?

This is the problem here. We change the startup objects namespace but never update the project properties to match. You should be able to pick the new namespace of your startup object from the list. This is also a good time to change the name of your .XAP file if you are so inclined. Here is what my project properties looks like when I am done.

At this point your application should compile and run. Now I can view my beautiful Hello World Silverlight application.

Great app huh? Any how, I hope this helps should you encounter the error above or run into issues changing your namespace. It’s pretty simple to do, but you’ll definitely get errors if you don’t get all of your changes made.

Some handy keywords you might find useful in SharePoint Enterprise Search

CoreyRoth — Tue, 20 Jul 2010 20:30:47 GMT

Enterprise Search is one of my favorite SharePoint topics to speak about. Often in my talks, I use various keyword queries to display results that people often end up asking me about. Today, I thought it would be useful to show you some of the most useful built-in keywords that you can use to troubleshoot search results or even build custom scopes with. I see people post in the forums all the time about how to do many of the queries I have in the list below, so I figure this is will be a good post to refer people to. You can use pretty much all of these with SharePoint 2010 or MOSS 2007 right out of the box (once you crawl).

The ContentSource keyword is incredibly powerful. You can use it to verify that results exists for a given content source. For example, if you were having issue crawling a file share and you wanted to see if any results were present or not, you could use this keyword. Start by identifying the content source in question on your content sources page in your Search Service Application.

Note the spelling of your content source exactly. If I wanted to return everything from the file share, I would issue this query.

ContentSource:”File Share”

On my server I get results like this.

Keep in mind that you can combine these keywords with other ones. For example if I only want accounting documents on the file share, it would look like this.

Accounting ContentSource:"File Share"

To query all documents on the SharePoint site itself, you could use a query against the Local SharePoint Sites (Local Office SharePoint Sites on MOSS 2007) content source. Be warned that this could return a large number of results.

ContentSource:”Local SharePoint Sites”

Another property, more people are more familiar with is IsDocument. This handy property is a great way to filter out items that are not documents (i.e.: folders, list items, and pages). You pass a value of 1 for true. In the example below, I return all documents on my SharePoint server.

IsDocument:”1”

If you have spent time building custom scopes, you might be interested in the Scope keyword. This makes it easy to query everything in your scope at once. You can use this to build advanced queries or just for simple troubleshooting of the scope. In this example, I have a scope with rules only to show items from a BCS content source with a property called Color set to red.

Scope:”Red Products”

A lot of times people ask questions about how to just show results for a given site or site collection. Maybe even a specific document library. You can do that with contextual search, but you can also do that with the Site keyword as well. Simply pass it the URL to the specific point on your site and it will filter the results. For example, if I just want to see results from my ECM subsite, I would use the following query.

Site:”http://sp2010/ecm”

Sometimes you might want to see all documents a particular user modified. The author keyword is a great way to find things when you want to see all documents from someone that recently left the company. For example, to see all of the documents by our fictitious CFO, Christina Murphy, we would use this query.

Author:”Christina Murphy”

If you know you only want to see documents of a certain content type, why not query for it? You can with the ContentType keyword. For example to search all documents of my custom type called Custom Document, I would use the following query.

ContentType:”Custom Document”

You can also write queries based on list template such as a document library, task list, or your own custom list template with the ContentClass keyword. Simply use any known content class with the keyword (here is a list). For example, to search only items in a task list, I would issue the following query.

ContentClass:”STS_ListItem_Tasks”

Want to see a query that only has Excel spreadsheets? No problem. Use the FileType keyword. Don’t include the period on the extension. You can use multiple FileType keywords together to span multiple types (i.e.: xlsx and xls).

FileType:”xlsx”

The last keyword I will cover is Write. You use it to query documents based on modification date. You can’t take full advantage of this keyword in MOSS 2007, but in SharePoint 2010, you can do some cool things by using the new operators >,<,>=,<=. This allows you to write queries to see all documents written since the beginning of the month for example. Your date should be in quotes and make sure there is no space between the keyword and the date itself.

Write>”7/1/2010”

These are all keyword I use on a regular basis. Remember you can combine most of these together to provide vary narrow search results. You could also make use of these by writing your own advanced search control. I hope these queries are useful the next time you use Enterprise Search. If you can think of any other good ones I left out, please leave a comment.

Bringing my PowerShell talk to Oklahoma City next Monday

CoreyRoth — Tue, 20 Jul 2010 18:32:06 GMT

I am excited to say that I am returning to Oklahoma City next Monday (7/26) to speak about using PowerShell with SharePoint. This fun talk will show you just enough PowerShell to be dangerous. You will learn the basics of using it, how to write scripts, and even see how to build a cmdlet. The talk is at OKC CoCo at 6 pm. I look forward to seeing you all then.