EPMSource

In 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.

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.

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.

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

The final step before building the PowerView was to save the PowerPivot back into the gallery in 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.

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 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.

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.

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 !!

Over the past week or two, Microsoft have been quietly uploading all the sessions from the recent Project Conference held in Phoenix to the Project channel on Microsoft Showcase. This morning the Project team officially announced the availability, so I am pleased to announce that the video of Mark McDermott’s and my session is now available for your viewing pleasure. I encourage you to watch it and let me know if you have any questions.

Finally, the session deck, including notes is available for download at slideshare.net.

Project Server has a fantastic reporting story, with a dedicated reporting database containing pretty much every piece of data you put in, and a myriad of ways to retrieve, tabulate and visualize that data for the end user. With a little customization or configuration it is possible to build reports that will show the status of the project portfolio, risk and issues and just about anything you want to see. With such powerful reporting it can quickly become difficult for a Project or Portfolio manager to identify the projects or information they are interested in seeing.

Enter Reporting Services Data Alerts, a new capability in SQL Server 2012 that allow users to create specific alerts when a data condition in a report occurs. For example, an organization could have a specific report that highlights all risks across the project portfolio, however the portfolio manager may only be interested when one of their projects is on the report with risks of high or critical. With a SQL Server Reporting Services Data Alert, the portfolio manager could create a data alert on the report that will would automatically send a copy of the report to them when the conditions are met.

Setting up a data alert is incredibly simple. To start with, run the report that you wish to set up the data alert on. In this example I have a very simple report that shows the number of extreme risks across all projects in the portfolio.

On the actions menu, select new data alert (If you are trying this yourself and the New Data Alert option is disabled, then you may need to set modify the report dataset to use a shared data source with stored credentials).

A dialog will pop up showing the different components of the report and allowing the user to define the different conditions that will cause the alert to fire.

Finally, the user can configure how often the data alert will poll, ranging from minutes up to months, and where to send the alert.

The output from the alert is a nicely formatted email containing the report, details of the rules used to generate the alert and links back to the underlying report.

Reporting Services also provides a mechanism for a user to view all the data alerts they have
configured as well as to modify, delete and run data alerts outside of the schedule.

All in all this is a pretty handy capability to provide your users should you be running SQL Server 2012.

Back in 2011 I sat in a session at TechEd Australia where Carolyn Chau showed off a number of the reporting enhancements coming in SQL Server 2012. SQL Server 2012 is a massive release, providing a stack of new features across all aspects of the server . Some of my favourites include things like Availability groups, support for Server core, a new Visual Studio based management to name but a few. I remember coming out of that session excited at the thought of plugging in the Project Server reporting database and seeing what it could do.

Fast forward to March this year and Microsoft officially confirmed that SQL Server 2012 was supported for SharePoint and Project Server SP1 upwards, so what better time to spin up a farm and give it a whirl?

So coming up in the next two posts I am going to look at two of the new Reporting Services features and how they can be leveraged in your Project Server farms. In the first post we are going to look at SQL Server Reporting Services Data Alerts and in the second, PowerView (formerly known as Project Crescent).

A while back I wrote about how you could customise your Project Server instance to incorporate one of the great new features of Internet Explorer 9, Jump Lists. In the post we explored how through a little bit of JavaScript and a content editor web part a Project Server administrator could provide quick access to common links in PWA when a user pins the site.

As I had been spending a lot of time around demand management workflows recently, I started to wonder if it would be possible to incorporate workflow approval tasks onto an IE9 jump list. After a little experimenting, I am pleased to announce IE9 Jump Lists Version 2.0

What does it do?

Just like the previous release, V2 provides quick access to core Project Server functions for users that choose to pin the site to their Windows 7 taskbar. Unlike the previous version, it will also poll Project Server to see if any workflow approval tasks are outstanding for the current user, specifically any tasks that are not in the status of completed and add them to the pinned menu in a special ‘Workflow tasks’ area.

Clicking on an approval task form the jump list will open up the approval task within Project Server for the user to action the approval.

Finally, V2 will also notify the user via a handy notification icon to show when there are outstanding workflow approvals awaiting action.

To give you a more detailed example of what V2 can do, I have created a small screencast showing the major features.

The new version of the IE9 Jump lists leverages a fantastic jQuery library called Pinify that is designed to reduce the effort and time required to implement IE9 jump lists on a site as well as providing a number of additional capabilities including notifications, discoverability and thumbnails. In our particular case, I have leveraged pinify to provide the static jump list components and to provide the plumbing to display the dynamic workflow tasks and notifications.

The other main components of the solution are:

• Leveraging the SPServices jQuery plugin to poll SharePoint to get the currently logged on user
• Using SharePoint’s inbuilt REST interface to lists to poll the currently assigned Workflow tasks from the ProjectServerWorkflowTasks list
• Parsing the returned results via JSON
• And finally, displaying a notification to the user on the task bar when Workflow Approval tasks are outstanding.

I am not going to go into the specifics of the implementation in this post due to the solution being slightly more complex than its predecessor. Instead, I have wrapped the whole solution, associated graphics and JavaScript files into a simple to install SharePoint feature, the source of which and Visual Studio project is available to download from my Skydrive account. In order for you to use this yourself, all that is needed is to:

1. Ensure you have a Shared Documents document library in your PWA site

2. Open the ie9jumplist.js file in the solution and update the PWA URL and ProjectServerWorkflowTasks list id to match your environment

3. Compile, test, deploy and enjoy.

N.B. This code is provided as is. No warranty is provided. You are responsible for testing the solution prior to deployment. The author accepts no responsibility for anything that may happen as a result of installing and using this code, etc, etc.

Today Mark McDermott and myself presented our session on Building advanced Project Server workflows with Nintex Workflow for Project Server. The session covered three main areas where organisations can leverage Nintex workflow within their Project Server environments.

Demand Management – During the session we looked at some of the more advanced scenarios for building demand management workflows, leveraging some of the Nintex out of the box actions such as state machines and flexi tasks. We also looked at using some of the new V2 features including the Query Project Data action that allows the workflow to query the PSI directly from within the workflow.

Event Driven – We introduced the new capability of Event Driven workflows allowing you to attach a Nintex workflow to a Project Server Server Side Event providing a huge array of new integration and extensibility options for your Project Server farm.

Project workspaces -Finally, we showed how workflow could be provided in project workspaces to support a project team with a number of activities including approvals, routing and custom notifications. We also showed how easily workflows could be extended to integrate with cloud based services such as Office 365.

Try it yourself

We wanted to make sure that you could try everything that we showed in the session yourself, so spent some time building up three detailed ‘how to’ guides each covering a different area. Links to these guides as well as further information on Nintex Workflow, Nintex Workflow for Project Server 2010 and how to obtain trial copies are available from http://bit.ly/pc332.

A while back I posted about how you can change the format of dates in Project. The post showed how easily you could change the format of the dates within the main data entry window of Project. One of my readers pointed out that this was not reflected in the Gantt chart bat text automatically, as you can see below.

Changing the format of these dates is just as simple as changing the other date formats. Either right click on the Gantt chart to bring up the context menu and select Layout.

Or on the Gantt Format ribbon choose Layout.

This will bring up a dialog where you can customise how links are displayed between tasks, bar height and most importantly, the format of any dates on the various bars.

Select the format you want and click ok. Your Gantt bars will now reflect your chosen format.

Last week I was lucky enough to be interviewed by Dux Raymond Sy about my upcoming session at the Microsoft Project Conference which I will be presenting with Mark McDermott of Nintex. Mark and I have been busy putting the final touches to the presentation and demonstrations and have a really great session planned.

Our session is on Thursday 22nd March at 10:30am in room North 222 C. If can’t make it, or even if you can, make sure you follow the twitter hash tags #pc332 and #mspc12 to keep up with the action.

It’s been a little quiet on the blog for the last couple of weeks, the reason is that I am head down preparing for my session at the upcoming Project Conference in Phoenix. I am presenting with the Vice President of Sales for Nintex, Mark McDermott on Building Advanced Project Server Workflows with Nintex Workflow for Project Server.

The session will go take you through some of the more advanced concepts with Nintex Workflow and how they can be related in your project server workflows, we will also be touching on some of the new components of version 2.0 of the product and how you can leverage them in your Project Server installation to improve project governance, delivery and accountability. 

For those of you that haven’t registered yet, what are you waiting for? There are over 90 sessions being delivered from a mix of partners, mvp’s, customers and the product team.  Registration is still open and available on the site.

