EPM Advice

Quick & dirty way to bulk delete project sites

2018-05-31T23:15:00.001-07:00

I had a requirement to bulk delete 2000 project sites. This used to be much easier in good old PSI days. Project Server CSOM couldn't do it and PSI support has been removed from Project Online & Project Server 2016.

To solve this problem, I wrote a script that basically simulates going to "Connected SharePoint Sites" page and click "Delete Site" button for each Project :) This is tested on Project Server 2016 only.

Here are the steps to apply this script.

1- In Chrome, go to PWA Settings -> Connected SharePoint Sites
2- Press F12 to get in developer mode
3- In Console window, copy paste the script provided at the end of this article and press Enter
4- Type DeleteSites() and press Enter
5- Watch the magic in Console :)

The JavaScript code is as following

Publish all projects in Project Online using PowerShell

2017-06-27T22:15:00.003-07:00

I was looking for a PowerShell script to publish all projects in a Project Online instance but surprisingly (or due to lack of correct google terms) couldn’t find one.

So I ended up writing one myself and uploading it on TechNet for rest of the world. You can directly download the script from this link

https://gallery.technet.microsoft.com/Publish-All-Projects-in-ad8ee80e/file/174745/1/Publish_All_Projects_ProjectOnline.ps1

Best practices for writing JavaScript for Project Online – Part 5

2017-06-18T22:34:00.000-07:00

Write values to PDP input fields.

If you have followed my previous blog, you’d know how to tap an input field value on a PDP. Once you get hold of it, you can perform many operations like hide it, lock it, move it etc. using simple JavaScript operations.

In one of my apps, I was a bit surprised to learn that setting a field value programmatically e.g. (‘#YXZ’).val(‘123’) wasn’t sufficient for a PDP to detect that an input value has changed and it should save the value back to project when the save button is pressed.

So to simulate the experience as if an end user has typed the value. I had to trigger the JavaScript change event. This then successfully forced PDP infrastructure to save the value back to project.
TypeScript code to write values back to PDP is following.

You can also download the code from
https://gist.github.com/hammadbinarif/6a3312528040f717a510c0a3d0f55df0

Best practices for writing JavaScript for Project Online – Part 4

2017-06-18T22:25:00.001-07:00

Read input field values on a PDP

Sometimes, in our script we need to know the values of custom fields to render our own output. Case in point, I once had to draw cool d3.js based graphics representing project status based custom field values. See the sample output below

Now I could have read the values from Project Odata endpoint /_api/ProjectData/Projects but the requirement was to update the graphics as soon as user changed those values. I’ll probably share some other key aspects of this script (rendering graphics and updating it when user changes it) in a different future blog. The focus of this blog is the piece of code that I wrote to read input field values.

The input field values are displayed as editable input boxes if a project is in edit mode. As shown in the screenshot above.

In non-edit mode, the fields are displayed as simple text values inside a DIV tag which in turn resides inside a TD tag. However, even in edit mode, there are cases where fields would be displayed as simple text, e.g. when a field is a calculated field or it is locked in the current workflow stage etc. see output of non-edit mode below.

The important points about the code that I am going to share are following

The code is in TypeScript but could easily be transformed to JavaScript. I highly recommend using TypeScript for any medium and large sized scripting apps.
It depends on JQuery
If in future, Microsoft changes the HTML Dom structure of a PDP page, the code will likely break and will require an update.

Without further ado, following is the code to read Number, Date & Text values from PDP in both edit and non-edit mode. This code can be downloaded from

https://gist.github.com/hammadbinarif/822e8eae649851822d1abd9d6a7af4e3

In my next blog, I’ll talk about writing values back to PDP fields. Fun trivia: It is not as simple as changing the values of the input fields 😊

Best practices for writing JavaScript for Project Online – Part 3

2017-06-06T23:15:00.002-07:00

Create dummy console object to avoid script error in Internet Explorer

One common issue I face with Internet Explorer version 11 and below is that If I my code uses console object for diagnostics, it blocks the execution of rest of the script.

Interestingly, if I open a debug console (by pressing F12 or right click -> Inspect element), the script executes just fine without any error message. After some google search it turned out that IE does not have a console object defined if a console window is not already open. See this stack overflow question for details

https://stackoverflow.com/questions/10183440/console-is-undefined-error-in-ie9

To avoid this error simply add these lines at the start of your JavaScript code. This will create a dummy console object if the real console object doesn’t exist already.

Best practices for writing JavaScript for Project Online – Part 2

2017-05-28T18:46:00.001-07:00

Checking the Project details page edit mode

The tip is about changing script behaviour based on the fact that project is editable or not.

I recently created a custom editable grid for user to enter project financial information. However, I wanted the grid to behave like regular PDP input fields which does not allow editing if the project is not in Edit mode. See the output of my grid in Edit and non-edit mode.

When PDP is in edit mode

When PDP is not in edit mode

On every PDP, project online PDP infrastructure injects a global object named EditState which exposes some useful information about the current edit state of the PDP. One of its property EditState.Editing is set to true when the page is in edit state. See the following example code.

In the next part of this series, I’ll talk about how we can avoid a common Internet Explorer error

Best practices for writing JavaScript for Project Online – Part 1

2017-05-23T00:19:00.003-07:00

Checking the Web Part page design mode

In this series of blogs, I’ll share some the practices I use to write better JavaScript to customise Project Online screens.

The first tip is about detecting the web part page edit mode. Frequently, we write scripts to manipulate UI of the page and if the user is already editing the page, our script can cause undesired behaviour.

We could either execute a different set of functions or at minimum, just not execute the script in design mode to keep the user experience clean.

Its relatively simply to detect the WebPart page design mode. Just add the following code to the startup your script.

In the next part of this series, I’ll talk about how we can detect if a Project Details Page is in Edit mode.

Fixing SharePoint designer 2013 workflow that won't switch to "Text-based designer" view

2015-09-29T23:33:00.003-07:00

I recently wrote a big (200+ lines) List workflow in a SharePoint designer 2013. Those of you who have done it know that it becomes hard to maintain the workflow of this size as SPD is not really designed to author large workflows.

After spending few days, it become common for workflow designer to display validation errors every now and then which happened because SPD kept mysteriously deleting the variable references from the workflow actions.

Finally, i reached the stage where my workflow will not open in text-based designer mode, no matter what i do. It always opened in Visual designer mode with empty visio page.

Exporting the workflow as vsdx file and re-importing as new workflow didn't solve the problem.

After some experimentation, i was able to recover my workflow using the following procedure.

I exported the original workflow as vsdx file using export to visio function.
I deleted the workflow from SharePoint designer
In SharePoint designer, I created a new workflow “temp” and associated with my original SharePoint List.
New workflow had only 1 line which was logging to history list. I published the workflow
On my computer, I renamed the .vsdx file (step 1) to .zip and extracted all its contents.
In SharePoint designer, I went to navigation windows on left hand side, there i clicked All Files -> WfSvc1 (this is a known folder where SPD stores workflow files)
In that folder, there were many sub-folders, each having a guid as its name. each folder represents a workflow
Went inside each folder and picked the folder which has most recent last modified date for workflow.xaml file (this represents workflow created in step 1)
From the extracted folder (step 5) I copy pasted the file workflow.xaml to the SPD folder (step 8)
Restarted SharePoint designer
Opened the workflow designer page for the new workflow "temp" and it showed the restored workflow J
Renamed & published the workflow

As a lesson learned, i decided to break the workflow into smaller manageable workflow pieces, so that the damage remains minimum in case of future corruption.

How to clone a Project Detail Page

2015-08-02T05:01:00.000-07:00

Project Detail pages are essentially ASPX files. But copying these pages isn't as simple as copying a document. If you copy these pages like an ordinary document, you'd notice that copied web part page doesn't retain any web parts in it. This is because Web parts information is stored in the SharePoint Content database. Use the following procedure to copy web part pages.

1) Go to Site Actions -> Manage Content and Structure

