Using Query Rules, Result Types and Display Templates for a Custom Search Sales Report in SharePoint 2013 - Share-n-dipity - Site Home

<Standard Disclaimer: Blog formatting sucks. Do your eyes a favor - download the attachment, which is the Word document version of this.)

UPDATE: a few things have changed for RTM so I am updating this post and adding the artifacts I used to create everything. Okay, a long title for today’s topic but we’re going to cover a lot of good material. SharePoint 2013 has a number of outstanding features that allow you to really use and customize search results like never before. I’m not going to try and give a comprehensive overview of all of them, or even significant details on the components we’re using in this posting because they are capably covered elsewhere. However I will give you a starting set of search results, give a brief overview of each of these features, and then walk through the process of using them to create a very cool and customized search result that demonstrates how all of these pieces work together.

First, let’s start with a view of what the search results look like when I run a query for “sales reports” in my farm (NOTE: this is a beta 2 build and the final release may look differently):

This is the out of the box experience with search results, and it looks good. However, we’ve added these features to help you really enrich the end user searching experience in SharePoint 2013, so let’s add a bit to our scenario here. We have several divisions throughout our company, and a number of them are responsible for managing sales within their division. Each division uses a standard Excel template to report their sales activity over time. In addition each division has one person that is responsible for managing the customer relationships and reporting their sales information. So how can we use these features in SharePoint to help standardize and bring out important information about the division and its customers? We’re going to take a multi-prong approach to solving this.

Custom Content Type

To begin with, we’re going to create a custom content type for everyone that uploads sales reports into SharePoint. I begin by creating the site columns that I’m going to use – these are the fields that I want to be able to surface in my search sales report. For our scenario I’ve defined the following site columns that we’ll be using:

Site Column Name	Type
Account Manager	Single line of text
Direct Reports	Number
Sales Region	Choice: Northamerica, EMEA, Asia
Top Accounts	Single line of text
Total Accounts	Number

Next I created a content type that includes all of these site columns. I created the content type in the publishing hub site associated with my Managed Metadata Service so that it gets pushed out to all my subscribing sites in the farm, which in my case is all sites in all web applications.

The next step, which was manual in my case, was to add this content type to the list of available content types for each division’s site collection, in the document library where they store sales reports (among other things). Obviously there are ways to automate this, but for brevity’s sake I did it manually. Okay, so our first step is done – we have a custom content type, it’s deployed, and as sales reports are added to these document libraries, the Account Managers can just identify a sales report when they upload it and fill out the metadata.

Managed Properties

Now that we have these sales reports populating our sites, we need to create managed properties from the sales report custom site columns. UPDATE: In the example included in the .zip file with this post, I just added site columns and used the managed properties in a single site collection created automatically by SharePoint. There’s nothing really new here in SharePoint 2013 compared to SharePoint 2010 for purposes of this scenario – you need to do a full crawl to create the crawled properties, then create managed properties that are mapped to those crawled properties, then do another full crawl. I say there’s nothing different in this scenario because we are implementing a farm-wide solution. However SharePoint 2013 does have a fabulous feature that lets site collection admins mark their site collection, or site, or even list for a full crawl. This saves you from having to do an enterprise full crawl when you want to do this at a more narrowly scoped level, which is really awesome. I’ll try and cover that feature in another post – I just wanted to point out that it exists, but isn’t applicable in this case because our solution scope is for the whole farm.

Query Rules

At this point we have sales reports that are using a custom content type, and we have managed properties that are populated with the metadata values for those sales reports. The next thing we want to do is to make sure that these items get “noticed” when someone is doing a search for something that should likely include one of these sales reports. SharePoint 2013 has the perfect feature for this and it’s called Query Rules. Query rules have three main components:

