Converting RTF to Text in SQL Part 3

captain-picard-facepalm-meme

Yes, Part 3. This technique was published some time ago and as it gets further “field testing”, new conditions have come to light which require a revision of this technique. Assuming this is the last post on this topic, I need to figure out how to duplicate this logic in PowerBI.

I consider this to be version 15 or so of this query since my first version was written in mid-September of 2010. The changes here are as follows.

First, the DTD definitions have been expanded to include all possible HTML codes. This should prevent a report blowing up when someone decides to use an unexpected symbol somewhere.

Second, I keep seeing random <br> tags in the fields, which ironically is breaking the query. I’ve added a replace statement for that as well.

Lastly, I’ve expanded the field sizes to Max where necessary to accommodate truncation issues.

Sample code is below. I strongly recommend copying into NotePad first so that you don’t accidentally get curly quotes. I’ve also left it as straight text to make it easier to copy.

declare @Headxml nvarchar(3000)
declare @Footxml nvarchar(50)

set @Headxml = N'<?xml version=”1.0″?>
<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN”
http://www.w3.org/TR/html4/loose.dtd”
[<!ENTITY quot “&#34;”>
<!ENTITY amp “&#38;”>
<!ENTITY lt “&#60;”>
<!ENTITY gt “&#62;”>
<!ENTITY nbsp “&#160;”>
<!ENTITY iexcl “&#161;”>
<!ENTITY cent “&#162;”>
<!ENTITY pound “&#163;”>
<!ENTITY curren “&#164;”>
<!ENTITY yen “&#165;”>
<!ENTITY brvbar “&#166;”>
<!ENTITY sect “&#167;”>
<!ENTITY uml “&#168;”>
<!ENTITY copy “&#169;”>
<!ENTITY ordf “&#170;”>
<!ENTITY laquo “&#171;”>
<!ENTITY not “&#172;”>
<!ENTITY shy “&#173;”>
<!ENTITY reg “&#174;”>
]><html><body>’

set @Footxml = N'</body></html>’

