Ruminating on Progress in 2012

The end of the year is traditionally one for reflection, an arbitrary milestone to look back at what we did right over the last year – and what we did wrong….a time to review what we’ve done to get ourselves closer to our goals.

In that spirit, I’ll close out the year with this article.  I stumbled across it about ten years ago, and I’ve probably re-read it at least once a year since then.  It’s definitely well worth both a read and a moment to reflect.

http://www.fastcompany.com/41704/dr-brilliant-vs-devil-ambition

Happy New Year!  Catch you in 2013.

The Epistemological Year in Review: Top Posts of 2012

As the year tapers off to a close, I figured I’d continue with a tradition and list the top ten posts of the year.   I know it sounds simple in theory, but much like a simple exercise in Business Intelligence and project metrics, I found myself spending an inordinate amount of time trying to figure out just how to calculate what exactly should comprise the top ten list.  As in many circumstances where a client asks a seemingly simple question, this question is actually quite tough to answer.

Hence, following the footsteps of many PMs before me, I engaged in the time honored ritual of taking something that should really be quite simple, and made it much more complicated than it actually deserves.  For those bloggers out there reading this, take this as a gauntlet thrown down to challenge you to come up with an even more complicated blog rating metric in coming years.

First off the list (in alphabetical order).  Following that, for those people who are actually interested, a few notes about the methodology. 

1. Adding Filtered Project Dashboards to PDPs (Part 1)
2. Automatically Filtering Excel Reports in PerformancePoint Dashboards
3. Capturing Custom Timescaled Data in Project Server (Part I)
4. Depicting Detailed Timeline Views with Visio Reports
5. First Look: Querying Project Server 2013 OData with LINQPad
6. Flagging Project Owner Changes with OData and VBA
7. Project 2013 On-Premises: The Missing Settings
8. Project Server Business Intelligence Resources (Part 1)
9. Project Server Business Intelligence Resources (Part 3)
10. Task Paths in Project 2013

Now, a note about the methodology:

I could have been lazy and simply looked at the total number of hits each blog post received over the year – which is what I did last year.  This would have resulted in a front loaded list of top posts, as clearly the posts I published in the beginning of the year would have had all year to accumulate hits, whereas the posts at the end of the year would have only had a few months to accumulate posts.  Hence, option #1 would have been front loaded with earlier posts.

The second option would have been to take all of the posts and pro-rate them based on the total number of hits divided by the total number of working days since the post was published.  This would have been a fair approach – if the overall blog readership had remained static over the year.  But the blog readership did not remain static.  In fact, I’m happy to report that the monthly traffic on this site is up 100% over the same time period in 2011.  Hence, the average daily hit rate per post in the latter half of the year was much higher than the earlier part.  This approach would have meant that the latter 47% of the posts were basically freeloading off of the audience generated by the posts in the first half off the year.  Clearly, that situation would have been untenable.

So I settled on option #3: normalize the number of hits/post (filtered on 2012 posts only) per day; sum the results into a normalized number of hits, and then divide that normalized score by the number of working days since the post was published.  It’s quite simple, really.  Even simpler when you realize that WordPress offers an API to generate the required data. 

I’ll spare you all of the boring details, but I was able to leverage some of my OData querying skills to generate the following URL:

http://stats.wordpress.com/csv.php?api_key=[PERSONALKEY]&blog_uri=https://azlav.wordpress.com&table=postviews&days=-1

This ends up generating our data set, which may be downloaded into Excel.

Once in Excel, I was able to do all of the required calculations using Vlookups and a couple of PivotTables.   For those dedicated readers out there, feel free to provide additional suggestions on how we can take this relatively simple exercise and make it even more extraordinarily complicated in future years.  (Did someone mention Eigenvectors?)

In other words….happy holidays and wishing you the best in 2012 and 2013!

Importing Files to Project Online with VBA

In yesterday’s post, I talked about how to run a script to export all of our Microsoft Project schedules into XML files.  In this post, I’ll provide the script to import these into a new instance of Project Server – either on-prem or online.  Note that this script will not import custom fields – but could be easily adapted to capture and transfer custom fields.

Note also that there are other third party tools that do much of the same thing, but better.