2) Go to the Project Detail Pages library

3) Select the PDP you want to copy

4) Click Actions -> Copy

5) Copy to any other document library or folder

6) Navigate to that document library or folder

7) Use the drop-down for that page and choose Edit Properties

8) Rename the page, update any other information, and click Save

9) Select the page, then click Actions -> Move

10) Choose the Project detail Pages folder

11) Then open the page and you should have an exact copy under the new page name

Query for getting lookup table in Row Number order

2015-01-21T17:10:00.001-08:00

For your reporting needs, you may want to display values from a Lookup Table exactly in the order they are saved in PWA.

Within PWA, this setting is controlled using “Display order for lookup table” option on Edit Lookup Table Page.

However, this sort order is not available in Reporting Database. Following query uses Published (and reporting) schema to get this information. This is tested on Project Server 2013 on premises.

SELECT [MemberValue] , tmv .LT_VALUE_SORT_INDEX

FROM [MSPLT_Target Market_OlapView] tm

inner join pub .MSP_LOOKUP_TABLE_VALUES tmv

on tm.LookupMemberUID = tmv.LT_STRUCT_UID

order by LT_VALUE_SORT_INDEX

Replace “Target market” with your Lookup Table name in the above query.

Solution: Queue job "Project Workflow Check-in" stuck and then fail

2015-01-20T20:46:00.001-08:00

Short answer: Restart the “Service Bus Gateway” and “Service Bus Message Broker” Service on the server where Workflow Manager is installed. The detailed description is below.

This has happened to me and a colleague so i thought that the solution is worth sharing.

On Project Server 2013 On premises instance, all my projects were stuck in “Project Workflow Check-in” queue job and that job was failing after about 45 minutes. The queue error message was,

General

Queue:
- GeneralQueueJobFailed (26000) -WorkflowCheckinNotify.ProjectCheckinNotifyWorkflowMessage. Details: id='26000' name='GeneralQueueJobFailed' uid='b3f897eb-4695-e411-80cd-000d3ad00e15' JobUID='c7c8744b-4295-e411-80cd-000d3ad00e15' ComputerName='8fa7e1bd-09b4-4c64-b019-57c33d5c769c' GroupType='WorkflowCheckinNotify' MessageType='ProjectCheckinNotifyWorkflowMessage' MessageId='1' Stage='' CorrelationUID='1af0dc9c-9e05-a05e-c769-ca04319a2489'. For more details, check the ULS logs on machine 8fa7e1bd-09b4-4c64-b019-57c33d5c769c for entries with JobUID c7c8744b-4295-e411-80cd-000d3ad00e15.

Interestingly, i wasn’t even using Workflows for my EPT. A common solution for this problem is to publish a dummy workflow via SharePoint Designer 2013 on Project Server instance but i had done it already. The error mentioned above started to appear after few weeks of instance provisioning.

