A Better Way to Create Indicator

Sometimes, you need to create a very complex formula for indicator.  It’s very hard to understand and maintain.   

For example, client wants to show schedule indicator green when duration variance within 10% of baseline duration, 11%~20% for yellow and above 20% shows red. 

 

Sample formula:

SWITCH([Finish Variance]=0,"On Target",[Finish Variance]<0,CSTR(CINT([Finish Variance]*(-100)/[Baseline Duration]))+"% Ahead Of Schedule", true,CSTR(CINT([Finish Variance]*100/[Baseline Duration]))+"% Over Schedule")

Then when you set graphic indicator, use "Is Less Than" or "Is Greater Than".  You can set as

Is Less Than     11      Green

Is Less Than     21      Yellow

Any Value                  Red

 

Project Professional is smarter enough to compare the percent as number.  You can event put percent in the middle of the data, such as "Task 10% Behind Schedule".

 

Project Server 2007 Active Directory Synchronization Error

I have experienced Project Server 2007 Active Directory Synchronization failure on client.  As the result, Active Directory cannot be synchronized and even worse, the user/resource edit page shows error when you tried to edit a synchronized user/resource.  It may potentially create corrupted assignments in project plan.

 

In the event log on project server, there are errors in application category.

 

 

Error Message:

 

Standard Information:PSI Entry Point:

Project User: IHESS\sdai

Correlation Id: 837bfd5f-9502-4e09-a193-2af20c8392b0

PWA Site URL: http://hacssia021/PWA

SSP Name: SharedServices_MOPS

PSError: Success (0)

A general exception occurred during communication with Active Directory. Context: SyncGroup. Additional Information: An exception occurred while syncing a project server group with active directory. GroupName: ‘%s’. PWA Group Guid: ‘%s’ Exception: ‘%s’.. Exception Info: EPIS Team Members

 

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

 

 

 

 

There also are errors in system category:

 

 

 

 

Error Message:

 

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID

{61738644-F196-11D0-9953-00C04FD919C1}

 to the user IHESS\SRV_PROJSVR_DB SID (S-1-5-21-789336058-2052111302-839522115-106752).  This security permission can be modified using the Component Services administrative tool.

 

 

 

From the error message, I decided to change the DCOM configuration first.  By searching the registry, I found that (S-1-5-21-789336058-2052111302-839522115-106752) is the ID of IIS WAMREG Admin Service component.

 

Open the Component Services on project server and select “Component Services” – “DCOM Config”.

 

 

Right click on component and select “Property”.  Then select “Security”.

 

 

Select “Edit” button in “Launch and Activation Permissions”, then add the SharePoint farm service user in and give it “Local Launch” and “Local Activation” permissions.

 

 

 

After done, I tried to synchronize the users again.  However, I still got errors.  When I check the event log, the errors are different.

 

 

 

 

Error Message:

 

Standard Information:PSI Entry Point:

Project User: IHESS\sdai

Correlation Id: 837bfd5f-9502-4e09-a193-2af20c8392b0

PWA Site URL: http://hacssia021/PWA

SSP Name: SharedServices_MOPS

PSError: Success (0)

Changes were detected for a resource during Project Server Active Directory Synchronization. However, the changes could not be applied because the resource could not be checked out. The checkout failed because the resource is checked out to another user. Resource GUID: 67581218-21cd-4f5e-9ee0-6ad4f0e25799

 

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

 

 

 

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

 

It shows some resource has been checked out.  But in force-in resource page, I did not find any check-out resources.  Actually, those resources are checked-out by AD synchronization process. 

 

I used “SQL Server Management Studio” to open the Project Server 2007 published database. 

 

Run the script to check back in the resources

 

UPDATE MSP_RESOURCES
SET RES_CHECKOUTBY = NULL
WHERE RES_UID in (
SELECT
RES_UID
FROM MSP_Resources
WHERE RES_CHECKOUTBY = ‘00000000-0000-0000-0000-000000000000’)

 

 

If you have corrupted assignments, you may use the script to find out:

 

SELECT MSP_PROJECTS.PROJ_NAME,
MSP_ASSIGNMENTS_SAVED.TASK_NAME,
MSP_RESOURCES.RES_NAME,
MSP_ASSIGNMENTS_SAVED.ASSN_UID,
MSP_ASSIGNMENTS_SAVED.PROJ_UID,
MSP_ASSIGNMENTS_SAVED.TASK_UID,
MSP_ASSIGNMENTS_SAVED.RES_UID
FROM MSP_ASSIGNMENTS_SAVED INNER JOIN
MSP_PROJECTS ON MSP_ASSIGNMENTS_SAVED.PROJ_UID =
MSP_PROJECTS.PROJ_UID INNER JOIN
MSP_RESOURCES ON MSP_ASSIGNMENTS_SAVED.RES_UID =
MSP_RESOURCES.RES_UID
WHERE (NOT EXISTS
(SELECT TASK_UID
FROM MSP_TASKS_SAVED
WHERE (TASK_UID = MSP_ASSIGNMENTS_SAVED.TASK_UID)))

 

 

 

Reference:

 

http://connect.microsoft.com/feedback/ViewFeedback.aspx?FeedbackID=288068&SiteID=235

 

PDS with multiple hosted sites – Error 405 method not allowed

Problem

 

I have a problem with PDS and an additional instance of Project Web Access (created with EditSite). I followed the description in pj11PDSref.chm and made a new copy of pds.wsdl and pds.wsml with the proper URL.

