Just making sure you saw this latest announcement about our industry-leading financial analytics and workflow tool…
http://umtblog.com/2012/05/29/announcing-the-public-availability-of-umt-project-essentials-2012/
Just making sure you saw this latest announcement about our industry-leading financial analytics and workflow tool…
http://umtblog.com/2012/05/29/announcing-the-public-availability-of-umt-project-essentials-2012/
I know what you’re all thinking. Project 2010 is fantastic – and we can’t wait till we get to see the next version. The only problem is that there’s this one critical function that Microsoft really needs to bake into the product. You see, they really need to add a button that will automatically translate my schedule into Romanian.
Now, I don’t think we’ll see that quite baked into the product in the near future, but in this post, I’ll show you how to quickly add a bit of VBA that ties into the Microsoft Translator API to automatically translate your project schedule into Romanian – or 36 other supported languages ranging from Hmong Daw to Haitian Creole. (Although, it still doesn’t support Mongol unfortunately. I guess I’ll have to wait a bit for that.)
I know that I am a bit stuck in a VBA rut, but every now and then, I run into a trick that’s so cool, it’s worth blogging about. In this case, I can’t really take much credit. JimS, one of the users on the forums came up with this code to automatically translate task names from one language to another. (Although as far as I can tell, the code originally derived from a post by Travis McGee back in 2010.)
The magic is done through the use of a bit of VBA code – that then calls the Microsoft Translation API, and drops the results into the Text1 field.
I had to update the code a bit for this blog as it appears the original code was using an older authentication form that has now been deprecated. Hence, I would definitely not recommend using the older Bing AppID authentication method that is floating around on the Web and instead implement the code below which authenticates against the Azure marketplace using an authorization token.
The first step though is to get two parameters: the ClientID and the ClientSecret from your free account on the Azure Marketplace. Check here for guidance on getting those. These parameters link the application to your user account – so for example, you could use the free version which is limited to 2 million characters/month, or pay a bit more to get more volume.
Hat tip to this post for the guidance on adding the functionality to get the Authorization Token.
You’ll need to enter your personal Client ID and the Client Secret into the code to get it to function. All the configuration parameters are grouped in the first function.
'Sources of info: 'http://blogs.msdn.com/b/translation/ 'http://msdn.microsoft.com/en-us/library/hh454950.aspx 'https://datamarket.azure.com/dataset/1899a118-d202-492c-aa16-ba21c33c06cb 'http://social.msdn.microsoft.com/Forums/en-US/microsofttranslator/thread/e4c149c4-fefb-48fd-8990-db6a8f0f9045 'Set the Following References : 'Microsoft XML, v6.0 Sub TranslateTaskNames() 'This macro will translate task names and drop the new values into the Text1 field. 'Set the authorization token parameters here. Macro won't work without the appropriate values. 'See here for instructions on getting set up with the appropriate codes. Dim ClientID As String Dim ClientSecret As String ClientID = "ENTER YOUR CLIENT ID HERE" ClientSecret = "ENTER YOUR CLIENT SECRET HERE" 'Refer to this page for language codes: http://msdn.microsoft.com/en-us/library/hh456380.aspx Dim LanguageFrom As String Dim LanguageTo As String LanguageFrom = "" 'Set the source language. Leave blank to auto-detect. LanguageTo = "FR" 'Give the user the option to change the target language LanguageTo = InputBox("Enter the target language code:", "Target", LanguageTo) 'The next section performs the translation Dim T As Task Dim tName As String For Each T In ActiveProject.Tasks tName = MicrosoftTranslate(T.Name, LanguageFrom, LanguageTo, ClientID, ClientSecret) 'Translate the Task Name tName = Mid(tName, 2, Len(tName) - 2) 'Truncate quotation marks from result set T.Text1 = tName Next T 'The next section displays a confirmation box Dim tCompleted As String tCompleted = MicrosoftTranslate("Translation Completed", LanguageFrom, LanguageTo, ClientID, ClientSecret) 'Translate confirmation message tCompleted = Mid(tCompleted, 2, Len(tCompleted) - 2) 'Truncate quotation marks from the result set MsgBox "Translation Completed / " & tCompleted, vbOKOnly, "Done" End Sub Function MicrosoftTranslate(sText As String, LanguageFrom As String, LanguageTo As String, ClientID As String, ClientSecret As String) As String 'This function calls the Microsoft Translate API Dim ID As String Dim sURL As String Dim oH As MSXML2.XMLHTTP Dim sToken As String ID = "" ' Bing appID deprecated in Dec 2011 in favour of Access Token. Use nothing for legacy appID parameter sURL = "http://api.microsofttranslator.com/V2/Ajax.svc/Translate?oncomplete=&appId=" & ID _ & "&from=" & LanguageFrom & "&to=" & LanguageTo & "&text=" & sText sToken = GetAccessToken(ClientID, ClientSecret) Set oH = CreateObject("MSXML2.XMLHTTP") oH.Open "POST", sURL, False oH.setRequestHeader "Authorization", "Bearer " & sToken oH.send MicrosoftTranslate = oH.responseText Set oH = Nothing End Function Function GetAccessToken(ClientID As String, ClientSecret As String) As String 'This function authenticates against the Azure marketplace to ensure the user is authorized to use the app. Dim webRequest As MSXML2.ServerXMLHTTP Set webRequest = CreateObject("MSXML2.ServerXMLHTTP") Dim URI As String URI = "https://datamarket.accesscontrol.windows.net/v2/OAuth2-13" Dim sRequest As String sRequest = "grant_type=client_credentials" & _ "&client_id=" & ClientID & _ "&client_secret=" & URLEncode(ClientSecret) & _ "&scope=http://api.microsofttranslator.com" Dim mtToken As String Set webRequest = New ServerXMLHTTP webRequest.Open "POST", URI, False webRequest.setRequestHeader "Content-Type", "application/x-www-form-urlencoded" webRequest.send sRequest mtToken = webRequest.responseText Set webRequest = Nothing 'Debug.Print mtToken Dim arr As Variant, header As String header = "{""access_token"":""" arr = Split(mtToken, ",") GetAccessToken = Mid$(arr(0), Len(header) + 1, Len(arr(0)) - Len(header) - 1) End Function Public Function URLEncode(StringVal As String, Optional SpaceAsPlus As Boolean = False) As String 'This function modifies the text to properly work in a URL. Dim StringLen As Long StringLen = Len(StringVal) If StringLen > 0 Then ReDim result(StringLen) As String Dim i As Long, CharCode As Integer Dim Char As String, Space As String If SpaceAsPlus Then Space = "+" Else Space = "%20" For i = 1 To StringLen Char = Mid$(StringVal, i, 1) CharCode = Asc(Char) Select Case CharCode Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126 result(i) = Char Case 32 result(i) = Space Case 0 To 15 result(i) = "%0" & Hex(CharCode) Case Else result(i) = "%" & Hex(CharCode) End Select Next i URLEncode = Join(result, "") End If End Function
Set the source and/or target language per the options here.
Here’s the same exercise from English to Spanish. I don’t make any claims about the quality of the translation, but overall, it doesn’t seem like a bad start.
Kind of wish I’d had something like this back in the late 90s when I had to figure out a system to translate Fluke testing reports into English.
The question always arises as to how to best take a snapshot of project data, and use that in timephased reports or change logs. In fact, by my count, this is the third time I’ve addressed this topic in this blog:
In this blog post, I’d like to take a different approach than in the past and show how to use VBA to post snapshots of project data to a SQL database. The value of storing the data in a custom SQL table of course is that the data is available on the enterprise level, subject to enterprise retention policies, and available for use with enterprise reporting solutions.
Now, I could always use automatically generated queries. In fact, it would be quite simple to set up a timed job that runs within SQL every Friday evening and takes a snapshot of project data. As I’ve mentioned before, I am not a big fan of such automated queries – generally because I know a lot of project managers who like to catch up on work over the weekend – or who often will submit the numbers only to immediately realize that they forgot to totally update their schedule, make adjustments and then resubmit. Running a timed snapshot process may not work well for folks like this.
My preference is for the project manager to manually trigger that snapshot in time as part of the routine status reporting cycle. You could even incorporate some workflow or alerts around the data when it’s submitted as part of the report.
In this case, I’ll just use some code that Calin, one of our developers created for me to support some EVM reporting. This query will generate a table within a SQL database. Generally, you would add this table to a custom database.
CREATE TABLE [dbo].[ProjectStatusSnapshots]( [Id] [int] IDENTITY(1,1) NOT NULL, [Date] [datetime] NOT NULL, [ProjectName] [nvarchar](500) NOT NULL, [ProjectUID] [uniqueidentifier] NULL, [ProjectStatusDate] [datetime] NULL, [ProjectACWP] [money] NULL, [ProjectBCWP] [money] NULL, [ProjectBCWS] [money] NULL, [ProjectEAC] [money] NULL, [ProjectCPI] [money] NULL, [ProjectSPI] [money] NULL, [ProjectTCPI] [money] NULL, [ProjectCost] [money] NULL, [ProjectActualCost] [money] NULL, [ProjectBaseline0Cost] [money] NULL CONSTRAINT [PK_ProjectStatusSnapshots] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
Modify the fields to fit your needs. You might also consider creating two tables, one for project header information, and one for task information.
Confirm that we have a new table in SQL.
The VBA then is relatively simple. We just need to create a bit of code to generate the database connections and then run a SQL update query to insert the right data into the database table.
Note that I am grabbing the ProjectUID as well. This will facilitate reports that will join the trend analysis data with the default Project Server reporting database.
As I mentioned above, you may want to consider adding code to capture task level metrics for trend analysis. That would pretty much be the same concept. Just make sure to grab a unique identifier for the tasks to use in generating reports. Using that technique and a field to capture the current user, you could effectively implement a change log – especially if you embed the VBA code in the Project OnSave activity.
Sub UpdateSQL() 'This macro will log specific field data into a customized SQL database. 'Create connection to SQL table.' Dim Conn As New ADODB.Connection Dim SQLInstance As String Dim SQLDatabase As String 'Set the connection parameters here SQLInstance = "Demo\Demo" 'Enter the name of the SQL instance SQLDatabase = "zzz_TrendAnalysis" 'Enter the name of the SQL database Conn.ConnectionString = "Provider=sqloledb;" _ & "Data Source=" & SQLInstance & ";" _ & "Initial Catalog=" & SQLDatabase & ";" _ & "Integrated Security=SSPI;" If Conn.State = 1 Then 'To avoid errors where the connection was opened and not closed Conn.Close End If 'Open the connection and assign the data to the table. Conn.Open With ActiveProject.ProjectSummaryTask Conn.Execute ("INSERT INTO " & SQLDatabase & ".dbo.ProjectStatusSnapshots([date], ProjectUID, ProjectName, " _ & "ProjectStatusDate, ProjectACWP, ProjectBCWP, ProjectBCWS, ProjectEAC, ProjectCPI, " _ & "ProjectSPI, ProjectTCPI, ProjectCost, ProjectActualCost, ProjectBaseline0Cost) " _ & "VALUES(GetDate(), " _ & Chr(39) & ActiveProject.GetServerProjectGuid & Chr(39) _ & Chr(44) & Chr(39) & ActiveProject.Name & Chr(39) _ & Chr(44) & Chr(39) & ActiveProject.StatusDate & Chr(39) _ & Chr(44) & .ACWP _ & Chr(44) & .BCWP _ & Chr(44) & .BCWS _ & Chr(44) & .EAC _ & Chr(44) & .CPI _ & Chr(44) & .SPI _ & Chr(44) & .TCPI _ & Chr(44) & .Cost _ & Chr(44) & .ActualCost _ & Chr(44) & .BaselineCost & ")") End With Conn.Close MsgBox "Project snapshot has been saved.", vbOKOnly, "Confirmation" End Sub
Run the VBA a couple of times to confirm it’s generating data.
Once you’ve validated the VBA, consider adding it as a button to a customized ribbon toolbar. This will give your users the ability to trigger the macro directly from the main Microsoft Project interface.
From there, it’s just a simple question of retrieving the data. As I mentioned above, I fully expect the PM to potentially create multiple snapshots at each status period, I may only want to grab the “final” one that was posted each day. To do that, we just need to modify a basic SQL query.
Here’s an example:
SELECT dbo.ProjectStatusSnapshots.Id, dbo.ProjectStatusSnapshots.Date, dbo.ProjectStatusSnapshots.ProjectName, dbo.ProjectStatusSnapshots.ProjectUID, dbo.ProjectStatusSnapshots.ProjectStatusDate, dbo.ProjectStatusSnapshots.ProjectACWP, dbo.ProjectStatusSnapshots.ProjectBCWP, dbo.ProjectStatusSnapshots.ProjectBCWS, dbo.ProjectStatusSnapshots.ProjectEAC, dbo.ProjectStatusSnapshots.ProjectCPI, dbo.ProjectStatusSnapshots.ProjectSPI, dbo.ProjectStatusSnapshots.ProjectTCPI, dbo.ProjectStatusSnapshots.ProjectCost, dbo.ProjectStatusSnapshots.ProjectActualCost, dbo.ProjectStatusSnapshots.ProjectBaseline0Cost, DailyRecord.LastRecord FROM dbo.ProjectStatusSnapshots RIGHT OUTER JOIN (SELECT MAX(Id) AS LastRecord FROM dbo.ProjectStatusSnapshots AS ProjectStatusSnapshots_1 GROUP BY ProjectUID, CAST(Date AS Date)) AS DailyRecord ON dbo.ProjectStatusSnapshots.Id = DailyRecord.LastRecord
Run this query to generate a table of the last entry posted per project on any given day. You could then use the ProjectUID field to join this date to live data in the Project Server reporting database.
For those developer types working in the ever increasing number of organizations that have deployed our Project Essentials product – I wanted to make sure you saw Mircea’s post on developing custom applications against PE:
http://www.ro.umt.com/blog/2012/05/08/developing-project-essentials-applications-using-wcf-2/
This is a topic I’ve briefly played with before – how to automatically incorporate Project Server data into Office documents. The REST API has long been the go to tool for doing such things, as the API allows us to consume Excel reports dynamically in Word or Powerpoint documents.
The general goal in adding this sort of document automation is to attempt to drive user behavior a bit. We want our users to move away from generating documents on their C drive and generating them within SharePoint. Adding document automation is on of the major incentives to do so, as it makes it easier to create a document within SharePoint than on the local drive.
In this post, I want to take that integration to the next level, i.e. I want to incorporate Project Server fields within the text itself of a Word document. I might do this to automate some of my status reporting, or to create a project charter template that automatically populates with key data.
Now, I’ll point out that this technique is not quite seamless, and probably could benefit from a little custom SharePoint development….but hopefully this may give you some ideas, and be referenced in other, more robust solutions. I also haven’t quite figured out how to deploy this across multiple sites without custom development. So for now, take the solution for what it is, a rough idea for automating documents at the workspace level. If someone smarter than I wants to take this idea and run with it….by all means, be my guest.
The first step in accomplishing this is to build an external content type (instructions here). When building the external content type, make sure to add a filter on the project name (instructions here).
Now let’s go create our document template. To do this we need a Project Workspace. It doesn’t have to be a live project workspace as really all we need is a document library. We’re going to add our ECT to the document library as a custom field.
First, navigate to the Library Settings.
Scroll down and add a new column.
I then configure the new column as below – using the name of the ECT that I created in one of the previous posts.
Now let’s associate that field with the default content type. To do this, we need to enable content type management within the document library. Click on the advanced settings to do this.
Enable content type management for the library.
Confirm that the field has been associated with the default document content type. Once we’ve done that, lets create a new document from the workspace. (You could also take an existing document template and load it to the document library and then adapt it to our purposes.)
Note how the Document Information Panel now shows a number of blank fields.
These fields will be populated from the project header data in the document library. More importantly, we can consume those fields within the document using the Quick Part mechanism.
I’ll go ahead and create a quick status report leveraging the Quick Parts.
Now let’s save the document back to the workspace. For good measure, I’ll save another copy off to my desktop as a template file.
Once the file has been posted back to the SharePoint site, test it out by selecting the option to Edit Properties.
Type the project name and hit the search button….
…and the metadata is now populated. Open the document to see the changes.
Yes….I know it’s kind of annoying to have to open the document and then close it and edit the metadata column to get it to work properly. I am sure there’s a decent solution to that, but for now, I’ll leave it there.
The next step here might be to associate our validated document template with the content type on the workspace. To do that, I go back into our document library and upload the template to the content type.
…and now I can create a document within the document library, save it, add the metadata and know that everything is properly updated.
As I said above, the entire process is perhaps a bit contrived, but hopefully this should serve as a launching point for ideas. Other options that might be a bit easier (depending on your particular skill set) would be to simply write VBA within Word to query Project Server data and populate fields correctly.
Also, consider combining this technique with the concept of document sets. In that case, you would only have to apply the fields once, and then have all of the changes trickle down to the document set components. Please see this post from Alex Burton on leveraging document sets with Project Server: http://epmsource.com/tag/document-sets/.
Note that this post was inspired by (yet another) highly informative and entertaining presentation from Mr. Tom Resing, now of Rackspace. His presentations have become a highlight of the annual Houston SharePoint Saturday event.
Update (5/11): Tom’s presentation was indeed informative and entertaining, but after looking at my notes, Mike Huguet, PFE Extraordinaire, was the one who actually presented on customizing Word documents at the SharePoint Saturday event. Just wanted to clear that confusion up. Now I can rest at night.
I’ve been using so many of Microsoft Project’s approximately 900 functions for so long, that often times, I forget that some of the functions I take for granted are often undiscovered to long time users of the software. In the last couple of months, at least twice, I’ve found end users to be unaware of the elapsed duration functionality. Hence, I figured I’d write up a quick blog post about it.
First off, what is elapsed duration? If you open Microsoft Project and enter “1ed” in the Duration column, you’ll see duration calculated as an elapsed day.
Essentially, it appears to be pretty much the same as a 1 day task. Let’s calculate 7 elapsed days.
You’ll see that elapsed duration ignores the weekend non-working time and schedules the task over the weekend.
Now, let’s assign work resources to further illustrate the difference…
You’ll see that Task 1 is calculating per the normal duration calculation, i.e. 7 days X 8 hours per day = 56 hours. Task 2 is actually calculating at 7 days X 24 hours per day = 168 hours.
Elapsed duration calculates a continuous 24 hour cycle and ignores non-working time.
So now we’ve defined elapsed duration. When should we use this to model activities in our schedule? Typically, you would use elapsed duration to model automated testing routines. For instance, I might start a test on Friday at 5PM, and then let it run over the weekend.
Similarly, I might use elapsed duration to model tasks like data conversions. I can kick the data conversion off before leaving for the day, then let the task run for 12 hours. I am sure the potential uses are endless.
Last but not least, I know Dale Howard was recently noodling on a similar topic. Check out his blog post for an overview of how to detect tasks using elapsed duration.