Caveats aside, if you followed yesterday’s post, you should now have a directory called C:\Exports that contains all of the exported project schedules in an XML format.

You should also have a Microsoft Project schedule containing a list of all of the projects you wish to import.

Add the following code…

Sub ImportProjects()

    Dim T As Task
    Dim fName As String

    For Each T In ActiveProject.Tasks
        fName = T.Name
        Application.FileOpenEx Name:="C:\Exports\" & fName & ".mpp", ReadOnly:=False, FormatID:="MSProject.MPP"
        Application.FileSaveAs Name:="<>\" & fName, FormatID:=""
        Application.FileCloseEx pjSave, True, False
    Next T

End Sub

…and run to import the files.  You will probably need to click through the prompts as they appear.

Pair this with a utility to publish all of the projects like the one Alex Burton wrote here and you’ve now moved your projects from one environment to another.

Exporting MPP Files with VBA

Well, I didn’t deliberately intend to get into a VBA rut, but periodically, I like to clean out some of the half written posts I have lying around, and it turns out that I apparently left a couple VBA posts started and not yet finished.  My resurgent interest in VBA of late has also been driven by my efforts to get up to speed in Project Online, and the realization that I can easily use my traditional VBA skills to interact with the Project client, and quickly upload data to my Project Online tenant.

Hence, I wrote this quick VBA script to export all of the MPP files in an existing Project Server instance, and then import the files into another Project Server instance.  And here’s the beauty of VBA and the Project client interface……the code is essentially version agnostic….meaning I could use it to do a dump of all of my existing project schedules (to prep for export or to perform a backup) from Project Server 2010 and then upload them using a different code into an online version of Project Server 2013.

I’ll also note that there are much more robust partner solutions that pretty much do the same thing – but better.  This code is crude, virtually untested, and has only been used in development environments to generate demos.

Identify the Target List

The first step  is to generate a list of the projects you would like to export.  The easiest way for me to do this was simply to grab the list of projects from the Project Center.

I simply exported the list to Excel, and then copied the results into a Microsoft Project file.  I could have simply run the macro from Excel I suppose, but its simpler for me to just use Project.

Note that I would include custom fields as custom fields in the MPP file if I were planning to transfer them over.  In this case, I just want the raw project data.

Add the macro…

Sub ExportProjects()

    Dim T As Task
    Dim fName As String

    For Each T In ActiveProject.Tasks
        fName = T.Name
        Application.FileOpenEx Name:="<>\" & fName, IgnoreReadOnlyRecommended:=True
        Application.FileSaveAs Name:="C:\Exports\" & fName, FormatID:="MSProject.XML"
        Application.FileCloseEx pjDoNotSave, CheckIn:=True
    Next T

End Sub

…and create a directory on the C drive called “Exports.”  We’ll export the project files as XML files into this directory.  Why XML?  It tends to clean up much of the junk that ends up getting pushed into these files over time.  It also removes user customizations to views – which I consider a good thing to do periodically.  Change the export format to “MPP” to export as an MPP file.  Note however that this increases the chance of the import not working properly.

Run the macro.  You may have to click through a couple times if a project opens and throws out a prompt to accept updates, etc.

You should end up with a directory full of XML files.

Next up….the code to take that directory and load it to a new Project Server instance.

Flagging Project Owner Changes with OData and VBA

This question comes up in the forums pretty frequently, and in fact, I’d actually spec’d out a similar procedure for on-prem Project Server installs which involved hitting the Reporting Database to figure out the Project Owner.  I am not sure how useful this code really is, but I was curious about how hard it would be to consume OData values with VBA and then manipulate Project desktop data with the results, and hence spent a couple cycles figuring it out.

In this case, as usual, I owe credit to someone else who did the heavy lifting.  In doing my research, I came across Marcelo Ruiz’s blog where he pretty much did all of the work.  I’d encourage you to check them out:

1. VBA and OData (Part I)
2. VBA and OData (Part II)
3. VBA and OData (Part III)

I basically took his work and repurposed it to work with Project.

The Scenario

