Feeds:
Posts
Comments

While working and extending a few macros recently, noticed i didn’t had access to database (same as in Project Online, well i could use Odata but again it cumbersome, so i went with my comfort zone VBA), and i needed to extract 15 project specific resources and thats where i started thinking should i do it manually, well easy enough if i only had 15 projects what if more than 100 and hence started writing macro, didn’t took longer may be 20 min and here is is

<code>

Sub GetAllRes()

‘Declare Object Variables for use

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

‘Set Excel Application, Workbook, Worksheet reference to be used
Set xlApp = New Excel.Application
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets.Add
xlSheet.Name = “AllRes”
Set xlRow = xlApp.ActiveCell

Dim allProj()

‘ Well i could have read this from a file as well, just got lazy as there were only handful projects😉
allProj = Array(“Prj1”, “Prj2”, “Prj3”, “Prj4”, “Prj5”, “Prj6”, “Prj7”, “Prj8”, “Prj9”)
Dim CurrArr
Dim Res As Resource
Dim Row As Integer
Row = 1

xlSheet.Rows.Cells(0, 0) = “ProjectName”
xlSheet.Rows.Cells(0, 1) = “ResourceName”
xlSheet.Rows.Cells(0, 2) = “Resource Type”

For Each CurrArr In allProj
PrjName = “<>\” + CurrArr
FileOpenEx Name:=PrjName, ReadOnly:=True
For Each Res In ActiveProject.Resources
‘MsgBox (Res.Name + “–” + CStr(Res.EnterpriseUniqueID))
xlSheet.Rows.Cells(Row, 1) = CurrArr
xlSheet.Rows.Cells(Row, 2) = Res.Name
If Res.EnterpriseUniqueID = -1 Then
xlSheet.Rows.Cells(Row, 2) = “Local”
Else
xlSheet.Rows.Cells(Row, 2) = “Enterprise”
End If
Next

Next

End Sub

</code>

Let me know what you think


Several Times I have seen this question coming up on how to read values of Enterprise Custom Fields, thought just inserting few lines here to aid out people looking for these, the below mentioned method will not only get you access to the Enterprise but out of box fields as well as local custom fields if any

<code>

Sub GetEntECFVal()

Dim tsks As Tasks
Dim tsk As Task

For Each tsk In ActiveProject.Tasks
MsgBox (tsk.Name + “—” + tsk.GetField(FieldNameToFieldConstant(“Field Name”)))
Next

End Sub

</code>


Once again back with a new post, recently got a requirement to develop a Project Status Report(PSR), as we all know, for on Premise environment this should be very easy, just write a bunch of SQL queries and joins and you should be good to go, however in this case the requirement was to get the report out of Project Online with bunch of custom fields and additional information from Project Site (Custom Lists)

Still not a big deal, you can easily get the information using OData except making joins is not as easy as it seems, so in this example you will notice quite a few things I have done as workaround and tried to manage and map within JQuery & JavaScript rather than just manipulating the OData

well I started working on it and then came the additional requirement can we make it mobile device compatible and the answer was yes why not, just that it takes time & effort J

Anyways started exploring options, and as we all know, JQuery & JavaScript is what you have to use
however which framework should we use for making the report mobile device compatible, one of my colleague recommended to use Bootstrap J started working on it and below are the results

 

Desktop version

1- First Section

2 - Second Section

3 - Third Section

4- fourth section

5 - Fifth Section

6 - sixth section

7 - Seventh Section

Looks Pretty and they are real time too:)

Now let’s take a look at Ipad Version on how it looks on Ipad, though i could simulate it in browser but wanted to show real

IPad Version

1- Ipad

Notice i have Content focused mode enabled:) to maximize the report on PDP

2 - Ipad

4-Ipad

5- Ipad

6- Ipad

7- Ipad

Okay so that was IPad version, let’s see how it looks on iPhone

IPhone Version

1- Iphone         2- Iphone 3- Iphone

Hopefully this should give you a good preview of mobile device compatible report, in my next post i will post the code and explain on how was this developed

 

 

 

 

 

 

 

 

 

 

 


