MOSS 2007 Department and People Viewer Part 1/2

• Download DPV - 4.82 KB

Introduction

A few weeks ago I was asked to prepare a list of employees working in different departments, but summarized in one head-departement. This list should reside on a Webpart-page of the head-department. The layout should be in many parts different from the standard-layout which People Search Core Result (PSCR) gives and it should fight against some limits that are build into PSCR.

These limits are:

• the number of results returned from the People Search is limited to a maximum count of 50 per round-trip
• sorting and grouping is only fragmentary
• when grouping/sorting over a resultset of more than 50 records is necessary, things get impossible with PSCR
• some people-properties are hard to get by PSCR

Additionally I have some other restrictions which, I think, many normal users in bigger, productive MOSS 2007-environemts have:

• I'm not able (or better said not allowed) to deploy any MOSS-solutions
• I can only use MOSS's build-in webparts

But fortunately I'm allowed to use Microsoft's Office SharePoint Designer 2007 and I have a fair knowledge of SQL, XML and XSLT!
So I'm gonna present you the result of my many trial and error roundtrips and a bunch of some tools (especially XSLTs) making the work easier for you.

I've divided this article into two parts becasue of my little spare time for writing it.

This first part gives you directions on how to get the data with the help of SharePoint's webservices.

The second part (coming soon) will provide you with some enhanced XSLT to format, page and group these data.

Background

After many trials to tune PSCR getting my wishes done, I decided that the only way to get arround the hardest limit, the 50 records per roundtrip, is to use a very basic, but always available webpart: WebPartPages:DataFormWebPart. This one is a very basic one, but has the advantage that you can influence it on your own.

Using the DataFormWebPart to retrieve People-Information

DataFormWebPart can retrieve data from various <DataSources>. For receiving people-data I decided to use one of SharePoint's webservice-methods. You can look up search-methods by browsing to http://yoursharepointserver/_vti_bin/search.asmx . The method we need for getting people-information is the QueryEx-method with a parameter queryXml.

A valid parameter-XML looks something like this:

<QueryPacket xmlns="urn:Microsoft.Search.Query" Revision="1000">
    <Query>
        <Context>
            <QueryText language="en-US" type="MSSQLFT">SELECT PreferredName, Department, Manager, AboutMe,
             Title, Path, Description, Write, Rank, Size  FROM SCOPE() where "scope"='People'
             AND Department LIKE 'MAN DEP 1'</QueryText>
        </Context>
        <Range>
            <StartAt>1</StartAt>
            <Count>1000</Count>
        </Range>
    </Query>
</QueryPacket>

A detailed description of the QueryPacket-syntax can be found here:
http://msdn.microsoft.com/en-us/library/ms563775.aspx

Most interesting in this context is the <QueryText>-element:
The attribute type="MSSQLFT" means that this a a "Microsoft SQL Full Text"-Query. So it is somewhat like SQL, but also somewhat different:

• The columns to be retrievd (PrefferedName, Department,...,Size in my example) are not table-columns but they're managed properties of your SharePoint-Server (to be exact: of your shared service provider of the MOSS 2007).
• The FROM-part is not a table, is is (since MOSS 2007) always SCOPE()
• The where-part consists of two different parts:
  • the scope-restriciton; because we want to look for people, we have to use the people-scope
    be aware of the correct use of the " and ' - signs! It took me an hour to get that misty syntax work.
    A Deeper description of search-scope would go far behind the scope of this article
  • the restriction on the properties; in my example I want to restrict the search to people working in the department called 'MAN DEP 1'.
    Don't use equal-sign (=)! Use LIKE and if you want to you can also use wildcards

Also interesting is the <Range>-element: <Start> determines from which position on the result will be returned; <Count> gives the number of results returned. My hardest limit and my motivation for doing this work was the "50-record per roundtrip"-limit; so here we can bypass that limit. It's up to you to make this number a high a you like; the default is 100.

