Table of Contents

Reporting Hub

Overview

Reports often forms one of the most critical and visible parts of any PPM (Project Portfolio Management) solution. Excellent reports communicate key insights quickly and effectively, and support informed decision making at all levels of an organisation. They also help with governance and the overall process of your solution. Microsoft Project Online allows for the creation of custom Excel Services and OData based reports. This is powerful as it allows end users to use Excel tools to build reports. However many customers have reporting needs that go beyond the abilities of OData and Excel... in this situation, the Sensei Reporting Hub comes to the rescue!

Our Project Online Reporting Hub enables you to report on data maintained in Project Online and SharePoint using a fast, reliable SQL Database connection. You can utilise the Reporting Hub to create reports in Microsoft SQL Server Reporting Services (SSRS) or any other reporting tool. If you have existing SSRS reports from an on-premises installation, they will work with minor modification in the Cloud.

This document will provide an overview of Reporting Hub, but your circumstances may differ hence this cannot be used for operational purposes but rather general information. This is a document to facilitate discussion, it is not a formal design or document to be used for anything but getting together and talking about.

What is it?

The Reporting Hub is software as a service that clients subscribe to from Sensei. This is not a custom built solution for a specific client, but rather a standardised service that everyone uses that is Cloud based. This specifically gives you, without needing to built or maintain it yourself:

  • a reporting service (database and report renderer) to consolidate, serve and visualise Project Online and other data

  • real-time reporting on your Project Online content

  • create reports and export reports in PDF, Word, Excel and PowerPoint

  • SharePoint Project Site data can be easily reported against

  • paid per month, per customer (not per user)

  • runs in Microsoft Azure

Benefits

  • the provided database is modelled after the on-premises Project Server reporting database. Therefore most existing "on premises" reports can be easily moved to Project Online with little or no changes

  • your Project Online data is available in real-time with no maintenance requirements

  • your data is secured and stored in your dedicated Database in your nearest Azure data centre

  • you don't need to develop reports using slow OData queries. Our Reporting Hub is fast to retrieve your reports

  • you can extend your Reporting Hub database to include data from other Line of Business applications for reporting purposes

Sensei Reporting Hub is an enabler of reporting in Project Online. It has two main pieces:

  1. Real-time SQL Azure Data Source. Sensei Reporting Hub retrieves all the Project Server and SharePoint list data from your installation and creates a set of tables and views inside SQL Azure that make most reporting tasks very easy.

  2. SQL Server Reporting Services Renderer. Reporting Services is a technology used to store and render reports for users. These reports are available via an App Part in the main Project Web Application (PWA) website and can be exported by the user in various formats such as PDF, Microsoft Word, Excel etc.

Both of these pieces are provided as a subscription service known as Reporting Hub.  The SQL Azure data source also has other use-cases such as integration with on premise data.

Design Advantages

There are several advantages in using the Reporting Hub for report confection. The top 4 main points are:

  1. High Availability solution with Geo-Replication

  2. Data security including credentials, location and your own database

  3. Elastic performance with scalable DTUs

  4. Utility with 250GB data storage included and ability to bring your own on-premise data

Using the Reporting Hub

The Reporting Hub is a service that you subscribe to, along with many other clients around the world. As a result you have your own SQL database that is populated by the Reporting Hub with your Project Online data, based on the Project Server 2013 reporting database structure.

You can write reports that use Reporting Hub using any reporting tool such as Power BI, SSRS etc. How you use the Reporting Hub database depends on the reports you want to write, and the results you get from your reports in terms of speed and usefulness will depend on how you write the queries, as well as if you have the Standard or Enterprise version of Reporting Hub.

Solution Overview

Job Queue and Timing Process

The changes done in Project Online are instantaneously reflected in the Reporting Hub. These changes are executed in the background on a separate space without affecting the overall Project Online domain space. They are not related to foreground activities and are executed with an appropriate execution priority. To ensure that users working with the Reporting Hub and Project Online are not affected by the activity.

Only when the data has been changed a job is generated. If no changes between the target data and the current data is detected nothing is queued and therefore it does not count against the number of changes. It means the Reporting Hub uses a very efficient method of capturing and tracking changes only when needed, and where they are needed.

Solution Architecture

Cloud Authentication Architecture

Reporting Hub Architecture

There are three main parts to the Reporting Hub product

  1. Data Collector and SQL Azure DB (Green lines)

  2. Integration Hub Service Add-on (Yellow lines)

  3. SSRS Renderer (Orange lines)

The following describes the flow through Reporting Hub for those interested in the technical architecture.

Item Details
1 Reporting Hub registers Project Online and SharePoint Online remote event handlers
2 Changes are queued in the closest region
3 The data pump de-queue the jobs
4 The data pump read the changed data form Project Online and SharePoint Online
5 The data pump updated the SQL Azure database
6 CSV files are generated by SAP and transferred to a machine within the DMZ. CSV files are downloaded from the Integration Hub are imported into SAP
7 CSV files are uploaded to and downloaded from an Azure Blob Storage using HTTPS
8 The Integration Hub will monitor the Blob Storage for changes and push new export files to the Blob Storage
9 The import logic detects changes in integration data
10 The import logic pushes the updates to Project Online and SharePoint Online
11 An user request to view a report from Project Online
12 The user request is validated against the Azure Active Directory
13 The user request for rendering a report is send to Reporting Services
14 Reporting Services gathers the report data from the Customer SQL Azure database
15 Each customer has an individual SQL Azure database. Each database is secured with individual users and firewall rules.

Frequently Asked Questions

The following are frequently asked questions that may help you with your further understanding of Reporting Hub.

Is this customised for me? Similar to O365 itself, the Reporting Hub is a Software As A Service (SaaS) product that clients subscribe to from Sensei. This is not a custom built solution for each a specific client, but rather a multi-tenant service.

Where is my data? In the Microsoft data centre most closely aligning to your O365 tenant in Melbourne, West US or West Europe.

How is my data stored? Sensei's Reporting Hub is a Cloud based solution that is hosted in a Microsoft Azure private Cloud instance. Each customer has an individually secured and encrypted Contained-Mode Azure SQL Database backing their application and these services can be replicated and configured to interact with multiple pre-production environments for testing and change management purposes.

What knowledge do I need to use the Reporting Hub? Reporting Hub is a SQL database, therefore you would need to understand the data structure to at least a basic level if you are using Power BI. If you are writing SQL queries you will need the understanding of a report developer who has used Project Server or Project Online and built up knowledge.

Is all Project Online data available in the Reporting Hub? No. If Microsoft has not exposed the data through ODATA then it is not available to the Reporting Hub or any other method, and hence it is not available for reporting. An example is some workflow data. In addition if data is not commonly used, Sensei has not exposed it. An example is the portfolio selection data. In this case it is mostly available and can be added to the Reporting Hub if requested.

How do I use it? Reporting Hub is a SQL database. It consists of SQL objects such as tables and views. You include these objects in SQL queries, or you use Power BI to bring through the default tables. Like any database you can create your own objects as well that specifically suit your needs.

Is the Reporting Hub a reporting tool? Yes it is a database and a SSRS renderer. A report authoring tool such as Reporting Services Report Builder or Power BI Desktop is used to author the reports used with the service.

