Using Analysis Services data in Excel Services part 3 – Create and test an Excel sheet

March 13th, 2007 by tonstegeman

This is the third and last part in a small series on my adventures to get Excel Services working with SQL Server 2005 Analysis Services. In this part we will create and test an Excel sheet.

Part 1 – Preparing the AD for Kerberos
Part 2 – Preparing the MOSS server
Part 3 – Create and test an Excel sheet
Part 4 – Overview and updates

Step 1 – Set permissions

The first thing to do is make sure that your user account has acces to the Analysis Services cube.

Step 2 – Create a data connection file

  • The first step is to create an ODC file the holds the connection to the Analyses Services cube. In my test environment I used the Microsoft Adventure Works sample. Go to the ‘My Data Sources’ folder in My Documents and double click ‘+Connect to New Data Source’
         Excel12
  • When the Data Connection Wizard starts in Excel, select Microsoft SQL Server Analysis Services and click Next.

               Excel13

  • Or start Excel 2007, select the Data tab and click ‘From Other Sources’ and then select ‘From Analysis Services’.
         Excel14
  • In the next screen of the wizard, select the server– and instance name of the Analysis Services server. Set the log on credentials to Use Windows Authentication.
         Excel15
  • In the next screen select your cube:
         Excel16
  • In the last screen of the wizard, enter a filename for the ODC files and also add a friendly name and description. Make sure that you check the box ‘Always use this file to attempt to refresh data’.
  • Click the button ‘Authentication Settings’ and select ‘Windows Authentication’
         Excel17
  • Your ODC file is now saved.
  • Navigate to the Data Connection Library (you created this in step 2) and upload your ODC file.
          Excel18

Step 2 – Create the Excel sheet

Start Excel and on the Data tab click Existing Connections. Click the button ‘Browse for more’ and navigate to the data connection library in your SharePoint site. Select the ODC file.
     Excel19

Now create the report you want.
      Excel20

Step 3 – Publish the Excel Sheet

After you finished creating your report, click on the Office button (you need the Enterprise version of Excel for this) and select ‘Publish’. Enter the url to your document library (see step 2) that was marked as a trusted file location and save the Excel sheet. After you have done this, Excel Web Access will open (if you checked the box) and load the Excel sheet.
     Excel22

If you succesfully configured all elements that I have discussed, you will be able to drilldown into this pivottable. If you did not, you will end up with this message:
     Unable to retrieve external data for the following connections:

     Adventure Works

     The data sources may be unreachable, may not be responding, or may have denied you access.

     Verify that data refresh is enabled for the trusted file location and that the workbook data authentication is correctly set.
This is shown as in the screenshot below.
     Excel23

If you did it all well, you should be able to drilldown into your pivot table, or refresh the data from the server:

     Excel27

Using Analysis Services data in Excel Services part 2 – Preparing the MOSS server

March 12th, 2007 by tonstegeman

This is the second part in a small series on my adventures to get Excel Services working with SQL Server 2005 Analysis Services. The first part describes the changes to the Active Directory to setup the Kerberos authentication for delegation of user accounts. In this part we will prepare the SharePoint server.

Part 1 – Preparing the AD for Kerberos
Part 2 – Preparing the MOSS server
Part 3 – Create and test an Excel sheet
Part 4 – Overview and updates

Step 1 – Set the Access Model

The first thing to do is set the Access Model on the server. After installing MOSS in a farm with multiple servers, the access model is default set to ‘Trusted subsystem’. This has to be set to ‘Delegation’. To do this you have to run these STSADM commands:

  • stsadm -o set-ecssecurity -ssp SharedServices1 -accessmodel delegation
  • stsadm -o execadmsvcjobs

In the first command, ‘SharedServices1’ is the name of my Shared Services provider.

Step 2 – Create a Data Connection Library

A Data Connection Library is a special type of document library that can hold ODC files. These are the data connection files that you use in your Excel sheets to connect to external data sources. Go to your SharePoint site and create a Data Connection Library (DCL) where you want it to be.
      Excel9

After creating the DCL, you need to trust it. By doing this, you tell SharePoint the the ODC files in this library are safe and can be used to get data from external data sources. To do this, go to the admin site for your shared services provider, and select ‘Trusted data connection libraries’ in the ‘Excel services settings’ section. Click ‘Add Trusted Data Connection Library’and enter the url to the DCL you just created.
Excel10

Step 3 – Create a document library

Anywhere in your SharePoint site, create a document library that you will use to publish you Excel sheets to. After creating this library, you have to tell Excel Services that it can trust this location for loading excel sheets. Navigate to the Shared Services admin site and select ‘Trusted file locations’ in the ‘Excel services settings’ section. Click ‘Add Trusted File Location’ and enter the url to the document library you just created.

Excel11

In the External Data section on the same page, make sure that you set the setting ‘Allow External Data’ to ‘Trusted Data Connection Libraries only’.

     Excel26

Step 4 – switch your web application to Kerberos authentication

  • Start the Central Administration and navigate to the Application Management tab.
  • In the ‘Application Security’ section, click ‘Authentication Providers’. Make sure that the web application of your site is selected in the dropdown on the topright of the page.
  • Click on the ‘’Default’’zone.
  • Change the IIS Authentication Settings from ‘NTLM’ to ‘Negotiate (Kerberos)’.
         Excel28
  • Reset IIS
  • Log off your client and log back on again

At this stage all server are ready for us to test the solution, which we will do in the next (and last) post.

