Smarter ideas worth writing about.

Search Functionality Created for Cincinnati Hospital Using SharePoint Business Connectivity and Custom WebParts

Recently, Cardinal Solutions created two robust public SharePoint sites for The Christ Hospital.  One for their health network and another for their physicians. These sites had various requirements, including a responsive visual design, URLs for both anonymous and authoring users, custom global and current navigation controls, and the ability to search for physicians and locations whose information is stored in an external system.  This post focuses on the development of the physician and location search by using a combination of out-of-the-box SharePoint features and custom developed components.

Overview of Requirements
Adding the ability for current and potential patients to easily obtain information about physicians and locations was one of the main priorities for these new websites. The Christ Hospital had numerous requirements around filtering and sorting their list of physicians and locations.  Users needed the ability to search physicians by keywords, specialty, name, and geographical location.  

For locations the criteria includes keywords, location type, and geographical location.  Results pages for both searches require a refinement panel allowing users to filter their results by more specific criteria grouped in categories and limit the results by a geographical radius.  The refinement panel allows for multiple selections per category, which the out-of-the-box SharePoint does not support, so we needed a custom WebPart.  Lastly, physician searches contained a map showing the geographical location of each physician in the displayed results.

The Solution
For SharePoint to be able to search the information in the external system, we needed to utilize the SharePoint Business Connectivity Service (BCS), SharePoint Search Service and FAST Query Language (FQL) in custom WebParts.  This data exports from the external system to a SQL Server data warehouse for SharePoint to read.

Using Business Connectivity Services (BCS) to Read External Data
Since the data being searched resides in an external system The Christ Hospital uses to manage their physician and location information, we used the built-in SharePoint BCS.  BCS is designed for the exact purpose of reading external data into a SharePoint site.  We built a data model inside BCS that reads from stored procedures in a data warehouse.  This model maps out all of the data elements being read, i.e. first name, last name, and phone number.  Using this method was critical because the search information changes regularly and prevents the need to manage in multiple systems.  It also guarantees users are getting the most up-to-date information.  

Leveraging SharePoint Search Service to Crawl External Data
Once a BCS model is created the SharePoint Search Service was configured to crawl that model and index the data for fast and easy querying.  Configuring this communication between the services consists of creating a content source and managed properties inside the search service.  A content source tells the service where to get its data from and how often to crawl the content source for updated data.  In our case, we did a nightly crawl of the data.  As the search service crawls the content source it stores the data in indexed managed properties.  We created a single managed property for each field in the BCS model.

Custom SharePoint Search WebParts Created
Once the external data is indexed by the SharePoint Search Service, it is available to search from a SharePoint site.  Based on the requirements for displaying the search results, we used the FAST Query Language (FQL) in two custom WebParts: search results and refinement panel.  On a side note, we also created WebParts to execute these searches.  The real “meat and potatoes” come from the WebParts on the results page, so we are just focusing on those.

  • Search Results WebPart
    This webpart is at the core of this functionality because it handles the communication with the refinement panel, executes the FQL query, and displays the results.  Whenever the refinement or current page changes, this WebPart will determine the new search criteria, rebuild/execute a new FQL query and refresh the results appropriately.  Once the results are returned the Google Maps JavaScript API is used to display the locations of physicians on an interactive map.

  • Refinement WebPart
    The refiner allows users to filter down to a subset of their initial search results by different categories, making it easier to find a specific physician or location.  As previously stated, multiple selections per category is allowed, which the out-of-the-box SharePoint refiners do not allow.  This refinement panel also displays the total count of results that will meet that criteria, taking into consideration other selected refinements.  For example, on physician results page above, if a user selects the “Internal Medicine” refinement in the “Specialties” category the counts next to the refinements in the “Gender” and “Languages Spoken” sections will update to reflect the physicians who specialize in “Internal Medicine.”  These numbers will also update when a geographical search radius is applied in the top portion of the refinement panel.

Benefits of the Solution
Adding a searchable directory of physicians and locations based on data residing in a system outside of SharePoint on a public website results in numerous benefits for The Christ Hospital.  Since SharePoint has the ability to obtain the data from an external system the data can be managed in one place.  This eliminates the need to manage data in multiple locations, which cuts down cost and removes the possibility of data being out of sync between the two systems.  Also, having a way to provide up-to-date, detailed information about their physicians and locations in an easy-to-read, filterable way helps patients get a better understanding of the services The Christ Hospital and its associated physicians provide.


About The Author

SharePoint Developer
Frank has been a SharePoint developer since SharePoint 2010 was first released.  He has worked on a variety of SharePoint implementations, including intranets, extranets and public sites.