#ProjectOnline #PowerApps example #Office365 #PPM #PMOT #Apps #MSProject #SharePoint Part2

February 24, 2018 at 10:17 pm | Posted in Add-on, Administration, App, Configuration, Customisation, Functionality, Information, Mobile | Leave a comment
Tags: , , , , ,

In part 2 of this series we look at continuing with the example PowerApp for Project Online for project details and project navigation. For those of you that missed part 1, a link to the post can be found below:

https://pwmather.wordpress.com/2018/02/21/projectonline-powerapps-example-office365-ppm-pmot-apps-msproject-part1/

In part 2 we will update the detail screen to display the data we want and include links to PWA for the associated project. If you are following, open the app you started in part 1:

image

Using the tree view on the left, click DetailScreen1 to load the detail screen or click the chevron next to a project from the browse screen:

SNAGHTMLad72abf

Firstly I updated the screen label to “Project Details” and deleted the delete and edit icons:

SNAGHTMLadf40c1

Next two buttons were added to the top bar, one for the Site and one for the Tasks. The Site button will launch DataCardValue8 which in my case is the Project Site URL as seen below:

SNAGHTMLaf5ceae

The Tasks button will launch the Project Schedule PDP but concatenating the PWA URL for the schedule PDP (or whatever PDP you want to link to) then using the ProjectID, in my case DataCardValue2, from the detail screen for the projuid URL parameter:

SNAGHTMLafa7b75

The buttons were added to the screen using the insert menu:

image

As you can see, there are lots of options on the insert ribbon! Other quick links could easily be added such as Issues, Risks, New Risk etc. That is it for the detail view, we have some project details and two buttons that link out to PWA for the associated project:

SNAGHTMLb051b8d

Just for fun I also added a new screen in for a chart. I inserted a bar chart and created a project count by progress chart:

image

For the Items, I entered the following formula to count the number of projects Completed, Not Starter or In Progress:

image

The formula is below for my data, ProjectData is the name of my SharePoint list and Progress is the column on my SharePoint list that contains the % complete value for each project:

Table(
    {Column: "Completed", Value: CountIf(ProjectData, Progress = 100)
    },
    {Column: "Not Started", Value: CountIf(ProjectData, Progress = 0)
    },
    {Column: "In Progress", Value: CountIf(ProjectData, Progress <> 0 && Progress <> 100)
    }
)

Then set the item colours:

image

I then added the back arrow to navigate back to the main browse screen:

image

The final change was to add an icon to the main browse screen that enabled navigation to the chart screen:

image

To preview your app in PowerApps, press F5:

Main screen: list of projects with search and sort using project name, link for each project to project detail screen, access to chart screen, refresh the data and link to create a new project in Project Online

image

Details screen: project details with button links to the project site or schedule PDP in Project Online PWA and a back button to the main screen

SNAGHTMLb7913d8

Chart screen: bar chart display project count by progress for Completed, Not Started and In Progress with a back button to the main screen

image

Once you are happy with your app you can share it to your organisation. Before you do, access the App Settings view and give it a name, set the icon and description:

image 

Then click Save and save it to your organisations PowerApps environment then share the app:

SNAGHTMLb8e0f8c

For details on Saving, publishing and sharing your apps see the links here: https://docs.microsoft.com/en-us/powerapps/save-publish-app & https://docs.microsoft.com/en-us/powerapps/share-app Users can then access your app using the browser, PowerApps Studio or the PowerApps mobile app!

Hopefully that has given you enough to get started or given you some ideas for Project Online PowerApps. There are so many features to PowerApps, they are very powerful! For getting started guides on PowerApps start here: https://docs.microsoft.com/en-us/powerapps/getting-started

Have fun!

Advertisements

#ProjectOnline #PowerApps example #Office365 #PPM #PMOT #Apps #MSProject Part1

February 21, 2018 at 11:47 pm | Posted in Add-on, Administration, App, Configuration, Customisation, Functionality, Information, Mobile | 1 Comment
Tags: , , , ,

PowerApps aren’t a new feature in Office 365 but not one that I have blogged about before, mainly due to the lack of native support for Project Online, but I have created apps before using PowerApps  – it’s an awesome application! PowerApps provide a great way for users to create powerful business apps without writing code. You can use the same connectors that have use for Flow etc. so there is great support for SharePoint Online but no so much for Project Online. To get around this, the easiest approach is to have your Project Online data in a SharePoint list, I have covered a few examples with code samples before:

https://pwmather.wordpress.com/2018/01/27/projectonline-project-level-html-fields-to-a-sharepoint-list-powershell-ppm-office365/

https://pwmather.wordpress.com/2016/08/26/projectonline-data-capture-snapshot-capability-with-powershell-sharepoint-office365-ppm-bi/

