Creating a Top Records Filter in Sitecore OMS

Step 3: Add a TOP Clause to your SQL Query

To add a filter implementation to a report, you normally add a placeholder to the WHERE clause in your SQL query. However, since the TOP clause appears immediately after the SELECT clause, you need a workaround to implement a Top filter. Use Transact SQL (TSQL) to create a CASE WHEN ELSE statement and embed this into the SELECT clause of your SQL query.

To add a TOP clause to your SQL query.

1. Open your ProfileKeyFilterTop report in the report designer

2. Right click the IPOwners data source and click Edit

3. Copy the following TSQL code into your SQL query directly after the SELECT statement.

top (
  CASE WHEN '{MaxRecords}' = ''
                  THEN 1000
                  ELSE CAST(REPLACE( REPLACE( '{MaxRecords}', 'and (', '' ), ')', '') as integer )
                  END
              )

Notice that the placeholder {MaxRecords} is included as part of this embedded code.

The Workaround

Our placeholder {MaxRecords} contains the three parameters added by the AddWhereClause method call (described in step 2). Our workaround allows us to insert this clause into the SELECT statement at the top of the SQL query even though this is not actually part of the WHERE clause. However, this will only work if we remove some of the unnecessary parameters and characters generated by the AddWhereClause method, because the AddWhereClause method generates text that looks something like:

and (10)

where “10” is the user entered value, but the TOP clause just requires:

10

The code in the parentheses following the TOP statement contains a TSQL CASE WHEN statement. In Transact SQL this can be used instead of the classic IF THEN statement. We use it here to add one of two alternative numerical values to the TOP clause:

· A default value of 1000 (stated in the THEN clause)

OR

· A user entered value (stated in the ELSE clause)

If an end-user fails to enter a value, then the first 1000 records are displayed by default. The ELSE part of the code takes the text generated by the AddWhereClause method, strips out unwanted code generated by the AddWhereClause method using REPLACE, and converts the remaining user entered value to an integer using CAST.

CAST(REPLACE( REPLACE( '{MaxRecords}', 'and (', '' ), ')', '') as integer )

So if a user enters a value such as “10” then TOP 10 is inserted into the SELECT part of the SQL query.

4. Next, create a report designer variable for your placeholder {MaxRecords}. To do this, in the Dictionary panel, click New Item, New Variable.

5. In the Name field enter MaxRecords and enter the same value in the Alias field. Keep the Type as string and click OK.

Make sure your full SQL query matches the code below:

SELECT
  top (
  CASE WHEN '{MaxRecords}' = ''
                  THEN 1000
                  ELSE CAST(REPLACE( REPLACE( '{MaxRecords}', 'and (', '' ), ')', '') as integer )
                  END
              )
Count(DISTINCT 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 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.Timestamp >= @StartDate and
Session.Timestamp <= @EndDate
{MyProfileKey}

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

ORDER BY Prof, Score DESC, IpOwner.Name

Note: This query also contains the placeholder for the profile key filter MyProfileKey that I created in my previous blog.

6. Click OK and save your changes in the report designer.

My next post: Step 4 – Configure your Sitecore Filter Definition Item