Specific fields are available with Project Server but not available within the Project Client.  Most notably, these fields include the Enterprise Project Type field or the Project Owner.  There are a number of scenarios that could be enable through making these fields visible in the Project Client.  For example, I would like to flag the project when the Project Owner has changed, as many organizations forget to change the Status Manager field – and as a result the task updates all flow back to the original Project Owner.

The Solution

In Project Online, we can’t consume the database data, and must instead consume the OData feed from Project Server.

Hence the code below.  It pings the OData feed and returns the value of the Project Owner, then performs two actions:

1. Flags tasks where the Status Manager does not equal the Project Owner.
2. Pops up a message box to let the user know the Project Owner has changed.  (Note this pops up the first time the macro is run on a project, but that would be easy enough to fix.)

Option Explicit

'Copied from Marcelo Ruiz's blog:
'http://blogs.msdn.com/b/marcelolr/archive/2010/02/16/consuming-odata-with-office-vba-part-i.aspx

' References that need to be added:
' Microsoft XML, v6.0
' Microsoft Scripting Runtime

Const ODataErrorFirst As Long = 100
Const ODataCannotReadUrlError As Long = ODataErrorFirst + 1
Const ODataParseError As Long = ODataErrorFirst + 2

'Given a URL, reads an OData feed or entry into an XML document.
'Copied from Marcelo Ruiz's blog:
'http://blogs.msdn.com/b/marcelolr/archive/2010/02/16/consuming-odata-with-office-vba-part-i.aspx
Function ODataReadUrl(ByVal strUrl As String) As MSXML2.DOMDocument60
    Dim objXmlHttp As MSXML2.XMLHTTP60
    Dim objResult As MSXML2.DOMDocument60
    Dim strText As String

    'Make a request for the URL.
    'AL - Note I tweaked the XMLHTTP TO XMLHTTP60
    Set objXmlHttp = New MSXML2.XMLHTTP60
    objXmlHttp.Open "GET", strUrl, False
    objXmlHttp.send

    If objXmlHttp.Status <> 200 Then
        Err.Raise ODataCannotReadUrlError, "ODataReadUrl", "Unable to get '" & strUrl & "' - status code: " & objXmlHttp.Status
    End If

    ' Get the result as text.
    strText = objXmlHttp.responseText
    Set objXmlHttp = Nothing

    ' Create a document from the text.
    Set objResult = New MSXML2.DOMDocument60
    objResult.LoadXML strText
    If objResult.parseError.ErrorCode <> 0 Then
        Err.Raise ODataParseError, "ODataReadUrl", "Unable to load '" & strUrl & "' - " & objResult.parseError.reason
    End If

    Set ODataReadUrl = objResult
End Function

Public Sub CheckProjectOwner()

    Dim PWAURL As String
    Dim ODataURL As String
    Dim objDocument As MSXML2.DOMDocument60
    Dim GetParam As String
    Dim pName As String
    Dim StartTag As String
    Dim FinTag As String
    Dim txtStart As String
    Dim txtFinish As String
    Dim OValue As String
    Dim T As Task

    'Configure the following parameters
    PWAURL = "https://ProjectOnline.sharepoint.com/sites/pwa"
    GetParam = "ProjectOwnerName"

    'Calculate the appropriate strings
    ODataURL = PWAURL & "/_api/ProjectData/"
    pName = ActiveProject.ProjectSummaryTask.Name
    StartTag = "<d:" & GetParam & ">"
    FinTag = "</d:" & GetParam & ">"

    'Ping OData with the query
    Set objDocument = ODataReadUrl(ODataURL & "Projects()?$filter=ProjectName eq '" _
    & pName & "'&$select=" & GetParam)

    'Parse the result set to look for the value
    txtStart = InStr(1, objDocument.XML, StartTag, vbTextCompare) + Len(StartTag)
    txtFinish = InStr(1, objDocument.XML, FinTag, vbTextCompare)
    OValue = Mid(objDocument.XML, txtStart, txtFinish - txtStart)

    Debug.Print OValue

    'Do something with the returned value
    'This changes a flag field if the value doesn't match the status manager

    For Each T In ActiveProject.Tasks
        T.Text1 = OValue
        If T.StatusManagerName = OValue Then
            T.Flag1 = True
        Else
            T.Flag1 = False
        End If
    Next T

    'This compares it with another field to launch a dialog box if it's been changed

    If ActiveProject.ProjectSummaryTask.Text1 <> OValue Then
        MsgBox "The Project Owner has been changed.  You should change the status manager.", vbOKOnly, "Reminder"
    End If

    'Store the current status manager for the next iteration.
    ActiveProject.ProjectSummaryTask.Text1 = OValue

