Install PowerPivot for SharePoint on a Domain Controller – TechNet Articles – Home – TechNet Wiki

Today I decided to begin building out a new SharePoint VM server for demos and development. The goal of this VM exercise is to explore the different options available for Business Intelligence solutions.

In the space of SharePoint Business Intelligence, there are a lot of different offerings under 2010, so understanding what each offers and the caveats of each one is very important. Also, investigating use cases and building demonstrations helps communicate to business users why they would choose one offering over another — not to mention it is a form of entertainment for me to see how they work and build out sample solutions. The current offerings for BI under SharePoint 2010 include:

  • Excel Services
  • Access Services
  • Visio Services
  • PerformancePoint
  • PowerPivot

Recently, I’ve been having a lot of discussions with a colleague concerning PowerPivot, so I decided to install it and see what it has to offer or why I would even venture to recommend it to a client. Following the instructions in the following article allowed me to get up and running quickly (and successfully)

Install PowerPivot for SharePoint on a Domain Controller – TechNet Articles – Home – TechNet Wiki.


Using SPServices to get the values of a Choice Field

In a recent requirement for a client, I needed to get a list of all the available choices to provide as a filter to a data view web part. One option would be to iterate over the entire list and return all of the unique values, but this list could contain upward of 3000 items. It wouldn’t be the most efficient way to handle less than 20 filter values. The second option is to get the available options from the list schema, and this is the option I decided to choose.


In order to complete this task, the following javascript libraries are needed:

  • jQuery 1.4.x
    The core jQuery library. I am currently using 1.4.2 in my production environment.
  • SPServices 0.5.7
    A jQuery plug-in used to communicate with the SharePoint 2007 web services.

I began by adding the scripts to a document library in SharePoint and created a new ASPX page. On the new ASPX page, I added my script references for jQuery 1.4.x and SPServices:

<head runat="server">
<META name="WebPartPageExpansion" content="full">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Untitled 1</title>
<script type="text/javascript" src="JS/jquery-1.4.2.min.js"></script>
<script type="text/javascript" src="JS/jquery.SPServices-0.5.7.min.js"></script>

Next, I added some place holders for my menu options:

<div id="SiteList" class="dataview-group-panel">
	<div class="dataview-heading">Site</div>
<div id="DocumentVersion" class="dataview-group-panel">
	<div class="dataview-heading">Document Version</div>

Now, for the heavy lifting I created a new function that executes SPServices to get the list schema information and return it to two separate functions for parsing.

Here is the function for executing SPServices to call the Lists web service.

