travislowdermilk.com

This is the final post in a series about creating a robust search U.I. for SharePoint.

We have successfully captured each search query submitted through our U.I. and stored them into a normalized schema in our database.

So the final question is: “How do we use this information to provide search suggestions to our users?”.

The first step starts with our queries.  Our schema has 3 tables:

Tag Table: This captures all the unique keywords a user might type into their search query e.g. “dress”, “code”, etc.
Context Table: Captures the date, time, and the user who performed the search.
Context_Tag: Combines keywords and context.

These three tables allow us create some nifty results.

For instance:

What are the top ten phrases? (Use this to make a tag cloud):

 DECLARE @tblContextTag TABLE(Row_Num int, ContextID int, Ordinal int, Tag nvarchar(255))
 DECLARE @tblContextPhrases TABLE (Phrase nvarchar(255))
 DECLARE @Tags int
 DECLARE @Tag n_varchar(255) --fix n_varchar to nvarchar [added due to issue with WordPress code plugin]
 DECLARE @Row_Num int
 DECLARE @ContextID int
 DECLARE @Ordinal int
 DECLARE @Phrase n_varchar(255) --fix n_varchar to nvarchar [added due to issue with WordPress code plugin]</pre>
INSERT INTO @tblContextTag (Row_Num, ContextID,  Ordinal, Tag)
 (SELECT ROW_NUMBER() OVER(ORDER BY ContextID, Ordinal) AS 'Row_Num', ContextID, Ordinal, Tag
 FROM Context_Tag A INNER JOIN
 Context ON A.ContextID = Context.ID INNER JOIN
 Tag ON A.TagID = Tag.ID
 GROUP BY ContextID, Ordinal, Tag, TagID)

 SET @Tags = 0
 SET @Row_Num = 1
 SET @ContextID = (SELECT TOP 1 ContextID FROM @tblContextTag WHERE Row_Num = @Row_Num ORDER BY ContextID)
 WHILE @Tags <= (SELECT COUNT(*) FROM Context) - 1
 BEGIN
 SET @Ordinal = 0
 SET @Phrase = ''
 WHILE @Row_Num <= (SELECT TOP 1 Row_Num FROM @tblContextTag WHERE ContextID = @ContextID ORDER BY Row_Num DESC)
 BEGIN
 SET @Tag = (SELECT TOP 1 Tag FROM @tblContextTag WHERE Row_Num = @Row_Num)
 SET @Phrase = @Phrase + ' ' + @Tag
 SET @Ordinal = @Ordinal + 1
 SET @Row_Num = @Row_Num + 1
 END
 INSERT INTO @tblContextPhrases (Phrase) VALUES (LTRIM(@Phrase))
 SET @ContextID = (SELECT ContextID FROM @tblContextTag WHERE Row_Num = @Row_Num)
 SET @Tags = @Tags + 1
 END

 SELECT TOP 10 Phrase, COUNT(Phrase) AS Weight
 FROM @tblContextPhrases
 GROUP BY Phrase
 ORDER BY COUNT(Phrase) DESC, Phrase

Or, how about:

What are the top 20 suggestions for <specified network login> based on what this user has search for in the past?  Plus I want to include what is popular among other users’ searches.  And please filter by <specified first letter of the query>.

