tirsdag den 30. september 2014

Sharepoint Map Network Drive

For a long time i have looked for a simple way to retrive and update documents stored in SharePoint. Today i found a very cool way to download all SharePoint documents to a network drive. This method works at least in SharePoint/Project Server 2007, 2010, 2013.

To map a SharePoint document library to a network drive simply navigate to the document library and copy the URL.
(In the below example i am referencing a library called "Documents")

Do not copy the "/Forms/Allitems.aspx" part.

Now in "File Explorer" leftclick on your "Computer" and select "Map network drive...".

Paste the URL to the document library and click "Finish", in my case the URL is: "https://365projectum.sharepoint.com/apps_products_and_concepts/Documents/".
And now the document library is mapped to your local computer.

lørdag den 27. september 2014

Project Site - Project/Custom fields displayed on Project Site or Project Workspace

If you want to display project fields or project custom fields on your project site, like in the image below, there are only a limited number of possibilities. One of them is of cause to use an App, but that limits the layout to the App.


In this post, I will walk you through how to insert a script that loads and displays any project field you like. The Script is really easy to configure.
I should mention that I found part of this solution at Poul's Project blog (link) but his solution was very difficult to configure so I rewrote the whole code.

First thing you need is to upload your JQuery script file to the PWA site. if you already have a JQuery file uploaded it will properly do fine.

Download the JQuery file here http://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js and save it on your desktop.

Go to the Site Settings of the PWA and click on Master Pages.