Using Analysis Services data in Excel Services part 1 – Preparing the AD for Kerberos

March 11th, 2007 by tonstegeman

 I started to work with Excel Services in combination with data from Analysis Services. First I configured this all in 1 virtual machine, which was easy, because it just works. The next step was to get it working in a real world scenario at one of our customers. Here we have a SharePoint server which also runs Excel Services. Databases and the Analysis Services cubes are on a different machine. The requirement is that we need to impersonate the user to Analysis Services. To be able to do this you have to implement delegation of user account using Kerberos authentication (or SSO). NTLM is not enough, because the web server cannot delegate the current user to the SQL Server. This is also known as the double hop.
In this small series I will descibe all steps that I took to get it working in a virtual test environment. First I will describe the setup of my environment and after that I will describe the changes to the Active Directory.

Part 1 – Preparing the AD for Kerberos
Part 2 – Preparing the MOSS server
Part 3 – Create and test an Excel sheet
Part 4 – Overview and updates

In my test lab I have 3 machines:

Active Directory and SQL Server 2005

  • Machine name: office2007
  • FQDN: office2007.tst.intra
  • Domain: tst
  • Roles: Active Directory, SQL Server 2005 SP2, Analysis Services
  • SQL services run under a domain account (tstsqlservice)
  • Domain functional level: Windows Server 2003

SharePoint and Excel Services

  • Machine name: tstmossdev
  • FQDN: tstmossdev.tst.intra
  • MOSS installed in a small server farm topology
  • Separate domain accounts for all application pools and services
  • Web Application on http://intranet (application pool identity: tstintranetapppool)

Client machine

  • Windows XP SP2
  • Excel 2007
  • Username: tst on
  • tst on is a member of the site running on the SharePoint server
  • tst on has reader access to the Analysis Services cube.

Below you will find all the steps for configuring the AD, to work with Kerberos authentication.

Step 1 – SPN for SQL Server account

The account that runs the SQL Server services (in my case TSTsqlservice) must have a Service Principal Registration in the AD.
To register this SPN, we use SetSPN.exe. This tool is part of the Windows 2003 Support tools. More information can be found in this kb-article. You have to be a domain admin to run these commands. The commands for my environment are:

  • setspn –a MSOLAPSvc.3/office2007:tstdev05 tstsqlservice
  • setspn –a MSOLAPSvc.3/office2007.tst.intra:tstdev05 tstsqlservice

You have to run both commands. In this command, ‘office2007’ is the servername of the SQL server and ’tstdev05’ is the SQLinstance. For more information on the exact syntax of the commands, please read this blog post by Mosha Pasumansky.

If you want to check what SPNs your account has, you can use setspn -l; in my case setspn -l tstsqlservice. After running the commands above, this returns:
     Excel4

Step 2 – SPN for Application pool account for the SharePoint/Excel server

Just as the SQL Service account, the account that runs the application pool of your web application also needs to have a SPN. The commands that we need to run to create the required SPN for this account:

  • setspn –a http/tstmossdev tstintranetapppool
  • setspn –a http/tstmossdev.tst.intra tstintranetapppool

In this example, ‘tstmossdev’is my SharePoint/Excel services server. After running these commands, the spn’s should look like this:
     Excel29

Step 3 – Enable delegation for the application account

The account we created a SPN for in step 2, needs to be enabled for delegation. To do this, find the service account in your AD and go the the properties. Navigatie to the Delegation tab and select “Trust this user for delegation to any service”:
     Excel30

Some remarks:

  • This screenshot is based on a domain running in Windows Server 2003 mode. If you are using the “Windows 2000 mixed” domain functional level, things look a bit different.
  • If there is no Delegation tab, check your SPNs, they are probably not correct.
  • I couldn’t get it to work using constrained delegation. I suspect it has something to do with the fact that I am using named instances in my SQL Server setup. Therefore I trust the account to delegate to any service.  

Step 4 – User accounts

Make sure that your users that connect to the cube through Analysis Service have the checkbox ‘Account is sensitive and cannot be delegated’ on the account page cleared:
     Excel7

 Step 5 – Enable delegation for the SharePoint server

The server that is running Excel services (in my case SharePoint and Excel Services) must be trusted for delegation as well. Find the server in your Active Directory, navigate to the Delegation tab and select “Trust this user for delegation to any service”:
     Excel31
In my sample setup this is the TSTMOSSDEV machine.

In part 2 of this series we will configure Excel Services and SharePoint to use the Kerberos authentication.

Managing Content Type order and visiblility on a SharePoint list in code

March 2nd, 2007 by tonstegeman

In SharePoint 2007 and WSS v3 it is possible to add multiple content types to a single list or document library. After adding the content types to your list, you can use the option “Change new button order and default content type” to change the order of the content types. If you uncheck the Visible checkbox, this content type will not appear in the New menu. The first content type in the list is considered the default content type. When a user clicks the New button instead of opening the New menu, the default content type will be selected for the new list item or document.

Besides setting these options in the user interface, you can also set them from code. In my example, I have a document library with 3 custom content types added in the wrong order as you can see below. This example will work on any SharePoint list.

     Contenttype1

In the code example we will re-order the content types and change the visibility. We only want content types 1 and 2 to appear on the new menu. While investigating how to do this, I found that these properties are stored at a folder (SPFolder) level. This means that you can sort your content types in a different way for each folder. And you can also let each folder have a specific set of content types. To see this in the user interface, create a folder, open the drop down menu and select “”.

     Contenttype2

The code snippet below sets the options for the list itself. This uses the RootFolder property of the list to find the correct folder. The sample assumes that you have a list or document library with some extra content types assigned.

    SPSite site = new SPSite("http://office2007:300");
    SPWeb web = site.AllWebs["intro/beheer"];
 
    SPList list = web.Lists["Docs"];
    list.ContentTypesEnabled = true;
    SPContentType listCt1 = list.ContentTypes["ContentType1"];
    SPContentType listCt2 = list.ContentTypes["ContentType2"];
    SPContentType listCt3 = list.ContentTypes["ContentType3"];
 
    SPFolder folder = list.RootFolder;
    SPContentType[] orderedContentTypes = new SPContentType[2];
    orderedContentTypes[0] = listCt1;
    orderedContentTypes[1] = listCt2;
    folder.UniqueContentTypeOrder = orderedContentTypes;
    folder.Update();

 

Each SPFolder object has a property called UniqueContentTypeOrder. This is an IList of SPContentType objects. Content types that are not in this list, are not visible. After running the code, the content types at the document library level now look like this:

     Contenttype3

 

Navigation options in a SharePoint Publishing Site

March 1st, 2007 by tonstegeman

As with almost anything in SharePoint 2007, it is possible to set the navigation options for a publishing site in code. To be able to do this, you need to reference the Microsoft.SharePoint.Publishing assembly and add these using directives.

using Microsoft.SharePoint;
using Microsoft.SharePoint.Publishing;
using Microsoft.SharePoint.Navigation;

Initialize

The following examples all use this initialization snippet:

    SPSite site = new SPSite("http://office2007:300");
    SPWeb web = site.AllWebs["intro/webcms"];
    string pagename = "IntroductieCMS.aspx";
 
    PublishingWeb pw = Microsoft.SharePoint.Publishing.PublishingWeb.GetPublishingWeb(web);
    PublishingPageCollection webpages = pw.GetPublishingPages();

Example 1 – Switch “Show Pages” on

This example switches the “Show Pages” option for a publishing site on. This setting is found in SharePoint on the Modify Navigation page.
         Navigation1

    if (!pw.IncludePagesInNavigation)
    {
        pw.IncludePagesInNavigation = true;
        pw.Update();
    }

Example 2 – Exclude a page from the navigation

This sample excludes the page called “IntroductieCMS.aspx” from the navigation. This is a normal page in the Pages document library.
         Navigation2

    PublishingPage page = webpages[string.Format("Pages/{0}", pagename)];
    if ((page != null) && (page.IncludeInCurrentNavigation))
    {
        page.CheckOut();
        page.IncludeInCurrentNavigation = false;
        page.Update();
        page.CheckIn("changed the navigation options");
    }

In this sample the page is in a Pages library that has versioning with the Require Check Out switched on. There we need to to a checkout of the item, before we can change it.

Example 3 – Add a link to the navigation

This example adds a custom link to the current navigation. This is the navigation on the left side of the page.
         Navigation3

    SPNavigationNodeCollection navNodes = pw.CurrentNavigationNodes;
    SPNavigationNode newNavNode = new SPNavigationNode("e-office", "http://www.e-office.com", true);
    navNodes.AddAsLast(newNavNode);
    newNavNode.Properties.Add("NodeType", NodeTypes.AuthoredLinkPlain.ToString());
    newNavNode.Update();

By using the NodeType property, you can choose the type of link. You can also use this to add a heading to the navigation. Please make sure that you first add the node to the collection and the set the properties in the HashTable. Otherwise the Properties value will be null and your code will fail.
To add a link to the navigation on top of the page, use the GlobalNavigationNodes property of the PublishingWeb.

These are just a few very basic examples, but they should be enough to get you started.

Adding a custom action to the MOSS expiration policy

February 20th, 2007 by tonstegeman

Office SharePoint 2007 out of the box has a policy framework, that can help with information management in your organization. On of the out of the box policies is the expiration policy. You can setup a policy for a site collection, content type of SharePoint List that will expire your item after a certain number of days, months or years after one of the date field values in your item. You can expire all whitepapers for example 5 years after they have been last modified.
Expire1

The action to SharePoint will perform when the item expires depends on you, Out of the box there are 2 options: The items is deleted or a workflow is started. If a workflow is available for the content type or list, it will be listed in the dropdown.
Expire2

It is also possible to create your own actions. After you have created and registered your action, it will show up in the “Perform this action” dropdown in the screenshot above.

Step 1 – Implement the interface

To create a custom action, you need to implement the IExpirationAction interface that can be found in the namespace Microsoft.Office.RecordsManagement.Features.

    public class ExpirationSendWarning : IExpirationAction 
    {
        public void OnExpiration(Microsoft.SharePoint.SPListItem item, 
            System.Xml.XmlNode parametersData, DateTime expiredDate)
        {
            // Do whatever you need to do with you item when it expires
        }
    }

You need to put this in a strong named assembly and add it to the GAC.

Step 2 – Create the registration xml

After creating the expiration action, you need to register it by creating an xml file. You will find an example below. The featureId attribute of the PolicyResource element is important. This needs to be the ID of the out of the box expiration policy. The rest is pretty clear.  Save this xml to a file called “actionmanifest.xml”

<?xml version="1.0" encoding="utf-8" ?>
<p:PolicyResource id="TST.POC.PolicyFeatures.ExpirationSendWarning" 
       featureId="Microsoft.Office.RecordsManagement.PolicyFeatures.Expiration" 
      type="Action"  xmlns:p="urn:schemas-microsoft-com:office:server:policy">
  <p:LocalizationResources>dlccore</p:LocalizationResources>
  <p:Name>ExpirationSendWarning</p:Name>
  <p:Description>Sends a warning on the reminder date</p:Description>
  <p:Publisher>Ton Stegeman</p:Publisher>
  <p:AssemblyName>
    TST.POC.PolicyOfTruth, Version=1.0.0.0, Culture=neutral, 
    PublicKeyToken=503edd7b21a430b3
  </p:AssemblyName>
  <p:ClassName>TST.POC.PolicyFeatures.ExpirationSendWarning</p:ClassName>
</p:PolicyResource>

Step 3 – Register the expiration action

The last step is to register the action. To do that, you run the example piece of code below from any type of application you like best:

    string actionmanifest = System.IO.File.ReadAllText("actionmanifest.xml");
    PolicyResourceCollection.Add(actionmanifest);

The PolicyResourceCollection can be found in the namespace “Microsoft.Office.RecordsManagement.InformationPolicy”. Both for step 1 and 3 you need a reference to Microsoft.Office.Policy.dll

Good luck!

MOSS Custom policies part 3 – implementing the custom policy

February 15th, 2007 by tonstegeman

This is the 3rd and last part in a small series on how to create a custom information management policy for SharePoint 2007.

In part 1 the policy was introduced and we created the policy feature and created the setup control that allows our users to configure the policy.
Part 2 shows how to create the handler that actually does some work and submits a document to the records center
The final part will put it all together. It also has all code attached. You can find the zip file at the bottom of this article.

Step 1 – Registering the PolicyFeature

In the first part we implemented the IPolicyFeature interface, but it didn’t do anything. The first method we will implement is the Register method. This is called when the policy is assigned to a content type. This is the perfect place if you need to do some extra configuration. I will do 2 things here:

  • Setup an event receiver for the content type.
  • Add an extra site column (and create if it doesn’t exist) to the content type.

To setup an event receiver for the content type, we’ll add this code to the register method:

    Assembly assembly = Assembly.GetExecutingAssembly();
    SPEventReceiverDefinition eventReceiver = ct.EventReceivers.Add();
    eventReceiver.Name = "Policy of Truth";
    eventReceiver.Type = SPEventReceiverType.ItemUpdated;
    eventReceiver.SequenceNumber = 200;
    eventReceiver.Assembly = assembly.FullName;
    eventReceiver.Class = "TST.POC.PolicyFeatures.PolicyOfTruthHandler";
    eventReceiver.Update();

The event receiver itself will be implemented in one of the next steps. The code to setup the site column to the content type is added below. This will first check if a field with internalname “SentToTruthRepository” is available in the content type. If it is not it will check if this field is available as a site column. If the site column is not yet available, it will create it as a readonly site column. The value of this field will only be updated by our policy, and users should not be able to change it manually. This last bit adds the site column to the Content Type.

    string fieldName = "SentToTruthRepository";
     // test if field is linked to content type
    foreach (SPFieldLink link in contentType.FieldLinks)
        if (link.Name == fieldName)
            return;
    SPField repositoryField = null;
 
    using (SPWeb web = contentType.ParentWeb)
    {
        // check if site column exists in the site
        foreach (SPField field in web.AvailableFields)
        {
            if (field.InternalName == fieldName)
            {
                repositoryField = field;
                break;
            }
        }
 
        // add site column if it does not exist
        if (repositoryField == null)
        {
            string xml = "<Field Name="SentToTruthRepository" FromBaseType="FALSE" Type="DateTime" ";
            xml += "DisplayName="Sent to truth repository" Required="TRUE" Format="DateTime" ";
            xml += "ReadOnly="TRUE" Group="Policy Columns" />";
            string newField = web.Fields.AddFieldAsXml(xml);
            repositoryField = web.Fields.GetFieldByInternalName(newField);
        }
    }
    // add field to content type
    SPFieldLink newLink = new SPFieldLink(repositoryField);
    contentType.FieldLinks.Add(newLink);
    contentType.Update(true);

Step 2 – Unregistering the policy

The method UnRegister on the policy feature is called when a policy is detached from a content type. This is the place to unregister the event handler that we created in the first step. You can also remove the extra site column from the content type, but I decided to leave it.

    public void UnRegister(Microsoft.SharePoint.SPContentType ct)
    {
        if (ct == null)
        {
            throw new ArgumentException();
        }
        SPEventReceiverDefinition delete = null;
        foreach (SPEventReceiverDefinition eventReceiver in ct.EventReceivers)
        {
            if ((eventReceiver.Name == "Policy of Truth") && (eventReceiver.Type == SPEventReceiverType.ItemUpdated))
            {
                delete = eventReceiver;
                break;
            }
        }
        if (delete != null)
            delete.Delete();
    }

Step 3 – Creating the event handler

The next step is to create the event handler we used in step 1. This will use the handler we created in the previous part. This handler check the item for the policy rules and submits the item to the records center. The event handler is a normal event reveiver for SharePoint list event. For demo purposes I have only implemented the ItemUpdated event.

    public class PolicyOfTruthHandler : SPItemEventReceiver
    {
        public override void ItemUpdated(SPItemEventProperties properties)
        {
            DisableEventFiring();
            RepositoryHandler repository = new RepositoryHandler();
            if (repository.HandleListItem(properties.ListItem))
            {
                string truthFieldName = "Sent to truth repository";
                properties.ListItem[truthFieldName] = DateTime.Now;
                properties.ListItem.Update();
            }
            EnableEventFiring();
        }
    }
