Feeds:
Posts
Comments

Archive for the ‘Programmability’ Category


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

Read Full Post »


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>

Read Full Post »


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

 

Read Full Post »


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;

Read Full Post »


So once again we are back with some interesting topic Resource Plan, it has been discussed in length at several places, blogs forums etc….and i am here with another thread on the same functionality / feature, though there are quite a few areas where resource plan can be improved and i am sure MS is working hard to prioritize things and getting them into the product but i still feel this is one area which has been ignored

So to start the discussion today if you have to do detail resource management, in my experience you need to know the MS Project tool extensively and all of it concepts to have proper resource management otherwise its possible or likely you are not going to get good results

Despite of loads of features being added everyday, even today PM’s tend to use all other applications like power point most favorite excel to do all kind of management why possibly the answer it they don’t need to learn excel or power point or visio to use it and this is the point of my today’s post which is usability, even though there are other functional areas which needs improvement in resource plan like calendars not being considered in resource plan which creates problem in calculation, not able to import resource plan into MS project, even though there are 3rd party tools or customizations and other things that will help you do it, however i am not going to discuss these today but will focus on the usability if resource plan, there are several inconsistencies in 2010 where in most of the places where project server uses JSGrid for resource plan there is no JSGrid perhaps an XML grid

So what are the problems, from my perspective UI has potential issues which creates problem in using the resource plan functionality, listed below

1. Adding Resources to Resource Plan

Why it has to be so hard, performing so many clicks just to  add a resource, in an OOB environment >> click 1 Build team >> select individual resources >> Add the resources to team >> do a save & close

Problems ::

  • Too many clicks

 Solution: Given that now with 2010 / 2013 there is a ribbon infrastructure available, it could have been as simple as adding a Add Resource button to the ribbon which provides search functionality(kind of auto complete or something like that) for resource name and all you do is keep typing name and selecting the resources and add them, something like as below

NewRibbonButtons

AddResource

ResourceAdded

2. Removing a resource from resource Plan

Same problems as with adding a resource plan, too many clicks, solution could have been as simple as selecting the resource from grid and on ribbon clicking a button remove resource and with a confirmation all should be done

DeleteResource

3. Searching Resource

Problem :: No easy way to search and add resource if there are quite a bit of resources in resource center, for me it was around 28k resources

  • Cannot perform Resource Match in resource plan
  • If there are too many resources e.g. ~30K resources no easy way to filter resources

Solution :: Including solution 1 where we have a ribbon button to add resource, a modal dialog box can be prompted with having a functionality to search resource and add it something like below

AutoCompleteSearch

4. Replacing Resource From Resource Plan

What happens if initially you added a resource in resource plan and now needs to replace him with someone else, should this be so hard to delete all data for existing resource >> follow all the steps to add new resource and re enter all same data 😮

Easier said than done

Solution :: Again my all solution are leveraging the ribbon infrastructure, so select a resource from grid, click ribbon and select new resource and simply hit replace, as in below snapshot

ReplaceResource

5. Modifying Resource Assignment level attributes

This may or may not be critical/applicable depending upon how you want it to but in our case it was

Now if you have to modify only few attributes of resource assignment you click on the edit custom fields, you are presented with  all custom fields not even relevant, why would you want the end user to see all attributes and additional things which end user might not even need to see, so wouldn’t that be overwhelming for end user

Solution :: simple solution allow users to edit custom field with restriction using a ribbon button, as below

UpdateResourceAttributes

ResourcePlanCustomAtrributeUpdated

 6. Even though you select resource Plan from the utilization drop down it changes back to project plan upon navigating between pages initially while building team and an overlook results in data problems specially while performing portfolio analysis

Again this may or may not be applicable to everyone

Solution : We defaulted it to Resource plan for everyone and hid the ribbon component

ResourcePlanCustomAtrributeUpdated

 

 

There is more to it, this was just a start which i wanted to focus on this post, stay tuned for more

Read Full Post »


With reference to our earlier post  Date Validation on PDP and similar kind of example in the post secure PDP pages, quite a few people have been trying to use the logic for disabling the controls on PDP pages now, while you can disable all the fields on the PDP, however it doesn’t do it for multi line text fields, the reason for it is while it initially disables it, but being a context sensistive control while you click in the box it loads the rich text ribbon and enables the text box for editing, however this can be overcome by disabling the entire div tag instead of just the text box, however also be aware the OOB description text box behaves slightly different than other textarea controls, to disable the text area control use the procedure as below