In this series of posts, we will create a simple navigation type app for Project Online using PowerApps. In part 1 we look at getting started and creating / updating the browse / home screen. To do this we will need the Project Online data in a SharePoint list using a similar process as the examples above. This post assumes you already have your Project Online data available in a SharePoint Online list and a process to keep this updated as your Project Online changes, similar to the list below:

SNAGHTML6eb4d2

Access PowerApps from the Office 365 app launcher:

image

This will launch PowerApps:

image

If you haven’t used SharePoint Online in Flow or PowerApps before, firstly you will need to click Connections > New Connections > Type SharePoint in the Search bar and add the SharePoint connection:

image

Once you have the connection you can create the app. To create the PowerApp you can either use the web based PowerApps Studio that can be accessed on the Apps page:

image

Or there is an App available in the Microsoft Store:

image

For the purpose of this blog post I will be using the Windows 10 App but the steps are the same.

Once the App launches, you can Open existing apps:

image

Or create a new one, click the New button:

image

From here you can create an app starting with your data, start from a blank canvas or start from a template. The templates options will present you with template previews to select from:

image

For the purpose of the this blog post and to keep the blog post shorter, we will start with our SharePoint data, if this is your first PowerApp this might be the best option to start with until you’re familiar with PowerApps:

image

Type the URL of the site that contains the list:

SNAGHTML787fc7

Press Go then select the correct list and press Connect, in this example it is ProjectData:

image

PowerApps will then build a working app for you based on the data in your SharePoint list:

image

Once finished, home / browse screen is displayed:

image

Clicking the chevron for a project loads the detail screen:

image

That might be good enough for some scenarios – pretty awesome given it was a click of a button! As mentioned, we will look to make a navigation type app that displays some high level project information so we will make some minor tweaks to the base app PowerApps kindly created for us.

Clicking on different areas on the canvas will enable different settings or you can click the elements using the left hand tree view. The first item we will change is the ProjectData label which by default takes the source list name. This property is the LblAppName1:

image

Moving down the screen, we want to change the search feature so that the search box and sorting work on the Project Name. To do this, select BrowseGallery1 from the left hand tree view, then click Advanced, finally update the Data items. Replace ComplianceAssetId with Title as seen below in two places:

image

The search box and sort icon will now work on the Project Name – in our case this is Title on the source SharePoint list.

Next up we want to see Project Name and Project Owner on the project summary section. Using the left hand tree view, click Title1 then access the Advanced menu. In the DATA section under the Text property, update this to “ThisItem.Title”:

image

Notice the Project Name now appears. Repeat for Subtitle1 and select a different project property or multiple properties, I will set the Project Owner and % complete:

image

I will also change the Body1 property to display the Project Start and Finish Dates:

image