During the troubleshooting, i was able to publish workflows from SharePoint designer but i noticed that if i associate a workflow to an EPT, the projects started to fail even on project creation screen. This give me a hint about issue with Workflow Manager.

I found out that Workflow Manager related services named “Service Bus Gateway” and “Service Bus Message Broker” on the server were stopped and restarting the two services solved the issue for me.

Solution: Project returns to Project Site home page when you close it on PDP

2015-01-19T22:44:00.001-08:00

One of the victims of Project Server 2013 utilising SharePoint platform is the navigation (in)consistency.

Here is a description of one of the associated issue (or feature by design, who knows?)

If you

i) Open a Project from Project Centre (Projects Page),

ii) Move between PDPs

iii) Press Close button on Ribbon

You’ll return on Projects page.

But if you,

i) Open a Project from Project Centre (Projects Page),

ii) Move between PDPDs

iii) Click Project Site link

iv) Click Project Details link to go back to PDPs

v) Press Close button on Ribbon

You’ll return on Project Site Home Page.

The culprit in this case is “&ret=1” parameter in the URL which is added by Project details link. Unfortunately, this link is automatically added by a Project Server feature so we can’t change it in a Site Template.

The solution is to use a Swiss army knife of SharePoint. a.k.a JavaScript.

Just add the following code on all the PDPs via Content Editor web part

function SetReturnPage() {
PDP_ret =0 ; // This will take the user back to Project Centre
}
_spBodyOnLoadFunctionNames.push("SetReturnPage");

</script>

How much can you over allocate a resource in resource plan?

2015-01-18T21:44:00.001-08:00

I was giving Resource plan feature demo to a customer and out of curiosity, customer asked if I can assign 1,000 days of work to a resource on a single day. It turned out we couldn’t. When you press Save after assigning 1,000 days of work the system gave the following error.

“Your updates were not saved because of the following reasons: General error: Work value used is invalid. (Use this error information along with other errors to find out the cause of the problem.)”

The behaviour was same for generic resources. And this raised the inevitable question from customer. “How much can I over allocate a resource on a given day in the resource plan?”

I didn’t know the answer to this, but I promised to get back to customer and following information (mostly learnt by trial & error) is the result of that promise.

1) A resource can be allocated maximum 100 times of its Max. Units in a resource plan. So a resource with Max. Units of 100% (this is default) i.e. one full time person, can be assigned 100 days of work on a single day in resource plan.

2) If you increase Max. Units, the maximum possible work on a single day will also increase in same ratio. (E.g. setting current Max. Units of a resource to 1000% means you can allocate 1000 days of work to that resource)

3) Maximum Units allocated to a resource can be between 0 and 100,000,000

I also tested the maximum work that could be assigned to a resource on a single day in a Project Schedule. But I’ll leave the answer to the day when a customer asks me about it.

The usefulness of this knowledge? Hmmm… remember, curiosity can kill a cat!

3 minute guide to Department field usage

2014-11-23T22:15:00.001-08:00

Here is a very brief summary of how Department field is commonly used in Project Online, Project Server 2013 and Project Server 2010. You need to have some values in “Department” lookup table to get any advantage from it.

1- Filter custom fields in a Project.

Assign a department to a Project level Custom field, then create a new project and assign the same department value to Project Departments field. The custom field will only appear in your newly created Project.

2- Filter resource level custom fields for Resource.

Assign a department to a Resource level Custom field, then create a new resource and assign the same department value to Resource Departments field. The custom field will only appear for your newly created Resource

3- Show EPTs only to certain users.

Assign a department to an EPT. Assign same department value to a Resource in Resource Departments field. Only that resource will see the EPT as an available option to create the new project from.

Department fields are also utilised to filter OLAP cube data, Portfolio Analysis business drivers and driver prioritisations. If you really want to learn more about department field, check out this article by Treb Gatte.
http://blogs.technet.com/b/projectadministration/archive/2010/03/17/departmental-custom-fields.aspx

Did you read this blog in in less than 3 minutes? Let me know in comments and I’ll update the blog title :)

How to Manually edit Actual Cost in Project Professional

2014-09-25T22:32:00.001-07:00

By default, Actual Cost column is locked down in Project professional both at Task and assignment level. Actual cost is calculated from Actual Work and Fixed Cost values. This way, the relationship with Work done and cost accrued remain consistent.

Ground realities are many times more complex and actual costs involve Journaling, currency conversions and other adjustments. This is typically handled in ERP systems and all PM wants to do is to manually enter the actual cost value in Project so that she can track the remaining cost.

To enter the actual costs manually, you need to change the following setting in Project Professional.

1- Go to File- > Options –> Schedule.

2- Scroll to the bottom of the page till you reach the last section “Calculation options for this project:”

3- Uncheck the box “Actual costs are always calculated by Project”

4- This will change the setting for Current Project only. If you want this setting to be applied to all Projects, then change the drop down value in front of “Calculation options for this Project” to “All Projects”

Some points to consider in this approach are

1- Actual cost will only be editable from Project Professional. It will still not be editable from PWA

2- Project Manager will need to manage actual costs for each cost based assignment. Automatic work based calculation will be turned off for entire project.

Project Online – SSIS OData Replication – Lessons Learned

2014-08-18T01:19:00.001-07:00

I recently got a chance to work on an SSIS Package in Windows Azure environment which periodically retrieved the Project online data via OData connector and stored over an Azure SQL Database for SSRS based reporting.

Here are some of my takeaways from this exercise.

Learning Resources

The article to get you started is

