Creating a Top Records Filter in Sitecore OMS

Introduction

Welcome to my third Sitecore blog on creating OMS reports.

In this post, I will create a “top” filter that limits the records displayed in a report. To do this you would normally hard code a SQL TOP clause into your SQL query but in this blog I will demonstrate how you can achieve the same result by creating a filter.

A “top” filter could allow an end-user to enter any numerical value of their own, such as top 10 or top 100. This would make it easier for a marketer to see at a glance which leads are the most important to follow up and is better for performance than displaying every record in the database.

 

We realized that this would be something “nice to have” in the product and the solution presented here is one way of achieving this. However, it is not a typical approach. For example, we don’t recommend the use of TSQL in your SQL queries as this can cause problems when migrating from a SQL Server to an Oracle database. This blog merely demonstrates some of the possibilities available to developers when creating Sitecore OMS reports. As a report analyst, if you have enough in-depth knowledge of the SQL query language and some programming experience, you can create almost any filter or report you need. The only limitations are the contents of your database tables and your own imagination!

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

Optional step: You can add a filter to any existing report but in this blog I will duplicate the ProfileKeyFilter report I created in my previous blog and rename it ProfileKeyFilterTop. This is an optional step which makes it easier to test your filter without affecting you other existing reports. It also means that you are working with the same data sources and SQL query.

Creating a Top Filter

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

· Create a Filter Definition Item

· Create your Visual Studio Filter Implementation

· Add a Top Clause to your SQL Query· Configure your Sitecore Definition Item

· Test your Top Filter

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