MS Project 2007 – How to assign resources with different working times

Create a Resource Calendar with 9 hrs and assign it to the Resource. MS Project will start to allocate resource for 9 hrs. You are not able to see the Change in Work hrs in your Gnatt Chart Table becuase there MSP shows 8 hrs as Work on the task, while in backend resource is made to work 9 hrs.

So if you want to see the change open Resource Usage View and therein Yellow timephased sheet you can see that resource is in fact working 9 hrs on the Task which is shown 8 hrs in your Gnatt chart view. Because when we plan a task we specify its Duration rather then the resource efforts in first place.

See at the linked Snapshot:

Project Calendar : 8 hrs    Resource “h” have 9 hrs Calendar

So when I assign h to a task of 3 days – Work gets calculated as per Project Cal i.e. 3 days X 8 hrs = 24 hrs. But h actually works 9 hrs on the task to finish 24 hrs of work.

---

Read Full Post »

Strange Behaviour with Project Duration when Task Constraint is changed from ASAP to ALAP

Its not a Bug of MS Project; its entirely the mixture of its various Algorithms trying to balance : Constrain Types , Start Date & PERT Chart’s Backward tracing of Late Start Date Calculation for Duration.

Replicated the Issue and found that :

As long as % Complete is 0% for last ALAP Task  >>

1. Summary Level Tasks & Project Summary Task is taking the appropriate dates as per the “Dependencies” no matter whatever the Constrain is ASAP or ALAP. This trend continues with % Complete happening until last task in the Plan with “ALAP” is marked 100% Complete.

2. The moment last ALAP is 100% Complete Project Start Date ignores all Dates and pick up the Start Date of that task as Start Date for Summary Level Tasks & Project Summary Task. Reason being now it collate all ALAP to reach to final concluding ALAP Date which gets reflected in Start Date.

MS Project Start Date use formula : Start + Delay , in this case Delay is basically what MS Project saves as backtrack float while calculating ALAP path for the Schedule PERT Chart. It cannot pick the Start Date until last ALAP is Finish.

 

NOTE :

1.     Summary Level & Project Summary Task Start Date takes “Start Date of last ALAP Task” of a Plan when it is marked 100%.

2.     Duration never changes honoring the Longest Path algorithm. Its just the Start Date that reflects unexpected Duration [here it shows 1 Day as per the Start & Finish Dates but duration Field still have 3 days.] Start Date is running on Backward tracing of the PERT Chart Late Start calculations.

3.     Exact Dates are getting captured any ways in “Actual Start” field of MSP. If we change Actual start Date, Start Date adjust themselves and still follow the above cycle for last ALAP Task.

Hope this Clarifies why Start Date takes the Start Date of Task 3 in your case when you mark it 100%., while it remains Intact when you have ALAP tasks as 0%.

---

Read Full Post »

Hammock Tasks

Because the hammock task relies on other tasks for start/finish information, anything that affects those other tasks (resource leveling, constraints) will ultimately affect the hammock tasks dates.

You can either control Work or Resource Units as Hammock Task’s duration gets derived from linked tasks.

If you want to Conrol Work hrs – make task as “Fixed Work” in this case with increase and decrease in the Duration, Unit % will get changed. If you want to Control Unit % – make task as “Fixed Unit” this will increase or decrease Work in proportion to the Duration

 

Say you have Hammock Task with : Duration – 2d   Unit – 1 Resource [100%]  Work – 16 hrs.

Now due to changed in linked task Hammock Task change its duration to 4 Days.

______________________________________________________________________________________________________

Fixed Work Scenario :

Duration : 4

Work Remain same [as initially calculated by VB Macro’s Duration & Resourcing] : 16 hrs

Units : R1 [25%]

Implying that now resource will work 4 hrs/day to Complete 16 hrs Work in 4 Days.

Fixed Unit Scenario :

Duration : 4

Fixed Unit : R1 [100%]