MSDN Article: Replicate Project Online Reporting Data to a SQL Server database using the OData protocol

if you are also interested in learning about fetching SharePoint List data using similar mechanism, check this blog by Martin Laukkanen

http://nearbaseline.com/blog/2014/04/project-site-custom-list-reporting-using-ssis-odata-connector/

Speed

The SSIS job gets data alright once it is setup. But oData is inherently slow and it can easily take minutes for each project if you have many SharePoint lists or if you are trying to retrieve time phased data sets. Here are some of the suggestions to workaround this limitation.

1) Create a separate job for time phased data. And run this job only once in a week or month. Your time phased data will not be nightly refreshed but rest of the data update won’t suffer delays because of it.

2) Download the data in a cache Table. For reporting, use a different copy of same table. Update the copied table once your downloading job is complete. This way, your reports will show last downloaded data when the current downloading is in progress.

3) Check out this demonstration package on Microsoft site. I haven’t tried it but it is designed to sync only changes from Project Online.

Project Online: SSIS package for OData delta sync

Logging / Error Handling

SSIS has great logging features. Use them. You can log to text file, SQL Tables, event logs etc. You can also control the the minimum level being logged depending on the log medium and other variables.

SSIS also has intensive error handling options available for handling data level error (e.g. Custom field deleted or SharePoint list column deleted from a particular site). Review these options and use according to your requirements

Schema

Project Online oData schema is a bit different from traditional Project Server reporting database. The tables are more flattened which are easier to build queries on. However, if you have already built reports for on-premises, keep some time aside to re-write the queries according to new schema.

Limitations

There are still some limitations when you retrieve data from oData. e.g. You can’t retrieve more than 45 text fields (and you can’t choose which 45). As of writing these lines, Workflow Phase and Stage for the current project can’t be retrieved either.

The rapid pace at which Project Online oData support is being enhance by Microsoft, these limitations would hopefully be removed at some point in future.

Effective Project, Portfolio & Work Management, Using SharePoint

2014-08-15T21:55:00.001-07:00

SharePoint 2013 has some exciting new features around Project Management. I delivered a webinar on this topic last Wednesday. Check the full webinar recording below on YouTube.

Filter SSRS Report on Project Site

2014-06-30T23:10:00.001-07:00

[Populate SSRS Report Parameters via JavaScript]

Requirement

Display a Project specific data on a Project Site using an SSRS Report.

Solution

SSRS Report can be displayed on a SharePoint web part page using Report Viewer Control. The Challenge was to populate the parameter with Project Name so that only that Project specific data gets loaded in SSRS Report.

Our familiar hero, JavaScript came to the rescue again, though it took some help from Text Filter Web Part this time. Following are the steps

1) Prepare your SSRS report with a parameter which accepts Project Workspace URL as Parameter value and loads Project Data accordingly

2) Display this report using Report Viewer web part (available in SQL Server Reporting category) on your desired SharePoint web part page in Project Site.

3) Add a “Text Filter” web part (available in Filters category) to your page ,

4) Connect Text filter web part with Report viewer web part so that the value of text filter web part gets passed as a parameter

5) To test your report, type the Project Site URL (e.g. http://pwaurl/pwa/siteUrl ) in the Text Filter web part text box and see if the report loads correctly

6) Now to automatically populate Text Filter web part, you need to find text filter control ID (inspect element is your friend) and a bit of JavaScript magic.

7) The JavaScript code (provided at the end of this blog) injected via Hidden Content Editor web part will determine Project Site URL, Populate Text Filter control, and enforce the post back event for the Text Filter control so that Report gets refreshed according the the passed parameter value.

8) Once you are happy with the results, you can hide the Text Filter control by minimizing it and setting the Chrome Type to none.

Script

Note: Use inspect element to find your text filter control ID and parameter id for post back function (slightly different from control ID) and replace at places highlighted as bold

// find the control by ID

var ttnA = document.getElementById('ctl00_ctl40_g_b619b608_692c_47f9_ad64_39b27e905477_SPTextSlicerValueTextControl');

// Find the Project Site URL

var url = window.location.protocol + "//" + window.location.host + _spPageContextInfo.webServerRelativeUrl;

// If the Control is not already populated (to avoid infinite refresh loop)

        if (ttnA.value != url)
        {
                ttnA.value=url;

// DO Post back ID parameter should be updated (it is slightly different from Control ID)

__doPostBack('ctl00$ctl40$g_b619b608_692c_47f9_ad64_39b27e905477$SPTextSlicerValueTextControl','');
}

}

_spBodyOnLoadFunctionNames.push("submitSiteURL");

</script>

Make PDP Read-only for certain users or groups

2014-03-07T02:35:00.001-08:00

Requirement

There is a particular PDP (say, financial information) which should only be editable by PMO and rest (including PM) should see it in read-only mode.

Solution

There were some solutions available on other blogs (see alternative solutions section) but i wanted to have a simpler solution. Its relatively easy to disable the input fields using JavaScript. The interesting part was to do it for certain users (anyone other then PMO) only. I used SharePoint Web part Audience Targeting feature to execute the disabling script only for limited users. Here is the summary of the solution

1- On the PDP, add a content editor webpart, render a DIV element with special ID (i used “hiddendiv” as ID)

2- Set Audience of this webpart as a PMO (in my case PMO was a SharePoint Group, but you could use Active Directory group or even compile custom Audience). Using Audience property, “hiddendiv” will only render when someone from PMO will come to the PDP

