#ProjectOnline Project Ideas list view with Project GUID #Office365 #SharePoint #JavaScript #jQuery

July 22, 2016 at 9:14 pm | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, Reporting | Leave a comment
Tags: , , , , , ,

For those of you that are familiar with the project ideas lists in Microsoft’s PPM PWA application you might want to skip this first paragraph. Any list you create in the PWA site collection at the top level you will see a button Create Projects, if you are not familiar with this feature take a look at this blog post: https://pwmather.wordpress.com/2012/12/13/creating-and-adding-projects-to-projectserver-2013-ps2013-sp2013-office365-part-1/.

The purpose of this post isn’t to revisit that feature, this post covers getting access to some of the data on that list that is hidden from the views and creating an enhanced view for the Project Ideas type list.

If you look at the view details, you can see below the columns I can add to this view:

image

Yet if I look at the columns available on this list using the REST API I see a few more, a key one being MSPWAPROJUID as seen below:

image

This column gets updated with the Project GUID when you create a project from the list. As this is a hidden column it can’t be added to any views which is a shame as that would be very useful. I thought ok, simple, I will just update the field hidden property false then add it to the view. Whilst that change is very simple with a few lines of code this appears to break the column getting updated with the Project GUID so I don’t recommend doing that currently. If I found a way to get this working I will create a post. So as that didn’t work that lead me on to the next idea, lets build my own view on a page. This can be seen below:

image

I have made the Item Title clickable to open the list item in a new window and where an idea item has been converted into a project, the Project GUID is a clickable link to the view the project. This opens the schedule Project Detail Page. The columns are sortable, by default it is sorted by Item ID, to change this use the arrows in the table column heading. As you can see, I don’t have much data on my test system and only one idea has been created into a project.

I have made the code available on the Microsoft code gallery, it can be downloaded here: https://gallery.technet.microsoft.com/Display-Online-ideas-list-c59c8cc7

To get the script to work for your PWA instance in Project Online you will need to update the listName variable to set the correct list title for your project ideas list:

image

Replace ProjectIdeas with the correct title. Save the JavaScript file in a document library in the PWA site collection then create a web part page in the PWA site collection, for example I created the page and uploaded the script to the Shared Documents library:

image

On the new web page page I added a content editor web part and linked to the JavaScript file:

image

Click Apply then OK and the data should load if the listName variable is correct. You can then add a link to the new page from the PWA quick launch if needed.

A nice simple solution that provides more functionality than the standard SharePoint view.

#ProjectOnline #Excel #PowerQuery Report #Office365 #PS2013 #BI #OData

January 20, 2015 at 1:21 pm | Posted in Administration, Configuration, Customisation, Functionality, Information, Reporting | 3 Comments
Tags: , , , , , ,

Something I have been meaning to blog about for a while now is Power Query. There is nothing revolutionary in this post but hopefully it will give the readers some idea of how powerful Excel Power Query can be if they have not seen / used it before. We will only touch the surface here in this post but there is plenty of information available already.

So firstly, what is Power Query. In short, Power Query is an add in for Excel that enables you to work with many different data sources and transform that data as required. An intro can be seen here. Power Query is formula based but this is different formula syntax to what you use in Excel. The Power Query formulas are based on what was known a “M” language, an intro to the formulas can be found here.

Now we will look at creating a simple Excel Power Query report for Project Online using the OData API. The first report we will create will be a simple report that allows you to dynamically filter the data based on a text value in a cell. The dynamic filtering is useful when you are looking at large amounts of data such as time phased assignments in Project Online. The example we create below doesn’t really benefit from the dynamic filtering as the data set is small but this is only an example to show you how this works.

Get your Project ODATA url, for this example and am using the following:

https://<URL TENANT>/sites/pwa/_api/ProjectData/Projects()?$select=ProjectName,ProjectOwnerName,ProjectWork,ProjectCost,ProjectDuration,ProjectPercentCompleted,ProjectDescription,EnterpriseProjectTypeName