Upload the JQuery file here and publish it.
View properties of the file and copy the JQuery file link.
Now go to your Project Site or Project Site template (if you want it on all sites) and insert a Content Editor Webpart.
Edit source of the webpart.
Paste the following script into the webpart.

 <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>  
 <link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.0/css/jquery.dataTables.css">  
 <script type="text/javascript" charset="utf8" src="//cdn.datatables.net/1.10.0/js/jquery.dataTables.js"></script>  
 <script type="text/javascript" src="/_layouts/15/sp.debug.js"></script>  
 <script type="text/javascript" src="/_layouts/15/sp.js"></script>  
 <div id="pData"></div>  
 <script type="text/javascript">  
   var ProjectFields =  
         [  
           //Project fields  
           // Header      Name  
           ['Project Name', 'ProjectName'],  
           ['Description', 'ProjectDescription'],  
           ['Project Owner', 'ProjectOwnerName'],  
           ['% Complete', 'ProjectPercentCompleted'],  
           ['Project Work', 'ProjectWork'],  
           //['Project Cost', 'ProjectCost'],  
           ['Project Health', 'ProjectHealth']  
         ];  
   var ProjectUID;  
  ExecuteOrDelayUntilScriptLoaded(getProjectUIDProperty, "sp.js");  
  function getProjectUIDProperty()  
  {  
    $('#pData').empty();  
    $('#pData').css('display', 'table');  
    $('#pData').css('width', '100%');  
    var row = $('<div />');  
    row.css('display', 'table-row');  
    var row_below = $('<div />');  
    row_below.css('display', 'table-row');  
    ProjectFields.forEach(function (field, i)  
    {  
      var cellContent = $('<div />');  
      cellContent.css('margin', '2px');  
      cellContent.css('border-bottom', '0px solid');  
      //cellContent.css('max-width', '400px');  
      cellContent.css('font-weight', 'bold');  
      cellContent.text(field[0]);  
      var cell = $('<div />');  
      cell.css('text-align', 'center');  
      cell.css('display', 'table-cell');  
      cell.css('vertical-align', 'middle');  
      cell.append(cellContent)  
      row.append(cell);  
      var cellContent_below = $('<div />');  
      cellContent_below.css('margin', '2px');  
      cellContent_below.css('border-bottom', '1px solid');  
      //cellContent.css('max-width', '400px');  
      cellContent_below.css('font-weight', 'bold');  
      var cell_below = $('<div />');  
      cell_below.css('text-align', 'center');  
      cell_below.css('display', 'table-cell');  
      cell_below.css('vertical-align', 'middle');  
      cell_below.append(cellContent_below)  
      row_below.append(cell_below);  
    });  
    $('#pData').append(row);  
    $('#pData').append(row_below);  
         var ctx = new SP.ClientContext.get_current();  
         this.web = ctx.get_web();  
         this.props = this.web.get_allProperties();  
         ctx.load(this.web);  
         ctx.load(this.props);  
         ctx.executeQueryAsync(Function.createDelegate(this, gotProperty), Function.createDelegate(this, failedGettingProperty));  
       }  
 function gotProperty() {  
          ProjectUID = this.props.get_item('MSPWAPROJUID');  
                      LoadProjectData();  
       }  
 function failedGettingProperty() {  
         alert('Error: ' + args.get_message());  
       }  
 function LoadProjectData()  
 {  
   var selectStr = '';  
   ProjectFields.forEach(function (field, i)  
   {  
     if (i == 0)  
     {  
       selectStr = field[1];  
     } else  
     {  
       selectStr = selectStr + ',' + field[1];  
     }  
   });  
   var data = $.ajax({  
     url: _spPageContextInfo.siteAbsoluteUrl + "/_api/ProjectData/Projects(guid'957d5fcd-5cbf-e111-9f1e-00155d022681')?"  
            + "$select=" + selectStr,  
     type: "GET",  
     dataType: "json",  
     headers: { Accept: "application/json;odata=verbose" }  
   });  
   data.done(function (data, textStatus, jqXHR)  
   {  
     try  
     {  
       var row = $('<div />');  
       row.css('display', 'table-row');  
       var row_below = $('<div />');  
       row_below.css('display', 'table-row');  
       ProjectFields.forEach(function (field, i)  
       {  
         var value = data.d[field[1]];  
         try{  
           var numValue = parseFloat(value).toFixed(2);  
           if (numValue != 'NaN')  
           {  
             value = numValue;  
           }  
         } catch (ex)  
         { }  
         var cellContent = $('<div />');  
         cellContent.css('margin', '2px');  
         cellContent.css('border', '0px solid');  
         //cellContent.css('max-width', '400px');  
         cellContent.css('height', '100%');  
         cellContent.text(value);  
         if (value == 'Red')  
         {  
           cellContent.css('background-color', 'red').css('color', 'white');  
         }  
         if (value == 'Yellow')  
         {  
           cellContent.css('background-color', 'orange').css('color', 'white');  
         }  
         if (value == 'Green')  
         {  
           cellContent.css('background-color', 'green').css('color', 'white');  
         }  
         var cell = $('<div />');  
         cell.css('text-align', 'center');  
         cell.css('display', 'table-cell');  
         cell.css('vertical-align', 'middle');  
         cell.append(cellContent)  
         row.append(cell);  
         var cellContent_below = $('<div />');  
         cellContent_below.css('margin', '2px');  
         cellContent_below.css('border-top', '1px solid');  
         //cellContent_below.css('max-width', '400px');  
         cellContent_below.css('height', '2px');  
         var cell_below = $('<div />');  
         cell_below.css('text-align', 'center');  
         cell_below.css('display', 'table-cell');  
         cell_below.append(cellContent_below)  
         row_below.append(cell_below);  
       });  
       $('#pData').append(row);  
       $('#pData').append(row_below);  
     } catch (ex)  
     {  
       alert(ex.get_message());  
     }  
   });  
   data.fail(function (jqXHR, textStatus, errorThrown)  
   {  
     alert("Error retrieving project data: " + jqXHR.responseText);  
   });  
 }  
 </script>  

Replace the JQuery script in the top of the file with the link to your local script.
 <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>  

Replace the Project Fields with the fields and headers you want to display.
var ProjectFields =  
         [  
           //Project fields  
           // Header      Name  
           ['Project Name', 'ProjectName'],  
           ['Description', 'ProjectDescription'],  
           ['Project Owner', 'ProjectOwnerName'],  
           ['% Complete', 'ProjectPercentCompleted'],  
           ['Project Work', 'ProjectWork'],  
           //['Project Cost', 'ProjectCost'],  
           ['Project Health', 'ProjectHealth']  
         ];  

Click OK and save the webpart page.

The fields will now be displayed on your Project Site.


Note: The Content Editor webpart sometimes converts the Javascript into HTML, so after clicking Save you cannot edit the script anymore. Remember to keep an offline copy of the script while modifying it.

fredag den 19. september 2014

Powershell procedures/cmdlets - MS Project client/Project Server

