none
TimeScaleValues Are Grouping at Beginning and End of Project RRS feed

  • Question

  • I’m trying to export actual cost from my SQL Server database to Microsoft Project.  This code used to work, but after some tinkering to how the data is received – not to the data itself – I am experiencing a strange error.

    This is the gist of my code, which hasn’t changed at all:

    objAssignment = appProj.Application.ActiveProject.Tasks(intTASK_ID).Assignments.Add(ResourceID:=appProj.ActiveProject.Resources(strRES_ID).ID) ‘Assign Actual resource to the task

     

     

    If dsExportDetail.Tables(0).Rows(intCounter).Item(2) Is DBNull.Value Then

           dateAssign = Now()‘If there is no date, assign data to the current day

    TSV = objAssignment.TimeScaleData(dateAssign, dateAssign, 28, 4)                                  TSV.Add(dsExportDetail.Tables(0).Rows(intCounter).Item(3), 1)

    Else

    dateAssign = dsExportDetail.Tables(0).Rows(intCounter).Item(2)

    ‘Set the TSV date to the actual cost date from SQL Server

           TSV = objAssignment.TimeScaleData(dateAssign, dateAssign, 28, 4)

    Try                                      TSV.Add(dsExportDetail.Tables(0).Rows(intCounter).Item(3), 1)

    ‘Add TSV data

    Catch ex As System.Exception                                     MsgBox(dsExportDetail.Tables(0).Rows(intCounter).Item(3))

           EndTry

    End If

     

    Whereas this code used to properly spread the data across the dates provided, now the values are grouping into a single lump sum and placed at either or the beginning or the end of the project’s schedule.  In other words, even if the actual cost looks like this:

    $5000 on 1/1/2012
    $300 on 1/2/2012
    $500 on 1/3/2012
    $200 on 1/4/2012

    The value that is assigned is $6,000 at the end of the project (say, 6/7/2012)… or it may be assigned to the beginning of the project, whenever that day is.

    Are the any ideas?  Thank you very much.

    Tuesday, February 21, 2012 7:15 PM

