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

  • Brian Smith's Microsoft Project Support Blog

    Customizing the Project Site in Project Server 2010

    • 41 Comments

    The steps to customize and use different Project Site templates (the entities formerly known as Project Workspace templates) have changed for 2010, and the ways you can use them with the new Enterprise Project Templates has introduced more change.  I thought I’d explain how these can be created, what customizations will work – and which will break things, what limitations you will see with the reporting data for modified sites and how they can be consumed.  I'll show screen shots as I go along too.

    The first thing is to create a new site that you will customize to become your new template.  For this the easiest way is to go to Site Actions (top left of PWA page) New Site

    image

    then make sure you choose Microsoft Project Site from the available sites, give it a title and URL name and click Create.  note in this screen shot I have scrolled down the page a little.

    image

    Once created then you can customize (almost) to your heart’s content.  This assumes your heart’s content does not want to delete or duplicate any of our built-in lists, or change the type or delete any of our built-in list’s columns.  For this example I will add another list, a document, add some columns to the Risks list and also change the choices for the Issues Status column.

    But first, I’ll change the theme and color.  Site Actions, Site settings, (Look and Feel) Site Theme will take you to here:

    image

    I’m going to choose Modern Rose, and scroll down and click Preview, which pops open a new windows like so:

    image

    which will do for demonstration purposes…  So I now click Apply on the original screen (down the bottom), and continue my customization.  I will add a column called Tracker.  All Site Content (bottom of left nav) Create, Filter by List and choose Custom List, with a Name of Tracking and click Create.

    image

    I will leave my list as it comes, but usually you’d want to add other columns and views too.  Next I add a document to my Project documents library – Project Documents, Add document (I think I can skip a picture or two) and then just browse and upload:

    image

    Next, for my Risks List I add a column called Triage.  Risks, then select the List tab in the List Tools ribbon element, then List Settings:

    image

    Scroll down the resulting page,and click Create column

    image

    I’m just calling mine triage and taking all the defaults, which also adds it to the default view – not much to see there:

    image

    Finally we will go to our Issues list and add another value to our Status choices.  Issues, List tab, List Settings, then click on the Status column.  I’ve added on hold and renumbered the list accordingly.

    image

    That’s my customization finished, so no to save and re-use.  Site Actions, Site Settings (Site Actions) Save Site as template.

    image

    Give it a File Name, Name and description, and check Include Content, then OK.

    image

    Then go to Site Actions, Site Settings, (Galleries), Solutions (or use the hyperlink on the “Operation Completed successfully’ screen and you will see your new Template is Activated! It will now be available both as an option within the default Project Site provisioning Settings option under PWA Server settings.  In this screen shot I have actually chosen my SampleTemplate template (I couldn’t get the drop down in a screen shot…)

    *** Update *** I discovered going through this process that the following setting is really redundant.  All projects created will get the setting from the an Enterprise Project Template, and not this setting.  Out of the box the Basic Project plan is the default for PSI or Project Professional plans. Changing the template here will do nothing. ***

    image

    It will also be available for either existing Enterprise Project Templates, or new ones, in the drop down at the foot of the EPT page:

    image

    Now, lets use my new template and see what we get.  First I’ll set my Basic Project plan EPT to use my new SampleTemplate, then I'll create a new basic project plan called SampleTest and publish to get my new site.  As you can see from my queue list it all went through, so I haven’t broken anything.

    image

    And here is my site!

    image

    I have my document

    image

    You can already see my new tracking list, and here if I add a new Issue I can choose my new On hold status.

    image

    The really cool thing is that my On hold status has flown right through to the reporting database WITHOUT HAVING TO PUBLISH THE PROJECT!  And this was a custom addition choice for the field.  This from my Excel sample template from the Business Intelligence Center – hardly the most inspiring report – but I was pleased to see it work!

    image

    So some gotchas:

    • The site template used is driven by the Enterprise Project Type – and at this stage I assume the default only gets used when creating projects that are not based on an EPT (possibly via PSI?).  From Project Professional the Basic Project Plan type is used.
    • Don’t add additional lists that are copies of the built in ones such as issues and risks
    • Don’t change the column type of existing columns
    • Don’t delete existing columns – just remove from views if they are not needed
    • Any new things (apart from new choices as I showed above) will not get into the reporting database (unless you do extra work…
    • Modern Rose is not the most inspiring theme

    Most of these gotchas will cause problems with reporting – so if doing heavy customization always monitor the queue to see exactly what works (and what breaks things!) with the Reporting jobs.  Look in the ULS logs for further information if you see failures.

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

    Project Server 2010: Business Intelligence Center–Common Errors

    • 22 Comments

    This follows on from the posting on the structure of the Business Intelligence Center in Project Server 2010 - http://blogs.msdn.com/b/brismith/archive/2010/08/24/project-server-2010-business-intelligence-center-behind-the-scenes.aspx and covers a few of the possible errors you might run in to.

    For Project Server 2007 one of my most read, or at least “most viewed” and commented pages was Common Error Messages when Building Cubes with Project Server 2007.  I think with the architecture of 2010 we resolve many of those cube building difficulties, but the configuration or Excel Services Application and Secure Store Service will fill the gap that leaves.  I am preparing some internal training on this topic so thought I’d re-purpose many of my screenshots in this blog posting.  I start with no configuration or Excel Services or the Secure Store Service at all, as if I had never read or even heard of the excellent article at http://technet.microsoft.com/en-us/library/ee662106.aspx that describes how things should be configured and then hit one error after another until finally I have it all working.  I will attempt to run the SimpleProjectList sample report from the default English (United States) library.  I will number the errors, and then at the end of the posting I will list the ULS log entries you can expect to see for each error, along with the text from these error dialogs, and hopefully this will allow you to work backwards from the logs to the cause.  I want to include the logs so the search engines have something cool to read without breaking the flow of the story too much – and the text from the dialogs as the search engines may struggle with the pictures.

    image

    Error 1

    image

    The first error I see tells me that it will not open that report as the Data Connection file is not in a trusted location.  Well that’s an easy one to fix (referring to the TechNet document referenced above) so I add the right URL as a trusted location and move on…

    Error 2

    image

    Well at least this ones just a warning – and I can continue just by clicking “Yes”, but we should really fix that.  This is an interesting one as you might have wondered (if you know already what needs configuring) why we didn’t get an error about our file itself;f not being in a trusted location.  The reason is that by default there is a trusted location of http://.  And this is configured to trust children, and also to allow External Data.  In fact the only thing it is missing from our recommended configuration is that Refresh warning enabled is still checked – hence the warning we are getting at Error 2.  Best practice would probably be to delete the http:// location and then just explicitly set the locations as per the TechNet document.  So moving on…

    Error 3

    image

    Now we have Excel Services Application configured just right – so we start hitting the Secure Store Errors.  In a bid to hit most of the possible errors I created a Secure Store Service Target Application but didn’t set any credentials…

    Error 4

    image

    Hmm.  Same error, but the ULS logs distinguish between them (– see the section at the end of the blog for the differences.  So now I set some credentials, but put in a bad password…

    Error 5

    image

    Different error, and this makes sense.  We have some credentials, but they don’t work.  I’ll put that password right, but for the group that can make use of this target application I will use a group that I do not belong to…

    Error 6

    image

    Back to the generic error again, but ULS comes good and gives us a better idea what might be wrong. “Claims stored in the credentials did not match with the group claim for a group app.”  Put in the right group, of which I am a member and hey presto!  All that just for one project!

    image

    I’m sure there may be other combinations of issues that will give different errors, but I have been very impressed with the wording in the ULS logs (see below) which does make it quite easy to understand what might have gone wrong.

    Here are the details of the errors again, more for the search engines, but feel free to read on.  I haven’t listed all the lines associated with the failure – just the main ones that will help identify the failure mode.  As mentioned above I have also included the text from each of the dialogs above

    Error 1

    Dialog - The Data Connection File <url to odc file> used in the workbook is not in a trusted location.  
    The following connections failed to refresh:

    Project Server - Simple Projects List
    ULS - 
    • w3wp.exe (0x230C) 0x18DC Excel Services Application External Data 5647 Information The ODC file http://brismith8100. /PWA/ProjectBICenter/Data%20Connections%20for%20PerformancePoint/English%20(United%20States)/Project%20Server%20-%20Simple%20Projects%20List.odc is not in a DCL that is trusted by Excel Services Application. To fix this problem, add the DCL to the trusted DCL list in the administrator console.
    • w3wp.exe (0x230C) 0x18DC Excel Services Application Excel Calculation Services erw5 Medium ConnectionInfoManager.GetConnectionInfo: InitConnInfoFromOdcFile threw an exception: Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInfoException: Exception of type 'Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInfoException' was thrown. at Microsoft.Office.Excel.Server.CalculationServer.ConnectionInfoManager.InitConnInfoFromOdcFile(ExtendedConnectionInfo extendedConnInfo) at Microsoft.Office.Excel.Server.CalculationServer.ConnectionInfoManager.GetConnectionInfo(Request request, String externalSourceName, Int32 externalSourceIndex, Boolean& shouldReportFailure)
    Error 2
    Dialog - This workbook contains one or more queries that refresh external data. A malicious user can design a query to access
    confidential information and distribute it to other users or perform other harmful actions.
    If you trust the source of this workbook, click Yes to enable queries to external data in this workbook. If you are not sure, click No so that changes are not applied to your workbook.
    Do you want to enable queries to external data in this workbook?
    ULS – Not applicable – just a warning
    Error 3
    Dialog -  An error occurred while accessing application id ProjectServerApplication
    from Secure Store Service. The following connection failed to refresh:
    Project Server - Simple Projects List
    ULS -
    • w3wp.exe (0x230C) 0x2344 Secure Store Service Secure Store elm4 High SQL command failed: Sproc name: dbo.proc_sss_GetCredentials, Application Id: ProjectServerApplication, Error code: 80630490, Error message: Target application not found (application id: ProjectServerApplication).
    • w3wp.exe (0x230C) 0x2344 Secure Store Service Secure Store 7493 Critical The Microsoft Secure Store Service application Secure Store Service failed to retrieve credentials. The error returned was 'Target application not found (application id: ProjectServerApplication).'. For more information, see the Microsoft SharePoint Products and Technologies Software Development Kit (SDK).
    • w3wp.exe (0x230C) 0x2344 Secure Store Service Secure Store efp4 Monitorable GetCredentials failed with the following exception: System.ServiceModel.FaultException`1[Microsoft.Office.SecureStoreService.Server.SecureStoreServiceTargetApplicationNotFoundFault]: Target application not found (application id: ProjectServerApplication). (Fault Detail is equal to Microsoft.Office.SecureStoreService.Server.SecureStoreServiceTargetApplicationNotFoundFault).
    • w3wp.exe (0x230C) 0x21C0 Excel Services Application Web Front End 4969 Warning SSS has failed with the following exception: Target application not found (application id: ProjectServerApplication). [Session stuff here...]
    Error 4
    Dialog - Same as Error 3.
    ULS -
    • w3wp.exe (0x1BF8) 0x09A8 Secure Store Service Secure Store elm4 High SQL command failed: Sproc name: dbo.proc_sss_GetCredentials, Application Id: ProjectServerApplication, Error code: 80630001, Error message: Credentials were not found for the current user within the target application 'ProjectServerApplication'. Please set the credentials for the current user.
    • w3wp.exe (0x1BF8) 0x09A8 Secure Store Service Secure Store 7493 Critical The Microsoft Secure Store Service application Secure Store Service failed to retrieve credentials. The error returned was 'Credentials were not found for the current user within the target application 'ProjectServerApplication'. Please set the credentials for the current user.'.
    • w3wp.exe (0x1BF8) 0x09A8 Secure Store Service Secure Store efp4 Monitorable GetCredentials failed with the following exception: System.ServiceModel.FaultException`1[Microsoft.Office.SecureStoreService.Server.SecureStoreServiceCredentialsNotFoundFault]: Credentials were not found for the current user within the target application 'ProjectServerApplication'. Please set the credentials for the current user. (Fault Detail is equal to Microsoft.Office.SecureStoreService.Server.SecureStoreServiceCredentialsNotFoundFault).
    • w3wp.exe (0x1BF8) 0x12A8 Excel Services Application Web Front End 4969 Warning SSS has failed with the following exception: Credentials were not found for the current user within the target application 'ProjectServerApplication'. Please set the credentials for the current user. [Session info here…]
    • w3wp.exe (0x1BF8) 0x12A8 Excel Services Application External Data 3238 Information Unable to establish a connection using credentials retrieved from SSS. This could be because the Unattended Service Account is not configured or because the credentials retrieved from SSS are not valid. [Session info here…]
    Error 5
    Dialog - Access was denied by the external data
    source. The following connections failed to refresh:
    Project Server - Simple Projects List
    ULS -
    • w3wp.exe (0x1BF8) 0x242C Secure Store Service Secure Store efr5 High ValidateCredentialClaims - Access Denied: Claims stored in the credentials did not match with the group claim for a group app.
    • w3wp.exe (0x1BF8) 0x242C Secure Store Service Secure Store 7493 Critical The Microsoft Secure Store Service application Secure Store Service failed to retrieve credentials. The error returned was 'Access is denied.'. For more information, see the Microsoft SharePoint Products and Technologies Software Development Kit (SDK).
    • w3wp.exe (0x1BF8) 0x242C Secure Store Service Secure Store efp4 Monitorable GetCredentials failed with the following exception: System.ServiceModel.FaultException`1[Microsoft.Office.SecureStoreService.Server.SecureStoreServiceFault]: Access is denied. (Fault Detail is equal to Microsoft.Office.SecureStoreService.Server.SecureStoreServiceFault).
    • w3wp.exe (0x1BF8) 0x12A8 Excel Services Application Web Front End 4969 Warning SSS has failed with the following exception: Access is denied. [Session stuff here]
    • w3wp.exe (0x1BF8) 0x12A8 Excel Services Application External Data 3238 Information Unable to establish a connection using credentials retrieved from SSS. This could be because the Unattended Service Account is not configured or because the credentials retrieved from SSS are not valid. [Session stuff here...]

    Error 6
    Dialog – Same as Error 3.
    ULS -
    • w3wp.exe (0x230C) 0x2344 Excel Services Application Excel Calculation Services 7mor Medium TrustedLocationCacheNode.Find: Found a uri that does not belong to any defined trusted location: https://brismith8100.redmond.corp.microsoft.com/PWA/ProjectBICenter/Sample%20Reports/English%20(United%20States)/SimpleProjectsList.xlsx
    • w3wp.exe (0x230C) 0x2344 SharePoint Server Logging Correlation Data xmnv Medium Result=Id=OpenWorkbookLocationNotTrusted; Microsoft.Office.Excel.Server.CalculationServer.FileOpenException: This workbook cannot be opened because it is not stored in an Excel Services Application trusted location. at Microsoft.Office.Excel.Server.CalculationServer.Session.OpenWorkbookAsync(AsyncHandler`1 callback, Object userState, Request request, Uri url, Boolean loadedOnDemand)…
    • w3wp.exe (0x230C) 0x2344 Excel Services Application Excel Calculation Services ech0 Medium ExcelService.PostProcessRequest: web method: OpenWorkbook, got exception Id=OpenWorkbookLocationNotTrusted; Microsoft.Office.Excel.Server.CalculationServer.FileOpenException: This workbook cannot be opened because it is not stored in an Excel Services Application trusted location. at Microsoft.Office.Excel.Server.CalculationServer.Session.OpenWorkbookAsync(AsyncHandler`1 callback, Object userState, Request request, Uri url, Boolean loadedOnDemand)…
    • w3wp.exe (0x230C) 0x2344 Excel Services Application Excel Calculation Services 2017 Information Failed to load 'https://brismith8100.redmond.corp.microsoft.com/PWA/ProjectBICenter/Sample%20Reports/English%20(United%20States)/SimpleProjectsList.xlsx' with error: 'The workbook is not in a trusted location.'. [Session stuff here...]
  • Brian Smith's Microsoft Project Support Blog

    RESOLVED! ActiveX Component Can't Create Object: Cube build failure against SQL Server 2005 Analysis Services

    • 20 Comments

    The original posting is still repeated below to help with other's troubleshooting.  I uninstalled the client tools to double check if the original instructions were OK.  It turned out that I didn't have the SQL Server 2005 Management Objects installed - even though when I tried loading it again it told me I hade a "higher version". (It also didn't show in Add/Remove in control panel - my clue to home in on this component). 

    To find out more about this "higher version" message I re-ran the msi with the /l* <logfilename> parameters to get a more complete log file.  This identified a component that was triggering the "higher version" message.  So I searched the registry for this GUID and identified it as the SQL Server Reporting Services 2005 Add-in for Microsoft SharePoint Technologies - which is a neat add-in to get SSRS report libraries in SharePoint and is a SQL Server 2005 SP2 feature.  I uninstalled this add-in and could then load the Management Objects.  I re-applied SP2 and then I could build cubes!

    The specific thing that identifies this issue is that in Process Monitor I found that the system could not find the key HKCR\CLSID\{554BBCA3-925F-4797-9460-2421A8CD7030} - I guess that the client tools has the bits I needed - as well as the Management Objects.

    I then re-loaded the SharePoint add-in and it said a lower version was present; did I want to upgrade - so somehow these two components are tied together.

    UPDATE - re-installing the add-in removed the key again and cube building breaks!  Remove it and add back the Management Objects and all is OK.  I'll search for a workaround that enables both to coexist...

    ORIGINAL POSTING BEGINS HERE...

    I've been working on my own servers and testing cube builds to remote servers this week, and one of the combinations of servers gave me this error - ActiveX Component Can't Create Object.  I quickly fell in to the "one error - one solution" trap and assumed I hadn't installed the components required - so re-visited the KB and checked.  They were all apparently there.  So I compared to a running system and ran Process Monitor to try and catch the differences.  It seemed the first failure point was trying to find Microsoft.AnalysisServices.DLL and the working server had this as it also had the full install of Analysis Services 2005 - even though for my tests I was building a cube on a remote server.

    The long and the short is that I then loaded the client tools for SQL Server 2005 on my application server and it all started working.  Not sure at this stage if it came from the management tools, connectivity tools, SDK or the legacy components - but I will have a look around next week and see what more detail I can find.  I'm not keen on giving workarounds like loading a set of stuff you don't really need - but if it gets someone out of a hole...

    One of the purposes of my work is to get a new KB together that serves for Project Server 2007 the way that KB 921116 serves for 2003.  We usually refer people to this KB even for 2007 - but the hotfix part of the KB is irrelevant for 2007 - so it can get confusing. I'd like to get to the bottom of this particular issue and cover as many failure modes as possible.

    If you haven't used Process Monitor then it is a cool tool.  A combination of filemon and regmon that shows all file and registry access and you can track what accounts are doing what.

    Technorati Tags: Project Server 2007

  • Brian Smith's Microsoft Project Support Blog

    Failure Audit message in SQL Server - Event ID: 18456 every minute?

    • 10 Comments

    This is a event log error I have seen in Project Server 2007 on various farms going right back to the Beta and I finally found some time to track it down.  It didn't seem to be breaking anything on my server, but made it difficult to read the logs and see other "important" stuff.  This is the error:

    Event Type:    Failure Audit
    Event Source:    MSSQLSERVER
    Event Category:    (4)
    Event ID:    18456
    Date:        1/17/2008
    Time:        1:29:00 PM
    User:        DOMAIN\User
    Computer:    SERVERNAME
    Description:
    Login failed for user 'DOMAIN\user'. [CLIENT: <local machine>]

    I did a SQL Profiler trace to see where it was coming from and discovered the cause was a SQL Server Agent job called SharedServices_DB_Job_DeleteExpiredSessions that was running every minute.  The reason for the failure was that I did not have a SharedServices_DB on that server.  I did once - but my test server gets changed around a fair bit and this was a remnant that didn't get cleaned up. Not sure if it would normally get removed and if I did something bad which left it hanging around. 

    This could also happen with Microsoft Office SharePoint Server 2007 even if Project isn't installed as it relates to the Shared Services Provider.  To disable the job you can go to SQL Management Studio, and connect to your database engine, then expand SQL Server Agent - select the Disable option. 

    image

    There will likely be other valid jobs there too - for your real SharedServices databases that still exist.  The bad one will show that it has failed when last executed if you look in the Job Activity Monitor.

    image

    Not a big problem - but at least disabling will keep the logs looking clean, and will save a few CPU cycles for some real work.

    Technorati Tags: Project Server 2007

  • Brian Smith's Microsoft Project Support Blog

    Setting custom field values using the PSI

    • 27 Comments

    We don't seem to have done a good job in educating our customers on this topic, so thought I'd put this out there at least as a starting point and I'm sure we can also try and get better samples in the SDK. 

    One of the tricky things with setting custom fields for any entity (Project, Resource or Task - but also assignment or timesheet) is that you cannot always just call up the dataset, navigate to the custom field row and set the value.  In many cases the row may not yet exist, so you need to add it, then set all the required properties and then update back through the PSI.

    As an example if you create a project through the PSI it will have no project level custom field rows created by default, (except ones based on Lookup Tables with a default value forced) and any tasks will only have the "Health" special custom field set, and again- any with Lookup Table defaults. If you then open in Project Professional and just save again, you will then have added any calculated fields at the project level, along with any calculated fields at the task level.  Also at this stage the project summary task (Task 0) is populated with any task level fields with roll up to summary level set.

    As my server does not have all the possible combinations of fields I may be missing something here - but ProjTool is a great way to see just what is in the dataset at any time.

    So whenever you are setting custom fields first see if the row already exists - and if not you will need to add a customfieldrow to whatever dataset you are working with.  Then the next step is to set the appropriate properties - which will depend on the type of field you are setting (defined by FIELD_TYPE_ENUM), and the dataset you are adding it to.  For instance a ProjectCustomFieldsRow will need the PROJ_UID.  A TaskCustomFieldsRow will need the PROJ_UID and the TASK_UID.  A Timesheet CustomFieldsRow will need the TS_UID to identify the timesheet and a TS_LINE_UID to identify the specific line.  Then for any row you need to set a new GUID for the CUSTOM_FIELD_UID, the MD_PROP_UID and/or the MD_PROP_ID to identify the specific custom field and the actual value you want to set in the appropriate property as detailed in the following table.

    Field Type ENUM Value Description Set field value in:
    COST 9 Value in 1/COST_MULTIPLIER dollars NUM_VALUE
    DATE 4 A date value. HIWORD contains days off set from 1/1/84. LOWORD contains minute off-set, ranging from 0 to 1440, from 12:00 A.M. (midnight) DATE_VALUE
    DURATION
    6 Value in 1/DUR_MULTIPLIER minutes DUR_VALUE and DUR_FMT
    TEXT 21 A string value TEXT_VALUE
    FLAG 17 Index into yes/no string table FLAG_VALUE
    NUMBER 15 A number value NUM_VALUE
    (Lookup Table)   GUID CODE_VALUE

    FINISHDATE is also listed in the SDK but is not applicable to these custom fields.  Lookup Table isn't specifically a custom field type - but I added it for completeness.  If the custom field is based on a Lookup Table then the LT_STRUCT_UID of the specific entry in the Lookup Table is entered in the CODE_VALUE property.  For those that accept multiple values there will be a row with each CODE_VALUE - not a single row with multiple CODE_VALUES.

    Another property you will come across in the datasets is the INDICATOR_VALUE - which will hold the enumeration value for the indicator to be displayed based on the custom field settings. 

    No code sample just yet on this - but I will try and come up with some shortly.  I am looking into a support incident where values are being set for summary tasks (or rather they are not) - these are likely to be read-only if "roll-up" is set, so be aware of that.

    *** Update *** Finally got a sample together - hope it is worth the wait - http://blogs.msdn.com/b/brismith/archive/2010/08/25/project-server-adding-custom-fields-to-projects-and-tasks-using-the-psi.aspx 

    The ProjCFDlg.cs sample in ProjTool and the Add Project Custom Field option on the Project Details pane of ProjTool is a great place to look for an example that covers this well.

    Other things to be aware of are that constraints applied to the custom fields will need to be adhered to.  If the custom field only allows selection of codes with no subordinate value (leaf nodes) then the PSI cannot over-ride this. 

Page 2 of 93 (461 items) 12345»