Powershell is becoming part of any IT-Administrator inventory. If you worked with Project Server 2013 or SharePoint 2013 you are properly avare of many of the many cmdlets available.


Project Server - Powershell cmdlets

For a list of the Project Server cmdlets see this Technet article: http://technet.microsoft.com/en-us/library/ee890097(v=office.15).aspx


MS Project - Powershell cmdlets

Untill recently I was not aware you can access the MS Project client though Powershell. There is actally quite a big API available as you are simply attaching into the VBA/VSTO API.
To connect to the MS Project client simply start up the Powershell ISE and use the following command.
 $Project = New-Object -ComObject msproject.application  

After you are connected the commands are very similar to the VBA counterpart, if you for example want to find the name of all the tasks.
 $Project.Visible = $True  
 $tsks = $Project.ActiveProject.Tasks  
 Foreach ($tsk in $tsks)  
 {  
   Write-Host $tsk.Name  
 }  

Or if you want to open and edit the enterprise calendar.
 $cal_guid = [GUID]"32E5163F-8F5C-472E-9497-F905B3A81E2D"  
 $Project.EditEnterpriseCalendar($cal_guid)  

Basically any VBA call can be migrated into Powershell, see this site for great VBA axamples.

General Powershell Problems

If you get the following error:
 file.ps1 cannot be loaded because the execution of scripts is disabled on this system  
Use the following command to allow the execution policy:
 set-executionpolicy unrestricted  
Note: the above command migth result in the following error:
 set-executionpolicy : Access to the registry key 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.PowerShell' is denied. To chang  
 e the execution policy for the default (LocalMachine) scope, start Windows PowerShell with the "Run as administrator" option. To change the execution p  
 olicy for the current user, run "Set-ExecutionPolicy -Scope CurrentUser".  
 At line:1 char:1  
 + set-executionpolicy unrestricted  
 + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  
   + CategoryInfo     : PermissionDenied: (:) [Set-ExecutionPolicy], UnauthorizedAccessException  
   + FullyQualifiedErrorId : System.UnauthorizedAccessException,Microsoft.PowerShell.Commands.SetExecutionPolicyCommand  
In this case it can be resolved by adding you account to the permission in a regestry key.
- Click start and run: regedt32
- Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell
- Right-Click the key and select Permissions 
- Select, and add your account, 
- Grant it "Full Control" privileges


mandag den 8. september 2014

SharePoint 2013 CAML Designer

When integrating SharePoint with Project Server I very often need to create some CAML query to filter/order/select list items.
To write the CAML query by hand can be quite timeconsuming. And you almost certain need an application to design the query for you.

There is a lot of applications out there to do this, but in my oppinion the best application by far is CAML Designer 2013.

Simply start the application and type in the site URL where the list is located.


Click the connect button and start designing your CAML.

Super easy!!

The application also works for SharePoint Online and Project Online.

The application can be downloaded from the biwug.be website, simply go to http://www.biwug.be/resources and download the click-once offline package.

Other applications that have the same or similar funktionality:
http://spcamleditor.codeplex.com/
http://spcamlqueryhelper.codeplex.com/




tirsdag den 2. september 2014

Project Server 2010 - PSI - Calendar Exceptions (AddCalendarExceptionsRow, RemoveCalendarExceptionsRow)

In the recent week we were creating an integration with vacation information from an external system into Project Server (2010).
We wanted to replicate all the calendar exceptions from the external system into Project Server.

This task looked quite simple until we got started using the PSI interface. We encountered a couple of problems and strange documentation. In our scenario we could not update the Project Server to the latest service pack so i do not know if these errors already are fixed.

In the following I will demonstrate how to delete all calendar exceptions on a resource and add only one new exception. This program was only created to outline how the PSI interface worked with calendar exceptions (creating one random exception does not make a lot of sense).




//The resource uid on the resource we want to change.
Guid resUID = new Guid("10b4d15c-4b62-417b-9b4d-924000c14cac");

WS_Resources.Resource wsresource = new WS_Resources.Resource();
wsresource.Url = "http://MyPWA/PWA/" + "/_vti_bin/psi/resource.asmx";
wsresource.Credentials = new System.Net.NetworkCredential("Administrator", "pass@word1", "CONTOSO");
WS_Resources.ResourceDataSet resDS = wsresource.ReadResources(""/*no filter*/, false);