If you are coming, our session is on Wednesday 21st, at 5:15pm, the last session before the party, so what better way to start your night at the Arizona Science Center than an hour of workflow immediately prior?  

If you’re not coming, keep an eye on twitter for the hash tags #mspc12 and our session #PC332 for more links to great information.

A user in the Project Server forums asked a question this week about whether it was possible to add printing capability to the Project Detail Pages in order to allow the ‘forms’ to be printed out as there is no print button available on the ribbon.

Out of the box, the printing capability in the Project Web Application is limited to the grids, including the schedule grids that are visible in a schedule PDP and uses a custom page to render the information as can be seen below.

After a little experimenting with the Internet Explorer printing, it seems that PDP’s can be printed quite well directly from the browser, so I thought I would try and pull together a ribbon feature that enables PDP printing.

The feature consists of XML button definition and a little piece of JavaScript to determine if the button will be enabled or not. This is important so that the button can be disabled when the user is on the schedule page that has it’s own grid based printing function like the picture above. For the purposes of this feature, I have gone the quick route and do the check based on the name of the page, in this case if the page is called schedule.aspx the button will be disabled. You may need to modify this check if your schedule based PDP(s) have a different name.

The element.xml looks like this:

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
	<CustomAction Id="Ribbon.Tabs.PDP.Home.Print"
				  Location="CommandUI.Ribbon">
		<CommandUIExtension>
			<CommandUIDefinitions>
				<CommandUIDefinition Location="Ribbon.Tabs.PDP.Home.Groups._children">
					<Group Id="Ribbon.Tabs.PDP.Home.Print"
						   Sequence="60"
						   Description="Print Custom Group"
						   Title="Share"
						   Command="EnableCustomGroup"
						   Template="Ribbon.Templates.Flexible2">
						<Controls Id="Ribbon.Tabs.PDP.Home.Print.Controls">
							<Button Id="Ribbon.Tabs.PDP.Home.Print.PrintPDP"
									Sequence="40"
									Command="PrintPDP"
									Alt="Print"
									Image16by16="/_layouts/$Resources:core,Language;/images/ps16x16.png"
									Image16by16Top="-96"
									Image16by16Left="-160"
									Image32by32="/_layouts/$Resources:core,Language;/images/ps32x32.png"
									Image32by32Top="-288"
									Image32by32Left="-128"
									LabelText="Print"
									TemplateAlias="o1"
							        ToolTipTitle="Print"
									ToolTipDescription="Print the contents of the PDP"/>
						</Controls>
					</Group>
				</CommandUIDefinition>
				<CommandUIDefinition Location="Ribbon.Tabs.PDP.Home.Scaling._children">
					<MaxSize
						Id="Ribbon.Tabs.PDP.Home.Scaling.Print"
						Sequence="140"
						GroupId="Ribbon.Tabs.PDP.Home.Print"
						Size="LargeLarge"/>
				</CommandUIDefinition>
			</CommandUIDefinitions>
			<CommandUIHandlers>
				<CommandUIHandler Command="EnableCustomGroup"
								  CommandAction="javascript:return true;" />
				<CommandUIHandler Command="PrintPDP"
								  CommandAction="javascript:window.print();"
								  EnabledScript="javascript:
								  function disablePrintForSchedule() {
									var sPath = window.location.pathname;
									var sPage = sPath.substring(sPath.lastIndexOf('/') + 1);
									if (sPage.toLowerCase() == 'schedule.aspx')
									{
										return false;
									}
									else
									{
										return true;
									}
								   }
									disablePrintForSchedule();"
/>
			</CommandUIHandlers>
		</CommandUIExtension>
	</CustomAction>
</Elements>

The process of building the feature is exactly the same as outlined in http://epmsource.com/2011/12/13/hiding-disabling-ribbon-items-in-project-server-part-ii/ but substitute the above code into the Element.xml file and change the feature name accordingly.

Once the feature is deployed and activated a Print button should available on the PDP ribbon that when clicked will invoke the Internet Explorer print dialog.

From my preliminary testing, the output is pretty good, as can be seen from this ‘Print to XPS’ below:

I have uploaded the full source to Skydrive which can be downloaded from the link below.

Project PDP Print Page

Happy PDP printing