This is a continuation of a series about creating a robust search U.I. for SharePoint.

Connecting the U.I. to Search Results

We now have our user control embedded on the master page of our site. First we should have the U.I. forwarding the user to the search results upon clicking “Search” or submitting a query.

This is simple enough. In reality, we are just redirecting them to the appropriate search results page and placing their query in a query string parameter:

//This would be for a general query
Response.Redirect("/SearchCenter/Pages/results.aspx?k=" + txtSearch.Text.Replace(" ", "%20"));
//This would be for a people search
Response.Redirect("/SearchCenter/Pages/peopleresults.aspx?k=" + txtSearch.Text.Replace(" ", "%20") + "&s=People");
//This would be for a custom scope
Response.Redirect("/SearchCenter/Pages/Results.aspx?k=" + txtSearch.Text.Replace(" ", "%20") + "&s=This isMyCustomScope");

You can create a U.I. that stores the selected search type into a session variable. I created tabs upon the top of the search. If the user clicks on “People”, for instance, I store a session variable of the value “people” and direct them to the appropriate page (peopleresults.aspx) upon the search click or search submit event:

KDCentral Search U.I.

Each tab stores a value in a session variable. The variable redirects the user to the appropriate search result page.

Enhancing search with JQuery and WebServices

We first need to integrate JQuery into our SharePoint site.  I imagine there are many ways to do this, however, this approach seemed the most straightforward to me:

1. Download the JQuery code.  It’s also available on the Microsoft CDN, but for an Intranet site, I prefer offering it from our own servers.

2. Open up SharePoint Designer and create a folder in the root called “_scripts” and place the jquery.js file there.

3. Open up your masterpage in SharePoint Designer and in the <head> section add:

<script type="text/javascript" src="/_scripts/jquery-1.3.2.js"></script>

4. You’ll also want to download the autocomplete plugin from JQuery.  This plugin allows us to present suggestions (very much like Google) as the user begins to type their query.

5. Follow steps 2 and 3 to add your autocomplete.js file

For brevity, I won’t go  into specifics about how the autocomplete js file works.  There are plenty of other sources on the Internet.  However, in order for our suggestions to be produced dynamically we do need to connect our user control to a web service.  In SharePoint this may not be as easy as it seems.  I chose to integrate the web service code into my existing site.  I’m sure there are many other methods, but this seemed the most straight-forward way:

1. Add a “New Item…” to your Visual Studio Web Project (the same project that contains your user control)

2. Choose “Web Service” to create a new .asmx file.

3. Write the necessary web method(s) to provide data to your autocomplete script (We’ll cover this in more detail in Part 3)

Now, when we compile our web project, the .dll will include code for our web service as well.  The .dll will go into our bin folder of our site, we will also need to create a space for our .asmx file.  I found that creating a folder in the location of your site’s file system is easiest (\\<front-end web server>\<drive-letter>$\inetpub\wwwroot\wss\VirtualDirectories\<site name and port>).

I created a folder called “_webservices” and placed the .asmx file in there.

I then used a ScriptManagerProxy control inside the user control to create a script proxy to be used by the autocomplete jquery plugin:

<asp:ScriptManagerProxy ID="SMP" runat="server">
<Services>
<asp:ServiceReference Path="/_webservices/<web service name>.asmx" InlineScript="false" />
</Services>
</asp:ScriptManagerProxy>

You will need to give “Everyone” or “Domain Users” access to this folder because the service actually provides a .js file to be embedded into your site at load time.

We now have the “plumbing” in order.  Let’s review:

1. We have a user control on the master page for all of our U.I. elements

2. We’ve added the jquery and autocomplete .js files, using SharePoint Designer, to a “_scripts” folder in the root.  We created references to those files in the <head> section of the master page.

3. We added a web service (.asmx) item to our Visual Studio project and added the necessary code to make our autocomplete plugin work.

4. The user control creates a proxy to connect to a web service that is stored in our site’s folder structure (\\<front-end web server>\<drive-letter>$\inetpub\wwwroot\wss\VirtualDirectories\<site name and port>\_webservices\webservice.asmx)

The next step will be to capture and store search queries so that we can provide things like tag clouds and suggestions.

Coming up… SharePoint Search on Steroids Part 3 – Storing Search Queries