The final change for the browse screen in part 1 of this blog post is to change the IconNewItem1, this is the + symbol in the top right corner. In the default app, this will load the edit screen to create a new list item, for this example we will point this to load the create project page in Project Online. Select the IconNewItem1 from the left hand tree view, replace the OnSelect “NewForm(EditForm1;Navigate(EditScreen1, None) with Launch(https://PWASiteURL/_layouts/15/PWA/Wizards/CreateProject.aspx) and update the tooltip as seen below:

SNAGHTMLb94801

The browse screen should look something like this now:

image

With the ability to search and sort on Project Name:

image

Worth noting is that this project data will not be security trimmed etc.

We have barely touched the surface of PowerApps but that is it for Part 1. In Part 2 we look at updating the detail view and adding in links to navigate to the project in PWA.

#MicrosoftForms and #MicrosoftFlow for #ProjectOnline #PPM project reviews #O365 #SharePoint #PMOT

February 11, 2018 at 10:47 pm | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information | Leave a comment
Tags: , , , ,

Most projects at some point have some kind of review such as a stakeholder review or project closure review. As Project Online is built on SharePoint there are many ways that this can be achieved but in this blog post we will look at making use of Microsoft Forms to design those reviews, Microsoft Flow to capture the responses for the reviews and SharePoint Online to store the data in a list in the Project Web App site collection. As Project Online is built in the Microsoft Office 365 cloud there are lots of great features that you can make use of, Forms seemed a good fit for a project review.

Firstly access https://forms.office.com/ to get started with your review form. Please note Forms is currently in Preview. Click the New Form button:

image

This will load the form designer:

image

You can use the Theme button to select a theme or upload your own:

image

Enter a form title and description:

image

Then click the Add question button:

image

Select the type of response your question requires, notice the two additional options on the ellipsis at the end. Depending on the type of question selected, that will determine the control used on the form. Design the form as required, for this blog post, here is my very simple form:

image 

Now on my Project Online PWA site in SharePoint Online I have created a list that contains columns for each of my questions:

image

The next step is to access Microsoft Flow and click create from template:

image

This blog post assumes you have already set up the connection to your SharePoint Online tenant in Microsoft Flow.

Search for forms and the existing templates for Forms will be loaded:

image

For this example we just need the first one “Record form responses in SharePoint”, click Continue:

SNAGHTMLfdf7de8

Give the Flow a name then select the correct form in the “When a new response is submitted” Flow action:

image

Then again in the “Get response details” Flow action:

image

Then select / type / paste the SharePoint site URL and select the list created in the “Create item” Flow action:

SNAGHTMLfe28386

Then map the responses from the form to the SharePoint list columns in the “Create item” Flow action:

SNAGHTMLfecca34

Save the Flow.

Now back in Forms, access the Form then click the ellipsis then Settings:

image

On the form settings, set who can respond to the form, in this example I only want people in my organisation to response and I set it to record their name:

image

Now click the Share button to get the form URL to send to the relevant users or add in the Project Web App site. For example, if you were creating a project closure review form or stakeholder review form you might add this to a certain Project Detail Page that is only visible at a certain stage of the project lifecycle.

Once users respond you will see the flow runs in the run history and you will also see the responses on the target SharePoint list. See some example responses below:

image 

Do be aware of the SharePoint list access – you might want to restrict access to this list!

#ProjectOnline Project level #HTML fields to a #SharePoint list #PowerShell #PPM #Office365

January 27, 2018 at 10:01 pm | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, PowerShell, Reporting | 1 Comment
Tags: , , , ,

Following on from my previous mini series of posts for including the HTML formatting in Project Online Power BI reports, this post is a supporting blog post for the PowerShell script I used in the 3rd post. For those that missed that mini series of posts, the links are below:

Part 1: https://pwmather.wordpress.com/2018/01/01/projectonline-powerbi-report-include-html-formatting-ppm-pmot-powerquery-odata-rest-part-1/

Part 2: https://pwmather.wordpress.com/2018/01/03/projectonline-powerbi-report-include-html-formatting-ppm-pmot-powerquery-odata-rest-part-2/

Part 3: https://pwmather.wordpress.com/2018/01/16/projectonline-powerbi-report-include-html-formatting-ppm-pmot-powerquery-odata-rest-part-3/

This blog post is the supporting blog post for the script sample published to the Microsoft Script Gallery: https://gallery.technet.microsoft.com/Online-Level-HTML-fields-5dc31a38

This PowerShell script will use the Project Reporting OData API to get all of the published projects in the Project Online PWA Site Collection, then for each project it will get the project level multiple lines of text fields that include the HTML from the REST API and then create a list item on the specified SharePoint list. The user setting up the script will need to make some changes to the script , this is covered in the blog post.

The account used will need access to the OData API in PWA, at least full read access to all projects and contribute access to the target SharePoint list. The SharePoint list will also need to be created beforehand with the required columns.

To get the script to work you will need to reference the DLL as seen in the image below:

image

This can be installed from the SharePoint Online Client components / management shell. I used the dll from the SharePoint Online Management Shell in this example.

Firstly decide what project level multiple lines of text fields you want to include, this will determine the list column requirements. Then create the SharePoint list in the PWA site collection with the required columns, for this example I created a list called ProjectMutliLineFields with the columns below:

image

I used the default Title field for the Project Name, ProjectId for the Project GUID then I created four multiple lines of text columns for my example project multiple lines of text fields. Set up the list and columns as required then update line 45 in the sample script to change the select query to include the correct project fields you need:

$url = $PWAInstanceURL + "/_api/ProjectServer/Projects(guid'$projectID')/IncludeCustomFields?`$Select=Name,Id,Custom_x005f_4d0daaaba6ade21193f900155d153dd4,Custom_x005f_3f9c814ca2ade21193f900155d153dd4,Custom_x005f_a801708ea5ade21193f900155d153dd4,Custom_x005f_70534c6aa2ade21193f900155d153dd4"

You will at least need to change all of the custom field GUIDs to be the correct GUIDs for your project fields. If you are unsure on how to get the correct custom field GUIDs, see post 2 in the HTML reporting series.

You will then need to update the list item creation part of the sample script to map to the correct SharePoint column names you created and the project fields:

image

Also ensure the variables have been updated correctly, placeholder values seen below:

image

Save and run the PowerShell script (fully test on a non-production PWA site collection before Production) to ensure the data is captured correctly in the target SharePoint list. This script could be run manually on demand or on schedule using a scheduled task if running on a server or a scheduled Azure Function or other methods.

Once the script is run you will see the data in the SharePoint list (data from our sales demo instance):

image

Whilst the purpose of this script was to enable us to get the data easily in Power BI in a such a way that supported refreshing in the Power BI Service, as you can see in the screen shot above, this list includes all of the HTML formatting in a central view – something you can’t get in a PWA Project Center view! Do keep in mind that this SharePoint list would not be security trimmed like a Project Center view though, so you might want to restrict access to the SharePoint list depending on your data / security policies for your PPM data.

Running the script multiple times will create multiple items for each project so you might want to set up grouping on the view or update the script to modify the SharePoint list item with the updated data so that you only have one list item per project.

The script is provided "As is" with no warranties etc.

#ProjectOnline #PowerBI Report – Include #HTML formatting #PPM #PMOT #PowerQuery #OData #REST Part 3

January 16, 2018 at 8:18 pm | Posted in Configuration, Customisation, Functionality, Information, Reporting | 3 Comments
Tags: , , , , , , ,

Following on from my 2nd post in this mini series on reporting including HTML formatting in Power BI, in this post we will look at a couple more options that will refresh in the Power BI App Service. If you missed the previous posts, the links are below:

Part 1: https://pwmather.wordpress.com/2018/01/01/projectonline-powerbi-report-include-html-formatting-ppm-pmot-powerquery-odata-rest-part-1/

Part 2: https://pwmather.wordpress.com/2018/01/03/projectonline-powerbi-report-include-html-formatting-ppm-pmot-powerquery-odata-rest-part-2/

The options we will look at in this post require a process to get the Project HTML data into a source that can be queried from Power BI with one call. Firstly I will demonstrate a simple PowerShell script that will get the data and write this to a SharePoint list on the PWA site. This is process is very similar to a Project Online snapshot solution starter script I published back in August 2016: https://pwmather.wordpress.com/2016/08/26/projectonline-data-capture-snapshot-capability-with-powershell-sharepoint-office365-ppm-bi/ Once you have a script running to capture the data on the defined scheduled you will see something similar to the screen shot seen below:

image

Here you can see my process has run twice, once back in August when I first wrote this script and just now when I ran it again. As this is based on sales demo data, you can see in the two expanded examples the data has not changed but in a real world usage I’d like to think the data would have changed / been updated! Having the data in one list enables a SharePoint OData call from Power BI, as I have included the ProjectId in the data on the list, this can easily be joined with the data from the main Project OData Reporting API. As this data is in a SharePoint list you might need to consider the user permissions / access to the list. If this was running on a schedule, either from a Windows Scheduled task if on-prem or maybe a scheduled Azure Function if you wanted to make use of Azure PaaS, set the schedule to run before the reports were due allowing time for this process to complete. I won’t cover the PowerShell script in detail here as I will create a dedicated post for that in a week or two, but I will highlight the changes required if you were to start with the OData snapshot example.

  • The first API called was updated in this example to change the select query to just return the ProjectId:

image

  • After the while statement, the script will start a foreach loop and set the ProjectId to a variable:

image

  • Then the REST URL is constructed and the ProjectId is passed in. The select query includes the Project Name, Project ID and the Multiline custom fields that I want to include. I then make the various REST calls in a try / catch block, firstly to get the data:

image

  • Then to write the data to the SharePoint list:

image

Once that runs successfully with an account that has full access to all projects and edit access the the SharePoint list, your target list will contain all of the projects along with the selected fields. As mentioned, I will post this full script in a week or two once I get a chance to tidy a few bits up in the code sample but hopefully the screenshots of the changes along with the snapshot example PowerShell script, there will be enough pointers to get started. Now the data is in a single source, it is very simple to use in Power BI.

In Power BI Desktop add a new OData feed, in the URL field enter the SharePoint list REST URL for the source list, for example the REST URL I used is: https://tenant.sharepoint.com/sites/PWA/_api/web/lists/getbytitle(‘ProjectMutliLineFields&#8217;)/Items    where ProjectMutliLineFields is the name of my SharePoint list. Edit the query to launch the Power BI query editor. In this example, my source SharePoint list contains duplicate projects but in my report I want to only see the latest. The steps below will transform the data so that the report only has the latest version for each project record. Rename the query to IDandDate then remove all columns except for the ProjectId and Created columns:

image

Now group by ProjectId and get the Max Created value, I called this column “Latest”:

image

That will give you a list of unique Project IDs using the latest record. Now add a 2nd OData feed and use the same SharePoint list REST URL as in the previous step. Remove columns that are not required, I removed all expect for Title, ProjectId, Created and the multiline fields. Then rename the columns to meaningful names if required:

image

This query will currently contain the duplicate project records based on my example list, next I will merge this query with the IDandDate query using the ProjectId column and the Created/Latest column:

image

Hold down the Ctrl key to select more than one column per table for the merge.

This will add the new column into the table:

image

Click the double arrow on the column heading to expand the column then select the aggregate radio button. On the dropdown menu next to Latest select Maximum:

image

This will show a date value for the latest records, where a null is displayed, there is a duplicate record with a later date:

image

Filter out the null records from the Max of Latest date column and that is it. For the purpose of this blog post, I also added a 3rd query to the Project OData API to show data from the two sources. Close and Apply the data then ensure the relationships are correct, I also set the IDandDate query to be hidden in the report view:

image

Then design your report as needed making use of the same HTML Viewer custom visual:

image

As you can see, this is just a simple example like the others just to highlight the HTML formatting being rendered in Power BI.

Another option without having to write and maintain any custom code or write the data to a SharePoint list does make use of a 3rd party tool that extracts the Project Online data into an Azure SQL database as the data changes in Project Online. This particular tool is developed by the Product Dev team I lead at CPS and is called DataStore. This product is part of our edison365 product suite but is available on its own. This isn’t sales pitch so I won’t go into details here but I just wanted to give another option as some people prefer no code solutions. There are also other software vendors that do similar products for Project Online but I’m not sure if they include the multiline project level fields with the HTML. So using this tool or similar (check they include the HTML fields), you can get all of your Project Online data into an Azure SQL database, as mentioned, the DataStore tool will also include the HTML data as displayed below in the example SQL query below:

image

Power BI can get data from the Azure SQL Server and this data will also refresh in the Power BI App Service.

Feel free to contact me if you have any queries or questions but hopefully that gives you some ideas on including the HTML formatting in your Project Online reports using Power BI!

#ProjectOnline #PowerBI Report – Include #HTML formatting #PPM #PMOT #PowerQuery #OData #REST Part 2

January 3, 2018 at 11:10 pm | Posted in Configuration, Customisation, Functionality, Information, Reporting | 8 Comments
Tags: , , , , , , ,

Following on from my first post discussing including HTML formatting for Project Online Power BI Reports, in this post we will look at a summary of options to get the correct data into Power BI then walkthrough one of those options. In part 3, the final part, we will look at one of the other options to get the data.

For those of you that missed part 1, see the post here: https://pwmather.wordpress.com/2018/01/01/projectonline-powerbi-report-include-html-formatting-ppm-pmot-powerquery-odata-rest-part-1/

As per the first post, it is very simple to have the data rendered in Power BI to include the HTML formatting, the slightly more tricky part is to get the Project Online data into Power BI with the HTML included.

First, a bit of background on where in Project Online you can access the enterprise project multiline custom fields with the HTML included. As per the first post, you need to access the REST API ({PWAURL}/_api/ProjectServer) to get the data with the HTML included as the OData Reporting API ({PWAURL}/_api/ProjectData) has had the HTML removed. Using the REST API we can view the endpoints at the root:

SNAGHTML59e8e263

This is the REST API to programmatically interact with the Project Online data, you can create, read, update and delete data using this API depending on your access. For this reporting post we only need to read data, carryout the steps with an account that has access to all projects in the PWA instance like an Admin account.

The endpoint we need is /Projects:

SNAGHTML59ef8e46

This will detail all of the projects the logged on user has access to – it is recommended to carry out these steps with an account that has access to all projects in the PWA instance otherwise you might / will see errors in later steps. For each project detailed you will see a few key project level properties including things like Name, Description, Created Date, ID to name a few. It is also possible to navigate from there using the Project ID to get more details for that project. For example you can get the project tasks using the following URL: ProjectServer/Projects(‘{ProjectGUID}’)/Tasks or get the project team using this URL: ProjectServer/Projects(‘{ProjectGUID}’)/ProjectResources. To get the enterprise project level multiline custom fields we need to use the following URL: ProjectServer/Projects(‘{ProjectGUID}’)/IncludeCustomFields. Accessing this URL for the specified Project GUID (replace the placeholder with an actual project GUID) you will see more properties for that project including the multiline custom fields we need:

SNAGHTML59fcff6f

Notice the HTML in the custom field outlined in red in the image above. You would need to do this call for all projects but using the correct Project GUIDs. Also worth pointing out, in this API the custom fields are referenced using the internal names, for example Custom_x005f_4d0daaaba6ade21193f900155d153dd4 rather than the display names. You can use the custom fields endpoint to map the internal names to the display names: /ProjectServer/CustomFields.

So that covers the background on how you access the multiline custom field data that includes the HTML using the REST API, next we look at how to do this from Power BI. What makes it slightly more tricky than just using the normal OData Reporting API is that you have to make a call dynamically for each project GUID if you are using the REST API directly. In this series of posts we will look at calling this API dynamically straight from Power BI (covered later on in this post) but that has a limitation and also another method to get this data from one call / endpoint but that requires a bit of custom code / a 3rd party tool but does remove the limitation / issue. I will cover off the latter option in the 3rd blog post including a code sample / snippet.

Moving on to Power BI and getting this data dynamically and explaining the limitation. This process with follow the same approach I documented a while back to report on project site data using the the SharePoint list REST API: https://pwmather.wordpress.com/2016/01/05/want-to-query-cross-project-site-sharepoint-lists-in-projectonline-projectserver-powerbi-powerquery-bi-office365-excel-ppm/ As per the post above, this will require a custom function and a custom column to call the function. The limitation of this approach is that it works fine in the Power BI Desktop client but the data will not currently refresh in the Power BI App service. There might be workarounds to this limitation but that is beyond the scope of this blog post.

Firstly get a REST URL for one project that includes custom fields, for example I have used this: https://tenant.sharepoint.com/sites/pwa/_api/ProjectServer/Projects(‘ad641588-f34b-e511-89e3-00059a3c7a00‘)/IncludeCustomFields?$Select=Id,Name,Custom_x005f_4d0daaaba6ade21193f900155d153dd4 – replace the parts highlighted in yellow with details from your PWA instance. In this example I have included just one of my multiline custom fields but include as many multiline fields as required, just separate them using a comma. As mentioned before, use the /CustomFields endpoint to identify the correct custom fields to include in the select statement. You can see below, the example multiline field I have used is called “Status Summary”

SNAGHTML5cb3b4d5

Now add this URL as a data source in Power BI using the Get Data > OData feed option. That will open the Query Editor and show the record:

image

Update the Query Name to something like projectHTMLCFsFunction as this query will be turned into a function. In the Query Editor, on the View tab access the Advanced Editor and you will see your query:

SNAGHTML5cc54192

The full query will be similar to this:

let
    Source = OData.Feed("https://tenant.sharepoint.com/sites/pwa/_api/ProjectServer/Projects(‘ad641588-f34b-e511-89e3-00059a3c7a00&#8217;)/IncludeCustomFields?$Select=Id,Name,Custom_x005f_4d0daaaba6ade21193f900155d153dd4")
in
    Source

This needs to be modified to turn this into a parameterised function like below, parts highlighted in yellow are added / edited:

let loadHTMLCFs = (GUID as text) =>
    let
        Source = OData.Feed("https://tenant.sharepoint.com/sites/pwa/_api/ProjectServer/Projects(&#8216;"&GUID&"‘)/IncludeCustomFields?$Select=Id,Name,Custom_x005f_4d0daaaba6ade21193f900155d153dd4")
    in
        Source
in  loadHTMLCFs

A screen shot below to show the completed query in the Query Editor as the formatting is clearer, bits added / edited are outlined in red:

SNAGHTML5cc4e299

Click Done in the Query Editor and you will see the following:

image

No need to do anything with the parameter or buttons. Now we need to add another data source in for the other data feeds required in the report, for the purpose of this blog I will just add in the minimum required and that is the default OData Reporting API /Projects endpoint to get the other project fields into the report. In the Query Editor on the Home tab click New Source > OData feed and add in the OData Reporting API URL: https://tenant.sharepoint.com/sites/pwa/_api/ProjectData then select the tables required. For this blog post I have just selected Projects. Using the Query Editor, remove unwanted columns, rename columns etc. You will need to keep at least ProjectId and ProjectType, they are required. For the purpose of the blog post I have just selected ProjectId, ProjectType, ProjectName and ProjectOwnerName. Using ProjectType, filter out ProjectType 7 as this is the Timesheet Project record. Keeping this in the dataset will cause errors later on.

Once you have edited the query as required a new custom column needs to be added to invoke the function created earlier. Click the Add Column tab then click Custom Column. Give the column a name such as GetProjectHTMLCFs then enter the following: projectHTMLCFsFunction([ProjectId]) as seen below:

image

projectHTMLCFsFunction is the name of the function we created earlier and we are passing in the ProjectId. When clicking OK, this might take a while depending on how many projects you have as this will invoke the function for each project and call the REST API, passing in the ProjectId for that row and bring back the records. Once completed you will see the records as below in the new custom column:

SNAGHTML5cdd9337

Now the column needs to be expanded, click the double arrow in the custom column heading and expand the multiline custom fields, in this example I just have one:

image

Click OK and the data will refresh / load then display the data for the multiline columns:

SNAGHTML5ce22300

Notice we have the HTML in the data! Rename the columns for the correct display names then when completed, click Close & Apply. The changes will now be applied to the Power BI Report and load the data. Add in the HTML Viewer custom visual as detailed in blog post 1 then add the data on the the report canvas as you would normally. Ensure that the multiline custom fields use the HTML Viewer custom visual:

image

An example with a normal table visual and the HTML Viewer visual:

image

That’s it, design your Project Status reports to now include the HTML formatting your users have added. Just remember though, this will only refresh in the Power BI Desktop client. It can be published to the Power BI App service but the data will be static and will not update, you would need to open the report in the Power BI Desktop client, refresh it then publish it back into the Power BI service.

Next up in part 3 we will look at a slightly different approach to get the data in Power BI that does enable the report / data to refresh in the Power BI App service.

#ProjectOnline #PowerBI Report – Include #HTML formatting #PPM #PMOT #PowerQuery #OData #REST Part 1

January 1, 2018 at 9:40 am | Posted in Configuration, Customisation, Functionality, Information, Reporting | 3 Comments
Tags: , , , , , , ,

My first post for 2018, Happy New Year to all! This post is the first of 2 or 3 posts covering HTML formatting in your Power BI reports from Project Online multiline project level custom fields as seen below – screenshot from mock up / demo data:

image

For those of you that are familiar with the Project Online Reporting API, Microsoft made a change back in May 2016 to remove the HTML from the OData API ({PWAURL}/_api/ProjectData): https://pwmather.wordpress.com/2016/05/30/projectonline-odata-reporting-api-updated-to-remove-html-tags-office365-bi-excel-powerbi/. This was due to requests from customers so that Excel / Power BI reports could contain cleansed data without having to remove the HTML from the strings yourself. As mentioned in the blog post above, the HTML strings for multiline project custom fields are still available from the REST API ({PWAURL}/_api/ProjectServer).

Back in November 2017 a new custom Power BI visual was released to render HTML: https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-november-2017-feature-summary/#HTMLViewer, this now means that you can include the nicely formatted text from Project Online multiline project level custom fields in your Power BI reports. A couple of screen shots below show what your project custom field multiline data probably looks like today in your reports and what it could look like. Ignore the very basic dull looking report, this is purely just to demo the HTML rendering.

Without the HTML formatting from the OData API – it is just a block of text:

image

With the HTML formatting – it is nicely formatted and readable:

image

This matches the text on the Project Detail Page (PDP) in the Project Web App for that example demo project:

image

To be able to include the HTML formatting there are two parts:

  • Get the data that includes the HTML
  • Add the HTML Viewer custom visual to your Power BI Desktop client

The latter being very simple from the Power BI Desktop client by either clicking the ellipsis in the Visualizations pane:

image

Or using the button on the Home ribbon:

image

Then search for the HTML viewer and add it:

image

In the next 1 or 2 posts I will cover some different options for getting access to the data that includes the HTML.

#ProjectOnline #PPM #PowerBI Report Pack v2 #BI #Reporting #PowerQuery #DAX #Office365

October 31, 2017 at 12:39 pm | Posted in Add-on, Administration, Customisation, Functionality, Information, Reporting | 2 Comments
Tags: , , , ,

Back in January this year I published my first Power BI report pack for Project Online, the post can be found here: https://pwmather.wordpress.com/2017/01/03/projectonline-ppm-powerbi-report-pack-bi-reporting-powerquery-dax-office365/ I have now published the second version of my report pack for Project Online. This version can be download from the link below:

https://gallery.technet.microsoft.com/Online-Power-BI-Report-abcb3c3b

This report pack consists of 8 reports for Project Online, these reports can be seen below:

Portfolio Report page:

image

Issues Report page:

image

Risks Report page:

image

Project Report page:

image

Resource Demand Report page:

image

Resource Report page:

image

Timesheet Summary Report page:

image

Timesheet Detail Report page:

image

These reports only use default intrinsic fields so it should work for all Project Online deployments.

Once downloaded, the report pack data sources will need to be updated to point to your target Project Online PWA instance. To do this you will need the Power BI desktop tool which is a free download here: https://powerbi.microsoft.com/en-us/desktop

Open the downloaded PWMatherProjectOnlinePowerBIReportPackv2.pbit template file in Power BI Desktop and follow the steps below to point the data sources to your Project Online PWA instance:

 

  • In the parameter window that opens, enter the full Project Online PWA URL
  • Click Load
  • The data will now start to load and you will be prompted to connect
  • On the OData feed window, click Organizational account and click Sign in and enter credentials as required
  • Click Connect
  • On the Privacy levels window set the privacy as required
  • Click Save
  • The data will load – this may take a few minutes depending on the dataset size in Project Online
  • Access the Project Report page and select a project from the project filter
  • Save the report

This file can either be emailed around to colleagues with details on how to update the credentials to their own or what would be better is to publish the report to your Power BI workspace can create an organisational content pack that others can add to their Power BI workspace. If the Power BI organisational content pack is the chosen option, you might want to create a Dashboard first. See a previous blog post on this: https://pwmather.wordpress.com/2017/02/10/projectonline-ppm-powerbi-report-pack-publish-bi-reporting-powerquery-dax-office365/

Enjoy, I hope you like it Smile

#Agile now available in #MSProject #Kanban #Scrum #Sprints #Tasks

October 25, 2017 at 7:57 am | Posted in Administration, Configuration, Customisation, Functionality, Information | Comments Off on #Agile now available in #MSProject #Kanban #Scrum #Sprints #Tasks
Tags: , , , ,

Agile is now available in Microsoft Project! This is available on the following build version or later: 8625.2055 Click-to-Run. This build version is available on the Insider – formerly Insider Fast or Monthly Channel (Targeted) – formerly Insider Slow. This post is an intro to the new features. On the New menu you will see three new projects as highlighted below:

image

The three new project types are:

  • Scrum Project
  • Kanban Project
  • Waterfall Project

Firstly, “Waterfall Project” is the traditional type of project that is the same as using “Blank Project”. When creating either of these you will notice some new views in Project:

image

These views are available regardless of the project type selected. I can insert the new Board Status and Sprint fields in to the default Gantt Chart view:

image

Switching views, I can then see these test tasks on the associated board views:

image

image

Both of these provide the ability to add new tasks as well as drag and drop capabilities to move tasks to different columns / statuses. On the board view you can add / update the columns / buckets, once updated here the Board Status column in the table views will have the updated values. Regardless of the view, you can use the Format tab option on the Task Boards Tools ribbon to change the columns:

image

More on these in when we look at the new project types.

Creating a Scrum Project or a Kanban Project gives you some other functionality. Starting with a Scrum Project (purely because it is first in the list) we can see the new project below:

image

The Scrum Project defaults to open with the Sprint Planning Board as the active view. On the ribbon you will also notice there is an Agile Tools Scrum tab as well as the previously seen Task Board Tools Format tab.

image

The Planning button enables you to switch between the two Sprint Planning views:

image

The Sprint button enables you to switch between the two Current Sprint views:

image

The All button enables you to switch to the Agile Tasks view which is a filtered tabular view that only shows tasks that are tagged as Agile = Yes.

Adjust allows you to manage the sprints. This creates three automatically for you:

image

By default the sprints are generated using the project finish date but you can change to manage this via a custom date if needed:

image

The Agile button enables you to change your project to a different Agile methodology:

image

Switching between Scrum / Kanban / None just changes the options you have on the ribbon. Changing to None will remove all the Agile options ribbon but you can use the Project tab to change your project back to an Agile methodology:

image

Tasks can be moved to different sprints or status using the board view with drag and drop or just the familiar drop down menus on the table views.

Switching to Kanban, the options change to enable you to switch backlog views:

image

When creating a new Kanban project, the default view is the Backlog Board.

The All button enables you to switch to the Agile Tasks view which is a filtered tabular view that only shows tasks that are tagged as Agile = Yes. Then you have the same Agile button too.

There are also new default Agile reports:

image

Here is the Agile – Task Status report as an example:

image

This new feature is a client side feature only, this capability is not available in PWA. All of the new fields in Project such as Agile, Sprint and Board Status are in Project only.

If you are looking to use an Agile methodology to manage your projects in Microsoft Project, take a look at this new feature as it allows you to manage a single project using a combination of methodologies if you wish.

#ProjectOnline #PowerBI content pack 2 available #BI #Office365 #PPM update

October 19, 2017 at 11:56 am | Posted in Add-on, Administration, Customisation, Functionality, Information, Reporting | 1 Comment
Tags: , , , ,

Following on from my previous blog post regarding the updated Project Online Power BI content pack from Microsoft –  see below if you missed it:

https://pwmather.wordpress.com/2017/09/26/projectonline-powerbi-content-pack-2-available-bi-office365-ppm/

That was shortly removed from the Power BI service and hasn’t been updated there yet but the template file has since been made available to download from GitHub:

https://github.com/OfficeDev/Project-Power-BI-Content-Packs

You can now have the default reports provided here and extend to your own requirements for example a quick change would be to change the currency symbol used if you are not using US dollars:

image

Another change you might want to do, is make this report pack support non-English PWA site collections. This can be done by editing all of the queries in the Query Editor, use the Advanced Editor and update the code: OData.Feed(#”PWA Site URL” & “/_api/ProjectData”), to OData.Feed(#”PWA Site URL” & “/_api/ProjectData/[en-US]“), This will ensure all of the properties exist and the reports work.

Make the changes as required then publish to your own organisation.

Next Page »

Create a free website or blog at WordPress.com.
Entries and comments feeds.