select   *
,ISNULL(LTRIM((CONVERT(xml,(@Headxml + replace([YourMulti-lineCustomField],'<br>’,”) + @Footxml),3).value(N'(/)’,’nvarchar(Max)’))),”)   AS [YourMulti-lineCustomFieldNewName]

,ISNULL(LTRIM((CONVERT(xml,(@Headxml + replace([YourMulti-lineCustomField2],'<br>’,”) + @Footxml),3).value(N'(/)’,’nvarchar(Max)’))),”)   AS [YourMulti-lineCustomFieldNewName2]

FROM  dbo.MSP_EpmProject_UserView

How To: Connect Project Pro to Project Online

Here’s a quick article on how to connect Project Pro to Project Online. The question seems to come up on a regular basis so I’m documenting it here.

In Project 2013 and Project Online, Microsoft added the ability for Project Pro to autoconfigure the connection to Project Web App. However, to make this happen, you need to start in PWA.

  • Navigate to your PWA site
  • Go to Project Center by clicking Projects tile or Projects in the Left Navigation
  • Click the Projects tab to display the ribbon
  • Click the New button in the ribbon
  • Click the In Project Professional option in the dropdown menu
  • Project Pro will open a blank project, after a brief pause
  • Close Project Pro without saving anything

Project Pro is now configured to open and edit projects from Project Web App.

Going forward, you should open your project from PWA as opposed to Project Pro. This is the future interaction direction. The Edit button provides an option to edit the project in Project Pro.

Project Tasks are Your Lowest Priority

Project tasks are the lowest priority work you have on any given day. Wait, what?

It’s true! Strategically, we know project work is the most important future investment for the company. When you break down what you do every day, you’ll see that you are fitting in project work around the other work you have to do. It’s frustrating. You know you could be doing more. It’s frustrating because someone thought you had the time to get this work done.

If you don’t believe the premise, imagine the following scenario. You are staying late at the office to get some project work completed. Your manager’s manager sees that you are in the office, comes over, and asks you to do a task for tomorrow morning. If your answer is “I’m sorry, but I can’t because I really need to get this project work completed.”, their response will determine the relative priority of project work in your environment. For some, rejecting the task would be a career-limiting move.

Perhaps then, we are asking the wrong question when it comes to resource capacity management. Instead of asking whether this resource has free capacity to do the work, shouldn’t we be asking if the resource has enough consolidated free time to work on project work? If they do not, what can we do to remedy this situation?

In my “Done in 40” webinar, we discussed recent research by time-tracking software companies that identified how the top 10% of productive employees work in an agile fashion. These employees typically work 52 minutes and take a 17 minute break away from the work.  This is coherent with ultradian body rhythms studies from the 90’s and 00’s that showed your focus naturally waxes and wanes on a 1.5-2 hour schedule. These work sprints can make you very productive and help reduce mistakes and rework.

I’ve personally tried the sprint approach and I can say, it works well for me. I use a timer app on my Pebble watch to monitor my sprints. Fifty minutes is roughly the time where the mind starts wandering to “Did Joe ever respond to my email?” or “Is there coffee?”. Three sprints enable the top three daily tasks to get done easily.

The catch is you need to have 69 uninterrupted minutes to complete a personal sprint. This leads us back to the question of does a resource have consolidated availability? Yes, they have 3 hours available that day but if it’s in 15 minute increments, that’s not usable.

When a client with project throughput issues engages my services, I find it’s usually not a project management issue. Many times, the lack of consolidated availability is preventing the project work from happening. If you are interrupted every 10 minutes, as are most office workers in the United States, it’s very hard to get work done. If you are having issues getting projects through the pipe, perhaps it’s time to look beyond your projects and to your operational work processes.

We spend the majority of our energy providing oversight and processes to projects, which are a minority of the work instead of doing the same for operational work. McKinsey released a white paper recently that showed most of the operational spend goes to keeping the company running. New projects are a small portion of the overall effort. Yet, we don’t monitor operational work holistically the way we do projects. Perhaps, its time we start.

Project management processes are very helpful and needed. We’ve worked out how to anticipate and reduce risk and how to deliver the reward. We need to apply these approaches to how we manage all work. It’s the operational work that provides the overall context within which we do our project work. If improperly managed, it also constricts our ability to get our project work done. Operational work management improvements could yield the biggest benefit by enabling the consolidation of availability, yielding more usable time for project work.

If you are interested in finding out more about the specific techniques and how to use Microsoft Project to support this need, sign up here and get the recording link to the full “Done In 40” webinar.

Is Your Data Looking for a Problem to Solve?

Data can be a wonderful thing. There are so many stories you can tell with the right data. These stories have the power to persuade and motivate positive changes in the organization. And so the marketing stories go on and on about the power of data.

The question is, do you have the right data? Can you tell compelling stories with your data? How do you know what is possible or needed? Let’s discuss some straightforward techniques to help you determine the answer.

Many times, I’ve walked into a client’s office and was greeted with large quantities of data. However, the client was still struggling to tell a compelling story from their data store. There was always doubt as to whether the reports they generated were useful or compelling.

I’ve also encountered clients who maintained additional data as they thought someone *might* need. Unfortunately, wishful thinking is not an effective business strategy and can waste scarce company resources.

Here are some signs that you might need to rethink your data strategy.

  • Do your reports require data definitions, report keys and long explanations for someone to understand what they are viewing?
  • Do you have a training session on “how to use the reports”?
  • Are you maintaining data for which you are unsure of the purpose or use of said data?

If you answered yes to any of these, it might be time for a little housecleaning.

The Effective SimplicityTM approach dictates that we maintain as little data as possible to meet the business need. Minimizing data is a way of reducing the overall cost by reducing the overhead to manage the system. The trick is to determine what data is needed which is sometimes easier said than done.

At Tumble Road, we use an approach that follows a pattern that is understandable and helps determine the context and the need for the right data.  The pattern is Conversation – Question – Supporting Data and Activities.

Conversation is about identifying the specific meeting / use case that the tool will ultimately support. A diagram of some standard Project Management conversations are illustrated below. The conversation will have a schedule so that you can determine how often the data needs to be updated. The conversation has standard participants, which is helpful if you need feedback on the data you will need to provide. It also helps later in the case where you need to inform the report consumers of an upcoming change. Lastly, a list of 1-3 key questions is defined for the use case.

Project Communications

Key Questions are the information needs that needs to be supported. The Key Question also determines the form in which the answer must be presented.

For example, if the conversation is the weekly Tuesday portfolio status meeting between IT Management and Finance, likely, you will need to answer questions similar to:

  • What have we spent?
  • What did we plan to spend so far?
  • What are we planning to spend in the near future?

Supporting Data and Activities are the exact data elements that allow the key question to be answered and the activities necessary to generate, collect and maintain said data. The data can help you determine other incidental data, specifically necessary for organizing and filtering the Supporting Data. The activities can help you spot process gaps that may be present that would prevent you from successfully addressing the question.

When examining the What have we spent question? above, Finance wants Project spend broken down by Project, Cost Type (Capital or Expense) and Sum the totals by Fiscal Year, Fiscal Period for each IT Director.

From this short exercise, I already know the following is needed:

    • A lookup table for Cost Type with values of Capital, Expense.
    • A task custom field with Assignment Roll Down of the values enabled.
    • A lookup table for IT Directors, to maintain consistency.
    • A Project custom field for IT Director so that this can be assigned to each project.
    • To add this Project custom field to the correct Project Detail Page so that the PM can maintain this data.
    • Resource rates in the system so that costs can be automatically calculated.
    • To enter the Fiscal Periods in Project Online.
    • A project template which exposes the Cost Type column at the task level.
    • The cross-tab report layout
Director/Project/Cost Type FY2014-M01 FY2014-M02 FY2014-M03 FY2014-M04 FY2014-M05
John Smith

$100

$100

$100

$100

$100

Project X

$100

$100

$100

$100

$100

Capital

$50

$50

$50

$50

$50

Expense

$50

$50

$50

$50

$50

 

As you can see, you can generate quite a bit of actionable detail using this approach. There are several follow-up questions that can now be asked since you have specific, concrete examples from which to work. For example:

  • Do you need filtering by director?
  • Do you show only incomplete projects?
  • Is the IT Director data something that should be maintained by PMs?

This approach also aids in designing supporting processes. You already know this is a weekly review, so cost information has to be updated on a weekly basis by the PM. As the meeting happens on Tuesday, Monday is the deadline to get updates in and published.

A final advantage is that it is easy to track the progress of visible work by your users. For these engagements, the top level summary task would represent the conversation, a key question represents a sub-tasks and key activities, such as gathering required data, defining maintenance process, etc. are the third level tasks. This makes it easy to determine the “health” of the conversation implementation.

Once you are maintaining the data essential to answering the questions, with clearly defined uses, you should see a reduction in overhead as well as evidence of easier training conversations. If you would like to learn more, please join our Community here.

Avoiding Chaos at Light Speed

Managing multiple Project Server instances over the years has taught me that Project Management tools amplify your project process and project communications effectiveness. If your process and communication effectiveness is good, a tool will make this situation better. If there’s a communications issue or process breakdown, a tool will create “chaos at light speed”, amplifying the underlying problem.

The first step of any Project Server or Project Online implementation is to review your current communication and process framework. Using a question-centric approach like our Effective Simplicity™ approach can help guide you away from potential communication gaps and issues.

Projects are Conversations

Projects have been around for as long as humans have worked together to achieve common goals. They weren’t called projects at that time as most interactions were face to face.  The Project Management Institute (PMI) defines a project as a temporary endeavor undertaken to create a unique product, service or result. Feeding the tribe, fighting off invaders or building shelter for the family were a form of projects.

The efforts grew in size as did the number of people involved, as time passed. The need to capture the conversation between all parties became more critical. Project plans were born as a technique to keep track of the overall conversation. Thus, project plans represent the latest state of the conversation between everyone involved with the project.

The diagram below is a simplified representation of the ongoing conversations related to just one project.

Project Communications

Effective Conversations Require Commonalities

Projects represent a formal conversation that is a temporary reorganization of your work social network. There are some requirements for this conversation to happen effectively.

  • Common language
  • Defined information outcomes
  • Information cadence
  • Defined audience

Notice, there is no mention of common approach. Having a single approach for all projects is a bit like having the same logic for all software. It just doesn’t work as business needs vary. Rather, the touch points between projects should be common, allowing project plans to be customized for the given business problem but still able to share information across the portfolio.

Common Language

Common language implies that the terms being used have the same meaning across the organization. If Marketing, Customer Support and IT are using different terms for the same concept or the same terms for different concepts, a conversation breakdown is imminent. For example, a Go Live date may mean the date that the software is placed into production by IT. However, Customer Support views the Go Live as the first date in which they can start generating tickets. As these are different events, unnecessary confusion with external stakeholders is bound to occur when communicating Go Live plans.

Detailed Information Outcomes

Defined information outcomes are the questions you need to answer with the data captured in the tool. The use of questions focuses your thought process on concrete examples that are easy to communicate, easy to define what is in scope and easy to judge value from the outcome.

For example, you’ve defined an Executive audience who have the following three questions.

  • What is the total project investment for this fiscal year for each of the CEO’s strategic initiatives?
  • Which quarter will key value propositions by CEO strategic initiative be realized?
  • What is overall variance trend of our project investment from original plan?

For each of these questions, you can discuss the desired outcome with the target audience, allowing the definition of clear and concise data to be collected to answer the question. It’s also easier to track progress on the question rather than attempting to ascertain progress from a list of functional configuration steps. Questions also help drive clear implementation requirements. Once these requirements are gathered and tracked according to each question, you can make more intelligent adjustments to overall scope by excluding questions rather than blindly cutting tool scope.

To drill down, the first question will require the scheduling of project costs and assignment of project contribution to strategic initiatives. The scheduling of project costs, which can be done using Project’s cost resources, can be a significant training effort for PMs new to cost scheduling. Using the drivers in Project’s Portfolio Management functions can capture the strategic initiative contribution. Portfolio Management functionality requires good schedule and cost data in order to be effective. This may represent a larger implementation effort than you are able to take on initially. Ultimately, you can do both but now you have a clearer picture of the impact.

Information Cadence

If you’ve ever been in a conversation with your significant other and had your mind drift, you quickly discovered how  a lack of timely information can lead to a serious issue. Information cadence within the organization is about ensuring that the right data is maintained and available on a regular interval. For some organizations, that means projects are updated weekly on Fridays. For others, a monthly cadence is more appropriate. Setting an information cadence expectation ensures that everyone is listening for the same information at the same time.

For example, one client has all updates made by Friday evening as standard reports are generated on Monday morning for the project review meetings that begin on Monday afternoon. The cadence ensures these meetings have the latest information.

Defined audience

Within the project, the importance and significance of a member’s role changes over the life of the project as information needs change to achieve work.

Tools such as RACI attempt to model the project’s social interactions. Communication plans also attempt to do this, but from a different perspective. However, RACI, communication plans and other tools of that sort represent a one-time look at how the project members and stakeholders interact. These models fall down as soon as the project starts and reality takes over.

The question-centric approach maps the audience to the question, enabling you to easily monitor and manage the needs over time. If the question mix changes for a given audience, it is easy to gauge the impact and required work.

Starting Well Prevents Later Issues

Starting your project management system design, using a question-centric approach, will help you avoid later issues by ensuring you are meeting the most important needs of your audience. Clear definition of audiences and questions facilitates clear communication of value propositions. The questions also enable clear prioritization and scope control. Tool configuration becomes a means of supporting a conversation rather than being a driver of conversations. Within this framework, the result will be a much leaner Project Server implementation.

Want to know more about the Effective Simplicity™ approach? Join our community to find out about upcoming events.

What’s In Your Notebook?

A recent question on note-taking tools in the Project Managers LinkedIn group sparked quite a response.  The discussion got me thinking.  Do we ever treat note-taking as a corporate capability and are there business advantages to doing so?

Collaboration is one of those overhead tasks that is not tracked, though everyone is expected to do it. According to McKinsey and IDC, the average worker spends nearly 60 percent of their week reading email, finding information and collaborating with co-workers and other parties. That’s 24 hours of your work week!

Any reduction in collaboration time will gain the organization more time for other high value-add activities like projects. The key is to improve the situation with the smallest and simplest change possible by following the principles of Effective Simplicity™.  Effective Simplicity™ is about focusing on doing a few core actions in your organization well; those that yield the biggest organizational benefit. It’s upon this operational base that competitive advantages and real changes are built.

Today’s hot discussions are all about Enterprise Social and Collaboration, with vendors falling all over themselves to sell you the next tool.  However, there’s little energy spent on the original collaboration tool– note-taking. Why is that? Many organizations have mountains of data locked away in paper notebooks that people may refer to once and then discard. Project teams reinvent the wheel with new efforts as they have no awareness of previous discussions or access to notes on key topics.

Note-taking is one of the primary starting points of collaboration.  Let’s take a look at note taking as an opportunity to streamline data capture and information dissemination.

In this article we’ll focus on the information needs of a project team using tools you likely already have in-house. In particular, we’ll showcase the use of Microsoft technology, but the concepts may also be applied to other software platforms as well. The intent is to show how minimal tweaking of a common process can result in significant savings in an effort to collect and distribute information in a productive manner.

Why Do We Take Notes?

People take notes generally for two major reasons: capture of needed actions and capture of information for reference.  Notes taken for reference are important because according to the study above, we spend six hours a week looking for information that helps us be more organized and make decisions. Notes captured for collecting and distributing information and tasks to other members of the team and other stakeholders are equally important because they help us prioritize, assign, baseline, and just plain old communicate using a concrete method. Given this central role in collaboration, could this be a nugget to be leveraged and turned into productivity gold?  Maybe you are also shocked that it receives so little attention?

What Do We Do with the Notes?

In an organization using cutting edge, technology, the notes we place in a central knowledgebase are just a search away. Sounds wonderful does it? The reality for most organizations is that we simply aren’t there yet.  If you are still buying cases of paper pads every month and you look cross-eyed at someone who brings a computer to a meeting, you are at the beginning of this journey to a new way of working.

Notes should be all about capturing data from various perspectives and allowing analysis to make them clear and accessible to the rest of the organization. The challenge with taking paper notes is that you incur two taxes. The first is a “Time Tax”, that is, how long it is before the notes become available. In many cases, the answer is never  because the information is locked away in a long forgotten notepad under your desk. If you remember to communicate the contents,  a “Transcription Tax” is incurred to get the requisite data in electronic format. Then likely, it is emailed and only available in the inboxes of those who received the email.  This is just slightly better than a notepad under your desk.

So What Can We Do About This?

Let the Organization Know What Information is Important to Capture

To clarify, let’s take the first step by considering three sections for getting the most out of note-taking.

What constitutes great notes?

  • Actions
  • Awareness
  • Other Reference

Actions include items such as to do items for yourself and others and information gathering for prioritizing.  These items are typically very immediate in nature and aren’t put in the formal project plan.

Awareness items are data elements gathered to assist with decision-making and action-taking. These items include dependencies for current work, decision recording and alternatives considered and rejected and upcoming events which may require interaction from the team.

Lastly, there are reference items. A recent study by McKinsey showed that an average of 19% of our work week is spent searching for relevant information. Reducing the effort for this task by 30 minutes per week will give back a person-week of capacity to the company for each employee impacted.  As you know, finding information is a serious drain on productivity.

As an example from one particular project, regularly printed electronic copies of project-related invoices, trouble tickets and license agreements were placed into OneNote to provide one place to search for these items.

Pick a Note Taking Method That Fits the Organizational Culture

The second step is to decide what tools capture notes easily. If you look around the room in a meeting, what do you see? iPads? Notebook computers? Paper and pencil? Depending on the current cultural norm in your organization, you have choices.

I Love the Smell of Fresh Paper in the Morning

If your organization is paper-centric, you might consider looking at technologies like LiveScribe 3 or Capturx. Both allow you to write using a special pen and the notes can be converted over to OneNote or other repositories. This allows individuals to continue taking notes in the format with which they are familiar but also provides a way to capture the information electronically to distribute in a number of ways, reducing both Time and Transcription taxes.  Note, these products are still early in the product adoption lifecycle and prices reflect that reality.

Moving to a more consistent manner of capturing and disseminating notes will require a conversation with meeting leaders about how long you have to transcribe notes and where to put the electronic transcriptions. Setting an organizational expectation will encourage adherence to the community agreement.

Time also impacts the quality of notes.  Longer time leads to data loss. One former client decided to reserve the last 10 minutes of a meeting to finalize the notes and transcribe them into email. The idea– capture information while it is still fresh in the mind of the note taker and get them locked.  This expectation helped stress the importance of capturing meeting notes to the organization and assisted people in adopting the habit.

Paper? We Don’t Need No Stinking Paper

There are also many electronic tools available for gathering notes. OneNote, a free alternative from Microsoft and Evernote, offer many data capture features that allow access to the data from any device. They both offer cloud based storage which keeps you from losing data in the event of a theft or corruption of your device.  Lastly, they both support third party applications, which enables the applications to integrate easily with other solutions. Our technical blog,  http://aboutmsproject.com/, will have a series for Administrators on how to implement a OneNote and SharePoint based solution for sharing notes.

Electronic tools have an advantage in that once you are done typing, the notes are mostly done. However, the organizational expectation that notes will be taken and distributed still needs to be set. Otherwise, you will get inconsistent information which will increase information search time. My personal experience has shown that setting a few clear expectations gives adoption a much better chance than a Licensing document approach to implementation.

Here’s a real world example of meeting note guidelines.

  • Always take meeting notes.
  • Include the agenda and who attended
  • Post the notes within four hours of when they are taken.
  • Capture actions, awareness items and any reference information relevant to the project
  • Put the notes here: [Provide the location]

This list has been a great springboard for new behavior.  By communicating to everyone that notes were expected within the same day and that certain bits of information, like an agenda, attendees, actions, awareness items and reference items should be in them, the organization was able to adopt the new process.  Of course, time must be allocated for this activity.  Also, a location was provided to ensure there was no excuse for “not knowing.”

As a PM, the other technique I used was to refer to the meeting notes when we had status meetings. If you didn’t do your notes in a timely fashion, your peers would let you know this outcome wasn’t acceptable.

Adopt a Consistent Manner to Distribute Notes

The final decision is how to best distribute either the notes themselves or notification that notes were posted.  Picking a narrow set of options makes it easier for people to develop the habit of both sending and expecting notes.  The problem really comes down to using multiple delivery channels creates challenges for the reader to consistently find the information. If you find yourself having this sort of internal dialog, “Did I get that in email or is it in Yammer or did he put that on the Team Site,” you may have a dissemination choice problem. Reducing the options to an agreed upon select few will reduce search time.

Small Changes Can Have Large Impacts

Who knew note-taking could affect so much?  Give these recommendations with your team.  Making three simple changes in your processes can help get your team and organization get back valuable time.

If you need further examples to substantiate taking action or would like to speak to someone that has tried similar techniques, contact us via this Join our Community link and indicate your interest.  Tumble Road believes in knowledge sharing that helps the overall community.  We’ll be glad to help get you to that day when you can search the Corporate Knowledgebase for all of the information you need for your job.

How to show Implementation Milestone Dates in Project Center

This technique illustrates how to use formulas to extract dates from tagged milestone tasks within your project plans and show the dates in the Project Center.

This technique has been tested with Project Online and Project Server 2013 On Premises. However, I don’t see a reason why it wouldn’t work in Project Server 2010. The primary difference is that 2010 will require a round-trip of the data in Project Professional to calculate the formulas.

Scenario

I’ve had several clients ask “Can we show the next Implementation date in Project Center?”. The challenge is that the Implementation milestones are task level data. The Project Center only shows project level data, therefore you must extract and transform the data to show it appropriately.

Since you may have multiple Implementation milestones, this technique will show the next upcoming Implementation date. If there is no upcoming date, it will show the most recent past Implementation date. This way, the field should always show data.

Using Metadata Instead of Task Names

My first job in industry was converting old RPGII programs on the IBM System 38. I quickly learned the evils of using content as data as that was a very common technique in those days. This technique led to users accidentally breaking processes by inadvertently adding a space or changing a term. If you’ve told your users not to update a task name or the name of some other element, you too are using content as data.

Search engines later taught us to use metadata tags to separate functional data from content. Thus, this technique requires the creation of a task metadata field for tagging tasks with a specific reporting purposes. The illustrated design only allows one tag per task.

The benefit of this technique is that it is agnostic to the names of tasks and milestones, as long as they are tagged appropriately. PMs can change the task names and plan structure to meet their needs as long as they tag the tasks and milestones appropriately.

Metadata Lookup Table

The Reporting Purposes lookup table supports two levels of values. This enables multiple classes of tags, such as milestones and phases. This exercise focuses on the Milestone.Implementation value.

image

Metadata Custom Field

Create the Reporting Purpose task custom field and attach it to the Reporting Purposes lookup table. Specify that Only allow codes with no subordinate values is selected. This prevents the user from selecting Milestones without selecting a more specific purpose.

image

Extracting the Data

The technique takes advantage of the rollup capability within Project, that enables a task level value to be rolled up to Task 0 (Project Summary Task). Task 0 values can be retrieved and used in Project formulas, thus translating the task data to project data.

Task Custom Date Field Setup

Two task level formula fields are needed to determine which Implementation date is available as stated in the requirement above. Show me the next Implementation date or if not available, show me the most recent past Implementation date. These requirements constitute two separate conditions leading to the need for two separate task formula custom fields.

Next Implementation Task Date

The first task custom date field is Next Implementation Date. This field determines if there are one or more Implementation dates in the future. The future is defined as any date equal to or greater than today on any task marked with a Reporting Purpose value of Milestone.Implementation.

The rollup is used to retrieve the soonest Implementation date if there are multiples. When defining this field, specify the rollup behavior as Minimum so that the Implementation date closest to today will be shown. 

image

Formula

IIf([Reporting Purpose] = "Implementation" And [Finish] >= Now(), [Finish],

IIf([Reporting Purpose] = "Milestone.Implementation" And [Finish] >= Now(),

[Finish], ProjDateValue("NA")))

This formula may appear a bit odd when examined closely. It appears that there are duplicate conditions specified. This is done intentionally as a workaround to a difference between PWA and Project Professional in formula evaluation during the schedule edit.

The Reporting Purpose custom field is hierarchical so the tagged value is Milestone.Implementation. In PWA, the formula only sees the “Implementation” part of the value when you edit the schedule. The first check ensures that PWA evaluates the formula correctly. In Project Professional, the formula sees the whole value. The second check of the full value is added to ensure Project Professional evaluates the formula correctly. Both checks prevent data from being inadvertently deleted when editing in across both platforms. If neither value is found, the field is set to a special date value of NA, using ProjDateValue(“NA”).

Previous Implementation Task Date

This field helps determine if the most recent past Implementation date. This field determines if there are one or more Implementation dates in the past. The past is defined as any date less than or equal to today on any task marked with a Reporting Purpose value of Milestone.Implementation. The same double check of the Reporting Purpose is required to keep the editing behavior consistent.

image

Formula

IIf([Reporting Purpose] = "Implementation" And [Finish] <= Now(), [Finish],

IIf([Reporting Purpose] = "Milestone.Implementation" And [Finish] <= Now(),

[Finish], ProjDateValue("NA")))

Rollup to the Project Level

A Project level custom date field is created to perform the logic of determining the correct Implementation date to display in the Project Center. The formula checks the Next Implementation Task Date for a value of NA. If found, it assumes there is no future date and uses the most recent past date. If NA is not found, it uses the soonest future date.

image

Formula

IIf([Next Implementation Task Date] = ProjDateValue("NA"),

[Previous Implementation Task Date], [Next Implementation Task Date])

Add to the Task Summary View in PWA

I now add the Reporting Purpose, Previous Implementation Task Date and Next Implementation Task Date fields to the Project Task Summary view. This enables the PM to mark the appropriate tasks in PWA if desired. It also helps you see the effects of the formulas.

When I edit the project in PWA, I will see something like the following. Here, I’ve created a project with four Implementation milestones, two in the past and two in the future.

image

Today is April 16, 2014, so the next future Implementation date is 4/25, which is what should be shown in the Project Center, as seen at 1 below.

image

I hope you have found this technique to be useful. Please post any questions in the comments.

Don’t Get Burned By Your Security Templates

image

Problem

If you’ve ever tried to use the built-in security templates in Project Web App, you may have accidentally messed up your security model without realizing it. This problem applies to Project Server 2003-2013 versions.

Security templates are designed as a way to quickly apply or reapply permissions for predefined roles, when creating new groups and categories. However, the out of box implementation can lead to issues if you don’t realize the impact of applying them.

Background

Groups and Categories have what is known as a many to many relationship.  A group can be associated to multiple categories and a category can be associated to multiple groups. The default security model relationships are shown below where blue boxes represent the Groups and orange boxes represent the Categories.

image

We’ll use Resource Manager as an example of the security template issue. Resource Manager has four relationships out of box by design:

  • My Organization so that they can see all resources and build team on any project
  • My Projects so that they can view any project of which they are part of the project team or own
  • My Resources so they can only see their resources below them in the RBS so that the Resource Manager can add them to a Resource Plan
  • My Direct Report which is reserved for you to customize functionality for the resources directly below the Resource Manager in the RBS The heavy lifting in the security model is at the intersection points between Group and Category. The intersection is where you set the what allowed Project and Resource actions (Group) can be taken on the data returned by the Category. If you’ve seen a “troubled” security model, it’s usually because this nuance was lost on whoever was maintaining the model.

Scenario

NOTE: PLEASE DON’T DO THIS PROCEDURE WITHOUT READING THE ENTIRE ARTICLE FIRST

Felix is a Project Server administrator who accidentally changed some category permissions in production on the Resource Manager – My Projects intersection. “No problem”, thinks Felix, “I’ll just reapply the Resource Manager security template and all will be good.”

    Felix then does the following actions.

He goes to PWA Settings under the Gear.

image

He clicks on Manage Groups under the Security section.

image

He clicks on the Resource Managers group to edit.

image

He scrolls down to Categories to access the Category permissions for the group for My Projects.

He selects My Projects in the Category list to show the permissions. At the bottom of the category permissions section, he selects the Resource Manager template and clicks Apply.

image

All good right? Not exactly.

The Issue

Remember, Resource Manager Group has four category relationships.

image

However, if you go into Manage Security Templates, there’s only one entry for Resource Manager.

image

So, which relationship does this security template represent? Was it the right one for Felix to apply? You don’t know without further research.

Suggested Fix For This Situation

If you choose to use Security Templates, I highly recommend doing the following prep work. This recommendation is based on the real world experience of managing two Fortune 250 company implementations and having cleaned up numerous security models for other companies. An hour or two of prep now will prevent tears later on.

Create a new template for group permissions and one for each intersection for the category permissions using this procedure. http://technet.microsoft.com/en-us/library/cc197679.aspx If you’ve heavily customized your security model, you will need to create a diagram similar to the one I have above first.

The resulting template list for Resource Manager will be as follows.

  • Resource Manager – Group Permissions Only
  • Resource Manager – My Organization
  • Resource Manager – My Projects
  • Resource Manager – My Resources
  • Resource Manager – My Direct Reports
    Now, when Felix applies a security template, he knows exactly which one he is applying to the security relationship.

Resources

You can find the default Project Server 2013 group and category permissions at these links for constructing your templates.

Take Control of Your PWA Home Page with 4 Clicks

image

Have you ever looked at the Project Web App home page and felt it was simply too plain for Jane? I understand the need for design simplicity but sometimes the PWA home page simply feels like underutilized real estate.

The Need

Your manager wants you to add system announcements, support contacts and a list of wiki based FAQs to the PWA home page. So, you knowledgeable Project Administrator, attempt to Edit Page the home page and start adding web parts, like you would do to most SharePoint based pages.

The Challenge

To your dismay, you quickly realize that the home page layout is single column, full page width. Technically, you can add these new parts to the page, but now the user likely needs to scroll and your manager hates to scroll. You attempt to change the layout and there’s no visible way to do this. Times like these cause grown Project Administrators to shake their heads in despair. Why is this so freaking hard???

SharePoint to the Rescue

SharePoint 2013 On Premise and Online versions provide a site feature that replaces the site home page with a Wiki page. Wikis are flexible by design and by doing this, you gain the ability to set different page layout. You are also able to add text, apps and web parts to the PWA home page as desired. Sounds like just the ticket.

Hold on, cowboy! There’s one caveat to this option. The home page URL will change as a result. See the before and after below.

Before PWA URL

https://servername/PWA/default.aspx

After PWA URL

https://servername/PWA/SitePages/Home.aspx

The Implementation Details, Illustrated

image

image

image

image

The Implementation Details, the Checklist

  1. Click the Gear in the upper right corner of PWA
  2. Click Site Settings
  3. Click Manage site features in the Site Actions section
  4. Scroll to the bottom of the page and click Activate for the Wiki Page Home Page feature

Redesign Your Home Page

The new home page is blank. To set the page layout, do steps 1 – 4 below.

image

image

image

Once set, place your cursor in the desired location on the page and perform steps 5 and 6 to insert web parts on the page.

image

To add back the default Track Your Work Links to the Home Page, insert it using steps 7-10 below.

image

The Lists

You can add whatever web parts you need to the various zones. The finished product uses three lists.

  • Announcements – View modified to use Newsletter, No Lines Style
  • Wiki Page Library – View modified to use Newsletter, No Lines Style
  • Custom List – Only a Person field, with Show Field in column definition set to Name (with picture and details)

For That Clean Look

I did two changes to remove unnecessary items. First, I don’t want any links for new items visible. So for each of these web parts, I set the Web Part value of Toolbar Type to No Toolbar.

    I didn’t want any list to have headers. So, I added a Script Editor web part and pasted in the following code.
<style type="text/css" >
.ms-viewheadertr   
{  
  display:none ;  
}  
</style>

The Final Result

image