What does the Reporting Hub give me that Project Online does not? Project Online is Cloud based, and therefore does not expose a SQL database that can be used for writing queries against, such as those used in reports. Without Reporting Hub, Project Online has the following limitations:

  1. Without Reporting Hub, SharePoint data is only provided by Project Online for out of the box fields. No new lists or fields are exposed through OData. Therefore you use Reporting Hub to gain access to all of your lists including customised new lists;

  2. Without Reporting Hub you need to use OData, which results in relationships between data and more advanced queries not being as easy. Using Reporting Hub means you use traditional SQL.

Does Reporting Hub provide anything specific that any other SQL database doesn't? Yes and no. It is a SQL database. It provides some additional relationships between tables, but conceptually it acts as any SQL database and should be used as such. The value of the Reporting Hub is in its ability to move data from Project Online into the SQL database so that you can report on it while also providing legacy compatibility for on-premises reporting investments.

Do I have to use the objects in the Reporting Hub? No. A SQL database consists of tables, and a set of views and procedures that exist to save you time if you keep needing to bring the same tables together. However since these views are created to provide all fields in Project Online so that anyone can use them, they are by default going to include more fields than you want in most cases. You may need to create your own views as they will be targeted to your specific needs. The smaller the view the faster it will perform as well.

Can I use another database? Using the hosted SQL Azure contained mode database or a BYOD SQL Azure database are the only two supported options.

Can I use any reporting tool? Yes. The Reporting Hub is exposed to you as a SQL database in Azure. Any reporting tool that can connect to it can be used, such as Reporting Services, Power BI, Excel, Qlik etc.

What do I do if my reports are slow? If reports are running slow there can be several reasons. First, is the query as optimal as it can be? Second, is the query using a default object such as a view that itself cannot handle the volume of fields? Third, is the query complex and needing to process a lot of data in a complex manner, and needs more processing power than the Reporting Hub Standard edition provides? If more processing resources are required, there are higher performance tiers of the Reporting Hub service available. Only pay for what you use.

Are there different levels of power available? Yes. By default the Standard level is provided. This serves the needs of most clients. However if you fall into one of the following you may need the Enterprise level:

  1. Large volume of data being processed by complex reports that result in some reports being slow and while they process other reports are slowed down;

  2. Stored procedures being used that are complex;

  3. Significant number of custom fields, which are more than the default objects can handle. Note that changing the queries themselves is usually the best approach to this not more processing power;

  4. High number of reports being run simultaneously.

Is the data structure in the Reporting Hub the same as the Project Server 2013 database? The main tables and views are the same. We have tried to maintain compatibility between 2010, 2013 and Online where possible. There are tables that are not present as this data is not exposed by Microsoft, and therefore cannot be obtained to add into the Reporting Hub. This is a technical limitation of Microsoft and no solution can access this data. An example is some workflow data.

There are a few tables that Sensei have not added into the Reporting Hub as the demand for them is low. These can be added if needed. An example is the portfolio analyser data.

The Reporting Hub does provide additional keys linking project and site data to allow for simpler queries to be written, and threby improves the database.

Are the views in the Reporting Hub the same as those in Project Server 2013? Where possible we have tried to maintain compatibility by reproducing the tables and views. But some of these objects are dynamically built when you add new custom fields. Each time a custom field is added the view is torn down and rebuilt automatically. This places a performance load onto the Reporting Hub.

The built in views are generic and are dynamically built to add in all of your custom fields. If there are a lot of custom fields this can be slow, which is why most report developers learn the data structure and then create their own views.

If needed Sensei can assist you with creating reports, views or queries. We are happy to discuss a project focussed on reporting with you.

Does Reporting Hub solve the 2,000 Project Site limit? Project Online can have 30,000 projects in it. Each project can have a project site. But only 2,000 of those projects can have project sites. To get around that different site collections are created. Enterprise Reporting Hub however allows all the data from multiple site collections to be brought into one database and therefore you can report across it all. This is specifically an Enterprise Reporting Hub feature.

Can I migrate existing reports from older Project Server to Project Online using Reporting Hub? Yes, with some updating of the data sources. The Reporting Hub structure is based on that of the Project Server 2013 reporting database. This means the tables mostly align. However the underlying technology is Azure in the cloud, not your local SQL Server. As a result you may find that performance is different, and you may need to look at the performance of the Azure under Reporting Hub. In addition you may find that queries and views that performed one way on premises perform differently in Azure.

I have a lot of fields and data. Does that matter? Yes. While the Reporting Hub which is based on SQL in Azure can handle a significantly large database, and is used globally for large databases, you need to size that database correctly. Just like your on premises system had the server sized to fit you and possibly increased over time, so does Reporting Hub need to be sized.

In addition, if you have a lot of custom fields the queries within Reporting Hub that implement the views may not be able to dynamically rebuild themselves based on the number of fields. As a result you may need to create your own tailored views. There is a 450 custom field limit in Project Online but that does not directly impact Reporting Hub. Reporting Hub is indirectly slowed when you try to use generic views that are out of the box with any large number of fields, which co-incidently may be close to 450.

Deployment

Requirements

When planning the deployment for Reporting Hub there are several requirements to ensure a smooth experience.

Pre-deployment checklist (see sections below for details):

Deployment via Sensei Hub requirements:

  1. Service Account (recommended -- required if also using Integration Hub):

    a. At minimum Project Online Professional license.

    b. If MFA or 2-factor authentication is enabled ensure "App Password" is generated

    c. Azure AD Conditional Access should be disabled/configured to allow external use.

    d. Site Collection Admin permissions to the PWA site to collect SharePoint data.

    e. At minimum Portfolio Viewer PWA permission to collect Project Data

    f. Non-expiring password recommended

  2. PWA Time-phased Data settings set to Daily.

  3. Network access for Report Designers:

    g. Outbound port 1433 open to all destinations

    h. Public IP address range known

  4. Trusted Sites updated if users are planning to use IE for printing/exporting

Service Account

To respond to events in your Project Online Installation Sensei Reporting Hub will utilise a service account (set of credentials). This service account has the following requirements:

  • Must have at least a Project Online Professional license.

  • Must have appropriate SharePoint Permissions. Since Reporting Hub also attempts to aggregate SharePoint list information -- it is important that the Reporting Hub service account is granted appropriate access to read all the required SharePoint items. If you want all your SharePoint list items to be reportable, grant the Reporting Hub Account Site Collection Administrator rights.

  • Must have at least "Portfolio Viewer" Project Online permissions -- this grants the service account access to the ODATA Feeds ("Reporting" permission). PWA Administrator is also acceptable.

  1. Create the Reporting Hub Service Account. Go to the Administration portal for your tenant

  2. Under Users->Active Users, press the plus button and fill out the account details.


    Ensure you ensure that the account is allocated at least a Project Online Professional license.

  3. Set the account to have a non-expiring password. This step can be accomplished via the O365 Administrative portal or by PowerShell

Set-MsolUser -UserPrincipalName <serviceaccount@contoso.com> -PasswordNeverExpires $true

Legacy Authentication Considerations