Launch Excel and click the Power Query tab then From Other Sources > From OData feed:

image

Paste in the URL:

image

Click OK. You will then be presented with an authentication window. Select “Organizational account” from the left hand side navigation and click Sign In. In the new window that appears enter the credentials for an account that has access to the ProjectData API.

Once authenticated you will see the Power Query Preview window load with the data:

image

You can see the formula used in the formula bar:

image

If you can’t see the formula bar, click the View tab and check the Formula bar check box:

image

We now have our Project Online data in Power Query ready to be used as a data source. In this example I have already selected the columns I wanted, but if you just used the Projects feed with no select (..pwa/_api/ProjectData/Projects()) you would see all of the columns displayed. At that point you can then easily chose which columns you would like in your data source by selecting the columns you don’t need then right click and chose Remove Columns.

image

Then you can see those columns have been removed. The formula bar has been updated with the latest steps:

image

You will also see another step appear in the applied steps, see it has the “Removed Columns” step:

image

At any point in time you can switch back to a previous step or even delete a step. If I click back on the source step I see the original data set with the two columns I deleted:

image

For the purpose of this post I will delete the “Removed Columns” step but before I do I want to show the Advanced Editor screen. This is available from the View tab:

image

This editor enables you to type the query yourself rather than using the UI menus. Some things you might want to do might not be possible from the UI and you are required to type / update the query. I will now delete the “Removed Columns” steps and you can see the query has been updated:

image

I can manually update the query update the data set, for example I can manually update the query to remove columns:

image

You will then see the data update and an applied step appear:

image

Now I am happy with my simple data set, but before I do anything I will update the query name to “Projects”:

image

Now chose “Close & Load To…” from the Home tab:

image

Then chose “Only create the connection” also check the check box to load the data to the data model:

image

You will then see the Excel sheet with the Workbook Queries window that allows you to preview the data:

image

Before we create the Excel report, I want be able to filter the data based on the Project Owner. Next insert a table like below:

image

Enter any Project Owner name for now, I used he MOD Administrator in this example as that user is the Project Owner for a few projects. Give the table a name, in this example I called it ProjectOwnerTable.

Now click the Power Query tab then “From Table”:

image

The Power Query editor will load with the following:

image

Switch to the advanced editor to see the query:

image

Update the query to change the type and select the record:

image

The record will then be displayed:

image

Now click the “Close & Load To…” from the Home tab and chose the “Only create connection” and check the check box to add the data to the data model.

Now load the first Projects query that was created in Power Query and switch to the advanced editor:

image

This needs to be modified to filter for the Project Owner in the new table that was created. See the updated query below:

image

You will need to enable the data in privacy option:

image

image

Click Close and load to save the changes in the Power Query editor and you will see the Excel worksheet:

image

Now you can create the Excel report, for this I will add a Pivot Table using an External Data source then selecting my data model:

image

Then update the pivot table with the fields:

image

Change the layout etc.

image

image

This is filtered to the projects where the owner is MOD Administrator, if I want to see other projects, update the Project Owner on row 2 and refresh the data:

image

image

A very quick and simple intro the Power Query for Excel with Project Online data using the ProjectData Odata API.

I hope to give more examples in the future.

Free #ProjectOnline /ProjectServer advice sessions @ #ProjChallenge #PPM #Microsoft #Project #PMOT

September 29, 2014 at 4:45 pm | Posted in Administration, Functionality, Information, Personal | Leave a comment
Tags: , , , , , ,

Just a quick email to let you know that I will be at Project Challenge in October 2014 at Olympia, London. I will be there over the two days and available for free 20 minute sessions where general advice can been given for common business or technical issues relating to the Microsoft PPM toolset.

For details see the following link:

http://www.cps.co.uk/Who-Are-We/News/Pages/CPS-Invite-you-to-free-‘Ask-the-Expert-Sessions’-at-Project-Challenge.aspx

