Roll Up Any List in SharePoint for Total Work Management

There are many types of work that have to happen on a day to day basis to keep an organization up and running.  It’s not just about projects or the tasks within them but all work that affects your resources such as action items, to do lists, issues, risks, etc.  Work management is a critical component for increased productivity.    So how do you get visibility of all this work in a project management tool?  WorkEngine can handle this for you.  Let me show you how.

With the WorkEngine Grid/Gantt web part you have the ability to roll up any list or multiple lists in SharePoint into the same view.  In the example below I have a list called “My Work” where I have created a rollup view of all the different lists in which I have work assigned.

In order for data to display in the view, all your list names and column types must match as well as have the same internal names for all the columns you want to display in the rollup view.  External column names can be completely different.  By this I mean that all columns in WorkEngine lists have an internal and external name.  The internal name is the name of the column when it gets created and never changes.  This is why we use the internal name of the column for the web part.  Take for example a column entitled “Schedule Status” (best practice is to never put a space in the internal name of a column).  For the column listed above you would create the column as “ScheduleStatus”.  Once the column has been created, you can go back and edit the column and change the name to “Schedule Status”.  Now the internal name of the column will forever be (unless you delete the column) “ScheduleStatus” and the external name is “Schedule Status”.  You can edit the column and change the name as many times as you want and the internal name never changes.  Here is how you check what the internal name of a column is.

  • Navigate to the list settings page for the list where the column resides


  • Find the column in question and click on the name of the column; it will show as a link that can be clicked on
  • On the edit page for the column, click in the URL address bar and move the cursor all the way to the far right (the end of the URL).  Here you will see the value as “Field=ScheduleStatus”.  This is the internal name of the column


  • Navigate to the list settings page for your custom rollup list (remember, I’m using a custom list called “My Work”)




  • Click the “General Settings” link
  • In the “Field Settings” section choose the appropriate “Start Date”, “Due Date”, “Progress Bar”, “Milestone”, “Right
    Information”, and “WBS” values, if applicable
  • In the ““Roll-Up Settings” section enter in the lists that you want to roll up into the view in the “Rollup List(s)” text box
    (Ex:  Task Center, Issues, Risks, Actions)



  • Select the “Enable Performance” check box
  • In the “Editable Grid Settings” section select “Allow Edit Toggle” if you want the users to be able to switch to an editable grid view.  This means the user can switch to this mode via the “Datasheet” button in the ribbon and be able to bulk edit in the view
  • Notice in screenshot below that you can see all work assigned to you in one view

Video: Integrated Work Management – The Value of Total Work Management in SharePoint

Many organizations are not getting the full value out of their PPM systems because they are not accounting for all work that affects their project’s resources.  Take a look at this integrated work management video as WorkEngine Solution Specialist Christina Williams walks you through some of the common issues organizations face when total work management is not considered.  Find out how you can extend your SharePoint value by practicing total work management within your organization today.  Click on thumbnail below to view video.

World Leader in Manufacturing Selects WorkEngine for PPM in New Product Development

Congratulations to Vectron International and EPM Live trusted partner PM Providers for implementing a successful WorkEngine PPM Solution for New Product Development!

“WorkEngine was a great choice for us.  We’ve been able to bring project teams together from across the globe and improve collaboration.  Our executives love the ease it has provided to roll up multiple projects at a glance, and the seamless data integration with our other business systems,” said Dave Chandler, Vectron Product Manager.

Vectron International is a world leader in the design and manufacture of Frequency Control, Sensor, and Hybrid Product solutions including crystals and crystal oscillators, frequency translators, clock and data recovery products, Crystal filters and other components used in telecommunications, data communications, frequency synthesizers, timing, navigation, military, aerospace and instrumentation systems.

Headquartered in Hudson, NH and with operating facilities and sales offices in North America, Europe and Asia, Vectron International is known for its technical capabilities in crystal oscillator and SAW filter design. The innovation and capability provided by the company reflects technologically sophisticated integrated solutions.

Vectron’s continued innovation curve is dependent on highly skilled engineering resources with specific domain expertise.  As a result, Vectron’s ability to develop and drive new products and custom applications to market is directly tied to the availability of these valuable and limited engineering skills.  In order to more effectively balance the need for new product development with customer-specific custom orders, Vectron needed a way to understand and model the entire project portfolio with specific focus on product development road-map projects to ensure that adequate resources continued to be focused on future product development.

In addition to developing and tracking the portfolio of projects in the pipeline, Vectron also needed a way to account for production support activities, resource allocations across the portfolio, and actual work accrued against projects across three business lines and four development sites in the US and Europe.  The development of good project scheduling techniques, product development process adherence, enhanced collaboration and communications, and time reporting were essential elements for driving improved performance, accountability, and resource capacity optimization.

Vectron knew they needed to deploy a Project Portfolio Management System (PPM) to address their business challenges but were concerned with introducing too much complexity for employees.  Vectron entrusted PM Providers, an EPM Live partner, to select the ‘right-fit’ PPM solution for their business.  PPM tool selection was a critical step to ensure system acceptance and data integrity.  After evaluating Microsoft Project Server, EPM Live’s WorkEngine, and PM Providers’ EPMLite solutions, Vectron selected WorkEngine as having the right mix of functionality and ease-of use for the organization.

With the WorkEngine Solution Vectron was able to rapidly develop business-centric views of their project portfolio with dynamic, real-time reporting including resource utilization and capacity analytics.  In addition, WorkEngine’s user-friendly timesheets and timesheet compliance reports allowed Vectron to quickly evaluate resource efforts across business units and projects and adjust allocations to meet shifting priorities.  Built on proven Microsoft SharePoint Technology, WorkEngine was clearly the right-fit solution for Vectron International.  Now Vectron is able to effectively monitor and control project resources and direct key staff efforts to strategic roadmap projects without compromising existing customer project support.

EPM Live Welcomes Cambridge Technology Partners to the Partner Program

EPM Live welcomes Swiss based Cambridge Technology Partners to the EPM Live Partner Program. As part of an ongoing effort to serve clients across Europe, Cambridge Technology Partners and EPM Live have partnered to help customers leverage WorkEngine’s end to end Project and Work Management capabilities.

Cambridge Technology Partners was founded in 1991 in Cambridge, Massachusetts, USA, and rapidly became a leading consultancy. Today, they are a Swiss company serving a diversified portfolio of upper mid-market and corporate companies from locations in Nyon, Zürich and Basel.

Burn Down Charts – Not Just for Software Development Anymore….

While participating in the recent EPM Live Bootcamp (which was a HUGE success!!), I came across an interesting case study presented by one of our experts that I’d like to share with you. By implementing the Enterprise Cost, Resource, and Work Management capabilities of EPM Live’s WorkEngine product, a prominent telecommunications firm saved over $20M in fines!

Some of the key requirements for this particular WorkEngine Enterprise deployment involved:

  • SAAS solution for the Enterprise Project & Portfolio Management solution
  • Enterprise level Task and Work Management collaboration and reporting
  • Portfolio visibility of all projects, displayed in Gantt views for Projects within SharePoint
  • Enterprise reporting and business intelligence

One of their key work management needs included the need to count the number of Planning, Engineering, and other key tasks by Phase (Not started, In progress, Completed). This data needed to be stored in a time-phased manner so that trends could be graphically displayed in a report. A sample of this report is attached.

For those of you who have been exposed to Agile methodology, you will notice this is very similar to a traditional agile “Burn-Down” report. I found this an interesting approach to leveraging Agile-type reports within a traditional waterfall schedule that was managed within Microsoft Project 2010. Because WorkEngine supports snapshots within their reporting solution, you can create a “Burn-down” or “Burn-up” chart on any SharePoint work items, not just your backlog!! State regulators were so impressed with this reporting method, there are many other vendors that have adopted this same reporting format.

This case study represents how WorkEngine supports true integrated work management to meet all needs across an organization, and provides valuable cost savings!  By implementing WorkEngine Enterprise, the organization avoided millions of dollars in penalties and improved resource efficiency by 40%.

Special thanks to our training department for hosting such a great Bootcamp where all our EPM Live experts could share their knowledge with our customers, partners and internal staff!

EPM Live Rocks the Gartner PPM and IT Governance Summit

Last week EPM Live sponsored the Gartner PPM & IT Governance Summit which took place in our very own sunny San Diego.  Our booth was full of excitement as we spent two days demonstrating WorkEngine’s newly enhanced PPM and IT Work Management capabilities.

WorkEngine was recently rated in the new Gartner Marketscope for Project and Portfolio Management Applications so we had a lot to celebrate as we kicked off the conference on Monday afternoon.  WorkEngine, represented as the “SharePoint Answer to PPM”, drew a large audience interested in expanding their underutilized SharePoint collaboration systems into full PPM solutions.

Although SharePoint-based PPM was a hot topic, so was WorkEngine On-Demand.  As Gartner predicted, SaaS solutions are on the rise and we definitely felt the heat as we answered all of the common SaaS questions that circulated the show floor.  PPM online is definitely the new rage and with WorkEngine’s affordable, quick and easy PPM online solution, the response was incredible!

But for those of you who know us well, it can’t be all work and no play.  EPM Live hosted a rockin’ EPM Live concert event in the historic Gaslamp District Monday night to help attendees wind down from the day’s events.  The EPM Live party limo bus escorted conference attendees to and from the event to give everyone the full rock star experience.  The EPM Live band was there to welcome the crowd as we rocked the night away.  EPM Live partners, customers and conference attendees filled the venue and helped make this year’s conference one of the best!  To check out more party pics from the evenings event, click here.  As for the conference, well, it was top notch!  We hope to see you next year!


WorkEngine #Agile Management – Expanding Iterative Planning Outside of Software Development

The Agile trend has been rapidly increasing now that the practices are more easily understood and adopted, and tools have become more increasingly available.  While Agile is definitely more prominent within the software development community, we’ve found customers finding new and innovative ways to leverage iterative planning outside of software development projects.  Since WorkEngine supports both standard waterfall scheduling approaches, along with iterative planning, organizations have been able to leverage one solution across multiple departments.

For example,  one of our customers has a department with a need to manage their monthly publications.  Key requirements that influenced them to use iterative planning include:

  • Short cycles with repeated activities
  • Frequent releases of publications
  • Highest priority items are addressed first
  • Finish date is firm but scope might be changed
  • Need to easily move work items among iterations

To meet these requirements, each monthly publication is represented as an iteration within WorkEngine.  Users can create, name and time-box these iterations associated to publications for management and tracking capabilities.  Managers easily create tasks, sub-tasks and general activities and associate them to a specific publication (iteration).  They then define the complexity (points) or workload (effort) to a specific monthly publication and resource within their WorkEngine solution to balance the workload.  As such, managers now have the capability to plan and coordinate resource workload to ensure proper placement, expertise and efficiency for each publication (iteration).  Work items that don’t make a specific publication can be easily dragged into another publication iteration.

In addition, some articles may span several iterations.  WorkEngine’s Agile solution allows them to associate each article to several tasks or activities within each iteration(s).  Combine the powerful iteration planning WorkEngine offers, along with the other work management capabilities such as workflow/approvals,  content management and financial management and you have one simple to use solution to manage across multiple department needs.

How to Edit Time and Work Status in WorkEngine's #SharePoint Timesheets

​I have had several questions recently asking, “Why do we have to enter time and work status or work item meta data separately?”  This question is typically followed by a statement along the lines of “It seems so cumbersome.” or “It’s time consuming and frustrating.”.

My answer is always that “You don’t have to!”.

I’m guessing there are others out there with the same question so I felt that I should shed some light on the subject. You absolutely can edit work item status information and/or meta data such as % Complete, Start Date, Finish Date, etc., right from the WorkEngine timesheet! In fact, you should be! We are all about making life and collaboration much easier.

How you might ask? It’s simple:

1. Navigate to the “General Settings” page located in “List Settings” for the My Timesheet list. (List Settings -> General Settings)

2. Check the boxes for “Default to Edit Mode” and “Allow Edit Toggle” if they are not already checked.  Click Save button.

As long as the internal column names of the columns in the My Timesheet Lists are the same as the internal columns names of the Work Lists, you will be able to edit those columns in My Timesheet!

That’s it!  Simple.  Now you can add time to the work items and update the status and meta data all at the same time.  Also, when you save your timesheet the values you changed for the items is copied to the workspace where the items actually resides.    Pretty sweet huh?

#SharePoint 2010 Tips and Tricks: Upload a File in the Middle of Writing an Announcement

Today I found myself in this predicament:

I was writing a new post in the Announcements list on my project workspace, and I wanted to insert a link to the document I was referencing in the post. But, the document was not already uploaded to my SharePoint Site.
Back in SharePoint 2007, that would mean that I have to go upload the document, and then come back to the announcement, and copy the URL from the location I stored it, and try to remember what I was writing…but for 2010…Microsoft improved the product!
To clarify – posting an Announcement is very simple: Title, Body, Expires (expiration date). But now in 2010, when I click into the “Body” section, a ribbon lights up and I have two tabs: Format Text andInsert.
On the Insert tab, there are four options. Under the Links group, is the new option for 2010: Upload file.

SharePoint 2007 offers Insert Table, Insert Hyperlink (you provide the address), and Insert Image as separate buttons that are activated when you click into the Body field of the Announcement (in Edit mode).

The Upload File option is dynamic. It lets you browse to a local directory for the document that you want to upload, and then specify the site location for where you want to store this document as well. The Upload To menu will pull down all the document libraries that you have permission to:
I know it’s a small thing, but I found the addition cool and intuitive.
Other applications for this kind of functionality might be if I just created a new Excel-based report, and wanted to announce the availability of the report to my team.
Or, if we have a new form required for all systems owned by my department, I could blast the news and upload the new file at the same time!
Perhaps I just setup an EPM WorkEngine site, and I want to share my Project Plan presentation with everyone who comes to the site for the first time: right there on the home page in my Announcements list.
There are myriad applications for this time-saving trick. And, really, it’s the sum of efficiencies throughout the day that add up to better productivity as information workers.
I’m pretty happy I found this little trick, and plan to use it next time I have the opportunity.

Use Microsoft Office and SharePoint to Extract Data from WorkEngine's Reporting Database – Part 1

I want to take some time to talk about Microsoft Office 2010 and ad-hoc reporting in WorkEngine.

In this first of two posts, I will review the basic premise of ad-hoc reporting: a business user, or end user’s ability to directly manipulate data – specifically, data from WorkEngine’s SQL database, and data in Microsoft Office Excel 2010.

To do this, I will use the example of an Office Data Connection file with the WorkEngine Reporting Solution and provide a practical outline for creating a data connection file that extracts information from that particular SQL database.

The concept of directly connecting to a database is extensible, and the needs of an organization should be well-thought out in order for the technology to be really useful and well-performing. The many details of administering and architecting an ad-hoc reporting solution that uses Office 2010 to connect to a custom SQL database are probably impossible to blog, but I will create a second part to this post that covers the ideas behind SharePoint administration and ad-hoc reporting architecture, with these technical assumptions and best practices in mind.

Getting back to the topic at hand, the outline I will walk you through now is technical by nature, but you just need to know that what you are doing is opening a door to your data. I hope you will understand the basic concept of how a data connection works at the end. Also, my hope is that you will feel confident in extracting your own data from the LOB applications your organization uses.

To start, I am using Excel 2010 and WorkEngine 2010 (which is built on SharePoint 2010). Yes, only the latest and greatest for this gal! However, if you are using an earlier version of any of those applications, the concept is adaptable. (If you are a 2007 WorkEngine/SharePoint/Office customer, let me know how I can help with the correlation.)

For practical application of this exercise, you will need to have a WorkEngine site collection, with a reporting database, and that setup information. If you are a hosted EPM Live customer, the information you need was sent to the Site Collection Administrator in an email, similar to the one below.

WorkEngine Reporting Information: Sample Email

If you are not an EPM Live customer, but would like to follow along with practical application of the outline, create a trial site by filling out this form.

If you setup your own WorkEngine site collection, use this reference table to associate the types of information you need to participate with other commonly used names:

Also known as
Server Name
Data Source, Connection String
EPM Live Hosted:,42069
In-house/Onsite:  *per  IT policy
Database Name
Initial Catalog, Database
EPM Live Hosted: _RPTworkenginesite
In-house/Onsite:  *per  IT policy
Username, User ID
EPM Live Hosted: RPTworkenginesite
In-house/Onsite:  *per  IT policy
Passwords are passwords…
EPM Live Hosted: *randomly generated
In-house/Onsite:  *per  IT policy

Connection String Information: Reference Table

Here is the practical outline that I would recommend to get started with extracting data.

Open Microsoft Excel 2010 to begin.

Go to the Data tab. Look for the From Other Sources button and choose From SQL Server on the menu.

Initiate the Data Connection Wizard

Enter your database Server name and the Log on credentials when prompted.

<pThis example uses the online database server name:,42069, and a demo site name:

Data Connection Wizard: Connect to Database Server

Notice the available databases in the next dialogue box, which is based on the server you entered. Pull the drop down menu and select your database name.

The list that is populated in the larger window, once you select your database name, is a list of all the tables now available for that specific database. Find the one that contains the data you want to extract.

For my EPM Live customers: Here is a summary of our most popular EPM and Application Management template list names and corresponding table names. There is a link at the end of this post for you to download Reporting Starter Kits that leverage some of these lists.

Default WorkEngine List Name
Corresponding Table in Reporting Solution
Project Center
Task Center
My Timesheet
Test Cases

>EPM Live Customers: Popular WorkEngine template list table names

I’m using the second part of the “Your Connection String” bullet from the EPM Live Online Services email to select the database name, and also choosing the WorkEngine Timesheet table.

If you don’t Connect to a specific table during this step, you will be prompted to select the table you want to extract when the data connection is refreshed.

Import Data kicks off after the Data Connection Wizard closes

Data Connection Wizard: Select Database and Table

Side note (from above screenshot) on RPTLog, RPTPeriods, RPTResLink, RPTSettings, and RPTWork: these tables are part of the WorkEngine Reporting Solution base schema, which has a point-in-time snapshot capability. Ultimately, the solution makes trend reporting across the platform successful, as it could include any SharePoint list. See the links at the end of this post for more info on the WorkEngine Reporting Solution.

The last screen for creating the Data Connection has a lot of detail. I will explain these areas one by one:

Data Connection Wizard: Save Data Connection File and Finish

  1. File Name – these file types always use the extension ODC (for Office Data Connection). If you are storing the file locally, use as many words as you need to in order to have a clear description. If you are planning to store the file on a SharePoint site, consider that the file name will be part of the URL – you may want to make it as short as possible while remaining descriptive.
  2. Browse – save this file to a location other than the default. The Windows 7 default location is C:Users[username]DocumentsMy Data Sources.
  3. Save Password in file – a handy option for connections using SQL authentication, but this option will warn you if you don’t have encryption, which may or may not be a security requirement at your company. Check with your IT group if you are not sure.
  4. Description – standard Multi-line text field. 255 character max. Write a description that explains how the data connection is meant to be used. That way you can remember why you set it up later!
  5. Friendly Name – in my experience, this comes into play when you import data as a table, and then reference the table in the same (or another) workbook. Excel will reference the outbound connection to the table by this ‘Friendly Name’ property.
  6. Search Keywords: I haven’t used this but it seems pretty straightforward.
  7. Always attempt to use this file to refresh data – very handy option for deployments where the Administrator is configuring reports for a business on the back-end and then pushing them out for consumption. We will go into that setup in part 2.
  8. Excel Services: Authentication settings… – I may cover these options in a later post. Excel Services is a SharePoint 2010 Enterprise feature, and is optional as part of the architecture for data extraction from a SQL warehouse.

I would highly recommend that you make a note of the configuration options that you chose before you click Finish!



Once you click Finish, the Import Data wizard pops up immediately. You will most likely want to use the connection to generate a Pivot Table or a combo Pivot Table + Chart, but you can do a flat extract as a table as well.

Here is a non-exhaustive list of reasons to import data as a Table:

  • When you need a local copy of the entire database table in your workbook in order to build charts or pivot tables from a point-in-time version of the data
  • When you need to insert a custom formula(s) that should be dynamically calculated based on inserted or removed rows from the source data (which would happen on a refresh)
  • When you need to perform ad-hoc queries of the database table for different views of the information
  • When you need an export of the entire database table for use (import) to another LOB application

Import Data kicks off after the Data Connection Wizard closes

By default the data goes into the Existing worksheet, and looks something like this:

Result of selecting Import Data as Pivot Table and Chart Option

Technically, we are done with what we set out to do at this point, which was to extract data from SQL into MS Office.

But, now that you know how easy it is to unlock that door, I know that what you really want to do is create a snazzy report.

So, bust out a few drag & drop moves, spice it up with a pre-designed MS chart layout……and… there it is! Comin’ in hot… :)

But just in case you need a bit more guidance, here are the references that I used to get savvy when I first started:

EPM Live product customers: you can download WorkEngine Reporting Starter Kits from the EPM Live KB:

Next time, I’ll take a look at the Administrator’s burden in the ad-hoc reporting setup, and lay out how to setup centralized data connections with SharePoint 2010.