The authentication mechanisms utilised by the Reporting Hub and Integration Hub are a function of the Microsoft API's. The Reporting Hub and Integration Hub are non-interactive services so they must authenticate without a user being present. The service account utilised for this process authenticates non-interactively via Legacy Authentication.

We realise this is not ideal however there are some additional points of note for Legacy Authentication

  1. Legacy Authentication is encrypted and secure. The communications channel utilised when sending username and password to Azure AD are protected by the latest TLS encryption, identical to online banking.
  2. Legacy Authentication is still a standard supported feature on all Microsoft O365 tenants.
  3. Legacy Authentication is the only method made available by Microsoft to authenticate non-interactively with the Project Online PSI interface. Microsoft doesn't support modern authentication of Service Principals to this interface (App Only tokens), and all access must be tied back to a licensed user of Project Online.
  4. Adding the Sensei Reporting Hub or Integration Hub to an Office 365 installation doesn't add any endpoints to that installation or alter the security posture of an O365 tenant.
  5. The Reporting Hub can be set to utilise App+User tokens, aka low-trust ACS mode (which we also call SharePoint App mode) but it is not fully functional, is reliant on refresh tokens that can expire therefore we cannot provide service SLAs in this mode. Additionally since the Integration Hub utilises the PSI (see below) it is not available in SharePoint App authentication mode (App + User token).
  6. Maximum conservative security posture while maintaining full functionality is achieved by implementing Azure AD conditional access to limit the source locations from which legacy authentication is available. See below for Azure AD Conditional Access considerations.

Authentication to APIs in Project Online

Legacy Authentication

  • ODATA Feed: Found at <PWA URL>/_api/ProjectData the Project ODATA feed can be accessed by a user token acquired via legacy authentication or a modern App + User token. Cannot be accessed via App-Only tokens.
  • PSI (Project Server Interface): Found at <PWA URL>/_vti_bin/psi The PSI can be accessed only via user token. The only way to acquire a user token non-interactively is via legacy authentication.
  • REST Interface: Found at <PWA URL>/_api/ProjectServer The REST interface for Project Online contains some basic functionality for performing basic operations and supports User and and App+User tokens
  • CSOM Interface: Utilised via client side libraries, this interface is commonly used with user tokens but can be used with App+User tokens in SharePoint Provider Hosted app scenarios. The functionality provided by this interface is incomplete.

Multi-Factor Authentication Considerations

When enabling Multi-Factor Authentication (MFA) for the account used as the Reporting Hub service account, the credentials supplied must instead be an "App Password" created for the Reporting Hub Service account.

Once you have created the Reporting Hub service account as per the instructions above:

  1. Log into SharePoint Online using the Reporting Hub service account, supplying the username, password and code as required by MFA.

  2. Go to Office 365 Settings

  3. Go to Additional security verification

  4. Click on Update your phone numbers used for account security.

  5. Choose "app passwords" in the header menu

  6. Then click create. This will issue the password for use with Reporting Hub.

Azure AD Conditional Access Considerations

Azure AD Conditional Access is an advanced security measure designed to limit the exposure of a directory from attack. We fully support this effort however our services must be white-listed in order to provide the necessary functionality.

The following IP Addresses range must be added to the Azure AD conditional access white-list:

All customers:

  • 20.190.127.130
  • 20.190.127.131
  • 20.190.127.152
  • 20.70.80.31
  • 20.70.81.217
  • 20.70.82.111
  • 20.70.84.161
  • 20.70.86.193
  • 20.70.86.214
  • 23.101.224.24
  • 23.101.224.25
  • 23.101.224.26
  • 23.101.224.27
  • 23.101.224.28
  • 4.198.156.120
  • 4.198.156.246
  • 4.198.157.137
  • 4.198.157.250
  • 4.198.159.59
  • 4.198.159.63
  • 40.127.74.210
  • 52.147.32.134

Additional IP addresses for customers in particular categories:

  • Australian BYOD Customers:

    • 52.255.42.4

  • Australian Enterprise/Premium Level Customers:

    • 13.70.147.206
    • 52.255.48.136
    • 52.255.52.247
    • 52.255.52.88
    • 52.255.54.129
    • 52.255.58.179
    • 52.255.59.119

  • United States - All Customers:

    • 23.101.194.183
    • 23.101.205.197
    • 23.101.206.54
    • 23.101.206.60

  • United States - BYOD customers

    • 40.125.70.84 (Removed on 16 December 2020)
    • 168.62.213.203

  • Europe - All customers:

    • 104.40.212.192
    • 104.46.52.221
    • 104.46.52.73
    • 104.46.59.103
    • 13.80.2.124
    • 13.80.3.14
    • 13.80.3.22
    • 13.94.233.107
    • 191.233.87.194
    • 20.76.129.180
    • 20.76.129.45
    • 20.76.131.133
    • 20.76.131.150
    • 20.76.131.152
    • 20.76.131.193
    • 98.64.25.117
    • 98.64.25.118
    • 98.64.25.121
    • 98.64.25.122
    • 98.64.25.125
    • 98.64.25.166

  • Europe BYOD customers:

    • 13.93.127.130

PWA Time-phased Data settings

The Aggregation settings for the Time-phased ODATA feeds must be set to daily for the Reporting Hub to produce expected behaviour.

To set this setting, navigate to PWA Settings->Enterprise Data-Reporting

Set the Time phased data aggregation setting to Daily and click Save

If you have any projects already in the system -- they will require republishing for the changes to take effect for those projects. If you have many projects to publish, consider installing the free Bulk Publish App from the Store.

Custom Field Naming

For the best Reporting Hub experience follow these rules when creating and managing custom fields:

  • Do not use these characters in field names: [ ] . , ; ' ` : / * | ? & % $ ! - + = ( ) { } # ^ < > " ~ @

  • Do not use leading or trailing whitespace

  • Do not use fields that start with numbers

  • Do not use fields that differ only by whitespace or one of the characters in the list above.

  • Do not create custom fields that have the same name as internal fields E.g. ProjectIdentifier, ProjectID, StagesInfo

  • Avoid renaming custom fields and lookup table entries.

Network access for Report Designers

If you need access to the SQL Azure Database component on your local network in one of the following scenarios:

  • Report Builder

  • Excel Desktop Application

  • Power BI Desktop

  • SQL Server Management Studio

You will need:

  • Office 365 components on your computer -- download from https://portal.office.com

  • SQL Server native client -- deployed with Report Builder or SQL Server Management Studio

  • Local network must allow outbound connections to all destinations on port 1433. As IP addressing in the Azure Service is likely to change without notice, opening 1433 outbound access to all destinations is recommended, however if that is not possible then please outbound port 1433 access to:

    • The SQL Azure Server. Refer to Microsoft documentation, but at the time of writing:

      • Australian customers: au1-rh.sensei.cloud

        • 13.73.109.251

        • 191.239.192.109

      • US Customers: us1-rh.sensei.cloud

        • 104.42.238.205

        • 23.99.34.75

      • Europe customers: eu1-rh.sensei.cloud

        • 40.68.37.158

        • 191.237.232.75

