Archive for April, 2007
Work with the Timescaled Data Object in Project 2007
30 April 2007
UPDATE:
I have updated the code in the file download link below. I don’t have time to rewrite the post to explain the changes in full but I found an issue with the code that caused a problem. Basically, if the start date of an assignment slipped into the future then the code would not act on the original dates because it was setup to use the Assignment start date as the earliest point for acting on the assignment values. Well if the assignment moved (the baseline data was ‘in the past’) then the code would just not do what it was designed to do. The new code at the link below will work better. Again ALWAYS test your code in a bunch of different situations. This is a great object lesson in how a developer can get tunnel vision and write code for a specific situation that leaves out other possible situations. I was writing based on a quick situation I could create and recreate very quickly during the coding process and it obviously did not include the assignment start date moving. It only included the assignment finish date moving. 
Like all good VBA macros we have to start with a problem that needs to be addressed or a function that needs to be added. Our problem today will be the fact that if you add a new task to an existing baseline and there is a resource assigned to this task the Baseline Work for that assignment is not rolled up to the Resource level Baseline Work field. Once the task is added to the baseline the Baseline Work for the Resource does NOT show the additional Work added to the baseline by the new assignment. This is also true of the Resource level timescaled baseline work data. The VBA code we will review here addressed this issue and gives us an opportunity to look at how to use the TimeScaledData method to access the timescaled data for tasks, resources, and assignments.
The steps to reproduce this issue are as follows:
- Create a task and assign a resource
- Baseline the project
- Create another task and assign the same resource
- Add just this second task to the project by clicking Tools | Tracking | Set Baseline and selecting “Selected Tasks”
- Go to the Resource Usage view and insert Baseline Work into the timescaled area
The example below shows how at the resource level Dwight still only has 64 hours of Baseline Work when in reality there should be 64 + 32. The timescaled area also shows only 8 hours of baseline work at the resource level for 19th, 20th, 23rd and 24th when there should be 16 for each of those days.
What is the TimeScaledData Method?
This method returns a collection of data that represents a specific type of data (Work, Cost, Baseline Work, etc) for a specific period of time (the duration of a task or assignment for example) that will be provided broken down by the specified time unit (days, weeks, months, etc). This collection can then be worked with to either read specific data about specific time slices in the collection or even to set specific data for a time slice in the collection. It is a very powerful feature of VBA but one that is not widely understood.
You can download the full macro here.
We will now review each section of the code and go over what it does:
The Variables
Dim R As Resource
Dim A As Assignment
Dim aBL As TimeScaleValues
Dim rBL As TimeScaleValues
Dim Counter As Integer
This section defines (or dimensions) the variables we will use.
R is an object variable that will represent one or more resources
A is an object variable that will represent one or more assignments
aBL is an object variable that represents timescaled data. It gets ‘loaded’ with data by using the TimeScaledData method. This object will represent the assignment timescaled values (hence the ‘a’)
rBL is just like aBL but it will represent the resource timescaled values (hence the ‘r’)
Counter is a variable that will be used to loop through the members of the collections when we need to access those values.
The Resource For…Next Loop
For Each R In ActiveProject.Resources
If Not (R Is Nothing) Then
Because we established above that ‘R’ was to represent a resource object we can use this variation of a For…Next to loop through ‘each’ resource (R) in the Resources collection of the active project.
The If…Then statement is testing to make sure that there are no blank lines in the resource sheet of the project. Blank lines cause a problem for VBA when dealing with the Tasks and Resources collection. If the loop above encountered a blank line an then the macro tried to access any property (the name, work or any other field for example) the macro would get an error. The test in the If…Then statement makes sure that the code ‘under’ it will only run if the object represented by ‘R’ is an actual resource.
Setting the Values to Zero
R.BaselineWork = 0
For Each A In R.Assignments
Set rBL = R.TimeScaleData(StartDate:=A.Start, EndDate:=A.Finish, _
Type:=pjResourceTimescaledBaselineWork, TimescaleUnit:=pjTimescaleDays, Count:=1)
For Counter = 1 To rBL.Count
rBL(Counter).Value = 0
Next Counter
Next A
The rest of our macro below will be recalculating what the Resource level baseline and timescaled baseline work data SHOULD be after a task is added to the baseline. This section of code is setting those values to 0 so that the code below can calculate the values correctly. This section shows us the first use of the TimeScaleData method.
The For..Each…Next here is looping through all the Assignments (all the ‘A’ objects) in the Assignments collection for the resource represetted by ‘R’. It is then setting the ‘rBL’ variable to equal the collection of timescaled data defined in the R.TimeScalData method. It sets the start date of the collection to equal the start date of the current assignment (A.Start) and the finish date to equal the finish date of the current assignment (A.Finish). The collection will contain Baseline Work because we are setting the Type to equal pjResourceTimescaledBaselineWork. The timescale will be days and the Count will be 1, meaning that each object in the collection will represent 1 day. If count were set to 2 then each object (or if you prefer ‘time slice’) would be 2 days.
Next we have a For…Next loop that sets the value for Counter to be 1 through the number of objects (time slices) in the ‘rBL’ collection we just created. This loop is letting us move through each slice so that we can do something to it, either read it or write to it. The line that reads ‘rBL(Counter).Value = 0′ is setting the value for Timescaled Baseline Work for the time each timeperiod in the assignment (in this case days) for the resource represented by ‘R’ to equal zero. Let’s say that the assignment represented by ‘A’ started on Day1 and finished on Day 5. This would mean that the ‘rBL’ collection would have 5 members (time slices) each representing 1 day. This means that the For Counter = 1 to rBL.Count would loop through 5 times. On the first loop through the rBL(Counter).Value represents the value of timescaled Baseline Work for the resource for Day1. On the second loop through it is Day 2 and so on. This sets the values for resource timescaled baseline work to 0 for the duration of the assignment represented by ‘A’. This will get repeated for each assignment for the resource represented by ‘R’.
You may ask yourself why the code above is setting the Resource timescaled values to 0 since we are using the Assignment start and finish dates. You might wonder why we are not just setting the Assignment values to 0 since the code above would have us in many cases setting the same days values to 0 several times (in the case of overlapping assignments.) This is a good question. The answer is that the problem in Project that we are solving is one where the assignment values for baseline work are correct but the resource values are incorrect. If we reset the assignment values to 0 then we would have no idea how to set the resource values. We need the assignment values intact. The price we pay for this is needing to loop through some days at the resource level a few extra times.
Adding up and Setting the Correct Values
For Each A In R.Assignments
R.BaselineWork = R.BaselineWork + A.BaselineWork
Set aBL = A.TimeScaleData(StartDate:=A.Start, EndDate:=A.Finish, _
Type:=pjAssignmentTimescaledBaselineWork, TimescaleUnit:=pjTimescaleDays, Count:=1)
Set rBL = R.TimeScaleData(StartDate:=A.Start, EndDate:=A.Finish, _
Type:=pjResourceTimescaledBaselineWork, TimescaleUnit:=pjTimescaleDays, Count:=1)
For Counter = 1 To aBL.Count
If Not (aBL(Counter).Value = “”) Then
rBL(Counter).Value = rBL(Counter).Value + aBL(Counter).Value
End If
Next Counter Next A
This section is in large part a repeat of the previous section with the addition of the setting of the ‘aBL’ collection that will allow us to read the assignment level timescaled Baseline Work data and insert it into the ‘rBL’ collection. So to that end you see the Set ‘aBL’ and Set ‘rBL’ lines. They are setting the collections for both assignment and resource based on the assignment start and finish because we are reading from the assignment. Next we have a slightly expanded ‘For Counter’ loop from the last section. It contains an If…Then statement we use to test to make sure that value of the aBL object currently being evaluated does not contain a null value. Project sets the Baseline Work for the default nonworking days of a calendar (Saturday and Sunday) differently than non-default nonworking days (days the user sets to nonworking via the Change Working Time dialog.) Saturdays and Sundays get set to Null values while nondefault nonworking days get set to 0. Testing to make sure that the value of the rBL object is not null saves us from getting an error.
The next line is the key to the whole thing. It looks at the value of the Assignment timescaled Baseline Work and adds it to the current value of the Resource timescaled Baseline Work. It does this for each timeslice in the aBL collection. This is repeated for each assignment at which point the resource timescaled baseline work is equal to the sum of the timescaled baseline work for each of the assignments for the resource.
Closing Out
End If
Next R
End Sub
The End If here is the closing of the “If Not (R Is Nothing) Then” test at the beginning of the routine.
The Next R is the Next for the “For Each R In ActiveProject.Resources” line in the beginning. Once the execution hits this line it has completed the rest of the code for one resource and if there are more than one resource it will repeat until it has run through for each resource.
The End Sub ends our macro.
Expanding on the Basics Above
The example above works with assignment and resource timescaled baseline work. But the same concept can be used to work with any of the timescaled data in Project with just a few changes to the code we worked with. These concepts can be applied to working with task timescaled data as well. For example if we wanted to access task timescaled cost data the code might look like this:
Set taskBL = T.TimeScaleData(StartDate:=T.Start, EndDate:=T.Finish, _
Type:=pjTaskTimescaledCost, TimescaleUnit:=pjTimescaleDays, Count:=1)
This code would return a collection (in the object variable taskBL) of timescaled task cost data where each member of the collection was 1 day in duration starting with the start of the task and ending on the finish date of the task. If we wanted it in months instead of days we would change ‘pjTimescaleDays’ to ‘pjTimeScaleMonths’. If we wanted it just for the first day of the task we would set the StartDate and EndDates to both be equal to T.Start.
One of the great things about VBA is that you don’t have to remember all of the parameters or constants you need to use to make this kind of method work. The VBA Editor prompts you if you used named parameters. As soon as you enter the open parentheses after TimeScaleData the editor tells you the parameters for the method.
You could just type A.Start in the first position and then a comma and A.Finish but good coding practice dictates that you type StartDate:=A.Start. If you do this it makes your code easier to read and gives the advantage of being prompted with the constants for things like Type and TimeScaleUnit like below. This way you do not need to remember the many, many constants available to you.
So Why Would I Use This?
You might ask yourself why this would be important? Always a good question. This kind of code is essential if you want to look at or edit specific time slices of data about tasks, assignments or resources. This would include code to integrate a timesheet system with Project. For sure most companies that are integrating systems like SAP will be using Project Server and using the PSI but a ‘timesheet system’ might be as simple as a set of Excel spreadsheets you distribute to your resources for them to enter status data. If they do it in a timescaled way (number of hours of work done per day or per week then this code is exactly how you would base a macro to pull that data from Excel and put it into Project in a timescaled way. The TimeScaleData method is also the best way to create your own custom exports from your Project files into other systems that need data on a day by day basis. These methods are shown here using VBA native to Project 2007 but the same concepts can be used within Visual Basic to create more robust applications.
Disclaimer
This code is presented here for educational purposes only. Before such code is used in a production environment it should be seriously tested for all possible situations to ensure that it performs as expected. It’s presentation here does not imply any guarantee or warranty on this code. Further, this code is not supported by Microsoft in any way. I am presenting it here as a means of education only.
Popularity: 1% [?]
Newly published content for Office Project Server 2007
29 April 2007You should probably bookmark this url: Newly published content for Office Project Server 2007 and check it on a regular basis for updated documentation on the EPM 2007 platform.< ?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
MsoNormal style=”MARGIN: 0cm 0cm 10pt”>For instance this month a detail guide on configuring OLAP for EPM 2007 using both SQL 2K0 and 2K5 was published as shown below:
Configure Office Project Server 2007 to use the Cube Building Service This chapter describes how to prepare your Microsoft Office Project Server 2007 deployment to use the Cube Building Service. It provides an overview of how the Cube Building Service works and information about supported deployment options. This chapter also includes requirements for using the Project Server 2007 Cube Building Services with either SQL Server 2000 Analysis Services or SQL Server 2005 Analysis Services, because both are supported with Office Project Server 2007.
This site also displays upcoming documentation for instance:
· Manage the Project Server 2007 Cube Building Service
· Configuring LDAP Membership Provider forms authentication for Office Project Server 2007
Popularity: 1% [?]
Troubleshooting Project Server Active Directory (AD) Synchronization
29 April 2007If you are having issues using AD synchronization with EPM (user not synched up for instance) you can do the following two actions to troubleshoot the sync. process:< ?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
MsoListParagraphCxSpFirst style=”MARGIN: 0cm 0cm 0pt 36pt; TEXT-INDENT: -18pt; mso-list: l0 level1 lfo2″>· Using “Active Directory Users and Computers” (dsa.msc) MMC snap-in, find the group to sync and if so, are the users listed under members tab?
· Get detail logs of the EPM to AD sync process and analyze them.
To activate the ULS do the following:
1. Go to your farm Central Administration page, select Central Administration > Operations > Diagnostic Logging
2. In Event Throttling, select Project Server Active Directory Synchronization category
3. Set Least critical event to report to the trace log as Verbose, and press OK
ULS are typically located under: C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\LOGS
Popularity: 1% [?]
Adding an EPM Web Part to another SharePoint site
29 April 2007Steps you will need to follow to display PWA Web Parts in another site within the same SharePoint farm:< ?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
=EN-US style=”FONT-SIZE: 10pt; LINE-HEIGHT: 115%; FONT-FAMILY: ‘Arial’,’sans-serif’; mso-ansi-language: EN-US; mso-no-proof: yes; mso-fareast-font-family: Arial”>
1. Chose the Web Part (e.g. TimesheetPart) from the PWA homepage or the web parts gallery (Project Web Access > Site Settings > Galleries > Web Parts) and Export and Save to your preferred desktop location:
2. Edit the Web Part in Notepad. Find the following property PSIURL (or add if it does not exist) and change it accordingly for instance:
<property name=“PsiUrl“ type=“string“> http://server/PWA</property>
3. Save Web Part update
4. From http://server/sites/project - Site Collection EPM > Web Part Gallery > Upload Document, select TimesheetPart modified earlier.
5. Add Web Part to the page you like (Site Action -> Edit Page)
To cross SharePoint server farms, you can use the Page Viewer web part in conjunction with SimpleUI=31
http://server/PWA/_layouts/pwa/timesheet/mytssummary.aspx?SimpleUI=31
If you encounter the following while adding the Web Part to the page : “Unable to add selected web part(s). My Timesheet: The request failed with HTTP status 401: Unauthorized” check the ULS logs. It’s typically an access right issue so make sure the user has access to the PWA site.
|
Project Server 2007 Web Parts |
|
|
· MySchedule |
· ResourceAssignmentsPart |
|
· OWCViewPart |
· ResourceCenterPart |
|
· ProjectCenterPart |
· StatusApprovalsPart |
|
· ProjectDrillDownPart |
· TaskPart |
|
· ProjectWorkspacesPart |
· TeamAssignmentsPart |
|
· QueueStatusPart |
· TimesheetPart |
|
· RemindersPart |
|
p=”true”>
Popularity: 1% [?]
Some reference information on creating archive database(s) and archive process for project plans and related WSS 2.0 sites in Project Server 2003?
29 April 2007
Popularity: 1% [?]
MSProjectExperts Merges With EPMCS, LLC
29 April 2007
Popularity: 1% [?]
Checking Errors
29 April 2007Here is a great recommendation from Jim Corbin:
There are two main places to check for configuration and runtime errors when you are developing solutions for Project Server. The Unified Logging Service (ULS) trace logs can be more detailed than the application event log.
· Application events In the Start menu on the Project Server computer, click Run, and then type eventvwr. In the left pane of the Event Viewer window, click Application to see the events logged by Project Server, Windows SharePoint Services, ASP.NET, SQL Server, custom event handlers, and other applications. The Project Server event sources include ProjectQueueService and pjevtsvc.
· ULS You can configure the ULS trace log to record specific or all categories and levels of activities in Project Server and SharePoint. To view the trace logs, you can use Windows Notepad, Microsoft Excel, or the Log Viewer add-in feature for SharePoint. Log Viewer is a useful download that is available from CodePlex.
To configure the ULS trace log for a specific Project category:
1. Open the SharePoint 3.0 Central Administration application, click Operations, and then click Diagnostic logging in the Logging and Reporting section.
2. In the Event Throttling section, select a specific category such as Project Server - Server-Side Events. If you select Project Server - General, all Project categories will be logged.
3. Select the Verbose level for the least critical event to report in the trace log.
Caution Run verbose logging only when you need it. Especially on a production server, select only a specific category. The size of logs can grow to be large. To turn off all logging, select the empty category and None for the trace log least critical event. To record relatively few events, select High or Monitorable.
4. Use the default path for the trace logs, for example, C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\LOGS\. If you use the Log Viewer add-in for SharePoint, it looks for trace logs in the default path.
5. The default maximum number of log files to maintain is 96.
6. The default number of minutes to use a log file is 30. That is, ULS tracing creates a new log file after 30 minutes. Log files include the date and time in the filename, for example, SERVERNAME -20070424-1612.log.
To use the Log Viewer add-in for SharePoint:
1. Download and install the Log Viewer feature from CodePlex SharePoint 2007 Features. See the Releases tab for the list of downloads. The release notes include installation instructions.
2. The Log Viewer is a global feature. After it is installed, click Operations on the Central Administration page, and then click View Unified Logging Service in the Utilities section.
3. On the Unified Logging Service (ULS) Logs page, select a file to view, and then select the Project Server category. That shows all of the specific categories such as Project Server Queue, Project Server Server-Side Events, Project Server Reporting, and so forth.
4. To see all events in the ULS log, leave the trace severity drop-down list blank. If you select Verbose, that shows only the verbose level events.
5. Click Go.
Log Viewer add-in for SharePoint showing a ULS log for Project
Popularity: 1% [?]
Update to OLAP Issue: February release of SQL Server Management Objects works with Reporting Services add-in for SharePoint
25 April 2007If you are a regular reader you may remember that the loading of the Microsoft SQL Server 2005 SP2 component ”Reporting Services Add-in for Microsoft SharePoint Technologies” broke the OLAP cube builds as it removed a required component when used on an aplication server that was not the SQL Server host too. Well if you load the February update to the SQL Management Objects (rather than the November issue) available as part of the Feature Pack release then this problem is resolved!
Feature Pack available here
Microsoft SQL Server 2005 Service Pack 2 available here
Don’t forget (like I did) to load the add-in for SharePoint on your web front ends as well as your app server - or you will see an error as your front end web.config will be referring to a component (RSSharePointSoapProxy) that your web front end knows nothing about.
Technorati Tags: Project Server 2007
Popularity: 1% [?]
Just checking in… updated
25 April 2007One problem that our customers appear to be running into is checking in projects. We do have a bug that appears to leave projects checked out when you wouldn’t expect them to be. But I suspect that in many cases it may be that the architectural changes to 2007 have caused some of this confusion - and the default view of the queue may well hide the true cause of the problem.
So - check out these different scenarios to see if this covers your issues:-
- Project Professional says my project is checked out - but the server says it isn’t. This is one we are looking at a hotfix for - but there is a simple workaround that seems to resolve in most cases. Open the project read-only, then close again. This seems to push through a check-in and then it can be opened read-write.
- The check-in says “Waiting to be Processed” and even using force check-in does not get it checked in. The force check-in option should really be called request check-in - as there is nothing forceful about it. It sinply puts a check-in request into the queue - but if the first one is just sitting there going nowhere then guess what - this second (third, fourth…) will not go any faster. The best approach for this type of error is to look in the queue for any outstanding jobs for the same project. If the problem happened yesterday (or before) then it may not show in the queue until you change the job history to include earlier dates. Many times we will hear “but the queue is empty…” which may just mean no failed jobs still sitting there from today - but the failure could have happenend last week or last month! Once you find the problem job then what should you do? Well, that depends on the nature of the job. If there is a save from Project Professional that is incomplete then getting the user who has the project checked out to open Project Professional and re-connect is the best approach. Cancelling a job of this type will lose data! Once you have resolved the initial issue and any other queued jobs for this project are processing (or cancelled if you really must) then the check-in should finally work.
- If your symptoms don’t match these scenarios then you may need to log a support call to get this checked out (if you will excuse the pun).
Thanks to Ed Hanna and Brian Kennemer for bringing it to my attention - but it appears that closing Project using the X in the top right can lead to check-in issues. So best practice would be first to save and ensure that any communication with the server has completed (look in the status area after saving) and then using File, Close.
Technorati Tags: Project Server 2007
Popularity: 2% [?]
How to update Analysis Views (OLAP) after an EPM 2003 to EPM 2007 migration?
23 April 2007After migrating yoru database from Project Server 2003 to Project Server 2007, or after migrating from one environement to another (Production to Development for instance), a quick and easy way to update the Data Analysis Views is to issue the following T-SQL command in the REPORTING database:< ?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
MsoNormal style=”MARGIN: 0cm 0cm 0pt; LINE-HEIGHT: normal; mso-layout-grid-align: none”>
USE ProjectServer_Published
GO
– Check Analysis Views that needs to be updated
SELECT WVIEW_ASServerName,WVIEW_ASDatabase,WVIEW_ASCubeName,WVIEW_ASServerExtranetAddress,*
FROM MSP_WEB_VIEW_REPORTS
WHERE WVIEW_ASServerName IS NOT NULL
AND WVIEW_FILTER IS NULL
– Update Analysis Server Name
UPDATE MSP_WEB_VIEW_REPORTS
SET WVIEW_ASServerName=‘MYASSERVERNAME\INSTANCENAME’
WHERE WVIEW_ASServerName IS NOT NULL
AND WVIEW_FILTER IS NULL
Test this query on a test environment prior to applying it on a Production system!
Popularity: 1% [?]