Assumption:

  • You are familiar with Azure working concepts
  • You have an active Azure account
  • You are familiar with Project Server Architecture specifically around remote event receivers

Step 1: Create A visual studio project >> Select Azure Cloud Service type project

Note: if you don’t have Azure Cloud Service template available >> install Azure SDK from Web Platform Installer

1 - CreateCloudVSProject

Step 2: Select the WCF web service role for this project

2 - AddWCFServiceWebRole

As soon as your project is created you would notice cloud explorer being available, we will use this later to publish / Deploy our WCF service to Azure, notice the Visual Studio project structure in solution explorer, further I renamed the WCF service and WebService roles you will see those later in the project

3 - VSProjectSturcture_CloudExplorer

4 - VSProjectStructure

Step 3: Now that the Project has been added, it’s time to add assembly references, as you know since azure will not have any runtimes available it’s necessary to add below mentioned DLL’s you can find them on any Project Server installation servers

Note: these DLL’s should be 15.0.X.X version, add all the ones highlighted below, ignore the Microsoft.Office.Project.Shared

5 - AddDLLReferences

Step 4: Implement the IprojectEventRecieverRemote interface, as shown below add the required using statements

6 - ImplementIProjectEventInterface

Step 5: Once the references has been added implement all the methods for the references

Step 6: Now that all the methods are available I will use one of the methods i.e. OnCreatingRemote, and add my own business logic
for e.g. I have added my little logic to cancel a project if it contains a specific keyword

7 - AddLogicToInterface

Step 7: That’s it compile the project and procced with packaging >> right click solution explorer >> Package

8 - PackagenPublish

For package choose the options as below, you may want to test it locally first but I was confident😉

9 - PackageOptions

Now once the packaging is completed Visual studio will open up the packages in windows explorer for you to copy

10 - PackageCreated

At this time you have an option, either you can take the packages and upload & deploy them by logging onto your azure portal, or you can use Visual studio to publish / deploy right from within, just for convenience I would use VS to publish my Service

 

Below is a snapshot from my azure portal, as you can see I have already deployed it once hence it’s giving me options to update it, however if you are deploying it for first time after creating your cloud service it will ask you to upload the packages and then deploy it

10_1 - UploadtoAzure

Step 8: Right click solution explore >> Publish >> it should ask for your Azure account logon credentials, typically I would already be signed in my cloud explorer to see my other azure resources

11 - PublishtoAzure

12 - PublishCommonSettings

Check and set your common settings

13 - PublishAzureSummary

Verify Summary

Step 9: Publish >> Your publishing should start you can check the publishing process in Azure activity log as below

14 - AzureDeployment1

It takes around 4-5 minutes to publish and deploy the service remember it does a lot of backend processing while it’s doing it for you< hence please be patient and wait until the process gets completed, even though after a while you would see the WebApp URL however wait until the whole process completes

As you can see below the process has completed  now

17 - AzureDeployment4

Step 10:  Go ahead and follow the link >> it should launch IE and you should be able to see your service hosted

18 - ExploreService

Step 11:  Verify and make sure you are able to browse your service and definition

19 - AzureServiceWSDL1

20 - AzureServiceWSDL2

Now since I have verified my service is working as expected, I would go ahead and register this with Project Online or could be Project Server 2013 On Premise, for registering I used a windows form to execute the code as shown below

21 - RegisterEventHandler

This would register the event handler to online tenant, not that I use a fixed GUID instead of registering just so that I can keep track of my event handlers J

private SecureString getSecureString()
{
SecureString sec = new SecureString();
string password = “YourPasswordHere”;
password.ToCharArray().ToList().ForEach(c => sec.AppendChar(c));
sec.MakeReadOnly();
return sec;
}