Connectivity Troubleshooting Tips

  • Always remember to specify the database name of the database you want to connect to before initiating a connection.

  • Specify a TCP connection is made by entering the server name in this format:
    tcp:host.database.windows.net,1433

  • Ensure your current location has been whitelisted by Sensei. (Consult Sensei support if unsure)

  • Ensure your computer has the latest software and updates.

Trusted Sites and using Internet Explorer

For customers using Internet Explorer, there are some extra steps that are required to ensure the smooth integration of Reporting Hub into the SharePoint environment. Users of other browsers such as Chrome, Firefox, Safari and Edge are exempt from these requirements:

In addition to the standard Microsoft guidance Sensei Reporting Hub requires the following configuration for Office 365 integration to function correctly:

Trusted Sites Zone:

  • https://*.microsoftonline.com

  • https://*.sharepoint.com

  • https://*.projectserver.com.au

Third-party cookies in Chrome

Chrome has phased out the acceptance of third-party cookies. The Reporting Hub requires third-party cookies for a smooth integration into the SharePoint environment.

If an environment is affected, they will receive a connection error like the below:

An example error screen is displayed

To fix this issue the client's SharePoint domain will need to be setup to allow the use of third-party cookies. The setting to fix this is found here:

Chrome third-party cookies

BYOD Preparation

If you plan to house your Reporting Hub data in a SQL Azure database in your own Azure Subscription, you will need to prepare:

  • A blank SQL Azure database with default collation settings.
    • Database performance level “S3” ( 1 x General Purpose vCore) or above is recommended for basic functionality. Custom data processing may require higher levels of performance. Monitoring and base-lining of performance over time is recommended.
  • SQL Azure Server must have the “Allow access to Azure services” enabled to provide access to Excel Services, Power BI and incoming data from the Reporting Hub.
    • For a more conservative risk posture, the Sensei Reporting Hub can be whitelisted by only allowing the IP addresses listed in Azure AD Conditional Access Considerations. If Power BI, SSRS or Excel Services is required then consider also whitelisting the IP addresses for those services.

Once completed, please provide Sensei with the following details to initiate the provisioning:

  • Connection details and SQL credentials with db_owner (Admin) permissions
    • SQL Server name
    • SQL Database name
    • SQL username
    • SQL user password

Disaster Recovery Posture

Note

Disaster recovery and SLA expectations don't apply for BYOD customers who host their own database, as many of the factors are outside our control.

Sensei will always use our best effort to maximise the uptime and your utility of the service. We have chosen the following disaster recovery options as suitable for most customers, however if you have special needs and require a higher availability for your service -- please let us know and we can organise to cater for those needs.

Data Storage SLA: 99.99%

SQL Azure Standard Tier Database:

  • SLA: 99.99% uptime provided by LRS (Locally Redundant Storage): 3 Copies of your data reside in the selected data centre and at least two are synchronous (always in sync and up to date). The cutover in the event of a failure is automatic and transparent.

  • GRS: Not enabled by default -- your data is not geo-replicated beyond the region you specify during deployment. This is important for data sovereignty reasons that your data is not subject to the laws of foreign governments. If you would like your data geo-replicated to a different region for enhanced DR scenarios we can provide this service on request.

  • Complete data centre failure protection: In the event of a complete regional failure we can establish a new Reporting Hub service for you based in one of our 3 locations around the world. Australia, Europe or West US. This can typically be done within 1 business day depending on data volumes, region selected and availability of the source Project Online service.

  • For disasters that don't involve an infrastructure failure, restore points are available every 5-10 minutes in the preceding 7 days, with weekly full backups available up to 28 days, extendable up to 10 years on request.

Data Flow:

  • Updates to SQL database updates from your tenant: 99.8%

SSRS rendering

  • Availability of report execution: 99%

Complete solution

  • Simultaneous availability of all components: 99%

When failing the SLA

Note

These SLA's don't apply to BYOD customers who host their own database, as many of the factors are outside our control.

While Sensei will take every precaution, and expend all necessary effort to ensure that the risk of a breach of the SLA is minimized, there may be circumstances outside of our control, or the occurrence of unlikely events that may cause the threshold to be reached. Since the service is not typically characterized as 'Mission Critical' we do not provide any remedy, compensation or refunds of fees already paid, instead we may consider discounting the cost future product renewals at our sole discretion.

Operations

Once provisioning is underway the Reporting Hub details screen will be presented, where the various operational functions for the Reporting Hub Instance can be administered.

Update Authentication Details