3- Add another Content Editor Webpart on PDP, check if “hiddendiv” element is present on PDP. If so, don’t do any thing. Otherwise disable all input fields on PDP.

4- To protect fields from being changed in Project Professional, fields behaviour should be defined as “Behavior controlled by workflow”.

The solution has been tested in Project Server 2013 on premises but should also work in Project Online. Full Code for both Content Editor Web Parts is provided below

Script

CEWP 1:

<div id="hiddenDiv"> </div>

CEWP 2:

<strong class="ms-rteForeColor-9" style="text-decoration: underline">Note</strong><span class="ms-rteForeColor-9">: Only PMO Administrators can edit the fields on this page</span><script>
function hiddenDivPresent()
    {
        var hiddenDivID ="hiddenDiv";
        var hiddenDiv = document.getElementById(hiddenDivID);
        if (hiddenDiv != null)
            return true;
        return false;
    }
    function MakeReadOnly()
    {
        try {
            var allProjectFieldWebparts = document.getElementsByName("WPClass:ProjectFieldPart");
            for (var h = 0; h < allProjectFieldWebparts.length; h++)
            {
                var arrInput = allProjectFieldWebparts[h].getElementsByTagName('input');
                for (var i = 0; i < arrInput.length; i++)
                {
                    arrInput[i].disabled=true;
                }
                var arrbtn = allProjectFieldWebparts[h].getElementsByTagName('button');
                for (var j = 0; j < arrbtn.length; j++) {
                    arrbtn[j].disabled=true;
                }
                var arrtxtArea = document.getElementsByTagName('textarea');
                for (var k = 0; k < arrtxtArea.length; k++)
                {
                    if(arrtxtArea[k].title == 'Description')
                    {
                        arrtxtArea[k].disabled=true;
                    }
                    else
                    {
                        var divID=arrtxtArea[k].id+'_div';
                        var txtAreaDiv =document.getElementById(divID);
                        if(txtAreaDiv!=null)
                        {
                            txtAreaDiv.disabled=true;
                            txtAreaDiv.removeAttribute("contentEditable");
                        }
                    }
                }
            }
        }
catch(e){
alert('An error has occurred: '+e.message)
}
    }
    function runAfterEverythingElse(){
        SP.SOD.executeOrDelayUntilScriptLoaded(function () {

}
_spBodyOnLoadFunctionNames.push("runAfterEverythingElse");</script>

Alternative Solutions

1- InfoPath based solution by Andrew Lavinsky

2- Custom Webpart based solution by epmxperts

Solution: Spaces removed when SSRS Report exported to PDF

2014-01-17T05:45:00.001-08:00

Me and some of my workmates have come across this issue quite a few times so thought about sharing the solution with everyone.

Problem:

When a SQL Server Reporting Services 2005, 2008 or 2008 R2 report gets exported to PDF, the spaces of a Project Server Multiline text fields appear so condensed that it looks like a long line with out any spaces. The same report looks fine on screen and exports with proper spaces to Microsoft Word. This is apparently an SSRS bug. Follow the solution below to fix disappeared spaces problem.

Solution:

1) Add the following code to your report by right clicking outside report body and then selecting Report Properties. Click Code section to add the code

Public FUNCTION RemoveHtml(ByVal Text As String) AS String

IF Text IsNot Nothing AND Text <> "" Then

Dim objRegExp AS NEW System.Text.RegularExpressions.Regex("<(.|\n)+?>")

'Replace Zero Width Space Character
Text = Replace(Text,ChrW(8203), "")

Text = Replace(Text," ", " ")

Text = Replace(Text,"'", "'")

Text = Replace(Text,":", ":")

Text = Replace(Text,"&", "&")

Text = Replace(Text,""", """")

Text = Replace(Text,"<", "<")

Text = Replace(Text,">", ">")

Text = Replace(Text,"©", "")

Text = Replace(Text,"<ul>", "[[ul]]")

Text = Replace(Text,"<li>", "[[li]]")

Text = Replace(Text,"</ul>", "[[/ul]]")

Text = Replace(Text,"</li>", "[[/li]]")

Text = Replace(Text,"</p>", vbcrlf)

Text= objRegExp.Replace(Text, "")

Text = Replace(Text,"[[ul]]","<ul>")

Text = Replace(Text,"[[/ul]]","</ul>")

Text = Replace(Text,"[[li]]","<li>")

Text = Replace(Text,"[[/li]]","</li>")

Text = Replace (Text,vbcrlf,"<BR>")

Return Text

ELSE

Return ""

END IF

END Function

2) In your desired textbox in the report, Use the following expression to display your multiline field.
=Code.RemoveHTML(Fields!Project_Description.Value)
(Use your Multiline text field name instead of Project_Description)

3) In report designer, Select the text <<expr>> in your Text box and set MarkupType property as HTML.

Your report now should export fine to PDF. The removeHTML function strips all the HTML codes except for new line so that your text doesn’t appear all on the same line. As an added bonus, it also preserves bullet points related HTML tags as Project Server users typically like to enter their comments in bullet points.

Update (28/07/2014) : An old colleague Martin Laukkanen got deeper into this issue and found out that a Zero Width Space Character () does also cause the same issue. I have updated the VB Code to eliminate this little bugger too.

Project Server Security Scenarios – Part 3

2013-12-21T09:42:00.000-08:00

Part 2 of this blog can be read here

Scenario 5: PM to only add Generic resources to their Project Team whereas Resources Managers should be adding specific resources.

