Developing Custom Applications
withSharePoint Server 2007

SharePoint Server 2007
as a Development Platform

WhitePaper

By Nicolas Leger and Zandy Garrard

With Scott Yokiel, Cesar Rodriguez, Brad Muza

Contact us:

RBA Consulting

Published: May 2008

Last Edited: 5/30/08

© 2008 RBA Consulting

Developing Custom Applications with SharePoint Server 2007Page 1

Table of Contents

1.Acknowledgements

2.Introduction - Business goal

2.1Our Audience - you

2.2Business scenario

2.3RBA Consulting’s collaboration with Microsoft

2.4Solution Roles & Use Cases

2.4.1Roles

2.4.2Use Cases

2.5Business Requirements

2.6Technical Requirements

2.73-Tier architecture

2.8SharePoint Development Toolset

3.User Interface, Web & Forms

3.1Forms with InfoPath 2007

3.1.1Business case

3.1.2Form data sources

3.1.2.1Internal data source

3.1.2.2External data sources

3.1.3Significant fields

3.1.4Data connections

3.1.5Workflow integration

3.1.5.1Extra ‘Status’ column in form library

3.1.5.2InfoPath forms submitted to SharePoint form library

3.2Look & feel – SharePoint theme

3.2.1Business case

3.2.2Create a new SharePoint site theme for branding

3.2.3Deploy the new theme

4.Data

4.1Data Structure

4.2Data Retrieval

4.3SharePoint lists vs. SQL table

5.Workflows

5.1Business Case

5.2How to choose between out-of-the-box vs. SharePoint Designer vs. Visual Studio

5.2.1Out-of-the-box workflows

5.2.2Build with SharePoint Designer 2007

5.2.3Build with Visual Studio 2005/2008

5.3Set up the Development Environment to support workflows

5.4Build a custom activity for SharePoint Designer

5.4.1Create the Visual Studio project

5.4.2Define Dependency Properties of the Activity class

5.4.3The Execute method

5.4.4Deployment of a custom SharePoint Designer activity

5.4.4.1Deploy the compiled assembly

5.4.4.2Mark the assembly as safe in web.config

5.4.4.3Create a new action in WSS.ACTIONS

5.4.4.4Restart IIS

5.4.5Use new custom SharePoint Designer activity in a SharePoint Designer workflow

5.4.6Tips & Tricks

5.4.6.1How to troubleshoot/debug a custom workflow activity?

5.4.6.2What is the difference between a Visual Studio workflow and a SharePoint Designer workflow?

6.Enterprise Search

6.1Keyword Search Queries

6.2Search Web Service

6.3List query API (SPQuery class)

6.4Cross-site query API (SPSiteDataQuery class)

6.5Full Text SQL Search Queries

6.5.1Business case

6.5.2Author code for the Full Text SQL Search API

6.5.2.1Create SQL query

6.5.2.2Execute the Query

6.5.2.3Parse and format results

6.5.2.4Bringing it all together

6.5.3Configure your SharePoint Server computer to use the Full Text SQL Search API

6.5.3.1Create content

6.5.3.2Crawl content

6.5.3.3Add managed properties

6.6Tips & Tricks

6.6.1.1How to search for items in a specific list?

6.6.1.2Separate query creation / execution / result parsing.

6.6.1.3The right search API for the job

6.6.1.4Column Names vs. Managed Property Names

6.6.1.5Where are my results?

6.6.1.6Creating console application for troubleshooting

7.Excel Services Reporting

7.1Business Case

7.2Business Intelligence with SharePoint Server 2007

7.3Intro to User-defined functions

7.4Creation of the user-defined function

7.5Creation of the Excel Services workbook

7.5.1The approach

7.5.2Leverage user-defined functions

7.5.3Add charts to an Excel Services workbook

7.6Deployment

7.6.1Deploy the user-defined function

7.6.2Publish the workbook to Excel Services

7.7View an Excel Services workbook

7.8Tips & Tricks

7.8.1Data flow

8.Development process

8.1Development Strategy

8.1.1Architectural choices

8.1.2Draw the line out-of-the-box vs. custom code

8.1.3SharePoint Designer vs. InfoPath vs. Visual Studio vs. Excel Services

