Where has Close tasks to update gone in Project Server 2013?

Close Task to update was an interesting feature that was used by the Project Manager to stop the selected tasks from showing up in resource task and timesheet views, therefore stopping users from recording time against those tasks.

Close Tasks to Update in Project Server 2010

In Project Server 2013, the close task to update capability has changed, to leverage a new ‘Locked’ capability which behaves in a similar manner, basically you can choose to lock a task through PWA and once locked and published, the task will no longer show up in the Task and Timesheets views.

To use the locked field you first need to add it to a view, in this case I have added it to the Task Summary view for a Project.

Task Summary - Add locked

When you open a project and choose that view you will see the new Locked field on the right hand side. By default all tasks will be set to ‘No’.

Project Server 2013 - Locked field

To lock a task, edit the project and change the value.

Add tasks to timesheet - after locked

When you publish the schedule the tasks that are locked will no longer appear in the task and timesheet views as can be seen in the table below.

Before After
Add tasks to timesheet - before locked Capture4

SQL Server 2012 & Project Server, Part 2– Business data visualisation with PowerView

PowerViewIn this second post looking at some of the new capabilities of SQL Server 2012 and how they can be leveraged in your Project Server farm, we are going to look at PowerView.

PowerView was originally announced as Project Crescent and has wowed pretty much anyone that has seen it. In essence, PowerView allows you to build visualisations of your data using a simple drag and drop interface that are ‘Presentation Ready’ meaning there is no nasty wiring up of data, simply drag your data onto the surface and being analyzing it.

PowerView can leverage data from two types of source, a Tabular model in SQL Server 2012, or a PowerPivot workbook stored in Excel. Of the two, the latter is by far the easiest to get up and running.

To start with I am going to assume you have set up your farm to leverage PowerPivot, this requires adding a special ‘PowerPivot’ analysis services instance to your farm and then configuring the farm to use it. The whole set up experience has gotten better in SQL Server 2012, but it’s still fiddly requiring a wizard to be run and a number of content types to be manually added to the relevant reporting library. There is a great video taking you through the whole process here , including how to set up the server side and the Excel client PowerPivot add-on.

In my case, I was keen to try out PowerView against Timesheet data mainly because I worked at an organization that used timesheets extensively and was familiar with the data model and some particular reporting use cases I wanted to try.

To start with we need to import the Project Server data into PowerPivot. To do this, open up the PowerPivot for Excel add-on and choose to import from a SQL Server Database.

Get data from SQL Server


A table import wizard will be shown, configure it to point at your Project Server Reporting database and click next twice, you will then be presented with a list of all the tables and views available in the database to use for reporting. As I am interested in timesheet info, I am going to pull in some timesheet only tables and a couple of the generic _userview tables.

Importing into PowerPivot


PowerPivot will then proceed to import the tables and this is where the really clever stuff will start, in most cases PowerPivot will be able to automatically infer the relationship between the tables, but as I have chosen to pull in the EPMProject_UserView and EPMResource_UserView tables I need to help it with the relationships.


PowerPivot Relationships

Creating a relationship is pretty easy, simply choose ‘Create Relationship’ from the design ribbon and select the relevant tables and joins.


Manually create relationships

The final step before building the PowerView was to save the PowerPivot back into the gallery in SharePoint.

PowerPivot uploaded into SharePoint


Once the PowerPivot Excel spreadsheet is in SharePoint we can start to build the PowerView by clicking on the PowerView icon which will open the designer.

Create Power View Report

The designer will show a design surface on the left and a list of entities on the right hand side. To start building the PowerView simply select the relevant information on the entities and drag it onto the design surface.

PowerView Designer

PowerView will automatically highlight the entities that are related from the relationships either detected automatically or manually set.

Once the entities have been dragged onto the design surface, PowerView allows the user to select the best way to visualize the data, allowing the user to change the layout and visualization method (table, graph or tiles). The PowerView can also be further refined through the use of slicers and filters.

PowerView visualisation

So in about 15 minutes I put the above timesheet analysis PowerView together, showing all resources in the organisation, and a breakdown of Planned & Billable work across the month, week and projects all with a few drag and drops.

The real power of course comes from interacting with the PowerView, so clicking on a resource name will change the data, clicking on the projects of hours breakdown will automatically cause other parts of the PowerView to filter as can be seen below.

Filtered PowerView Visualisation

There are a number of other parts to PowerView that I haven’t explored in this post, specifically things like Play Axis that allows you to see data sets changing over time, which would be interesting to wire up to some of Project Server timephased data, the ability to embed the visualisation into a PowerPoint slide, card and tile visualiations to name but a few. Luckily Microsoft have released an excellent tutorial over at TechNet that takes you through the process of building PowerView from start to finish. If you have SQL Server 2012 installed and are using Project Server 2010, I challenge you to get cracking to wire up some Project data and amaze your executives and PMO !!

Mobile Timesheets on Windows Phone 7

A couple of weeks ago I finally took the plunge and got rid of my iPhone and replaced it with a Windows Phone 7 and promptly upgraded it to the new Mango release. After using it in anger for a while, I can’t believe I didn’t upgrade sooner.

Now why am I telling you this? Well whilst out on the road last week, I forgot to complete my timesheet. My company, like many others out there use Project Server 2010’s timesheet capability to record the time for each project we are working on. The only device I had on me at the time was my WP7, so I thought I would give it a go.

To my surprise, PWA popped up and rendered perfectly in Mango’s IE9 app. Now as I haven’t worked out how to screenshot in WP7, I have opened up PWA in the desktop emulator to give you an idea of what it looks like.

PWA on Windows Phone 7

Zooming in by pinching the screen will let you see more detail. Unlike the iPad that I wrote about previously, moving around the page is very simple and easy to achieve.

Timesheets on Windows Phone 7

If you are in need of a mobile timesheet and task solution for Project Server 2010, then you can’t really go past IE9 in Mango. Nice work MS!