#ProjectServer #PowerView report in #Excel 2013 #PS2010 #PS2013 #Office2013

March 1, 2013 at 11:51 pm | Posted in Administration, Configuration, Functionality, Reporting, T-SQL | 2 Comments
Tags: , , , , , , ,

This post will take a brief look at creating a map view of Project Server data – this does assume you tag your projects with a location!

For the purpose of this post I will use the example Excel file shown below – this pulls data from one of my test Project Server PWA instances, hence the project names!

image

In Excel 2013, click Insert > Power View Reports:

image

You will now see a Power View report:

image

To create a map with the projects plotted in the correct location by cost, see the steps below.

On the design tab, click Map and you will see the following:

image

Now click the map and modify the Power View fields shown below:

image

For this example, add ProjectCost to the size property, add Project Locations to the Locations property and set the colour property to ProjectName:

image

Increase the size of the map and add a title:

image

You can hover over the data circles and a tooltip will appear with the project details:

image

The data can be refreshed and the map updates.

A quick and simple report to show projects by location.

Create and publish a Project in #ProjectServer via #PowerShell #PS2010 #MSProject #SP2010

January 17, 2012 at 7:53 pm | Posted in Configuration, Customisation, Functionality, PowerShell, PSI | 6 Comments
Tags: , , ,

Following on from a recent post on Project Server PSI PowerShell scripts, I thought of a nice slightly more advanced example. The script below will create a new project using the specified project template.

$ProjName = Read-Host -Prompt "Enter the Name of the Project"
Write-host "The Project is called $ProjName"
$svcPSProxy = New-WebServiceProxy -uri "http://vm353/pwatest/_vti_bin/PSI/Project.asmx?wsdl&quot; -useDefaultCredential
$NewProjGUID = $svcPSProxy.CreateProjectFromTemplate("01dbd28a-fcb6-4ccf-bb42-645982f48cf3", $ProjName)
$G = [System.Guid]::NewGuid()
$svcPSProxy = New-WebServiceProxy -uri "http://vm353/pwatest/_vti_bin/PSI/Project.asmx?wsdl&quot; -useDefaultCredential
$svcPSProxy.QueuePublish("$G", $NewProjGUID, "true","")
Write-host "********* $ProjName has now been created *********"

Below is a walkthrough:

I have the script saved as CreateProject.ps1 on my desktop:

image

Hit Enter to execute the script:

image

You are prompted for the Project name, give the new Project a name and hit Enter:

image

A message will then appear with the chosen Project Name:

image

The script will then create a new project using the CreateProjectFromTemplate method with the project template and project name specified. In the next step it generates a new GUID ready to be used for the project publish job GUID. The script will then use the QueuePublish method and pass in the new GUID for the publish job, the GUID of the new project, specifies a full publish and creates a project site:

image

Now you can see the project in the Project Centre:

image

And the Project Site:

image

A basic example of creating a project with a 5 or 6 line PowerShell script!

#ProjectServer 2010 PSI #PowerShell examples #PS2010 #MSProject #SP2010

January 12, 2012 at 10:03 pm | Posted in Customisation, Functionality, PowerShell, PSI | 9 Comments
Tags: , , ,

Following from a post I wrote mid last year on Project Server data export using the PSI and PowerShell I thought I would create a new post to show further simple examples of getting Project data via the PSI using PowerShell (no coding!).

Firstly I will just explain each part of a simple script using the example below:

The first line adds the PWA instance URL to the pwaUrl variable:

$pwaUrl = “http://vm353/pwatest”

The second line concatenates the PWA URL (stored in the pwaUrl variable) and the web service URL and adds both of these to the svcPSUrl variable:

$svcPSUrl = $pwaUrl + "/_vti_bin/PSI/Project.asmx?wsdl"

The next line calls the Get-Credential cmdlet to specify a user name and password and adds the credentials to the c variable:

$c = Get-Credential

The fourth line creates the new web service proxy with the PWA web service URL and the credentials previously entered and adds these details to the svcPSProxy variable:

$svcPSProxy = New-WebServiceProxy -uri $svcPSUrl -credential $c

The last line is used to access and query the web service, the web service details are stored in the svcPSProxy variable, then the ReadProjectList method is called to access the Project details specified in the select statement using the Project property of the ProjectDataSet class:

$svcPSProxy.ReadProjectList().Project | Select Proj_name, Proj_UID, Proj_Type

A shorter version of the same query can be seen below:

$svcPSProxy = New-WebServiceProxy -uri "http://vm353/pwatest/_vti_bin/PSI/Project.asmx?wsdl&quot; -useDefaultCredential
$svcPSProxy.ReadProjectList().Project | Select Proj_name, Proj_UID, Proj_Type

The script above does exactly the same but is structured differently. The PWA web service URL is added directly to the New-WebServiceProxy object and this query will pass through the logged on users credentials rather than having to enter a user name and password by specifying the useDefaultCredential property.

Below are some simple examples.

Read all users and export to a CSV file:

$pwaUrl = "http://vm353/pwatest&quot;
$PSsvcUrl = $pwaUrl + "/_vti_bin/PSI/Resource.asmx?wsdl"
$c = Get-Credential
$svcPSProxy = New-WebServiceProxy -uri $PSsvcUrl -credential $c
$svcPSProxy.ReadUserList("ALL").Resources | Export-CSV C:\Resources.txt -Delimiter "|"

To read a specific resource us the ReadResource method and pass the res_UID to the resourceUID parameter:

$svcPSProxy.ReadResource("2B8767C3-DE3E-4343-8E51-B268697FB10F").Resources

Read Project Team:

$pwaUrl = "http://vm353/pwatest&quot;
$svcProjectUrl = $pwaUrl + "/_vti_bin/PSI/Project.asmx?wsdl"
$c = Get-Credential
$svcProjectProxy = New-WebServiceProxy -uri $svcProjectUrl -credential $c
$svcProjectProxy.ReadProjectTeam("3DC95F6F-60C4-48CB-8DA0-AA28DA6E31FD").ProjectTeam
–will return all fields in the ProjectTeam dataset for the given project  GUID.

To return specified fields add the select command:

$svcProjectProxy.ReadProjectTeam("3DC95F6F-60C4-48CB-8DA0-AA28DA6E31FD").ProjectTeam | Select res_UID, Res_Name — will select limited fields from the dataset, in this case Resource UID and Resource Name.

It is also possible to run certain jobs from PowerShell, in the example below the archive projects job will run when the script is executed:

$pwaUrl = "http://vm353/pwatest&quot;
$svcProjectUrl = $pwaUrl + "/_vti_bin/PSI/Archive.asmx?wsdl"
$c = Get-Credential
$svcProjectProxy = New-WebServiceProxy -uri $svcProjectUrl -credential $c
$svcProjectProxy.QueueArchiveProjects()

The following script will synchronise the PWA users and the SharePoint site membership:

$pwa = "http://vm353/pwatest&quot;
$svcPSUrl = $pwa + "/_vti_bin/PSI/WssInterop.asmx?wsdl"
$c = Get-Credential
$svcPSProxy = New-WebServiceProxy -uri $svcPSUrl -credential $c
$svcPSProxy.SynchronizeMembershipForPwaAppRootSite()

These are just a few simple examples to show how quickly you can access the PSI with no code and not touching Visual Studio!

Details on the Project Server 2010 PSI can be found here:

http://msdn.microsoft.com/en-us/library/ms457477.aspx

Details on PowerShell can be found here:

http://msdn.microsoft.com/en-us/library/windows/desktop/aa973757(v=vs.85).aspx

Unknown error when saving #ProjectServer Security Group #PS2010 #MSProject #SP2010 #EPM

December 22, 2011 at 5:39 pm | Posted in Administration, Configuration, Customisation, Issue, T-SQL | Leave a comment
Tags: , , ,