The RepositoryHandler you see here is the handler I created in the previous part of this series. The code to update the list item and set the date in the special site column, has moved from the repository handler to the event handler. The eventhandler first calls DisableEventFiring() to prevent the update of the list item by the policy from firing the event a second time. For the update this works, but for some strange reason the ItemUpdated event fires twice in the process. As soon as we call the SubmitFile method on the OfficialFile.asmx webservice, the event gets fired a second time. This way we end up with 2 documents in the records center each time we change an item. I’ve spent quite a bit of time trying to stop this, but I didn’t succeed. I decided to leave it as is, because I wanted to get the policy working and it is not a real world scenario.
 

Step 4 – The rest of the policy feature

Our policy feature also implements the method “ProcessListItem”. According to the policy sample in the ECM Starter Kit (MOSS SDK), this method is called for list items of the content type that were added before the policy was in place. Items that we not handled by the vent handlers (because the event handlers were not there yet) will we processed by ProcessListItem when the policy is assigned. I tried to test this using my custom policy of truth, but couldn’t get it to work.

To be a full working solution our policy should also implement the OnCustomDataChange method. This is called when the custom setup of the policy is changed. In our case this is when an administrator changes the keywords. Our policy feature should then check which documents are considered as ‘truth documents’ and these should be added to the records center.

Step 5 – Testing the solution

Here are the steps how I tested the custom policy:

  • Create a new content type called “Whitepaper”
  • Create a new custom policy for this content type. Activate the Policy of Truth and set the keywords to SharePoint and WSS
    Policytest1
  • Assign the content type to a document library
    Policytest2
  • Upload a new document to the document library and set the title to “The truth on SharePoint development”
    Policytest3
  • Navigate to your records center and test if your document was submitted to the records center. It should be submitted as an unclassified record. Please note that my testdocument was uploaded twice. I explained the reason for that above in step 3.
    Policytest4
  • Open one of the xml files in the “Properties” folder and notice that we also set a custom property while submitting the file to the repository: ‘SubmittedBy’.
    Policytest5
  • Go back to the document library that has the document we just added. Change the view to include the field “Sent to truth repository” field. Notice that this field now has a value. This was set by the policy after successfully submitting the document to the records center.
    Policytest6

Overview of all parts:

  • Part 1 – introduction and creating the policy feature
  • Part 2 – implementing the handler and submitting to a records center
  • Part 3 – implementing and testing the policy

 

MOSS Custom policies part 2 – creating a handler to submit items to the records center

February 12th, 2007 by tonstegeman

In the first part of this series, I introduced a custom information management policy. In the previous post I created and registered the custom policyfeature. We ended up with a policy that we could create and setup. In this post, we’ll create the handler that will make the policy do some work. The sample policy that I was working on is the “policy of truth”. In case a user submits an item containing the word “truth” in combination with one of the keywords in the policy setup, the policy will do it’s work. Because I was also preparing for the beta exam 70–542, I decided to submit the item to a records repository. If you want to learn more about the Records Center in SharePoint, this item on the Records Management Team Blog is a good starting point.

I will post the most important code bits here. When the series is complete, I will post the full code (I’ll need to do some cleaning up first….)

Step 1 – Create the repository handler

First we will create a new class that will handle the list items. It will check if the item needs to be submitted and if so, it will submit the item. Our “RepositoryHandler” has a method called “HandleListItem”. This takes a SPListItem as parameter.

Step 2 - Get the keywords from the policy

First the handler needs to find out what keywords the user has set when setting up the policy:

        private string GetKeywords(SPListItem item)
        {
            // get the keywords from the policy options
            Policy policy = Policy.GetPolicy(item.ContentType);
            PolicyItem policyItem = policy.Items[PolicyOfTruth.PolicyId];
            string keywords = string.Empty;
            using (XmlReader reader = XmlReader.Create(new System.IO.StringReader(policyItem.CustomData)))
            {
                reader.ReadStartElement("data");
                reader.ReadStartElement("keywords");
                keywords = reader.ReadString();
                reader.ReadEndElement();
                reader.ReadEndElement();
            }
            return keywords;
        }

 The PolicyId of the PolicyOfTruth feature is a static property that just returns the id of the policy:

    public static string PolicyId
    {
        get { return "TST.POC.PolicyFeatures.PolicyOfTruth"; }
    }

Step 3 - Check if we need to submit the item

This bit of code checks if any of the keywords and the word ‘truth’ is found in one of the metadata fields of the list item. This code is very simple, because my goal was to get the policy working, not to create something for a useful scenario.

    private bool HandleItem(SPListItem item, string keywords)
    {
        bool handle = false;
        // for now just do documents in a doclib.
        if (item.ParentList is SPDocumentLibrary)
        {
            string[] keywordItems = keywords.Split(new char[] { ';' });
            foreach (string keyword in keywordItems)
            {
                foreach (SPField field in item.Fields)
                {
                    if (item[field.Id] != null)
                    {
                        string value = item[field.Id].ToString();
                        if ((value.ToLower().IndexOf(keyword.ToLower().Trim()) > -1) &&
                            (value.ToLower().IndexOf("truth") > -1))
                        {
                            handle = true;
                            break;
                        }
                    }
                }
                if (handle) break;
            }
        }
        return handle;
    }

Step 4 - Setup the connection to the records center