Work = Fixed Unit( hrs per day) X Duration = 8hrs X 4 days = 32 hrs

__________________________________________________________________________________________________

NOTE : Making Hammock Tasks of “Fixed Duration” works fine in duration bracket which suffice its Start & Finish tasks Dates, beyond that if manually Duation for Hammock task is increased though it maintains its Finish linked Task Date but start ignoring Start Date that is drived by other task. In that case Hammock task automatically schedule itself to Start Prior to Finish Date of task from which it was drieving its Start Date intitally.

Hope this helps 🙂

Read Full Post »

Edits Spread to Status Date

By Default “Edits to total task % Complete will be spread to the Status Date” remains DESELECTED in MS Project.

The option implies that : What ever is the “% Complete” for a task, it gets broken up evenly from Start to Status Date. When this option is DESELECTED “% Complete” gets distributed till Today.

To View appropriate reflection of this Toggle option -> Open Task Usage View -> Insert “Percentage Complete” Row in the the timephased grid (the yellow timesheet grid on the right side of the View); and try to read % Complete for 2 tasks [ 1 with this option Selected & other with this option Deselected].

Scenario :

Start Date (both Task) – 10th-Dec-10 , Status Date : 12th Dec 10, Today is 14th Dec 10.

Task Option Selected [TS]: % Complete = 70%

Task Option Deselected [TD]: % Complete = 70%

Task Usage Timephased Grid [Yellow Graph] will Show :

           10th        11th     12th     13th     14th        

TS  :    15%        15%     15%      15%    10%             [Here edits in % Complete is broken up equally till Current Date 14th Dec (here)]

TD :     25%         25%    20%                                    [Here edits in % Complete is broken up equally till Status Date 12th Dec (here)]
Hope this Clarifies.

Read Full Post »

Show Your Project Status On Google Map

Have been working with telecom industries for a while now (But its not limited to telecom though it was suitable for them but it can be used for othe rverticals where projects are typically spread out), as pretty obvious thing visual representation strikes more than textual data, so i was thinking over how could i do so and make it interesting, and here came the thought why not show their project status with those beautiful little colorful indicator(marker) on the map representing their status , and i started writing a module to do that, now typically i should have chosen MS Virtual earth as a map, but instead my instinct said lets paly with google API, and i started exploring the options, i wasn’t familiar with much of the langauages other than Java Script and so i started of with Java Script, now the next problem was how do i integrate it with project server as PS API (Now in My Code i Simply used Data From RDB, you may want to extend it to ) interact using .NET code and Google map with Java, so i used the trivial method of building dynamic javascript and pass it to the ASP.NET Literal control to render, though i know it could have been done much better, but i leave that to you 🙂

So here goes my code

——————————————–Edit Default.aspx Source File to Include reference & Place Holder for JS—–
<%

@ Page Language=”C#” AutoEventWireup=”true” CodeFile=”Default.aspx.cs” Inherits=”_Default” %>
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”&gt;
<html xmlns=”http://www.w3.org/1999/xhtml”&gt;
<head id=”Head1″ runat=”server”>
<title>Project Data on Map By Sunil </title>

<%–Google API reference–%>
<%–The Below Key should be replaced by a new one once it gets on production, it will work till its on Localhost–%>
<script src=”http://maps.google.com/maps?file=api&amp;v=2&amp;sensor=false&amp;key=abcdefg&#8221; type=”text/javascript”></script>
</head>
<body onload=”initialize()” onunload=”GUnload()”>
<form id=”form1″ runat=”server”>
<asp:Panel ID=”Panel1″ runat=”server”>
<%–ASP Literal Control – Place holder to fill with javascript by server side code–%>
<asp:Literal ID=”js” runat=”server”></asp:Literal>
<div id=”map_canvas” style=”width: 100%; height: 728px; margin-bottom: 2px;”></div>
<br />
</asp:Panel>
</form>
</body>
</html>
———–Pretty Simple Right —————————————————-
Now Move Onto Default.aspx.cs file and paste the code

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Sqlclient; 

