Cascade Skyline - with Microsoft Logo and Project Support header - author Brian Smith

August, 2010

  • Brian Smith's Microsoft Project Support Blog

    Project Server 2010: Reporting using Excel Compared to Office Web Components


    One of out early adopter customers noticed this different behavior between Office Web Components (OWC) and Excel, when connected to the same Analysis Services cube, so thought I would share to hopefully save others some head scratching and offer a (lame) workaround.  As OWC is no longer used in Project Server 2010 we expect many users will now have Excel as a main part of their reporting story so worth understanding what is going on here.

    The issue comes from the fact that OWC and Excel pull the information from Analysis Services in a different way, which means that OWC builds up relationships that Excel can only infer.  As an example I will use Projects and Resources, and my cube is actually created from Project Server 2007, so that I can show the behavior in OWC and also Excel 2010 – but the same issue would exist if I used a 2010 cube as my target..  The MSP_PORTFOLIO_ANALYZER cube does not have a direct relationship between projects and resources, but obviously is does have resource data relating to work that builds up within projects.  In my simple example I will just show projects, resource and actual work.

    So in OWC we see something like this:


    and I can expand the ‘+’ signs to show the resources:


    The same thing in Excel looks like this, and I have cut it short – but you will get the idea.  I don’t have the ‘+’ sign so cannot expand and contract (apart from unselecting and reselecting the Resource List dimension).  It does have the structure, but not the expand/collapse.


    *** Update 2/9/2011 *** I should point out that the following workaround isn't meant to be a serious option as the whole idea of using PivotTables is to have the dynamic slice and dice capability - the following steps would need to be repeated every time you wanted to make a slight "what-if" change.  No sane person would want to do this - just wanted to clarify I was not (quite) insane when I suggested it...

    A work around is to build a PivotTable based on the PivotTable, but first you need to change you original PivotTable above by changing a couple of settings:

    First right-click and select PivotTable Options and the display column.

    Select the Classic PivotTable layout and click, then the Totals and Filters Tab and uncheck both Show grand totals boxes - then OK.



    Then right-click a project name, and select Field Settings and the Layout and Print tab and select the Repeat item labels (new in 2010!),  On the Subtotals & Filters Tab select None - then OK.



    Your PivotTable should now look something like this.


    If we now insert a new PivotTable, and use this as our source table (from the second line) we can get to this:


    Which is pretty close to our OWC starting point.  I guess you could do some automation, but honestly this does take away from the flexibility of the PivotTable if you start adding extra dimensions to the original one – and if you had a time dimension across the top that would again add some challenges.

    I’d be interested to hear if I am missing something and if there is a better solution – and we are looking to see if we could perhaps do something in the cube to start with so that Excel gives us what we need a little easier.  One interesting observation is that SQL Management Studio also gives the expand/collapse option when browsing data in the cube.

  • Brian Smith's Microsoft Project Support Blog

    Project Server: Flag fields – why can’t I make them required?


    We have had a couple of questions recently relating to this, so thought I’d post some explanation.  In Project Server 2007 we hid the option to make flag custom fields required as soon as you selected a custom field type of ‘Flag’.  In 2010 we just greyed out the option, which might be what has raised awareness of this one.  A Flag field has just two values.  Yes or no. (no that wasn’t a question – but the answer).  By default the value is set to No.  If the user wants to they can change to Yes.  But it will always have a value (either Yes or no) – so there is absolutely no point in making it required – so we do not even give you the option.  For this same reason in the workflow we do not display any fields of type flag in the list of fields to be required at specific stages – because it will always have a value anyway!

    When asking this question what customers really want is a way to ensure that the flag has a value that someone has consciously set – rather than the default.  So does ‘No’ mean ‘No’ – or did the user just not bother changing it?  One way around this is to create a lookup table with three values:

    • Not Set
    • Yes
    • No

    and make Not Set the default for the custom fields that use it.  This remove ambiguity.  Or you could just have two values in a lookup table – Yes and No, but for the custom field have no default and then you could make the custom field required.  In this case a blank value means it has not been set..  I hope that gives you enough options to achieve what you need.

    Technorati Tags: ,
  • Brian Smith's Microsoft Project Support Blog

    Project Server 2010: 5 DB Restore and Provision cannot be used within the same farm to clone a PWA site


    I know of a few customers who have tried this, and it seems as though it should work, but for the way SharePoint references objects.  Even if you try to clone your PWA into a new web application you will have problems.  The first issue is when you try to attach a content database to one web application that has been backed up and restored with a new database name.  The error you get will look like this:


    Now the first part of the text in the error gives a potential workaround:

    • The attach operation cannot continue because another object in this farm already contains the same ID. Each object in a farm must have a unique ID. In order to proceed with the attach operation you must assign a new ID to this database. To attach this database with a new ID, use the Mount-SPContentDatabase command with the -AssignNewDatabaseId parameter.

    And it is possible to use that workaround with PowerShell and get the copied Content DB attached.  However, the second part of the error comes in to play:

    • Note that if this new database and an existing database contain the same site collections, attaching this database will likely result in orphaned site collections due to conflicts between the two databases.

    You will see that when connected there will be fewer site collections contained in the attached Content DB than in the original.  Unfortunately this missing sites will include PWA and any Project sites in the DB.  So you will not be able to browse to the copy sites on this web application, and neither will you be able to provision a PWA site against the copy of PWA.  If you do try and provision a new PWA on this web application (with copies of the Project Server databases that match up to the copied content DB) then it will appear to work, but will in fact have produced a new PWA site, and although the projects will be there, the result will be just like a 4 DB provision – and no access to the sites, and more importantly, the Project Detail Pages (PDPs) will be possible.  Project Center will be broken as the required PDPs will not be there.  You will have access to your projects via Project Professional, and some other parts of PWA will work, so it depends why you want this copy – it may still suit your needs.

    If you are doing this to create a Test or Development environment then I would strongly recommend having dedicated hardware (or a ‘dedicated’ virtual environment) for this, and not trying to duplicate on the production server.

    One helpful tool will be coming along soon that will give another option for cloning sites – the 2010 version of the Project Server Playbooks tool.  I will certainly be reviewing and posting a blog when it is available.

    Technorati Tags:
  • Brian Smith's Microsoft Project Support Blog

    Project Server 2010: Integration with Team Foundation Server 2010


    I know many of you have been waiting for this, and I missed the original posting just as I went on holiday.  TFS 2010 supported integration with Project Standard 2010 and Project Professional 2010, but now there is a CTP release demonstrating the integration between Team Foundation Server 2010 and Project Server 2010.  Much more detail available on bharry’s blog at, but briefly there is a download of the CTPand a forum to give feedback.  The work isn’t finished – so take a look and see how the integration would work for you, if you are users of both these great platforms.

    There will also be a webcast on August 20th at 8:00am PST. with Christophe from the Project side of things and John Nierenberg, Senior Program Manager from the TFS side.

    Technorati Tags: ,
  • Brian Smith's Microsoft Project Support Blog

    SharePoint Server 2010: Accidentally stopped your Central Administration service? Start it again with PowerShell!


    OK, I had one of those days yesterday.  I was working on my various servers and Hyper-V images to get them all patched and also wanted to update my SQL Server, but rather than shut down my Project Server 2010 application server I thought I’d just stop all the service in Central Administration.  Including Central Administration itself…  If you haven’t tried that one then you will go from this:


    to this, when you click Stop.


    Excellent!  A chance to learn some more PowerShell, as I was sure there must be some way of recovering from this from the SharePoint 2010 Management Shell.  So using the wonderful TAB feature to auto complete commands I soon found, by opening the Management Shell as administrator, typing start and then TAB a few times the command I needed – Start-SPServiceInstance.  I guess get-command, or gcm start* would have been nearly as quick.  A quick look at get-help start-SPServiceInstance and I found I needed the GUID (Identity) that represented the Central Administration service.  Come on, you all know yours by heart?  Get-SPServiceInstance came to the rescue, but I knew there was a better way than cutting and pasting the GUID after first sifting through all the services… (Tip – Get-SPServiceInstance | fl > servicelist.txt makes it a bit easier to read).  I should be able to pipeline the filtered output from my Get command through to my Start command.  So a few minutes and syntax checks later I had it solved:

    Get-SPServiceInstance | Where-Object {$_.TypeName –eq ‘Central Administration’} | Start-SPServiceInstance

    and my Central Administration site was back and available!  If you try this and it just sticks at provisioning then perhaps your SharePoint Timer Service is not running (Check Administrative Tools, Services on the application server) .  You can check the status with:

    Get-SPServiceInstance | Where-Object {$_.TypeName –eq ‘Central Administration’}

    it should eventually say Online.  It will say Disabled before you start it, and then Provisioning and finally Online.

    It is certainly worth getting to know what PowerShell can do to automate SharePoint.

Page 2 of 3 (11 items) 123