Wednesday, October 23, 2013

#ProjectOnline #ProjectServer oData "One or more Data Connections Failed to refresh"

I've been exploring the magical world of Project Online oData Reporting this last month. So far I've managed to pull together some pretty useful PowerPivot-based Timesheet dashboards, and some decent Project/Task Statusing reports.  Nothing as fun as on-premise, but not a bad start.  

However....as well as the well documented service change required to grant reporting access to project online (see here: http://office.microsoft.com/en-gb/office365-project-online-help/grant-reporting-access-in-project-online-HA104021109.aspx) there is another issue to be aware of.

One thing has been concerning me, and that is data set sizes. As we need to pull down some large tables to build a decent data-model  it has become clear that filtering the feeds is the only way to make these reports workable.  Unfortunately, with Project Online its not quite as easy as that.

SharePoint in Office365 uses WopiFrame to display published Excel reports in the browser.  This is basically the Office Web App, rather than the standard xlviewer seen in non-webapp installations.

You can see the behavior in the URL of the report when you view it in the browser

Unfortunately this Office Web App viewer has a limitation that it cannot accept filtered odata feeds when refreshing reports in the browser.  When refreshing reports with filtered feeds you will receive something like:

External Data Refresh Failed
An error occurred while working on the Data Model in the workbook. Please try again. 
We were unable to refresh one or more data connections in this workbook.
The following connections failed to refresh: 
etc

There is a powershell script (below) to switch the default behavior for an on premise installation but this isn't available online.

The command to change this behavior for on-premise is: New-SPWOPISuppressionSetting -extension xlsx -action view

So, what this is currently looking like (and I am hunting for alternatives here) is that - for project online, you cannot use filtered oData feeds  and still allow the report to be refreshed via the browser

This is a fairly fundamental limitation of this service, and one that I understand is known of (although it was new to me) so I am hopeful for a fix/change to this.

here's hoping....


.... oh, and don't get me started on the lack of OUTER join function in PowerPivot

Monday, September 30, 2013

Issues update on #projectserver2013 - Timesheets and Publishing

Reporting Publish
** updated with links to other related discussions, and a VBA macro **

the following issue has been noted on publish since June 13 CU was applied:


ReportingProjectChangeMessageFailed (24006) - Object reference not set to an instance of an object.. Details: id='24006' name='ReportingProjectChangeMessageFailed' uid='4d869e56-f625-e311-bb41-005056b90052' QueueMessageBody='Project UID='e3f49977-b2bc-e211-8559-005056b90052'. PublishType='ProjectPublish'' Error='Object reference not set to an instance of an object.'.


I have seen this noted previously on a similar issue:

http://nearbaseline.com/blog/2013/06/ms-reporting-project-publish-jobs-failed-after-aprilcu/comment-page-1/#comment-14741

This is  caused by Baselined Milestones having NULL Baseline Cost values
Original bug note with potential workaround is here:


The following macro (from MS) is designed to resolve this:
NOTE:  I am providing NO warranty for this!


'DISCLAIMER OF WARRANTY
'
'THIS FIX CODE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.
'MICROSOFT FURTHER DISCLAIMS ALL IMPLIED WARRANTIES INCLUDING WITHOUT
'LIMITATION ANY IMPLIED WARRANTIES OF MERCHANTABILITY OR OF FITNESS
'FOR A PARTICULAR PURPOSE. THE ENTIRE RISK ARISING OUT OF THE USE OR
'PERFORMANCE OF THE CODE REMAINS WITH YOU.
'
'IN NO EVENT SHALL MICROSOFT OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES
'WHATSOEVER (INCLUDING, WITHOUT LIMITATION, DAMAGES FOR LOSS OF
'BUSINESS PROFITS, BUSINESS INTERRUPTION, LOSS OF BUSINESS INFORMATION,
'OR OTHER PECUNIARY LOSS) ARISING OUT OF THE USE OF OR INABILITY TO USE
'THIS MACRO, EVEN IF MICROSOFT HAS BEEN ADVISED OF THE POSSIBILITY OF
'SUCH DAMAGES. BECAUSE SOME STATES DO NOT ALLOW THE EXCLUSION OR
'LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES, THE
'ABOVE LIMITATION MAY NOT APPLY TO YOU.



'This macro attempts to work around the reporting publish failure that occurs when there are tasks

'with baselines and where the BaselineCost contour is NULL. The symptoms that you see in the queue
'for the "Reporting (Project Publish)" job has details similar to:
'
'
'
'uid="c72e20a7-9b7f-487b-9da2-19460b2e6c32"
'QueueMessageBody="Project UID='8e074f24-1064-47dd-bce5-c2dad8d556c8'.
'PublishType='ProjectPublish'" Error="Object reference not set to an instance of an object." />


'This code simply walks through the tasks in the tasks in the project and if there are

'baseline(s) set, then the code sets a time scaled baseline cost = 0 on the day prior to
'where the baseline start is scheduled.  This sets the baseline contour so that the
'"Object reference not set to an instance of an object" problem can be overcome during the
'reporting publish job.

Sub FixPublish()


Dim t As Tasks

Dim tsv As TimeScaleValues
Dim i As Long
Dim bCalc As Boolean

On Error Resume Next


'get the current application calculation state

bCalc = Application.Calculation
'set calculation to manual (helps with performance while the code runs)
Application.Calculation = pjManual

Set t = ActiveProject.Tasks


'walk through the tasks in the project. If a task as a baseline(s) then set a timescaled baselinecost = 0

For i = 1 To t.Count
    If Not t(i) Is Nothing Then
        If t(i).BaselineStart <> "NA" Then
            Set tsv = t(i).TimeScaleData(t(i).BaselineStart - 1, t(i).BaselineStart - 1, pjTaskTimescaledBaselineCost, pjTimescaleDays)
            If tsv(1).Value = "" Then
                tsv(1).Value = 0
            End If
        End If
        If t(i).Baseline1Start <> "NA" Then
            Set tsv = t(i).TimeScaleData(t(i).Baseline1Start - 1, t(i).Baseline1Start - 1, pjTaskTimescaledBaseline1Cost, pjTimescaleDays)
            If tsv(1).Value = "" Then
                tsv(1).Value = 0
            End If
        End If
        If t(i).Baseline2Start <> "NA" Then
            Set tsv = t(i).TimeScaleData(t(i).Baseline2Start - 1, t(i).Baseline2Start - 1, pjTaskTimescaledBaseline2Cost, pjTimescaleDays)
            If tsv(1).Value = "" Then
                tsv(1).Value = 0
            End If
        End If
        If t(i).Baseline3Start <> "NA" Then
            Set tsv = t(i).TimeScaleData(t(i).Baseline3Start - 1, t(i).Baseline3Start - 1, pjTaskTimescaledBaseline3Cost, pjTimescaleDays)
            If tsv(1).Value = "" Then
                tsv(1).Value = 0
            End If
        End If
        If t(i).Baseline4Start <> "NA" Then
            Set tsv = t(i).TimeScaleData(t(i).Baseline4Start - 1, t(i).Baseline4Start - 1, pjTaskTimescaledBaseline4Cost, pjTimescaleDays)
            If tsv(1).Value = "" Then
                tsv(1).Value = 0
            End If
        End If
        If t(i).Baseline5Start <> "NA" Then
            Set tsv = t(i).TimeScaleData(t(i).Baseline5Start - 1, t(i).Baseline5Start - 1, pjTaskTimescaledBaseline5Cost, pjTimescaleDays)
            If tsv(1).Value = "" Then
                tsv(1).Value = 0
            End If
        End If
        If t(i).Baseline6Start <> "NA" Then
            Set tsv = t(i).TimeScaleData(t(i).Baseline6Start - 1, t(i).Baseline6Start - 1, pjTaskTimescaledBaseline6Cost, pjTimescaleDays)
            If tsv(1).Value = "" Then
                tsv(1).Value = 0
            End If
        End If
        If t(i).Baseline7Start <> "NA" Then
            Set tsv = t(i).TimeScaleData(t(i).Baseline7Start - 1, t(i).Baseline7Start - 1, pjTaskTimescaledBaseline7Cost, pjTimescaleDays)
            If tsv(1).Value = "" Then
                tsv(1).Value = 0
            End If
        End If
        If t(i).Baseline8Start <> "NA" Then
            Set tsv = t(i).TimeScaleData(t(i).Baseline8Start - 1, t(i).Baseline8Start - 1, pjTaskTimescaledBaseline8Cost, pjTimescaleDays)
            If tsv(1).Value = "" Then
                tsv(1).Value = 0
            End If
        End If
        If t(i).Baseline9Start <> "NA" Then
            Set tsv = t(i).TimeScaleData(t(i).Baseline9Start - 1, t(i).Baseline9Start - 1, pjTaskTimescaledBaseline9Cost, pjTimescaleDays)
            If tsv(1).Value = "" Then
                tsv(1).Value = 0
            End If
        End If
        If t(i).Baseline10Start <> "NA" Then
            Set tsv = t(i).TimeScaleData(t(i).Baseline10Start - 1, t(i).Baseline10Start - 1, pjTaskTimescaledBaseline10Cost, pjTimescaleDays)
            If tsv(1).Value = "" Then
                tsv(1).Value = 0
            End If
        End If
    End If
Next

'fix the project summary task

With ActiveProject.ProjectSummaryTask
    If .BaselineStart <> "NA" Then
        Set tsv = .TimeScaleData(.BaselineStart - 1, .BaselineStart - 1, pjTaskTimescaledBaselineCost, pjTimescaleDays)
        If tsv(1).Value = "" Then
            tsv(1).Value = 0
        End If
    End If
    If .Baseline1Start <> "NA" Then
        Set tsv = .TimeScaleData(.Baseline1Start - 1, .Baseline1Start - 1, pjTaskTimescaledBaseline1Cost, pjTimescaleDays)
        If tsv(1).Value = "" Then
            tsv(1).Value = 0
        End If
    End If
    If .Baseline2Start <> "NA" Then
        Set tsv = .TimeScaleData(.Baseline2Start - 1, .Baseline2Start - 1, pjTaskTimescaledBaseline2Cost, pjTimescaleDays)
        If tsv(1).Value = "" Then
            tsv(1).Value = 0
        End If
    End If
    If .Baseline3Start <> "NA" Then
        Set tsv = .TimeScaleData(.Baseline3Start - 1, .Baseline3Start - 1, pjTaskTimescaledBaseline3Cost, pjTimescaleDays)
        If tsv(1).Value = "" Then
            tsv(1).Value = 0
        End If
    End If
    If .Baseline4Start <> "NA" Then
        Set tsv = .TimeScaleData(.Baseline4Start - 1, .Baseline4Start - 1, pjTaskTimescaledBaseline4Cost, pjTimescaleDays)
        If tsv(1).Value = "" Then
            tsv(1).Value = 0
        End If
    End If
    If .Baseline5Start <> "NA" Then
        Set tsv = .TimeScaleData(.Baseline5Start - 1, .Baseline5Start - 1, pjTaskTimescaledBaseline5Cost, pjTimescaleDays)
        If tsv(1).Value = "" Then
            tsv(1).Value = 0
        End If
    End If
    If .Baseline6Start <> "NA" Then
        Set tsv = .TimeScaleData(.Baseline6Start - 1, .Baseline6Start - 1, pjTaskTimescaledBaseline6Cost, pjTimescaleDays)
        If tsv(1).Value = "" Then
            tsv(1).Value = 0
        End If
    End If
    If .Baseline7Start <> "NA" Then
        Set tsv = .TimeScaleData(.Baseline7Start - 1, .Baseline7Start - 1, pjTaskTimescaledBaseline7Cost, pjTimescaleDays)
        If tsv(1).Value = "" Then
            tsv(1).Value = 0
        End If
    End If
    If .Baseline8Start <> "NA" Then
        Set tsv = .TimeScaleData(.Baseline8Start - 1, .Baseline8Start - 1, pjTaskTimescaledBaseline8Cost, pjTimescaleDays)
        If tsv(1).Value = "" Then
            tsv(1).Value = 0
        End If
    End If
    If .Baseline9Start <> "NA" Then
        Set tsv = .TimeScaleData(.Baseline9Start - 1, .Baseline9Start - 1, pjTaskTimescaledBaseline9Cost, pjTimescaleDays)
        If tsv(1).Value = "" Then
            tsv(1).Value = 0
        End If
    End If
    If .Baseline10Start <> "NA" Then
        Set tsv = .TimeScaleData(.Baseline10Start - 1, .Baseline10Start - 1, pjTaskTimescaledBaseline10Cost, pjTimescaleDays)
        If tsv(1).Value = "" Then
            tsv(1).Value = 0
        End If
    End If
End With
            
'set the application calculation state back the way it was
Application.Calculation = bCalc
            
End Sub



Tuesday, July 9, 2013

#ProjectServer2013 Timeline issue

New project created
PWA Schedule timeline Phases shown in timeline













  


Project Published
SPSite Timeline seen to be a match for PWA schedule Timeline












Task #3 – Managing and controlling is deleted (with all its subtasks)


Initial view of PWA Schedule timeline is incorrect (I will forgive this one)












Publish plan and switch between PDPs and eventually the timeline displays correctly















Note the three remaining PHASE tasks
Now switch to the SPSite Timeline….











Notice the new task on the timeline now (making 4)
I have also seen other events where tasks are deleted from the plan, but yet remain in the Project Site Task List (and timeline) so are therefore orphaned, although I haven’t yet managed to repro this particular event


*FIXED* #ProjectServer2013 bug? - cannot close in progress timesheet periods

Timesheet periods cannot be closed "bug"(?):

** UPDATE:  THIS IS NOW FIXED WITH THE JUNE 2013 CUMULATIVE UPDATE FOR PROJECT SERVER 2013 **
http://support.microsoft.com/kb/2817341

The following has been repro'd on 
- an Project Server 2013 (RTM) - next step is June CU
- project online

Repro:
- Create a number of resources/users
- Set up timesheet periods
- Test 1: TS periods can be closed
- Create a timesheet (one user)
- Save timesheet
- Test 2: Close timehseet period (same period as timesheet created in) = Result - TS period cannot be closed 

Note:  you can also note a javascript error in IE at line 1409.  When you look at the source code, this appears to point to a page with the URL /pwa/_Layouts/15/pwa/admin/PeriodCloseConfirmation.aspx
Now I don't know if this is a legacy page or not, but if you navigate to this page you see this (which is exactly what I want to see when closing a period)


So what this means if this means is:- if you are running timesheets it is impossible to exempt some users from timesheets submission as periods wont be able to be closed!! 

Another fail in this is that the Timesheet Status flag is extremely unreliable. I have seen many examples where a timesheet returns to 0 State (not submitted) event when it is approved. This will also trigger this failure to close timesheet periods.

It's my belief that this is not by design behavior due to the existence of this page, which suggests it is an override to allow periods to be closed:

Friday, July 5, 2013

#ProjectServer2010 PWA Provisioned With Errors (no ProjectBICenter provisioned)

Here's a repeated scenario that I thought I'd write up quickly:

On a new environment (2010 SP1 + June 2011 CU V2) I provisioned the first PWA site in the Farm.  Unfortunately the site provisioned but with errors, the errors being that the ProjectBICenter did not get created.

I worked through the usual ULS log entries and found a number of records relating to:

- Unable to create Images library due to content type "document" already exists


The fix for this as far as I can tell:
- Open STSADM and run
           

         stsadm -o deleteweb -url http://pwaaddress/projectbicenter


- Navigate to the PWA site and create a new subsite 
-- Using BI Center template
-- Named Business intelligence center
-- URL /ProjectBICenter

- now go to the Project Server Service App
-- Select the pulldown menu and select EDIT
-- Click EDIT to reforce provisioning

This should now complete, and in my scenario this stopped the issue happening again

fin

Monday, July 1, 2013

#projectserver2013 Issues part 2 - Cannot close timesheet periods

#projectserver2013 Issues part 2

Following on from my previous post, here's some other tidbits of anomalies on PS2013

Close Timesheet Periods
There's an issue with page rendering and IE versions here.  When attempting to close periods, the changes cannot be saved

symptoms:
Javascript error on page (syntax error at line 1408)

Occurs on
IE8
IE9

The problem appears to be a page call to /pwa/_Layouts/15/pwa/admin/PeriodCloseConfirmation.aspx

I expect (unconfirmed) that the issue is because of Timesheets that have not been submitted for a period I wish to close.  However some resources do NOT complete timesheets in our configuration so this is ok.

When I looked at the code I found a call to this PeriodCloseConfirmation page which I assume is supposed to load when this event occurs, but it does not.

This looks a fairly substantial issue right now.  I'd be interested to know if anyone else sees the same thing.








Friday, May 24, 2013

#ProjectServer2013 Issues with Views

Project Server 2013 Issues Summary

Over the last few weeks I've been implementing a number of Project Server 2013 systems, and have found a couple of application issues that are worthy of note:

Views Issue #1 - Project Center / Resource Center - "The View Failed to Load"

This error, followed by "You do not have permissions to view" message appear to relate to a failure of the webpart to select a view from the list available to me.

One example of how this can happen is that if I selected View A the last time I accessed Project Centre, but since then View A has been removed/taken away, then this error will occur.

Another example MAY (awaiting repro) be if you delete/rename/remove the default Project Centre view.

The fix is three clicks of a mouse but is really not good.

- Open the ribbon
- Select a new view from the available list
- F5 Refresh

This should be a simple code fix to ensure that if the cached last view is no longer available, enumerate through to find the first available view and display that.

Note:  This has also been noted on Resource Centre although the second error you get is Access Denied


Views Issue #2 - Phantom Project Schedule views
This is a very new one.  we removed a pile of the default schedule views from all categories as we had replaced them with our own custom views.
Strangely any projects created PRIOR to this change being made still display the views as available, however if you select them they return "There is no data available" (as you would expect as they are not attached to a category)

Any new projects do not display these phantom views.

Again, this is very early warning, and I will be aiming to repro this in a few weeks time.

Views Issues #3 - Timesheet creation failure
Again this is initially noted by the generic "The view failed to load" but this time there are no options to workaround this.  The issue was noted for a specific resource, so I worked on the basis that it was a data issue.

I looked in the tasks.aspx view for the resource while delegating and found one assignment with a NULL value in the Work field.

Upon reviewing this in the plan, this task was a Milestone.

Of course we all know that you shouldn't put resouces on milestones, but as this is supported by Tasks.aspx and you cannot stop it, the timesheet task population job SHOULD account for this and at least either exclude the assignment or add it in as read only.

The ULS error we saw was:

Error is:  GeneralUnhandledException.  Details: General Unhandled Exception in _Timesheet.CreateTimesheet_ Attributes: System invaldcastexception: Cannot cast DBNull.Value to type 'System.Byte'. Please use a nullable type.

Removing these resources from milestones, and publishing, resolved the issue.

more to follow once i've repro'd!

Carl

Friday, February 1, 2013

First #ProjectServer2013 Implementation done

Just a small note to say that as of last week we have completed our first SharePoint and Project Server 2013 Implementation in the UK.

Over the course of the next few weeks I will be documenting my findings, pitfalls and fun that entails... as soon as I get a minute to spare.

Here comes the new world




Tuesday, January 8, 2013

Project Server 2010 PSI : Unhandled Communication Fault #projectserver




Environment
  • Two tier environment
  • -        2x NLB Servers operating in WFE/APP role
  • -        Active/Passive SQL Cluster
  • NLB Settings
  • -        Multicast
  • -        Single affinity / sticky sessions
  • Web App created against NLB Host
  • PWA site created on Host Header
  • PWA Site DNS Alias linked to NLB Hostname

Issue: 
Running a custom powershell PSI job failed.  

Exception calling "readuserlist" with "1" argument(s): "Unhandled Communication Fault Occurred"
At line: 1 char:25
CategoryInfo NotSpecified: (:) [], MethodInvocationException

I ran projtool and when attempting to log on I received “Unhandled Communication Fault occurred” 

Fiddler returned the following http 500 error

1a2
http://schemas.xmlsoap.org/soap/envelope/
">http://Microsoft.Office.Project.Server">a:ProjectServerFaultCodeUnhandled Communication Fault occurredhttp://schemas.microsoft.com/2003/10/Serialization/">Value cannot be null.
Parameter name: account

0

Resolution:
Disabling Anonymous Access in IIS on both WFE/APP Servers resolved the issue

  • Open IIS
  • Identify and select the website that is hosting the Project Web App Site(s)
  • Select Authentication 
  • Right click on Anonymous Access and Disable
  • Retest PSI Calls - should now be successful