Conditions – the conditions for a query rule determine when a rule will fire. There are a number of different variations that you can do with rules. This is just a brief run through of them but the possibilities are HUGE for using these, and in fact you will see a number of these are shipped out of the box. One other interesting twist – if you want a query rule to fire on EVERY search result, then you just have no conditions. You can of course do the opposite, and have multiple conditions as well. You can probably quickly see how you can get lost in the scope of possibilities here. The conditions you can use for a query rule include:
1. a query that starts with or ends with a particular word or phrase
2. a query that contains an action word (words that you define that are typically verbs, like “download”, etc.)
3. a query contains a word that’s in a managed metadata term set (some very COOL things you can do with that)
4. a query that is common in another result source, like video results, document results, etc. – it can be whatever because you define and create result sources
5. the results contain a commonly clicked result type, like discussions, Excel spreadsheets, etc.
6. advanced rules, where you can go crazy with regular expressions, split the query up into action terms and subject terms (what’s left over after action terms are removed), etc.
Actions – actions are what you are going to do when the conditions for your query rule have been met. You have three different choices here as well; you can:
1. Add Promoted Result – this is similar to how Best Bets worked in SharePoint 2010 and Visual Best Bets worked with FAST Search for SharePoint 2010. You can add a new link that will show at the top of all the search results. You can choose to have the link rendered as a hyperlink or as a picture – thus the analogy to a Visual Best Bet.
2. Add Result Block – a result block is where you execute an additional query and return and render the results along with the original search results. It’s called a block because the results are rendered all together, in a block. You can have the block show up at the top of all the search results, or you can have it intermingled with the other search results based on rank. It’s important to note that this DOES NOT MEAN that it does relevancy ranking between the two queries. It just means that the block as a whole will have a rank along with all the other local search results. When you click on any item in the result block, that click action is recorded locally and the block as a whole becomes more relevant. So if items in the block get lots of clicks, then over the time the block will start moving up in the results because it will have greater relevancy that individual results that are not selected as frequently. There are actually a TON of things you can do to configure your result block, but as I said earlier, I’m not going to deep dive on that here and now.
3. Changed ranked results by changing the query – this rather verbose option does just what it says. You can actually go in and change the query that was requested any which way you want. You can add additional criteria, you can remove terms, you can use XRANK to modify the ranking – the options are pretty wide open here.
Publishing – publishing is what allows you to control if and when a query rule is used. For example, you may want to create a set of rules that you want to fire on your day after Thanksgiving sale. However, you don’t want them going off until that day actually arrives. So you can create the rules, but configure the publishing so that rule is inactive. Or you can make the rule active, but configure it to not start until a particular date and then end at a later date.

Okay, that was the “brief” rundown on what query rules are, so how will we use them here?

UPDATE: this query rule was in beta 2 but did not make it into RTM. You can manually create a rule like it by using an Advanced Text Match and selecting Query contains one of these phrases:, then adding this: analysis;cube;dashboard;dashboards;data;database;report;reports;sales. Check Start of and End Of query matches checkboxes, and uncheck Entire query matches exactly. Select the last radio button to Assign match to {actionTerms}, unmatched items to {subjectTerms}. For the Action, add a result block that issues a query for the {subjectTerms} against the Local Reports and Data Results result source. Create the rule for the Local SharePoint Results result source.

Well it turns out that there is a query rule that ships out of the box that is going to take care of this for us. I actually made the rule inactive when I took the screenshot above so you could understand the impact of it better. In our case, we want queries in which someone might want to see a sales report to have our division sales report pop up and be noticed. So, go into your Search site collection, Site Settings, and click on the Query Rules link. In the Select a source dropdown, click on it and select Local Reports and Data Results; you will see a query rule called Reports and Data. UPDATE: Select the Local SharePoint Results result source if you are creating your own rule as described above. Click on it and select View from the drop down menu to see how the rule is constructed. It works like this:

Condition – the condition for the rule is that the query contains one of these words at the start or end of the query: analysis;cube;dashboard;dashboards;data;database;report;reports;sales. Notice that you see both reports and sales in there. So if someone does a query for “sales report” then this query rule will execute. That sounds perfect – if someone searches for “sales report” then we DO want them to see our division sales reports. As part of this rule it assigns the match to the action terms, and everything else is assigned to the subject terms. In this case it assigns “reports” to the action term, and “sales” to the subject term.
Action – the action for this rule is just running another query based ONLY on the subject terms. So based on the condition above it will run a separate query just for “sales”. It’s going to add a block that is always returned on top of all the other results. BUT…it’s ONLY going to search for sales in the Local Reports and Data Results source. This is a result source that also ships out of the box, and effectively just returns Excel documents (file extension ends with .XLSX, XLS, etc.). Great, so it’s query to run query for “sales” only against Excel documents.
Publishing – the rule is active with no start or end date restrictions, so it always fires.

I re-enabled that query rule, so now this is what the results look like when I do a query for sales reports:

Cool, this is getting better – the query rule has kicked in and now we’re getting our documents based on our custom sales report content type showing up in the top of the results – that’s great! However, we still aren’t showing any of the metadata about the documents and that’s what really brings this all together.

UPDATE: To simplify this process, you should do the following:

Copy the query rule I described above.
For the Action, instead of issuing a query for {subjectTerms} against the Local Reports and Data Results result, change the query criteria to ContentType:”Sales Report”.
Save this new query rule, and make the other one inactive.

When you’re done you have one active query rule that retrieves only items that are based on our custom Sales Report content type.

Display Templates

In SharePoint 2010 if you wanted to render a particular item differently, it was a fairly arduous process of going in and modifying one HUGE chunk of XSLT in the core results web part. You got to practice your awesome XSLT skills and try and find the right place in that enormous document where to insert your custom code. Overall, it was a less than joyful experience.

In SharePoint 2013 – queue the music – we have display templates and what an improvement they are. No longer do you need to carry your XSLT Zen about you, now you can create your custom display code as straight HTML – yahoo! In fact, for this posting I actually used Adobe’s Dreamweaver CS6 to create the “code” for my custom display template. So, how do display templates work?

With a display template, you have a few different things to keep track of:

Managed properties – you need to tell it what managed properties you need retrieved at query time. Those properties you can then use in your HTML, using a method I’ll describe below.
External JS and CSS – if you have any javascript or CSS files you want used with your display template then you can externalize them and add them to your display template.
Inline JS – you can also use inline JS in your display template. You just need to make sure that it is below the first <div> in your display template – also more on that below.
HTML – this is where you create the actual HTML for your display template that will render the results.

For our display template, we are going to pull together the attributes from the custom sales report content type we created so that we can have it shown in the search results like this:

Let’s get started – to begin with, you will pretty much always want to start with an existing display template when you are building a new one. If you go into your search center site collection, Site Settings, then click on Master pages and page layouts link. When you get into the library, click on the Display Templates folder, then click on the Search folder. In there you will find all of the display templates that we ship out of the box. You will likely see a number of files that have the same name, but either a .html or .js suffix. All you care about are the .html files – the .js files are generated automatically when you upload an .html file. NOTE: If you do not see the HTML files, then you are probably not working in a search center site. If you want to do this in a non-search site for some reason, enable the Publishing feature in the site collection. In this case, since the display template is for Excel files I started out with the Item_Excel.htm file. I downloaded a copy locally, renamed it SalesReport.html, and then opened it in Dreamweaver.

Next I add my managed properties. There is a tag called mso:ManagedPropertyMapping where you put in the managed properties your display template requires. I just added mine to the end of the list, using the same format as the rest of the managed properties – it looks like this:

UPDATE: Just an FYI – these managed property names assume you create them yourself in the Search Service Application. In the example display template included in the .zip file with this post, it uses the managed property names
you get when you just add site columns and let SharePoint created the managed properties in a single site collection for you automatically.