8.1.4Environment architecture

8.1.5Replicate the data structure from one environment to another

8.2Code & deliverables repository

8.3Documentation

8.4Collaboration

9.Best practices

9.1Debugging and Troubleshooting

9.1.1Log messages to the Windows Event Log

9.1.2Troubleshooting Enterprise Search API code

9.1.3Troubleshooting InfoPath forms

9.1.4Use a .NET Console application for unit testing

9.2Keep the business engaged

9.3Tie back to business needs

9.4Flexibility to support business changes

10.Conclusion

11.Resources

1.Acknowledgements

RBA Consulting would like to thank Jerome Thiebaud, Product Manager for Microsoft Office SharePoint Designer 2007 at Microsoft. Our team sincerely enjoyed the many opportunities to present about our experience building a business critical custom application on Microsoft SharePoint Products and Technologies.Sharing best practices on SharePoint development with customers, peer consultants, and experts from the Microsoft field has continually pushed us to evaluate the platform in thorough details from a developer perspective.

During this process, Jerome has shown a profound understanding of the specific needs of each audience. Over the past few months, his guidance was instrumental in creating material about our project that is information-rich, enjoyable, and easy to consume.

Among the many others who provided valuable feedback as we wrote this whitepaper, Mitch Prince stands out for suggesting creative ways to make the most of Office SharePointServer 2007. There are often many paths to satisfy a business requirement usingSharePoint Server; Mitch has in several occasions challenged us to “think outside the box” and find the optimal solution.

Our consultants look forward to a future collaboration with Microsoft to further demonstrate the broad capabilities of SharePoint Server 2007.As just one example, we are especially thrilledcurrently by the integration scenariosbetween SharePoint Server 2007 and Microsoft Silverlight.

2.Introduction - Business goal

2.1Our Audience - you

Late in 2007, executives at RBA Consulting identified the need to streamline the way we track consultant assignments to client projects. After a decision process we will discuss further in this document, our team of developers set out to build a business application on the SharePoint Server 2007 (MOSS) Enterprise platform.

With this whitepaper, Microsoft gave us a fantastic opportunity to tell about our experience. We are confident its content will be most useful to:

  • IT decision makers who must select a development platform for their organization. We tried to include as much high level information as possible on how the SharePoint platform supported our business goals. As we examine each aspect of the SharePoint platform – and there are many – we tie these capabilities back to the needs of the business. There is a strong belief at RBA Consulting that a development platform is only as good as the business needs it addresses.
  • ASP.NET Developers looking for a “deep-dive” into the SharePoint platform. We include actual code, step-by-step walkthroughs and extensive screenshots to help developers “get things done” using the platform. While developers in general can leverage this material to evaluate the SharePoint development platform, developers with ASP.NET experience will make the most of the in depth discussions and code samples.

2.2Business scenario

Our firm, RBA Consulting, offers high-end consulting services on Microsoft technologies. To be successful in the consulting business, we need a mechanism to keep track of the client engagements of our consultants:

  • Where is each consultant billing now and in the future?
  • What is the skill set of each consultant?
  • What client opportunities are becoming available in the pipeline? Who in the firm has the skills to deliver successfully on these opportunities?

RBA Consulting has an existing solution to keep track of that information. However by the end of 2007, management recognized that a new version was needed that would offer improved flexibility and scalability. For all the reasons that will be discussed in this paper, we settled on SharePoint Server 2007 to build the solution.

2.3RBA Consulting’s collaboration with Microsoft

Shortly after we initiated the requirements gathering phase, Microsoft’s marketing offeredRBA to use our solution as a real-life SharePoint development story. From day one, we had the mandate to provide unbiased feedback and participate in several initiatives to discuss our experience:

  • Microsoft Academy Live presentation (January ‘08): these recurring events are organized by Microsoft marketing, and target Microsoft employees in the field. The audience is a mix of sales and technical professionals who join the presentation from numerous Microsoft offices throughout the U.S. and the world. On this occasion, we demonstrated a working version of the application, and discussed some of our design decisions as well as code samples.
  • Microsoft Office SharePoint Conference 2008 (March ‘08): the yearly conference attracts larger and larger crowds every time. In fact this year over 3,000 people attended. The application demo and code discussion was the center-piece of the session. We focused on Excel Services 2007, Microsoft Office Excel user-defined functions, Microsoft Office InfoPath 2007 forms, Enterprise Search and Office SharePoint Designer workflows. Several sections of this whitepaper summarize discussions from the conference.
  • this whitepaper (April ’08): as large as they can get, live presentations only address a small fraction of the developers out there with an interest in SharePoint Products and Technologies. We had to have in-depth written material that people could go back to as a reference.

2.4Solution Roles & Use Cases

2.4.1Roles

Ultimately company executives are the primary beneficiaries of the consultant tracking solution, as it provides them with deeper business insights. However stakeholders in various roles throughout our company will interact with the target solution. The following organizational roles were identified as relevant for the design of the solution:

  • Account Executive. The A.E. has sales responsibilities. After a client opportunity has been identified, the A.E. must submit a resource request to the organization to identify a qualified resource for the job.
  • Resource Manager. The Resource Manager must match client opportunities as identified by the Account Executive and qualified consultants who can deliver the job.
  • Executive. The C-level executive is usually not involved in running day-to-day operations, but needs to know how well the business is performing against business goals.
  • Consultant. Consultants are assigned to client opportunities based on their skills and experience. Consultants interact with the solution primarily through submitting leave requests. These leave requests, if approved, impact the consultant’s availability for future client projects.

2.4.2Use Cases

Employees throughout the firm use the solution in various ways based on who they are. Here are just a few sample usage scenarios implemented by the solution. The organizational roles that take part in the scenario are also identified.

  • Review business performance through report [Executive]
  • Request a project resource [Account Executive, Resource Manager]
  • Submit a leave request [Consultant, Resource Manager]

2.5Business Requirements

This section discusses at a high level the business requirements that the solution had to meet:

  • Roles. Different users need access to different information based on who they are. They will also participate in different steps of business processes based on their role within the firm.
  • Portal. The solution need to make available information extracted from various sources. Users must see only information relevant to their level of responsibility.
  • Reporting. The solution must provide high-level reports and Key Performance Indicators (KPIs) to provide executives an instant snapshot of business performance.
  • Forms. Several electronic forms are needed to upload and maintain information within the solution.
  • Business Processes, Workflows. Users will need to participate in various business processes through the solution. Workflow capabilities are used to route workflow items to the right people at the right time. These processes were documented in a requirements gathering phase using Microsoft Visio.
  • Information Storage.To deliver on other requirements, the solution needs to keep track of information related to day-to-day business of our consulting firm.
  • Search. All information stored by the solution needs to be searchable securely.
  • Look & feel. The solution must be branded with an RBA Consulting-specific look & feel.

2.6Technical Requirements

While business requirements were the critical success factors to building the new solution, a number of technical requirements also had to be taken into account:

  • Minimize custom code and maintenance costs. We needed a solution that was easy to maintain. As a secondary goal, it meant we had to keep custom code to a minimum. While many MOSS 2007 deployments will require some level of custom coding, there is great value for IT in limiting the footprint of that code.
  • Flexibility to support business changes. It is not uncommon for companies to alter how they do business. As an example our management may decide in a few weeks or months that the process to identify project resources needs to change. We had to build a solution that could adapt easily to most change requests.

2.73-Tier architecture

The following diagram illustrates how the application was designed using a 3-tier model:

  • Presentation/User interface
  • Office InfoPath is used to author and submit forms
  • Excel Services renders web-based reports, including charts
  • Internet Explorer is used for all other end user interactions as well as administration
  • Business logic
  • Several technologies are leveraged to implement the business logic. The key take away here is that different tools should be used based on the requirements. The best example is using an Excel Services-renders spreadsheet that contains financial calculations. It would not make sense to embed these calculations in .NET code as SMEs on the calculations (Financial analysts) do not have the skills to update or validate the code. Instead they should deliver to IT an Excel Services-ready spreadsheet that contains the calculations
  • Data storage and retrieval
  • SharePoint lists and document libraries are used throughout the solution for data storage. Pure Microsoft SQL Server databases may have been used for some of the functionalities. But leveraging SharePoint lists gave use access to high-level APIs to manipulate the data (e.g. search) as well as the ability to trigger workflows when the data is modified.

