Home > Sitecore OMS reports > Creating a Top Records Filter in Sitecore OMS

Creating a Top Records Filter in Sitecore OMS

November 2, 2009 cbushnell Leave a comment Go to comments

Step 2: Create your Filter Implementation in Visual Studio

To create a new report filter, you must use Visual Studio and create a new C# class. Alternatively, re-use an existing filter, such as WhereSpecificProfileKey from my last blog. If you do this, re-name and edit the existing C# class file.

· Make sure that your class includes the following references and inherits from the Operator Filter Base class:

· Sitecore.Kernel

· Sitecore.Analytics

public class WhereTopRecords : OperatorFilterBase

· Your filter class, uses the following two methods:

a. ApplyFilter

b. IsApplicable

 Use these two methods for all filter implementations.

C# code used in this filter
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Sitecore.Analytics.Data.Filters.Filters;
using Sitecore.Analytics.Data.Filters;
using Sitecore.Diagnostics;

namespace <your project name>.sitecore_modules.<your project folder>
{
    public class WhereTopRecords : OperatorFilterBase
    {

        public string value;

        // Methods
        public override void ApplyFilter(SqlCommand sqlCommand)
        {
            Assert.ArgumentNotNull(sqlCommand, "sqlCommand");
            string @operator = base.GetOperator();
            sqlCommand.AddWhereClause("MaxRecords", Value, false);
        }

        public override bool IsApplicable(SqlCommand sqlCommand)
        {
            Assert.ArgumentNotNull(sqlCommand, "sqlCommand");
            return (sqlCommand.HasPlaceholder("MaxRecords"));
        }

        //Properties   
        public string Value
        {
            get
            {
                return (this.value ?? "1000");
            }
            set
            {
                Assert.ArgumentNotNull(value, "value");
                int defaultValue = 1000;
                Int32.TryParse(value, out defaultValue);
                this.value = defaultValue.ToString();
            }
        }

    }
}

You can either copy and paste this code into your C# class file or use the ApplyFilter and IsApplicable methods to construct your own code solution.

Explaining the code:

In my previous blog post, I explained the standard code to include in a filter implementation. In this post, I will only include the sections that are important to this filter.

Variables

Declare the following variable. This variable is used in the Properties section of your class and holds a value for the TOP clause entered by an end-user.

// Fields
        public string value;

Methods

ApplyFilter

This method inserts your filter into the WHERE clause of your SQL query. It also passes parameters, such as a value for the TOP clause to the report engine.

// Methods
public override void ApplyFilter(SqlCommand sqlCommand)
        {
            Assert.ArgumentNotNull(sqlCommand, "sqlCommand");
            string @operator = base.GetOperator();
            sqlCommand.AddWhereClause("Top", Value, false);
        }

Edit the AddWhereClause parameters:

(<Placeholder name>, <where clause>, <isExceptCondition>)

Use the following syntax:

Parameter Data type Value
Placeholder name string MaxRecords – Appears in the SQL query as {MaxRecords}.Use MaxRecords to avoid confusion with SQL TOP.
Where clause string Value –This is a variable containing a top value entered by an end-user.
isExceptCondition boolean False – this determines what should be displayed if there is an error.

IsApplicable

This method determines where to insert your filter using a placeholder. The placeholder must be inserted into the WHERE clause of your SQL query using the following syntax:

{placeholder}

Normally a SQL TOP clause can only be added to the SELECT part of a SQL query. To make it possible to insert it in the right place, you need to use Transact SQL to create a workaround.

public override bool IsApplicable(SqlCommand sqlCommand)
        {
            Assert.ArgumentNotNull(sqlCommand, "sqlCommand");
            return (sqlCommand.HasPlaceholder("MaxRecords"));
        }

You can either use the placeholder name I have used {MaxRecords} or replace this with a placeholder name of your own. Both methods must include the same placeholder name.

Properties

Create the following properties.

Use the get and set properties to process user input. If a user enters a value, such as 10 the filter returns the top 10 records. If the value is left empty then the top 1000 records are returned by default.

//Properties   
        public string Value
        {
            get
            {
                return (this.value ?? "1000");
            }
            set
            {
                Assert.ArgumentNotNull(value, "value");
                int defaultValue = 1000;
                Int32.TryParse(value, out defaultValue);
                this.value = defaultValue.ToString();
            }
        }

Compile your code and ensure that it is added to your Sitecore bin folder.

C:\Inetpub\wwwroot\<site name>\WebSite\bin

 

My next post:  Step 3 – Add a TOP Clause to your SQL Query

Possibly related posts: (automatically generated)

Categories: Sitecore OMS reports