End Sub

Filtering Excel WebParts in Office 365 (Part 3)

Just wanted to close out this discussion with filtered Excel reports in Project Online with a last use case.  In yesterday’s post, I talked about how to create an automatically filtered project dashboard.  In this post, I’ll talk about another common example, creating an Excel report that filters by user.

I’ll use one of the reports I created in yesterday’s post.  This one shows project work, and allows the user to slice by resource name.

I’ll add this via an Excel Webpart to a page in my PWA site collection.  Pretty much the same as the last exercise.  The main exception is that we have a dependency on the User Profile Service being provisioned – which is the case by default in Project Online.

When passing the parameter from the Current User Filter Webpart then, we need to select the Name field, and not the user account field to be passed into the Excel report.

Throw that onto a page and login with a valid user account to confirm it all works…

…and well that’s pretty much it, but stay tuned for how we can have a bit more fun with filtered reports….and VBA…

Filtering Excel WebParts in Office 365 (Part 2)

In this post, I’ll continue discussing how to promote parameters into SharePoint and then automatically filter reports using the SharePoint Filter Webparts.  Note that this solution should pretty much work both in on-prem and online – with the caveat that you might want to construct your data model differently based on the solution you’re using.  Check the last post for guidance on that (and check the bottom of this post to see if things might have changed since I wrote it).

Creating the Data Model (Option 2 – With Filters)

See the chart below to (hopefully) alleviate any confusion…

 

Picking up from the last post, I have two OData connections that I will add to Excel.

http://demo/PWA//_api/ProjectData/Assignments()?$select=ProjectName,ProjectId,ResourceName,AssignmentWork

…and this…

http://demo/PWA//_api/ProjectData/Projects()?$select=ProjectId,EnterpriseProjectTypeName

This time, when I add them to the Excel workbook, I select the option to simply add them as tables.

..which yields a workbook with two tables.

Instead of using PowerPivot, I can then move fields between the tables using a vlookup formula.

…then I create a PivotTable from the table itself by selecting the option to “Summarize in a PivotTable.”   From here, it’s simply a matter of turning the table into a couple bar charts to get something that looks like this…

I’m now going to add a slicer to each chart.  I’ll add a ProjectID slicer to the top chart and a Resource Name slicer to the bottom chart.  Click on the slicers to validate the results.  At this point, I’d also name the charts to ensure we display the correct ones.

I load this report to SharePoint.  When I save to SharePoint, I select the browser options button and configure both of the slicers as parameters.

Displaying in SharePoint

After uploading to SharePoint, we now can display it.  In the first case, we’ll add our Resource Report to a PDP that I’ve created called the Project Dashboard.  I add two webparts….one Query String Webpart and one Excel Webpart.

I configure the Excel Webpart to display the Resource Report.

…then I configure the Query String Webpart to pull the ProjUID parameter from the URL.  The ProjUID is a 36 character identified for each of the projects in the system.

Add a connection from the Query String filter to the Excel Webpart.

…and I now have a filtered report showing project specific data.

…in tomorrow’s post….the same, but filtered by resource name.

Filtering Excel WebParts in Office 365 (Part I)

Following Mike McLean and my triumphant debut as Project Server 2013 BI masters at the recent SharePoint conference, I wanted to document one of the demos – specifically the one where we filtered Excel reports automatically based on external parameters.  I’ve more or less covered this before in Project Server 2010 and PerformancePoint, so if you’re looking for more information, please feel free to check out this link.

This post is focused specifically on implementing the same solution with the Excel Services Webparts, which are pretty much your obligatory go-to option when working in Project Online as PerformancePoint Webparts are not available.

On OData and OData Refresh

The main issue I ran into when trying to filter data sets was around the OData refresh issue.  Specifically, as of the time of this writing, OData refresh is not supported in Excel Services.  That’s not as big a deal as some might think, as I can always just open the Excel document to refresh it in the desktop….or if all else fails, write some VBA to cycle through my reports and refresh them on a daily basis.