2.8SharePoint Development Toolset

Numerous tools are available to develop solutions on the SharePoint platform. Below is a list of the tools and products we used, along with information as to how they were used:

  • SharePoint Environment
  • Lists and document libraries. They are the vehicle of choice to store data. Information stored in lists or libraries can easily be integrated with workflows or InfoPath forms.
  • Alerts. They were used to implement a simple notification mechanism.
  • Web parts.We used web parts to provide custom search capabilities. Because our search requirements are highly specialized, it made sense to use custom web parts.
  • InfoPath
  • Forms. They are used throughout the solution for data entry. Because our forms have complex business logic (data source integration, control formatting, input validation), it would not have been cost effective to use pure ASP.NET forms. Essentially our InfoPath forms provide the user interface for all business processes supported by the solution.
  • SharePoint Designer
  • Look & Feel. We had very specific requirements on branding to match our corporate identity. Through master pages, layouts and .CSS stylesheets, SharePoint Designer made it straightforward to create and deploy a custom theme.
  • Custom Workflows. The out-of-the-box workflows that ship with SharePoint Server did not meet our requirements. At the same time SharePoint Designer gave us enough flexibility to meet our workflow requirements without introducing undue complexity. Because some steps of the workflows had to be custom coded, we created them in Microsoft Visual Studio and imported them in SharePoint Designer. SharePoint Designer helped putting the big picture together, delivering most steps of the workflows through a wizard-based editor. Plus since the tool is targeted at business users and power users, it makes it easy to discuss workflows with business. We were able to sit down with some of our business stakeholders and validate workflows with them.
  • Visual Studio 2005/2008
  • Custom Web Parts. As mentioned previously, we had to create a few custom web parts for advanced search functionalities.
  • User-defined functions. To provide custom reporting capabilities in Excel Services, we created a .NET user-defined function that extracts data from SharePoint lists to populate the Excel Services spreadsheet.

3.User Interface, Web & Forms

3.1Forms with InfoPath 2007

3.1.1Business case

Our consultant tracking solution implements a number of business processes for one or more stakeholders to submit or process requests. To support these processes, several forms had to be created. These forms would have to:

  • Implement complex business logic. Some form sections must be shown or hidden dynamically based on underlying data
  • Integrate easily with a data repository – in our case a number of SharePoint lists and forms libraries
  • Integrate with web services without any need for custom code
  • Work seamlessly with SharePoint workflows

We selected InfoPath forms as the best option for the following reasons:

  • The integration with SharePoint Server is delivered simply through wizards. Once your form is ready, it can be published as a template to a SharePoint form library. Any field in the form data structure can be promoted to a SharePoint column to the destination form library. As a result, data entered in an InfoPath form is readily available like any other SharePoint list item for search, sorting or displaying.
  • The tool is intuitive to many business users. As a consequence we were able to design the form literally sitting next to a business user, receiving immediate feedback on the form design

Here is the form as it looks in design mode, when every control is visible:

3.1.2Form data sources

3.1.2.1Internal data source

Every form you create in InfoPath has an underlying Data Source. This is essentially an XML document that stores values as they are entered in fields throughout the form. Here is the data source for our Resource Request form:

3.1.2.2External data sources

Engagements

This data source extracts items from a SharePoint list that contains all existing client engagements. Using that data source, a dropdown can be populated automatically in the form containing the list of engagements.

Here is how you would create an InfoPath data source that points to a SharePoint list.

Step / Instructions
1 / In the Design view of the form, click Tools / Data Connections…. The following window pops up:

2 / Click Add…In the next window, select Create a new connection to: then Receive data. Click Next.
3 / In the Select the source of your data window, select SharePoint library or list and click Next.
4 / Enter the URL of list. Here is an example:

5 / Click Next. Click Next again as the correct list should be selected already. The following screen lets you decide which columns of the list will be available in the InfoPath form:

6 / Once you are satisfied with your select, click Next twice. Finally enter the name of the data connection and click Finish.

7 / The new connection has been added:

8 / Click Close to close the pop-up window and go back to your form in Design mode. The new connection is available to pull data dynamically from the SharePoint list into the form.

One of the sections below provides details on populating a drop-down control on the form with data from the data connection.