by Martin Laukkanen | Apr 22, 2010 | How to, Project 2010, Reporting
As you’ll quickly find in Project Server 2010, if you want to use the default Business Intelligence Center data connections then in order to include your own custom fields, then you’ll need to update the connectors. Here’s a quick guide on doing that to get to that data for your first Project Status Report.
Before I get into it don’t forget that the OLAP connections are automatically created, so in many cases you can use those, but if you’re like me and prefer direct reporting from the Reporting Database then you’ll have to create your own connections.
Step 1: Start from an existing connection file
For this I am going to use "Project Server – Project and Task Data" from the default location "BC CenterData Connections – English (…)", find the existing file and save it somewhere locally using the Send to – Download a Copy option.
Now open that ODC file in Notepad and you’ll see the XML contents and hopefully the important part the section labelled <odc:CommandText> where the SQL "SELECT …" statement is, that’s what we need to change to include our custom fields.
Step 2: Add your Custom Field details using SQL Management Studio
Using SQL Management Studio is the easiest way to confirm that you have the right field names to add to the query, to do that find your ProjectServer_Reporting database and in particular what you will want is the View named dbo.MSP_EpmProject_UserView (or one of the others if you want task or resource fields). Select the view and using the ‘Select Top 1000 Rows’ option to generate a SELECT query returning all data.
You’ll end up with something like this:
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [ProjectUID]
,[ProjectName]
[ …. Snip lots of built in fields….]
,[ProjectBaseline10Duration]
,[Project Departments]
,[Sample Business Need]
,[Sample Areas Impacted]
,[Sample Proposal Cost]
,[Sample Compliance Proposal]
,[Sample Assumptions]
,[Sample Goals]
,[Sample Post Implementation Review Date]
,[Sample Post Implementation Review Notes]
,[Sample Proposed Start Date]
,[Sample Proposed Finish Date]
,[Sample Approved Start Date]
,[Sample Approved Finish Date]
FROM [PWA_ProjectServer_Reporting].[dbo].[MSP_EpmProject_UserView]
The nice thing is in the views all the custom fields are listed last, as you can see from above the out of the box ‘Sample’ fields are all there to see. So next you just have to copy those field names into the ODC file opened earlier and save your changes.
Step 3: Update the ODC file
The only catch with updating the ODC file as you probably noticed is the format includes the view or table name prefix, such as:
MSP_EpmProject_UserView.ProjectBaseline10FinishDate as [ProjectBaseline10FinishDate], MSP_EpmProject_UserView.ProjectBaseline10Duration as [ProjectBaseline10Duration],
So all you need to know is that unless your changing the name you don’t need the bit after ‘as’, so if you search and replace to prefix each of your custom fields to add the "MSP_EpmProject_UserView." before the "," so you end up with something like:
,MSP_EpmProject_UserView.[Sample Business Need] ,MSP_EpmProject_UserView.[Sample Areas Impacted] ,MSP_EpmProject_UserView.[Sample Proposal Cost]
Now the last step is to paste those lines into the ODC file in Notepad, but note that the commas are at the start not the end, so you just need to make sure that when you paste the new lines that you have just one comma between each line except for the last line before the "FROM". You should end up with something like this:
MSP_EpmTask_UserView.TaskBaseline10Duration as [TaskBaseline10Duration], MSP_EpmTask_UserView.TaskBaseline10DurationString as [TaskBaseline10DurationString], MSP_EpmTask_UserView.[Health] as [Health] ,MSP_EpmProject_UserView.[Sample Business Need] ,MSP_EpmProject_UserView.[Sample Areas Impacted] ,MSP_EpmProject_UserView.[Sample Proposal Cost] FROM dbo.MSP_EpmProject_UserView INNER JOIN dbo.MSP_EpmTask_UserView ON MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID ORDER BY MSP_EpmProject_UserView.ProjectName, MSP_EpmTask_UserView.TaskIndex, MSP_EpmTask_UserView.TaskName </odc:CommandText>
Step 4: Upload the new ODC file to your Data Connection library
Now your just about done, save the file and back in your BI Center upload the file to a new location, make sure that you don’t try to overwrite the default file as those may be replaced by future service packs, I upload them directly into the Data Connections library.
Once uploaded you can now use them in Excel as normal. You might want to update or copy the existing templates and edit the data connection properties to point to this new ODC file, or otherwise just add them in as you normally would and carry on..
by Martin Laukkanen | Apr 11, 2010 | Development, Project 2010, Troubleshooting, Workflow
I’ve been working on a full end-to-end demand management workflow over the past couple of weeks and unfortunately as RTM is due any day now, much of the final documentation is still not public, meaning that some things just need to be figured out.
One problem that I found is the custom workflow task form, which has a very useful "Review project details" link on it. However if you have used Microsoft’s demo2010a image, then you’d see that for all of the workflow’s the link initially does not work. (See image)
Fortunately this one was relatively easy to guess, thanks MS for making it quite obvious!
Firstly a custom workflow task content type called PSWApprovalTask is created by default in the PWA site "Project Server Workflow Tasks" list, here’s what it looks like out-of-the-box:
Interestingly though, it is not by default added to the list content types, BUT if you run through the Sample Proposal Workflow once then you will see it appear in the list automatically. For the purpose of this just use the ‘Add from existing site content types’ option in List Settings to add it:
Now another interesting thing is apparent on the list settings page:
Specifically Microsoft has pre-defined three Columns named: Project Name, Project Owner and ProjectUid (and some others..), however they are not added to the content type by default either, but they are used in the views including the default My Tasks.
To the point:
Now getting to the point of this post, if you use the content type and populate all of those fields then the task approval page will work as expected! Specifically the ProjectUID Column needs to be populated with guess what: the Project’s GUID, once that is done then the link works as expected!
Too easy!
Well unfortunately not. The Visual Studio Workflow Activities included with Project Server 2010 gives you the very handy ReadProjectProperty action, however while having a choice of either reading a Custom Field based on GUID or a pre-defined built in field based on name, it does not include Project GUID. This is where it gets complicated.
The “simple” solution is to use PSI, then using something like this nice little method created by Chris Boyd you can retrieve the GUID by providing the Project Name using PSI method ReadProjectStatus.
Unfortunately having to use PSI in what otherwise could be a very simple SharePoint workflow in my opinion is clunky, it would be very nice if MS could add the Proj_UID property to the ReadProjectProperty action!
If you’re interested, based on Chris’s blog post linked above, here is an extract of my code to get all of those fields working (Note that this plugs in nicely to my last code post: Extending the Branching Workflow):
// Declare your PSI Stuff
private static WebSvcProject.Project project = new WebSvcProject.Project();
private void createTask_InitApproval(object sender, EventArgs e)
{
[… insert your other required task attributes …]
taskProps.ExtendedProperties["Project Name"] = Proj_Name[0];
taskProps.ExtendedProperties["Project Owner"] = Proj_Owner_Name[0];
taskProps.ExtendedProperties["ProjectUid"] = GetProjectUidFromProjectName(Proj_Name[0]);
}
public static Guid GetProjectUidFromProjectName(string projectName)
{
Guid projectGUID;
project.Credentials = CredentialCache.DefaultCredentials;
WebSvcProject.ProjectDataSet readProjDs = new WebSvcProject.ProjectDataSet();
readProjDs = project.ReadProjectStatus(
Guid.Empty,
WebSvcProject.DataStoreEnum.WorkingStore,
projectName,
0
);
if (readProjDs.Project.Rows.Count == 1)
{
projectGUID = new Guid(readProjDs.Project[0].PROJ_UID.ToString());
}
else
{
throw new Exception("No Project by the name: " + projectName + " Found");
}
return projectGUID;
}
Enjoy!
Update 4/08/2010:Check the comments below for a much easier way to correct this issue, without the use of PSI.
by Martin Laukkanen | Apr 11, 2010 | Development, How to, Project 2010, Workflow
With Project Server 2010 you get an out of the box workflow named ‘Sample Proposal Workflow’, this is quite a good demonstration as it includes all of the usual things you might see in such a process; Validation, Approval, Selection, etc. However unfortunately as of this time (days before RTM) the source for this sample is still not available, at the moment the only SDK example is a simple Branching Workflow which only includes a single validation step.
I had an opportunity at the Sydney Ignite to ask Jan Kalis about this one, and he assured me that the full source for the sample will be released sometime around RTM, but we’ll have to wait a little bit more for that one.
In the meantime the world moves on and I have been busily writing my first full blow demand management workflow for a customer deployment, and so I thought I would share some of what I have done here. To that end I have taken the SDK Branching Workflow and extended it to include an Approval stage similar to that in the Sample Proposal Workflow.
Read below for what I have done and what you need to test it for yourself, then download my Visual Studio 2010 project below.
Branching Workflow with Approval:
The steps below are a summary of the changes made to the Branching Workflow, for a more complete how-to guide I very strongly suggest that you read the SDK Branching Workflow article and if you complete the examples there you will end where my steps below begin. Furthermore all the general requirements for getting this running (Visual Studio 2010 etc) are in the SDK article.
Before you start (or download):
This example is built on a default PWA instance, I have renamed the out of the box Sample Proposal Workflow to ‘Branching Workflow with Approvals’ then after deploying the code changed the workflow selected to the new one. All other PDPs and Stages are used as is, and the only other change required is an optional one below in step 5 below (see image).
Changes Made:
- Starting with the SDK Branching Workflow (note that if you download it in the p14betasdk then the code is incomplete and a few steps from the MSDN article must be completed). I also renamed the Feature and the Workflow in the deployment package to avoid confusion.
- Then I added a pre-build event command to automatically regenerate the Strong Name. (Can be removed, but just makes testing easier)
- Added a step after the Proposal Details Stage where the workflow goes to Proposal Selection Stage in Select Phase (updateProjectStageStatus20 – Waiting for Approval) – Note I have used the Select phase as my ‘Approval’ phase, I am not doing anything with Portfolio Selection here just approval.
- Next I read the Project Properties of "Project Name" and "Owner" for the Workflow Task (These are optional and just for completeness).
- Read "Portfolio Managers" Project Server Group membership to create approval request for first person returned in group.
- Create Workflow Task using CreateTaskWithContentType and assign to approver and populate task properties with Project Name and Owner.
Important: In order for these task properties to work you must ad the built in Columns to the PSWApprovalTask content type by editing the Default Workflow Approvals, then updating the content type to include the existing list columns.
(See Screenshot from List Settings PSWApprovalTask Content Type Properties)
- Then we add a while activity onTaskChanged to wait for the Approval task to be actioned. In this step the ExtendedProperties are checked for a value "Approved" anything else results in Rejection.
- And finally an IfElseActivity is used to branch on the Approved / Rejected result.
- If true then continue to end and UpdateProjectStageStatus to Execution stage.
- Else UpdateProjectStageStatus to Not Selected state and Terminate.
Here is the result in Visual Studio:
What’s left?
- Email notification? You can simply use the SharePoint list notifications on the Workflow Task list as a simple and consistent email notification method.
- Portfolio Selection? I’ll leave that one to you.
Technical Details:
This example was created on the following beta software, meaning that things might change by RTM:
- SharePoint Server 2010 RC Build
- Project Server 2010 RC Build
- Visual Studio 2010 RC Build
Download the full project files in a zip here. (*see update below)
MSDN References:
How to: Create a Branching Workflow <http://msdn.microsoft.com/en-us/library/ee767701(v=office.14).aspx>
Step 3: Create the Workflow <http://msdn.microsoft.com/en-us/library/ms580283.aspx>
UPDATE 3/09/2010:
I thought that I’d revisit this one as clearly from the comments there was something wrong after RTM, basically the problem everyone seems to be having relates to the ‘WorkflowTaskCTypeID’ defined. Somehow what I had in the BranchingWorkflow.cs file is actually incorrect, the solution has the following on line 46:
public String WorkflowTaskCTypeID = “0x0108010038A52C27344148C9B9214F82C7C0298500544602C73FFD1245BCC090442C85426B”;
When it should be:
public String WorkflowTaskCTypeID = “0x0108010038A52C27344148C9B9214F82C7C02985?;
Not quite sure where the first one came from (and why it worked?!) but that fixed it for me. If you update that line then recompile / rebuild you should be good.
PS. I don’t use the CreateTaskWithContentType anymore, have a look at the 2010 workflow activity (OfficeTask class) used in the Sample Proposal found in the SDK: http://msdn.microsoft.com/en-us/library/microsoft.office.workflow.actions.officetask.aspx
by Martin Laukkanen | Apr 1, 2010 | Project 2010
One thing I had been thus far unable to find out with 2010 and SQL AS is what’s changed with regards to the repository and DSO configuration, didn’t seem to be much specific mention in TechNet and I have seen a few posts indicating that the setup procedure is the same. (As in here.)
Well I just quickly tested and realised that it the whole configuration of the repository and DSO settings is no longer necessary! Hurrah! No more manually creating the repository database, and thank you MS for getting rid of the option to use an MDB repository!
Now that it’s 2010 we can finally get rid of some of those 90’s technologies..
by Martin Laukkanen | Apr 1, 2010 | Project 2010
Been reviewing the MS documentation on 2010 in preparation for my first production 2010 deployment, and found this little titbit in this TechNet Article under ‘Enable the common language runtime’.
Use the following SQL script to improve Project Server performance, quote: “Enabling the common language runtime provides a significant improvement in performance for custom field operations.”
sp_configure ‘clr enabled’, 1
go
reconfigure
go
Simple enough, just open SQL Management Studio, connect to your SQL server and select New Query then paste and run that.