To submit the item to the records center, I used the code sample from the ECM Starter Kit (part of the MOSS SDK). First you need to get a web reference to the webservice of the records center. If you don’t have a record center yet, the first thing to do is create one. The webservice can be found on this url: http://office2007:3736/_vti_bin/OfficialFile.asmx. In my case the records center is running on port 3736 of my server called ‘office2007’. My web reference is called ‘Repository’. Setup a connection:

    TST.POC.PolicyFeatures.Repository.RecordsRepository repository = new Repository.RecordsRepository();
    repository.Credentials = System.Net.CredentialCache.DefaultCredentials;
    repository.PreAuthenticate = true;

Step 5 – Add properties for the records center

The next thing is to add a property to the item that will get submitted to the records repository. In this property we will save the accountname of the user who initially created the item. To do this, you create a new RecordRepositoryProperty:

    Repository.RecordsRepositoryProperty[] repositoryProperties = new Repository.RecordsRepositoryProperty[1];
    repositoryProperties[0] = new Repository.RecordsRepositoryProperty();
    repositoryProperties[0].Name = "SubmittedBy";
    repositoryProperties[0].Type = "Text";
    repositoryProperties[0].Value = item["Created By"].ToString();

Step 6 – Submit the document

To submit the document to the records center, you first have to read it into a byte array. Then call the SubmitFile method. This takes the byte array, the properties array we created as parameters. You also need the pass the name of the routing. I have not yet created a special routing for my items, so I used the default routing called “Unclassified Records”.

    byte[] doc = item.File.OpenBinary();
    string result = repository.SubmitFile(doc, repositoryProperties, 
          "Unclassified Records", item.Url, item.Web.CurrentUser.Name);

Step 7 – Handling the result

The SubmitFile method of the OfficialFile webservice returns a xml string. Here is a way to find out what happened. In case of success, the handler updates the item that was submitted. It saves the current datetime in a custom field that was added to the content type by the policy.

    result = string.Format("<Result>{0}</Result>", result);
    XmlDocument xml = new XmlDocument();
    xml.LoadXml(result);
    XmlElement root = xml.DocumentElement;
    string resultCode = root.SelectSingleNode("ResultCode").FirstChild.Value;
    string additionalInformation = string.Empty;
    if (root.SelectSingleNode("AdditionalInformation") != null)
    {
        additionalInformation = root.SelectSingleNode("AdditionalInformation").FirstChild.Value;
    }
    if (resultCode == "Success")
    {
        item[truthFieldName] = DateTime.Now;
        item.Update();
    }
        return string.Format("Submitted to records center: {0} - {1}", resultCode, additionalInformation);

In my example code it generated a new result string that is returned by the HandleListItem method on the repository handler.

In this post we created the handler that makes our policy do some work. In the next post, I will put it all together, so that it will be a working custom information management policy.

Update 15–02–2007 – Added overview of all parts:

  • Part 1 – introduction and creating the policy feature
  • Part 2 – implementing the handler and submitting to a records center
  • Part 3 – implementing and testing the policy

Incoming e-mail in SharePoint 2007 – WOW!

February 1st, 2007 by tonstegeman

Sometimes you find these things in SharePoint that make your life easier. Incoming e-mail is such a feature. We configured it in our intranet yesterday and it works great! Sending invitations to event calendars, creating announcements, discussion, it is all done by just sending an e-mail.

The real wow was when I created an email address for the blog on my MySite. I sent an e-mail with some images in it to my blog. SharePoint added the images as attachement to the post item and updated the links in the text, so my mail changed into a blogpost without doing something special. That’s what I call “working easier”!
(working easier is the ‘mission’ of the company I work for)

MOSS Custom policies part 1 – Creating a custom information management policy

February 1st, 2007 by tonstegeman

An information management policy in SharePoint 2007 is a set of rules and actions that help an organization to manage the content in their SharePoint sites. Policies are assigned to lists and content types. Therefore they make it easy to enforce a policy without your users having to think about it. This MSDN page contains a nice overview of the architecture of the policy architecture in SharePoint.

Out of the box there are 4 policies:

  • Expiration
  • Auditing
  • Document Labels
  • Document Bar Codes

These 4 policies are available as “Policy Features”. These can be used to specify the policy for a SharePoint list or a content type. These polices can be specified at the site collection level to be used throughout the whole site, or directly at the list/content type.

The nice thing is that the policy framework is an extensibly framework, so you can write your own information management policies. This will be the first of a number of posts on this topic. The first part shows you how to create and register a policy so that it can be used. The scenario I used for this example is not very useful, but I wanted to have a simple policy to start with.

Because I was listening to the latest Depeche Mode DVD (Live in Milan) when reading the SDK, I decided to create a “Policy of Truth”. Administrators the specify a policy can enter a number of keywords. When new content is added to SharePoint lists (or document libraries) that have our policy assigned, our policy will check the metadata for the presence of one of the keywords. When one of the keywords is found, and the metadata also contains the word “truth” or “proof”, a link to the item is submitted to a central list in a special site collection. This way, we can easily manage our version of “the truth”. Not very useful, but pretty straightforward and it covers most aspects of building a custom policy.

Step 1 – Create the policy feature