public partial class _Default : System.Web.UI.Page
{    protected void Page_Load(object sender, EventArgs e)
    {
        DataTable dtbl = new DataTable();
        DataColumn myDataColumn;
        myDataColumn = new DataColumn();
        myDataColumn.DataType = Type.GetType(“System.String”);
        myDataColumn.ColumnName = “Latitude”;
        dtbl.Columns.Add(myDataColumn);
       myDataColumn = new DataColumn();
        myDataColumn.DataType = Type.GetType(“System.String”);
        myDataColumn.ColumnName = “Longitude”;
        dtbl.Columns.Add(myDataColumn);
        /*Now the below Lat & Long are just for demo purpose, you may replace it with a web service
        (there are plety of them freely available) which will map location name with lat long and in turn can
        be used to map your project status on map, you should give people a project custom field which will give
        them option to selct location, and that should be mapped to the lat & long */
        DataRow dr;
        dr = dtbl.NewRow();
        dr[0] = “28.616667”;
        dr[1] = “77.216667”;
        dtbl.Rows.Add(dr);
        DataRow dr1;
        dr1 = dtbl.NewRow();
        dr1[0] = “17.2”;
        dr1[1] = “78.3”;
        dtbl.Rows.Add(dr1);
        //////////////////////Get PS RDB Data///////////////////////
        System.Text.StringBuilder NewTextBuilder = new System.Text.StringBuilder();
        string ConnStr = “Data Source=ServerName;Initial Catalog=ReportingDB;User Id=UserName;Password=password”;
        System.Data.SqlClient.SqlConnection Conn = new SqlConnection(ConnStr);
        string Query = @”SELECT      [ProjectName],[FileName],PlannedEndDate], [ProjectID],[Theme],  [PlannedStartDate] FROM [ReportingDB].[MSP_EPMProject_USerView]”;
        SqlCommand Cmd = new SqlCommand(Query);
        Cmd.Connection = Conn;
        SqlDataAdapter Da = new SqlDataAdapter(Cmd);
        Da.Fill(Ds);
        BuildScript(dtbl,Ds);
  }