<mso:ManagedPropertyMapping msdt:dt="string">'Title':'Title','Author':'Author','Size':'Size','Path':'Path','Description':'Description','LastModifiedTime':'LastModifiedTime','CollapsingStatus':'CollapsingStatus','DocId':'DocId','HitHighlightedSummary':'HitHighlightedSummary','HitHighlightedProperties':'HitHighlightedProperties','FileExtension':'FileExtension','ViewsLifeTime':'ViewsLifeTime','ParentLink':'ParentLink','ViewsRecent':'ViewsRecent','FileType':'FileType','IsContainer':'IsContainer','ServerRedirectedURL':'ServerRedirectedURL','ServerRedirectedEmbedURL':'ServerRedirectedEmbedURL','ServerRedirectedPreviewURL':'ServerRedirectedPreviewURL', 'AccountManager':'AccountManager', 'SalesRegion':'SalesRegion', 'TotalAccounts':'TotalAccounts', 'TopAccounts':'TopAccounts', 'DirectReports':'DirectReports', 'ContentType':'ContentType'</mso:ManagedPropertyMapping>

As you can see, I added AccountManager, SalesRegion, etc. – all of the properties I had created for my custom content type. After that I scrolled down to where it says this:

_#=ctx.RenderBody(ctx)=#_

</div>

And I deleted everything between the outer DIV tags (which I’ve highlighted in red above). Now I can get down to writing my HTML. Before I do that, there are three things you should know before you start:

You will probably find it easiest to keep a <style> tag in the <head> section of your display template. For reasons I won’t go into now, those style tags will be discarded when your display template is rendered, so you ultimately need to copy out your style tags and put them in a separate CSS file. However, while you’re actually creating your HTML you can use and modify your style tags to make sure they reflect the look and feel that you want your display template to have.
As mentioned above, you can add what I called inline javascript, which really means that you can add javascript code as long as it’s in the correct location. The correct location for a display template means that it must be placed after the first DIV tag in your display template. Also, it needs to go between an opening and closing tag set that looks like this: . I’ll explain more about custom tags below, but suffice to say if you do not have your javascript between these tags it will not execute. One thing that is VERY cool about this though is that you can use variables you define in your inline javascript when you create the HTML that will be output. I’ll explain that more in the next item.
To emit a managed property or variable from any inline javascript you’ve created, they need to be enclosed in an opening and closing tag set that looks like this: _#= and =#_. The managed properties are all available in an object called ctx.CurrentItem. For example, to emit the AccountManager managed property, I would add this tag: _#= ctx.CurrentItem.AccountManager =#_. If I had javascript that looked like this: var foo = “The current Account Manager is “ + ctx.CurrentItem.AccountManager + “.”;, then to emit “foo” I would add this tag: _#= foo =#_. This gives you complete flexibility to add or process data to work for your display template.

Okay, now that you understand the mechanisms for creating the HTML, here is what I used to create the display template I showed above; in the head tag I added these style attributes:

<style>

.ReportDiv

{

float:left;

}

.ReportText

{

font-family: Verdana, Geneva, sans-serif;

font-size: 12px;

}

.ReportHeading

{

font-weight: bold;

}

.LogoImg

{

margin-top: 15px;

width: 118px;

height: 101px;

}

.MasterDiv

{

float:left;

height: 215px;

width: 342px;

background-repeat: no-repeat;

background-image: url(http://sps/sites/search/PublishingImages/SalesReportBackground.PNG);

padding: 15px;

}

.DetailsContainer

{

background-color:#CCC;

}

</style>

The main thing worth noting here is really just that the image I use for a background in the account manager details is one that I’ve already uploaded to my site; everything else should be fairly self-explanatory. UPDATE: I’ve highlighted these elements in the post to make sure you understand that in order to get this to work in your environment, you need to a) upload the image and b) update the SalesReport.css and SalesReport.html files with the location where you added them. Again, by having this style tag in my <head> section I can see exactly what the layout is going to like while I’m designing it in Dreamweaver.

Next let’s look at the HTML itself that is used to create the display template:

<div>

</div>

<a href="_#= ctx.CurrentItem.Path =#_" class="ReportText">_#= ctx.CurrentItem.Title =#_</a>

Account Manager:

</span>

_#= ctx.CurrentItem.AccountManager =#_

</span><br/>

Sales Region:

</span>

_#= ctx.CurrentItem.SalesRegion =#_

</span><br/>

Total Accounts:

</span>

_#= ctx.CurrentItem.TotalAccounts =#_

