Blogroll

Month: March 2012

A couple years ago, when Project Server 2010 was first released, I was sitting in a room with a member of the product team and I asked him what he felt was the most powerful feature of Project Server. With very little hesitation, his response was that Project Server enabled personal and personalized views of key data. With Project Server, it was relatively easy to to generate my own personal dashboard with information that I care about.

Fast forward a couple of years, and that’s still a feature that is very much underutilized in my opinion. Seeing as Mike McLean and I had to gin up a demo of this for the recent Project Conference, I figured I’d go ahead and blog it up.

First off, I will be building off of the two reports that I created in this post. In those two reports, you’ll note that I added the Resource List as background item. This will allow me to hook the SharePoint Current User filter Webpart into the reports.

I am using the dashboard we created in the last post – although a full dashboard is probably not strictly required.

Choosing a Filtering Technique

First off, you are faced with a decision of which filtering technique you’d like to use. There are two that I’ve identified: Customizing the MDX query and using default SharePoint functionality.

To implement MDX filtering, go back to the data source you’re using for your report. Set the connection to include the current user account.

Now go to the report in Dashboard Designer. Click on the Query tab and you should see the MDX query underlying the report.

In theory, at this point, you should be able to edit the MDX query to filter the results on the current user data in the connection string. Here’re the instructions on how to do that in general – although I must admit my MDX skills are not up to the challenge. If anyone wants to try this out and post back the results though, it would be welcome.

The second filtering technique is more my speed, as it doesn’t require any customization. In this example, we’ll use the out of the box Current User Filter functionality. This functionality is dependent on the User Profile Service being active and running.

Applying the Current User Filter

Going back to the dashboard I created in the previous post, I remove the filter that I’d created and redeploy to my SharePoint site.

From here, I select Site Actions and choose to Edit the Page. Then I add the Current User Filter Webpart.

The system is looking for “[Carol Troup]” not “Carol Troup” or “Contoso\CarolT.” To support this output, we need to make two minor adjustments to the settings for the Webpart: Set the output to the resource name, and make sure to add square brackets around the name.

From there, click the filter Webpart dropdown and add the connections to the two reporting Webparts on the page.

Once you’ve completed that, exit from the Edit Page interface and test the results. Here’s what it looks like when I’m logged in as the Administrator.

In this post, I’ll talk about how to add a drop down filter to a newly created PerformancePoint dashboard. This is a follow up to the BI presentation that Mike McLean and I delivered at the Project Conference last week.

So we now have two reports created as PerformancePoint reports. I added the Resource List as a Background item – which allows me to hook into it for any of the filters I may add to the dashboard.

Creating a Filter

Within PerformancePoint Dashboard Designer, select the option to create a new filter.

Out of the available options, I’ll pick the Member Selection option. That pulls the list of available members from the OLAP cube we’re using as the data source.

Select the Resource List as the filtered dimension.

Select the members to include in the filter. You can deliberately select specific resources or right click and select all members.

At the end, the filter should look as follows:

Click Next to identify how you wish the filter to appear.

Rename and save the resulting filter. By default, the filter will be saved to the PerformancePoint content library on the BI Center.

Now it’s time to throw this all together onto a dashboard.

Creating the Dashboard

Creating a dashboard is as simple as selecting the option in the Create tab of the Ribbon in Dashboard Designer.

Select the template for the first page.

This creates a new dashboard in the workspace. Change the name to something useful and save it to create the item in the content library.

Available items appear in the Details section on the right. Drag and drop those items into the relevant section within the dashboard. To pull new items into the workspace, select the Add Lists option in the Home tab.

Select the items to add, i.e. reports that have already been created, and select Add Items to include them in the workspace. These items may now be added to the dashboard.

Going back to the dashboard, I modify the page list to create two pages within the dashboard. I can always go back and edit the dashboard with Dashboard Designer.

Now, it’s a matter of dragging and dropping from the Details items on the right. Note that I added the filter we created to the top row.

From there, all we have to do is connect the Webparts by dragging the correct fields from the filter. That launches the Connection dialog.

Repeat the process for both of the reporting Webparts that we’re adding.

The page is now ready. To deploy to a SharePoint site, right click on the left and select the option to deploy the dashboard. Note that the author must have rights to deploy dashboards to the specific target library.

After it’s deployed, the page should render correctly. You now have a dashboard that may be controlled with the filter criteria defined by the user:

Following up from last week’s Project Server Conference, this was one of the demos that Mike and I did in our second BI session – the one on Wednesday afternoon. In this demo, we showed how you could take much of the standalone Excel and PerformancePoint reports we’d already created and turn those into filtered PerformancePoint dashboards.