Bob the PM Should not directly be assigning actual people to its Project. Instead, he should be assigning Generic Resources. A resource manager can later add specific resources. Once specific resources are added, PM should be able to assign tasks to those specific resources as well.

Solution:

1) PMs by default have permission to assign any resource. To remove this permission, Go to Server Settings -> Manage Groups, click Project Managers, in selected Categories, click “My Organization” , under Resource Permissions, uncheck “Assign Resource” and Save.

2) Create a new Category named Generic Resources, in the resources section select “only the resources indicated” and put all generic resources to “Available resources” Area.

3) If a new Generic resource gets created in future, you would need to manually add that Resource to this category.

4) Unfortunately, there is no out of the box way to create a dynamic rule, which will automatically select all Generic Resources. If you frequently create generic resources, maintaining this category could be difficult. Your best bet would be to contact a Microsoft EPM Partner company to develop custom solution for you.

5) in the same resources section, select the item “They are members of a Project Team on a project owned by the User”

6) This checkbox would mean that PM will be able to assign tasks to non-generic resources, once they have been added by Resource Manager.

7) Check the following views in “Views – Add to Category” and click Save

a. Project -> Tasks Summary

b. Resource Center -> All Resources

c. Team Builder -> All Resources

8) Go to Server Settings -> Manage Groups -> Project Managers. Add your newly created category “Generic Resources” to Selected Categories section

9) While “Generic Resources” is in selected categories section and it is clicked. You should see Permissions section which is specific for this category. Give the following permission as per screenshot,

10) Clicking save will allow PM to assign only generic resources in their Projects.

11) To give resource managers the permission to assign any resource in the organization, Go to Manage Groups, click Resource Managers, in selected Categories, click “My Organization”, give the following permissions and click save

12) Resource Manager should be part of the Project team to access the Project. Alternatively, they can be given permissions to access any project in “My organization” category.

Project Server Security Scenarios – Part 2

2013-12-19T05:58:00.000-08:00

Part 1 of this blog can be read here

Scenario 3: Bob should access SharePoint Project Site for some Projects (But shouldn’t access Project details in PWA)

Bob is a user who’ll only be accessing SharePoint based Project sites to work on Risks, Issues and other SharePoint related stuff in Project Sites. He’ll only be using PWA to access Project Site and won’t be using any other Project Server Feature.

Solution 1:

The best way is to add Bob in that Project’s Project Team. If Bob doesn’t have any task assigned to him then he’ll get reader access to that project. If Bob requires Contribute access (so that he can add content to the Project Site), then there must be at least 1 task assigned to him on that Project.

However, under default configuration, Bob will also get Team Member permissions on that Project, which means he’ll be able to see Project Schedule, can create new task assignment etc.

Refer to Solution 2 below, if you want Bob to access Project Sites but you don’t want to allow him anything else to do in PWA.

Solution 2:

1) Create a new Category called “Project Site Access”, Add all Projects which you want to give Bob access to. Or Select “All current and future projects in Project Server Database” if you want Bob to access all Projects’ Project Sites.

2) In “Views – Add to Category” section, Assign at least 1 Project Center view to this Category, so that Bob can see the Project Name in Project Center.

3) Create a new group called “Project Site Users”, Add Bob to this group, and add your newly created category “Project Site Access” to Selected Categories section.

4) While “Project Site Access” is in selected categories section and it is clicked. You should see Permissions section which is specific for this category. Give the following permissions as per screenshot,

5) “Save Project to Project Server” permission is required for Bob to get read-write access. In the absence of this permission, he’ll get Read-only access to the Project Site.

6) “View Project Summary in Project Center” is required for Bob to see the Project in Project Center and he can then click “Project Site” button to access Project Site. In absence of this check box, Bob will need to know the direct URL of the Project Site.

7) “View Project Site” permission is required for read-only access to Project Site. In my tests, giving “Save Project to Project Server” permission without giving “View Project Site” permission was sufficient to give read-write access to Project Site.

Scenario 4: Bob should access Projects with specific value of a Custom Field.

Bob need to access all Projects where a Custom field has been set to specific value. (E.g. Where Field “Business Unit” is set to “Infrastructure” OR projects where one of the selected “Strategic Goal” is “Organisational excellence”

Solution:

1) Create a new category named “Infrastructure Business Unit Projects” or other name which is appropriate for your scenario.

2) In the Projects Section select “Only the projects indicated” and then add all projects which fulfils your criteria to “Select Projects” side.

3) If a new Project gets created in future which fulfils your criteria, you would need to manually add that Project to this category.

4) Unfortunately, there is no out of the box way to create a dynamic rule, which will automatically select Projects for you as per your custom field value criteria. If you have too many such categories, maintaining them could be very difficult. Your best bet would be to contact a Microsoft EPM Partner company to develop custom solution for you.

5) Create a new group called “Business Unit Managers” (or other appropriate name), Add Bob to this group, and add your newly created category “Infrastructure Business Unit Projects” to Selected Categories section.

6) While “Infrastructure Business Unit Projects” is in selected categories section and it is clicked. You should see Permissions section which is specific for this category. If you want to give Bob the same level of access as of Project Manager then select “Project Manager” in the “Set Permissions with template” Drop down and click apply. You can also give permissions of Admin, team member or any other pre-defined access set.

In next part of this blog, I'll talk about the scenario where a PM should only be assigning Generic resources to his Project Plan and actual resources should be assigned by Resource Manager.

Project Server Security Scenarios – Part 1

