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:
Download Power BI Desktop from the Windows Store
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:
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.
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:
Run a query and export the results from the Compliance Centre (useful if a specific thing is trying to be located), or
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
Go to the Admin centre and select Users | Active users
Select the separated user's account
Select Mail (tab) | Read and manage permissions (under Mailbox permissions)
Select +Add permissions
Select the people that need access and click Add.
Let the users that need access know that they now have access. They simply need to add the separated user's account in Outlook.
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).
In the left-hand blade of the SharePoint admin centre, select More features | User profiles | Manage User Profiles
Search for the separated person's account
Right click on the account and select Manage site collection owners.
Add the users that need access to this OneDrive.
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).
Use the following command to connect: Connect-ExchangeOnline -UserPrincipalName [admin-account] -ShowProgress $true
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
Run Powershell as Administrator
Get Office 365 credentials: $LiveCred = Get-Credential
If MFA is not turned on for your account:
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
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.
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):
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.
Apply a search to the case. From the menu bar, select Searches and then + New Search
Select Specific locations, and then Exchange Mailboxes only
In Define your search conditions select the date range for mailbox items
Review the search and click Submit
Wait until the search you have just submitted completes
Initiate the export of mailbox items found in the search
Click on the search you created in step 4 and details should be shown in the right-hand blade
Click Actions | Export results
In the Export results blade, leave defaults as they are (particularly Export Exchange content as One PST file for each mailbox
Click Export
Download export results (must be done from Edge)
Click on Exports in the menu bar
Select the export that you have created in the previous step
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.
Download and install AzCopy as per the instructions. Note that:
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.
You can use this spreadsheet as a template to create AzCopy commands
Use the /NC2:2 switch on slower upload speeds to avoid timeout. Lowers thread count to 2. More here.
Check that AzCopy uploaded the correct number of PST files