Create custom "like" filter for Analytics report

Once I had requirements from customer to have ability for filtering results in "site search" report by some "like"-based criteria.
The project has 2 kinds of search: global search (overall site search) and search in the specific section (lets call that section "Answers").
Both searches track standard "site search" event which allows us to see its results in the analytics report. But the report shows a mix of global and "answers" search and it is very inconvenient to recognize them.

We have great ability in Sitecore OMS for creation custom filters.
There is very useful information from Chris Bushnell describing creating report filters. Unfortunately I've found it too late and had looked into this process by myself, using reflector, standard filters and my luck :)

First what I started to think was - "Using what criteria I can distinguish the "Answers" searches from all searches bunch?". Fortunately there is the standard OMS event "search" which tracks every time searching performs. And in my case that event tracks for "Answer" search programmatically:

The code line shows that search event tracks with the specific text (the second parameter of TriggerEvent method). That text always has the prefix "Sitecore Answers search:". It means I can use the text fragment in "like" clause for my custom filter.

First of all I should create new filter item under "/sitecore/system/Settings/Analytics/Filters/Criteria" root. The item must be based from "/sitecore/templates/System/Analytics/Filter" template. Let's call that item "where visitor is searching within Sitecore Answers". There are also 3 fields which should be filled in:
  • Header: shows in filters editor dialog.
  • Text: shows in filter editor dialog, description area. It contains usually a macros. In this example it is something like "visitor is searching within Sitecore Answers with page event text like [Text,,,text]". The clause "[Text,,,text]" is a macro which is used for passing value entered by user to filter implementation. All what I need just take care about public auto property with the name "Text" in my filter class. 
  • Type: contains the namespace, class name and assembly name of filter implementation (will be described below)

Create filter implementation. It should be class based on FilterBase or OperatorFilterBase classes. The second one should be made as base class if we are going to use comparison operators like ">", "=", "<=" etc.
This post describe the filter with "like" operator so we just can use FilterBase base class.

The custom filter implementation based on FilterBase class and have 2 methods  overrode:

ApplyFilter method is used for adding where clause to sql command.
      • First parameter is placeholder name which should contain the name of placeholder exists in appropriate analytics report. In my example I used existing "{Browser}" placeholder (In order not to make changes in standard report "*.mrt" file I decided to use that placeholder but it is recommended to make a new one for your purpose).
      • Second parameter - clause string. In the example this string make "like" operation between "Text" field of "PageEvent" table and string line passed from filter editor through "public string Text" property. The value of "Text" property should be processed by "EscapeQuote" method in order to handle possible single quote symbols in input string.
      • Third parameter is the boolean flag which shows whether the filter can be reversed.
    IsApplicable method checks whether the filter is available for current report. If the placeholder is present in current report then filter is showed in the list of available ones. It means that in my sample the custom filter can be used only for reports which have "{Browser}" placeholder.

    My example might appear slightly tricky. My apologies, I've just used the real thing got from customers.
    I think, custom "like" filter might be useful in the site search report for filtering records by search term. In example we can get all the records which contain "Web marketing" keyword.
    It is very simple to modify custom filter implementation for this purpose: just use PageEvent.DataKey field instead of PageEvent.Text.

    That's all what I wanted to share about my experience in making custom filters. If you are interesting in some additional things don't forget about information from Chris Bushnell and, of course, you can leave comments below.


    1. shouldn't we reference to the filter ID some where ? I followed the Chris Bushnell blog and it doesn't say that we should,
      but after I was done with the steps I couldn't see the filter on the report , when I looked into other reports like Top pages I found that the filter field has these values

      My guess is the id reference for the filter id but what does the uid means ?

    2. Hi Sarah.
      Thank you for your reply.

      As I understand you don't see the filter in list of filters for appropriate report.
      It seems it's because it is not applicable for the report you are trying to apply it to.

      There has to be appropriate placeholder (like "{Browser}" in my example) which the method IsApplicable in your filter class checking for.
      In other words this line "return sqlCommand.HasPlaceholder("Browser");" returns false and that's because filter is not visible for your report.

      Please make sure you have appropriate placeholder in "*.mrt" file of the report you are trying to filter.


    Post a Comment