I had all sorts of trouble trying to get the Project Web App standard reporting (ie Excel sheets supplied OOTB that use an Odata connection to extract data from PWA) working. I tried a lot of things including enabling the Project Web App Permission for Excel Web App Refresh site collection feature on the PWA site. So I opted to use Power BI. Here's how to do this:

  1. Download Power BI Desktop from the Windows Store
  2. Follow these instructions to connect to your PWA site

I wanted to create a timesheet report that I can send to clients where we do time and materials work. This is what I did.

Timesheet report

Load the following tables (and select these fields) from your PWA site:

  • TimesheetLineActualDataSet (ActualWorkBillable, Comment, Resource Name, TimeByDay)
  • TimesheetLines (ProjectName, TaskName, TimesheetLineComment)
  • Timesheets (Comment, PreiodName)

We generate a timesheet report for our clients by displaying the above in a table, and then export the visualisation to a csv.

Note that Power BI automatically applies Referential Integrity so you don't need to set this up!

Date columns are composite, in that they are broken up into year, month, day and quarter. We leave quarter out. To sort by date, select year, month and day columns in the table together by clicking and holding shift.

I am really getting into how Project Web App (PWA) could be the central place that we could run projects and keep a track of the many antics of the team. Here is some more that I have learned. Please note that this information relates to the Project Online version of PWA.

Licencing

I've been looking into the minimum licencing we can get away with to get team members submitting timesheets (in particular) and providing progress updates:

  • We have 5 Dynamics Customer Engagement licences through our Action Pack. Each of these can get full access to PWA.
  • For one additional user, I've found the cheapest way was to assign an Office 365 E1 licence and then a Project Plan 1 licence.
  • Once your user base begins to grow, it is likely to be cheaper to assign a Project Plan 3 licence for one user and then Project Plan Essentials for subsequent users. There must be a Project Plan 3 or 5 licence in use before Project Plan Essentials licences can be utilised.

Getting PWA to play nice with MS Project

  • Getting %Complete showing in PWA. If you are editing your project schedule in MS Project, you'll need to ensure that the %Complete column is visible. Then, when you save and publish the schedule, the %Complete of the project should flow through to PWA.
  • Getting the Resource Capacity function to display hours burned in timesheets. Ensure that when projects are edited in MS Project, that the Actual Work column is shown. Then hours recorded in timesheets should flow through to the Resource Capacity function.
  • Stop hours auto-populating in timesheets. This happens because of 2 qwerks of Project: 1) by default, %Complete and %Work Complete are linked (ie updating one on a task will automatically update the other to the same value); and 2) setting %Complete to 100% will always set %Work complete to the same. To get around this, 1) unlink %Complete and %Work Complete by going into Project, then selecting File | Options | Schedule. Scroll down to Calculation options for this project. Uncheck Updating Task status updates resource status. Then Save the Project and re-Publish; 2) When a task is complete but all hours aren't yet booked, set %Complete to 99%.

Issues and solutions

  • User can't access timesheets. This is usually due to an issue with the way in which their account was setup. The easiest way to resolve this is to delete the user and then re-add. If you have followed the instructions in my previous post to setup users, then deleting a user is a 2 step process: 1) Make the resource inactive, and then go to Delete Enterprise Objects (in PWA Settings) and delete the user from there too.

Project Web App (PWA) is a part of Project Online (and Project Server - although not the focus of this post) and our intention is to use it for:

  • Overall project tracking and reporting
  • Timesheeting

We may decide to use more features of this down the track.  PWA is attractive as it is flexible, extensible and is covers more complex scenarios that are more aligned to the work we do.  In addition, PWA is free as part of our Action Pack (for the first 5 users) and additional users can be added with a Team Member O365 licence.

There is another alternative – using the Dynamics 365 Project Service.  I’ve opted for PWA as it seems more straightforward.  Both integrate with MS Project.