Creating the Analytic Grid Report

To show this functionality, I’ll go ahead and create two PerformancePoint reports. To do this, I navigate to the Project BI Center, click on the PerformancePoint content, and then select the option on the Ribbon to create a new report.

After a brief pause, this launches the PerformancePoint Dashboard Designer. Initially, it will ask you to either create a new data source or to use an existing one. In this case, I have a connection to the Portfolio Analyzer cube already configured on the SharePoint site.

By selecting that connection, it is added to the Dashboard Designer workspace.

For the first report, I select the option to add an Analytic Grid.

Then I simply drag and drop the fields from the right into the appropriate sections at the bottom.

This isn’t too tricky, although it’s important to note the purpose of the “Background” section. Those are dimensions that may be filtered on in the future. So while I don’t actually display Resources in the report, I can use that as a hook later on in this post to build filters against – for example providing a resource pick list or automatically filtering the results based on the current user.

This yields a report that looks like this:

Note that there are a couple of quirks in the user interface that are pretty simple but perhaps not immediately obvious to new users:

1) To increase the level of detail, you can right click on the chart element and select the option to drill down. In this example, I can set the report to drill down to the summary task level.

2) As the default display is aggregated to All Projects, it helps to click on the Project List drop down, then select the option to Display All Visible. I take the same approach with the Resource List.

3) As the result set often includes a number of blank rows and columns, and we want the report to automatically resize, it helps to right click on the report itself, and select the options to Filter Empty Rows and Filter Empty Columns.

Take a note of some of the other filter options at the same time. You may consider using a specific value filter for future reporting requirements.

Name and save the report.

Creating the Analytic Chart Report

Now I’ll take the same steps to create an Analytic Chart Report. I go to the Ribbon at the top of Dashboard Designer and select the option to create an Analytic Chart.

I select the same connection I used for my Analytic Table Report.

Then I pretty much drag the same options over to the chart properties in the bottom.

I click the Project List and Resource List dropdowns and select the option to display All Visible.

And finally, I apply filters on blank series and axis elements. This will allow the chart to automatically resize whenever a new selection is made in the filter.

Regular readers of this blog may recall a series of posts from January where I discussed how to use VBA to store timescaled BCWP values in the Baseline10Cost field. At the time, I’d mentioned that the same technique could be used to store other custom timescaled data, for instance, taking a snapshot of milestone variance at each status date.

As I had to develop this solution for my presentation with Mike McLean at the recent Project Conference, I figured I’d go ahead and share the solution.

The Challenge

So the challenge I set myself was to figure out a way to manually trigger the storage of key timescaled data. In this example, I wish to store the variance for each of the project milestones as of the status date. The way I envision this would be that the project manager would update the schedule each week, then trigger the macro that takes a snapshot of the data.

The Macro

The VBA actually proved to be quite simple. While there may be a performance hit, as I am only copying data into a single timescaled cell, it didn’t seem noticeable.

What this does is copy the variance into the Baseline10Cost field. Now that’s a bit of a hack, as we’re copying a number field into a cost field. As there are no timescaled number fields, I figured it would still work – and it does.

To use, simply set the status date, then trigger the macro. Navigate to the Task Usage view and add the Baseline10Cost field to confirm.

The Query

Once the project has been published, create an Excel report from an ODC configured with the following SQL query:

Continuing with the linkfest from my previous two posts as the wrap up from the Project Conference this week in Phoenix.

In this post, I’ll include links to the instructions for demos for PerformancePoint, PowerPivot and VBA Trend Analysis. Note that some of these links may not yet be active – but will be in the next several weeks.

PerformancePoint

PerformancePoint is another one of those underutilized tools in the Project Server reporting tool set. In our presentation, we showed how to create a PerformancePoint report, how to decompose it, and how to incorporate it into a filtered PerformancePoint dashboard.

For a technical overview as well as a link the official white papers on the topic, please check out this site.

See below for additional information on the specific functionality demonstrated at the conference.

2. Creating Personalized, Filtered Dashboards in PerformancePoint with Project Server data. Ever wonder how you can create dashboards and have them automatically filter based on the current user? Check out these posts (Part 1, Part 2 & Part 3).

Trend Analysis with VBA

One of the often heard requests around Microsoft Project and reporting is the ability to perform trend analysis over time. OLAP cubes will generally meet your needs for cost or effort based reporting, but trend reporting of items such as EVM or milestone variance has traditionally been a challenge.

Solutions typically include a custom SQL routine that takes snapshots of project fields or a third party tool such as UMT’s Project Essentials suite that lets users configure the fields using a simple UI.

