Copy Planned Hours to Actuals in Project Server’s Timesheet Page

Following is the code which will help users to copy and paste the planned Work into actual work in their timesheet.

    1. Create a Notepad file and put the below code in
<div id ="target" style="color:Blue;background-color:#f2f2f2;" align="right">Click here to copy Planned into Actuals!!</div>
<script src=""></script>
$( "#target" ).click(function() {
	var mytable;
	  var curTable = $(this);
		  if(mytable.indexOf('_TimesheetPartJSGridControl_rightpane_mainTable') > -1)
			var table = document.getElementById(mytable);
			for (var i=1, row; row = table.rows[i]; i++) 
				var rownumber=0;
				//iterate through rows
				//rows would be accessed using the "row" variable assigned in the for loop
				for (var j = 1, col; col = row.cells[j]; j++) 
					//iterate through columns
					//columns would be accessed using the "col" variable assigned in the for loop
						var a=row.cells[0].firstChild.innerHTML;
							var AVal=table.rows[i].cells[j].innerHTML;
					catch(err){	alert(err.message);}
			alert("Operation Completed!!");
  • 3 Now, Upload the same text file into the Site Assets folders
  • 4 then add CEWP to the Timesheet page and link them to above code by editing the webpart
  • 5 Once, linked with the text file. A button (“Click here to copy Planned into Actuals!!”) appear which will just copy and paste planned work to actual work.

Result will look something like this


Above code to work do not forget to add the Planned Row into your timesheet view. Same can be enabled by going to the options tab on timesheet page and then checking the Planned checkbox.

Hope this helps someone!

Audit on SSRS Report Export with Project Server

Recently I had a request from customer about generating an Audit for SSRS. Generally, this has to do with the Export. How administrator knows, that project managers have generated their monthly report for submission, which they are supposed to do every month and submit the same to PMO for their Monthly Review. To Achieve this we need to look into the reporting database. which gets generated when configured Reporting server site using Configuration wizard.

Since,we have Project Server database and Reporting database. In Reporting database there is a table “ExecutionLogStorage” which has this information. And Below is the query to know if Project Managers have generated their report and exported it to PDF/EXCEL etc.

SELECT UserName,ProjectName,datename(MM,TimeEnd),datename(YEAR,TimeEnd),Roadmap  
from  ExecutionLogStorage ELS, EPM_UAT_PWA.dbo.msp_epmproject_userview P  where P.ProjectUID=cast(substring(Parameters,9,36) as uniqueidentifier)
AND Format='PDF' and Status='rsSuccess' and ReportID in (
select ItemID from [dbo].Catalog where Name=YourReportName.rdl') Order by TimeEnd desc 

In My case Parameters column had the projectuid which I could use to fetch the project uniqueidentifier and later use it to fetch project information.

Forms Authentication in SSRS with Sqlmemberhsip Provider

One of our customer for whom we have developed a portal. wanted us to also create the reports for the same portal/data. And since we had used the Form Auth. for the same portal. we haven been by the customer to use same authentication mode to use for reports as well. Since, By default SSRS provides Windows Authentication and doing that is pretty straight forward.  How ever dong the Form Authentication is not that simple and straight forward (don’t know why MS keep making things complicated for developers) anyhow i started searching the Internet and found some help, but those lacked some documentation. So here is my take on the same problem with little more documentation for others to use and also some of the errors i faced during the implementation.

However, i am yet to solve some of the Issues like

  1. After implementing the Sql Membership provider how am i gonna deploy the reports — Currently, while deploying the report i change the Auth Mode to Windows from Form Auth.
  2. After Login, how to enable the users to give permission to view reports.
  3. Single Sign on is an Issue, because of the Authentication cookies.  But let us start it anyhow and see if we can solve those problems in some time after doing some R&D or with your help😉

So, First you need to download the Solution from here then download and install the solution, you will see the sample solution for the same and just open the solution named : Customsecurity and then you need to change the Logon.aspx.cs file and UILogon.aspx.cs

In my requirement i was supposed to use the Sqlmembership Provider.  Where authentication process simply happens using  Membership.ValidateUser(TxtUser.Text, TxtPwd.Text); 

Once you get the True from the above Form you can allow user to go inside the report server to click on their reports (Here you might face the issue that not all users are allowed to login or you might receive problem regarding permission)

Then Simply set the Auth Cookie using below lines and then redirect to your choice of Url in report server.

 FormsAuthentication.SetAuthCookie(TxtUser.Text, false);

Once, you have changed the code and build the solution you will required to deploy the dll and pdb file  in

  • C:\Program Files\Microsoft SQL Server\MSRS11.aa\Reporting Services\ReportServer\bin
  • C:\Program Files\Microsoft SQL Server\MSRS11.aa\Reporting Services\ReportManager\bin

Now, it is required to change 5 files which needs to be modified in 2 different locations as mentioned below, make sure you take backup of the existing files before changing which will be useful to overcome the problem no. 1 as mentioned above.

  1. Report Server: C:\Program Files\Microsoft SQL Server\MSRS11.aa\Reporting Services\ReportServer
    1. rsreportserver
      1. <!–Added by Parth–>
        <!–Ends here–>
      2. <!–added By Parth–>
        <!–ends here–>
      3. <!–<Security>
        <Extension Name=”Windows” Type=”Microsoft.ReportingServices.Authorization.WindowsAuthorization, Microsoft.ReportingServices.Authorization”/>
        <Extension Name=”Windows” Type=”Microsoft.ReportingServices.Authentication.WindowsAuthentication, Microsoft.ReportingServices.Authorization”/>
        <!–added by Parth–>
        <Extension Name=”Forms” Type=”Microsoft.Samples.ReportingServices.CustomSecurity.Authorization,Microsoft.Samples.ReportingServices.CustomSecurity”>
        <UserName>User which is in your Membership Provider</UserName>
        <Extension Name=”Forms” Type=”Microsoft.Samples.ReportingServices.CustomSecurity.AuthenticationExtension,Microsoft.Samples.ReportingServices.CustomSecurity”/>
        <!–ends here–>
    2. rssrvpolicy
      1. Add the Following lines after Url=$CodeGen$          <CodeGroup
        Description=”Code group for the sample security extension”
        Url=”Microsoft.Samples.ReportingServices.CustomSecurity.dll location inside bin folder“/>
    3. Web
      1. <!–<authentication mode=”Windows” />
        <identity impersonate=”true” />–>
        <!–This is added by Parth –>
        <authentication mode=”Forms”>
        <forms loginUrl=”Logon.aspx”
        path=”/” >
        <identity impersonate=”false” />
        <deny users=”?” />
        <membership defaultProvider=”AspNetSqlMembershipProvider11″>
        <add name=”AspNetSqlMembershipProvider11″
        applicationName=”/” />
        <!–Ends Here–>
      2. <connectionStrings>
        <add name=”UserAccounts” connectionString=”Data Source=<Server Name>;Initial Catalog=<Database Name>;User ID=<UserName>;Password=<Password>” providerName=”System.Data.SqlClient”/>
  2. Report Manager: C:\Program Files\Microsoft SQL Server\MSRS11.aa\Reporting Services\ReportManager
    1. rsmgrpolicy
      1. <!–Changed By Parth: Changed from Execution to FullTrust–>
        Description=”This code group grants MyComputer code Execution permission. “>
        Zone=”MyComputer” />
    2. Web
      1. <!–This is added by PArth –>
        <authentication mode=”Forms” />
        <identity impersonate=”false” />
        <!–Ends Here–>
        <!–<authentication mode=”Windows” />
        <identity impersonate=”true” />–>

In my case only 1 user authentication is enough as  my users do not require the single sign on because reports will only open from the our portal and while they reach reports/dashboard we check for valid session. and i think this is the trade off😉

Also, email me @ if you wish to receive config files

Note: Visual Studio  will not allow deploying the new report as whenever, you will try to deploy the new reports it will asked for credentials and even if you put credentials it will not deploy the reports. So everytime you need to deploy the reports change the authentication mode to  Windows by replacing the changed config files to old ones. then deploy the reports and revert back the config files to enable the forms authentication

After Copy BCWP Value changes in MSP’s Task Usage View

Last year had been busy, in fact very busy🙂 and it continues. during this time i have come across a problem in Project Server.

As usual our Sales team has promised customer 5 reports (part of their marketing plan where they do not check the requirements) without any requirement gathering. for which we landed in soup and it took hell lot of time for us to create those reports. in this one of the requirement was to show the earned value on monthly basis. which can be seen inside the MSP but unfortunately it is not available inside the Project Server database. and same has been confirmed by the MS during support call.  however customer being customer. they were adamant on getting this values from MSP, as it is crucial part in their reports. and customer being the construction domain earned value is their highest priority.

So, we went ahead and created the custom database. which takes the snapshot in monthly basis and stores the Project/ Task’s BCWP in my custom database. Now, there was another problem since this earned value is cumulative on month on month basis. this solution also did not served purpose. then we suggested that let’s introduce 2 custom fields

  1. Earned Value Current Month
  2. Earned Value Previous Month
  3. Net Earned Value (Calculated Field) = Earned Value Current Month – Earned Value Previous month + Cumulative EV for Last Month

after above custom field creation, we thought we have solved the purpose/ requirement of the customer. but no we faced another challenge where when we copy the BCWP from Task Usage view to Custom field. Somehow, that value gets change automatically. Which is again an Issue and not acceptable at all. then we found an article where MS has suggested to write a Macro which copies the BCWP and stores it inside the Custom Field. 

Now,Considering the pain i had to go through. and also i  i don’t want others to go through it. so below is how you create the form and update the custom Field. and form will look something like this:

Paste Earned Value

As you can see in above image After copy and Paste values change in Task Usage sheet. now without any further a due let’s just go with the code.


Private Sub btnCurrent_Click()
On Error GoTo A
    Call TimePhasedData(txtCurrStart.Value, txtCurrFinish.Value, "Earned Value Current Month")
    MsgBox "Previous Month EV value has been pasted successfully"
    Exit Sub
MsgBox Err.Description
Exit Sub
End Sub
Sub TimePhasedData(s As Date, F As Date, CF_Name As String)
Dim T As Task
Dim tsv As TimeScaleValue
Dim tsvs As TimeScaleValues
   'Timephased for Task with a UniqueID of 1
   For Each T In ActiveProject.Tasks
        Set tsvs = T.TimeScaleData( _
           StartDate:=s, _
           EndDate:=F, _
           Type:=pjTaskTimescaledBCWP, _
           TimeScaleUnit:=pjTimescaleMonths, Count:=1)
        For Each tsv In tsvs
           Debug.Print "Task Name: " & T.Name & "Start: " & Format(tsv.StartDate, _
              "Long Date"), "BCWP: " & Val(tsv.Value) & "$"
         T.SetField (FieldNameToFieldConstant(CF_Name)), Val(tsv.Value)
        Next tsv
        Debug.Print 'Blank line
   Next T
End Sub

Update Project Custom Field in Project Online

Hi, It’s been many days i have written a blog. been busy working on some of the mobile projects outside Project Server. For a change.🙂

Let’s do something which every Project Server Developer does, almost on daily basis. Updating the Project Custom fields.

As usual without wasting any time of yours and mine, straight to the code. Please leave a comment in case you require any clarification😉

Remember, updating the Project Level custom field is very easy in Project Online, you just have to append “custom_” with every UID of the custom field and it will straight up updates the custom field for you. which can be of any type other than Lookup (refer this to get the code value of the lookup table.

 private static void UpdateProjectCustomField()
            DraftProject projCheckedOut = null;
                Dictionary&lt;string, object&gt; projDict = new Dictionary&lt;string, object&gt;();
                MsOnlineClaimsHelper claimsHelper = new MsOnlineClaimsHelper(&quot;https://abc/sites/pwa&quot;, &quot;Email Add&quot;, &quot;password&quot;);

                using (ProjectContext projContext = new ProjectContext(&quot;https://abc/sites/pwa&quot;))

                    projContext.ExecutingWebRequest += claimsHelper.clientContext_ExecutingWebRequest;

                    var PrjList = projContext.LoadQuery(projContext.Projects.Where(proj =&gt; proj.Name == &quot;A1 Customer Demo&quot;));
                    Guid pGuid = PrjList.First().Id;

                    PublishedProject proj2Edit = PrjList.First();
                    projCheckedOut = proj2Edit.CheckOut().IncludeCustomFields;

                    projDict = projCheckedOut.FieldValues;
                    if (!projDict.Contains(new KeyValuePair&lt;string, object&gt;(&quot;Custom_f80c56d8-b0ee-e211-b62d-00155d84f60d&quot;, &quot;&quot;)))
                        projCheckedOut.FieldValues.Add(&quot;Custom_f80c56d8-b0ee-e211-b62d-00155d84f60d&quot;, &quot;&quot;);
                    foreach (KeyValuePair&lt;string, object&gt; kvp in projDict)
                            Console.WriteLine(&quot;\tKey = {0}, Value = {1}&quot;, kvp.Key, kvp.Value.ToString());
                        catch { }

                    projCheckedOut.SetCustomFieldValue(&quot;Custom_da7594e7-e1f8-e211-85bd-00155d853508&quot;, &quot;Technology Management&quot;);
                    QueueJob qJob = projCheckedOut.Publish(true);
                    JobState jobState = projContext.WaitForQueue(qJob, 70);
            catch (Exception ex)


Update Resources using CSOM in Project Online


Recently, on one of the customer places we have been asked to do an Integration with their resource system. while doing so I was in a need of updating the Resource Level Customfield. Very Innocently i took an Approach of updating the Resource using the CSOM. but behold i could not find any method which will do so😦 then I logged a call with the microsoft support team and they have also come back saying that it is not possible, Forcibly i had to took the web services approach. And which took me to the unknown territory of authentication and authorization😦

So, Project Online does dual authentication which can be managed using the ClaimsHelper Class. but in our case client was using the Claim based authentication. so they used to have their own authentication page. and cause of that even ClaimsHelper class was not helping😦

Then i started working on storing cookies directly and passing them with the Web service’s CookieContainer. and then i was able to update the Resources.

First Let us refer to the Code shared by Microsoft on recording cookies and use the CSOM to fetch Web Title…

In the same solution Here, you will find the ReadCookies class which will read and then convert the same into the Cookie Collection using the following Code:

 private CookieCollection ExtractAuthCookiesFromUrl(string url)
            Uri uriBase = new Uri(url);
            Uri uri = new Uri(uriBase, "/");
            // call WinInet.dll to get cookie.
            string stringCookie = CookieReader.GetCookie(uri.ToString());
            if (string.IsNullOrEmpty(stringCookie)) return null;
            stringCookie = stringCookie.Replace("; ", ",").Replace(";", ",");
            // use CookieContainer to parse the string cookie to CookieCollection
            CookieContainer cookieContainer = new CookieContainer();
            cookieContainer.SetCookies(uri, stringCookie);
            return cookieContainer.GetCookies(uri);

Now, same will be used to pass into the Resource.asmx to update the Resource level custom fields.

Note:  How to get the cookies, Please use the the full solution where you found the ReadCookies Class. and debug the same to get the cookies as string which then can be pass into the above method which will be a cookie collections.

Hope this will help someone!!

Read Project & Task level custom field (Lookup)

Those who are working with project server will definately like to go with the CSOM, as It reduces lot of development effort for a developer in terms of the retrieving the custom field values of the project,task and resources. with CSOM it is very easy to fetch the custom field information by using the simple API which is FieldValues. By simply using such API CSOM will return the custom field information.

Now, this works perfectly ok when we are trying to fetch the normal custom field values. but when we need to get the custom field information, which has the lookup table is not worry hard but i would say little tricky.

Remember when we had to get the custom field values which had the lookup in the Project server 2007/2010. We need to get the project level custom fields and loop through to get the MD_PROP_ID and MD_PROP_UID and other details which will find the custom field name and Guid. then find the lookup values and match with the code values and lot of other stuff. 

Well, Here also we need to do few steps as mentioned below in order to fetch the information.

  1. First get all of the custom fields from the O365 and Loop through the same to get the InternalName of the Custom field
  2. Image
  3. This method will fetch the values from the Project Online for that lookup as we will only get the CODE_VALUE
  4. Now below is the code:
private static string CF_LookupValues(ProjectContext projContext, string CustomfieldName, string Value)
            string str = "NA";
                MsOnlineClaimsHelper claimsHelper = new MsOnlineClaimsHelper(PWAUrl, UserName, Password);
                using (projContext = new ProjectContext(PWAUrl))
                    projContext.ExecutingWebRequest += claimsHelper.clientContext_ExecutingWebRequest;

                    var CustomFieldName = projContext.LoadQuery(projContext.CustomFields.Where(CF =&gt; CF.Name == CustomfieldName));


                    Guid LTuid = CustomFieldName.First().LookupTable.Id;

                    var LookupTableList = projContext.LoadQuery(projContext.LookupTables.Where(LT =&gt; LT.Id == LTuid));

                    foreach (LookupEntry lutEntry in LookupTableList.First().Entries)
                        Guid Id = new Guid(Value.Remove(0, 6));
                        if (lutEntry.Id == Id)
                            str = lutEntry.FullValue.ToString();
                        //Console.WriteLine("\n\t{0}, SortIndex: {1}\n\tFullValue: {2}",lutEntry.Id, lutEntry.SortIndex.ToString("F0"), lutEntry.FullValue);

            catch (Exception ex)
            { }
            return str;