-- Add the parameters for the stored procedure here
 @value n_varchar(1), --fix n_varchar to nvarchar [added due to issue with WordPress code plugin]
 @networkid n_varchar(10) --fix n_varchar to nvarchar [added due to issue with WordPress code plugin]
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 -- Insert statements for procedure here
 DECLARE @tblContextTag TABLE(Row_Num int, ContextID int, Ordinal int, Tag nvarchar(255))
 DECLARE @tblContextPhrases TABLE (Phrase nvarchar(255))
 DECLARE @tblContextTOPPhrases TABLE (Phrase nvarchar(255))
 DECLARE @Tags int
 DECLARE @Tag n_varchar(255) --fix n_varchar to nvarchar [added due to issue with WordPress code plugin]
 DECLARE @Row_Num int
 DECLARE @ContextID int
 DECLARE @Ordinal int
 DECLARE @Phrase n_varchar(255) --fix n_varchar to nvarchar [added due to issue with WordPress code plugin]

 INSERT INTO @tblContextTag (Row_Num, ContextID,  Ordinal, Tag)
 (SELECT ROW_NUMBER() OVER(ORDER BY ContextID DESC, Ordinal) AS 'Row_Num', ContextID, Ordinal, Tag
 FROM Context_Tag A INNER JOIN
 Context ON A.ContextID = Context.ID INNER JOIN
 Tag ON A.TagID = Tag.ID
 WHERE NetworkID = @networkid AND
 Date_Time >= DATEADD(mm, -6, GETDATE()) AND Date_Time <= GETDATE()
 GROUP BY ContextID, Ordinal, Tag, TagID)

 SET @Tags = 0
 SET @Row_Num = 1
 SET @ContextID = (SELECT TOP 1 ContextID FROM @tblContextTag WHERE Row_Num = @Row_Num ORDER BY ContextID)
 WHILE @Tags <= (SELECT COUNT(*) FROM Context WHERE NetworkID = @networkid AND Date_Time >= DATEADD(mm, -6, GETDATE()) AND Date_Time <= GETDATE()) - 1
 BEGIN
 SET @Ordinal = 0
 SET @Phrase = ''
 WHILE @Row_Num <= (SELECT TOP 1 Row_Num FROM @tblContextTag WHERE ContextID = @ContextID ORDER BY Row_Num DESC)
 BEGIN
 SET @Tag = (SELECT TOP 1 Tag FROM @tblContextTag WHERE Row_Num = @Row_Num)
 SET @Phrase = @Phrase + ' ' + @Tag
 SET @Ordinal = @Ordinal + 1
 SET @Row_Num = @Row_Num + 1
 END
 INSERT INTO @tblContextPhrases (Phrase) VALUES (LTRIM(@Phrase))
 SET @ContextID = (SELECT ContextID FROM @tblContextTag WHERE Row_Num = @Row_Num)
 SET @Tags = @Tags + 1
 END
 --Now get overall popular results
 SET @Tags = 0
 SET @Row_Num = 1
 SET @ContextID = 0
 SET @Tags = 0
 DELETE @tblContextTag

 INSERT INTO @tblContextTag (Row_Num, ContextID,  Ordinal, Tag)
 (SELECT ROW_NUMBER() OVER(ORDER BY ContextID, Ordinal) AS 'Row_Num', ContextID, Ordinal, Tag
 FROM Context_Tag A INNER JOIN
 Context ON A.ContextID = Context.ID INNER JOIN
 Tag ON A.TagID = Tag.ID
 WHERE Date_Time >= DATEADD(mm, -6, GETDATE()) AND Date_Time <= GETDATE()
 GROUP BY ContextID, Ordinal, Tag, TagID)

 SET @Tags = 0
 SET @Row_Num = 1
 SET @ContextID = (SELECT TOP 1 ContextID FROM @tblContextTag WHERE Row_Num = @Row_Num ORDER BY ContextID)
 WHILE @Tags <= (SELECT COUNT(*) FROM Context WHERE Date_Time >= DATEADD(mm, -6, GETDATE()) AND Date_Time <= GETDATE()) - 1
 BEGIN
 SET @Ordinal = 0
 SET @Phrase = ''
 WHILE @Row_Num <= (SELECT TOP 1 Row_Num FROM @tblContextTag WHERE ContextID = @ContextID ORDER BY Row_Num DESC)
 BEGIN
 SET @Tag = (SELECT TOP 1 Tag FROM @tblContextTag WHERE Row_Num = @Row_Num)
 SET @Phrase = @Phrase + ' ' + @Tag
 SET @Ordinal = @Ordinal + 1
 SET @Row_Num = @Row_Num + 1
 END
 INSERT INTO @tblContextTOPPhrases (Phrase) VALUES (LTRIM(@Phrase))
 SET @ContextID = (SELECT ContextID FROM @tblContextTag WHERE Row_Num = @Row_Num)
 SET @Tags = @Tags + 1
 END

 --Insert overall popular results
 INSERT INTO @tblContextPhrases (Phrase)
 SELECT TOP 20 Phrase FROM @tblContextTOPPhrases
 GROUP BY Phrase HAVING Count(Phrase) > 5
 ORDER BY COUNT(Phrase) DESC, Phrase

 SELECT DISTINCT Phrase AS value
 FROM @tblContextPhrases
 WHERE Phrase LIKE @value + '%'

END

The above stored procedure will give you the result set you need in order to inject meaningful suggestions into your U.I. using the jQuery autocomplete function.  Just call the stored procedure from the web service that is connected to your jquery plugin.  Here is some more info on how the jquery autocomplete code functions.  Also, if you’re wondering how you might get the user’s login from SharePoint to pass to the stored procedure, check this post out.

And the final result is a rich U.I. with personalized search suggestions. Right in SharePoint!

Passing multiple, comma-delimited, parameters to a stored procedure is tricky. I found the need for this trick while trying to pass multiple parameters from a web form to my Microsoft SQL Reporting Services report.

Let’s say, for instance, you would like to filter employee data by a series of employee numbers:

One would think this would work. However, it does not. The IN operator cannot accept a comma-delimited string. Too bad. It would make things a lot easier if it did.

4GuysFromRolla came up with this solution around this problem.

Essentially, they create a user-defined function that receives a comma-delimited string and converts it into a table. The IN operator loves it some tables.

Here’s the function:

You can now use this function like so:

The Split function returns a table to the IN operator:

Furthermore, because you are using a SELECT statement with the Split function, you could apply additional filters to remove specified parameters that you would like to ignore:

“What were the dates of all the Mondays in the last 5 months?”
“How many Fridays were there between this date range?”
“What were the dates of the paydays in the last 10 months?”

Do any of these types of questions sound familiar? If you’ve ever worked with these types of date time questions in SQL, you would know that it can be VERY challenging.

At the hospital we use Cisco Unified Communications Manager (a.k.a CallManager).

The directors desired a report to break down various call statistics to one of their clinics. They were hoping this information would give them insight into how to handle call load.

One particular question was a tricky one: “What is the average call per days of the week, per hour, for the last three months?”.

For example: “What was the average call count, per hour, for all the Mondays in the last 3 months?”

Thanks to newly discovered dimensional tables, I had the breakdown per hour figured out, however, I was stumped on how to group it for average Mondays, Tuesdays, Wednesdays, etc.

That’s why I was thrilled to discover this solution by Peter Larsson, titled How Many More Mondays Until I Retire?

Essentially, Peter suggests creating a scalar SQL function that returns a table of dates between a specified date range. This opens a range of possibilities as far as dealing with tricky date queries.

Here is Peter’s function:

CREATE FUNCTION dbo.fnSeqDates
(
    @LowDate DATETIME,
    @HighDate DATETIME
)
RETURNS @Dates TABLE
        (
            SeqDate DATETIME
        )
AS

BEGIN
    DECLARE @Temp DATETIME

    IF @LowDate > @HighDate
        SELECT    @Temp = @LowDate,
                  @LowDate = DATEADD(day, DATEDIFF(day, 0, @HighDate), 0),
                  @HighDate = DATEADD(day, DATEDIFF(day, 0, @Temp), 0)
    ELSE
        SELECT    @LowDate = DATEADD(day, DATEDIFF(day, 0, @LowDate), 0),
                  @HighDate = DATEADD(day, DATEDIFF(day, 0, @HighDate), 0)

    INSERT    @Dates
              (
                  SeqDate
              )
    VALUES    (
                  @LowDate
              )

    WHILE @@ROWCOUNT > 0
        INSERT     @Dates
                   (
                       SeqDate
                   )
        SELECT     DATEADD(dd, n.Items, d.SeqDate)
        FROM       @Dates d
        CROSS JOIN (
                        SELECT COUNT(SeqDate) Items
                        FROM   @Dates
                   ) n
        WHERE      DATEADD(dd, n.Items, d.SeqDate) <= @HighDate

    RETURN
END

I used the function in my stored procedure like this:

	@StartDate datetime,
	@EndDate datetime,
	@Extensions varchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

DECLARE @Time_Value TABLE (
RangeName varchar(18) UNIQUE,
Value int UNIQUE
)
-- This section is for setting up the dimensional table for the hours breakdown
INSERT INTO @Time_Value VALUES ('00',0)
INSERT INTO @Time_Value VALUES ('01',1)
INSERT INTO @Time_Value VALUES ('02',2)
INSERT INTO @Time_Value VALUES ('03',3)
INSERT INTO @Time_Value VALUES ('04',4)
INSERT INTO @Time_Value VALUES ('05',5)
INSERT INTO @Time_Value VALUES ('06',6)
INSERT INTO @Time_Value VALUES ('07',7)
INSERT INTO @Time_Value VALUES ('08',8)
INSERT INTO @Time_Value VALUES ('09',9)
INSERT INTO @Time_Value VALUES ('10',10)
INSERT INTO @Time_Value VALUES ('11',11)
INSERT INTO @Time_Value VALUES ('12',12)
INSERT INTO @Time_Value VALUES ('13',13)
INSERT INTO @Time_Value VALUES ('14',14)
INSERT INTO @Time_Value VALUES ('15',15)
INSERT INTO @Time_Value VALUES ('16',16)
INSERT INTO @Time_Value VALUES ('17',17)
INSERT INTO @Time_Value VALUES ('18',18)
INSERT INTO @Time_Value VALUES ('19',19)
INSERT INTO @Time_Value VALUES ('20',20)
INSERT INTO @Time_Value VALUES ('21',21)
INSERT INTO @Time_Value VALUES ('22',22)
INSERT INTO @Time_Value VALUES ('23',23)