private void btnRemoteEvent_Click(object sender, EventArgs e)
{
string userName = “UserName@Site.onmicrosoft.com”;
string OnlinePWA = “https://Site.sharepoint.com/sites/pwa/&#8221;;
using (ProjectContext PrjContext = new ProjectContext(OnlinePWA))//PWA Url
{
PrjContext.Credentials = new SharePointOnlineCredentials(userName, getSecureString());
PrjContext.EventHandlers.Add(new EventHandlerCreationInformation()
{
EndpointUrl = @”http://psrehdemo.cloudapp.net/RemoteServiceEventHandler.svc&#8221;,
Id = new Guid(“a1aa68bc-cf64-46d3-80a8-6f69af712294”), //You can choose to create new //Guid, i just wanted to keep track of the guid so that i can later unregister it
Name = “ProjectOnCreate – Remote Event Handler”,
EventId = (int)PSEventID.ProjectCreating,
Order =1,
CancelOnError = true,
});
PrjContext.EventHandlers.Update();
PrjContext.ExecuteQuery();
}

Step 12: Time to test the event handler, log onto your Project Online tenant >> Goto project Center >> New >> Enterprise project >> provide the name with the secret keyword (“rogue”) for me
22 - CreateNewProject

If your event handler is working properly >> you should get an error while creating it
23 - ProjectCreationFailed

Time to check the queue and for sure I see project create having trouble Oh Oh

24 - ChekQueueForFailedProject

Quickly verifying the queue error message and for sure it’s the remote event handler:)

25 - QueueJobError

 


 

So in our last post we saw how to create a new PWA, in this post we will see step by step how to migrate 2013 to 2016, again fairly straight forward and most of the steps resembles what we use to do earlier except few things which you will notice is new in 2016 version

We will use a database attach upgrade mechanism for migration

 

Assumption:

  • All the Content DB & Project Server DB has been backed up & restored to the new DB server
  • If you have multiple Content DB you would want to merge the database prior to migration or do some cleanup else you can mount all content DB one by one, in my case I merged the content DB’s

12 - Merge WSS Content DB