Probably the greatest out-of-the-box offering from the SharePoint product is search. However, the U.I. for search within SharePoint sites leaves a bit to be desired. The various site templates place the search box in areas on the page, I felt, weren’t prominent enough. Plus, I wanted to offer things like suggestions when searching for people and tracking/trending search topics.

Based on my research in user behavior, I knew that having a robust search U.I. would be vital in providing a successful Intranet site using SharePoint.

So, I set out to create a fully-functional user control to enhance the experience for our users. This is the first in a series of posts that will cover how it all got implemented.

Creating the User Control

I felt like search should be a tool that is readily available throughout the entire Intranet experience. I decided to create one user control that could be placed on the master page of the Intranet site. All the code would be developed into the user control and data would be provided by a web service.

1. Open Visual Studio and create a new Web Site Project
2. Add a new user control to the project
3. Add References to Microsoft.SharePoint and System.DirectoryServices (The Microsoft.SharePoint.dll can be located on your front-end web server if you develop on a separate workstation)
4. You may create a masterpage and attach it to the Default.aspx in your project. Then you can add the user control to the Default.aspx page. This will create an environment similar to your SharePoint site.

Now we have an environment similar to our SharePoint site. We can build design elements on our user control and bring them into our SharePoint site experience.

That’s great. But how do I get the user control working across my entire SharePoint site?

Because the search is so fundamental in our design, I wanted to add the user control to the master page of the SharePoint site. This would make the search experience prominent within the site (think Google or Yahoo).

1. Create a folder called “usercontrols” so that you can put the user control design (.ascx) file in a location the SharePoint site can find. (\\\C$\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\CONTROLTEMPLATES\usercontrols)
2. In the usercontols folder, place your .ascx file (after you build the project of course)
3. In the bin file of your site (\\$\inetpub\wwwroot\wss\VirtualDirectories\sharepointsite.domain.com80\bin) copy the compiled .dll from your project.
4. Open up SharePoint Designer and navigate to your master page (in my case I’m using “_catalogs\masterpage\default.master”)
5. Register the user control on the master page: <%@ Register src="~/_controltemplates/usercontrols/.ascx” tagname=”” tagprefix=”uc1″ %>

Of course, for this to work we need to mark certain assemblies as “safe” in the web.config of our site if they have not been deployed to the GAC. For instance, if you are using a ScriptManager you may want to add

<SafeControl Assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" Namespace="System.Web.UI" TypeName="*" Safe="True" AllowRemoteDesigner="True" />

to your web.config under “SafeControls”. Depending on any other assemblies (Telerik, Dundas, etc.) you may need to add those as well. There is plenty of information in Googleland to handle those types of questions.

You may also want to take a look at my post about controlling the styling of your master page. This will help you in getting your user control placed and designed exactly how you want it.

Next Part: SharePoint Search on Steroids Part 2 — Using JQuery and Web Services

SharePoint has pretty amazing “people” searching capabilities. However, if you are like our organization, your Active Directory data may not be as complete as you’d like it to be. Or maybe you manage identities using another system like PeopleSoft. So the question becomes: How do you get identity information into SharePoint?

I decided to develop a small console application that would insert user profile data, pulled from our PeopleSoft database, into the SharePoint User Profile Store:

What kind of dark magic must be peformed to get information in here!?
What kind of dark magic must be peformed to get information in here!?

Also, we had employee photos, sitting in a web directory on the Central Admin, front-end web server, that I wanted to point to for the profile pictures.

All of this can be done using the SharePoint User Profile Manager class in the SharePoint API.

1. Start a Console Project and add references to System.Web, Microsoft.SharePoint, and Microsoft.Office.Server

(NOTE: If you are deving on a box that doesn’t have WSS or MOSS installed, you won’t be able to debug, however, you can still add the .dll files to your project for compiling.  Just go to your MOSS or WSS server and pull the Microsoft.Office.Server.dll and the Microsoft.SharePoint.dll [%Program Files%Common Files\Microsoft Shared\web server extensions\12\ISAPI] into your project)

Below is the code I used.  Obviously, there are many variants to this to make it work and no two data structures are the same.  However, I think the general concept could work for many different environments.