var arrtxtArea = document.getElementsByTagName('textarea');
for (var k = 0; k < arrtxtArea.length; k++)
{
if(arrtxtArea[k].title == 'Description')
{
arrtxtArea[k].disabled='disabled';
arrtxtArea[k].readonly='readonly';
}
else
{
<span style="color: #003366;">var divID=arrtxtArea[k].id+'_div';</span>
<span style="color: #003366;"> var txtAreaDiv =document.getElementById(divID);</span>
if(txtAreaDiv!=null)
{
txtAreaDiv.disabled = true;
}
}

As you notice in this case i am disabling the entire div tag for the text area and that works

RichTextBoxDisabled

Read Full Post »


I have had quite a few requests from users, wherein they wanted to bulk add Risks/Issues onto project site from other systems, while working on it there are several ways to do it, i chose one to use the powershell as it seemed easiest to implement and use, even within powershell you can either use web service to do the same or you can use the SharePoint object model to add/update, i have chosen object model as that suits my need but for reference i am also posting the web service method in case you want to use it

Generally have seen users having problem with updating DateTime type and person type columns, hence emphasizing on that in notes below

As usual before we make it to the actual powershell, few snapshots

Note::

Make Sure your URL is properly formatted i.e. without any special characters

ExcelPowershellUpdateURLFormat

Make sure Date Format is like yyyy-mm-dd hh:mm:ss

I used a text type column and formatted the date time type as above

ExcelPowershellUpdateDateFormat

ExcelPowershellUpdateSharepointUpdate

If your excel is properly formatted, launch SharePoint powershell prompt, or if its native powershell add sharepoint snapin


<span style="color: #000080;"><strong>#Adding list item using Object Model</strong> </span>

$xl = New-Object -COM "Excel.Application"
#$xl.Visible = $true
$wb = $xl.Workbooks.Open("C:\Users\Administrator\Desktop\IssuesandRisks.xls")
$ws = $wb.Sheets.Item(1)
for ($i = 2; $i -le 5; $i++) #Change row no highlighted here
{
$ProjectHref = $ws.Cells.Item($i, 2).value2 #Adjust Column no here
$SPWeb = Get-SPWeb $ProjectHref
$List = $SPWeb.Lists["Risks"]
$Item = $List.Items.add()
$Item["Title"] = $ws.Cells.Item($i, 4).value2
$Item["Status"] = $ws.Cells.Item($i, 7).value2
$Item["Category"] = $ws.Cells.Item($i, 8).value2
$Item["Description"] = $ws.Cells.Item($i, 10).value2
$Item["Due Date"] = $ws.Cells.Item($i, 9).value2
[Microsoft.SharePoint.SPUser]$spuser = $SPWeb.EnsureUser($ws.Cells.Item($i, 5).value2)
$Item["Assigned To"] =$spuser
[Microsoft.SharePoint.SPUser]$spuser = $SPWeb.EnsureUser($ws.Cells.Item($i, 6).value2)
$Item["Owner"] =$spuser
$item.Update()
Write-Host "Updated row-" $i
$SPWeb.Dispose()
}
$wb.Close()
$xl.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
Write-Host "Operation Complete"

<strong><span style="color: #000080;">#Adding list item using Web Service</span></strong>

$xl = New-Object -COM "Excel.Application"
#$xl.Visible = $true
$wb = $xl.Workbooks.Open("C:\Users\Administrator\Desktop\IssuesandRisks.xls")
$ws = $wb.Sheets.Item(1)
for ($i = 2; $i -le 5; $i++) #Change row no highlighted here
{
$ProjectHref = $ws.Cells.Item($i, 2).value2 #Adjust Column no here
$uri = "$ProjectHref/_vti_bin/lists.asmx?wsdl"
$listname = "Risks"
$SPService = New-WebServiceProxy -uri $uri -NameSpace SpWs -UseDefaultCredential
$ListInfo = $SPservice.GetListandView($listname,"")
$ListID  = $ListInfo.List.Name
$ViewID  = $ListInfo.View.Name
# build the XML 'batch' of entries that make up the Item
$doc = new-object "System.Xml.XmlDocument"
$batch = $doc.CreateElement("Batch")
$batch.SetAttribute("OnError", "Continue");
$batch.SetAttribute("ListVersion", "1");
$batch.SetAttribute("ViewName", $ViewID);

$batch.InnerXml = "<Method ID='1' Cmd='New'>" +
""+$ws.Cells.Item($i, 4).value2+"" +
""+$ws.Cells.Item($i, 7).value2+"" +
""+$ws.Cells.Item($i, 8).value2+"" +
""+$ws.Cells.Item($i, 10).value2+"" + ""
$response = $SPservice.UpdateListItems($ListID, $batch)

}
$wb.Close()
$xl.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)

Read Full Post »

Older Posts »