Creating a Report Filter in Sitecore OMS

Introduction

Welcome to my second blog on creating Sitecore OMS reports.

If you select any report in Sitecore Analytics, you can see a number of different pre-configured filters displayed in the Filters Editor window. You can select or clear any of these using the appropriate check box to make your reports more specific and meaningful. For example, in the Latest Company Sessions report you can filter by visitor country or only show human visits to exclude visits by robots from your reports.

The aim of this blog is to show you how to create a new filter from scratch and provide you with all the steps that you need to achieve this goal.

You will create a new filter that enables you to filter a report by profile key, then you will associate the filter with a report (similar to the one created in my previous post) which then displays the top scores and organizations for the chosen key. A filter like this can provide marketers with a useful insight into the browsing behavior of visitors to their Web site, enabling them to see at a glance which organizations could be potential customers or important leads to follow up.

Before we start, we need to make some small changes to the profile key report used in my previous blog. This is necessary so that we can begin with an unfiltered report that displays all profile keys and scores in the same report.

When you have completed all the steps in this blog you will see the following filter displayed in the Filters Editor window.

Prerequisites

When you create a new filter, you place the logic behind the filter in a C# class file. Therefore to create new filters it is necessary to have some knowledge of C# and Visual Studio.

You also need to install the following:

· Sitecore OMS

· Stimulsoft Report Designer (Stimulsoft Reports.Net 2009.2)

· Visual Studio 2005 or 2008

Preparation

In my first blog, I created a report for a single profile key displaying top scores and organizations for the Semi-pro profile key. The SQL query that specified which profile key to select from the Analytics database was hard coded into the report:

WHERE ProfileKeyDefinition.Name = 'Semi-pro'

Here is a link to my first blog:

Creating an OMS Profile Report

In my second blog, I will create a filter that allows end-users to select any profile key value from the Nicam site without the requirement to change the underlying SQL query.

To do this, you first need to create an unfiltered report that displays all profile keys in one long scrolling report. Start by making a duplicate of the ProfileKeyReport .mrt file and rename it to ProfileKeyFilter.mrt.

Open ProfileKeyFilter.mrt in the Report Designer. In the Dictionary panel, right click the IpOwners data source and then click Edit to open the SQL query.

Changes to the SQL Query

Make the following changes to the SQL query:

· Remove Top 20 from the beginning of the SQL statement:

Top 20 

· Add the following alias:  

ProfileKeyDefinition.Name AS Prof

· Change the Order clause:

ORDER BY Prof, Score DESC, IPOwner.Name

· Add the following where clause:

WHERE Session.IpId = Ip.IpId
AND Ip.IpOwnerId = IpOwner.IpOwnerId
AND Session.BrowserId = Browser.BrowserId
AND Session.GlobalSessionId = GlobalSession.GlobalSessionId

Alternatively just copy and paste the entire query to create a new ProfileKeyFilter report. This unfiltered report can be the starting point for this blog.

SELECT IpOwner.IPOwnerId AS IPOwnerIdObject, IpOwner.Name, IpOwner.Country,
Count(DISTINCT Session.GlobalSessionId) AS VisitorCount,
Count(DISTINCT Session.SessionId) AS VisitCount,
Sum(DISTINCT ProfileKey.Value) AS Score, ProfileKeyDefinition.Name,
ProfileKeyDefinition.Name AS Prof 

FROM Session INNER JOIN
Profile ON Session.SessionId = Profile.SessionId INNER JOIN
GlobalSession ON GlobalSession.GlobalSessionId = Session.GlobalSessionId INNER JOIN
Ip ON Ip.IpId = Session.IpId INNER JOIN
IpOwner ON IpOwner.IpOwnerId = Ip.IpOwnerId INNER JOIN
Browser ON Browser.BrowserId = Session.BrowserId INNER JOIN
ProfileKey ON ProfileKey.ProfileId = Profile.ProfileId INNER JOIN
ProfileKeyDefinition ON ProfileKeyDefinition.ProfileKeyDefinitionId =
ProfileKey.ProfileKeyDefinitionId 

WHERE Session.IpId = Ip.IpId AND Ip.IpOwnerId = IpOwner.IpOwnerId AND
Session.BrowserId = Browser.BrowserId AND Session.GlobalSessionId =
GlobalSession.GlobalSessionId 

GROUP BY IpOwner.IPOwnerId, IpOwner.Name, IpOwner.Country,
ProfileKeyDefinition.Name 

ORDER BY Prof, Score DESC, IpOwner.Name

Changes to the Layout of your Report

1. Drag and drop the column Prof from the Data Dictionary onto your report and add a text box in the header to display the selected Profile Key name.

2. Double click the text box you added for the Profile Key name. In the Text Editor, Expression tab enter the following expression:

{(IPOwners.Score) > 0 ? "Profile Key" : ""}

This expression displays the profile name if the database contains a score for the selected profile key. If the score = zero, no data is displayed. Add a similar expression to the other column headings for Score and Organization.

3. In your report title, enter the following expression to display the selected profile key name:

Profile Key: {IPOwners.Prof}

4. Finally, format the report title using the Title style.

When you have made all these changes your report should look something like this:

The unfiltered report displays all profile keys contained in the Nicam site, listing all organizations by highest score.

Note: When you have made these changes use the date filter to select a date at least three months before the current date, so that enough data is available from the Nicam site to make your reports meaningful.

Creating a Profile Key Filter

To create a new filter, you need to complete the following steps:

1. Create a filter definition item

2. Create your filter implementation in Visual Studio

3. Add a placeholder to your report SQL query

4. Configure the filter definition item

5. Test your filter

 

My next post: Step 1 – Create a Filter Definition Item