2013-12-17T05:58:00.000-08:00

In this blog series, I’ll talk about implementing some specific security requirements in Microsoft Project Server 2010 / 2013. I’ll assume that you already have some basic idea about Groups, Categories & Permissions but if that’s not the case, you can learn about Project Server 2010 Security Model (also applicable on Project Server 2013) through this excellent article by Ben Howard
http://www.applepark.co.uk/project-server-security-explained/

Project Server 2013 also offers a new permission mode known as SharePoint permission mode. As the name suggests, it is managed via SharePoint 2013 permissions model. It is a simplified model which can quickly be implemented, but it is not possible to implement the scenarios I am going to cover in this blog and hence the rest of the blog will only talk about classic Project Server permissions mode.

Scenario 1: Bob should access all Projects managed by his Department
By “Project managed by his department”, I mean the projects where the Project Manager belongs to Bob’s department. We don’t want to give access to Bob on the projects where his resources are working as a team member but not managing the projects.

Solution:
We need to use RBS (Resource breakdown Structure) to define departmental relationships of users. This can mimic the actual organizational hierarchy or it could be a simplified version just to cover projects security requirements.
1) Go to Server Settings -> Manager Users > Bob (or whatever the name of your user is). Set the RBS value of that user at a top departmental level. E.g. Acme.HR
2) All other Project Managers in that department should either be at same RBS level (i.e. Acme.HR) or below it (e.g. Acme.HR.PM)
3) Create a new category called my department Projects. In the Projects Section, use the following settings.

4) The last checkbox is only required if some of the PMs are at the same RBS level as of Bob.
5) In “Views – Add to Category” section, Assign at least 1 Project Center view to this Category, so that Bob can see the Project Name in Project Center.

6) Create a new group called “Department Managers”, Add Bob to this group, and add your newly created category “My department Projects” to Selected Categories section.
7) While “My department Projects” is in selected categories section and it is clicked. You should see Permissions section which is specific for this category. If you want to give Bob the same level of access as of Project Manager then select “Project Manager” in the “Set Permissions with template” Drop down and click apply. You can also give Permissions of Administrator, Team member or any other pre-defined access set.

8) For users of other departments, who want similar access as of bob for their own department’s Projects, You need to setup their RBS values but without creating a new category, you can simply add them to “Department Managers” group.

Scenario 2: Bob should access all Projects managed by his sub-ordinates.

This is the extension of previous scenario. Here Bob instead of being a department manager, could be at any level in an organization. So he could be leading a Business Unit, division or team etc.

Solution:
As long as your RBS is setup properly (i.e. all subordinates’ RBS value are at level below the RBS value of Bob), the solution will remain same as of scenario 1. For semantic correctness, you may want to use different names for your newly created category and Security group.

In the next part of this blog series, I'll cover scenarios including access to SharePoint based Project Sites and accessing Projects where a certain value for a custom field has been set.

Project Online – What it does and doesn't do.

2013-11-25T02:13:00.000-08:00

Though Project online is less than a year’s old offering by Microsoft and at the time of writing this , not yet launched in Australia, it is based on a proven Microsoft Project Server technology which has come a long way over last decade and has kept pace with demands of maturing PPM practice worldwide.

Being nascent in its cloud hosted variation, Microsoft Project Online has yet to re-factor some of its features to make the product more usable and valuable for its growing customer base.

Here is a list of differentiating features which still make Project 2013 on-premise more viable solution to decision makers who are looking to gain real value out of a PPM Investment.

Business Intelligence: SSRS + direct DB access is not supported with Project online. The alternative supported tool i.e. Excel + oData has some cool uses but its quite slow and doesn’t cover many key reporting scenarios like

a. Filtering data at query time based on user supplied parameters

b. Very limited time phased data available and no access to OLAP cubes.

c. Not optimized for PDF / Print output

d. Fetching cross project sites data is v. limited

Access to ULS Logs: The two most common error messages end users receive in Project interface are “Unknown error” and “Unexpected error”. In on-premises configuration, administrator can access ULS logs to troubleshoot the cause. In Project Online, you need to raise a support ticket to Microsoft

Controlled Patching: Project online is great because it always offers the latest software. This, however may not always be good as some times new code can change existing behaviour or worst yet, break working functionality with no advance warning. In on-premise environment, test environment can be used to control patching in planned manner.

Central Admin Access: Granular control over backing up and restoring project schedules, controlling refresh warning in excel data connections etc. are few of those settings which are essential yet being part of central admin, are available in on-premise version only.

EPMonDemand is an offering by EPM Partners (the company I work for) in Australia which offers best of both worlds by providing a cloud managed solution with flexibility & control of an on-premise solution. Having a successful track record of providing cloud hosted EPM solutions since Project server 2010, EPMOnDemand has tackled the typical challenges of multi-tenant environment by offering greater control over deployed solution based on customer needs.

Here is a full comparison list of features in Microsoft Project On-premises vs Project Online vs. EPMonDemand

