Filtered Project Server Dashboards with PerformancePoint (Part 3)

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.

image

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.

image

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

image

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.

image

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

image

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. 

image

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

image

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.

image

…and when I log back in as Carol Troup….

image

Filtered Project Server Dashboards with PerformancePoint (Part 3)

Filtered Project Server Dashboards with PerformancePoint (Part 2)

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.

image

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.

image

Select the Resource List as the filtered dimension.

image

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

image

At the end, the filter should look as follows:

image

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

image

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

image

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.

image

Select the template for the first page.

image

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.

image

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.

image

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.

image

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.

image

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.

image

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

image

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

image

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.

image

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:

image

Next up….filtering automatically based on the current user.

Filtered Project Server Dashboards with PerformancePoint (Part 2)

Filtered Project Server Dashboards with PerformancePoint (Part 1)

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. 

image

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. 

image

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.

image

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

image

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:

image

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.

image

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.

image

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.

image

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.

image

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

image

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

image

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

image

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.

image

Next up….creating the filter and adding it to the dashboard.

Filtered Project Server Dashboards with PerformancePoint (Part 1)

Storing Custom Timescaled Data with VBA

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.

image

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.

Sub MSVariance()

    'This macro will copy timescaled variance data into the Baseline10Cost field.
    
    Dim TSVBaselineCost As TimeScaleValue 'Capture the dataset for the Baseline10Cost
    Dim TSVSBaselineCost As TimeScaleValues
    Dim T As Task
    
    ActiveProject.StatusDate = InputBox("Enter the Status Date.", "Status Date", ActiveProject.StatusDate)
    
    Application.OpenUndoTransaction "UpdateVariance" 'Create an undo group to remove the changes

    For Each T In ActiveProject.Tasks
        If T.Milestone = True Then
            If T.PercentComplete < 100 Then
                Set TSVSBaselineCost = T.TimeScaleData((ActiveProject.StatusDate), ActiveProject.StatusDate, pjTaskTimescaledBaseline10Cost, pjTimescaleDays, 1)
                For Each TSVBaselineCost In TSVSBaselineCost
                    TSVBaselineCost = T.FinishVariance / 480
                Next TSVBaselineCost
                T.Baseline10Cost = T.Baseline10Cost + 1
            End If
        End If

    Next T

    Application.CloseUndoTransaction  'Close the undo group for the changes.
    
    MsgBox "Milestone variance has been stored in the Baseline10Cost field.", vbOKOnly, "Confirmation"

End Sub

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.

image

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:

SELECT     TOP (100) PERCENT dbo.MSP_EpmProject_UserView.ProjectName, dbo.MSP_EpmTask_UserView.TaskName, dbo.MSP_EpmTaskByDay_UserView.TimeByDay, 
                      dbo.MSP_EpmTaskByDay_UserView.TaskBaseline10Cost
FROM         dbo.MSP_EpmProject_UserView INNER JOIN
                      dbo.MSP_EpmTaskByDay_UserView ON dbo.MSP_EpmProject_UserView.ProjectUID = dbo.MSP_EpmTaskByDay_UserView.ProjectUID INNER JOIN
                      dbo.MSP_EpmTask_UserView ON dbo.MSP_EpmTaskByDay_UserView.TaskUID = dbo.MSP_EpmTask_UserView.TaskUID
WHERE     (dbo.MSP_EpmTaskByDay_UserView.TaskBaseline10Cost IS NOT NULL) AND (dbo.MSP_EpmTaskByDay_UserView.TaskBaseline10Cost <> 0)
ORDER BY dbo.MSP_EpmTaskByDay_UserView.TimeByDay

The Report

Once the data’s in Excel, it’s a pretty easy report to generate.

Configure the Pivot Table so it appears as follows:

image

Add a line chart, and you now have a custom timescaled data report.

image

Storing Custom Timescaled Data with VBA

Project Server Business Intelligence Resources (Part 3)

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.

image

1. Leveraging Decomposition Trees in PerformancePoint with Project Server.

image

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.

image

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.

image

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

…and that’s it for the links.  Bring on the content!

Project Server Business Intelligence Resources (Part 3)

Project Server Business Intelligence Resources (Part 2)

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.

Excel Services

image

1. Developing a Portfolio Stage Gate Report with Excel Services. (Part 1 & Part 2).  Probably a required report for most organizations.

image

2. Developing a Baseline Execution Index Report (Part 1 & Part 2) – this is a great report for showing velocity over time – and work backlogs.

image

3. The Cumulative Milestone Report. (Part 1 & Part 2)

image

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:

http://project.contoso.com/PWA/_vti_bin/ExcelRest.aspx/ProjectBICenter/Sample%20Reports/Resource%20Report.xlsx/Model/Charts(‘Chart%201’)

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.

image

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.

image

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.

Next up….PerformancePoint and VBA for Trend Analysis

Project Server Business Intelligence Resources (Part 2)

Project Server Business Intelligence Resources (Part 1)

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

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. Microsoft’s page of resources on getting started with BI in Project Server.

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.

Here’s a link to a Text file containing all of the queries that we used.  Please test these out in a test environment before deploying to any sort of production hardware.  These are presented as is, with no implied warranties or support.

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

image

  1. Mapping project data to geographic elements in a Visio chart. (Part 1, Part 2 & Part 3)

image

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.

image

3.  Developing a project timeline view in Visio Services.  I lumped both the report above and below in this blog post as they’re essentially the same thing behind the scenes.

image

Next up….Excel Services, PerformancePoint, External Content Types and more….

Project Server Business Intelligence Resources (Part 1)