To get started, spin up a PWA site from the SharePoint Admin Centre

  • Click + Create, then Other Options
  • From choose a Template, select Project Web App site.   Enter the other fields and then click Finish.
  • The PWA site will be created in a few minutes.  Next step is to set it up.

To setup a PWA site

Enter your new PWA site – click on it under Active sites in the SharePoint Admin Centre is one way to get there.

Click on the cog (top right) and then select PWA settings.

Set the following:

  • Fiscal periods. Define Fiscal Period Start Date – set to 7/1/2021 (for example), Set Fiscal Year Creation Model – leave this as default, Define Period Naming Convention – set to M1FY22 (for example).  Then click Create and save.
  • Time reporting periods.  Number of periods to be created – 52, Starts on – 7/5/2021 (first Monday in July and aligns with Quickbooks Payroll), Length of standard period – 7, Define Batch Naming Convention – W1FY22 (for example).  Click Create bulk to create time sheet periods.  Then click Save.
  • Timesheet Settings and Defaults. Default Timesheet Creation Mode – set to Current Projects.  Time sheet auditing – enabled, Single Entry Mode – enabled.  All others remain as defaults. Click Save.
  • Administrative time. Uncheck all time types. Click Save. Our policy is to only charge time to projects.
  • Timesheet managers.  Select at least one user of the system as a timesheet manager. If none are set
  • Under Additional Server settings. Set Currency settings to AUD, Full-time Equivalent Calculation to Hours per day (being 8). Notification Email Settings are enabled. Click Save.

Also, change the date format to Aus.  Do this by again selecting the cog (top right) and then Site Settings. Select Regional settings (under Site Administration). Set Locale to English(Australia). Then click OK.

On the left blade, select Edit links.  Select all Quick link items and then Save and close.  This will mean all features are easily available to you.

Next step is to add projects for people to book time to.

To establish resources

  • Click Resources in the left hand blade
  • Click RESOURCES in the top ribbon and then New.
  • Click Associate resource with a user account, and then enter the user’s account name in the User logon account field.  Also set Timesheet Manager and Default Assignment Owner to an appropriate user (ie their manager).

There is also a way to import users from a M365 group.

To create a project

  • Select Projects in the left hand blade
  • Click on the link to add the first project, or in the top ribbon, click PROJECTS and then New – Enterprise Project.
  • Enter details as you require
  • Enter the schedule.  Click Save and then Publish once done.

Finalising setup