Seeing the result-data

But some questions will arise now to you:

1. So what does the query return?
2. Where do I get a list of those properties in the columns-part of the MSSQLFT?
3. And how can I do that in SharePoint-Designer?

1) The query returns XML (Surprised? - No, not really). This makes absolutly sense, because then we can render it by using XSLT.

2) A list of properties supported by your Shared Service Provider / MOSS 2007 can be obtained by another method of the same webservice: GetSearchMetaData (see below on how to view also these information on a SharePoint weppart-page)

3) Yeah, this is the most "frustrating" part in it, because IMO SharePoint Designer doesn't support this kind of work very well! But it works.

• Open up the weppart-page (for exapmle on your MySite) in which you want to add your search in SharePoint Designer
• Select the zone where you want to add the search (for example "Full Page" if you have used the "Full Page"-template for your webpartpage; which makes sense if you have much to show)
• In the menu click "Insert --> SharePoint-Controlelement --> Dataview (I hope I've translated those menu-items correctly; I work with a german version)
• In the created WebPartPages:DataFormWebPart click on the link for the datasource-library. This will open up a panel which shows you the actual datasources of your actual site. Inside that panel you should have a section XML-Webservices with a link for connecting to a webservice. Click on this
• In the now opened datasource-window type the adress: http://yoursharepointserver/_vti_bin/search.asmx?WSDL (there is a underscore in front of the v and the b; so it is _vti_bin) and click on "Connect now"
• Now select "Select" in the next listbox and in the action-listbox select QueryEx
• In the parameter-list double-click on queryXml
• Now enter the xml for your query (see above). Yes, I know it's a horrible editor and you must enter it all in one line. So it's best to have the queryXml prepared in an editor of your choice first, remove all line-breaks and then copy it. But also see below; I'll provide you some help on doing it semi-automatic.
• In the datasource-library you should now have a new "QueryService at yoursharepointserver" under the XML-webservices; click on the context-menu on it and select the first menu item "View Data"
• If your query is ok and receives results you should see a treeview starting with soap:Envelope --> soap:Body ..... If not, there is a big chance that something is wrong with the sysntax of your query.
• Scroll down and look for diffgr:diffgram (.NET-prgrammers will mention that this is a serialized DataSet). Under that you will find RelevantResults and you can click on the small arrows on the right side to navigate through your matching people-records
• You're now able to select the wished fields and drop them to your DataFormWebpart; but the support for formatting here is very mysty and I don't like it. SharePoint Designer builds up XSLT in the background and puts it into the <XSL></XSL>-tags of the DataFormWebpart. So if you have a XSLT-editor (or VisualStudio) and are somewhat familar with XSLT it's maybe easier to use those tools. If you've the permission to modify your webpart on your webpart-page (you should normally have, because it's YOUR site), you can directly copy&paste the XSLT into the properties there.

Because most times I want first to have the data and afterwards I pay attention to the formatting, I used this XSLT whereever I wanted to see what comes back from my query (see file ShowAllXML.xslt):

<?xml version="1.0" encoding="utf-8"?>D
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
      <xsl:template match="/">
            <xmp>
                  <xsl:copy-of select="*"/>
            </xmp>
      </xsl:template>
</xsl:stylesheet>

This XSL just takes the input XML and render it back like XML; xsl:copy-of select="*" takes every node in the XML and gives it back; the surrounding <xmp>-tags give the information to the browser, that it should be treated like text (and not like tags, because then we would only see the parts of the input-XML which are not tags).

Inside SharePoint/MOSS 2007 XSL is used in the following way:

So, whenever you use now webservices inside your SharePoint-pages, use this simple XSL to first look what data you really get. And, another advantage: you can copy the rendered HTML, which is shown like XML, to your XSLT-tool as an input-file for testing the XSL.

If you don't have any XSLT-tool, you can alos use Internet-Explorer's build-in XSLT-processing capability by referencing the XSLT-file (XSLTFileTableView.xslt in my example here) which IE shall use to show your XML:

<?xml version="1.0" encoding="utf-8"?>
<!--Referencing the correct XSLT to use-->
<?xml-stylesheet type="text/xsl" href="XSLTFileTableView.xslt"?>
<All_Results>
....Rest of your XML...
</All_Results>

So, where are we now?
We got:
- some parts of the needed data (maybe not all the properties you like to see)
- a way to look into the data in SharePoint-Designer
- a way to render the data as xml in our webpart-page

What's missing:
- a list of properties we can fetch (the "columns" of the MSSQLFT)
- some really good formatting with grouping and paging

Properties for the MSSQLFT

As mentioned above, the webservice-method GetSearchMetaData gives the information about the available properties for the QueryEx-method. So just put another DataFormWebPart to your search-page (or create a nwe page and insert it there), create a new connection to the search.asmx of your site and now use the GetSearchMetaData instead of the QueryEx-method. Save the webpage, modify the webpart and copy the XSL of ShowAllXML.xslt (see above) into the XSL-editor of your webpart's properties. As a result you get an XML showing you the SearchMetaData of your site. Each managed property is one of these elements:

...
<Properties diffgr:id="Properties68" msdata:rowOrder="67" diffgr:hasChanges="inserted">
              <Name>Manager</Name>
              <Description />
              <Type>System.String</Type>
              <Retrievable>true</Retrievable>
              <FullTextQueriable>false</FullTextQueriable>
</Properties>
...

The <name>-element gives you the name that you can use in your MSSQLFT-query of the QueryEx. I cannot provide you a full list of the properties because they are dynamically / configurable on the SharePoint-Shared Service Provider. What I've mentioned in our environment is, that for the people-properties which shall be exposed to the query-service, there is a need for indexing them. So if you're missing a property in your list, the chance is good to ask your SharePoint-admin to set the property to "indexed" in the people-import properties of the Shared Service Provider (your admin will know where to find it :-) )