SELECT
CASE
	WHEN DATEPART(weekday,ReportShortDateTime) = 1 THEN 'Sunday'
	WHEN DATEPART(weekday,ReportShortDateTime) = 2 THEN 'Monday'
	WHEN DATEPART(weekday,ReportShortDateTime) = 3 THEN 'Tuesday'
	WHEN DATEPART(weekday,ReportShortDateTime) = 4 THEN 'Wednesday'
	WHEN DATEPART(weekday,ReportShortDateTime) = 5 THEN 'Thursday'
	WHEN DATEPART(weekday,ReportShortDateTime) = 6 THEN 'Friday'
	WHEN DATEPART(weekday,ReportShortDateTime) = 7 THEN 'Saturday'
END AS Day_Name, RangeName AS Hour_Range,
COUNT(ReportShortDateTime) AS Calls,
(ROUND((CAST(COUNT(ReportShortDateTime) AS Float) / CAST((SELECT COUNT(SeqDate) FROM dbo.fnSeqDates(@StartDate, @EndDate) WHERE DATEPART(dw, SeqDate) = DATEPART(weekday,ReportShortDateTime)) AS Float)), 1)) AS Average_Calls
FROM [CALLMANAGER] INNER JOIN
@Time_Value ON DATEPART(hour, ReportDateTimeOrigination) = Value
WHERE ReportShortDateTime BETWEEN @StartDate AND @EndDate AND
originalCalledPartyNumber IN (Select Convert(varchar(50),Value) FROM dbo.fnSplit(@Extensions, ','))
GROUP BY DATEPART(weekday,ReportShortDateTime), RangeName, Value
ORDER BY DATEPART(weekday,ReportShortDateTime), Value
END

As you can see in the query, I use the function like this:

SELECT COUNT(SeqDate) FROM dbo.fnSeqDates(@StartDate, @EndDate) WHERE DATEPART(dw, SeqDate) = DATEPART(weekday,ReportShortDateTime)

In other words, I’m saying: “Give me the count of days, between this specified date range, that are Mondays, Tuesdays, Wednesdays, etc.”

Today, on the Interwebs, I found a great way to group data into ranges. I modified it quite a bit to fit my needs, however, I imagine you could apply this to any type of ranges you might want to use.

In this case, I wanted to group a dataset of information into time ranges (4 hour groups).

Essentially, I create a temporary dimensional table and join to it. Simply brilliant! I’m ashamed to admit I’ve never used these types of dimensional temp tables in my queries. However, I’m learning it won’t be my last.

DECLARE @Time_Ranges TABLE (
RangeName varchar(18) UNIQUE,
Minimum int UNIQUE,
Maximum int UNIQUE
)

INSERT INTO @Time_Ranges VALUES ('12:00 AM - 3:00 AM',0,3)
INSERT INTO @Time_Ranges VALUES ('4:00 AM - 7:59 AM',4,7)
INSERT INTO @Time_Ranges VALUES ('8:00 AM - 11:59 AM',8,11)
INSERT INTO @Time_Ranges VALUES ('12:00 PM - 3:59 PM',12,15)
INSERT INTO @Time_Ranges VALUES ('4:00 PM - 7:59 PM',16,19)
INSERT INTO @Time_Ranges VALUES ('8:00 PM - 11:59 PM',20,23)

SELECT RangeName, COUNT(*) AS Total
FROM [YOURSQLTABLE] INNER JOIN
@Time_Ranges ON DATEPART(hour, DateTimeField) >= Minimum AND
DATEPART(hour, DateTimeField) <= Maximum
GROUP BY RangeName, Minimum
ORDER BY Minimum

I have currently been tasked with the duty of providing a web part that displays patient satisfaction scores for the hospital. However, there was one stipulation:

“Can you make the web part reflect the unit that the nurse is working on?”.

In other words, could I create a web part that would observe the user’s login and display appropriate information based on where, in the hospital, that employee worked. In this case, the requester (ahem, my boss) wanted the scores to display for the nurse’s unit (think of a unit as a “wing” or “floor” in a hospital).

Provocative.

Right off the bat, I figured the best way to access the nurse’s unit was by pulling the “Department” property from our Active Directory schema.