In the first step we will create the policy feature. To do this you need to implement the IPolicyFeature interface. At this stage we will only implement an empty Policy feature to be sure that we get it registered correctly. The actual work done by the policy will be covered in the next post. Here is the code for the poliy feature:

    public class PolicyOfTruth : IPolicyFeature
    {
        public PolicyOfTruth()
        {
        }
 
        public void OnCustomDataChange(PolicyItem policyItem, Microsoft.SharePoint.SPContentType ct)
        {
        }
 
        public void OnGlobalCustomDataChange(PolicyFeature feature)
        {
        }
 
        public bool ProcessListItem(Microsoft.SharePoint.SPSite site, PolicyItem policyItem, Microsoft.SharePoint.SPListItem listItem)
        {
            return true;
        }
 
        public bool ProcessListItemOnRemove(Microsoft.SharePoint.SPSite site, Microsoft.SharePoint.SPListItem listItem)
        {
            return false;
        }
 
        public void Register(Microsoft.SharePoint.SPContentType ct)
        {
        }
 
        public void UnRegister(Microsoft.SharePoint.SPContentType ct)
        {
        }

Add this to a class library, strong name the assembly and add it to the GAC. IPolicyFeature can be found in the Microsoft.Office.RecordsManagement.InformationPolicy namespace. To use this you need a reference to Microsoft.Office.Policy.dll.

Step 2 – Create the manifest

In a later step we will register the new policy. To do this we need a manifest. I saved this to a manifest.xml file that is loaded by the process that registers the policy.

<?xml version="1.0" encoding="utf-8" ?>
<p:PolicyFeature id="TST.POC.PolicyFeatures.PolicyOfTruth" 
     xmlns:p="urn:schemas-microsoft-com:office:server:policy" group="Policy">
  <p:LocalizationResources>dlccore</p:LocalizationResources>
  <p:Name>Policy of Truth</p:Name>
  <p:Description>
      This policy helps us to achieve the goals set in our
      'one version of the truth' project
  </p:Description>
  <p:Publisher>Ton Stegeman</p:Publisher>
  <p:ConfigPage>policyoftruthsettings.ascx</p:ConfigPage>
  <p:ConfigPageInstructions>
      You can add keywords here. 
      If any of these keywords is found in the item's metadata and the metadata also has
      the word 'truth' or 'proof', then the item is considered to be the 'truth'. And our
      truth is something we need to manage. Separate your keywords with a ';'
  </p:ConfigPageInstructions>
  <p:AssemblyName>
      TST.POC.PolicyOfTruth, Version=1.0.0.0, Culture=neutral, 
      PublicKeyToken=503edd7b21a430b3
  </p:AssemblyName>
  <p:ClassName>TST.POC.PolicyFeatures.PolicyOfTruth</p:ClassName>
</p:PolicyFeature>

The name and description are used to describe your policy. The ConfigPage and ConfigPageInstructions are used on the page that is loaded when a user creates a new policy. The instructions are displayed in the left column of the page and the ASCX in the ConfigPage element is loaded when a user checks the box for our “Policy of Truth” policy. The AssemblyName and ClassName elements are the reference to the policy feature we created in step 1.

Step 3 – Create the configuration page.

The configuration page is the page that is loaded when a user creates a policy using our feature. The screenshot below shows the control for this specific policy.

              Custompolicy1

To create this control, you create a new ASCX file and copy that to the SharePoint LAYOUTS folder. The contents of my PolicyOfTruthSettings.ASCX file:

    <!-- _lcid="1033" _version="12.0.4518" _dal="1" -->
    <!-- _LocalBinding -->
    <%@ Assembly Name="TST.POC.PolicyOfTruth, Version=1.0.0.0, Culture=neutral, PublicKeyToken=503edd7b21a430b3"%>
    <%@ Register Tagprefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls" 
            Assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %> 
    <%@ Register Tagprefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" 
            Assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %> 
    <%@ Import Namespace="Microsoft.SharePoint" %>
    <%@ Control Language="C#" Inherits="TST.POC.PolicyOfTruth.PolicyOfTruthSettings" %>
    <p>
    <table cellpadding="0" class="ms-authoringcontrols">
        <tr>
            <td>&nbsp;</td>
            <td><asp:Label runat="server" Text="Enter your keywords, separated by ';'"></asp:Label></td>
        </tr>
        <tr>
            <td>&nbsp;</td>
            <td>
             <asp:TextBox id="TextBoxKeywords" runat="server" MaxLength="1024" 
                    class="ms-input" ToolTip="Enter your keywords here." />
             <asp:RequiredFieldValidator
                        id="RequiredValidatorKeywords"
                        ControlToValidate="TextBoxKeywords"
                        ErrorMessage="At least one keyword is required."
                        Text="Please enter on or more keywords separated by a semicolon."
                        EnableClientScript="false"
                        runat="server"/>
            </td>
        </tr>
    </table>
    </p>

You can see here the this control inherits a custom class that I have created called “PolicyOfTruthSettings”. This class inherites from CustomSettingsControl and is compiled into the same assembly as created in step 1. The CustomSettingsControl can be found in namespace “Microsoft.Office.RecordsManagement.InformationPolicy”. It is an abstract control with some abstract methods that our CustomSettingsControl needs to implement. Here is the code for this class in my example:

    public class PolicyOfTruthSettings : CustomSettingsControl
    {
        private SPContentType _contentType;
        private string _customData;
        private SPList _list;
        protected TextBox TextBoxKeywords;
 
        public override Microsoft.SharePoint.SPContentType ContentType
        {
            get {return _contentType;}
            set {_contentType = value;}
        }
 
        public override string CustomData
        {
            get
            {
                XmlDocument doc = new XmlDocument();
                XmlElement rootNode = doc.CreateElement("data");
                doc.AppendChild(rootNode);
                XmlElement keywordsNode = doc.CreateElement("keywords");
                rootNode.AppendChild(keywordsNode);
                keywordsNode.InnerText = TextBoxKeywords.Text;
                _customData = doc.InnerXml;
                return _customData;
            }
            set {_customData = value;}
        }
 
        public override Microsoft.SharePoint.SPList List
        {
            get {return _list;}
            set {_list = value;}
        }
 
        public override bool LoadPostData(string postDataKey, 
            System.Collections.Specialized.NameValueCollection values)
        {
            string oldData = this.CustomData;
            string newData = values[postDataKey];
            if (oldData!=newData)
            {
                this.CustomData = newData;
                return true;
            }
            return false;
        }
 
        public override void RaisePostDataChangedEvent()
        {
        }
 
        protected override void OnLoad(EventArgs e)
        {
            base.OnLoad(e);
            if ((base.IsPostBack) || (string.IsNullOrEmpty(_customData)))
            {
                return;
            }
            using (XmlReader reader = XmlReader.Create(new System.IO.StringReader(_customData)))
            {
                reader.ReadStartElement("data");
                reader.ReadStartElement("keywords");
                TextBoxKeywords.Text = reader.ReadString();
                reader.ReadEndElement();
                reader.ReadEndElement();
            }
        }
    }

The most important parts of this control are the CustomData property and the OnLoad. All custom data that you collect using the controls in the CustomSettingsControl is saved with the policy as xml. You can see that when you export a policy. See the last step for an example of the policy we are developing. A warning here: you should be careful with this xml, because if you create some xml that cannot be parsed or has another error, SharePoint will crash the page leaving you with a corrupt policy that cannot be removed. In my case I created a site collection policy that made all other policies inaccessible, so I had to re-create the site collection. So be warned!. In the CustomData property the xml is generated with the values of the controls. The OnLoad reads the xml string and sets the values for the usercontrol(s) in your editor.

Step 4 – Register the policy feature

The last step after you have deployed the assembly and the ascx file is to register the policy feature in the PolicyCatalog. Although I am not 100% sure, I think there is 1 policy catalog for each MOSS server. This MSDN page in the SharePoint Server 2007 SDK contains more information. The way to register your custom policy is doing it programmatically. I do it in a custom tool that makes it easy to register / unregister the custom policy. The best way to do it (and the way SharePoint does it) is by creating a new SPFeatureReceiver object that registers the policy. Here is the code to register our policy:

        PolicyFeatureCollection policyFeatures = PolicyCatalog.FeatureList;
        foreach (PolicyFeature policyFeature in policyFeatures)
        {
            if (policyFeature.Id=="TST.POC.PolicyFeatures.PolicyOfTruth")
            {
                MessageBox.Show("Policy was already installed");
                return;
            }
        }
        string manifest = System.IO.File.ReadAllText("manifest.xml");
        PolicyFeature.ValidateManifest(manifest);
        PolicyFeatureCollection.Add(manifest);

This piece of code first checks if the policy is not registered. If it is not, it reads the manifest for the policy from the manifest.xml file. Then this manifest is validated and added to the PolicyFeatureCollection. The objects referenced here are in the same namespace that we used in step 1 and 2.

And if you need to unregister it:

        PolicyFeatureCollection policyFeatures = PolicyCatalog.FeatureList;
        foreach (PolicyFeature policyFeature in policyFeatures)
        {
            if (policyFeature.Id == "TST.POC.PolicyFeatures.PolicyOfTruth")
            {
                PolicyFeatureCollection.Delete(policyFeature.Id);
                return;
            }
        }

Step 5 – Test

 After you successfully registered your policy feature, you are now ready to test if your policy is available. To create a policy at the site collection level, go to “Site collection policies” in the Site Collection Administration. When you click Create, the “Policy of Truth” should be available:

     Custompolicy2

When you check the box, you will see the screenshot from step 3. After creating and saving the policy, you can export it to an xml file. If you open this file, you can see that the data we entered in our custom control, is stored in the policy xml. This was done by the CustomSettingsControl from step 3. An example of the xml:

      <p:Policy xmlns:p="office.server.policy" local="false" id="62bb137b-e4c5-4dab-9b90-c9b3e54384c5">
        <p:Name>The truth about SharePoint</p:Name>
        <p:Description>This policy manages 'truth' items on SharePoint in our portal</p:Description>
        <p:Statement>
          SharePoint list items and documents that are considered to be the truth about SharePoint
          Technologies, will be managed by our 'truth manager'.
        </p:Statement>
        <p:PolicyItems>
          <p:PolicyItem featureId="TST.POC.PolicyFeatures.PolicyOfTruth">
            <p:Name>Policy of Truth</p:Name>
            <p:Description>This policy helps us to achieve the goals set in our 'one version of the 
                truth' project</p:Description>
            <p:CustomData>
              <data>
                <keywords>SharePoint; MOSS; WSS</keywords>
              </data>
            </p:CustomData>
          </p:PolicyItem>
        </p:PolicyItems>
      </p:Policy>

You can also directly assign a new policy on a list or a content type, or you can assign the policy we just created for the site collection.

     Custompolicy3

In the next item we’ll make the policy do some work.

Update 15–02–2007 – Added overview of all parts:

  • Part 1 – introduction and creating the policy feature
  • Part 2 – implementing the handler and submitting to a records center
  • Part 3 – implementing and testing the policy