I am able to logon, but when i try to execute "ProjectsStatus" I get the error "… 405 method not allowed". The same application works fine with the "Main" Project Web Access.

 

Solution

 

1. Ensure the MS SOAP Toolkit 3.0 is installed on your Project Server
2. Navigate into IIS
3. Right click on the original /projectserver virtual directory created during installation.
4. Click on Properties for the virtual directory
5. Click on the Configuration…button
6. Scroll all the way to the bottom and take note of the path of the "wsdl" extension
    This should be something like "c:\Program Files\Common Files\MSSOAP\BINARIES\SOAPIS30.dll"
7. Click cancel twice to return to the main IIS screen

Now configure the new virtual directories:
1. Right click on the new virtual directory created using EditSite
2. Click on Properties for the new virtual directory
4. Click on the Configuration… button
5. Click Add…
6. Enter the path from step #6 above (with the quotes)
7. Enter GET, POST in the Limit To box
8. Enter wsdl in the Extension box
9. Click OK Twice.

10. Reset IIS.

This should fix your problem.

Customize Issue on Homepage of PWA

The Issues list of WSS workspace has three status, Active Postpone and Closed.  Clients may want to replace it with their values.  The problem of custom status field is that PWA homepage would search WSS for "Active" issues(Risks).  If there is no "Active" status for issue(risks), then the homepage can not be any issue.  If we remove "Active.aspx" file from workspace template, the link of active issue will cause 404 error – page not found.

 

To custom status field and show proper issues in homepage, you have to do:

1. Change the status field as you want.

2. Open ".\Home\IssuesHome.asp" file and find IssueHome_Write function.

3. Find "nActive = GetIssuesCount(rsMyProjects, oStringsIssues.GetString(IDS_ISSUES_STATUS_ACTIVE), true);", the value "oStringsIssues.GetString(IDS_ISSUES_STATUS_ACTIVE)" is the specific status PWA will search on.  You may replace it with any status string you want to search on.

3. Find "var sLink = ‘<A ID="idIssueProj’ + nProjID + ‘" CLASS="link2" HREF="../Issues/IssueShell.asp?ProjID=’ + nProjID + ‘&Frame=Active.aspx" TITLE="’ + oStringsHome.GetString(IDS_HOME_ISSUES_ACTIVE_ALT) + ‘" ACCESSKEY="’ + oStringsHome.GetString(IDS_HOME_ISSUES_ACTIVE_KEY) + ‘">’;". The value "Frame=Active.aspx" is the view page that shows the issues.  You may replace it with any view page you want.

Customize WSS Role/User permission for Project Server 2003

By default, project server will create four roles in WSS workspace and put users in corresponding roles.  This is default setting:

 

Role Name	Project Server User	Permissions
Project Managers (Microsoft Office Project Server)	Project Managers who have published the project, or who have Save Project permission on the project	All permissions except Manage List Permission, Manage Site Groups, and Manage Web Site
Readers (Microsoft Office Project Server)	Who have View Issue permission on the project	View Items and View Pages permissions.
Team Members (Microsoft Office Project Server)	Who have assignments in the project or who are the team lead of the assignments in the project	Add Items, Edit Items, Delete Items, View Items, Browse Categories, View Pages, Manage Personal Views, Add/Remove Private  WebPart, and Update Private WebPart
Web Administrators (Microsoft Office Project Server)	Who have Manage Sharepoint global permission	All Permissions

 

However, clients may have different requirement.  For example, let project manager be the web administrator of its own project workspace.  Select/deselect one of the default permission of specific role.  Project Server cannot customize the roles and users.  If we change the role configuration in WSS, then after synchonization, the setting will be restored to default settings.

 

The customization can be done by modifing the project server ASP file.  The permission of each role is defined in DOCLIB\STSADUTL.ASP, and the function name is StsAdminUtil_DoCustomSOAPToWSSOM.  The mask defines the permission matrix.  You may find the permission matrix in WSS SDK or as follows:

Name	Value	Description	Site Groups
AddAndCustomizePages	0x00040000	Add, change, or delete ASPX pages, HTML pages, or Web Part Pages, and edit the Web site using a Windows SharePoint Services-compatible editor.	Web Designer, Administrator
AddDelPrivateWebParts	0x10000000	Add or remove Web Parts on a personalized Web Part Page.	Contributor, Web Designer, Administrator
AddListItems	0x00000002	Add items to lists, add documents to document libraries, and add Web discussion comments.	Contributor, Web Designer, Administrator
ApplyStyleSheets	0x00100000	Apply a style sheet (.CSS file) to the Web site.	Web Designer, Administrator
ApplyThemeAndBorder	0x00080000	Apply a theme or borders to the entire Web site.	Web Designer, Administrator
BrowseDirectories	0x04000000	Browse directories in a Web site.	Contributor, Web Designer, Administrator
BrowseUserInfo	0x08000000	View information about users. This right is not available through the user interface.	Guest, Reader, Contributor, Web Designer, Administrator
CancelCheckout	0x00000100	Check in a document without saving the current changes.	Web Designer, Administrator
CreatePersonalGroups	0x01000000	Create, change, and delete site groups, including adding users to the site groups and specifying which rights are assigned to a site group.	Contributor, Web Designer, Administrator
CreateSSCSite	0x00400000	Create a Web site using Self-Service Site Creation.	Reader, Contributor, Web Designer, Administrator
DeleteListItems	0x00000008	Delete items from a list, documents from a document library, and Web discussion comments in documents.	Contributor, Web Designer, Administrator
EditListItems	0x00000004	Edit items in lists, edit documents in document libraries, edit Web discussion comments in documents, and customize Web Part Pages in document libraries.	Contributor, Web Designer, Administrator
EmptyMask	0x00000000	Has no permissions on the Web site. Not available through the user interface.	N/A
FullMask	-1	Has all permissions on the Web site. Not available through the user interface.	N/A
ManageListPermissions	0x00000400	Grant, deny, or change user permissions to a list.	Administrator
ManageLists	0x00000800	Approve content in lists, add or remove columns in a list, and add or remove public views of a list.	Web Designer, Administrator
ManagePersonalViews	0x00000200	Create, change, and delete personal views of lists.	Contributor, Web Designer, Administrator
ManageRoles	0x02000000	Create, change, and delete site groups, including adding users to the site groups and specifying which rights are assigned to a site group.	Administrator
ManageSubwebs	0x00800000	Manage or create subsites.	Administrator
ManageWeb	0x40000000	Manage a site, including the ability to perform all administration tasks for the site and manage contents and permissions	Administrator
OpenWeb	0x00010000	Open the SharePoint Web site and get metadata related to the site, as well as see the underlying navigation structure (not exposed in the user interface).	Guest, Reader, Contributor, Web Designer, Administrator
UpdatePersonalWebParts	0x20000000	Update Web Parts to display personalized information.	Contributor, Web Designer, Administrator
ViewListItems	0x00000001	View items in lists, documents in document libraries, view Web discussion comments, and set up e-mail alerts for lists.	Reader, Contributor, Web Designer, Administrator
ViewPages	0x00020000	View pages in a Web site.	Reader, Contributor, Web Designer, Administrator
ViewUsageData	0x00200000	View reports on Web site usage.	Administrator

 

To change the permission or add more roles, you can modify the mask or the whole function.

Also in STSADUTL.ASP file, StsAdminUtil_RoleUsers function defines who should be put in each role.  You may change the function to change the user role.

Generate .NET Code by using Olymars

Olymars, full name as SQL Server Centric .NET Code Generator, is a beta software by Microsoft, which can generate .NET code based on SQL Server database.  By default, it has five build-in templates.  However, developers can easily extend it to fit your needs.  Here is a example to extend the templates, which can generate data access layer for application by using Microsoft Application Blocks.

 

To extend the templates:

1. Add a new groups in repository

2. Add a reference to System.dll in repository

3. Add a dynamic variable as CompanyName

4. Add reference of System.dll, System.Window.Forms.dll to your new group

5. Add dynamic variables as CompanyName and DeveloperAlias to your new group

6. Add three elements to your new group.

7. Edit the templates.

 

[Code of Templates]

<*
// ----------------------------------------------------------------------------------------------------
// GetName function for element: 'Data Access Layer Interface'
// ----------------------------------------------------------------------------------------------------
public string IDataAccessLayer_GetName() {

	// Choose whatever name you wish for this object
	return (@"IDataAccessLayer.cs");

}