I came across this issue on my test instance a while back and never bothered to investigate it or fix it, when trying to save the security group the following error was returned:

The group could not be saved due to the following reason(s):

  • An unknown error has occurred.
  • An unknown error has occurred.

image

After some digging around I remember I did some testing with adding custom permissions, 2 in fact which coincided with the number of unknown errors. The Administrators group did originally have both of these custom permissions enabled. After looking down the list of Global permissions I couldn’t see the two custom permissions so I checked the Published database. Luckily I had the SQL queries saved from when I created the two custom permissions so I knew what Name ID to check for in the database tables. The two custom permissions existed in the MSP_WEB_SECURITY_FEATURES_ACTIONS table but not in the MSP_WEB_CONVERSIONS table as seen below:

image

As I thought, my two custom permission names had been removed from the web conversions table, almost certainly after applying an update for Project Server. The fix is quite simple, just add the custom permission names back into the MSP_WEB_CONVERSTIONS table, my example can be seen below:

image

The security group can now be saved without any errors.

Worth noting if you use custom permissions on your Project Server environment, hopefully that will help anyone seeing this issue. Smile

Some #ProjectServer default fields not in the Reporting Database #PS2010 #MSProject #SP2010

December 19, 2011 at 10:10 am | Posted in Administration, Configuration, Functionality, Reporting, T-SQL, Workarounds | Leave a comment
Tags: , , ,

Not all default fields are available in the Project Server Reporting database, for example the Contact and Resume fields. To be able to easily include these fields in reports using direct T-SQL you will need to get the values for these fields in the Project Server Reporting database. The easiest was to achieve this is to create a new custom field that uses a formula and reference the default field. The new custom field will then be available in the reporting database. Below is walkthrough guide using the Contact field to show you what I mean.

Task 1 in the project below has PaulM as the Contact as seen below:

image

To get this value in the Reporting I will create a new Task Level custom field called TaskContact that uses a formula of [Contact] as shown below:

image

Now I need to open and publish the project/s so that the TaskContact custom field pulls in the values from the default Contact field and synchronises these values to the Reporting database when published. You can see below that the Contact PaulM is now available in the MSP_EPMTask_UserView for Task1.

image

Report on Project Server baseline save dates? #ProjectServer #PS2003 #PS2007 #PS2010 #MSProject #EPM

August 20, 2011 at 11:16 pm | Posted in Customisation, Functionality | 2 Comments
Tags: , , , , , ,

Quite often clients want to report on the dates when the baselines were saved. This information is not available out of the box in the databases using T-SQL. This post explains how to get this data into the database so that is accessible using T-SQL in the Reporting database.

Firstly we need to create 11 Project level enterprise custom text fields to store the baseline save dates. For the purpose of this post not all custom fields have been created, the custom fields in this example are Baseline0saveddate, Baseline1savedate and Baseline2savedate. Next add a VBA macro into the Enterprise Global that copies the baseline save dates to the enterprise custom fields created above. The following macro is just an example cut down version to copy the baseline save dates to the custom fields. This cut down version also only includes the first 3 baseline save dates.

Private Sub Project_BeforeSave(ByVal pj As Project)
ActiveProject.ProjectSummaryTask.Baseline0saveddate = ActiveProject.BaselineSavedDate(pjBaseline)
ActiveProject.ProjectSummaryTask.Baseline1saveddate = ActiveProject.BaselineSavedDate(pjBaseline1)
ActiveProject.ProjectSummaryTask.Baseline2saveddate = ActiveProject.BaselineSavedDate(pjBaseline2)
End Sub

The macro above will work for Project 2003, 2007 and 2010.

If a baseline value is not set NA will be set in the associated custom field. Once the macro has been added to the enterprise global, save the changes then close Project Professional. Re-launch Project Professional and open a project that has baselines set. Insert the new Project level custom fields into the view, click Save and you will see either the baseline save date appear in the associated custom field or NA if a baseline has not been set for a particular baseline. Once this project is published this information will be available to report on in the Reporting database. For example, the following T-SQL will give you the baseline save dates based on this post example:

