Work | SharePoint (and Project Server) Shenanigans

Calculated Column Formula: Date Format #SP2010 #SharePoint #in

March 9, 2011 Giles Hamson 1 comment

Just a reminder for me really… At a client at the moment who wants to put the date in list web parts in the following format: dd-MMM-yyyy.

I found the following article suggesting using the TEXT function in a Calculated Column

http://www.sharepointusecases.com/index.php/2009/01/customizing-datetime-format-on-a-list-view-web-part/

Formula Example:

1 =TEXT(Created,"dd-mmm-yyyy")

Output:

09-Mar-2011

Categories: Work Tags: Calculated Column, MOSS 2007, SharePoint 2010, SPF 2010, WSS 3.0

Using jQuery to return the Managed Metadata Term GUID #SP2010 #SharePoint #in

March 2, 2011 Giles Hamson 3 comments

For a completely different client who is using SharePoint 2010 for their company intranet we needed to implemented a UI web part which allowed end users to click on keywords which would then pre-filter search.

Keywords were entered in using the new Managed Metadata feature of SharePoint 2010.

However, during development we found that SharePoint Search for Managed Metadata does not use just the standard ?k= query string parameter to run the search query.

A new query string parameter is available especially for Managed Metadata:

And the expected format is similar to the following:

“ManagedMetadataTerm:TermGUID”

NB: I will probably explain the above in more detail in a later post

Anyway, when querying the lists.asmx web service and returning managed metadata columns, only the values are returned, not the GUID’s which poses us a problem for restricting search.

So after some research I came up with the following jQuery function calling the taxonomyClientService.asmx web service introduced in SharePoint 2010.

It’s is pretty basic at the moment but suits the requirement for my client in their setup:

Code Example:

 1 <script type="text/javascript"> 
 2     $(document).ready(function() {
 3         getTermsByLabel("Project Server"); //pass your variable in here;
 4     });
 5  
 6  function getTermsByLabel(termString) {
 7   var soapEnv =
 8             "<?xml version='1.0' encoding='utf-8'?> \
 9     <soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'> \
10       <soap:Body> \
11        <GetTermsByLabel xmlns='http://schemas.microsoft.com/sharepoint/taxonomy/soap/'>\
12         <label>" + termString + "</label>\
13         <lcid>1033</lcid>\
14         <matchOption>ExactMatch</matchOption>\
15         <resultCollectionSize>100</resultCollectionSize>\
16         <addIfNotFound>0</addIfNotFound>\
17        </GetTermsByLabel>\
18       </soap:Body> \
19     </soap:Envelope>";
20   //alert(soapEnv);
21         $.ajax({
22             url: "/_vti_bin/taxonomyclientservice.asmx",
23             type: "POST",
24             dataType: "xml",
25             data: soapEnv,
26             complete: processResult,
27             contentType: "text/xml; charset=\"utf-8\""
28         });
29  }
30  
31     function processResult(xData, status) {
32      //alert(status);
33      //alert(xData.responseText);
34      
35      $(xData.responseXML).find("GetTermsByLabelResult").each(function() {
36       
37       var outputValue = $(this).text();
38       var a9Array = outputValue.split("a9=");
39       var a21Array = a9Array[1].split("a21=");
40       var termGUID = a21Array[0].replace(/"/g, "");
41       termGUID = white_space(termGUID);
42       alert("-=" + termGUID + "=-");
43       
44   });
45           
46     }
47     
48     function white_space(stringValue) {
49   stringValue = (stringValue).replace(/^\s*|\s*$/g,'');
50   return stringValue;
51  }
52  
53 </script>
54

Links I found useful during my research:

TaxonomyClientService.asmx MSDN Reference:

http://msdn.microsoft.com/en-us/library/ee562146.aspx

GetTermsByLabel MSDN Reference:

http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.taxonomy.webservices.taxonomyclientservice.gettermsbylabel.aspx

Blog Post 1 – .NET Examples:

http://sharepointblog.virtosoftware.com/blogs/virtoteam/archive/2011/01/27/sharepoint-taxonomy-field-internal.aspx

Blog Post 2 – .NET Examples + Analysis of the XML output:

http://blogs.solidq.com/sharepoint/Post.aspx?ID=37&title=SharePoint%202010%2C%20Managed%20Metadata%2C%20TaxonomyClientService%20in%20depth

Categories: Work Tags: JavaScript, JQuery, Managed Metadata, SharePoint, SharePoint 2010, SPF 2010, Web Services

Useful findings whilst learning Reporting Services with #SP2010 #SharePoint #PS2010 #ProjectServer

March 2, 2011 Giles Hamson Leave a comment

So the first phase of the project I have been on for the last year is starting to move into the closing phases. The information architecture is in place, most of the custom development is complete and mapped to the project lifecycle / business processes of my client.

The project takes data from multiple places including:

SAP (Resource, cost rate, metadata and product contract information)
Project Server 2010 (Timesheets, Plans, Project / Task / Assignment data)
SharePoint 2010 (Risks, Issues, Actions, Documents etc.)
Custom database (Snapshot data)

I’ve learnt a lot about SharePoint 2010 and Project Server 2010 since I started this project (pre public beta). So now that we have all this data, the time has come to bring it all together with Business Intelligence dashboards and UI design.

Once of the main tools we are using for this is Reporting Services in SharePoint Integration mode and although I have lead projects involving SSRS in the past, actually getting my hands dirty and creating reports myself is something of a novelty.

However, time and resource pressures have meant that I have had to setup the SSRS infrastructure, development environments and test reports to ensure that the data can be utilised in the required way. As part of this setup I am also training my client on the business intelligence studio interface and deployment of reports in to SharePoint environment.

So getting to the point of this post, below is a list of useful links, findings and discoveries I have made during this setup process this week to serve as a reminder to me and will hopefully help others too.

Reporting Services Web Part – Firefox Compatibility (Windows, Linux – 3.6.13)

Turn off Asynchronous data (Edit Web Part Settings > View Section)

NB: If this setting is left to its default, the loading screen does not go away until you hit the refresh button on the Web Part toolbar.

SharePoint Integration Mode – Linking to pages within SharePoint

It would appear that the Reporting Services web part in SP2010 disables links where the target URL is within SharePoint itself (“/pages/default.aspx” for example).

This blog post suggested a useful workaround:

http://weblogs.asp.net/mnissen/archive/2009/03/26/reporting-services-does-not-display-hyperlink-for-placeholder-with-go-to-url-action.aspx

However the suggested approach was good if you had a definitive URL such as default.aspx, but if you wanted to add a query string parameter based on the data returned, adding as action directly to the image did not allow me to select the appropriate date.

To get round this, I added a calculated field to the data set using the same process suggested in the post:

="javascript:void(window.location.href='/pages/projectdashboard.aspx?projectid='" + dataset.fields[<field name>] +")"

NB: Since working this out, I have extended it to use a standard JavaScript function that is running from the Master Page to make links in the reports more generic.

Then for the action on the image I was able to select “Go to URL” and select my calculated field.

This doesn’t work in the designer preview (or any exports of the report), however it works a treat when you are using the report as part of a dashboard with drill down features.

Interactive Column Sorting

Another useful blog post:

http://weblogs.sqlteam.com/joew/archive/2008/08/02/60666.aspx

Although the instructions are for SSRS 2005, it is still relevant today.

Right click on the column header field
Select Properties
Interactive Sort Tab
Select the appropriate sort field
Click OK

Alternating Row Colours

Always a popular choice for tabular based reports.

Highlight the data row and under the properties pane for the background color, use the following expression:

=IIf(RowNumber(Nothing) Mod 2 = 0, "#F7F7F7", "#FFFFFF")

MSDN Documentation Links

Support Features for SharePoint Integration Mode: http://msdn.microsoft.com/en-us/library/bb326290(v=SQL.100).aspx
Reporting Services Features: http://msdn.microsoft.com/en-us/library/ms159273(v=SQL.100).aspx
Tutorials: http://msdn.microsoft.com/en-us/library/bb522859(v=SQL.100).aspx
Design Guidance: http://msdn.microsoft.com/en-us/library/ms159734(v=SQL.100).aspx
Designing and Implementing Reports Using Report Designer: http://msdn.microsoft.com/en-us/library/ms159253(v=SQL.100).aspx
Development Topics (High Level): http://msdn.microsoft.com/en-us/library/bb522683(v=SQL.100).aspx
Report Builder Information: http://technet.microsoft.com/en-us/ff657833.aspx
How to publish a report to a SharePoint Library: http://msdn.microsoft.com/en-us/library/bb283155.aspx

NB: Although a lot of the above links are for SSRS 2008, there is an option at the top of the page to make the content relevant for SSRS 2008 R2 also.

Categories: Work Tags: Business Intelligence, MOSS 2007, Project Server 2010, Reporting Services, SharePoint, SharePoint 2010, SPF 2010, WSS 3.0

Useful JavaScript Function – Refreshing a page with parameters for use with Query String Filter Web Parts in #SP2010 #SharePoint #ProjectServer #PS2010 #in

February 22, 2011 Giles Hamson Leave a comment

In my current project I have the need to determine the current users personal preferences based on favourites stored in a list.

These personal preferences then drive various web parts on the page.

Using this the Enterprise Edition of SharePoint Server 2010 makes this very easy with the use of the Query String Filter Web Part and various other web parts that support connections including:

Reporting Services Web Part
Excel Services Web Part
List Web Parts
etc…

Using the combination of parameters and the above web parts it makes it a lot easier to create dashboard pages that are specific to a users:

Preferences
Job Role
Department
etc…

Now in most of my projects, I have found that dashboard pages tend to be buried under a 2nd or 3rd level of navigation, which makes it very easy to create links with parameters already in them.

However, in this case, I have to create various dashboard pages at the root of the site, whilst using standard SharePoint Publishing Navigation (Global Navigation). The parameters being passed are user selectable based on favourites stored in a list.

Given that the SharePoint Site Map Provider doesn’t know about this random favourites list I have created, I need to improvise to ensure that the end user is presented with dashboard pages personalised to them, as soon as they hit the root site URL.

Code Example:

 1 <script type="text/javascript">
 2 
 3 var currentLocation = location.href; //Current Page URL
 4 //set it to lowercase for comparison purposes
 5 currentLocation = currentLocation.toLowerCase(); 
 6 
 7 //run function before document / SharePoint ready to avoid unwanted page flashing
 8 refreshPageWithParameters();
 9 
10 function refreshPageWithParameter() {
11 
12  var urlParameter = "";
13  
14  //In this example I am checking for a query string parameter called portfolioid
15  urlParameter = querySt("portfolioid"); 
16 
17  //confirm we do not have the parameter so the page refresh doesn't loop
18  if (urlParameter == "") {
19   //Stop the rest of the page render, JavaScript will still run
20   document.execCommand("stop");
21   
22   /*
23    Run functions you need to do to get your parameter value here
24    
25    set output of functions to urlParameter below
26   */
27   
28   urlParameter = "test"; //<function output here>;
29   
30   var newURL = currentLocation;
31  
32   if (currentPathname.indexOf("?") != -1) {
33    //& - Other Query String Parameters exist - add this to the end
34    newURL = "&portfolioid=" + urlParameter;
35   }
36   else {
37    //? - Other Query String Parameters do not exist
38    newURL = "?portfolioid=" + urlParameter;
39   }
40  
41   //Refresh the page with the new URL
42   location.href = newURL;
43  
44  }
45  
46 }
47 
48 function querySt(ji) {
49 
50  //This Useful JavaScript function was explained in a previous blog post
51  hu = window.location.search.substring(1);
52  hu = hu.toLowerCase();
53  gy = hu.split("&");
54 
55  for (i=0;i<gy.length;i++) {
56   ft = gy[i].split("=");
57   if (ft[0] == ji) {
58    return ft[1];
59   }
60  }
61  
62  return "";
63 }
64 
65 </script>

Essentially the following happens:

The page load to a certain point
We determine if we have the query string parameter(s) we are looking for
If we don’t, we stop the page from rendering using document.execCommand(“stop”)
Go get our require parameter(s)
Determine if any previous parameters existed on the page currently (to make the code more generic)
Reload the page with the new parameter(s)

If you find any unwanted page flashing before the page reload, use JavaScript to set style=”display: none;” on the main content <div> on the v4.master – Master Page, if the parameter doesn’t exist.

Although this is certainly one method of achieving my goal and seems to currently fit my clients requirements, I would certainly be interested to hear from others on how to achieve my goal without drastically changing standard SharePoint components.

Categories: Work Tags: JavaScript, MOSS 2007, Query String Filter Web Part, SharePoint 2010, SPF 2010, WSS 3.0

Useful JavaScript Function: isInteger and isNumeric #SharePoint #SP2010 #in

February 16, 2011 Giles Hamson Leave a comment

Just a quick post as a reminder for myself in future projects.

 1 function isNumeric(sText)
 2 {
 3    var ValidChars = "0123456789.";
 4    var IsNumber=true;
 5    var Char;
 6 
 7  
 8    for (i = 0; i < sText.length && IsNumber == true; i++) 
 9       { 
10       Char = sText.charAt(i); 
11       if (ValidChars.indexOf(Char) == -1) 
12          {
13          IsNumber = false;
14          }
15       }
16    return IsNumber;
17    
18 }
19 
20 function isInteger(sText)
21 {
22    var ValidChars = "0123456789";
23    var IsNumber=true;
24    var Char;
25 
26  
27    for (i = 0; i < sText.length && IsNumber == true; i++) 
28       { 
29       Char = sText.charAt(i); 
30       if (ValidChars.indexOf(Char) == -1) 
31          {
32          IsNumber = false;
33          }
34       }
35    return IsNumber;
36    
37 }
38

Categories: Work Tags: JavaScript, MOSS 2007, SharePoint 2010, SPF 2010, WSS 3.0

Working with Enterprise Custom Fields via VBA Macros in MS Project 2010 #MSProject #ProjectServer #SharePoint #SP2010 #in

February 15, 2011 Giles Hamson Leave a comment

As per my previous post, my colleagues and I are working on various customisations to the Microsoft Project 2010 client in our SharePoint 2010 / Project Server 2010 integration project for an international company.

Primarily my job is to architect and implement SharePoint solutions but recently I have been getting heavily involved in Project Server and Microsoft Project.

Yesterday and today I have been having to dust off my VBA skills to aid a colleague and wil the help of MSDN forums, we were able to come to a solution.

We found that when querying Enterprise Custom Task Fields from Project Server using the GetFields method, it would return them as a string value as it was shown in the client, regardless of the field type.

If you use the same GetFields method to query a local task field, it will be returned as expected in accordance to the field type.

For Example:

Client Setup Details: 1 Day = 8 Hours (480 Minutes)

Enterprise Custom Task Field

Field Name: EnterpriseDurationTest
Field Type: Duration
Field Source: Enterprise Custom Task Field (Project Server)
Value: 1 Day
GetFields Method return: 1 Day

Local Custom Task Field

Field Name: LocalDurationTest
Field Type: Duration
Field Source: Local Task Field (Microsoft Project)
Value: 1 Day
GetFields Method return: 480 (returned in minutes)

Now this make comparison quite hard unless you start splitting the string returned from the enterprise custom field and hard coding the number of hours that make up a day.

As a work around, Jan De Messemaeker (Project MVP), helped provide an answer:

By simply taking the output of the Enterprise Custom Task Field and placing it into a spare local field, it will do the appropriate conversion for us and we can the query the local field to to our comparison.

This blog post is simply here as a reminder for myself (future projects etc.) but also as a thank you to the growing Project Server and Microsoft Project community.

Categories: Work Tags: Macro, Microsoft Project 2010, Project Server 2010, VBA

Useful details and links when rolling out Microsoft Project 2010 #PS2010 #ProjectServer #SharePoint #SP2010 #MSProject #in

February 15, 2011 Giles Hamson 2 comments

In my current project where we are integrating SharePoint Server 2010 and Project Server 2010, we are starting to go into the details of rolling out the Microsoft Project 2010 client globally.

Some of the features we are implementing require VBA macros in MS Project 2010 which are brought down to the client application via the Enterprise Global Template from Project Server.

The following details and links have been useful during my investigations:

ADM Templates for Office 2010 – AD Policy Templates:

How to Digitally Sign a VBA Macro in Microsoft Project:

http://office.microsoft.com/en-us/project-help/add-a-digital-signature-to-a-file-or-macro-project-HP005249557.aspx

Registry Key to change:

\\HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\MS Project\Security\VBAWarnings

Registry Option Values:

1 = Enable all macros (not recommended; potentially dangerous code can run)
2 = Disable all macros except digitally signed macros
3 = Disable all macros with notification
4 = Disable all macros without notification

NB: The text in the options match those available in the Trust Center in File > Options of the Microsoft Project 2010 Client.

Categories: Work Tags: Active Directory, ADM Templates, Macro, Microsoft Project 2010, Office 2010, Project Server 2010, SharePoint 2010, VBA

Programmatically setting a field to hidden within a Content Type #SharePoint #SP2010 #in

February 9, 2011 Giles Hamson 2 comments

At a client recently, my development colleague was struggling to find out how to set a field attached to a custom Content Type in a list to be hidden to aid in document management (so it is not shown in the Document Properties panel in Microsoft Word).

The following post seemed to suggest an unofficial way:

http://blogs.flexnetconsult.co.uk/colinbyrne/permalink,guid,74e14296-f641-4d42-affa-8ef13e499c93.aspx

However, we try to keep things in a supported model if possible.

Through further looking he came across the following property:

http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spcontenttype.fieldlinks(v=office.14).aspx

After further playing in code, he managed to crack it with the “.hidden” method.

Code Example:

This code will set the Hidden flag (same as you can through the UI) against the document library. The above iterates through a List of strings which contains the fields i want to hide.

 1 SPContentType docCT = docLib.ContentTypes[0];
 2 
 3 foreach (string fieldDispName in fieldsToHide)
 4 { 
 5    SPField field = docCT.Fields[fieldDispName]; 
 6    docCT.FieldLinks[field.Id].Hidden = true; 
 7 }
 8 
 9 docCT.Update();
10

NB: Please not that this works because we are using our own content type attached to the library.

Categories: Work Tags: C#, MOSS 2007, SharePoint 2010, SPContentType, SPF 2010, WSS 3.0

Creating Test / Filler / Example text from Microsoft Word #SharePoint #SP2010 #in ‘#MSOffice

February 7, 2011 Giles Hamson 1 comment

I always forget the following link when I want some example text in my projects for Plain / Rich Text fields (in Lists and InfoPath Forms), Word Documents (Document Management etc.)

Microsoft Office Help Link: http://blogs.office.com/b/microsoft-word/archive/2009/05/12/quick-tip-filler-text.aspx

Quick Reference

Within Word type the following: