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

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.

Reporting and Enterprise Reporting: Which SharePoint 2010 Business Intelligence Approach is Best?

Reporting solutions range from complex, technically challenging infrastructure types to simple, ad-hoc reporting from a client-based application. Often, the most difficult aspect of selecting a solution is the process of understanding what the organization really needs.

The success factors of implementation are basically three-fold:

  1. The level of commitment and sponsorship of the project from senior management
  2. The level of business need for creating a BI implementation
  3. The amount and quality of business data available


In this post, I have analyzed some of the most popular and most common reporting solutions for SharePoint 2010, mentioning both benefits and challenges. An organization may need one, some or all of these reporting solutions to meet their enterprise needs. In practice, all of these solutions are compatible in an infrastructure properly designed with the whole in mind and can be deployed in totality.

Summary of Reporting and Enterprise Reporting Solutions:

SharePoint Reporting Options WorkEngine Reporting Solution Benefits Description
SharePoint Web Part Pages/Dashboard
WorkEngine Chart Web Part Template Pack:
Low-impact to infrastructure
Leverages SharePoint Out of Box web part pages, high flexibility
Commonly tracked project information and will save you valuable time with the ability to place them in any Web Part Zone in your WorkEngine application.
Microsoft Office (Excel, Access) connected directly to a reporting database
WorkEngine Excel Report Packs:
General Instructions for setting up Excel to connect to WorkEngine Reporting Database:
WorkEngine Preconfigured Excel Reports:
WorkEngine Preconfigured Timesheet Reporting:
Low-impact to infrastructure (usually little to no change required to setup)
Report design is easily modified and updated
Commonly used Excel based application for managing and reporting
Web-based Microsoft Office (Excel, Access) rendered through SharePoint WorkEngine Excel Report Packs:
General Instructions for setting up Excel to connect to WorkEngine Reporting Database:
WorkEngine Preconfigured Excel Reports:
WorkEngine Preconfigured Timesheet Reporting:
Medium-impact to infrastructure, requiring technical resources to implement
Report design is easily modified and updated as organizational changes flow down
Reports are centralized through technical design and maintained as part of the SharePoint farm
Web-based SQL Reporting Services reports stored on SharePoint WorkEngine SSRS Report Pack:
High-impact to infrastructure, requiring specialized technical resources to implement
Report design is tightly controlled
Reports are centralized through technical design and maintained as part of the SharePoint farm
Reports performance is based on SQL server stability and performance, which can handle millions of rows
Web-based KPIs and dashboards using PerformancePoint in SharePoint Performance Point integration with WorkEngine Reporting Database
WorkEngine Reporting Database Info:  Please review section 7 of our Administration and Configuration Guide.
High-impact to infrastructure, requiring specialized technical resources to implement
Report design is tightly controlled
Reports are centralized through technical design and maintained as part of the SharePoint farm
Reports performance is based on SharePoint server stability and performance, and can handle millions of rows
No end-user training is required to consume the reports, and they are generated automatically

Weigh the technical and organizational challenges, your business requirements and the three success factors mentioned upfront to discover which reporting solution will work best for you.

You can always start with the most simple, extensible option and grow into a more complex solution over time. At the very least, I would recommend to first try the solution with the lowest initial investment (whichever one that is for you) for a period of time, and then do a re-assessment on whether the organization’s business intelligence needs are satisfied.​

WorkEngine's January Tips, Tricks and Best Practices

Need Project Requests?

Use the WorkEngine Project Requests feature to efficiently and effectively capture, classify, evaluate, and appropriately prioritize projects within your organization.  This feature resides at the EPM WorkEngine top level site and serves as a repository for all project requests associated with the project portfolio.  See below for step by step instructions to begin using Project Requests today:
  1. Navigate to the Project Requests list in your EPM Live WorkEngine site collection and select the New Item button.  Complete the fields that are applicable to your request and click the Save button.  Your new project request will be added to the Project Requests list.
  2. Initiate the out of box Project Approval workflow or create your own incorporating your internal processes.
  3. Use one of the many views pre-configured for you to review status and analyze data for project selection.
    1. All ‘Proposed’ projects:
    2. The entire Project Portfolio (i.e., project requests, active projects, etc.).
    3. Detailed Bubble Charts assists in evaluating Risk vs. Strategic Value of proposed as well as active projects.
    4. Upon approval of a Project Request item, create your project workspace with the project data you’ve been adding and updating in the request item throughout the review and approval process.
    5. Your new project workspace is created.
Resource Pool Out of Control?

Use the Build Team feature in WorkEngine to group, sort, and filter the Enterprise Resource Pool to render only the type of resources required for the project.  This feature is ideal for organizations that have large resource pools.  See below for details on how to use this time saving feature.

  1. From your EPM Live project workspace, navigate to Site Settings ànd Build Team and select the Add Users button.
  2. The entire list of resources from the Enterprise Resource Pool will load. From here the Project Manager can group, sort and filter to narrow the list down to render only resources required for the project.  See example below.

WorkEngine's Feature Spotlight

In addition to WorkEngine’s core functionality, WorkEngine has many features that deserve the spotlight from time to time.  This month’s spotlight includes the following features and functionality that add even more value to your SharePoint platform:

  • Work Log:  WorkEngine’s Work log allow team members to enter their Timesheet data from any SharePoint list item.  For example:  If a team member is working on an issue, task or a even service request stored in a SharePoint list, that team member no longer has to leave that item and go to their timesheet to enter how many hours they have worked on that day, they can now simply click the Work Log link in the SharePoint item and enter their time for the day.  They will then see these hours show up in their timesheet when they are ready to submit for approval.
  • Resource Tools: WorkEngine 2010 provides visibility into resource demands across the organization so functional managers can make staffing decisions to support corporate goals and objectives. Understand which projects and types of work are consuming your most valuable resources for effectively and proactively aligning your staffing plan based on changing priorities and budgets.  As you are assigning resources to any item in SharePoint, make sure you use the “Check Resources” or “Find Resources” feature to view availability and current assignments.
  • My Work Reminder Web Part:  My Work is a WorkEngine feature that allows users to view all their work from one centralized location.  The My Reminders Web Part is designed to display the status of such actionable items rolled up from all sites where the work items were created.  The My Reminders Web Part renders the following information on the My Work home page (and can be easily configured to include additional list data as well):
  • Tasks: New Tasks, Overdue Tasks
  • Issues: Active Issues, Overdue Issues
  • Risks: Active Risks, Overdue Risks
  • Actions: New Actions, Overdue Actions

How to Use Manually Scheduled Tasks in Microsoft Project 2010

As you may have heard by now, Microsoft Project 2010 has introduced manually scheduled tasks.  For experienced Microsoft Project users who have ever found yourself scratching your head trying to figure out why your schedule insists on behaving in a manner that was not approved by its owner (we’ve all been there), or for those of you who are new to the scheduling world and need a little flexibility in your scheduling to get your feet wet, this feature will come as a great treat!

When setting up a project schedule you will want to set the Task Mode to either Manually Scheduled or Auto Scheduled.  If you choose not to specify an option Microsoft Project 2010 will automatically create all tasks for your project in the Manually Scheduled mode.  You will also be able to change the setting at the task level by clicking on the task, clicking on Task Mode and then manually changing the setting for that specific task.

If the Task Mode Column is visible in your project schedule you will be able to view via a specialized indicator which Task Mode each task is in.  If a task is in the Manually Scheduled Task Mode, you will be able specify your own values for Duration, Start and Finish.  You will also be allowed to enter other textual data in those fields such as “approx. 1 week” or “TBD”.  You can also choose to leave the fields blank until you have the appropriate information available.

If you choose to link Manually Scheduled tasks to other tasks in your schedule, Microsoft Project has multiple scenarios for determining how the task will be scheduled. If you enter a valid Duration date into your Manually Scheduled task it will leverage the Duration date you entered and update the Start and Finish date accordingly.  If you don’t enter a valid Duration date the system will default to a Duration of 1 day and then calculate the Start and Finish date from there.

So as you can probably see there are many different scenarios that apply to the Manually Scheduled Task Mode.  Many of which I did not cover above but I will continue to supply tips and tricks to help you familiarize yourself with Microsoft Project 2010.  There is defintiely a lot of functionality to look forward to!