WS_Resources.ResourceDataSet.CalendarExceptionsRow[] calExceptions = null;

//Not needed, only part of test
foreach (WS_Resources.ResourceDataSet.ResourcesRow rrow in resDS.Resources)
{
    if (rrow.RES_UID == resUID)
    {
        //Find exceptions so we can call RemoveCalendarExceptionsRow on each row
        calExceptions = rrow.GetCalendarExceptionsRows();
        break;
    }
}
foreach (WS_Resources.ResourceDataSet.CalendarExceptionsRow row in calExceptions)
{
    if (row.RES_UID == resUID)
    {
        //You would think this will do the trick, however this actually caused
        // an Unhandled Comunication Error when we called UpdateResources().
        //resDS.CalendarExceptions.RemoveCalendarExceptionsRow(row);
        //row.Delete();
        break;
    }

}
           
foreach (WS_Resources.ResourceDataSet.CalendarExceptionsRow calexrow in resDS.CalendarExceptions)
{
    if (calexrow.RES_UID == resUID)
    {
        //This is what works
        calexrow.Delete();
    }
}

WS_Resources.ResourceDataSet.CalendarExceptionsRow newCE = resDS.CalendarExceptions.NewCalendarExceptionsRow();
newCE.RES_UID = resUID;
newCE.Start = new DateTime(2013, 08, 12);
newCE.Finish = new DateTime(2013, 08, 16);
newCE.Name = "Vacation";
newCE.RecurrenceFrequency = 1;
newCE.RecurrenceType = (int)PSLibrary.CalendarConstants.CalendarRecurrenceType.Daily;
resDS.CalendarExceptions.AddCalendarExceptionsRow(newCE);
try
{
    wsresource.CheckOutResources(new Guid[] { resUID });
    wsresource.UpdateResources(resDS, false, false);
    wsresource.CheckInResources(new Guid[] { resUID }, false);
}
catch (Exception Ex) { wsresource.CheckInResources(new Guid[] { resUID }, true); throw Ex; }
finally {  }

Project Server Read Only Custom Fields

There are multiple ways to create read only custom fields in Project Server 2007/2010/2013. The most common way is through Javascript and a Content Editor Web part
(see: http://technicaltrix.blogspot.dk/2014/10/project-server-read-only-custom-fields.html).

Another more simple way is to use the build in custom fields in Project Server. The downside in this solution is that it is not as flexible as the javascript solution, however it can easily solve the most common scenarios.

Read only custom field - through standard functionality

Assume we have a custom field "Project SAP Number", this field is maintained by the PMO and we do not want the project managers to change it.
We would then create a new calculated custom field.
Go to the PWA and navigate to Server Settings->Enterprise Custom Fields and Lookup Tables.
Click on New Field.
Name the new field "Project SAP Number (Read Only)" and select "Project" in Entity, "Text" in Type and "Formula" in Custom Attributes (see below).
In the Enter formula type the name of the custom field you want to be read only, surrounded by brackets. In our case it's [Project SAP Number].
Click Save.

Now navigate to the PDP where you want the read only field to be displayed. Edit the PDP and modify the Project Fields web part.
Add the "Project SAP Number (Read Only)" field.
Click OK, OK and Stoop Editing.
The field is now read only on the selected PDP.

Note: If you like you can have a PDP only viewable for the PMO with the actual "Project SAP Number" field, this way the PMO can view and edit the field and project managers can only view the field.
The trick is simply to limit the SharePoint permissions so only the PMO can see this PDP.




mandag den 1. september 2014

Project Server Minimize/Hide Quick Launch

On a standard Project Server or SharePoint web page there is a quick launch in the left side of the screen. it is not always desirable to have view this side bar to the user and sometimes you just want to hide it and let the user restore it when he needs it.

Remove Quick Launch
If you just want to remove the quick launch on a SharePoint web page you can add the following script in a content editor on the webpage.

<style type="text/css">          
#s4-leftpanel
{
   display:none
}
.s4-ca

{
   margin-left:0px
}

</style>

But if you want to let the user be able to view it on command you can do it by adding Javascript to the content editor webpart.

Hide Quick Launch
You