Get your Project Data Directly to Excel

These instructions are specifically for Office 365:

Microsoft Excel allows you to present data directly from Project Insight. Just use the PI API. Microsoft's query interface is the same for both Excel and Power BI. Make sure you can connect to PI from Excel using the instructions to Get Project Insight Data into Power BI Using REST.

Here's an easy example to get started with your first Excel report once you have your API connection working (see link above). Practice with this first and we'll look at the details of the syntax and how it works later so that you can easily modify this for the data you want.

This simple report will get the active projects name, type, status, percent complete and primary project manager.

1. Use the connection string for the "Get Data > From Web" option.
• Use this example and remember to use the instructions referenced earlier for connecting with an API token. You can copy and paste everything here from /api after your full URL (including projectinsight.net).
  https://[your_url].projectinsight.net/api/project/search?isActive=true&modelProperties=Name,ProjectType,ProjectStatus,WorkpercentComplete,PrimaryProjectManager;PrimaryProjectManager:FirstName,LastName;ProjectType:Name;ProjectStatus:Name
  
2. Transform Entire Data Set to Table - Delimiter: None; Extra Columns: Show As Errors
   
   
   
3. In Column 1 Heading click small icon for selecting the correct columns.
   
4. Select Column to display the "Record" cells you want in your worksheet.
   
5. Merge desired cells, FirstName LastName for Primary Project Manager using 'space' as the separator and name the column.
   
   
6. Rename headers for all columns as you would like them displayed in your worksheet.
   
7. Arrange columns in desired order by dragging and dropping them into place.
   
8. Close & Load.
   

Your query for getting and arranging the data you want is now the data source for your worksheet, you can save it and Refresh All from your saved Excel file anytime you want to update your worksheet from real-time PI data.

While the above steps are simple, the REST API References used to construct the query string and see the references can be overwhelming. No worries; we've got your back! Let's look at this string again and break it out into sections which will help you build your own data query:

https://[your_url].projectinsight.net/api/project/search?isActive=true&modelProperties=Name,ProjectType,ProjectStatus,WorkpercentComplete,PrimaryProjectManager;PrimaryProjectManager:FirstName,LastName;ProjectType:Name;ProjectStatus:Name

https://[your_url].projectinsight.net/api
Your base address with /api which is required to access all of the data references

/project/search?isActive=true
You're identifying that the data will come from projects and that those projects are in the active state.

&modelProperties=
modelProperties define which data fields to retrieve. The following sections are the modelProperties we've chosen in this example. Up to the first semi-colon is the list of properties for the project and after the semi-colon is list of properties of those properties:

Name,ProjectType,ProjectStatus,WorkpercentComplete,PrimaryProjectManager;
Which fields do you want in our spreadsheet? APIs call those fields "properties" of the object, in this case, a project. Make a list separated with commas in this section. Upon declaring you want those properties, use a semi-colon to clarify further the desired properties of those properties:

PrimaryProjectManager:FirstName,LastName;ProjectType:Name;ProjectStatus:Name
These are optional. If you don't specify the properties of your model properties, PI will send over the defaults. The default properties are listed here: Rest API Models

Each record may contain many data fields by default, including a unique identifier (GUID). Most frequently, you only want the Name of the field. This section helps to minimize the data downloaded within that record. The pattern is "[the object property] separated with a colon [the data fields we want separated by commas] semi-colon".

We often need more complex data than what we've described here. That's why we work with real-time data retrieval into powerful analytics tools in the first place. A single worksheet often contains multiple queries for different item types using reference data and pivot tables for analysis and presentation. We use Excel as an example because everybody knows Excel. This same method works for most business intelligence tools available today with a few modifications.

Remember, we've got your back! Questions about query strings, data record names and modelProperties are all answered in our PI#enterprise Discussions. Search and if someone has not already asked the question, post your question to get answers from our community.