Since I’ve already seen some discussion of this in the online forums, I’d point out that I’m pretty confident (as a non-Microsoft employee) that the fact that OData will not refresh in Excel Services is a temporary issue that will get resolved in the future.  Needless to say, lack of online OData refresh is a significant obstacle to establishing parity between the online and on-prem reporting world.

Now how does that impact the specific use case we’re talking about today, i.e. the ability to filter Excel charts dynamically within SharePoint?  As near as I can tell, we can still filter OData based reports – provided they’re not using a PowerPivot data model.  My current hypothesis is that the filter mechanism somehow involves an interaction with PowerPivot which is not at present supported within Excel Services.  Hence, to support this scenario, I’ll use vlookups and other Excel trickery rather than resorting to using PowerPivot and DAX expressions to build my data model.

As a disclaimer, I’ll point out that this post is all based on observations of how the tool works and are not based on in-depth technical analyses of the underpinning code.  I’ll defer to the readers of this blog for that.  I’d also stress that if you’re reading this post more than several months after it was posted, check the documentation to confirm my conclusions are still valid.  If things should change, and I be so motivated, I’ll add a postback in the comments section to any updates.

Assembling the Data Set

Here you have a number of options.  If you’re using an on-prem deployment, you could use the “traditional” method of creating Office Data Connection (ODC) files with embedded SQL queries.  In this case, we simply create the data model as part of the SQL query.  As far as I can tell, we can create reports from this, promote the parameter, and filter to our heart’s content.

In the interest of artificially creating a challenge, I’ll be using OData for my reports in this example.

First off, I am going to create a new report.  Using my new OData skills, I fire up LINQPad and generate a URL to create a simple project work report.  This URL will provide the assignment data:

http://demo/PWA//_api/ProjectData/Assignments()?$select=ProjectName,ProjectId,ResourceName,AssignmentWork

…to add some difficulty, we can also pull in a field from the Project table.

http://demo/PWA//_api/ProjectData/Projects()?$select=ProjectId,EnterpriseProjectTypeName

As I mentioned in this post, I’m using an on-prem version of Project Server to generate and validate the URLs, then I’ll append everything from the …/_api bit onwards onto my O365 instance to ensure it works.  In the case of the first one, that will look like this:

http://o365instance.sharepoint.com/sites/pwa/_api/ProjectData/Assignments()?$select=ProjectName,ProjectId,ResourceName,AssignmentWork

Creating the Data Model (Option 1 – No Filters)

I wrote this part of the post before I figured out what worked in the filtering mechanism and what didn’t.  Since I had it written, I figured I’d include it for thoroughness and with the hope that it’s not too confusing.  Note that this method will not work with the filtering mechanism I am demonstrating.  It is an effective way of building reports however…..so if you don’t plan to filter your reports automatically using SharePoint filter Webparts, I’d definitely recommend using PowerPivot to create your data model.

Hopefully this should clarify:

 

Per one of the comments I got at SharePoint Conference, feel free to use OData in your on-prem reporting as well.  Personally, I feel that since I’m comfortable in SQL, I’ll just continue using that – but it’s probably important to note that it’s available and useful in on-prem as well.

Anyway, I add both of those feeds into Excel 2013 and flip to PowerPivot to ensure they are joined properly.  (Check some of my prior posts for more information on that.)

Now, back in the main Excel interface, I add a PivotTable based on the PowerPivot data model.

…which looks like this…

…to illustrate a second concept, I’ll go ahead and create a second pivot table that looks like this…

Turn them both into a bar chart to get something that looks like this…

I’m now going to add a slicer to each chart.  I’ll add a ProjectID slicer to the top chart and a Resource Name slicer to the bottom chart.  Click on the slicers to validate the results.  At this point, I’d also name the charts to ensure we display the correct ones.

…and well that’s pretty much it for this option.  Upload to SharePoint to allow your users to click the slicers and filter the Excel reports manually.  In my next post, I’ll talk about how to connect these (or rather similar) reports to SharePoint filter webparts to get that automatically filtered experience that we demo’d in Vegas a couple of weeks ago.