// ----------------------------------------------------------------------------------------------------
// Creation method for element: 'Data Access Layer Interface'
// ----------------------------------------------------------------------------------------------------
public void IDataAccessLayer() {

	try {

		Source.CurrentTemplate.ExecutionStatusMessage = "Code Generation succeed. ";
		Response.WriteLine("//  File Name: " + IDataAccessLayer_GetName());
		Response.WriteLine("//  Code Generated By: " + DynamicParameters.DeveloperAlias);
		Response.WriteLine("//  Code Generated On: " + System.DateTime.Now.ToShortDateString());
		Response.WriteLine();
		Response.WriteLine("using System;");
		Response.WriteLine("using System.Data;");
		Response.WriteLine();
		Response.WriteLine("namespace " + DynamicParameters.CompanyName + ".Data");
		Response.WriteLine("{");
		Response.WriteLine("\tpublic interface IDataAccessLayer");
		Response.WriteLine("\t{");
		
		Response.WriteLine("\t\tstring ConnectionString {get;}");
		Response.WriteLine("\t\tbool WithTransaction {get;}");
		StoredProcedureCollection Procedures = Source.SelectedStoredProcedures;
		foreach (StoredProcedure SP in Procedures)
		{
			string ReturnType;
			if (SP.ReturnsData == true)
			{
				if (SP.Resultsets.Count > 1)
					ReturnType = "DataSet";
				else
					ReturnType = "DataTable";
			}
			else
			{
				ReturnType = "void";
			}

			ParameterCollection Parameters = SP.Parameters;
			string ParameterString = "";
			foreach (Parameter P in Parameters)
			{
				string Direction = "";
				if (P.Direction == ParameterDirection.Output)
				{
					Direction = "out ";
				}
				else if (P.Direction == ParameterDirection.ReturnValue)
				{
					continue;
				}
				if (ParameterString != "")
				{
					ParameterString += ",";
				}
				string DotNetName = P.Name;

				if (DotNetName.Substring(0,1) == "@")
				{
					DotNetName = DotNetName.Remove(0,1);
				}

				ParameterString += Direction + P.DotNetType.CommonTypeSystemEquivalent + " " + DotNetName;
			}

			Response.WriteLine("\t\t" + ReturnType + " " + SP.Name.Replace(" ", "_") + "(" + ParameterString + ");");
		}

		Response.WriteLine("\t}");
		Response.WriteLine("}");
	}
	catch (System.Exception GlobalException) {

		Source.CurrentTemplate.ExecutionStatusCode = GenerationStatus.Error;
		Source.CurrentTemplate.ExecutionStatusMessage = GlobalException.Message;

		Response.WriteLine();
		Response.WriteLine("*************************");
		Response.WriteLine(" AN EXCEPTION WAS THROWN");
		Response.WriteLine("*************************");
		Response.WriteLine(GlobalException.Message);
		Response.WriteLine();
		Response.WriteLine("****************");
		Response.WriteLine(" FULL EXCEPTION");
		Response.WriteLine("****************");
		Response.WriteLine(GlobalException.ToString());
		if (Source.DesignMode) {
			System.Windows.Forms.MessageBox.Show(GlobalException.ToString(), "An error has occured", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
		}
		else {
			throw;
		}
	}
}

// ----------------------------------------------------------------------------------------------------
// GetName function for element: 'Data Access Layer Basic Implementation'
// ----------------------------------------------------------------------------------------------------
public string DataAccessLayer_GetName() {

	// Choose whatever name you wish for this object
	return (@"DataAccessLayer.cs");

}

// ----------------------------------------------------------------------------------------------------
// Creation method for element: 'Data Access Layer Basic Implementation'
// ----------------------------------------------------------------------------------------------------
public void DataAccessLayer() {

	try {

		Source.CurrentTemplate.ExecutionStatusMessage = "Code Generation succeed. ";
		Response.WriteLine("//  File Name: " + DataAccessLayer_GetName());
		Response.WriteLine("//  Code Generated By: " + DynamicParameters.DeveloperAlias);
		Response.WriteLine("//  Code Generated On: " + System.DateTime.Now.ToShortDateString());
		Response.WriteLine();
		Response.WriteLine("using System;");
		Response.WriteLine("using System.Data;");
		Response.WriteLine("using System.Data.SqlClient;");
		Response.WriteLine("using Microsoft.ApplicationBlocks.Data;");
		Response.WriteLine("using " + DynamicParameters.CompanyName + ".Configure;");
		Response.WriteLine();
		Response.WriteLine("namespace " + DynamicParameters.CompanyName + ".Data");
		Response.WriteLine("{");
		Response.WriteLine("\tpublic class DataAccessLayer : IDataAccessLayer");
		Response.WriteLine("\t{");
		Response.WriteLine("\t\t// connection string of SQL database");
		Response.WriteLine("\t\tprivate string _ConnectionString = \"\";");
		Response.WriteLine("\t\t// transaction option");
		Response.WriteLine("\t\tprivate bool _WithTransaction = false;");
		Response.WriteLine("\t\t// connection");
		Response.WriteLine("\t\tprivate SqlConnection _Connection = null;");
		Response.WriteLine("\t\t// transaction");
		Response.WriteLine("\t\tprivate SqlTransaction _Transaction = null;");
		Response.WriteLine();
		Response.WriteLine("\t\t// get connection string");
		Response.WriteLine("\t\tpublic string ConnectionString");
		Response.WriteLine("\t\t{");
		Response.WriteLine("\t\t\tget {return _ConnectionString;}");
		Response.WriteLine("\t\t}");
		Response.WriteLine();
		Response.WriteLine("\t\t// get or set transaction option");
		Response.WriteLine("\t\tpublic bool WithTransaction");
		Response.WriteLine("\t\t{");
		Response.WriteLine("\t\t\tget {return _WithTransaction;}");
		Response.WriteLine("\t\t}");
		Response.WriteLine();

		Response.WriteLine("\t\t// construction with connection string");
		Response.WriteLine("\t\tpublic DataAccessLayer(string Connection)");
		Response.WriteLine("\t\t{");
		Response.WriteLine("\t\t\t_ConnectionString = Connection;");
		Response.WriteLine("\t\t}");
		Response.WriteLine();
		Response.WriteLine("\t\t// construction with configure");
		Response.WriteLine("\t\tpublic DataAccessLayer(IConfigure Config)");
		Response.WriteLine("\t\t{");
		Response.WriteLine("\t\t\t_ConnectionString = Config.GetConnectionString();");
		Response.WriteLine("\t\t}");
		Response.WriteLine();
		Response.WriteLine("\t\t// construction with connection, caller will manage the connection");
		Response.WriteLine("\t\tpublic DataAccessLayer(SqlConnection Connection)");
		Response.WriteLine("\t\t{");
		Response.WriteLine("\t\t\tif (Connection == null)");
		Response.WriteLine("\t\t\t\tthrow new Exception(\"Connection cannot be null.\");");
		Response.WriteLine("\t\t\t_Connection = Connection;");
		Response.WriteLine("\t\t}");
		Response.WriteLine();
		Response.WriteLine("\t\t// construction with transaction, caller will manage the connection and transaction");
		Response.WriteLine("\t\tpublic DataAccessLayer(SqlTransaction Transaction)");
		Response.WriteLine("\t\t{");
		Response.WriteLine("\t\t\tif (Transaction == null)");
		Response.WriteLine("\t\t\t\tthrow new Exception(\"Transaction cannot be null.\");");
		Response.WriteLine("\t\t\t_Connection = Transaction.Connection;");
		Response.WriteLine("\t\t\t_ConnectionString = _Connection.ConnectionString;");
		Response.WriteLine("\t\t\t_Transaction = Transaction;");
		Response.WriteLine("\t\t\t_WithTransaction = true;");
		Response.WriteLine("\t\t}");
		Response.WriteLine();

		StoredProcedureCollection Procedures = Source.SelectedStoredProcedures;
		foreach (StoredProcedure SP in Procedures)
		{
			string ReturnType;
			if (SP.ReturnsData == true)
			{
				if (SP.Resultsets.Count > 1)
					ReturnType = "DataSet";
				else
					ReturnType = "DataTable";
			}
			else
			{
				ReturnType = "void";
			}

			ParameterCollection Parameters = SP.Parameters;
			string ParameterString = "";
			string SqlDBParameterString = "";
			int TrueParameter = 0;
			foreach (Parameter P in Parameters)
			{
				string Direction = "";
				if (P.Direction == ParameterDirection.Output)
				{
					Direction = "out ";
				}
				else if (P.Direction == ParameterDirection.ReturnValue)
				{
					continue;
				}
				if (ParameterString != "")
				{
					ParameterString += ",";
					SqlDBParameterString += ",";
				}
				string DotNetName = P.Name;

				if (DotNetName.Substring(0,1) == "@")
				{
					DotNetName = DotNetName.Remove(0,1);
				}

				ParameterString += Direction + P.DotNetType.CommonTypeSystemEquivalent + " " + DotNetName;
				SqlDBParameterString += P.Name + " " + P.SqlFullType;
				TrueParameter ++;
			}
			Response.WriteLine("\t\t// Execute stored procedure:");
			Response.WriteLine("\t\t//     " + SP.Name + "(" + SqlDBParameterString + ")");
			Response.WriteLine("\t\tpublic " + ReturnType + " " + SP.Name.Replace(" ", "_") + "(" + ParameterString + ")");
			Response.WriteLine("\t\t{");
			if (SP.ReturnsData == true)
				Response.WriteLine("\t\t\tDataSet ds = null;");
			Response.WriteLine();
			Response.WriteLine("\t\t\ttry");
			Response.WriteLine("\t\t\t{");
			if (TrueParameter > 0)
			{
				Response.WriteLine("\t\t\t\tSqlParameter[] Params = new SqlParameter[" + TrueParameter + "];");
				Response.WriteLine();
				int i = 0;
				foreach (Parameter P in Parameters)
				{
					if (P.Direction == ParameterDirection.ReturnValue)
					{
						continue;
					}
					string DotNetName = P.Name;

					if (DotNetName.Substring(0,1) == "@")
					{
						DotNetName = DotNetName.Remove(0,1);
					}

					Response.WriteLine("\t\t\t\tParams[" + i + "] = new SqlParameter(\"" + P.Name+ "\",  "+ P.DotNetType.FullSqlDbTypeEquivalent + ", " + P.Length+ ");");
					Response.WriteLine("\t\t\t\tParams[" + i + "].Value = " + DotNetName +";");
					if (P.Direction == ParameterDirection.Output)
						Response.WriteLine("\t\t\t\tParams[" + i + "].Direction = ParameterDirection.Output;");
					Response.WriteLine();
					i++;
				}
			}
			string MethodName = "";
			string ReturnDataSet = "";
			if (SP.ReturnsData == true)
			{
				MethodName = "ExecuteDataset";
				ReturnDataSet = "ds = ";
			}
			else
			{
				MethodName = "ExecuteNoQuery";
			}
			string ParamString = "";
			if (TrueParameter > 0)
			{
				ParamString = ", Params";
			}
			Response.WriteLine("\t\t\t\tif (_Transaction != null)");
			Response.WriteLine("\t\t\t\t{");
			Response.WriteLine("\t\t\t\t\t" + ReturnDataSet+ "SqlHelper." + MethodName + "(_Transaction, CommandType.StoredProcedure, \"" + SP.Name + "\"" + ParamString + ");");
			Response.WriteLine("\t\t\t\t}");
			Response.WriteLine("\t\t\t\telse if (_Connection != null)");
			Response.WriteLine("\t\t\t\t{");
			Response.WriteLine("\t\t\t\t\t" + ReturnDataSet+ "SqlHelper." + MethodName + "(_Connection, CommandType.StoredProcedure, \"" + SP.Name + "\"" + ParamString + ");");
			Response.WriteLine("\t\t\t\t}");
			Response.WriteLine("\t\t\t\telse");
			Response.WriteLine("\t\t\t\t{");
			Response.WriteLine("\t\t\t\t\t" + ReturnDataSet+ "SqlHelper." + MethodName + "(_ConnectionString, CommandType.StoredProcedure, \"" + SP.Name + "\"" + ParamString + ");");
			Response.WriteLine("\t\t\t\t}");
			if (SP.ReturnsData == true)
			{
				if (SP.Resultsets.Count > 1)
					Response.WriteLine("\t\t\t\treturn ds;");
				else
					Response.WriteLine("\t\t\t\treturn ds.Tables[0];");
			}
			else
				Response.WriteLine("\t\t\t\treturn;");
			Response.WriteLine("\t\t\t}");
			Response.WriteLine("\t\t\tcatch(Exception ex)");
			Response.WriteLine("\t\t\t{");
			Response.WriteLine("\t\t\t\tthrow new Exception(\"Cannot execute the stored procedure.\", ex);");
			Response.WriteLine("\t\t\t}");
			Response.WriteLine("\t\t}");
			Response.WriteLine();
		}

		Response.WriteLine("\t}");
		Response.WriteLine("}");

	}
	catch (System.Exception GlobalException) {

		Source.CurrentTemplate.ExecutionStatusCode = GenerationStatus.Error;
		Source.CurrentTemplate.ExecutionStatusMessage = GlobalException.Message;

		Response.WriteLine();
		Response.WriteLine("*************************");
		Response.WriteLine(" AN EXCEPTION WAS THROWN");
		Response.WriteLine("*************************");
		Response.WriteLine(GlobalException.Message);
		Response.WriteLine();
		Response.WriteLine("****************");
		Response.WriteLine(" FULL EXCEPTION");
		Response.WriteLine("****************");
		Response.WriteLine(GlobalException.ToString());
		if (Source.DesignMode) {
			System.Windows.Forms.MessageBox.Show(GlobalException.ToString(), "An error has occured", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
		}
		else {
			throw;
		}
	}
}

// ----------------------------------------------------------------------------------------------------
// GetName function for element: 'Configuration Interface'
// ----------------------------------------------------------------------------------------------------
public string IConfigure_GetName() {

	// Choose whatever name you wish for this object
	return (@"IConfigure.cs");

}

// ----------------------------------------------------------------------------------------------------
// Creation method for element: 'Configuration Interface'
// ----------------------------------------------------------------------------------------------------
public void IConfigure() {

	try {

		Source.CurrentTemplate.ExecutionStatusMessage = "Code Generated Succeed.";
		Response.WriteLine("//  File Name: " + IConfigure_GetName());
		Response.WriteLine("//  Code Generated By: " + DynamicParameters.DeveloperAlias);
		Response.WriteLine("//  Code Generated On: " + System.DateTime.Now.ToShortDateString());
		Response.WriteLine();
		Response.WriteLine("using System;");
		Response.WriteLine();
		Response.WriteLine("namespace " + DynamicParameters.CompanyName + ".Configure");
		Response.WriteLine("{");
		Response.WriteLine("\tpublic interface IConfigure");
		Response.WriteLine("\t{");
		Response.WriteLine("\t\tstring GetConnectionString();");
		Response.WriteLine("\t}");
		Response.WriteLine("}");

	}
	catch (System.Exception GlobalException) {

		Source.CurrentTemplate.ExecutionStatusCode = GenerationStatus.Error;
		Source.CurrentTemplate.ExecutionStatusMessage = GlobalException.Message;

		Response.WriteLine();
		Response.WriteLine("*************************");
		Response.WriteLine(" AN EXCEPTION WAS THROWN");
		Response.WriteLine("*************************");
		Response.WriteLine(GlobalException.Message);
		Response.WriteLine();
		Response.WriteLine("****************");
		Response.WriteLine(" FULL EXCEPTION");
		Response.WriteLine("****************");
		Response.WriteLine(GlobalException.ToString());
		if (Source.DesignMode) {
			System.Windows.Forms.MessageBox.Show(GlobalException.ToString(), "An error has occured", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
		}
		else {
			throw;
		}
	}
}
*>

Close a project in EPM

EPM system does not have ability to manage projects in all lifecycle.  It can only manage a project when it is ongoing.  When a project is finished, it does not have an indicator, and most important, it cannot remove the project from project list.  As the result, project manager can still open the project, modify the project plan, and etc.
 
One way to avoid this is to remove the project from EPM system.  However, we may need the project data for statistic.  There is one way we can keep the project in EPM.  Portfolio manager can view the data in OLAP cube, but the project manager cannot modify it, and team member cannot put any hours against project.  To do so, we need:

 

1. Create a custom outline code in EPM global.  This will act as an indicator to show the project status.
2. Open every project in Project Professional set the value of the custom outline code and republishes it.
3. Create a security, Closed Projects, which disables projects in the list for opening and viewing.
4. Open the finished projects in Project Professional to change the resource from committed to purposed.
5. Put those finished project in the Closed Projects.

 

Then all finished projects will be removed from timesheet, project center, and project professional.  However, for each finished project, it requires administrator to add it in the security.  To improve it, there are several solutions:

 

1. Create a custom page in admin page of the PWA, which has a data grid to show all projects with closed status.  Then administrator can select from the list, then click button to close the project.
2. Create a SQL agent that execute periodically, which close all projects with closed status.
3. Create a SQL trigger that execute immediately when project manager saves the project.  If the project manager set the project status to be closed, then the trigger will close the project.

Extend the OLAP cube for week in Time dimension

By default, the OLAP cube of project server does not have week in time dimension.  However, in real world, week is important.  To add week into time dimension, we need to extend the standard OLAP cube building process.

 

In fact, the MSP_CUBE_TIME_BY_DAY table has already had week number in it.  We can use it to create a new level in time dimension.  However, to have more friendly interface, you may create additional column in MSP_CUBE_TIME_BY_DAY table as the display of the week level. 

 

To extend the week, you need to do the following:

1. For all physical cube, delete time dimension if used and remember all the cube name.

2. For all virtual cube, delete time dimension if used and remember all the cube name.

3. Delete the time dimension

4. Create a new time dimension and create six levels of it.

5. Add the time dimension into the physical cube of step 1.

6. Add the time dimension into the virtual cube of step 2.

 

There is one thing you may need to know.  When you remove or add a dimension from a physical cube, you need to reconstruct the join clause. 

 

Here is the source code in VB6 for SQLServer 2000 database.  If your database is SQLServer 2005, it may be a little different.

 

‘ Modify the time dimension to insert week of month.
Public Function ModifyTimeDimension(dsoDatabase As DSO.MDStore)
    Dim dsoDimension As DSO.Dimension
    Dim dsoLevelAll As DSO.DbLevel
    Dim dsoLevel As DSO.Level
    Dim strDimensionName As String
    Dim strDimensionTableName As String
    Dim dsoCube As DSO.MDStore
    Dim DicCubes As Dictionary
    Dim fSuccess As Boolean
    Dim jcJoin As JoinClauseManager
    Dim Condition As JoinClause
    Dim nPos  As Integer
   
    fSuccess = False
   
    On Error GoTo LblErrorHandler
   
    strDimensionName = "Time"
    strDimensionTableName = "MSP_CUBE_TIME_BY_DAY"
   
    Set DicCubes = New Dictionary
    Set jcJoin = New JoinClauseManager
   
    If dsoDatabase.Dimensions.Find(strDimensionName) Then
        For nPos = 1 To dsoDatabase.MDStores.Count
            Set dsoCube = dsoDatabase.MDStores(nPos)
            If dsoCube.Dimensions.Find(strDimensionName) And dsoCube.SubClassType = sbclsRegular Then
                Call DicCubes.Add(dsoCube.Name, dsoCube.Name)
                dsoCube.Dimensions.Remove (strDimensionName)
               
                Call jcJoin.LoadJoinClause(dsoCube.JoinClause)
                Call jcJoin.RemoveJoinCondition(TrimQuate(dsoCube.SourceTable), "TIME_ID", strDimensionTableName, "TIME_ID")
                Call jcJoin.RemoveJoinCondition(TrimQuate(dsoCube.SourceTable), "TIME_DATE", strDimensionTableName, "TIME_DATE")
                dsoCube.JoinClause = jcJoin.GetJoinClause
                dsoCube.Update
                dsoCube.Process processFull
           End If
        Next nPos
       
        For nPos = 1 To dsoDatabase.MDStores.Count
            Set dsoCube = dsoDatabase.MDStores(nPos)
            If dsoCube.Dimensions.Find(strDimensionName) And dsoCube.SubClassType = sbclsVirtual Then
                Call DicCubes.Add(dsoCube.Name, dsoCube.Name)
                dsoCube.Dimensions.Remove (strDimensionName)
               
                dsoCube.Update
                dsoCube.Process processFull
           End If
        Next nPos
        dsoDatabase.Dimensions.Remove (strDimensionName)
    End If
   
    Set dsoDimension = dsoDatabase.Dimensions.AddNew(strDimensionName, sbclsRegular)
    
    Set dsoDimension.DataSource = dsoDatabase.DataSources(1)
    dsoDimension.FromClause = """" & strDimensionTableName & """"
   
    Set dsoLevelAll = dsoDimension.Parent.Parent.CreateObject(clsDatabaseLevel)
       
    dsoLevelAll.Name = "All"
    dsoLevelAll.IsAll = True
   
    If dsoDimension.Levels.Count > 0 Then
        Call dsoDimension.Levels.Add(dsoLevelAll, , 1)
    Else
        Call dsoDimension.Levels.Add(dsoLevelAll)
    End If
     
    dsoLevelAll.MemberKeyColumn = "All Years"
    dsoLevelAll.LevelType = levAll
   
    Set dsoLevel = dsoDimension.Levels.AddNew("Year", sbclsRegular)
    dsoLevel.MemberKeyColumn = """" & strDimensionTableName & """.""" & "TIME_YEAR"""
    dsoLevel.MemberNameColumn = """" & strDimensionTableName & """.""" & "TIME_YEAR"""
    dsoLevel.LevelType = levTimeYears
    dsoLevel.columnType = adSmallInt

   
    Set dsoLevel = dsoDimension.Levels.AddNew("Quarter", sbclsRegular)
    dsoLevel.MemberKeyColumn = """" & strDimensionTableName & """.""" & "TIME_QTR"""
    dsoLevel.MemberNameColumn = """" & strDimensionTableName & """.""" & "TIME_QTR"""
    dsoLevel.LevelType = levTimeQuarters
    dsoLevel.columnType = adWChar
   
    Set dsoLevel = dsoDimension.Levels.AddNew("Month", sbclsRegular)
    dsoLevel.MemberKeyColumn = """" & strDimensionTableName & """.""" & "TIME_MONTH_OF_YEAR"""
    dsoLevel.MemberNameColumn = """" & strDimensionTableName & """.""" & "TIME_MONTH"""
    dsoLevel.LevelType = levTimeMonths
    dsoLevel.columnType = adSmallInt
    dsoLevel.Ordering = orderKey

   
    Set dsoLevel = dsoDimension.Levels.AddNew("Week", sbclsRegular)
    dsoLevel.MemberKeyColumn = """" & strDimensionTableName & """.""" & "TIME_WEEK_OF_YEAR"""
    dsoLevel.MemberNameColumn = """" & strDimensionTableName & """.""" & "TIME_WEEK"""
    dsoLevel.LevelType = levTimeWeeks
    dsoLevel.columnType = adSmallInt
    dsoLevel.Ordering = orderKey
   
    Set dsoLevel = dsoDimension.Levels.AddNew("Days", sbclsRegular)
    dsoLevel.MemberKeyColumn = """" & strDimensionTableName & """.""" & "TIME_DAY_OF_WEEK"""
    dsoLevel.MemberNameColumn = """" & strDimensionTableName & """.""" & "TIME_DAY_OF_MONTH"""
    dsoLevel.LevelType = levTimeDays
    dsoLevel.columnType = adSmallInt
    dsoLevel.Ordering = orderKey
   
    ‘Update and process the dimension.
    dsoDimension.Update
    dsoDimension.Process (processFull)
   
    For Each dsoCube In dsoDatabase.MDStores
        If DicCubes.Exists(dsoCube.Name) And dsoCube.SubClassType = sbclsRegular Then
            dsoCube.Dimensions.AddNew (strDimensionName)
            Call jcJoin.LoadJoinClause(dsoCube.JoinClause)
            Call jcJoin.AddJoinCondition(TrimQuate(dsoCube.SourceTable), "TIME_ID", strDimensionTableName, "TIME_ID")
            Call jcJoin.AddJoinCondition(TrimQuate(dsoCube.SourceTable), "TIME_DATE", strDimensionTableName, "TIME_DATE")
            dsoCube.JoinClause = jcJoin.GetJoinClause
            dsoCube.Update
            dsoCube.Process (processFull)
        End If
    Next dsoCube
   
    For Each dsoCube In dsoDatabase.MDStores
        If DicCubes.Exists(dsoCube.Name) And dsoCube.SubClassType = sbclsVirtual Then
            dsoCube.Dimensions.AddNew (strDimensionName)
            dsoCube.Update
            dsoCube.Process (processFull)
        End If
    Next dsoCube
   
    fSuccess = True
   
LblErrorHandler:
  
   Set dsoDimension = Nothing
   Set dsoLevelAll = Nothing
   Set dsoLevel = Nothing
   Set dsoCube = Nothing
  
   If (Not fSuccess) Then
      Call TraceAndRaise("Error in ModifyTimeDimension.")
   End If
   
End Function

Synchronize All

PWA provides a function to synchronize the users between project server and SharePoint server, however, it only allow the administrator to synchronize one project at a time.  If administrator wants to synchronize multiply projects or all projects, it would be time consuming.
 
Fortunately, PWA is an extendable framework, that we can extend PWA to support Synchronize multiple projects at a time.  To extend the PWA for synchronizing all projects, we need to do:

1. Modify the ManageSTS.ASP page
    a. Add a button in the toolbar.
    b. Add a form with a hidden field in the page.
    c. Add an event handler for the button.
2. Copy the SyncUpUsers.ASP page as SyncUpAllUsers.ASP, and modify it to send a request for all projects.  The original page can send one request, we only need to add a loop in the code so it can send multiply requests.

Extend OLAP cube for Task – create virtual cube

Microsoft provides a document and source code for extending OLAP cube for task data, however, they did not mention two things which are important for viewing a cube.  One is to add all role in database level to cube level, Victor has already discussed it in his article.  Another thing is to create virtual cube with project cube and resource cube.  I will discuss it here.

 

Custom task cube only contains the task information, not associated with projects and resources.  Normally people like to view data with project information, even resource.  We need to create virtual cube for it. 

 

Virtual cube, like view in database, is a logical structure that itself contains no data.  In this extension, we will create two virtual cube, MSP_TASK_PORTFOLIO, which is based on task cube and project cube.  Other is MSP_PORTFOLIO, which is based on all three physical cubes.  The following is the sample code to create virtual cube:

 

Private Sub createPortfolioVirtualCube(dsoDatabase As DSO.MDStore)
    Dim dsoVirtualCube As DSO.MDStore
    Dim dsoAssnCube As DSO.MDStore
    Dim dsoTaskCube As DSO.MDStore
    Dim dsoResCube As DSO.MDStore
    Dim dsoDim As DSO.Dimension
    Dim dsoMea As DSO.Measure
    Dim dsoNewMea As DSO.Measure
   
    Set dsoVirtualCube = dsoDatabase.MDStores.AddNew(VIRTUAL_PORTFOLIO, sbclsVirtual)
    Set dsoAssnCube = dsoDatabase.MDStores(ASSN_CUBE)
    Set dsoTaskCube = dsoDatabase.MDStores(TASK_CUBE)
    Set dsoResCube = dsoDatabase.MDStores(RES_CUBE)
    For Each dsoDim In dsoTaskCube.Dimensions
        dsoVirtualCube.Dimensions.AddNew (dsoDim.Name)
    Next
    For Each dsoDim In dsoAssnCube.Dimensions
        If dsoVirtualCube.Dimensions(dsoDim.Name) Is Nothing Then
            dsoVirtualCube.Dimensions.AddNew (dsoDim.Name)
        End If
    Next
    For Each dsoDim In dsoResCube.Dimensions
        If dsoVirtualCube.Dimensions(dsoDim.Name) Is Nothing Then
            dsoVirtualCube.Dimensions.AddNew (dsoDim.Name)
        End If
    Next
    For Each dsoMea In dsoTaskCube.Measures
        Set dsoNewMea = dsoVirtualCube.Measures.AddNew(dsoMea.Name)
        dsoNewMea.SourceColumn = "[" & TASK_CUBE & "].[" & dsoMea.Name & "]"
    Next
    dsoVirtualCube.Update
    dsoVirtualCube.Process

End Sub