The list of properties may be horrible long and may miss descriptions. So you will have to filter them somehow. Becasue I hate to copy&paste the names out of the XML into the QueryEx-Expression-XML, I did the following:

• I took the complete output of SearchMetaData and saved into an XML-file (see PropertiesAndScopes.xml)
• I commented out those properties I don't want to use for the QueryEx by using <!-- just before the <Properties>-tag and --> directly behind the closing </Properties> tag
• then I used the XSLT-file PropertiesAndScopesToQueryPacket.xslt to process the PropertiesAndScopes.xml in Visual Studio 2005; the output of this is one line of text, which you can copy&paste to the QueryEx-Expression editor in SharePoint Designer. If you like to use IE, please copy the third line of my example-file so that is uses the slightly different XSLT for IE:
  

  <?xml-stylesheet type="text/xsl" href="PropertiesAndScopesToQueryPacketIE.xslt"?>

  
  Then open up the xml in IE and copy all to your clipboard and then paste into SharePoint Designer's QueryEx-Expression.

Take care of the following when using this:

• Inside the PropertiesAndScopesToQueryPacket.xslt look for the text: where "scope"='People' AND . After the AND insert your own where-criteria; you can use any criteria in the where-part provided that you've included the property in the select-part (so you shouldn't have it commented in the steps before)
• You need to include the properties Title, Path, Description, Write, Rank and Size (see http://msdn.microsoft.com/en-us/library/ms543175.aspx)
• (I mentioned that the length of the complete QueryPacket is limitted to appr. 5KB. Whenever my QueryPacket was longer, I received errors in the webpart/SharePointDesigner; I'm not sure, where this limit comes from and I didn't find any notes about it. So try yourself.)

So you should now have the wished people-records with the properties you want in XML-format, and NOT limitted to 50 records.

The second part of this article will then give you a guide on how to format, group and paging this result with the help of XSLT. Stay tuned!

History

This is the first version of this article.