use            PWA_Reporting
select        ProjectName
        ,    Baseline0saveddate
        ,    Baseline1saveddate
        ,    Baseline2saveddate
from        dbo.MSP_EpmProject_UserView

The T-SQL above will work for Project Server 2007 and Project Server 2010.

As mentioned above the VBA code is just as an example and would need to be improved for a production environment.

#ProjectServer 2010 #Excel / Excel Services Report Authentication #PS2010 #EPM #SharePoint #SP2010

July 25, 2011 at 12:07 pm | Posted in Administration, Functionality, Installation | 11 Comments
Tags: , ,

Project Server 2010 uses Excel Services as the primary reporting technology, this post gives an overview on how users authenticate to each data source along with the errors seen if users / accounts don’t have access. There are two different data sources by default, the Reporting database and the OLAP Cube.

Authentication In Excel Client

When creating or editing Excel Services reports the credentials of the logged on user who launched Excel will be used to authenticate the user against the data source. Firstly we will look at the Reporting database reports. To read data from a SQL database the user will need Data Reader (db_datareader) permission against the database, in this case the PWA_Reporting database. The recommended approach from Microsoft is to create a new Active Directory Security Group for Report Authors and add all users who create / edit reports to the this group. This group is then added to the PWA_Reporting database with data reader permissions, this saves adding users directly to the database. Excel will connect directly to the PWA_Reporting database with the logged on users credentials, the logged on user must be a part of the Reporting Authors AD group otherwise they will just see a SQL Server login box in Excel unless they have access directly to the SQL server / database.

image

When editing or creating Excel reports that use the OLAP Cube data, the logged on users credentials are used to authenticate at the data source. The access to the OLAP Cube data is controlled differently to the PWA_Reporting database. To access the OLAP Cube data the logged on user needs to be a member of the ProjectServerViewOlapDataRole against the Cube as shown below:

image

All users that are members of a Project Server security group that allows “View OLAP Data” will be added to the ProjectServerViewOlapDataRole role when the Project Server cube is built. Without the “View OLAP Data” permission allowed, the user will not be able to create or edit OLAP reports in Excel. If the user doesn’t have permission to the cube they will see this login box:

image

Authentication In Excel Services

Viewing Reports in Excel Services will authenticate users using the ProjectServerApplication Secure Store Service App. This is the same for both data sources, when the ProjectServerApplication target application is created there is a members field, this is where you allow users access. Microsoft recommends to create another Active Directory Security group for Report Viewers, then add this group in the members field as shown below:

image

All users who need to view the reports need to be added to the Report Viewers AD group. Once you have created the ProjectServerApplication target app, you then need to set the credentials of the user account that will be used to authenticate against each data source, this is shown below:

image

This account will need data reader access to the PWA_Reporting database and at least the ProjectServerViewOlapDataRole against the OLAP cube. When users refresh the Excel Services reports in the web, firstly it will check that they are a member of the ProjectServerApplication target app then the data source will be accessed using the credentials set for the ProjectServerApplication target app. If the account used for the ProjectServerApplication doesn’t have access to the data sources you will see the errors below:

Excel Services Error:

An error occurred during an attempt to establish a connection to the external data source. The following connections failed to refresh:

image

The same error is seen for each data source apart from the connection name.

ULS log error:

ConnectionManager.GetConnection: Caught an exception: Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException: Exception of type ‘Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException’ was thrown.     at Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInterop.InitConnection()     at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.<>c__DisplayClass1.<CreateConnection>b__0()     at Microsoft.Office.Excel.Server.Credentials.TryExecuteImpersonated(WindowsIdentity wi, ExecuteImpersonatedMethod method, Boolean dispose)     at Microsoft.Office.Excel.Server.Credentials.TryExecuteImpersonated.

Next Page »

Blog at WordPress.com.
Entries and comments feeds.