For our demo the other day, I figured I’d demonstrate another technique. In that demo, we used VBA to record data in a spare Baseline10Cost field, then Excel Services to generate a report. Effectively, we’re creating custom timescaled data fields.

1. Enterprise BCWP Reporting (Part 1, Part 2, Part 3, Part 4) – although I should point out to those folks who attended my EVM presentation on Thursday that technically speaking, keeping historical BCWP in Project as an editable field isn’t really compliant with formal EVM reporting requirements. For something like that, a locked down SQL or third party solution is probably more appropriate.

2. Milestone Variance Analysis – here’s a solution to take a snapshot of milestone variance and store it in a spare Baseline Cost field – then extract into a useful report.

External Lists

1. Reporting on Risks with External Lists (Part 1 & Part 2). This walks you through how to use Project Server Risk data to generate a dynamically updated, conditionally formatted SharePoint list. Here’s a walk through using the same approach to generate a list of projects.

PowerPivot

1. Reporting with PowerPivot and Project Server 2010 – in this demo, Mike showed how to pull Project Server data into Excel – then combined the data with offline resource rate calculations. Finally, he showed how to use PowerPivot to aggregate Risk and Issues data across project workspaces. Watch the Project Team Blog over the next couple of weeks for that post. (I’ll probably come back and update this post when that gets published.)

Thanks again to all of the folks who attended the sessions I presented with Mike McLean at the Project Conference. This post is a follow up to those presentations, and includes links to additional reference material as well as links to instructions on how to recreate the scenarios we demonstrated.

Feel free to add feedback at the bottom… I’ll make sure that it all gets shared with Mike.

Note that the Excel reports listed below depend on the ODC files that I mentioned in the last post.

I also point out that if you’re deploying Excel Services, chances are that you’ll eventually end up on this post from Brian Smith listing common configuration errors. I figured I’d save you all the trouble and just provide it here.

4. Cumulative Task Completion. I’m not providing instructions on this one, but did throw the query into the ODC file that can be downloaded above. Once you get the data into Excel, it’s just a matter of setting the PivotTable to show cumulative numbers and you’re good to go.

REST API

The REST API and Project Server has been covered both here and here. The general gist is that if you make note of the specific chart elements when they’re posted in Excel Services, you can generate a URL that will go into the Excel report and generate a view of the chart or table.

In a report called Resource Report posted to the demo Contoso library, you would end up with a URL that looks like this:

Note how the spaces are swapped for the %20 code. Here’s a more technical overview with links to more information. You can even pass parameters through the REST API – for instance, pushing a resource name into a calculated workbook and displaying the results.

The first two links in this section talk about how to embed a REST-generated chart in a Word document. One of the tricks I learned for this presentation however, was how to enable a dynamically updated chart in PowerPoint:

Once you have the URL defined. Test it out in the browser. Confirm that the chart appears.

Open PowerPoint. Select the option to Insert a Picture. Paste the URL in the dialog box.

Here’s the trick. Instead of clicking Insert, hit the drop down.

Link to a File will cause the image not to render if the deck is opened offline. Insert and Link will actually insert an image, but will refresh it if the deck is opened while connected to the network.

Thanks again to all of the folks who attended the sessions I presented with Mike McLean at the Project Conference. This post is a follow up to those presentations, and includes links to additional reference material as well as links to instructions on how to recreate the scenarios we demonstrated.

Feel free to add feedback at the bottom… I’ll make sure that it all gets shared with Mike.

The General Stuff

First off, the obligatory BI poster. This is the latest link to Microsoft’s one page overview of the latest BI options – and more importantly how to select an appropriate one.

Next, we have the also-obligatory shameless plug for my own content. Here’s a little white paper I wrote last year that walks you through the various reporting options available. It probably needs a refresh, but is still a good resource – although as the author, I am hardly unbiased in that opinion.

The Project Server Content Pivot. Amaze your friends with this vision of Technet next. No, really, this is just a slick way of collecting all of the Project Server into one slick package. Lots of reporting goodness posted here.

The Microsoft BI Center. Take a look at this list of resources and case studies to get ideas of how to use many of the tools that have probably already been deployed within your organization.

ODC Files

As we discussed in the presentation, Office Data Connection files are simple text files that tell Excel or Visio how to connect to the source data. In the examples we presented, those ODC files were generated on the demo Contoso image using SQL queries.

Visio Services

(Note that some of these links reference blog posts in the queue that have not yet been published. If the link doesn’t work today, it will within the next week or so. If it doesn’t work by mid-April or so, please let me know.)

2. Developing a portfolio timeline view in Visio Services. (Part 1, Part 2, Part 3). Note that the Visio template may be downloaded here. Make sure to follow the instructions for getting it set up though.