private void BuildScript(DataTable tbl,DataSet Ds)//Pass Data Table containing Latitude/Longitude(mandatory to plot map marker – Project level Custom Field)                                       //and Other Parameters From Project RDB as many as you like
{    int i = 0;
   /////////////////The Below mentioned icons can be replaced with your custom icons/////////////////
   string GreenIcon = @http://gmaps-samples.googlecode.com/svn/trunk/markers/green/blank.png;
    string RedIcon = @http://gmaps-samples.googlecode.com/svn/trunk/markers/red/blank.png;
    string OrangeIcon = @http://gmaps-samples.googlecode.com/svn/trunk/markers/orange/blank.png;
    string PinkIcon = @http://gmaps-samples.googlecode.com/svn/trunk/markers/pink/blank.png;
    string BlueIcon = @http://gmaps-samples.googlecode.com/svn/trunk/markers/blue/blank.png;
   foreach (DataRow r in tbl.Rows)
   {
       int ProjectStatus = i;
       String MarkerText = Ds.Tables[0].Rows[i][0].ToString();//@”Project Status”; // This Should be replaced with dynamic Project Data, whatever you want to display
       // Should be used for Passing Project Status Based on Custom Calculation
     // bypass empty rows
       if (r[“Latitude”].ToString().Trim().Length == 0)
           continue;
         string Latitude = r[“Latitude”].ToString();
         string Longitude = r[“Longitude”].ToString();
        NewTextBuilder.Append(Environment.NewLine);
        NewTextBuilder.Append(” var point” + i + ” = new GLatLng(” + Latitude + “,” + Longitude + “); “);
        NewTextBuilder.Append(Environment.NewLine);
        NewTextBuilder.Append(Environment.NewLine);
        NewTextBuilder.Append(“var Plot” + i + “Icon = new GIcon(G_DEFAULT_ICON);”);
        NewTextBuilder.Append(Environment.NewLine);
        NewTextBuilder.Append(“markerOptions = { icon:Plot”+ i +”Icon };”);
        if (ProjectStatus == 0) { NewTextBuilder.Append(” Plot” + i + “Icon.image = ‘” + RedIcon + @”‘; “); }
        if (ProjectStatus == 1) { NewTextBuilder.Append(” Plot” + i + “Icon.image = ‘” + GreenIcon + @”‘; “); }
        if (ProjectStatus == 2) { NewTextBuilder.Append(” Plot” + i + “Icon.image = ‘” + OrangeIcon + @”‘; “); }
        if (ProjectStatus == 3) { NewTextBuilder.Append(” Plot” + i + “Icon.image = ‘” + PinkIcon + @”‘; “); }
        if (ProjectStatus == 4) { NewTextBuilder.Append(” Plot” + i + “Icon.image = ‘” + BlueIcon + @”‘; “); }
        NewTextBuilder.Append(Environment.NewLine);
        NewTextBuilder.Append(” var marker” + i + ” = new GMarker(point” + i + “,markerOptions); “);
        NewTextBuilder.Append(Environment.NewLine);
        NewTextBuilder.Append(” map.addOverlay(marker” + i + “,” + i + “);”);
        NewTextBuilder.Append(Environment.NewLine);
        NewTextBuilder.Append(” marker” + i + “.value = ” + i + ” ;”);
        NewTextBuilder.Append(Environment.NewLine);
        string markerEvent = ” GEvent.addListener(marker” + i + “,\”click\”, function(){ ” + ” map.openInfoWindowHtml(point” + i + “,'” + MarkerText +” – + i + “‘);}); “;
        NewTextBuilder.Append(markerEvent);
        i += 1;
   }
  // construct the final script
  js.Text = @”<script type=’text/javascript’>
              function initialize()
              {
                  if (GBrowserIsCompatible())
                  {
                    var map = new GMap2(document.getElementById(‘map_canvas’));
                    map.setCenter(new GLatLng(28.63,77.2), 4);
                    ” + NewTextBuilder.ToString() + @”
                    map.setUIToDefault();
                  }
               }
         </script> “;
}
}

 

Read Full Post »

Tribute to LFD Team

Bahut yaad aata hai wo gujra zamaana,
LFD ki raaton mein wo jaagna jagaana,
Wo raat raat bhar user synchronization chalaana,
subah uth ke Database restore corrupt pana,
kabhi unsupported scenario decalaration ka baad kati patang ho jana,
to kabhi UAT pass ho jane pe Tees Mar Khan ban jana…

Bahut yaad aata hai wo gujra zamaana,
LFD ki raaton mein wo jaagna jagaana,

Woh gaadi ke bonut pe kabhi machhi khaana,
Wo Chole kulche ki theli aur paraathe ke saath manchurian lagaana,
wo Cofee Machine ke band ho jane pe team ki energy ka lut jana,
wo tea break me agli shift ke logo ka naam jutana…
jaagi neend mein raat raat bhar wo PSI code chaalana,
restore chalaate chalaate wo kursi pe ludhak jaana,
Database backup me space kam ho jana, wo raat mein atul duggal ko phone lagana,
wo HTTPS ke liye SSL(Vipin) ko lagana
Reporting DB ka Publish DB se har roj bhid jana…
Bahut yaad aata hai wo gujra zamaana,
LFD ki raaton mein wo jaagna jagaana,

Yaad ata hai wo Gaurav ka achanak se aa jana,
aur uske aate hi influenza, body ache and severe bimariyo ka fel jana,
ek seat pe 3 -3 baithne walo ka floor ke kone knone me chup jana,
phir bhi Gaurav ki najaro me query chalate hue amit(Mr Report) ka fas jana..
Wo PSI Code chaalate hue Devendra ka dar dar kr bhaagna

Bahut yaad aata hai wo gujra zamaana,
LFD ki raaton mein wo jaagna jagaana,

Bohat pyare the wo din, jab na raat dekhe the na din,
Na bhookh dekhi the na pyaas, na salary ki thee koi aas,
har haal me…har jeet har haar me…LFD team thee ek sath…
wo team ka dukh ko chupa ke kehna, “Hum hain to kya gam hai”

choad gye ane walo ke liye nishani..
“khub lade mardane…wo to the LFD wale prani the”
—————————————Contributed By Sushil———————————————-
Wo raat mein sutte ki tadap mein SSL ka chatpataana
der raat mein jab saari dukaan band ho, to jugaad ke liye mujhse jack lagwaana 
sir se dheere se sharmaate hue lena, phir sutta lagate hue wo mand mand muskaana
Sach bahut yaad aata hai wo gujra zamaana
——————————————————————————————————————–

Read Full Post »

Search Database For Any Specific Text

While working with Project Server / Protfolio Server Reporting DB, I often faced problems keep on looking / searching for certain column names, where the heck is that column to reference !!! LOL, Even though you would say, SDK has all the schema 😦 , Still at times it became very difficult to find the Column References, for eg – try looking SDK for Resource Cost Rate Table Info, and i bet you would have to try hard n hard to find it,
Then i thought why not have a utility which will search specific texts for me in all tables and give me the columns name which contain that particular text, wouldn’t that make my life easier and here i wrote one such piece, thought of sharing as it would help you reduce some work load atleast 🙂

CREATE TABLE #TempTable (TABLE_NAME sysname, COLUMN_NAME sysname, MatchesFound int)
GO
SET NOCOUNT ON
DECLARE @SQL varchar(8000), @TABLE_NAME sysname, @COLUMN_NAME sysname, @SearchString varchar(80), @Count int
SELECT @SearchString = ‘Cost Rate’
DECLARE SearchCursor CURSOR FOR 
 SELECT c.TABLE_NAME, c.COLUMN_NAME    FROM INFORMATION_SCHEMA.Columns c INNER JOIN INFORMATION_SCHEMA.Tables t
     ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
  WHERE c.DATA_TYPE IN (‘char’,’nchar’,’varchar’,’nvarchar’,’text’,’ntext’)
    AND t.TABLE_TYPE = ‘BASE TABLE’
OPEN SearchCursor 
FETCH NEXT FROM SearchCursor INTO @TABLE_NAME, @COLUMN_NAME
WHILE @@FETCH_STATUS = 0
 BEGIN
  SELECT @SQL = ‘INSERT INTO #TempTable (TABLE_NAME, COLUMN_NAME, MatchesFound) SELECT ‘    + ”” +  @TABLE_NAME + ”” + ‘,’    + ”” + @COLUMN_NAME + ”” + ‘,’    + ‘COUNT(*) FROM [‘ + @TABLE_NAME      + ‘] WHERE [‘ + @COLUMN_NAME + ‘] Like ‘    + ””+ ‘%’ + @SearchString + ‘%’ + ””  
  EXEC(@SQL)
  IF @@ERROR <> 0
   BEGIN
    SELECT @SQL
    SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @TABLE_NAME
    GOTO Error
   END
  FETCH NEXT FROM SearchCursor INTO @TABLE_NAME, @COLUMN_NAME
 END
SELECT * FROM #TempTable WHERE MatchesFound <> 0
Error:
CLOSE SearchCursor
DEALLOCATE SearchCursor
GO
DROP TABLE #TempTable
GO
SET NOCOUNT OFF–__________________
–Sunil Kumar Singh

Read Full Post »