I found that my account (I have global admin and performed the above setup) seemed to be created twice.  To solve this, I deleted my account as an Enterprise Resource and readded it – along the lines of this procedure (https://docs.microsoft.com/en-us/office/troubleshoot/project/error-when-add-edit-resource)

More help on resolving PWA issues can be found here (https://docs.microsoft.com/en-us/office/troubleshoot/office-client-welcome) and click on Projects in the left hand blade.

To turn on delegate permissions, you’ll first need to turn on Project Permission Mode (not set by default).  Go here to find more https://docs.microsoft.com/en-us/projectonline/change-permission-management-in-project-online.  Then, the Security section should be visible in PWA Settings.  In this section, click on Manage delegates.  Specify the person to act as delegate and the person and time period that they will act on their behalf.  Make sure also that the Delegate is part of the Admin group and the person having permissions delegated is part of at least the Team Members group.  Then to switch to that user, go to PWA Settings and under Personal Settings, click Act as a Delegate.  Select the person you want to act on their behalf and click Start Delegate Session.

Sometimes when someone separates from an organisation, we need to provide access for another person to view the Exchange mailbox and personal OneDrive of the separated person. There are a few ways to do this:

  1. Run a query and export the results from the Compliance Centre (useful if a specific thing is trying to be located), or
  2. Update permissions to allow others to access

In this post, we'll focus on the second method. To complete these steps, you'll need to be a Global Administrator.

To provide access:

  • Ensure that the account of the separated person is Active. This may mean that the account needs to be recovered from Deleted users (if under 28 days) or from backup. Also ensure that the account has sign-in blocked so that the separated person can't inadvertently sign in.

For access to their Exchange mailbox

  1. Go to the Admin centre and select Users | Active users
  2. Select the separated user's account
  3. Select Mail (tab) | Read and manage permissions (under Mailbox permissions)
  4. Select +Add permissions
  5. Select the people that need access and click Add.
  6. Let the users that need access know that they now have access. They simply need to add the separated user's account in Outlook.

More details here.

For access to their OneDrive:

  1. Go to the Admin Centre and then select Admin centres | SharePoint (you may need to show all to reveal this in the left-hand blade).
  2. In the left-hand blade of the SharePoint admin centre, select More features | User profiles | Manage User Profiles
  3. Search for the separated person's account
  4. Right click on the account and select Manage site collection owners.
  5. Add the users that need access to this OneDrive.
  6. Send the URL of the OneDrive to the users that need access. To find this, go to back to the Admin Centre and select Users | Active users and then the separated user. In the flyout (right), select OneDrive, and then Create link to files (under Get access to files).

Refer to this for more details.

Sometimes you need to use Powershell to manipulate Exchange Online. When MFA is enabled, you may get something like the following error:

New-PSSession : [ps.outlook.com] Connecting to remote server ps.outlook.com failed with the following error message :Access is denied.

To fix this situation, you'll need to setup EXO V2 on your local PC. To do this:

When working with Exchange Online, sometimes you'll come across issues in sharing calendars with others. As an administrator, you can use the following steps to check calendar permissions and to update them as you need.

To get current calendar permissions for a user

  1. Run Powershell as Administrator
  2. Get Office 365 credentials: $LiveCred = Get-Credential
  3. If MFA is not turned on for your account:
    1. Start a session with the Office 365 tenant - $Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell/ -Credential $LiveCred -Authentication Basic –AllowRedirection
      1. You may have to execute the above command a second time if you get a connection error – note that you may also get an authentication error if the login account has MFA turned on and EXO V2 is not installed on the PC you are using. Check this out to fix this. Then follow the instructions If MFA is enabled for your account.
    2. Import Powershell session: Import-PSSession $Session
  4. If MFA is enabled for your account:
    1. Connect-ExchangeOnline -UserPrincipalName [user-name] -ShowProgress $true
  5. Get calendar permission details: Get-MailboxFolderPermission [user-account]:\calendar

This will then list out all of the accounts that have been granted access, including groups.

To add or update calendar permissions

We normally have a group called All Staff of which all staff are a member. Then default permissions for calendars are defined for this group (normally Reviewer - so that anyone can see calendar items of others but can't change them). Elevated permissions (if needed) can be granted specifically for people or groups on top of this default.

If say user-A doesn't have access to user-B's calendar and needs Reviewer access, run the command: Add-MailboxFolderPermission -Identity [user-B]:\Calendar -User [user-A] -AccessRights Reviewer

Alternatively, if say user-A had AvailabilityOnly access to user-B's calendar and needs Reviewer access then run: Set-MailboxFolderPermission -Identity [user-B]:\Calendar -User [user-A] -AccessRights Reviewer

To remove permissions one-by-one, you can use: Remove-MailboxFolderPermission -Identity [user-B]:\Calendar -User [user-A] -AccessRights Reviewer

Alternatively, use the following to remove permissions in bulk for a specific user calendar (and leaving Default or Anonymous permissions):

Get-MailboxFolderPermission [user-B]:\Calendar ` 
| ? {$_.User -notmatch "^(Default|Anonymous)$"} `
| % { Remove-MailboxFolderPermission -Identity $_.Identity -User $_.User.DisplayName -Confirm:$false }

More details can be found at: https://www.michev.info/Blog/Post/2500/how-to-reset-mailbox-folder-permissions

Other references

Sometimes migrating mailboxes from on-prem to Exchange Online is easy. But when it comes to moving mailboxes from a GoDaddy managed tenant, getting Admin access to perform the export is almost impossible. Here's one way that seems to work.

Export mailbox contents

  1. Log into the Compliance Centre as an account admin (https://compliance.microsoft.com/)
  2. Select eDiscovery | Core from the left blade
  3. Create a case
  4. Apply a search to the case.  From the menu bar, select Searches and then + New Search
    1. Select Specific locations, and then Exchange Mailboxes only
    1. In Define your search conditions select the date range for mailbox items
    1. Review the search and click Submit
  5. Wait until the search you have just submitted completes
  6. Initiate the export of mailbox items found in the search
    1. Click on the search you created in step 4 and details should be shown in the right-hand blade
    1. Click Actions | Export results
    1. In the Export results blade, leave defaults as they are (particularly Export Exchange content as One PST file for each mailbox
    1. Click Export
  7. Download export results (must be done from Edge)
    1. Click on Exports in the menu bar
    1. Select the export that you have created in the previous step
    1. Wait until the export completes (this may take a few hours)

Update folder structure in PST files

The exported PST files will need to be modified so that they have a similar folder structure to the target mailbox. If you open the exported PST, you'll notice that the Inbox is in a subfolder under <mailbox name>(Primary store)/top of information store/. Simply locate the Inbox and move this to folder root. Repeat this for any other folders that need to come across. Delete any folders that don't need to come across (like the now empty <mailbox name>(Primary store)/top of information store/ folder).

To make these changes, open the PST in Outlook, made changes, then close the PST.

Note that if this step is not done, a new folder structure will be created in the target mailbox for the imported items, rather than them being merged with the target inbox.

Import mailbox contents

Follow these instructions in the new tenant: https://docs.microsoft.com/en-us/microsoft-365/compliance/use-network-upload-to-import-pst-files?view=o365-worldwide

First step is to create an import job

  1. Again, log into the Compliance Centre as the account admin (https://compliance.microsoft.com/)
  2. Select Information Governance | Import
    1. Note that PST files should be < 20Gb
  3. Follow the instructions provided
  4. Download and install AzCopy as per the instructions. Note that:
    1. If you get an error when trying to upload PSTs with AzCopy, then the SAS URL must be modified to enclose all & in double quotes (ie & becomes "&"). Alternatively, close entire parameter values in closed double quotes.
    2. You can use this spreadsheet as a template to create AzCopy commands
    3. Use the /NC2:2 switch on slower upload speeds to avoid timeout. Lowers thread count to 2. More here.
  5. Check that AzCopy uploaded the correct number of PST files
  6. Create a mapping file, using this as a template
    1. Note that the fields FilePath will usually be blank and the TargetRootFolder will usually be / (ie Inbox)
  7. Select the mapping file and complete the import.

Next step is to run the import job to import into Office 365

  1. Select the import job you want to import
  2. Click on Import into Office 365
  3. Select whether you would like any filters applied (ie only import items from the last 12 months)
  4. Select OK to commence the import

To enable users to add guests within the tenant

Sometimes you need users to add guests on their own. Normally, this can only be done by Admins. To enable this:

  1. Log into the Office 365 admin portal (https://admin.microsoft.com/)
  2. Go to Settings | Org settings (left blade) | Security and Privacy (menu bar) | Sharing
  3. Check Let users add new guests

To add a new guest user

In the Office 365 admin portal:

  1. Select Users | Guest users
  2. Click + Add a guest user.  You’ll be redirected to Azure
  3. Select Invite user and enter their details. 

The guest user needs to accept the invite before gaining access to anything.

To add them to a Planner board, follow https://support.microsoft.com/en-us/office/guest-access-in-microsoft-planner-cc5d7f96-dced-4da4-ab62-08c72d9759c6