This operation is used to return to the screen seen during provisioning, allowing for the authentication method to be changed or refreshed.

  1. From the Operations section, click Update Authentication Details.

  2. Select the required Authentication Method.

    Note

    Service Account is our recommended methodology, and is required if utilising the Integration Hub product.

  3. If required update the Username (note by default the Username used during application of authentication method will be applied. Next, enter your Password.

    Important

    In the next step, please ensure you click the link in Open SharePoint App to trust - the Update Reporting Hub Instance button may not be available until you do so.

  4. Click the Update Reporting Hub Instance button. You have now completed updating your authentication details.

Create Report Designer

This operation is used to create a Report Designer account for the current user, as well as a firewall exclusion for the current public IP address.

  1. From Reporting Hub, select the required Reporting Hub Instance to edit, by clicking on the Edit button within the Operations column.

  2. From the Operations section, click the Create Report Designer button.

  3. From the Your Report Designer credentials section, click on the Create Account button.

  4. A new account will be created based on the current user's login credentials.

Setup Secure Store

This operation is used to view the instructions and credentials needed to setup the Secure Store service within SharePoint Online. Using the Secure Store service is best practice when using Excel Services and necessary for some BDC scenarios.

  1. From the Operations section, click Setup Secure Store.

  2. From the Configure Secure Store in SharePoint Online for Reporting Hub section, click the Open Secure Store Service Application in SharePoint admin center link.

    Note

    The SharePoint admin center will launch in a new browser tab, automatically navigating you to the secure store page.

  3. From the Edit tab on the ribbon, locate the Manage Target Applications group and click New.

    Note

    Target Application Settings Steps 4-7 demonstrates how to enter the required data with the Target Application Settings section.

  4. Enter the Target Application ID:

    • Navigate to the Sensei Hub browser tab, and from the Configure Secure Store in SharePoint Online for Reporting Hub, copy the Target Application ID.

    • Navigate to the Create a New Secure Store browser tab, and within the Target Application Settings section, paste the Target Application ID you just copied from the Reporting Hub into the Target Application ID field.

  5. Enter the Display Name:

    • Navigate to the Sensei Hub browser tab, and from the Configure Secure Store in SharePoint Online for Reporting Hub, copy the Display Name.

    • Navigate to the Create a New Secure Store browser tab, and within the Target Application Settings section, paste the Display Name you just copied from the Reporting Hub into the Display Name field.

  6. Enter the Contact E-mail:

    • Navigate to the Sensei Hub browser tab, and from the Configure Secure Store in SharePoint Online for Reporting Hub, copy the Contact E-mail.

    • Navigate to the Create a New Secure Store browser tab, and within the Target Application Settings section, paste the Contact E-mail you just copied from the Reporting Hub into the Contact E-mail field.

  7. Confirm the Target Application Type is correct:

    • Navigate to the Sensei Hub browser tab, and from the Configure Secure Store in SharePoint Online for Reporting Hub, note the Target Application Type.

    • Navigate to the Create a New Secure Store browser tab, and within the Target Application Settings section, confirm the Target Application Type is the same as the Reporting Hub Target Application Type; if it isn't use the drop-down arrow to adjust.

    Note

    Credential Fields Step 8 demonstrates how to confirm the data within the Credential Fields section.

  8. Confirm the Credential Fields are correct:

    • Navigate to the Sensei Hub browser tab, and from the Configure Secure Store in SharePoint Online for Reporting Hub, note the Credential Fields.

    • Navigate to the Create a New Secure Store browser tab, and within the Credential Fields section, confirm the Field Name and Field Type for the fields displayed are correct.

    Note

    Target Application Administrators Step 9 demonstrates how to enter user(s) who will be provided access to manage the Target Application.

  9. Enter user(s) who will be Target Application Administrators:

    • Navigate to the Sensei Hub browser tab, and from the Configure Secure Store in SharePoint Online for Reporting Hub, copy users listed in the Target Administrators area.

    • Navigate to the Create a New Secure Store browser tab, and within the Target Application Settings section, paste the list of users you just copied from the Reporting Hub into the Target Application Administrators section.

    Note

    Members Step 10 demonstrates how to enter users and groups that are mapped to the credentials defined for this Target Application.

  10. Enter user(s) who will be Members:

    • Navigate to the Sensei Hub browser tab, and from the Configure Secure Store in SharePoint Online for Reporting Hub, copy users and groups listed in the Members area.

    • Navigate to the Create a New Secure Store browser tab, and within the Target Application Settings section, paste the list of users you just copied from the Reporting Hub into the Members section.

  11. Click the OK button when steps 1-10 have been completed.

Manual Database Refresh

Schema changes to the database are normally made overnight, however by using this operation they can be actioned immediately. A manual database refresh may also be useful after a period when the authentication method has expired to rapidly bring the database up-to-date.

  1. From the Operations section, click Manual Database Refresh.

  2. From the Force a database refresh section, click the Start database refresh button.

Firewall Rules

In the case where ranges of Public IP address space need to be white listed, adding manual firewall rules can be useful.

  1. From the Operations section, click Firewall rules.

    Note

    You must already have a reporting designer account and only want to add, remove or update firewall rules.

    Note

    Edit a Firewall Rule Steps 2-4 demonstrates how to edit a Firewall Rule IP range.

  2. Within the Firewall Rules section, click on the edit icon.

  3. Update the Firewall Rule with a new IP.

    Note

    You can only update the IP range, not the actual Firewall label.

  4. Click on the Save icon.

    Note

    Delete a Firewall Rule Step 5 demonstrates how to delete an existing Firewall Rule.

  5. Click the Delete icon.

Permissions

This setting allows the Partner or Customer Primary admins to create permissions for users from domains that are associated with their organisation tenant to access the Sensei Hub to create Report Designer database credentials and their own firewall rule; a single firewall rule for their Report Designer credentials is created using the current IP address from which they click the Create Report Designer button.

  1. From the Operations section, click Permissions.

    Note

    Add a permission Steps 2-4 demonstrates how to create a new user permission.

  2. Click the Add Permission button.

  3. Enter the new user's email address.

  4. Click the Save icon.

    Note

    Delete a User Permission Steps 5-6 demonstrates how delete an existing user permission.

  5. From the listed permissions, locate the existing user permission that requires to be deleted.

  6. Click the Delete icon.

Linked Site Collections

There are a few reasons why your organisation might make use of the capability to store project sites in your SharePoint online environment in multiple SharePoint site collections. Our company described this capability in detail through a blog post when the feature was introduced in 2017.

How it works

Typically, the choice to leverage multiple site collections to house your project sites has to do with the fact that your number of projects requiring sites exceeds 2000; the limit for subsites within a single site collection. Additionally, when running Project Online at scale, Microsoft strongly recommends that each Enterprise Project Type (EPT) create a project site in its own site collection.

Note

The 2000 sites per site collection guidance is a Microsoft limitation, not a limitation in the Sensei Reporting Hub.

The challenge that was previously presented to Sensei clients when leveraging multiple site collections to store their project data is that the data within these site collections was not synced to the Reporting Hub. To support usage of multiple site collections in Project Online Sensei introduced a feature in the Sensei Hub called Linked Site Collections.

The way that this works is that a parent Reporting Hub database is created for the primary Project Web App (PWA) site collection, and "child" Reporting Hub databases are created for the separate site collection(s) that house project sites for one or more Enterprise Project Types (EPTs).

Data is synchronised from both the parent Project Web App (PWA) site collection and any "child" site collections to the corresponding Reporting Hub instance. All data is visible in the parent Reporting Hub instance as if the data had originated from the parent site collection.

Once linked, all the SharePoint data in the child databases will be available in the same/original appropriate views of the parent database.

This allows for:

  1. The Reporting Hub to distribute the load of any queries and handle scenarios where there are significant amounts of SharePoint data.
  2. All existing reports to function as expected without any modification as the data is added to the appropriate views in the parent Reporting Hub.

  1. From the Operations section of your Reporting Hub instance, click Linked Site Collections.

  2. The maintenance page we will list all site collections that have project sites referred to by the current one (parent site collection) and their linked status.

    Note

    A site collection can have the following statuses (messages shown where relevant):

    • Link applied and has referenced project sites within it
    • Referred to and available. Consider linking. - Not linked but references a project site and could be linked
    • Referred to and not available. Consider deployment. - Checkbox is disabled and unlinked as it has a referenced project site, but does not have a deployed Reporting Hub instance to link
    • Not in use here, can be un-linked. - Linked but doesn't reference a project site and could be unlinked
    • Not in use here, can be linked. - Not linked but a Reporting Hub instance has been provisioned, and could be linked if planning to provision project sites for this PWA instance within
    • Link application pending. or Link removal pending. - Awaiting the application/removal of the links between the Reporting Hub databases
    Note

    Linked Database Refresh Step 3 advises of the nightly refresh and demonstrates how to trigger a manual update.

  3. The linking of databases is applied on the nightly refresh of the database or can be triggered manually by the administrator, by clicking on the triggered manually link through the Sensei Hub.

    Note

    Once linked, Reporting Hub will be able to:

    • Return the SharePoint project site data in the child site collections.
    • Retrieve data from the child site collection that is part of an Integration flow leveraging Sensei's Integration Hub (if applicable).
    • Data will be updated and visible in the parent Reporting Hub instance views. Data will be updated and visible in the parent Reporting Hub instance views.

Link a Site Collection the following seven step process demonstrates how to configure an additional site collection for use with project sites and have it appear in Reporting Hub.

Steps must be undertaken within the required environments

  • blue = Office 365
  • green = Project Web App
  • purple = Sensei Hub
Step Details
In the SharePoint admin center in Office 365, created a new PWA enabled site collection by choosing New Private Site Collection with Project Web App.
Set the Permissions Mode for the new site collection to Project Permissions Mode.
Create the new Reporting Hub instance for the newly created site collection.
Create a new Enterprise Project Type (EPT) in the parent PWA
During the configuration of the EPT, set it to use the newly created child Site Collection for Project Site creation and input the appropriate URL. Also ensure that the EPT is set to Automatically create a project site on next publish.
Create a new project using that newly created EPT and ensure that a project site is also created in the corresponding child site collection.
Use the Sensei Hub to administer the parent Reporting Hub instance and link the child instance to
Note

At this time, Sensei does not support non-PWA enabled site collections for linking.

Note

Your Sensei partner must add the managed site collection first.

System Status

The Reporting Hub status is accessible on the Project Online PWA Settings (aka Server Settings) page.

  1. Click the Settings Gear in the upper right hand corner to open the settings menu.
  2. Click PWA Settings in the settings menu.
  3. Locate Sensei section on the PWA Settings page.

Click Reporting Hub status to open a dialog that displays the current status of this Reporting Hub instance.

Health

Status of the Reporting Hub instance.

Service Checked Description
Overall status Summary health indicator.
Daily maintenance Every night a validation of the Reporting Hub instance, Project Online settings and all connections is performed.
Daily full sync After the daily maintenance the Reporting Hub full sync is started to ensure all data is up-to-date.
Daily index optimize After the daily full sync the Reporting Hub database is optimized for reporting usage.
Hourly maintenance Every hour the Reporting Hub validates the integrity of the Reporting Hub database.
Also Project Online engagement data is synchronized.

Each service checked can have one of the following statuses:

Status Description
Green Tick Everything is awesome.
Warning A minor issue occured during the last run. If the issue persists you can contact Sensei Support.
Error The last job failed.
Clock The job is being queued and will soon start processing.
Gears The job is currently being processed.
Note

Hover over the status indicator to get additional details.

Activated Features

Displays a list of all Sensei products activated in this Project Web App site collection.

Sensei Hub Administration

Click Sensei Hub Administration to open the Sensei Hub associated with this environment.

Power BI Embedded

Service Description

Sensei Power BI Embedded is an easy way to distribute standardised Power BI Reports based on Reporting Hub data to a wide audience of Project Online users without additional licensing costs. It provides complimentary functionality to the Power BI service from Microsoft:

Feature Sensei Power BI Embedded Power BI Free Power BI Pro Power BI Premium
Project Online context sensitive embedding
Use without requiring a Power BI Pro license
Paginated Reports (SSRS)
Embed in SharePoint
Scheduled Refresh
Email subscriptions
Multiple Data Sources
Custom Reports *See Custom Reports
Automatic/on-demand refreshes Dataset > 4 hours old Daily 8 per day 48 per day
Max Dataset size 1 GB (50MB PBIX size) 1 GB 1GB 10 GB

Custom Reports

Sensei Power BI Embedded is used to distribute pre-configured reports to Project users, however in some cases these reports will need to be customised to support changes in customer banding or fields. Enterprise tiers of both Empower PPM and Reporting Hub support the facility for uploading custom reports via the installed SharePoint Webpart.

Note

Power BI Embedded is not intended to be a replacement for Power BI Pro. Power BI Embedded has resource and refresh limitations that make Power BI Pro a better choice for custom report development.

Downloading Existing Reports

An existing report can be downloaded and customised locally using Power BI Desktop, then subsequently re-uploaded back into the service.

Understanding Data Refresh

Power BI Refresh behaviour varies by storage type:

  • DirectQuery mode
  • Import mode

Only Import Mode reports require a data refresh, as DirectQuery mode reports are always refreshed when viewed.

When an Import Mode report from the Sensei Power BI Embedded service is displayed, data from the last successful refresh is displayed. If that data is more than 4 hours old, a request to refresh the data will be made. When the background data refresh is completed the report will be updated automatically in the user's browser.

Import mode datasets are limited to 1GB storage.

If the data refresh doesn't complete because of an error, the problem will be shown in a banner at the top of the report.

In order to optimise custom reports and minimise data refresh times please refer to the following resources:

How to get it

Sensei Power BI Embedded is an optional Reporting Hub add-on. You can purchase it via:

  1. Empower PPM subscription.
  2. Reporting Hub Enterprise tier subscription.
  3. Separately, contact Sensei for details.

Technical Information

Visualisation Types

Power BI supports three different types of visualisation:

  • Report
  • Dashboard
  • Q&A / Dataset
    • Q&A only works with Dataset configured in Import Storage mode. Dataset in Dual or DirectQuery Storage mode are not available.
Query Parameters

During the upload of a Power Bi report the following query parameters are overridden.

  • Server
    • Fully Qualified Domain Name of the Reporting Hub database server.
    • Example: tcp:au1-rh.database.windows.net,1433
  • Database
    • Name of the Reporting Hub database on the database server.
    • Example: contoso_sharepoint_com_sites_pwa
  • Tenant SharePoint URL
    • Example: https://contoso.sharepoint.com
  • PWA Site Path
    • Relative path of the Project Online site collection.
    • Example: /sites/pwa

Auto Filter

In Report mode you can define report filter to be automatically populated. The filter are set on load. If the Filter panel is displayed the user can modify the filter. This feature works with all reports.

You can choose from the following options:

  • Project Uid
    • GUID of the current project.
    • Only on PDP pages.
    • Example: 9186c1ad-2e73-4d78-8176-cc527a9ac447
  • Current User - EMail
    • SharePoint Mail of the user rendering the report.
    • Example: fabian.schmidt@sensei.cloud
  • Current User - Id
    • SharePoint User id of the user rendering the report
    • Example: 30
  • Current User - Login name
    • SharePoint Login name of the user rendering the report.
    • Example: i:0#.f|membership|fschmidt@sensei.cloud
  • Current User - Resource Uid
    • Project Online resource Guid.
    • Example: b037e043-2a9b-e611-80cd-00155de4d408
Note

Power Bi Table and Column names are case senstive.

Report Security

In Report and Q&A mode you can define Security Role to be used. The Report must be prepared by setting up Security Roles.

See Row-level security for instructions how to create Roles.

The following Roles can be used in the Reporting Hub:

  • ProjectUid
    • GUID of the current project or Guid empty when not found.
    • Only on PDP pages.
    • Example: 9186c1ad-2e73-4d78-8176-cc527a9ac447
  • CurrentUser_Mail
    • SharePoint Mail of the user rendering the report or `` (space) when the current user has no EMail.
    • Example: fabian.schmidt@sensei.cloud
  • CurrentUser_Id
    • SharePoint User id of the user rendering the report
    • Example: 30
  • CurrentUser_LoginName
    • SharePoint Login name of the user rendering the report.
    • Example: i:0#.f|membership|fschmidt@sensei.cloud
  • CurrentUser_UPN
    • SharePoint Login claim value of the user rendering the report.
    • Example: fschmidt@sensei.cloud
  • ResourceUid
    • Project Online resource Guid or Guid empty when not found.
    • Example: b037e043-2a9b-e611-80cd-00155de4d408

The value is stored in the USERNAME() DAX expression.

Example:

Note

If the message Can't display this visual. appears after selecting a Role, then the Report is configured incorrectly.

Read Scale-out support

Feature Description

This is an Enterprise Reporting Hub customer feature that not only provides for high availability, but also performance benefits.

As part of Azure SQL Databases high availability architecture, each Enterprise (Premium) database actually has a primary copy which can be used for reads and writes, and then several secondary replicas are read only and allows you to spread the load of read only query execution. The Read Scale-Out feature then allows the performance of the primary to be protected for write operations, while any designated read only queries can be load balanced across the replicas.

For our Enterprise tier customers this means reports can take advantage of the performance benefits without additional cost simply by updating the connectionstring used. See the connectionstring below and specifically the "ApplicationIntent=ReadOnly" property.

Server=tcp:<server>.database.windows.net;Database=<mydatabase>;ApplicationIntent=ReadOnly;User ID=<myLogin>;Password=<myPassword>;Trusted_Connection=False; Encrypt=True;
Note

What this means is that no report queries will be able to make writes to the database.

SSRS Reporting Usage

By default we now provision all reports uploaded via the "SSRS Reporting Hub Report" Webpart with a read only datasource connectionstring. This means Enterprise customers will get any performance benefit automatically.

As the default report viewer account does not have write permissions this should not create any issues. If for certain specific scenarios your report does require write access, please contact our Customer Care team.

Power BI Usage

There is currently no explicit built in support for the ApplicationIntent property in the Azure SQL Database connector. We will be monitoring the situation and provide an update when things change.

Indirect support is provided by enabling the "SQL Server Failover support" option in the Data Source settings Power BI will add the property to the connectionstring.

Snapshot

Snapshot is an out of the box feature for the EPM (Project Server) User View within Reporting Hub that works by storing every day the changes you have made to all projects into history tables so that you are enabled to undertake trending per week, per month, per quarter over time for all of your projects without configuration!

By default, the frequency of the data capture occurs during nightly maintenance, however depending on your needs Sensei can assist with the creation of customised frequency captures.

With this feature you will be able to create trend analysis reports like the following:

Sample Chart

Sample Chart

Note: This is not meant to be instructions on how to build reports or use SQL Server. For additional training on these tools consult your Sensei account manager.

Trend Views

When Snapshot is active you will be able to make review trends on your projects by week, month or quarter. It is important to note that history will only commence being collected when the feature is activated in your Site Collection.

Trend View

The following views will be available:

  • MSP_EpmProject_WithCustomFields_UserView_ByWeek
  • MSP_EpmProject_WithCustomFields_UserView_ByMonth
  • MSP_EpmProject_WithCustomFields_UserView_ByQuarter
  • MSP_EpmProject_WithTaskBaseline_UserView_ByWeek
  • MSP_EpmProject_WithTaskBaseline_UserView_ByMonth
  • MSP_EpmProject_WithTaskBaseline_UserView_ByQuarter

Each view will contain the relevant columns for that part of the project user view with additional fields allowing a user to chart and report on the specified time dimension. For each time period the state of the project for that point in time will be returned; these dimension columns are:

By Week

  • TimeByDay - Datetime for the last minute of the week
  • TimeYear - The year of the week
  • TimeWeekOfTheYear - The week of the year

Snapshot Week Data

By Month

  • TimeByDay - Datetime for the last minute of the month
  • TimeYear - The year of the month
  • TimeMonthofTheYear - The month of the year

Snapshot Month Data

By Quarter

  • TimeByDay - Datetime for the last minute of the quarter
  • TimeYear - The year of the quarter
  • TimeQuarter - The quarter of the year

Snapshot Quarter Data

These will enable you to establish the series in your chart and do any additional grouping.

Snapshot Moments

Additionally, you can track the trends against specific points in time by executing a stored procedure.

To create a new moment the following Stored Procedure should be executed using the report designer account or higher.

exec [Snapshot].[CreateSnapshotMoment]  @Name='My Snapshot', @CreatedBy='John Smith', @MetaData='<insert any metadata you want… e.g. xml or json containing a description>'

Or… as all parameters are optional

exec [Snapshot].[CreateSnapshotMoment]

When executed, the stored procedure will check for any changes across the project data and register the moment in the snapshots table.

These moments will add a time by day grouping to the following views with a project row per snapshot moment, along with the values passed to the stored procedure:

  • MSP_EpmProject_WithCustomFields_UserView_BySnapshot
  • MSP_EpmProject_WithTaskBaseline_UserView_BySnapshot

Snapshot Moment Data

Via customer care or partner with sufficient access, snapshot moments can be created or updated for dates and time in the past, and as long as history data was stored for that point in time, you can review the state of the projects for that moment.

Sample: Creating a snapshot moment via Flow

To automate the creation of a snapshot moment via Microsoft Flow it is straight forward… and in turn trigger it via a flow button, a timer, a power app or any other system that can trigger a Flow.

Using the Azure SQL Server connection details for your report designer account use the "Execute stored procedure" action, providing the parameters as needed.

Sample Flow Action Usage

SSRS Reporting

With the Reporting Hub solution, the SQL Azure data source and SSRS renderer can be used together to create a custom reporting experience for your users. You can create custom reports against any data included in the Reporting Hub database for your tenant.

Begin this process by acquiring a tool to author SQL Server Reporting Services reports. This can be:

Open Report Builder and chose to start with a blank report.

You will be presented with the report canvas. Right click on the Data Sources node of the Report Data tree, and select "Add Data Source"

In the new data source window:

  • Enter a name for the data source

  • Select "Use a connection embedded in my report"

  • Select "Microsoft SQL Azure" as the connection type

  • And then press the build button.

You will then be presented with the data source credentials screen. On this screen enter your Report Designer Credentials as SQL Server credentials, and the specific database you have access to. It is normal not to be able to browse for the database -- you will need to enter the specific database you have access to. Also -- remember to check the "Save my password" checkbox.

Press the Test Connection button and you should be shown the "Test connection succeeded" message.

If you receive a failure message this could be because:

  • The username /password is incorrect
  • Your current location is not in the list of acceptable locations to be accessing the data source. (Whitelist). See Report Designer Credentials in this document.

If you continue to have issues with your provided credentials, please log a support ticket at https://ipmo.zendesk.com

Right click on the datasets node in the Report Data window and select "Add Dataset"

Give the dataset a friendly name, choose to embed the dataset into the report, select the Reporting Hub datasource and then press the query designer button.

This will contact the database for a list of available tables and views. While it is possible to query the tables directly, a series of views have been created to help with the query process.

The available views for reporting are divided into several groups organised by prefix:

  • MSP -- View that replicates the Project Server reporting database behaviour. Useful for porting legacy reports to Project Online

  • MSPLT - View that replicates the Project Server reporting database behaviour for lookup tables. Useful for porting legacy reports to Project Online

  • WSS_PWA -- Views of SharePoint lists collected from the root PWA site.

  • WSS -- View of SharePoint list data collected from Project Sites

Once you have selected the data you need for your report -- press OK and then okay again to return to the report canvas.

From this point the construction of the report is largely up to your own creativity -- but our example report is simply going to list out a few fields about our projects.

Right-click and insert a table

Drag and drop fields into the data columns of the table, and set the title of the report.

Then press the button to preview the report:

Save the report to an "RDL" file onto your local computer or into your own source control system.

Publishing reports

After having created a report like the one in the previous section, you can upload this report to Reporting Hub so that other people can view it.

Log onto your Project Online instance, and select ⚙ -> Add an App and choose a Wiki Page Library and give it a descriptive name.

Navigate to the page menu and select edit:

Delete the existing content and then select Add App Part from the Insert Tab

Add the App Part to the page:

Use the arrow in the top right corner to edit the webpart

Choose a report from your local computer to upload and then press "Upload"

Once completed the uploaded file information will be displayed:

There is also the opportunity to download a copy of this report for future modification.

Press the button to save that page and render the report.

You can now share this page with your stakeholders.

Creating reports on PDPs

One of the best way to expose reports to users is to create them on a PDP.

This has the advantage that the report is easily discoverable, and that the report can show just information that is relevant to the project being viewed.

The first step is to add a parameter to your report that allows it to be filtered automatically.

Open your report in report builder and right-click the Parameters node of the Report Data tree to add a new parameter.

Set the options for the parameter:

In this case the name of the parameter is very important. For Reporting Hub to automatically populate the parameter it needs to be called one of the following names (case insensitive):

  • ProjectUid (recommended for consistency)

  • ProjUid

  • ProjId

  • ProjectId

You can leave the parameter "Visible" because when the parameter is auto-populated it will be hidden. In addition to hiding auto-populated parameters, if there are no other visible parameters then the parameter page will also be hidden from the user.

After adding the parameter to your report -- filter your datasets by the parameter as desired

See SSRS Report parameter for a full list of parameters.

Save the RDL file to your local computer.

To host your filtered report, navigate to an existing PDP, or preferably create a new PDP to host your report by following the instructions to create a new PDP.

Then, follow the instructions in the section on Publishing reports to add the SSRS web part to the page and upload the report.

At completion, you should see your report rendering with filtered data only relevant to your report:

Using this technique, you can create more complex reports that are tailored to be useful on a PDP

Auto Parameters

Some parameters are automatically filled by Reporting Hub. They are hidden from the user if they are found.

  • Project Id

    • GUID of the current project.
    • Only on PDP pages.
    • Example: 9186c1ad-2e73-4d78-8176-cc527a9ac447
    • SSRS Parameter names: "projuid", "projid", "projectid", "projectuid"

  • Current User Email

    • SharePoint Mail of the user rendering the report.
    • Example: fabian.schmidt@sensei.cloud
    • SSRS Parameter names: "currentuser", "currentuser_email", "currentuser_mail", "cu", "cu_email", "cu_mail"

  • Current User Id

    • SharePoint User id of the user rendering the report
    • Example: 30
    • SSRS Parameter names: "currentuser_id", "cu_id"

  • Current User Login name

    • SharePoint Login name of the user rendering the report.
    • Example: i:0#.f|membership|fschmidt@sensei.cloud
    • SSRS Parameter names: "currentuser_loginname", "currentuser_login", "cu_loginname", "cu_login"

  • Current User Title

    • SharePoint Title of the user rendering the report.
    • Example: Fabian Schmidt
    • SSRS Parameter names: "currentuser_title", "currentuser_name", "cu_title", "cu_name"

  • Current User Id

    • SharePoint User id (Name Id) of the user rendering the report.
    • Example: 10033fff95968328
    • SSRS Parameter names:"currentuser_userid", "cu_userid"

  • Current User Id issuer

    • SharePoint User id (Name Id) Issuer of the user rendering the report.
    • Example: urn:federation:microsoftonline
    • SSRS Parameter names: "currentuser_useridissuer", "cu_useridissuer"

  • Current Resource Id

    • Project Online resource Guid.
    • Example: b037e043-2a9b-e611-80cd-00155de4d408 or Guid empty when not found ( 00000000-0000-0000-0000-000000000000 )
    • SSRS Parameter names:"currentresource_id", "cr_id"

  • Current User Project Permissions

    • To which project does the current user have "Open Project" permissions. This is cached by Reporting Hub up to 15 minutes.
    • Can have two different formats based on the report settings.
      • If parameter is single value string.

        • JSON array of project Uid.

        • This is recommended for larger customers. As Multi Value with a lot of values have a known limitation (above 1000 values).

        • Example:

          ["00000000-0000-0000-0000-000000000001","00000000-0000-0000-0000-000000000002","00000000-0000-0000-0000-000000000003"]

        • SQL Usage:

          SELECT MSP_EpmProject.ProjectUid
  FROM MSP_EpmProject
  JOIN OPENJSON(@currentuser_readpermission) WITH (ProjectUid uniqueidentifier N'$') AS perm
    ON perm.ProjectUid = MSP_EpmProject.ProjectUid

        • When the current user has no permissions the result is []

      • If parameter is "Multi Value" string.

        • Array of project Uid

        • SQL Usage:

          SELECT ProjectUid
  FROM MSP_EpmProject
WHERE ProjectUid IN (@currentuser_readpermission)

        • When the current user has no permissions the result is 00000000-0000-0000-0000-000000000000

  • SSRS Parameter names: "readpermission", "currentuser_readpermission"

See example Report for an SSRS report using all parameter.

Known issues

Problem: When using multi value parameter the drop-down menu does not extend over the parameter area.

Solutions:

  1. Configure the webpart to have a fixed height.
  2. Minimize and expand the parameter area.
  3. Use single value parameter.

Problem: WSS Data not removed from Deleted sites

This is due to a Microsoft bug that is not consistently firing events on SharePoint site deletion.

Additional limits to be aware of:

  • Enterprise Project Types are not removed from the reporting Hub Database for data integrity reasons.
  • Renaming of Lookup table entries are not propogated to projects until the project is next modified.
  • Engagements are not synchronised in real-time due to the lack of Support from Microsoft. They are synchronised approximately every hour.
  • Integration Hub is not available with SharePoint application authentication method.

Service Boundaries and Limits

The Reporting Hub service aims to replicate data from the source environments as quickly as possible and with as much fidelity as possible, however for operational reasons there are limits to the feasibility of achieving this. Some of these reasons are API related and other are a consideration for the load placed on the Microsoft infrastructure we integrate with.

Known limits:

Entity Limit
Assignment Baselines Timephased per project 2,000,000
Assignments Timephased per project 2,000,000
Assignment Baselines per project 500,000
Assignments per project 500,000
Resource Timephased per resource 2,000,000
SharePoint items per list 50,000
Tasks Timephased per project 2,000,000
TaskBaseline Timephased per project 2,000,000
TaskBaselines per project 500,000
Integration Hub Items per SHarePoint List 1,000
Integration Hub CSV File size limit 2GB

Additional limits to be aware of:

  • Enterprise Project Types are not removed from the reporting Hub Database for data integrity reasons.
  • Renaming of Lookup table entries are not propogated to projects until the project is next modified.
  • Engagements are not synchronised in real-time due to the lack of Support from Microsoft. They are synchronised approximately every hour.
  • Integration Hub is not available with SharePoint application authentication method.

While not unsupported, Sensei recommends staying inside the boundaries provided by Microsoft:

Edit this page