Feature	Project Online	Project On premises	EPMonDemand
Administrative backup and restore for Projects (used to restore a slightly older version of a project without pulling a tape and impacting an entire organization)		X	X
Archival Environments to retain data		X	X
Multi-Dimensional Olap Cubes		X	X
Direct access to SQL Server Databases and OLAP Databases		X	X Greater flexibility for Reporting
Direct SQL Query Access to Project Server and SharePoint Databases		X	X
Team Foundation Server Connectivity (including 2012)		X	X Can be achieved with “YourCloud” offering.
Event Handlers can use both full trust event handlers and remote event receivers		X	X
Full access to PSI and CSOM		X	X
Access to ULS Logs		X	X
As a result of full access to the databases, PSI and CSOM there is more flexibility for Business Intelligence, Dashboards, and Reports		X	X
Recycling Bin for documents, projects, and sites	X	X	X
Project Web Applications (Add applications to PWA)	X	X	X
SharePoint Permission Mode or Project Server Permission Mode	X	X	X
SharePoint Designer for workflows	X	X	X
No database administration	X		X
Security handled by Provider	X
Latest and greatest service packs and cumulative updates	X		X Patches tested first in internal environment
No direct access to SQL Server and OLAP Databases (Reporting is done via OData)	X OData is not on par with direct data access
Access using CSOM (Note: PSI interfaces do not support O- Authentication)	X		X CSOM & PSI, both are supported.
Event Handlers require remote event receivers	X
Extensibility is accomplished via the use of the SharePoint Extensibility Model – (as a result there is no full-trust code)	X

Reference:

Online and On-Premises Solutions for Project Server 2013 (white paper)
http://technet.microsoft.com/en-us/library/dn217893.aspx

What’s old in Project Server 2013

2013-08-01T23:23:00.000-07:00

[Classic Project Server functional limitations that prevail in 2013]

It seems Microsoft do listen to its customers feedback and have provided some great new features in Project 2013. These enhancements directly address most of the key limitations customers faced using Project Server 2010.

My favourite amongst these features is the ability to define Project Governance workflow in SharePoint designer 2013. Power View has a great WOW factor. True multi browser support is awesome and despite some limitations , the only practical issue I came across in my extensive (Ahem! 3 weeks ;) ) usage of Project Server 2013 on Google Chrome was that I couldn’t use Explorer view for my SharePoint libraries.

Other than that, ability to set baseline with in PWA, developer improvements (CSOM), mobile access views, oData feeds are all welcome additions.

But if you earn your bread by configuring and customizing Microsoft EPM (oh, I meant PPM) Solution like me, you would notice that some pain areas have survived and ‘successfully’ migrated to 2013 version. Here is a (not exhaustive by any means) list of such points.

PDP Infrastructure and Custom Fields

1- Project Fields Web part (now renamed to Basic info in 2013) gives severely limited options in customizing the look & feel of the form. This means that

a. You can’t change label of fields (and can’t rename system fields) without opting for JavaScript hacks or replace it altogether with InfoPath.

b. Hierarchical drop downs are not possible (e.g. Drop down for section can’t change its possible values based on what is selected in Drop down for Department).

c. Custom validation of fields is not possible without JavaScript trickery.

d. Can’t pre-populate fields based on some calculation and still leave them open for editing.

e. No attachment button. Would be great to attach related documents like business case on PDP itself.

f. No Resource picker control.

g. No checkboxes and hence can’t make ticking a check-box mandatory. Can’t make a flag field mandatory either. If you want your PM to fill a check-list, and mark all items as “Yes” before he can move to next stage, this is not possible without server side workflow validation. Though the good news is that developing such workflow is much easier now in 2013 than it was in 2010.

2- Security can’t be controlled on PDP Level (example scenario is where only PMO should see the budget information PDP or only PM should update the Status Updates PDP)

Governance / Workflow

3- Site Template and Plan template can’t be attached to EPT post-project creation. So you can’t decide if a project is going to be small/medium or enterprise in planning phase and then attach the appropriate template in execution phase.

4- Change the Project Owner after project creation and any task assignment which was created prior (one frequent source is Project Plan Template) to that would still keep previous PM as Status Manager and that guy will keep receiving approval requests on that task.

Reporting

5- SQL Server Reporting Services (SSRS) 2012 is nearly identical to SSRS 2008. Whatever you dislike about 2008 is still there J one example is limited browser support. You have to spend 90% of time in completing last 10% of report formatting to make it export compatible. Can’t control much of the parameters look & feel or their conditional visibility. Old style VB for customization. No CSS/Java script Support. No event handling. Can’t show a page number other than in header/footer. Well! I forgot I was only giving one example.

6- Couldn’t show labels on any chart in PerformancePoint 2010. Can’t do that in PerformancePoint 2013 either. That’s btw true for Excel Power View 2013 as well but hay that’s a “new” issue so it doesn’t count.

Project Site Integration

7- Can’t calculate Project server Risk Indicator or Issue Indicator fields based on custom columns in Risk & Issue Lists in Project site. A corollary to that is that Risk/Issue Project site information can’t be shown in Project Center views.

8- Can’t show project fields in Project site or vice versa without unsupported solution starter components.

Scheduling in PWA

9- Its great to save baseline within PWA. Though I would like to assign partial resources to tasks as well. More details here http://blogs.office.com/b/project/archive/2013/05/29/differences-between-project-professional-and-project-online.aspx

10- You can’t have Cost resources assigned to task in Schedule template. If you do so, you can’t create a Project in PWA.

Project Professional

11- Create whatever PDPs you want to create. Project professional doesn’t know about and won’t honour them, it will show all the fields in Project Information form whose behaviour is not dependent on workflow and are not multiline.

Other

12- No mechanism to generate Auto Project ID

13- No intrinsic support in PWA for Program Management. Master Project and sub Project relationships can be defined in Project Professional which may not be the tool of choice for PMO.

14- New Project can’t be cloned from other Project.