But then I thought: “Why not build a function that would return any property I might want from Active Directory?” That way, from here on out, as I am building my web parts I could make decisions of the data to display based on a user’s department, job title, director, etc.

Essentially, creating web parts that are “user-aware”.

Good idea self!

Below is a namespace (ActiveDirectoryInfo) , class (UserInfo), and function (GetPropertyValue) that I created to do just this:

using Microsoft.Office.Server.Audience;

using Microsoft.SharePoint;

using Microsoft.SharePoint.WebControls;

using System;

using System.Collections.Generic;

using System.DirectoryServices;

using System.Text;

using System.Web;

namespace ActiveDirectoryInfo

{

class UserInfo

{

public String GetPropertyValue(string PropKey)

{

//Get user’s Department from Active Directory

SPWeb spw = SPControl.GetContextWeb(HttpContext.Current); //Establishes the current SharePoint site.

SPUser spu = spw.CurrentUser; //Grabs user information from the logged in state of the user.

DirectoryEntry dentry = new DirectoryEntry(@”LDAP://DC=<hidden>,DC=<hidden>”, “<domain_user>”, “<domain_user_password>”);

DirectorySearcher dsearch = new DirectorySearcher(dentry);

String retUserName= “”;

dsearch.Filter = “(objectSid=” + spu.Sid + “)”; //Filters the search result based on the user’s SID.

//dsearch.Filter = “(sAMAccountName=” + spu.LoginName.Replace(@”Domain\”, “”) + “)”; //Filters the search result based on the user’s login (without Domain\).

dsearch.PropertiesToLoad.Add(PropKey); //Loads the specific property to be viewed.

SearchResult result = dsearch.FindOne(); //Peforms the search for one matching record.

if (null == result)

{

//Nothing

}

else

{

retUserName = result.Properties[PropKey][0].ToString();

}

return retUserName;

}

}

}

So here is how we might call upon this function within a web part:

protected override void Render(System.Web.UI.HtmlTextWriter writer)

{

UserInfo ui = new ActiveDirectoryInfo.UserInfo();

writer.Write(ui.GetPropertyValue(“cn”)); //Returns user’s full name

writer.Write(ui.GetPropertyValue(“mail”)); //Returns user’s e-mail

writer.Write(ui.GetPropertyValue(“Department”)); //Returns user’s Department

}

I found myself in a tricky situation the other day. In the hospital we have patient units. Think of them as floors or “wings” in a hospital. We also have sections in each one of the units. A section might be food, nurses, a room, or the discharge process. As a hospital, we survey our patients and have them rate the quality of a section in the unit they stayed in. Got me so far? Okay.

Well, I needed to create a report to reflect the managers that are responsible for a particular section in a particular unit. Rather than have each manager and the section they are accountable for listed in rows, I wanted to have one field in a column that represented a list of managers (separated by commas).

For example, something like this:

In SQL Management Studio, I created a Scalar_valued Function and put the following code inside:

– Add the parameters for the function here

@unit nvarchar(50),

@section int

)

RETURNS nvarchar(500)

AS

BEGIN

– Declare the return variable here

DECLARE @Individuals CURSOR

DECLARE @Ac_Individuals nvarchar(50)

DECLARE @RV nvarchar(500)

– Add the T-SQL statements to compute the return value here

SET @Individuals = CURSOR FOR

SELECT Fname + ‘ ‘ + Lname AS Individuals

FROM PG_Individuals A INNER JOIN

PG_Accountable B ON A.IID = B.IID

WHERE A.Active = 1 AND UID = @unit AND SID = @section

OPEN @Individuals

WHILE @@FETCH_STATUS = 0

BEGIN

IF @RV IS NULL

BEGIN

SELECT @RV = @Ac_Individuals

END

ELSE

SELECT @RV = @Ac_Individuals + ‘, ‘ + @RV

FETCH NEXT FROM @Individuals INTO @Ac_Individuals

END

CLOSE @Individuals

DEALLOCATE @Individuals

–SELECT @Ac_Individuals = @unit

– Return the result of the function

RETURN @RV

@unit and @section are the parameters that are passed into the function.

@Individuals is a CURSOR that loops through a SELECT query that selects all the managers and joins them to the accountable table, thus pairing them up with their respective accountabilities. This query is filtered using the @unit and @section parameters.

I then begin to FETCH each individual record and store it into the @Ac_Individuals variable. Cycling through I add each entry from @Ac_Individuals into the @RV variable appending a comma.

Finally, I return the @RV variable that is representative of the comma separated list.

Now, I can use this function in the following way:

SELECT dbo.<name_of_my_function>(A.UID, A.SID) AS Accountability