All replies

  • Is this code inside a loop? It looks to me like your code is setting the actual cost for 1 day since the start and end dates are both the same date.

    What exactly did you change between the time it worked and the time it did not work?


    Brian Kennemer – DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

    Tuesday, February 21, 2012 7:27 PM
    Moderator
  • It's a bit hard to interpret your code as you have numbers when field names and constant names would make this much easier to read. Some questions:

    Is DateAssign accurate when you step thru the code?

    28 is AssignmentActualCost?

    In VBA you don't need to set TSV then use TSV.Add unless you are appending a new date time slice. VB.Net should be the same. I would expect to see:

    TSV(1).Value=ActualCost rather than a .Add method.

    Is your code returning the correct values from SQL Server when you step thru?

    What version of Project and has the version changed since the code last worked?

    Are you using the same version of Visual Studio?


    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management

    Tuesday, February 21, 2012 7:57 PM
    Moderator
  • Yes, this is inside a loop, which goes through a dataset at assigns the dollar amount to the date it corresponds with.  For example:

    $5000 on 1/1/2012
    $300 on 1/2/2012
    $500 on 1/3/2012
    $200 on 1/4/2012

    Between the time it worked and when it did not work, I changed how the data was retrieved.  Originally my program was designed to work off of Project files saved into my applications SQL Server database.  However, since Project 2007 on files cannot be saved to SQL Server.  Therefore, I had to upgrade my code to pull task ID's in another manner.

    Oddly enough, the actual cost is being applied to the correct task, so I don't think that's the issue.  It's just lumping the timephased data into a single sum.

    Thursday, February 23, 2012 9:43 PM
  • Yes, dateAssign is accurate.

    28 is the constant number that tells the TimeScaleData to assign to Actual Cost.

    All the correct values are coming back from SQL Server, and even the totals that come into Project are correct.  Trouble is, I don't want totals... I want the data spread.  That's why I'm using TSV.Add - I want to add a date time slice for every value that comes out of SQL Server.  I'm afraid I cut out the loop because I figured the error was here (or in the Project settings), and I didn't want to complicate the issue.  Should I post the entire block of code?

    I'm using Microsoft Project 2003, which I've used since I began development.  My version of Studio has not changed either.

    Thank you very much for your effort.

    Dustin

    Thursday, February 23, 2012 9:48 PM
  • I still think something is not right with how you handle the Timescale values. .Add should not be necessary. Easiest solution might be to convert back to VBA and get it working there (no need to read from SQL Server, force feed actual costs) then convert back.

    In a new project add 1 task of 1d duration starting Feb 2. In options deselect Actual Costs calculated by Project.

    Sub Test()
    Dim Tsvs As TimeScaleValues
        With ActiveProject.Tasks(1)
            Set Tsvs = .TimeScaleData("3/2/2012", "3/2/2012", pjTaskTimescaledActualCost, pjTimescaleDays)
            Tsvs(1).Value = 5000
        End With
    End Sub

    This code automatically adds costs to day after task finishes, no need for .Add. Add-in behavior is the same.

    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management

    Thursday, February 23, 2012 9:58 PM
    Moderator
  • Im not clear on how you are accessing the timescaled data. The way I normally do it is to set the object to be the span of the task. then I loop through the items in the collection that gets added. Then I set the value.

    You dont have to use the add method. The object is already there so you dont have to add it, you just have to set the value. My gut says that this is where the issue might be.

    the code below is how I generaly set tsv values. It is setting baselinework but it works just the same.

    If I were you I would set the object to start on the first day of your data set and end on the last day of your dataset and then loop through and set you 5000, 300, 500 and 200. If you have gaps inbetween values I would check for that and then just set them as $0.

    Set TSV = T.TimeScaleData(SDate, EDate, pjTaskTimescaledBaselineWork, pjTimescaleDays)
    For Counter = 1 To TSV.Count
        If IsNumeric(TSV(Counter).Value) = True Then
            BLWorktoStatus = BLWorktoStatus + TSV(Counter).Value
                 
        End If
    Next Counter


    Brian Kennemer – DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

    Thursday, February 23, 2012 9:58 PM
    Moderator
  • Yep your way is how I do it Brian. However given only the snippet of code we were, specific days only may be given. The only problem with the snippet is the .Add which adds an extra day to the date range which I suspect is not intended. So Dustin, can you post all the code or have you now fixed the problem?

    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management

    Friday, February 24, 2012 11:17 PM
    Moderator
  • My apologies for taking so long to reply.  I was temporarily called off this project to take care of a higher priority, but now I'm back to this one.  I've changed my code to reflect your suggestions, and included a more complete version here for your attention:

     Dim objAssignment As Assignment
           
            intGridRow = 0
    
            appProj.OptionsCalculation(AutoCalcCosts:=False)
    
                 Dim tsvs As TimeScaleValues
    
            Do Until intGridRow = gridExportOptions.RowCount
    
                If gridExportOptions.GetRowCellValue(intGridRow, gridExportOptions.Columns(2)) = True Then
                    objAssignment = Nothing
                    intTASK_ID = ReturnTaskID(gridExportOptions.GetRowCellValue(intGridRow, gridExportOptions.Columns(0)))
    
                    If intTASK_ID <> 0 Then
                        objAssignment = appProj.Application.ActiveProject.Tasks(intTASK_ID).Assignments.Add(ResourceID:=appProj.ActiveProject.Resources(strRES_ID).ID) 
                      
                        intCounter = 0
                        Do Until intCounter = dsExportDetail.Tables(0).Rows.Count 'Loop through and insert TimeScaledData
    
                            If dsExportDetail.Tables(0).Rows(intCounter).Item("BD_TASK_ID") = gridExportOptions.GetRowCellValue(intGridRow, gridExportOptions.Columns("MSPTask_UID")) Then
    
                              
                                If CDec(dsExportDetail.Tables(0).Rows(intCounter).Item("Total")) > 60000000 Then
                                    boolWarning = True
                                Else
    
                                    If dsExportDetail.Tables(0).Rows(intCounter).Item(2) Is DBNull.Value Then
    
                                        dateAssign = Now() 'If there is no date, assign data to the current day
    
                                        tsvs = objAssignment.TimeScaleData(dateAssign, dateAssign, 28, 4)
                                        tsvs.Add(dsExportDetail.Tables(0).Rows(intCounter).Item("Total"), 1)
    
                                    Else
    
                                        dateAssign = dsExportDetail.Tables(0).Rows(intCounter).Item(2)
                                        tsvs = objAssignment.TimeScaleData(dateAssign, dateAssign, 28, 4)
    
                                        ' tsvs.Add(dsExportDetail.Tables(0).Rows(intCounter).Item("Total"), 1)
                                        'For Each tsv In tsvs
                                        tsvs(1).Value = dsExportDetail.Tables(0).Rows(intCounter).Item("Total")
                                        ' Next
                                        'Add TSV data
    
                                    End If
                                End If
                            Else
    
                          End If
                            intCounter += 1
                        Loop
         
                    End If
                End If
               intGridRow += 1
    
            Loop

    This causes a couple NEW issues:

    1)  The Cost total for the task is accurate.  However, the timephased data is still blank.
    2)  The last record for the first task throws an error: "The argument value is not valid."  I don't understand why this is occurring - the date is valid ('6/16/2011'), and the amount is $240.00.

    Any ideas on what I'm missing?

    Thanks!

    Wednesday, July 11, 2012 8:35 PM