This is a useful solution; with it you can update on a PDP an Excel spreadsheet saved in project’s workspace document library and then potentially update custom fields from the results.
However it is far from a complete solution to download and deploy.
First issue – Admin rights required to use Web-Part:
Installed as per instructions (build / package / run script), then when adding the web part this familiar error occurred:
“An unexpected error has occurred.”
A quick look at the ULS shows more details:
The source was not found, but some or all event logs could not be searched. Inaccessible logs: Security.
Now that’s interesting as I had this exact same issue a few months back working with a 2007 PSI project, it turns out the answer is rather simple; the web service is attempting to write to the event log using “EventLog.WriteEntry” method calls, however in Windows 2008 and above this is restricted to Administrators only. So as the PWA users are not local admins the error was guaranteed.
A quick solution is to comment out each line in the code beginning with “EventLog.” of course that will disable the logging, so you would be better off doing that after you get everything else working. The file in the solution that you are after is CostAndBenefiXlsWebPart.cs in the WebPart.CostBenefit project and you’ll find about half a dozen envent log entries to comment out.
Second issue – The default site template location:
While your in that file commenting out lines, you’ll most likely notice this;
private string pdpSitePath = @”http://project.contoso.com/PWA/MajorPWSTemplateSite;”
Although the solution will work without changing this, as that is only used when no workspace site exists (like when in server settings – project detail pages), updating that to point to your http://server/pwa/template workspace site template will fix any file not found issues when setting up your PDPs.
Third issue – Location of the Excel document used:
If you’ve gotten this far then like me you might be seeing something like this:
No item exists at http://servername/_layouts/xlviewer.aspx?list=b73da3b8-b48e-400c-8fe0-9b6a48442518&id=1&DefaultItemOpen=1&Edit=1. It may have been deleted or renamed by another user.
A little digging turned up the cause of this one to be the embedded ID in that URL “&id=1”, which means that it is trying to open the document with ID 1 in that particular list. This again is hard coded in the same .cs file as above (line 191), so you can either make certain that in your template above the first file saved to the Project Documents folder is your xls sheet, or modify the code again.
All done.
So now we should have a working webpart referencing our Excel sheet saved in the project workspace site, there are some obvious limitations, not least of all the inability to use any other spreadsheet in the webpart. But I guess that’s why they call it a “solution starter”.
Update 23/10/2010: Most of the points above have been fixed in the more recent updates to the solution starter package, make sure you update your code from Codeplex: http://code.msdn.microsoft.com/P2010SolutionStarter
Hi,
I deployed cost and benefit webpart from vs2010 itself. Its deployed but when am trying to add as PDP its giving error ” File cannot open”.
I changed the PDP setttings as you said above.
private string pdpSitePath = @”http://epm2010/PWA;”
Can i use another excel sheet?
Thanks in advance
Iqbalkmk
It sounds like your pdpSitePath is incorrect, that should point to your workspace template location, e.g; “http://epm2010/PWA/template”, but that would not cause issues when the webpart is used in a published site, in that case it is probably issue 3 above.
hi,
after deploy and debug the solution, it seems that the CostBenefit.svc is not being fired, at least the custom fields are not being updated.
i changed all the references to pwa site and i pointed the IIS application to the P2010.WCF.CostBenefit physical path.
Also, I changed Constants.cs and CostBenefitService.cs in order to read my customized excel book.
What happens is… nothing! I can debug correctly the webpart but it seems that Save action doesn’t make anything.
Any idea??
Thanks
Hi there,
Any idea how to change the currency for the cost and benefits webpart, so when I deploy the solution all new created proposals/projects will fill it with £ costs?? Rather than $??
Hmm, I would imagine that once you have PWA / SharePoint setup with the UK local then you would need to make the same changes in the Excel sheet and possibly Excel Web App (?). Sorry can’t say I’ve had to try that one..
Hi,
I have downloaded latest solution starter for cost and Benefit.I have deployed the solution and able to open the excel.But I’m unable to edit the excel.Also,I’m not getting the interface which I could see in the virtual lab.Will you please help me.
Hi,
The version of the Cost Benefit solution starter is massively changed since the Virtual Lab version, it’s far better and easier to use now!
If you can’t edit the Excel in my experience it is either; you don’t have Office Web Apps installed, or the Excel Workbook contains features not supported by Office Web Apps (such as data-validation).
Try using the Excel file provided with the solution starter that just has two lines, that should work as long as you have your Excel Web App setup correctly.
HTH,
Martin
Amazing article Very nice greetings :-)!