For more details on merging content DB read here ( https://technet.microsoft.com/en-us/library/cc825328.aspx )

Step 1: Create a New Web Application

M1 - Create Web App

Step 2:  Mount the Content Database from your source

Mount –SPContentDatabase –Name <ContentDBName>

M2 - Mount Content DB to PWA Web App

Step 3:  Test your mounted content DB, key here is to look for Upgrade Blocking : Ture , in case there are any, you would want to fix them in your source system before proceeding

M3 - Test Mounted Content DB

Step 4: Migrate Project Server Database

Migrate-SPProjectDatabase –Name <PWADB Name> -SiteCollection

M4 - Error While Migrating PWA DB

I hit an error while trying to use this command apparently on TechNet the database name is spelled as –Name instead it should be –DataBaseName, as you can see from above figured it out using the Get-Help command

Migrate-SPProjectDatabase –DatabaseName -SiteCollection

M6 - Initiate Migration of PWA DB

Depending upon how much data is there in my case it was ~40GB and tool around 5-7 minutes to convert database

Step 5:  As you all know now the resource plan in 2016 is deprecated and rather replaced by function called “Resource Engagement” so you need to convert your existing resource plans to resource engagements

Migrate-SPProjectResourcePlans –URL <New PWA>

M8 - Migrate Resource Plan

 

Upon migration I opened up the newly provisioned PWA and as a first check I could see all my timesheet submissions due, that made me feel good that yes all the data is migrated J

M9 - PWA Created with Data

So I went in looking for other data too, and as you can see all my old data was migrated as it is

M10 - Check Project Center

 


So as everyone already know now with 2016 PowerShell is the way to go for all Project Server related deployment operations, below is the step by step guide to install & deploy Project Server 2016, assuming you all know how to deploy SharePoint 2016, refer to install details here (https://technet.microsoft.com/en-us/library/ff608031(v=office.16).aspx)

For the below installation I am using a single server VM in a Hyper-V environment

  • Hardware
    • 10GB RAM
    • 4 virtual proc assigned
    • 120 GB Disk space
  • Software
    • Windows Server 2012 Enterprise with latest SP
    • SQL Server 2014 SP1
    • SharePoint Server 2016 Beta2 (only because I wanted to upgrade to RC)
    • Domain Controller
    • Visual Studio 2015 Community edition (Optional)

 

  • Assumptions
    • You already have managed account set up
    • Project Server Application proxy has been setup
    • Managed Path has been setup

 

As you can see unlike 2013 now you cannot PWA (Project Web Access) creation menus are not available, hence we will use a combination of PowerShell & GUI interface to deploy the Project Server though everything performed via GUI can also be done with PowerShell however it’s just my personal preference to use a combination

0 - PWA Deploy CA Check

Step 1: Make sure you have Project Server License enabled, in my case I have already activate the project server license key, to get a key for Beta refer here

“When you create a Project Server site on SharePoint Server 2016 Release Candidate, you need a Project Server 180-day trial key. If you attempt to enable the Project site, the following error appears: “The farm does not have a product key for Project Server. You can add your key by using Enable-ProjectServerLicense.

Use the following 180-day trial key for Project Server:
Y2WC2-K7NFX-KWCVC-T4Q8P-4RG9W

1 - Check License

 

Step 2: Create a Web Application

In this case, I chose to use GUI to create a new Web App which will host the PWA site collection

2 - Create Web App

Step 4: Create a root site collection

3 - Create Site Collection

Error in my trial and error thing I had created a root site collection from the UI, however was again trying to do it from the PowerShell, you can use either to do so, in my case I deleted the one I created earlier using GUI and created new one with PowerShell

6 - Error Site Coll Already Exists

Step 5: to create PWA site collection use the command below

New-SPSite -ContentDatabase -OwnerAlias   -Name "Project Web App Demo" -Template "pwa#0"

Important is to use the required template else you will have to activate the PWA features later😉
7 - Deploy Site Coll PWA

Fairly straight forward, isn’t it ? you get your new PWA provisioned

8 - PWA Instance Created

Last thing though optional I wanted to get my permission mode changed to Project Server instead of SharePoint

9 - PS Security Missing

Set-SPProjectPermissionMode -Url -Mode ProjectServer

10 - Change PS Security

11 - PS Security Mode available


Recently had a requirement to update the title of the document with the same name as of document irrespective of what user sets, thought to do it via javascript but then given 2013 changed my mind to give it a try with ECMA script, it took an hour(most of it in finding the name of the document:( ) of troubleshooting but got it working:)

Could have done it in lesser steps may be only one query load, but left it in a raw state for others to read through and refine as required😉

in case someone want to go through some extra steps

UpdateListItemTitle


&lt;script type='text/javascript'&gt;

function getContext()

{

    try

    {

        var context = new SP.ClientContext.get_current(); 

        var list = context.get_web().get_lists().getByTitle('Documents');; 

        var camlQuery = new SP.CamlQuery();

        camlQuery.set_viewXml('');

        this.collListItem = list.getItems(camlQuery);

        context.load(collListItem);

        context.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));        

    }

    catch(exception)

    {

        alert(exception);

    }

}

function onQuerySucceeded(sender, args) {

    try

    {

        var listItemInfo = '';

        var listItemEnumerator = collListItem.getEnumerator();

        while (listItemEnumerator.moveNext()) {

            var oListItem = listItemEnumerator.get_current();

            alert(&quot;Updating item with ID--&gt;&quot;+oListItem.get_item('ID')+&quot; with Title--&gt;&quot;+oListItem.get_item('Title')+&quot; To new Title --&gt;&quot;+oListItem.get_item('FileLeafRef'));

            //if(oListItem.get_item('Title')==null){}

            updateListItem(oListItem.get_item('ID'), oListItem.get_item('FileLeafRef'));

        }

    }

    catch(ex)

    {alert(ex);}

}

function onQueryFailed(sender, args) {

    alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());

}

function updateListItem(ItemID, title) {

    var clientContext = new SP.ClientContext.get_current();

    var oList = clientContext.get_web().get_lists().getByTitle('Documents');

    this.oListItem = oList.getItemById(ItemID);

    oListItem.set_item('Title', title);

    oListItem.update();

    clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceededUpdate), Function.createDelegate(this, this.onQueryFailedUpdate));

}

function onQuerySucceededUpdate() {

    alert('Item updated!');

}

function onQueryFailedUpdate(sender, args) {

    alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());

}

_spBodyOnLoadFunctionNames.push(&quot;getContext&quot;);

&lt;/script&gt;
Follow

Get every new post delivered to your Inbox.

Join 31 other followers