MOSS 2007 Employee Directory web part using Search and Data View web parts


SharePoint Employee Directory web part

One of the frequent requests from my customers in a MOSS 2007 implementation is an employee directory webpart. During the pre-sales cycle they are excited to see the Active Directory integration and the first question they ask is ” Can this replace our existing employee directory out of the box ?”. Unfortunately there are no out of the box web parts at this time to display an employee directory from the user profiles.

Bamboo Solutions(Link) has a great package that provides some of these webparts. However if you don’t need the robust suite from Bamboo, the same can be built using MOSS 2007 Search and the Data View webpart. Let us get started on building the employee directory webpart in this article.

To build an employee directory web part, you need the following

  • The free SharePoint Search Query Tool – refer to this article for download instructions
  • SharePoint Designer 2007 – Click here to download a free trial
  • Information on how to build Data View Web Parts – there is a great white paper here

First, we need to retrieve the properties from the user profile. Since the user profiles are available under the people scope, we will use the SharePoint search query tool to build our people search query. For this article, we will retrieve the “preferredname”, “firstname”. “lastname”, “workemail”,”picturelurl”. Run the Sharepoint search query tool. Since we will be connecting to a MOSS 2007 instance select the MOSS option and enter your server web service URL ( http://<servername>/_vti_bin/search.asmx)

Now we build a SQL search query to retrieve the properties

“select preferredname,firstname,lastname,workemail,workphone,pictureurl,Title,Description,write,Rank,Size, from scope() where “scope” = ‘People’ order by preferredname ASC”

Notice the double quotes around the scope. I am not sure why this is needed but it doesn’t work without the quotes. Enter the number of search results you will need by setting the “Count” field in the query tool. Once the SQL Query is in place click “Build Request XML” . This will build an XML fragment. Once the XML fragment is built, Click on QueryEx to ensure that you are getting the desired results. Remember to click build request XML everytime you make a change to the query.

The screenshot shows a the final results of this exercise. We will need the XML generated by the Build Request XML action. Copy the XML and paste into Notepad and remove all linebreaks in the XML. Ensure that the entire XML is available on a single line within Notepad. Here is how our XML query looks

<QueryPacket xmlns=”urn:Microsoft.Search.Query”>Query> <SupportedFormats><Format>urn:Microsoft.Search.Response.Document:Document</Format></SupportedFormats> <Context> <QueryText type=”MSSQLFT” language=”en-us”>select preferredname, firstname, lastname, workemail, workphone, Title,Path, pictureurl, description, write, rank, size from scope() where “scope”= ‘People’ and firstname != ” order by preferredname asc</QueryText> </Context> <Range> <StartAt>1</StartAt> <Count>1000</Count> </Range> <EnableStemming>true</EnableStemming> <TrimDuplicates>true</TrimDuplicates> <IgnoreAllNoiseQuery>true</IgnoreAllNoiseQuery> <ImplicitAndBehavior>true</ImplicitAndBehavior> <IncludeRelevanceResults>true</IncludeRelevanceResults> <IncludeSpecialTermResults>true</IncludeSpecialTermResults> <IncludeHighConfidenceResults>true</IncludeHighConfidenceResults> </Query></QueryPacket>

If you need to retrieve additional properties, add them to the SQL query. To refer to the fieldnames for the properties, click on

Central Admin -> Shared Service Provider -> User Profiles and Properties -> View Profile Properties

Click Edit on the property that you need and get the property name. In this case I needed the Profile Picture. The name of the property is PictureURL, which is what we need in the query

Now that we have our XML ready, this is time for us to build the web part. We will be using the Data View webpart available within SharePoint Designer for building the UI

The white paper linked in this article above is a must read for building Data View web parts.

First we will create a web part page to host our web part. Click Site Actions-> Create and select web part page to create a new web part page. Here we have used the Full Page Vertical option and named our page “EmployeeDirectory.aspx”. Now that we have our page ready we build the webpart using SharePoint Designer. Open the site within SharePoint designer and navigate to the web part page

Open the site within SharePoint Designer

Once the site is opened, click on the document library where the web part page was created. In our case it is Shared Documents.

Click on the EmployeeDirectory.aspx file to edit

Now we will connect to the MOSS web service and use the XML query to retrieve the user profiles. In the Data Source Library options in the right toolbar, select XML Web services and connect to a web service. Enter the location of the web service, which will be the same as what we entered in the Query tool and click “Connect”. Change the “Operation” to “QueryEx”. All other options can be left at the defaults.

Now we will use the Data View webpart to display these values. Click on the Web Part zone which says “Insert a Web Part” and insert a Data View Web Part by selecting Data View from the menu bar and “insert data view”. Your screen should look like this

The queryXML needs to be modified and our XML fragment from Part-1 needs to be used here. Click on queryXml and click modify and enter the search query from the notepad file. If your query has any line breaks, then your XML will not be copied over correctly. Ensure that your XML ends with a </QueryPacket>. If it does not, then you have some line breaks in your XML fragment. You can now switch to the General tab and set an appropriate name for this connection and set the authentication to Windows in the Login tab.

Click on the connection drop down and click Show Data to view the results of the search query during design time

We can see the fields returned from the web service as well as the data in those fields. There are 115 records returned in this case. Now you can drag and drop these fields on to the data view web part to create your employee directory. The DataView white paper provides information about the different options for formatting and working with the web part. In the code view in SharePoint Designer, search for <XSL>. The entire stylesheet for displaying your data is available within the <XSL></XSL> tags. You can develop the stylesheet externally and include it within these tags. Just ensure that the propertynames in the XSLT tags shown below match the query from the web service.

<xsl:variable name=”fullname” select=”Title” /> <xsl:variable name=”url” select=”Path”/> <xsl:variable name=”email” select=”WorkEmail”/> <xsl:variable name=”pictureurl” select=”PictureURL”/>. In the example above select=”Title” will need to be modified as select=”TITLE” and so on for the other properties.

Our web part is now ready. Save the changes and preview your page.

Here is a great article on how to include the presence information. The presence information is embedded into the XSLT by adding an image with a call to the IMNRC function onload.

<img
border=0
valign=middle
height=12
width=12


src=/_layouts/images/imnhdr.gif
onload=IMNRC(‘{$email}’)


id={generate-id()}
ShowOfflinePawn= 1 />

Click here for the web part. Search for SelectUrl and WsdlPath attributes and replace the links to match your environment

Click here if you just need the XSL.

Any changes to the profile, will be available upon the next indexer crawl, which is set up in the central admin console.

Update:

See Gavin’s post below for resizing the image

About these ads

41 Responses to MOSS 2007 Employee Directory web part using Search and Data View web parts

  1. Mark Smith says:

    Hi, I really can not get the XML format down on this. Atleast that is what I think is holding me back. I create the connection and when I go to “show Data” I can not connect. Is there a tool that may help? I thought I removed all the line breaks. All the text is in there, but it will not work.

  2. techdhaan says:

    Do you have the correct URL to the Web service ? It should be of the format http:///_vti_bin/search.asmx. Also please check the authentication settings.

  3. preet says:

    How can I conduct a search on the Employee Directory?
    I want to make the employee directory, but additionally provide the users to be able to search on skills, firstname, etc.
    Additionally can I browse the directory category wise?
    I was planning to use content query web part, do you suggest that?

  4. Damien says:

    Hi

    I need help please!!!

    Everytime I try to run QueryEX via SPD I get “The server returned a non-specific error”

    Query is

    urn:Microsoft.Search.Response.Document:Document

    select path from scope() where scope=(‘People’)

    1
    10

    true
    true
    true
    true
    true
    true
    true

    and in Sharepoint query test tool I get

    System.Web.Services.Protocols.SoapException: Your query is malformed. Please rephrase your query.
    at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
    at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
    at SharePointSearchWebServiceApp.MOSSSearch.QueryService.QueryEx(String queryXml)
    at SharePointSearchWebServiceApp.SPSearchQueryService.QueryEx(String strQueryXML)
    at SharePointSearchWebServiceApp.Form1.button3_Click(Object sender, EventArgs e)

    Any ideas, kind regards

    Damien

  5. techdhaan says:

    Preet,
    The out of the box People Search page provides tools for searching on skills/responsibilities etc. If you need those additional properties to be displayed in the employee directory, then you will need to add those to the query

  6. techdhaan says:

    Damien
    Can you post your QueryXML from the SharePoint query tool ? Do you have double quotes around the scope ? The query should be something like select path from scope() where “scope” = ‘people’
    Sai

  7. Damien says:

    Hi Sai

    QueryXML is

    urn:Microsoft.Search.Response.Document:Document

    “select path from scope() where scope=(‘People’) ”

    1
    10

    true
    true
    true
    true
    true
    true
    true

    Cheers

  8. Damien says:

    Sai email me at damien.harrison@tewv.nhs.uk so that i can email you the QueryXML as the comment box is breaking the code.

    Cheers

    Damien

  9. Priyanka says:

    Thanks for the reply. I used this, and added additional properties which I required. But I have 2 questions now. The first one, even if I add several properties in the query, the showdata returns only those columns which have data in it. due to which I cannot add all the columns in my dataview webpart. So is there any workaround for that, right now, I change the XSLT manually to include other fields.

    The second one is, as you have shown that webpart is added to the aspx file, and you have given a .webpart file for users to use. How do you make a .webpart file from the aspx page that you are showing. Am aware of making .webpart or .dwp file when making a webpart project, and then deploying the dll, adding it to safe control, and then adding it to webpart gallery. But how do I do it in this case where just have an independent aspx file and convert it to .webpart.

    I apolozise for being so novice, but am really very new to Sharepoint development. Can you give me the solution please.

    Thanks

  10. techdhaan says:

    Priyanka,
    Once you add the Data View web part to the ASPX page, open the page in a browser. Then you should be able to click the dropdown arrow on the chrome of the web part and export it to a web part file.

    As for the query items that didnt have values, I havent checked that yet. will respond back once I find something
    Shankar

  11. Philip Colmer says:

    Great article – thank you!

    I’ve got it all working except for two bits:

    1. Attempts to retrieve the manager results in an empty value – even when using the SharePoint Query Web Service Test Tool.

    2. If I add the presence piece as well, the presence icon doesn’t just appear where I put the code – it also appears over the user’s photo, their name and their email address! Any suggestions on what I need to do with the code to fix that?

  12. Philip Colmer says:

    I’ve solved #2 – you need to put the presence IMG tag inside something like a Span tag, i.e. span – img – span/

  13. Priyanka says:

    Hi, thanks for the prompt reply, but have already tried that. After adding everything in the designer, when I go back to the browser, there’s no dropdown arrow which generally appears with the webparts. Infact even after editing the page, the dropdown does not appear. Is it because the browser does not recognize the data view webpart as a valid one?

  14. techdhaan says:

    Priyanka
    Can you post your HTML Source here or send it by email to
    s-sh@nk@rNOSPAM@live.com [ Replace @s with a's and remove NOSPAM]
    Shankar

  15. Damien says:

    Hi, does anyone know of a way of making this feature available to anonymous users, when anonymous is turned on I get the following error.

    Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Windows SharePoint Services-compatible HTML editor such as Microsoft Office SharePoint Designer. If the problem persists, contact your Web server administrator.

    Regards

    Damien

  16. Ben says:

    I tried inputting your query…

    “select preferredname,firstname,lastname,workemail,workphone,pictureurl,Title,Description,write,Rank,Size, from scope() where “scope” = ‘People’ order by preferredname ASC”

    to retrieve the properties, but I got this error when I ran the QueryEx button…

    System.Web.Services.Protocols.SoapException: Server did not recognize the value of HTTP Header SOAPAction: http://microsoft.com/webservices/OfficeServer/QueryService/QueryEx.
    at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
    at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
    at SharePointSearchWebServiceApp.MOSSSearch.QueryService.QueryEx(String queryXml)
    at SharePointSearchWebServiceApp.SPSearchQueryService.QueryEx(String strQueryXML)
    at SharePointSearchWebServiceApp.Form1.button3_Click(Object sender, EventArgs e)

    Can you point me in the right direction to get the query working within the SharePoint Query Tool?

    Thanks,

    Ben

  17. Damien says:

    Hi Ben

    It looks at though you have anonymous access turned on to your site, if you turn anonymous access off and try again.

    Regards

    Damien

  18. Maggie says:

    What account do I need to grant permissions to so that I am not getting this error message?

    System.Security.SecurityException: Request for the permission of type ‘System.Security.Permissions.EnvironmentPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′ failed.
    at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
    at System.Security.CodeAccessPermission.Demand()
    at System.Net.CredentialCache.get_DefaultCredentials()
    at System.Web.Services.Protocols.WebClientProtocol.set_UseDefaultCredentials(Boolean value)
    at SharePointSearchWebServiceApp.MOSSSearch.QueryService.set_UseDefaultCredentials(Boolean value)
    at SharePointSearchWebServiceApp.SPSearchQueryService.set_UseDefaultCredentials(Boolean value)
    at SharePointSearchWebServiceApp.Form1.button3_Click(Object sender, EventArgs e)
    The action that failed was:
    Demand
    The type of the first permission that failed was:
    System.Security.Permissions.EnvironmentPermission
    The Zone of the assembly that failed was:
    Internet

  19. Aaron says:

    Howdy. Naturally I came across this and it is exactly what I am looking for, but I am having problems at the last step!

    I have done all the work in the searsh query tool, getting the data in QueryEx just as I need. The XML Query is:

    urn:Microsoft.Search.Response.Document:Document

    select Title, Path, Description, Write, Rank, Size, preferredname, firstname, lastname, workemail, workphone, workcountry, pictureurl, jobtitle, picturethumbnailurl, company, department, manager from scope() where “scope” = ‘People’

    1
    1000

    false
    false
    false
    false
    false
    false
    false

    Ive converted this to one line as instructed:

    urn:Microsoft.Search.Response.Document:Document select Title, Path, Description, Write, Rank, Size from scope() where “scope” = ‘People’ 1 1000 true true true true true true true

    Ive then created the connection in SPD, pasted in the value – all loks good…

    But when I go to show data it hangs for a while and comes back with the entirely informative “The server for the data source returned a non-specific error…”

    Any suggestions? I really like this idea, and would love to get it wortking, or at least understand why it doesnt!!

  20. Alex says:

    Hey, good article. Unfortunately I too am running into an error. Here’s my xml:

    urn:Microsoft.Search.Response.Document.DocumentSELECT preferredname, firstname, lastname, path FROM SCOPE() WHERE “contentclass” = ‘urn:content-class:SPSPeople’14

    This works fine and returns 4 results. Now here’s where it gets weird; if i increase the counter over 45, i’m getting an error.

    ERROR_SERVER System.Runtime.InteropServices.COMException

    I have no idea why 45 is the magic number, but it is. also adding to the problem is that this just started when we moved indexing to a dedicated server; it used to be on the front end server and never had a problem, no matter the count.

    Any ideas?

  21. Hey there,

    This is nice stuff. You might want to modify how the XSLT handles the profile image, as I’ve just been struggling to do something similar for the last day. The piece below will use the standard SP functions to resize the image in case it is over 100 pixels wide, as it does when displaying in webparts and on the Profile pages.

    Cheers
    Gavin

    table

    _TR

    _TD

    padding-right:5px;
    imgResizeMax(”, 100);imgResizeTbl(”);

    window.setTimeout(“imgResizeMax(”, 100);imgResizeTbl(”);”, 500);

  22. Derek Brown says:

    @Gavin Pollock: I have a few profile images that exceed the 100 pixel width, and it makes the page somewhat awkward looking. I don’t understand where to put the code you mentioned above…

    @anyone else: I see the little circle icon for presence information, but none of them seem to work. I can click the circle, and I get a context menu, but none of the circles show green (for users who I know are online). Thoughts?

  23. Roman Eremenko says:

    Hello!
    How this webpart should be installed to Portal? Should it be wrapped with additional .xml to make this a “Feature”?

  24. Christina says:

    this is a great tool and definitely what we needed. It is working just fine, but how to I get the data to update automatically from the search? I changed the criteria that is pulling from the sharepoint people scope, but the employeedirectory.aspx is not updating to match.

    Thank You.

  25. techdhaan says:

    Christina
    Your search indexer should be scheduled to update frequently. This should make sure that the web part is updated

  26. naresh says:

    Hi, I am struggling in displaying the “showdata” after passing the queryex to the search webservice in sharepoint
    i can retrieve the results using the tool,
    here is my queryEx:
    urn:Microsoft.Search.Response.Document:Document select preferredname,Title, Path, Description, Write, Rank,Size from scope() where “scope” = ‘people’ order by preferredname ASC1100truetruetruetruetruetruetrue

    and i also can see the result query by using this code in visual studio webreference program.. this is the program i used for debugging:

    class Program
    {
    static void Main(string[] args)
    {
    sharepointdev.QueryService qs = new ConsoleApplication2.sharepointdev.QueryService();
    qs.PreAuthenticate = false;
    qs.Credentials = System.Net.CredentialCache.DefaultCredentials;
    DataSet ds = new DataSet();
    //string ds;
    ds = qs.QueryEx(@” urn:Microsoft.Search.Response.Document:Document select preferredname,Title, Path, Description, Write, Rank,Size from scope() where “”scope”” = ‘people’ order by preferredname ASC11000truetruetruetruetruetruetrue”);

    }
    }

    I have also checked the query by directly passing it to queryex method in query service.
    http://sharepointdev/_vti_bin/search.asmx?op=QueryEx
    and I do get the result set.

    all i need is to get the result set once i clik on show data in sharepoint designer. have followed this article and can not see the result after repated trails in every possible way.

    seems like problem is with the sharepoint designer. I have even tried with the sharepoint support link:
    http://support.microsoft.com/kb/923173
    but has no luck. can somebody please help me.

  27. Ray Gaida says:

    Naresh,

    Do you have SharePoint Designer installed on the Web Frontend, or are you running it on your workstation?

  28. naresh says:

    Hi,Ray Thanks for the reply…
    I have sharepoint designer installed on my local pc. and we do not have this installed on the server. although i could not get the results connecting to the development server i am getting the data if i use production server. and production server doesn’t have the designer installed in frontend too. and the difference between these two servers is enterprise feature is not enabled in production but it is enabled in development.

    I am trying out creating search criteria as text boxes and dropdowns and add a search buttton. so that search should occur on button press event to display based up on property we select.. for example i ant to search for person whose First Name = “some name” or Last Name = “some name”, Department to be selected from a drop down List and Location should be selcted from a dropdown list. then after cliking on search button results should appear.
    in this scenario, i am not getting how do i connect this criteria fields to my people search data view webpart. do i need to create multiple queries or how the querying and sorting should be done and connect the result set to the text box criteria text.

    please suggest me a solution for this problem.
    thanks.

  29. Saleem says:

    Hi,

    I am trying to query and everytime I do, I get different results and the columms shown are “Workid”, “Rank”, “title”, “author” etc, it never shows any of the fields selected in the query. what could be the problem.

  30. Yannick Laurent says:

    Saleem,

    I’ve been same issue.

    resolution :
    the copy/past of the line “Select…” at the beginning of this post doesn’t works as is.
    first, you have to remove the last comma after the last property (Size)
    Second, you have to rewrite the simple and double quote (arround scope and People).

    AND last point : don’t select MOSS option, but choose SQL.

    (AND if your MOSS is not in english, like me, the name of the scope is probably not ‘People’ !)

    hope that help you.

  31. Madhu says:

    Hi,
    I have a user profile property called ‘office’ which is mapped to data source field ‘PhysicalDeliveryOfficeName’ in ADS/Ladap.
    However when I try adding this in the query I get an error. Is there something special about certain fields?

  32. Mustafa says:

    My solution for the following Exception:

    “System.Web.Services.Protocols.SoapException: Server did not recognize the value of HTTP Header SOAPAction: http://microsoft.com/webservices/OfficeServer/QueryService/QueryEx.”

    I found out that there were some library references wich had to be added in order for the Web Service to work. After adding the following references to my project my problems were solved, let me know if it helps!

    Microsoft.Office.Server
    “C:\WINDOWS\assembly\GAC_MSIL\Microsoft.Office.Server\12.0.0.0__71e9bce111e9429c\Microsoft.Office.Server.dll”

    Microsoft.Office.Server.Search
    “C:\WINDOWS\assembly\GAC_32\Microsoft.Office.Server.Search\12.0.0.0__71e9bce111e9429c\Microsoft.Office.Server.Search.dll”

    Microsoft.SharePoint.Search
    “C:\WINDOWS\assembly\GAC_32\Microsoft.SharePoint.Search\12.0.0.0__71e9bce111e9429c\Microsoft.SharePoint.Search.dll”

  33. Kate says:

    Hi
    I have tried this.. and key in all information exactly the same as yours. When i click queryex, it shows the table but it return nothing. I have 15 staff records in Sharepoint. any idea ?

    THanks a lot

    • Kate says:

      Hi Kate,

      I encountered the same problem and i solve with this steps :
      1. Replace “scope” to double quote “scope”
      2. Replace ‘People’ to single quote ‘People’
      3. Use query type SQL instead of MOSS

  34. Kate says:

    HI all,

    Is there any way to use this feature as a list instead of webpart? THanks

  35. Yannick Laurent says:

    Hi, Madhu,

    same problem with ‘Office’ property which is not referenced.
    Instead, I found HighConfidenceDisplayProperty4 property which contain ‘Office’ value !

  36. Maya says:

    Hello,

    What should I do if I want to have the SharePoint Employee Directory web part in wss3.0?
    We don’t have user profiles in wss3.0!
    please let me know if you have any solutions.
    Thanks

  37. KyleB says:

    The SharePoint Search Query Tool no longer exists, its been replaced by the SharePoint Search Service Tool at http://www.codeplex.com/SharePointSearchServ I have tried this but when I test it I get:

    -
    -
    ERROR_BAD_QUERY
    Your query is malformed. Please rephrase your query.

    My Query is:

    urn:Microsoft.Search.Response.Document.Document

    11000
    true
    true
    true
    true
    true
    true
    true

    Can I get a little help? There may be a difference in between the original tool and the new one. I dunno.

  38. Chau says:

    Thank you for the post. I tried it and got the web part to work perfectly. Can you tell me how I can set parameters to the query to be filter on the web part? What I want to to show individual departments on each page.

  39. Pingback: links for 2009-07-21 « Jet Grrl

  40. Pingback: MOSS 2007 Presence Awareness Problem « Doug Dossett

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: