red-shoes.jpg

Great and Powerful SPGridView

On a recent project, we needed to display some data from lists which spanned multiple sites within a site collection, but the client wanted the data in the “familiar” SharePoint list format, with sorting and filtering and edit and ECB action capabilities. We explained that collecting the data can easily be done using an SPSiteDataQuery, but displaying the data with the sorting and filtering and familiar “list view” look and feel the users were familiar with, would take a little effort.

The response was, “Well, can’t you just use the list view in SharePoint?”.

Sort of…

SharePoint gives you a great deal out of the box, but it’s sort of like the wizard’s line in the The Wizard of Oz, “Pay no attention to that man behind the curtain.”  That “man” behind the curtain is really busy pulling levers and twisting knobs to make things look really simple.

The thought was that integrating the data and mimicking the look and feel of SharePoint should be easy, right?

The key was how to reuse the power of SharePoint’s out of box features to minimize the amount of coding we needed to do.

Let’s open the curtain and see how we got this accomplished.

Follow the Yellow Brick Road

Our client had a very clear idea of what data they wanted, and how they wanted to interact with that data.  Here are the loose requirements in User Story format (with some of the details cloudy from the poppies):

As a team member I want to display list data for the current site only for top-level sites
As a team member I want to display all data from a non-root site  and all child sites
As a team member I want to edit the displayed fields without leaving the view of all the items
As a team member I want to be able to edit a specific list item by clicking the “Edit” menu for the item
To implement these features, we needed to  query data from multiple lists, assemble the data and enable the ECB menu style interaction with the returned data. Here’s the high level steps required to accomplish the previous features.

Using ObjectDataSource and SPSiteDataQuery to query list data
Creating a Custom People Editor Bound Field
Binding SPGridView to the ObjectDataSource
Providing an ECB Menu using MenuTemplate and SPMenuField
Dealing with the Flying Monkeys
Using ObjectDataSource and SPSiteDataQuery To Query List Data

Many articles and blog posts hint at using the SPDataSource as the datasource to bind to the SPGridView instance.  However, for our purposes, SPDataSource had some shortcomings.  Specifically, the search modes of SPDataSource were insufficient for our scenario.   Using the ObjectDataSource gave us the most flexibility, enabling configuration of Select and Update (we did not implement Insert and Delete) commands to execute on our data, as well as the integration we wanted to keep all of the SharePoint goodness.

One challenge of using  the ObjectDataSource combined with SPSiteDataQuery was providing an SPContext in order to query the correct site and web.  The ObjectDataSource enables this by providing the OnDataSourceCreating event, and wiring this up enables us to provide the SPContext information to an overloaded constructor of our ObjectDataSource.  The following are snippets of the code that initializes the instance of our CustomDataSource and then the handler that lets us use the SPSite and SPWeb we need for querying data.

private void InitDataSource()
{
string assemblyName = typeof(CustomDataSource).AssemblyQualifiedName.ToString();
_dataSource = new ObjectDataSource();
_dataSource.TypeName = assemblyName;
_dataSource.ID = "CustomDataSource";
_dataSource.SelectMethod = "GetDataTable";
_dataSource.UpdateMethod = "UpdateDataTable";
_dataSource.SortParameterName = "sortExpression";
_dataSource.FilterExpression = _filterExpression;
_dataSource.ObjectCreating +=
new ObjectDataSourceObjectEventHandler(OnDataSourceCreating);
_dataSource.Filtering +=
new ObjectDataSourceFilteringEventHandler(OnDataSourceFiltering);
Controls.Add(_dataSource);
}

And here is the handler definition.

private void OnDataSourceCreating(object sender, ObjectDataSourceEventArgs e)
{
SPSite site = SPContext.Current.Site;
SPWeb web = SPContext.Current.Web;
e.ObjectInstance = new CustomDataSource(site, web);
}

Within the GetDataTable method of our CustomDataSource, we dynamically build a CAML query for all Lists of a specific type (by list ID) and use an instance of the SPSiteDataQuery class and SPContext.Site instance to get the DataTable back from our CAML Query.

InitCaml();  //dynamically builds the CAML for our query
_siteDataQuery = new SPSiteDataQuery();
_siteDataQuery.ViewFields = _viewFieldsClauseXml;
_siteDataQuery.Lists = _listsClauseXml;
_siteDataQuery.Webs = _websClauseXml;
_siteDataQuery.Query = _queryClauseXml;</code>

DataTable table = _site.RootWeb.GetSiteData(_siteDataQuery);

We then iterate over the rows in the table and add or modify columns and data to get specific information.  One specific piece of data required was the SPUser that an item was Assigned To (the lists were based on the Task List).   Getting the AssignedTo translated value into the table led into another key feature – binding the row to a list item and enabling the use of the custom PeoplePickerBoundField to bind and update the AssignedTo field.

To enable the Update of a given list item as a postback, the UpdateDataTable method of our CustomDataSource used the SPSite, SPWeb, ListID and ListItemID from our query contained in the DataTable to access the list and update the list item for any SPWeb within the site collection.

Creating a Custom PeopleEditor Bound Field

Since one of the columns we wanted to display for the user to edit was an AssignedTo field, we wanted to enable the field to be bound to a PeopleEditor part.  Well, it turns out that there is no out of the box implementation of a BoundField that will let you bind to an SPUser (at least we couldn’t find one…please comment on this post if there is).  Here’s one of those places to help the wizard and create a custom BoundField implementation.  The following is the most important bit of code in creating the PeopleEditorBoundField:

protected virtual void OnBindingField(object sender, EventArgs e)
{
Control control = (Control)sender;
//if we are in view mode
if (control is TableCell)
{
((TableCell)control).Text = GetValueForView(control.NamingContainer);
}
else if (control is PeopleEditor)
{
PeopleEditor peopleEditor = (PeopleEditor)control;
peopleEditor.Visible = true;
peopleEditor.AllowEmpty = true;
peopleEditor.MultiSelect = false;
peopleEditor.Width = Unit.Pixel(200);
peopleEditor.SelectionSet = "User";
ArrayList entityArrayList = new ArrayList();
PickerEntity entity = new PickerEntity();
entity.Key = GetValueForEdit(control.NamingContainer);
entity = peopleEditor.ValidateEntity(entity);
entityArrayList.Add(entity);
peopleEditor.UpdateEntities(entityArrayList);
}
}

For details on creating a custom BoundField, see the Notes and Links below.

Binding SPGridView to the ObjectDataSource

Now that we have the data in a DataTable by using our CustomDataSource, and our custom PeopleEditorBoundField that we bind user information to, we need to display the data. The SPGridView inherits from GridView, and so gives you all of the crunchy goodness of the GridView with the standard rendering of  the SharePoint styled grid in the browser.  We just configured the SPGridView and enabled the options needed, like the data source, sorting and filtering and our columns to display.  Below are some of the more interesting bits of initializing the SPGridView instance in our case.

//we're going to identify the columns we want
<span style="font-family: monospace;">_spGridView.AutoGenerateColumns = false; </span>

//we'll wire our own in the InitBoundColumns
_spGridView.AutoGenerateEditButton = false; 

//we'll use this later to mimic the ECB menu
_spGridView.DataKeyNames = new string[] { "WebId", "ID" }; 

...

//enable sorting
_spGridView.AllowSorting = true; 

//enable filtering, we also set the&nbsp;FilterDataFields property
_spGridView.AllowFiltering = true;

...

//set the event handlers for the sorting and binding
_spGridView.Sorting += new GridViewSortEventHandler(OnGridViewSorting);
_spGridView.RowDataBound += new GridViewRowEventHandler(OnGridViewRowDataBound);

One interesting aspect of using the SPGridView with the ObjectDataSource is that you must specify the DataSourceID when using the ObjectDataSource.  This is required since the underlying calls find the DataSourceControl by the parent NamingContainer and the DataSourceID properties.

//must set DataSourceID in order to use the ObjectDataSource binding
_spGridView.DataSourceID = "CustomDataSourceID";

Providing an ECB Menu Using MenuTemplate and SPMenuField

Finally, we needed the ability for the user to view or edit a list item directly by the use of ECB menu style actions.  To do this, while building the columns for our grid view, we simply use the MenuTemplate and SPMenuField classes to build a menu that has links and custom actions for our list items (code modified to cleanse some specifics).

//construct and add the Title MenuTemplate
MenuTemplate itemMenuTemplate = new MenuTemplate();
itemMenuTemplate.ID = "ItmeTemplateID";

//construct and add the Item Title MenuField
SPMenuField itemMenuField = new SPMenuField();
itemMenuField.HeaderText = _displayColumnName;
itemMenuField.TextFields = _internalColumnName;
itemMenuField.MenuTemplateId = "ItmeTemplateID";
itemMenuField.ToolTipFields = ...; &nbsp;//string[] of fields for tool tips
itemMenuField.ToolTipFormat = ...; &nbsp;//format stringfor tool tips
itemMenuField.NavigateUrlFields = ...;
itemMenuField.NavigateUrlFormat = ...;//dynamically built url with token replacement
itemMenuField.TokenNameAndValueFields = "WEB=WebId,ID=ID";
itemMenuField.SortExpression = "Title";

//construct and add the Items Title MenuItem
MenuItemTemplate editItemMenuTemplate = new MenuItemTemplate...;
//dynamically built url with token replacement
editItemMenuTemplate.ClientOnClickNavigateUrl = ...;
itemMenuTemplate.Controls.Add(editItemMenuTemplate);
this.Controls.Add(itemMenuTemplate);
_scopingGridView.Columns.Add(itemMenuField);

//Custom bound PeopleEditor field
PeopleEditorBoundField assignedToColumn = new PeopleEditorBoundField();
assignedToColumn.ShowHeader = true;
assignedToColumn.HeaderText = _displayPersonColumnName;
assignedToColumn.DataField = _internalPersonColumnName;
assignedToColumn.SortExpression = _internalPersonColumnName;
_spGridView.Columns.Add(assignedToColumn);

This gives us a link to the list item like the standard SharePoint List with the Title column as a link, a menu with a link to the EditForm.aspx page for the specific list item, and our custom PeopleEditor bound field.

Dealing With the Flying Monkeys

One of the special requirements was to enable a RootWeb to retrieve all of the data for all webs recursively, but to initially filter the data to the RootWeb list items. So far everything was going smoothly, a webpart, a custom ObjectDataSource and a custom BoundField, easy-peasy.

Well, you knew the flying monkeys had to show up at some point to make slow us down, right?

The most difficult issue in this process was attempting to get the SPGridView to be filtered automatically on page load. Turns out that there are properties that the SPGridView uses to determine and pass the FilterExpression to the ObjectDataSourceView.CreateFilteredDataView() instance in order to filter the data to render to the user.

Well, you might think this should be exposed as a property or method, but the SetFilterField and SetFilterValue properties are private.  This time the “curtain” was lifted by .NET Reflector.   Turns out, the private methods in SPGriedView set the ViewState entries for the FilterFieldName and FilterFieldValue keys respectively.   Using this knowledge, getting the filtering to be applied immediately on page load was a matter of ensuring that the “filterFieldName” and “filterFieldValue” attributes were applied before the grid was rendered by the following:

if (Page.IsPostBack &amp;&amp; !string.IsNullOrEmpty(_filterExpression))
{
_spGridView.Attributes["filterFieldName"] = _filterExpression.Split('=')[0].Trim();
_spGridView.Attributes["filterFieldValue"] = _filterExpression.Split('=')[1].Trim();
}

Putting It All Together

Once the pieces were all in place, wiring this all together was amazingly simple. To recap, we created a custom web part wrapping the SPGridView, created a custom ObjectDataSource and used the SPSiteDataQuery to query across webs and lists, and bound the SPGridView to the ObjectDataSource.  Finally we configured our SPGridView to use a standard and custom BoundFields to display, edit and persist  list item changes.  Here is the web parts CreateChildControls method:

protected override void CreateChildControls()
{
InitGridView(); &nbsp;//set up the grid view for filtering and sorting and events
InitBoundColumns(); &nbsp;//initialize the bound columns
InitDataSource(); &nbsp;//initialize the custom ObjectDataSource
//must set DataSourceID in order to use the ObjectDataSource binding
_spGridView.DataSourceID = "CustomDataSourceID";
Controls.Add(_spGridView);
}
//It is best to databind in the Render method since this
//is after all other sorting and filtering flags have been set
protected override void Render(System.Web.UI.HtmlTextWriter writer)
{
base.Render(writer);
_spGridView.DataBind();
}

This gave our users the familiar look and feel of the lists in SharePoint, let the users sort and filter the list, and even bound that data in the rows to enable in-place updates of list items that spanned multiple sites (webs) and lists.

Notes and Links

While determining how to solve this issue, we used the following links and resources.  Thanks to all of these authors for pointing us in the right direction and easing the time to implement.  Hopefully someone can use this post’s information the same way.

http://blogs.msdn.com/b/sharepointdesigner/2007/04/24/spdatasource-and-rollups-with-the-data-view.aspx
http://msdn.microsoft.com/en-us/library/dx70zk47(v=VS.100).aspx
http://msdn.microsoft.com/en-us/magazine/cc163673.aspx

read more
Pete SkellyGreat and Powerful SPGridView
tips-white-e1425575645901.jpg

Web Part Page Maintenance

The Web Part Page Maintenance page is where you can go to see all the web parts included in a web page and perform some limited maintenance activities. To get there, simply append ?Contents=1 to the URL of the page (ex: http://somewhere.com/Pages/Default.aspx?Contents=1). The page which appears lists all the web parts which are included on the page. From there you can choose to remove (Delete) web parts or remove personalization (Reset) for specific web parts. Personalization can be removed for all users (the default) or you can switch to personal view and remove personalization for just the current user.

This SharePoint feature is really handy if one of your web parts is throwing an error preventing you placing the page in Edit Mode. In this case, you can use the Web Part Page Maintenance page to remove the offending web parts.

Thanks to coworkers Kirk Liemohn and Chris Edwards for this great tip!


SharePoint is a web application platform in the Microsoft Office server suite. Launched in 2001, SharePoint combines various functions which are traditionally separate applications: intranet, extranet, content management, document management, personal cloud, enterprise social networking, enterprise search, business intelligence, workflow management, web content management, and an enterprise application store. SharePoint servers have traditionally been deployed for internal use in mid-size businesses and large departments alongside Microsoft Exchange, Skype for Business, and Office Web Apps; but Microsoft’s ‘Office 365’ software as a service offering (which includes a version of SharePoint) has led to increased usage of SharePoint in smaller organizations.

While Office 365 provides SharePoint as a service, installing SharePoint on premises typically requires multiple virtual machines, at least two separate physical servers, and is a somewhat significant installation and configuration effort. The software is based on an n-tier service oriented architecture. Enterprise application software (for example, email servers, ERP, BI and CRM products) often either requires or integrates with elements of SharePoint. As an application platform, SharePoint provides central management, governance, and security controls. The SharePoint platform manages Internet Information Services (IIS) via form-based management tooling.

Since the release of SharePoint 2013, Microsoft’s primary channel for distribution of SharePoint has been Office 365, where the product is continuously being upgraded. New versions are released every few years, and represent a supported snapshot of the cloud software. Microsoft currently has three tiers of pricing for SharePoint 2013, including a free version (whose future is currently uncertain). SharePoint 2013 is also resold through a cloud model by many third-party vendors. The next on-premises release is SharePoint 2016, expected to have increased hybrid cloud integration.

Office 365 is the brand name used by Microsoft for a group of software plus services subscriptions that provides productivity software and related services to its subscribers. For consumers, the service allows the use of Microsoft Office apps on Windows and OS X, provides storage space on Microsoft’s cloud storage service OneDrive, and grants 60 Skype minutes per month. For business and enterprise users, Office 365 offers plans including e-mail and social networking services through hosted versions of Exchange Server, Skype for Business Server, SharePoint and Office Online, integration with Yammer, as well as access to the Office software.

After a beta test that began in October 2010, Office 365 was launched on June 28, 2011, as a successor to Microsoft Business Productivity Online Suite (MSBPOS), originally aimed at corporate users. With the release of Microsoft Office 2013, Office 365 was expanded to include new plans aimed at different types of businesses, along with new plans aimed at general consumers wanting to use the Office desktop software on a subscription basis—with an emphasis on the rolling release model.

read more
Eric BowdenWeb Part Page Maintenance
virtual-earth1.jpg

Microsoft Virtual Earth Web Part

Last Monday we held a “Next Level” seminar at Microsoft in Alpharetta. Danny has mentioned several downloads that are available. I wanted to go ahead and provide the Virtual Earth web part we demonstrated.

This was a simple (yet hopefully interesting) example of how you can create a web part. It does not demonstrate all aspects of web parts. For example, many web parts use ASP.NET server side controls. Typically you would instantiate those controls in CreateChildControls() and populate them with data from web services, list items, or databases within OnPreRender() and let the base classes handle rendering those child controls. To keep this example simple, we kept it down to two methods: OnLoad() and RenderContents() and we are simply rendering Javascript and HTML.
There are plenty of improvements that can be made, but in the interest of keeping it simple (and getting sleep), we kept it as small as possible. For a list of ideas, see the source code.

Here’s what you need to do to get this to work:

  1. Download the source from here.
  2. Unzip the source and put it on a development SharePoint server.
  3. Open the solution and fix the reference to Microsoft.SharePoint.dll (typically this is found in C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\ISAPI)
  4. Update the <SafeControls> section of web.config to contain the following:<“ThreeWill.SharePoint.NextLevel.UI.WebControls.WebParts” TypeName=”*” Safe=”True” />
  5. The web.config file is typically found in C:\Inetpub\wwwroot\wss\VirtualDirectories\80.
  6. Compile the project then run the provided “_install.bat”. This batch file simply puts the web part in the GAC and runs IISRESET. Web controls can alternatively be deployed to the web site bin directory.
  7. Add the web part to the web part gallery.
  8. Go to the web part gallery by going to the top level site, then choosing Site Actions -> Site Settings, and under Galleries click Web Parts
  9. On the Web Part Gallery page click New.
  10. On the Web Part Gallery : New Web Parts page scroll down until you see ThreeWill.SharePoint.NextLevel.UI.WebControls.WebParts.VirtualEarthWebPart. Click its checkbox and then click the Populate Gallery button.
  11. Navigate to the page where you want an instance of your web part, edit the page and add the “VirtualEarthWebPart” (under the Miscellaneous section).
  12. Create a list to contain pin locations.
  13. Within the same site that has your web part create a “Locations” list (name it whatever you like).
  14. Update the list schema by adding “Latititude”, “Longitude”, and “Description” fields (the exact field names matter).
  15. Add rows to the list. One way to find latitude and longitude values is to use http://terraserver.microsoft.com/.
  16. Edit your web part properties. Go to the miscellaneous section and at least set the list name to be the name of your list. The zoom level can be anywhere from 1 to 19.

Hopefully that does it for you. Granted, this could be packaged into a nice feature that did all of the above work for you. You should be able to add multiple instances of the web part on the same page and each one could point to the same locations list or different lists.

I believe there are more sophisticated Virtual Earth web parts out there. The purpose of this was just to show something fun and simple. We were able to draw on recent project experience where we created a more complicated Virtual Earth web part.

One final note… Microsoft Virtual Earth does have terms of use that you need to adhere to.

read more
Kirk LiemohnMicrosoft Virtual Earth Web Part
report-pencil.jpg

SQL Server 2005 Reporting Services Add-in Primer

The SQL Server 2005 Reporting Services Add-in provides the following functionality:

  • A Report Viewer Web Part, which provides report viewing capability, export to other rendering formats, page navigation, search, print, and zoom.
  • Web application pages so that you can create subscriptions and schedules, set model item security, and manage reports, models, and data sources.
  • Support for standard Windows SharePoint Services features including document management, collaboration, security, and deployment with report server content types. You can use alerts, versioning (check in/out), and Filter Web Parts with reports. You can add the Report Viewer Web Part to any page or dashboard on a SharePoint site and customize its appearance. You can use SharePoint permission levels and roles to control access to report server content. You can also use SharePoint forms authentication to support access over Internet connections.
  • Note
    The add-in is for reporting on SQL data not SharePoint data.

This walk through makes a few assumptions about your setup environment.

  • Active Directory 2003 domain running in native mode
  • The SharePoint server is on a separate box from the Reporting server
  • The reporting server and the SQL server are on the same box
  • The SPAdmin account is the SharePoint administration account and is the local administrator on the SharePoint server and the SQL server
  • SPSQL account runs the sql services and reporting services
  • SPSites account runs the application pool for the SharePoint web site.
  • Your SharePoint Server is set to use Kerberos authentication
SetSPN

SetSPN (set spin) is used to configure Active Directory user and computer accounts for Kerberos delegations. Kerberos delegation is necessary if you are running reporting services on a different server than your SharePoint server. If user A hits a website on computer B, computer B can forward the authentication to computer C. There are two benefits to configuring Kerberos; one, Kerberos is a more secure protocol than NTLM, two, Kerberos is necessary to correctly configure Reporting Services.

  1. Login to domain controller
  2. Download the setspn.exe from http://www.microsoft.com/downloads/details.aspx?familyid=5fd831fd-ab77-46a3-9cfe-ff01d29e5c46&displaylang=en
  3. Run setspn_setup.exe and install tool, click next
  4. Agree to the EULA
  5. Accept the default path and click install now
  6. Click start -> Run and enter cmd
  7. From the command prompt navigate to C:\Program Files\Resource Kit. You will need to use the setspn for the following three accounts. The SharePoint Service Account (SPAdmin), the Default site application pool account (SPSites) and the SQL Service account (SPSQL). Issue the following commands:
    1. setspn -A http/llqawss01 qalbapad\spadmin
    2. setspn -A http/llqawss01.qalbapad.qalocal qalbapad\spadmin
    3. setspn -A http/llqawss01 qalbapad\spsites
    4. setspn -A http/llqawss01.qalbapad.qalocal qalbapad\spsites
    5. setspn -A http/llqawss01 qalbapad\spsql
    6. setspn -A http/llqawss01.qalbapad.qalocal qalbapad\spsql
    7. setspn -A http/llqasql01 qalbapad\spadmin
    8. setspn -A http/llqasql01.qalbapad.qalocal qalbapad\spadmin
    9. setspn -A http/FQDN of server (www.ll.com) qalbapad\spsites
    10. setspn -A http/FQDN of server (www.ll.com) qalbapad\spadmin
    11. setspn -A http/FQDN of server (www.ll.com) qalbapad\spql
      You notice that you will need to setspn on each name the computer may use, the netbios name, the internal FQDN, if this machine uses another FQDN you will need to add this as well.(To be honest this is probably over-kill but this will cover all your bases)
  8. On the domain controller open active directory users and computers, We need to trust the computer accounts and Service accounts for delegation
    1. Find the SQL server in Active Directory Users and Computers (ADUC) right Click and go to properties and click the Delegation tab, then select Trust this computer for delegation to any service (Kerberos only)
    2. Find the WSS server in ADUC right Click and go to properties and click the Delegation tab then select Trust this computer for dlegation to any service (Kerberos only)
    3. Find the SharePoint Service account in ADUC go to properties and click the Delegation tab then select Trust this user for delegation to any service(Kerberos only)
    4. Find the SharePoint Site (SPSites) account in ADUC go to properties and click the Delegation tab then select Trust this user for delegation to any service(Kerberos only)
    5. Find the SQL Server Service (SPSQL) in ADUC go to properties and click the Delegation tab then select Trust this user for delegation to any service(Kerberos only)
On the SQL/Reporting Server
  1. Make SPAdmin local administrator of the SQL server computer
  2. Install Microsoft .NET Framework 2.0
  3. Microsoft .NET Framework 3.0
  4. Download the SharePoint install from Microsoft
  5. Execute SharePoint.exe
  6. Accept the licensing agreement, click continue
  7. Choose the advanced installation option
  8. For Server Type choose Web Front-End (WFE), click install now
  9. Click close to run the SharePoint Technology Configuration wizard
  10. Select Yes, I want to connect to an existing server farm, click next
  11. Enter the name of the database server and then click Retrieve Database Names. This will bring back the SharePoint Configuration database name.
  12. In the Specify Database Access account enter the SharePoint Service account (SPAdmin) and password information, click next
  13. Click Next
  14. Click Finish
  15. Download the reporting services add-in http://www.microsoft.com/downloads/details.aspx?familyid=1E53F882-0C16-4847-B331-132274AE8C84&displaylang=en
On the SharePoint Server

Install the SharePoint add in for Reporting services.

  1. SharePointRS.msi, click next
  2. Accept the Licensing agreement, click next
  3. Click next
  4. Click Install
  5. Click Finish when complete
  6. Login to the SQL Server computer, click start -> All Programs -> Microsoft SQL Server 2005 -> Configurations Tools -> Reporting Services Configuration
  7. Connect to the SQL Server
  8. Click on Database Setup
  9. Click on Change to change the server mode to SharePoint
  10. Click yes to create a new Reporting Services database
  11. Leave the defaults and enter a name for the new SharePoint integrated Reporting Services database, click OK.
  12. Click Apply
  13. Leave the defaults and click OK
  14. Now we need to configure the Reporting Services application pool to run as SPAdmin. Open IIS Manager and navigate to the Application Pool -> Report Server
  15. Right click on Report Server and click properties, click the Identity tab
  16. Configure the identity to be SPAdmin. This will allow the reporting server to access the SharePoint server for the SharePoint integration to work properly.
  17. In IIS manager under the Web Sites folder right click the default site (This is where reporting services web is located) and click properties
  18. In the Web Sites tab change the port to 8080, Click OK to apply
  19. Return to the Reporting Server configuration and refresh. In the Web Service Identity you will need to click apply to complete the change made to the application pool
  20. Click on SharePoint Integration
  21. Follow link to SharePoint Central Administration site
  22. From the Application tab click on Manage integration settings
  23. Enter the url for the report server plus the virtual directory for the report server. Most likely this will be http://machinename:port#/reportserver, Click OK
  24. Click on Grant database access, this will default to the local server. Change to the reporting server. Click OK
  25. You will be prompted to enter credentials for accessing the report server. Enter the SQL account (SPSQL), click ok
  26. Click on Set Server Defaults
  27. In Reporting Services Server Defaults accept the defaults and click OK
read more
Tim CoalsonSQL Server 2005 Reporting Services Add-in Primer
copy-e1425586355402.jpg

Web Part Page Templates

Recently one of my peers at work came across an MSDN article that described how to expose a “custom template” in SharePoint 2003 (http://msdn2.microsoft.com/en-us/library/ms916835.aspx). I was able to follow these instructions using WSS 2007 and was able to expose my custom template in a list of available templates.  The solution involves modifying a shared page in the layouts folder (_layouts/create.aspx) as well as making a custom copy of an existing page ( _layouts/spfc.aspx).

Lastly, you need to copy your template and an image of your template to the SMARTPGS folder (C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\1033\STS\DOCTEMP\SMARTPGS) and associated images folder (C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\IMAGES).


SharePoint is a web application platform in the Microsoft Office server suite. Launched in 2001, SharePoint combines various functions which are traditionally separate applications: intranet, extranet, content management, document management, personal cloud, enterprise social networking, enterprise search, business intelligence, workflow management, web content management, and an enterprise application store. SharePoint servers have traditionally been deployed for internal use in mid-size businesses and large departments alongside Microsoft Exchange, Skype for Business, and Office Web Apps; but Microsoft’s ‘Office 365’ software as a service offering (which includes a version of SharePoint) has led to increased usage of SharePoint in smaller organizations.

While Office 365 provides SharePoint as a service, installing SharePoint on premises typically requires multiple virtual machines, at least two separate physical servers, and is a somewhat significant installation and configuration effort. The software is based on an n-tier service oriented architecture. Enterprise application software (for example, email servers, ERP, BI and CRM products) often either requires or integrates with elements of SharePoint. As an application platform, SharePoint provides central management, governance, and security controls. The SharePoint platform manages Internet Information Services (IIS) via form-based management tooling.

Since the release of SharePoint 2013, Microsoft’s primary channel for distribution of SharePoint has been Office 365, where the product is continuously being upgraded. New versions are released every few years, and represent a supported snapshot of the cloud software. Microsoft currently has three tiers of pricing for SharePoint 2013, including a free version (whose future is currently uncertain). SharePoint 2013 is also resold through a cloud model by many third-party vendors. The next on-premises release is SharePoint 2016, expected to have increased hybrid cloud integration.

Office 365 is the brand name used by Microsoft for a group of software plus services subscriptions that provides productivity software and related services to its subscribers. For consumers, the service allows the use of Microsoft Office apps on Windows and OS X, provides storage space on Microsoft’s cloud storage service OneDrive, and grants 60 Skype minutes per month. For business and enterprise users, Office 365 offers plans including e-mail and social networking services through hosted versions of Exchange Server, Skype for Business Server, SharePoint and Office Online, integration with Yammer, as well as access to the Office software.

After a beta test that began in October 2010, Office 365 was launched on June 28, 2011, as a successor to Microsoft Business Productivity Online Suite (MSBPOS), originally aimed at corporate users. With the release of Microsoft Office 2013, Office 365 was expanded to include new plans aimed at different types of businesses, along with new plans aimed at general consumers wanting to use the Office desktop software on a subscription basis—with an emphasis on the rolling release model.

read more
Tim CoalsonWeb Part Page Templates
web-20.jpg

Custom Web Part Properties

Out of the box SharePoint comes with a lot of Web 2.0 features that empower the end users to customize and personalize their pages and the web parts on those pages. In WSS v3 much of this power comes from ASP.NET 2.0 and the web part infrastructure, including many EditorPart controls found in the System.Web.UI.WebControls.WebParts namespace.  Some of these controls include the AppearanceEditorPart, BehaviorEditorWebPart and LayoutEditorPart, which are all used to define characteristics about how the web part is rendered to the end user.

Additionally, there is the PropertyGridEditorPart which provides a great deal of power and flexibility to web part developers by allowing them to configure additional web part properties that are persisted to durable storage on either a shared or per user basis.  While I love this power, I didn’t like having a separate zone to edit web part properties.  I felt that it would be more user friendly if certain properties were editable within the web part frame rather than in a separate tool part.

Based on the functionality of the PropertyGridEditorPart, a base web part was created allowing all inheriting web parts to reap the rewards of an expand/collapse property editor that when wrapped with an Update Panel, provides a clean no visible post back way of editing web part properties.  In the code snippets below I have broken down my approach into steps that correlate to the lifecycle of the web part.

First the controls for each of the properties needed to be created.

In the CreateChildControls override, a filtered list of editable properties for the user is retrieved for the web part using the GetEditableProperties  method.  Then, controls are created for each of the properties using CreateEditorControl.

protected override void CreateChildControls()
{
//... Additional code omitted for clarity
// Clear editor controls before populating
this.EditorControls.Clear();
// Loop through filtered list of editable properties to generate a property editor control for it
foreach (PropertyDescriptor descriptor in this.GetEditableProperties(true))
{
// Create the control
Control control = this.CreateEditorControl(descriptor);
if (control != null)
{
// Add to the editor controls array for user later when applying changes and handling errors
this.EditorControls.Add(control);

 

// Get the display name and description to use for the control label
String displayName = this.GetDisplayName(descriptor);
String description = this.GetDescription(descriptor);
// Add the control to the property table with the label and description
this.AddControlToPropertiesTable(displayName, description, control);
}
}

 

// Initialize error messages array to the number of editor controls
this._errorMessages = new String[this.EditorControls.Count];
//... Additional code omitted for clarity
}
private PropertyDescriptorCollection GetEditableProperties(bool sort)
{
// Get a filtered list of properties
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(this,
new Attribute[] { WebPartBrowsableAttribute.Yes });

 

// Sort the list if necessary
if (sort)
{
properties = properties.Sort();
}

// Loop through list of properties and determine if this user can edit (personalizible vs shared)
PropertyDescriptorCollection descriptors2 = new PropertyDescriptorCollection(null);
foreach (PropertyDescriptor descriptor in properties)
{
if (this.CanEditProperty(descriptor))
{
descriptors2.Add(descriptor);
}
}
return descriptors2;
}
private Control CreateEditorControl(PropertyDescriptor property)
{
// Get the type for the property to determine which control to create
Type propertyType = property.PropertyType;

// Return a CheckBox if the property is a boolean
if (propertyType == typeof(bool))
{
CheckBox checkBox = new CheckBox();
checkBox.ID = controlId;
return checkBox;
}

// Return a DropDownList if the property is an enumeration
if (typeof(Enum).IsAssignableFrom(propertyType))
{
DropDownList list = new DropDownList();
list.ID = controlId;

// Create the list of enumeration items add populate the DropDownList
foreach (Object obj in property.Converter.GetStandardValues())
{
String text = property.Converter.ConvertToString(obj);
list.Items.Add(new ListItem(text));
}
return list;
}

// Return a TextBox if no other control match was found
TextBox textbox = new TextBox();
textbox.ID = controlId;
textbox.Columns = TextBoxColumns;
return textbox;
}

Now that the controls have been created they need to be populated with values from the web part properties.

In the OnPreRender event (which occurs after CreateChildControls() and is a recommended location for your data interaction activities), the properties are synced to the controls so when they are rendered they have the updated property information.  If you’ve ever created a custom EditorPart, SyncChanges will be very familiar since that is a method you override for syncing your properties to your custom controls.

protected override void OnPreRender(EventArgs e)
{
//... Additional code omitted for clarity
// If the web part is visible and there are no errors load property editor controls with their data

 

if (this.Visible && !this.HasError)
{
this.SyncChanges();
}
//... Additional code omitted for clarity
}

 

private void SyncChanges()
{
// Make sure the controls exist
this.EnsureChildControls();
int num = 0;

 

// Get all the editable properties and set the control value
foreach (PropertyDescriptor descriptor in this.GetEditableProperties(true))
{
if (this.CanEditProperty(descriptor))
{
// Get the control from the editor controls collection
Control control = (Control)this.EditorControls[num];
// Set the value of the control based on type
this.SyncChanges(control, descriptor);
num++;
}
}
}
private void SyncChanges(Control control, PropertyDescriptor property)
{
if (propertyType == typeof(bool))
{
CheckBox box = (CheckBox)control;
box.Checked = (bool)property.GetValue(this);
}
else if (typeof(Enum).IsAssignableFrom(propertyType))
{
DropDownList list = (DropDownList)control;
list.SelectedValue = property.Converter.ConvertToString(property.GetValue(this));
}
else
{
TextBox box2 = (TextBox)control;
box2.Text = property.Converter.ConvertToString(property.GetValue(this));
}
}

Finally, when you make changes to the property values in the web controls, the changes need to be applied to the web part properties.

In the save event handler this is accomplished by calling an ApplyChanges method. Again, ApplyChanges comes from its EditorPart roots meaning to apply control changes to the properties. Within the ApplyChanges method the GetEditableProperties method is used again to retrieve the properties and then an Array of controls populated during CreateChildControls is used to retrieve the control values and set the property value. Note that much of the error handling and try catch blocks have been removed for clarity.

protected void SaveLinkButton_Click(Object sender, EventArgs e)
{
//... Additional code omitted for clarity
// Update the properties
this.ApplyChanges();
}
private bool ApplyChanges()
{
//... Additional code omitted for clarity
// Loop through each editible property, get the control for it and set its value
PropertyDescriptorCollection editableProperties = this.GetEditableProperties(true);

 

for (int i = 0; i < editableProperties.Count; i++)
{
// Get the property
PropertyDescriptor property = editableProperties[i];
// Get the editor control
Control editorControl = (Control)this.EditorControls[i];
// Get the value from the control
Object editorControlValue = this.GetEditorControlValue(editorControl, property);
property.SetValue(this, editorControlValue);

//... Additional code omitted for clarity
}
//... Additional code omitted for clarity
}

 

private Object GetEditorControlValue(Control editorControl, PropertyDescriptor property)
{
CheckBox box = editorControl as CheckBox;
if (box != null)
{
return box.Checked;
}
DropDownList list = editorControl as DropDownList;
if (list != null)
{
String selectedValue = list.SelectedValue;
return property.Converter.ConvertFromString(selectedValue);
}
TextBox box2 = (TextBox)editorControl;
return property.Converter.ConvertFromString(box2.Text);
}

As part of this exercise, a custom attribute was introduced to allow showing properties only in the custom web part properties panel and not in the standard PropertyGridEditorPart.

read more
Kirk LiemohnCustom Web Part Properties
reports-e1425586188238.jpg

Effective SharePoint Reporting

To effectively report on SharePoint data using SQL Server Reporting Services (SSRS), you must copy the data into SQL Server using SSIS. Unfortunately, SharePoint out of the box does not have a solution for reporting on its own data other than configuring list views or building data views. Building a reporting database gives developers a powerful toolkit for building high-performance reports while off-loading the burden of report processing from SharePoint.

So how do you get the SharePoint data loaded into SQL Server so reports can be built and executed in SSRS? Simple. Write an SSIS package that executes the following steps:

  • Executes a small custom utility to extract the SharePoint data to xml files using the built-in ‘Lists’ web service.
  • Delete the current reporting data in the database
  • Load each of the list’s xml data files into the corresponding tables
  • Send an e-mail notifying that reporting data has been successfully loaded

Finished Control Flow Example

Small Utility to the Generate the List XML Data Files (see the “Run SharePoint Extractor” task in the Control Flow above):

First, create custom views for each SharePoint list to contain the data needed for reporting. The views must be defined to return a set amount of records used for making multiple calls to get all the data from large lists. Also, the views can be used to only extract the data needed for reports instead of all the data in the list. Next, write a method named something like ExtractListData that takes the following parameters: string listName, string fileName, string elementName, string viewName. The ExtractListData method basically makes a call to the SharePoint Lists’ GetListItems method to retrieve the data. Once you retrieve the data into the NodeListItems, then there are several important items that need to be done:

  • Write out the root xml element
  • Scrub all lookup values to other lists – you only include the ID value, so you have to remove the #;<data value> from the columns value
  • Scrub all the Person/Group values to only the data part of the value so you have to remove the ID and the “#;”
  • Remove the z: from the z:row element name because the SSIS XML Source Data Flow Component will not load the data
  • Convert any number values to integers if they are defined as integers in the database
  • Write out the transformed rows
  • Write out the ending root xml element

Delete the Data from the Reporting Tables (see the “Delete All Records” task in the Control Flow above):

The design is to completely delete all the data in the SQL Server reporting tables and then reload all the data from the xml data files generated above. Since the entire process happens rather quickly, I did not feel the need to do incremental updates based on my performance requirements, but that may be needed if there is a tremendous amount of data in the SharePoint lists. I basically had 16 lists where most lists were < 1000 records and one list with ~50,000 records.

Load Each List’s XML Data Files:

To create the SSIS XML Source Data Flow Components, you need an XML Schema file for each of the lists. I just opened up the XML files generated above in Visual Studio 2005 and selected the XML->Create Schema item for a quick and dirty way to get the XML Schema. The generated schema will need to cleaned up a little, such as setting lookups to xs:integer.

Now you can create a new SSIS Data Flow task and put an XML Source component in it. Right-click and select Edit… on the XML Source to configure the settings including the schema file and the columns used to load the reporting database.

Next, drop a Row Count Data Flow Transformation and create a link between the XML Source and the Row Count tasks. Following that, drop an OLE DB Destination Data Flow Destination and configure it to connect to the reporting database and select the appropriate table. You also need to link all the columns from the XML Source columns to the reporting database table columns to define where the data gets loaded.

Data Flow Task Example:

Send Email to Notify Successful Data Load:

Drop a Send Email Task on the control flow and link it to the last Data Flow task. First, you will need to create an SMTP Connection to configure which email server to use to send the email.

Next, right-click on the task and select Edit… and select the SMTP connection created above and fill out the rest of the email information.

read more
Kirk LiemohnEffective SharePoint Reporting
calendars.jpg

Aggregating SharePoint Events

One of the requirements that we have received from our customers building Intranet Portals is the ability to aggregate data to the Home site from Calendars and Announcements that “live” on child sites such as HR and IT. Obviously, one way to accomplish this would be to create Announcements and Calendars lists on the Home site and post duplicate entries to these lists when entries are created in the Calendar and Announcement lists in HR and IT. Of course this is not a very efficient way to do things and has a greater chance of data entry errors.

Using MOSS and the Content Query Web Part, aggregating data from lists located in child sites is now possible without the need for unnecessary duplication.

To leverage this capability in MOSS, first make sure the Content Query Web Part shows up in your list of available Web Parts. If not, you likely need to turn on the Office SharePoint Server Publishing feature on the site by going to Site Actions, Site Settings, Site Features and activating the Office SharePoint Server Publishing feature as demonstrated below:

Click activate. Under normal circumstances, the screen will refresh and you will notice the blue “Active” indicator next to the feature. However, in some cases, you might see the screen below which indicates an additional step is necessary.

To install the Office SharePoint Server Publishing Infrastructure, navigate to the site collection. Select Site Actions, Site Settings, Site Collection Features to see the following screen.

Choose to Activate the Office SharePoint Server Publishing Infrastructure feature for the site collection. The screen shot below demonstrates a successful activation.

Now that the Office SharePoint Server Publishing Infrastructure is in place, you can now activate the Office SharePoint Server Publishing feature by navigating to the Site Collection, selecting Site Actions, Site Features and then activating the Office SharePoint Server Publishing feature as demonstrated below.

After selecting Activate for the Office SharePoint Server Publishing feature, you should see the screen below

Now that the Content Query Web Part is available, you can add the Content Query Web Part to a page by navigating to a site, clicking Site Actions, edit page and then selecting Add a Web Part in one of the Web Part zones as shown below.

After you click, Add a Web Part, you will see the following dialog where you can scroll down and click the Content Query Web part

Now that you have successfully added the Content Query Web Part to a Web Part page, you can quickly see content from child sites on the Home Page by updating the Query configuration in the Web Part properties page. However, by default, the number of fields you have access to and the ability to alter the display and styling of this data is severely limited without applying some customization. The good news is that George Perantatos does a great job of describing these steps in detail, so I will merely point you to the ECM blog for these details http://blogs.msdn.com/ecm/archive/2006/10/25/configuring-and-customizing-the-content-query-web-part.aspx.

In addition, Heather Solomon, SharePoint MVP, provides more detailed instructions on creating a new style which can be found at http://www.heathersolomon.com/blog/articles/CustomItemStyle.aspx.

Now that I can view Calendar and Announcement data from child sites, I want to provide the content authors more control over the data that gets displayed on the Home site as well as some control over the duration that the data is displayed. To accomplish this, I add 3 new Columns to each list; Display on Home Page (Yes/No), Display Start Date (Date and Time) and Display End Date (Date and Time). These fields allow content authors to determine if they want this content to “bubble up” to the Home site and, if so, what dates to display this data. To make use of these fields in the Content Query Web Part query, I navigate back to the Content Query Web Part and update the filter section under the Query Properties to leverage these fields as shown below.

This wraps up the basics of aggregating Calendar and Announcement data using the Content Query Web Part. I hope you find this post helps stimulate some ideas for you to add value to your organization or customer.

read more
Tim CoalsonAggregating SharePoint Events