</span><br/>

Top Accounts:

</span>

_#= ctx.CurrentItem.TopAccounts =#_

</span><br/>

Direct Reports:

</span>

_#= ctx.CurrentItem.DirectReports =#_

</span><br/>

<br/>

Details:

</span>

<br/>

_#= Srch.U.processHHXML(ctx.CurrentItem.HitHighlightedSummary) =#_

</span>

</div>

I’ll skip talking about the formatting features of this content and just focus on the data. You can see where I’ve plugged in the managed properties throughout, using the _#= and =#_ tags. You’ll see that it is pure token substitution so I can even use it directly in my <a> href attribute above (as opposed to doing this with XSLT, which is far more involved). The rest of the fields are just plugged into the appropriate DIV or SPAN tag for formatting. The one “special” case shown here is for the HitHighlightedSummary, which is using a built in processing component that comes with search. At this time I don’t have a complete list of components and methods and what they do, but I add this one only because if you don’t use it, your summary will not be hit highlighted. So make sure you use this method for that purpose.

Now that I’ve got everything working, there is one other thing I need to do to my markup. I copy everything between my <style> tags and put them in a new CSS document called SalesReport.css. In my master page gallery, in the /Display Templates/Search folder, I created another folder called styles. I uploaded my SalesReport.css file into that directory. To use it now in my display template I have to add a new script tag. It should be below the <body> tag, and above the first <div> tag. I use a SharePoint javascript function called $includeCSS to get my CSS file pulled down for the display template. So my opening markup looks like this:

<body>

$includeCSS(this.url, "./styles/SalesReport.css");

</script>

UPDATE: Once you’ve made these changes, you need to save your display template and upload it to the /Display Templates/Search folder. If you are uploading the SalesReport.html that is included with this post, you MUST be sure that when you check the file in you change the content type from Document to Item Display Template; otherwise it cannot be used.

All right, so we are code complete now, but how do we get our custom display template to be used?

Result Types

Result Types are the way in which you get display templates invoked, based on a set of rules. The rules are pretty straightforward to use – you can have them fire for a specific pre-defined type of content, like an email, a PDF, a Word document, a SharePoint Wiki, etc. In addition to that you can only also only have them used when the query is for a specific result source. Finally, you can also choose any managed property as criteria for the rule, with any common comparison. For example, you could have a result type rule only fire when the AccountManager field contains “Vice President” if you wanted to have a different display type used for your VPs. In our case, we want our result type to fire only when the content type equals “Sales Report”. So we add a condition for our result type that says when ContentType equals “Sales Report”.

UPDATE: I was hoping this would work for RTM but in fact it does not. You have two options here: 1) you can change the equality condition to “Contains” instead of “Equals”. This is because the content type string actually ends up being a long string, the last part of which is “Sales Report” in our case. Alternatively, if you only wanted it used with a single query rule, then in the query rule properties you can select the display template to be used for
displaying items.

As you can see we could even add multiple values for the match. We can also add multiple properties, that get AND’d together. For this scenario ContentType is all we need. Once we’ve defined the conditions, we can configure the Action for the rule. For a result type, the Action is just a drop down with all of the available display templates. All I need to do is to pick my Sales Report display template from the dropdown, and click the Save button to create my result type. Again, if you’re not sure how to create result types then take a look at all of the result types that come out of the box in SharePoint. You can pick up lots of good ideas for new result types by looking at some of the existing ones.

Bringing It All Together

Now we have all the pieces in place – a custom content type that captures the metadata we want, some managed properties to extract it out and put it in the index, a query rule that ensures our sales report content pops up to the top of the list when someone queries for “sales report”, a custom display template that shows off the metadata we captured in a really unique and useful format that doesn’t look remotely close to a typical search result, and a result type that makes sure our display template is used when our custom content type is returned in search results. The final results when we’re finished now look like this:

That concludes this introduction to some of the very cool features in SharePoint 2013 for presenting search results. Hopefully you have a feel for the power in these features and can find many useful and interesting ways to use them.