function LoadSiteList()
		operation: "GetList",
		listName: "Department Instructions",
		completefunc: function(xData, Status) { 
                                  parseSiteField(xData, Status); 
                                  parseDocVersionField(xData, Status); 
		async: false

This function simply makes a call to the lists.asmx web service and executes a call to get the list schema for the specified list. The returned list schema looks similar to the following:

        <List DocTemplateUrl="" DefaultViewUrl="/sites/policy/di/Forms/myDocuments.aspx"
        MobileDefaultViewUrl="" ID="{67E5E03B-A930-4D47-9097-D4A7A03FF497}"
        Title="Department Instructions" Description="" ImageUrl="/_layouts/images/itdl.gif"
        Name="{67E5E03B-A930-4D47-9097-D4A7A03FF497}" BaseType="1"
        FeatureId="00bfea71-e717-4e80-aa17-d0c71b360101" ServerTemplate="101" Created="20101021 09:01:00"
        Modified="20101025 09:06:43" LastDeleted="20101025 08:31:00" Version="129"
        Direction="none" ThumbnailSize="" WebImageWidth="" WebImageHeight="" Flags="79695880" ItemCount="33"
        AnonymousPermMask="0" RootFolder="/sites/policy/di"
        ReadSecurity="1" WriteSecurity="1" Author="1" EventSinkAssembly="" EventSinkClass="" EventSinkData=""
        EmailInsertsFolder="" EmailAlias="" WebFullUrl="/sites/policy" WebId="464d650f-041e-4209-8877-80b77b7511bb"
        SendToLocation="|" ScopeId="59d29d47-5a65-48b9-8a03-698972e7702e" MajorVersionLimit="0" MajorWithMinorVersionsLimit="0"
        WorkFlowId="" HasUniqueScopes="False" AllowDeletion="True" AllowMultiResponses="False" EnableAttachments="False"
        EnableModeration="False" EnableVersioning="False" Hidden="False" MultipleDataList="False" Ordered="False"
        ShowUser="True" EnableMinorVersion="False" RequireCheckout="False">
            <Field Type="Choice" DisplayName="Document Version"
            Required="FALSE" Format="Dropdown" FillInChoice="FALSE"
            Group="Policy Custom Columns"
            StaticName="Doc_Version" Name="Doc_Version"
            Description="Select the appropriate revision (or New)."
            Version="2" Customization="" ColName="nvarchar11"
            <Field Type="MultiChoice" DisplayName="Site"
            Required="FALSE" FillInChoice="TRUE"
            Group="Policy Custom Columns"
            StaticName="Site" Name="Site" Customization=""
            ColName="ntext2" RowOrdinal="0"
            Description="Select the site(s) the document applies to."
                <CHOICE>Site 1</CHOICE>
                <CHOICE>Site 2</CHOICE>
                <CHOICE>Site 3</CHOICE>

The list schema includes all of the data related to the list, including the fields that make up the list. Leveraging this information, the schema can be parsed by jQuery to get the values for each choice column where I’m interested in providing filtered values.

The first field to parse is the Site field. To parse the data, simply use the soap response with an appropriate jQuery selector, in this case each field definition is defined as a <Field … />. One of the available attributes is DisplayName. Using this information, the selector becomes Field[DisplayName='[[NAME OF FIELD]]’] CHOICE. Now, simply plug this into a javascript function and target the appropriate placeholder:

function parseSiteField(xData, Status) {
	if(Status == 'success') {
		var SiteList = $("#SiteList");
		$(xData.responseXML).find("Field[DisplayName='Site'] CHOICE").each( function () {
			$(SiteList).append("<div class=\"dataview-choice-option\">" + $(this).text() + "</div>");

And again, notice the jQuery selector for a different field:

function parseDocVersionField(xData, Status) {
	if(Status == 'success') {
		var DocVersion = $("#DocumentVersion");
		$(xData.responseXML).find("Field[DisplayName='Document Version'] CHOICE").each( function () {
			$(DocVersion).append("<div class=\"dataview-choice-option\">" + $(this).text() + "</div>");

Now, instead of iterating over the entire list to find filter values, I simply use the List’s schema to get the available filters for any choice column in a list.

Developer Patterns to Integrate Microsoft Silverlight 3.0 with Microsoft SharePoint 2010 :: Sessions :: Microsoft PDC10 | October 28 — 29

Developer Patterns to Integrate Microsoft Silverlight 3.0 with Microsoft SharePoint 2010 :: Sessions :: Microsoft PDC10 | October 28 — 29.

I watched this video today and learned a lot about some of the integration options available with SharePoint 2010 and Silverlight. I already see several projects that I can begin leveraging Silverlight to create complete solutions for several of my clients.

Copy InfoPath Rules

One of the most frustrating tasks in InfoPath 2007 is the duplication of similar or identical rules. If you have several views that will apply very similar, or identical rules, the inability to copy those rules leaves you to have to recreate each and every action stored in the rule. This is not such a big deal if there are only a couple of actions, but if you have multiple actions that need to be executed, this can be a painful way to modify and create rules.

What if there were a way to stub out the rules that will be duplicated and just copy them making simply changes back in the UI when done? Here’s how:

Create the first complete set of actions for the rule that needs to be duplicated/copied while in the InfoPath designer. Go to each place where the rule needs to be duplicated and create an initial rule with a single action of some kind – also be sure to provide a meaningful name. Save the InfoPath from as source files (File > Save as Source Files). Close the form and use a text editor to view the XML in manifest.xsf.

Find the node xsf:ruleSets. This is a list of all the rules defined in your form and each one will have an xsf:ruleset followed by the actions contained in the ruleset. Copy the actions from your completed ruleset to an incomplete ruleset.

Completed Rules
<xsf:ruleSet name="ruleSet_2">
	<xsf:rule caption="Query Profile Using Root URL" isEnabled="yes">
		<xsf:queryAction adapter="GetUserProfileByName - Ops"></xsf:queryAction>
		<xsf:assignmentAction targetField="my:LoadFromOpsSuccess" expression=""true""></xsf:assignmentAction>
		<xsf:assignmentAction targetField="my:FirstName" expression="xdXDocument:GetDOM("GetUserProfileByName - Ops")/dfs:myFields/dfs:dataFields/s0:GetUserProfileByNameResponse/s0:GetUserProfileByNameResult/s0:PropertyData/s0:Values/s0:ValueData/s0:Value[../../../s0:Name = "FirstName"]"></xsf:assignmentAction>
		<xsf:assignmentAction targetField="my:LastName" expression="xdXDocument:GetDOM("GetUserProfileByName - Ops")/dfs:myFields/dfs:dataFields/s0:GetUserProfileByNameResponse/s0:GetUserProfileByNameResult/s0:PropertyData/s0:Values/s0:ValueData/s0:Value[../../../s0:Name = "LastName"]"></xsf:assignmentAction>
		<xsf:assignmentAction targetField="my:DisplayName" expression="xdXDocument:GetDOM("GetUserProfileByName - Ops")/dfs:myFields/dfs:dataFields/s0:GetUserProfileByNameResponse/s0:GetUserProfileByNameResult/s0:PropertyData/s0:Values/s0:ValueData/s0:Value[../../../s0:Name = "PreferredName"]"></xsf:assignmentAction>
		<xsf:assignmentAction targetField="my:Account" expression="xdXDocument:GetDOM("GetUserProfileByName - Ops")/dfs:myFields/dfs:dataFields/s0:GetUserProfileByNameResponse/s0:GetUserProfileByNameResult/s0:PropertyData/s0:Values/s0:ValueData/s0:Value[../../../s0:Name = "AccountName"]"></xsf:assignmentAction>
		<xsf:assignmentAction targetField="@my:Source" expression=""Ops""></xsf:assignmentAction>
Incomplete Ruleset
<xsf:ruleSet name="ruleSet_2">
	<xsf:rule caption="Query Profile Using Root URL" isEnabled="yes">
						<span style="color:#000000;background:#FFFF00;"><!-- REPLACE ALL INNER NODES --></span>
		<xsf:queryAction adapter="GetUserProfileByName - Ops"></xsf:queryAction>

Save the file and open InfoPath 2007 again into design mode. You rules should now be copied and ready to tweak.