Essentially, what I did was iterate through a DataTable that had all my employee data in it.  I then pulled up the user profile, using the SharePoint Profile Manager, and inserted the profile property values.

All of the values I’m referencing (PhoneNo, JobTitle, Supervisor_NetworkID, etc.) were available in our PeopleSoft database. If you don’t have access to that level of data, or it’s not being collected in your organization…well…this will be kinda hard.

Finally, I committed the changes in SharePoint and moved on to the next record:

using Microsoft.SharePoint;
using Microsoft.Office.Server;
using Microsoft.Office.Server.UserProfiles;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
namespace SharePoint_Employee_Photos
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                //Get usernames
                //Retrieve E-Mail Addresses
                SqlConnection sqlconn1 = new SqlConnection("Data Source=Server;Initial Catalog=Database;User Id=dbuser;Password=dbpassword;");
                SqlCommand sqlcomm1 = new SqlCommand("Stored_Procedure", sqlconn1);
                sqlcomm1.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter sda = new SqlDataAdapter(sqlcomm1);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                using (SPSite spSite = new SPSite(@"http://sharepoint.site.org"))
                {
                    ServerContext siteContext = ServerContext.GetContext(spSite);
                    UserProfileManager pManager = new UserProfileManager(siteContext);

                    foreach (DataRow dr in dt.Rows)
                    {
                        String strUsername = "Domain\\" + dr["NetworkID"].ToString();
                        UserProfile userProfile = null;
                        //Check to see if the profile exists in SharePoint
                        if (pManager.UserExists(strUsername))
                        {
                            userProfile = pManager.GetUserProfile(strUsername);
                            Console.WriteLine("User Success: Domain\\" + dr["NetworkID"].ToString());

                            //Update any of the user profile property values

                            //Photos are made available through a separate SSIS task that queries the PeopleSoft database and outputs
                            //the images to a web folder.  The web folder is served on my Central Admin, web front end server.
                            //Let's make sure the file is there first.
                            if (File.Exists(@"[C:\PhotoWebsite\photos\" + dr["EmpID"].ToString() + ".jpg"))
                            {
                                userProfile[PropertyConstants.PictureUrl].Value = "http://website.photos.org/photos/" + dr["EmpID"].ToString() + ".jpg";
                            }
                            if (!dr["PhoneNo"].Equals(DBNull.Value))
                            {
                                if (!dr["PhoneNo"].Equals(String.Empty))
                                {
                                    userProfile[PropertyConstants.WorkPhone].Value = dr["PhoneNo"].ToString();
                                }
                            }
                            if (!dr["DeptName"].Equals(DBNull.Value))
                            {
                                if (!dr["DeptName"].Equals(String.Empty))
                                {
                                    userProfile[PropertyConstants.Department].Value = dr["DeptName"].ToString();
                                }
                            }
                            if (!dr["JobTitle"].Equals(DBNull.Value))
                            {
                                if (!dr["JobTitle"].Equals(String.Empty))
                                {
                                    userProfile[PropertyConstants.Title].Value = dr["JobTitle"].ToString();
                                }
                            }
                            if (!dr["Supervisor_NetworkID"].Equals(DBNull.Value))
                            {
                                if (!dr["Supervisor_NetworkID"].Equals(String.Empty))
                                {
                                    String strSupervisorUsername = "Domain\\" + dr["Supervisor_NetworkID"].ToString();
                                    if (pManager.UserExists(strSupervisorUsername))
                                    {
                                        userProfile[PropertyConstants.Manager].Value = strSupervisorUsername;
                                        Console.WriteLine("Supervisor Success: Domain\\" + dr["Supervisor_NetworkID"].ToString());
                                    }
                                }
                            }

                            userProfile[PropertyConstants.WorkEmail].Value = dr["Email"].ToString();

                            //Commit the changes
                            userProfile.Commit();
                            Console.WriteLine("Import Success!");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                Console.ReadLine();
            }
        }
    }
}

I found myself in a situation, the other day, where I needed to display items from a Links List on one of my subsites.

The trouble was that the Links List resided on the parent site.  Well, that should be easy right?  Wrong.

I started by using the Content Query Web Part (CQWP).   I selected to “Show items from the following list” and selected the Links list on the parent site.

The list resolved, however, this is what it looked like:

Hmmmm...That dont look right
Hmmmm...That dont look right

It turns out that the CQWP doesn’t know what to do with the URL and Title fields on the Links List.

Also, when you click on the “(Blank)” link, it takes you to the item view of the link and not the page you’re linking to.

So I started Googling and found Ishai Sagi’s clever little tip.

Basically, he exports the web part and makes some modifications.  Essentially, creating a custom web part.

1. I would start by configuring any settings you will want to carry over to your custom web part (Query, Appearance, Title, Sorting, Audience Targeting, etc.)  This will save you from having to make those adjustments after importing the web part to your pages.

2. Click the “edit” button on the web part and select “Export…”

Select "Export..." from the edit menu on the web part
Select "Export..." from the edit menu on the web part

3.  Save the .webpart file to your hard disk.  Then open it using your favorite XML editor.

4.  You’re going to be looking for a property “CommonViewFields”:

<property name="CommonViewFields" type="string" />

Change it to this:

<property name="CommonViewFields" type="string">URL,text</property>

5. Now we need to add a custom “Item Style”, so the web part will understand what to do with the new “CommonViewFields” property. This will require editing the item style XSL of the site.

6. Open up SharePoint Designer and navigate to your site or parent site.

7. Check out and open Style Library > XSL Style Sheets > ItemStyle.xsl

8. We are now looking for the template for the style “NoImage”:

<xsl:template name="NoImage" match="Row[@Style='NoImage']" mode="itemstyle">

9. Make a copy of the complete template:

from

<xsl:template name="NoImage" match="Row[@Style='NoImage']" mode="itemstyle">

to

<xsl:template name="NoImage" match="Row[@Style='NoImage']" mode="itemstyle">

and paste it under the “NoImage” template.

  1. Rename “NoImage” to “LinkList”.
  2. Change the “href” attribute to point to the URL column, the text before the column.
  3. Change the link text to point to the URL column, the text after the column.

10.  Here’s the complete code:

<xsl:template name="LinkList" match="Row[@Style='LinkList']" mode="itemstyle">
 <xsl:variable name="DisplayTitle">
        <xsl:call-template name="OuterTemplate.GetTitle">
            <xsl:with-param name="Title" select="@URL"/>
            <xsl:with-param name="UrlColumnName" select="'URL'"/>
        </xsl:call-template>
    </xsl:variable>
 <xsl:variable name="LinkTarget">
        <xsl:if test="@OpenInNewWindow = 'True'" >_blank</xsl:if>
    </xsl:variable>
 <div id="linkitem" class="item" >
  <div class="bullet link-item">
   <xsl:call-template name="OuterTemplate.CallPresenceStatusIconTemplate"/>
   <a>
    <xsl:attribute name="href">
     <xsl:value-of select="substring-before($DisplayTitle,', ')">
     </xsl:value-of>
    </xsl:attribute>
    <xsl:attribute name="title">
     <xsl:value-of select="@Description">
     </xsl:value-of>
    </xsl:attribute>
    <xsl:value-of select="substring-after($DisplayTitle,', ')">
    </xsl:value-of>
   </a>
  </div>
 </div>
</xsl:template>

If you make any mistakes with this part of the styling, it will affect ANY other CQWP you might have deployed on other pages.  Might be a good idea to check on those right now.

11. If everything looks gravy, save the changes to the file and go to the Site Settings of your site or parent site.

12. Under “Galleries”, select “Web Parts”

13. Upload the .webpart file.  Some other settings will come up. Give it a file name, Title, Description. I also like to specify a “Custom” Group value. That way the web part is easy to find in the “Add Web Parts” dialog.

14. Go to your site page or subsite page and Add Web Part.  Add the custom web part to the page.

15. Modify the web part and under Presentation > Styles > Item Style,  select “LinkList” from the dropdown menu:

The links should be showing correctly.  Plus, when you click on them, they go directly to the page you are linking to.

Nice work Ishai!

Even though most organizations deal with Adobe Acrobat and .pdf documents, it doesn’t mean Microsoft added this file type in SharePoint. Sure you can upload .pdf documents to document libraries, but you can forget about crawling those documents and returning results from your search queries.

Thankfully, there are a few resources to address this issue. I will document those resources below, however, here is a condensed list of actions:

1. You have to get your hands on an IFilter from Adobe. Essentially, the IFilter is the link that gets SharePoint to understand what a .pdf document is and how to crawl it. The easiest way to install the IFilter is to download the latest version of Adobe Acrobat Reader on your Central Administration server. The latest versions (8.0 and up) of Reader include the IFilter.

2.  You then need to add the extension to your SharePoint extension list:

  1. Click Start, click Run, type regedit, and then click OK.
  2. Locate and then click the following registry subkey:
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.0\Search\Applications\GUID\Gather\Search\Extensions\ExtensionList
  3. On the Edit menu, point to New, and then click String Value.
  4. Type 38, and then press ENTER.
  5. Right-click the registry entry that you created, and then click Modify.
  6. In the Value data box, type pdf, and then click OK.

3.  Add the PDF file type to the Extensions List for WSS search by editing the registry:

  1. Start regedit
  2. Open the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.0\Search\Applications\{Random GUID}\Gather\Search\Extensions\ExtensionList
  3. Add PDF to the list as a new String Value. Use a new high value e.g. if 37 is the highest value, use “38″ as the key with the value “pdf”

4.  Make a graphic for the .pdf icon to be displayed in the search results and add it to SharePoint files to be referenced:

  1. Create a 16X16 pixel image of the Acrobat file icon.
  2. Save the image as pdf16.gif
  3. Add the Acrobat PDF picture to the SharePoint templates directory. Copy the Acrobat PDF picture called pdf16.gif in the 12 Hive\TEMPLATE\IMAGES folder, e.g. %programfiles%\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\IMAGES.
  4. Bind the Acrobat PDF picture to the PDF file type
  5. Open the 12 Hive\TEMPLATE\XML\DOCICON.XML file
  6. Find the <DocIcons.ByExtension> part
  7. Add the following mapping: <mapping Key=”pdf” Value=”pdf16.gif” OpenControl=”" />

5.  Run IISReset

6. For MOSS 2007 users, Add the file type to Central Administration:

  1. Go to your SSP site
  2. Click on Search Settings > File Types > New File Type
  3. Add pdf as a file type

7. Complete a Full Crawl of your content sources.  This will re-crawl pdf documents that may already be in libraries.

Some of you might see the following warning in your crawl logs:

The file reached the maximum download limit. Check that the full text of the document can be meaningfully crawled.

This is because some of your .pdf files may be too large.  Also, SharePoint wont crawl .pdf documents that have other .pdf documents contained within them.

There doesn’t seem to be a fix for complex .pdf documents, however the size issue can be resolved by adjusting the MaxDownloadSize key in the registry:

  1. Start Registry Editor (Regedit.exe).
  2. Locate the following key in the registry: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\12.0\Search\Global\Gathering Manager
  3. Open Edit – New – DWORD Value. Name it MaxDownloadSize. Double-click, change the value to Decimal, and type the maximum size (in MB) for files that the gatherer downloads.

You may also want to adjust the timeout period for SharePoint to crawl the documents as you have just adjusted the amount of data that can be crawled and it may take longer:

  1. In Central Administration, on the Application Management tab, in the Search section, click Manage search service.
  2. On the Manage Search Service page, in the Farm-Level Search Settings section, click Farm-level search settings.
  3. In the Timeout Settings section change Connection and Request acknowledgement time.
  1. For WSS3 users: The registry key for is HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\
    Web Server Extensions\12.0\Search\Global\Gathering Manager

Default would be MaxDownloadSize (16MB) * MaxGrowFactor (4MB) = 16 * 4 = 64MB of text can be indexed.

After all of your changes, you should restart the CA server and run another full crawl.

Resources:

http://support.microsoft.com/default.aspx/kb/927675

http://blog.tylerholmes.com/2008/04/walkthrough-installing-adobe-v6-pdf.html

http://bloggingabout.net/blogs/harold/archive/2008/10/02/index-pdf-documents-on-sharepoint-using-adobe-pdf-ifilter-9.aspx

http://grounding.co.za/blogs/neil/archive/2008/12/02/working-with-pdf-s-and-sharepoint.aspx

http://fmuntean.wordpress.com/2008/05/22/increase-file-size-crawling/

One of my favorite features to demo on SharePoint is its incredible searching capabilities. It is, by far, the feature that elicits the greatest reaction.

More specifically, I just plain LOVE the way SharePoint handles searching for people within the organization.

However, the data comes from our Active Directory schema and if your organization is anything like ours, it’s not the cleanest set of data.

For instance, I was finding devices, voicemail boxes, security and distribution groups, and all sorts of other objects showing up in my people results.

I did some binging around the Internet and discovered Mindsharp’s blog.
They had a couple of posts regarding filtering profile data within SharePoint.

This one written by Wayne Hall (archived) and
this one written by Daniel A. Galant.

Here’s the breakdown:

1. Go to your Central Administration site and select your Shared Service Provider. It may require you to log in under the farm administrator account.
2. Under User Profiles and My Sites, select: “User profiles and properties”.
3. On the User Profiles and Properties page, select: “View import connections”.
4. You can select “Create New Connection” or edit an existing connection.

You’ll have to specify your own domain controller information (most of it is already auto detected). The most important item is “User filter”.

The user filter allows you to specify criteria you would like included or excluded from the import. There is a variety of options to explore using LDAP query filters. There are SO many resources out there to explain writing LDAP filter queries. Here’s one to get you started.

The query that I decided to use, was this:

(&(objectCategory=person)(objectClass=user)(sn=*)(mail=*)(showInAddressBook=*)
(!(userAccountControl:1.2.840.113556.1.4.803:=2)))

This, essentially, filters the Active Directory schema to only import those objects that:

1. (objectCategory=person) = are under category “person”.

AND

2. (objectClass=user) = are of class “user”.

AND

3. (sn=*) = have a surname [Last Name].

AND

4. (mail=*) = have a valid e-mail address.

AND

5. (showInAddressBook=*) = are available in our corporate address book.

AND

6. (!(userAccountControl:1.2.840.113556.1.4.803:=2)) = is an account that is active.

This filter eliminated over 9,000 unnecessary items in our user profile list making our people search far more effective.

NOTE : One area that messed me up was setting the Scope to “One level”. This was producing an error:

A test import based on current input returned no results. Please re-check the search base and filter.

This was occurring because the filter was searching for items at the root level. Because I had no items that matched my criteria, at the root level, it was returning no results.
Once I selected the option “Subtree” it searched the entire schema and I was all aboard the Gravy Train Express.

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:

DECLARE @EmployeeIDS nvarchar(50)
SET @EmployeeIDS = ‘15000, 34587, 20989, 987665’

SELECT *
FROM Employees
WHERE EmployeeID IN (@EmployeeIDS)

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:

CREATE FUNCTION dbo.Split
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue TABLE
(

Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGINWhile (Charindex(@SplitOn,@List)&gt;0)
Begin

INSERT INTO @RtnValue (value)
SELECT
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
SET @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
INSERT INTO @RtnValue (Value)
SELECT Value = ltrim(rtrim(@List))
RETURN
END

You can now use this function like so:

DECLARE @EmployeeIDS nvarchar(50)
SET @EmployeeIDS = ‘15000, 34587, 20989, 987665’

SELECT *
FROM Employees
WHERE EmployeeID IN (SELECT convert(int, Value) FROM dbo.Split(@EmployeeIDS,,))

The Split function returns a table to the IN operator:

Value
15000
34587
20989
20989
987665

 

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:

DECLARE @EmployeeIDS nvarchar(50)
SET @EmployeeIDS = ‘15000, 34587, 20989, 987665’

SELECT *
FROM Employees
WHERE EmployeeID IN (SELECT convert(int, Value) FROM dbo.Split(@EmployeeIDS,,) WHERE Value NOT IN (SELECT EmployeeID FROM [Table_Terminated_Employees])

October 9th, 2009Dealing with an IT Person

This is a departure from the typical things that I blog about.  However, given some recent dealings I’ve had supporting users, I think this could serve as being, somewhat, cathartic.

And yes, I realize this topic has probably been covered a million different ways on other blogs.

The audience for this post would be the average user that has to come to us for help.  I understand that, at times, this can be an arduous process.  I would like to clarify what, I think, are some misconceptions that users often have.

Just because you think it’s easy, it’s not

I know that, at times, the solution to the problem seems so easy.  I’ll hear users say things like:

“Well all I need is…” or

“It can’t be that hard to add…” or

“I don’t understand why it’s so hard to…”

Believe us when we say that it will be difficult to do what it is you’re asking us to do.  Keep in mind that most IT folks support a multitude of systems and keeping them all straight in our heads can be a challenge in of itself.

Your request, as small as it might seem, may just be the one thing that blows everything up or ruins the experience for everyone else.  We’d like to try and prevent that.

We love tackling challenges, just not everywhere we go

I’ve been asked to offer IT support in some of the oddest places.  A co-worker I used to work with was actually asked about a laptop issue while giving birth to her son!  We understand that your issues are troubling, but please respect our time.

I know that I would never ask a doctor to look at my bum shoulder at a Christmas party.  I wouldn’t ask a mechanic to look at my car while visiting my house for a BBQ.

I would hope that you would not do the same to an IT person.

It’s hard to say no and, more often than not, we won’t.  So don’t use this as an indication of our willingness to help.

We get frustrated when things don’t work too

We know that when things don’t work, it’s frustrating to a user.  But believe me when I say that it’s much more frustrating for us.

Here’s the big secret.

Often times, we don’t know what’s causing your issue.

Granted, we could do a better job of just admitting that, but we want you to have confidence that we’re going to fix your problem.

Moreover, a lot of times, you’re not the only one that’s experiencing the issue.  By the time you’ve called us, outraged by the impact of the problem, we’ve already heard ten other users irate about the same thing.

We don’t like when you’re unhappy

I sometimes get the impression that users think I live to make their lives difficult.  Nothing could be further from the truth.

Most of us got into this business because we have an insane desire to improve the way people do things.  Often times, we have a hard time standing idly by while someone struggles with an antiquated way of doing things.

If you’re still doing something on paper, chances are, one of us is going to show you a way to do it faster, better, cheaper, etc.  We just can’t help ourselves.

Nothing pleases us more than someone saying: “Wow, this makes my life so much easier” or “I can’t imagine how I lived without this!” Those statements are nerd nirvana!

Myself and a lot of people I work with obsess when something is going wrong for you.  We will talk about the problem at lunch, with our spouses, and even our dogs (if they’ll listen).

It’s all we can think about.

When we’re in the shower, at the movies, at church, we think about the problem you’re having and how to fix it.

And as developer, I have another piece to be unhappy about.  I built the thing!  Trust me, most developers are way more critical of themselves than you could ever be.  So please be gentle with your criticisms.

Sometimes, things are just plain out of our control

Most IT departments don’t have control over policy decisions.  We just offer tools to help support those decisions.

I can’t count the number of times I’ve been placed in a position to have to explain to a user why we were implementing a new piece of technology.  A nasty trick your boss will often use is blaming technology as the reason why you’re going to have to do things differently.  It’s okay.  We understand.  However, this often puts us in an awkward spot.

Keep in mind that, sometimes, we are doing things because we have been asked by the higher-ups.  Or that new piece of software you hate?  That might not have been our decision either.

Just please remember that, more often than not, we are just doing what we were asked to do.  Sure we offer advice and council but a lot times,in the end,it’s not our decision.

So when you tell us you HATE this @*^%$@! program.

What we won’t tell you is…We do too.

We would appreciate some perspective

Finally, I would suggest that you keep things in perspective.

Is the problem you’re having really THAT big of a deal?  Sometimes it REALLY is.  I’ve found that most times, it’s not.

A problem is a problem, so don’t think I’m saying this to be cavalier.  But try to keep your problem in perspective.  Imagine all the other issues an IT person might be dealing with that are of a higher priority.

And sometimes, your problem is not really a problem at all.

Sure, you don’t like the color of the window or having to click that extra button is annoying.  But try to focus on all the things that the technology IS doing for you.  Or focus on the things that are faster instead of the one piece that makes you wait.

I think, in general, having this perspective in dealing with technology will grant you less frustration and foster a more productive relationship with your IT people.

September 28th, 2009Sequencing Dates in SQL

“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.”

September 21st, 2009Grouping Data Into Time Ranges

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

© 2007 travislowdermilk.com | iKon Wordpress Theme by TextNData | Powered by Wordpress | rakCha web directory