The Cora PPM API & Power - BI Connector User Guide

Using the API 

Please contact your system administrator to ensure the API is configured and switched on.

Note: The API checkbox in My Installation > Features is a legacy API that is still used by certain customers. All new customers are recommended to use Cora’s new API which must be enabled by contacting your systems administrator. The API in the image below is the incorrect API for this tutorial

Accessing the API index menu is very easy once it is activated. 

1. Copy the URL from the CoraPPM site you are using:

2. Paste the URL into another tab.

3. Remove /menu.aspx

4. Now add _api/swagger

5. Your new URL should look like the example shown below:

6. Now press enter, you will then be greeted with the API index menu.

7. Bookmark this page for ease of access later.
  

Exporting data using Cora’s API allows users to further analyze information for reports or for connecting with other applications. In this exercise Excel will be used to get information from CoraPPM. 

Project Data

In this example we will demonstrate how to export project information to excel such as project status, number of projects, project types, etc. 

1. First, we must create and access token. Log into your Cora PPM.

2. Navigate to the API menu in Administration

3. Navigate to Personal Access Tokens

4. Click Add

5.Name your token. It can be named after its use case etc.

6. It is also possible to add a description if required.

7. Set an expiry date. It is only possible to set it to a maximum date of 1 year from today. You can come back at a later date and push it out again. Once the date passes it will block the API call from processing.

8. Take note of the token. If you ever need to generate a new token for security reasons click Generate Token Value

9. Navigate to Projects in the API index

10. Click on Projects, this will expand a selection of options available

11. In this example we will use GET /projects. The GET /projects returns a large amount of information. ­ When you click on     it expands the screen to show a menu similar to what is shown below.

12. Within CoraPPM please find the highest level of the programme structure of projects you wish to return. In this example Enterprise Portfolio will be selected:

Identify the Project ID of the root project:

In this example it is 843.

13. Enter the project ID in rootProjectID

and then click Try it out!

Note: You can limit the data you wish to return using the additional fields below. When you perform the query, you will then see the information required in order to limit the returned data. Please also note the Data Type that is required when entering a filter. For example, a project type will return its description but also its unique ID:

As the projectTypes query requires data type integer you must always use this. In this example if you wish to return projects with project type “Transformation”, enter “7”.

14. You will then be presented by a curl field and a response URL. This is the information you require to open a connection between this request and Excel.

15. Open Excel and navigate to Data, click on From Web

Note: This tutorial is using Excel 2019, how you navigate within Excel will depend on the version you are using.

16. A popup will open in excel, choose Advanced

17. Add the URL from Request URL in the API index page into URL parts within the Excel popup.

18. Now you need to add the parameters. You will always need a Accept header and an Authorization header. This data is stored within the Curl field in the API index.

   The data we require is within the Curl:

19. For Accept copy application/json and paste it into the field beside the Accept dropdown in Excel

20. For Authorization, insert private-token followed by space and then the key you created in Cora PPM earlier.

Example (Remove <>): <private-token Msgsj56gDj58nYEzQEjyPKcbg85B9E>

 21. Your popup in excel should now look like this:

22. Click OK 

23. Click Connect in the next popup screen:

24. You are now at the Query Editor screen. Each query is stored as a record.

25. Now convert these records to a table. Choose Convert To Table on the top left of the screen:

26. A popup will appear, select OK:

27. The records are now converted to a table that has not yet been expanded, click on the arrows as shown below:

28. Choose the data you wish to keep; in this example we will return all the information available. Select OK

29. The data should now look similar to the example shown below:

30. Now choose Close & Load

Note: You can use the option Refresh Preview as long as the login token you have used for this query is still active. In most cases the token will time out after 15 minutes if no action is performed against it. This timeout time can be extended however precaution must be taken to consider security and the time a site login can be left active.

31. The information has now been converted to a table and can be interpreted using the tools provided by excel.

Timesheet Data

In this example we will export timesheet data, it is very similar to the previous example therefore we will make use of the same steps again.

1. Open the API index page.

2. Navigate to timesheets

3. In this example we will return all timesheet information. Select GET /timesheets 

4. This will return all relevant response information.

5. Repeat steps 9 – 26 from the previous example, following these steps will export all timesheet information from CoraPPM and import into excel.

Register Information

In this example we will export register information. This is useful to get a large data dump of all register information within CoraPPM.

1. First, we need to get the ID of the register we wish to get the information from. Navigate to ProjectRegisters in the API index page and click GET /projectRegsiters

2. Ensure you have authorized the connection then click Try it out!

3. The response body will contain a large amount of information, it is recommended to copy this information to a text editor in order to easily find the ID you want. In this example we will return information from the register called Action Log. In the image below you will see this has an ID of 9

4. Now navigate to GET /projectRegister/{id} and enter the ID of the register you wish to return all information from.

5. Repeat steps 9 – 26 from the first example, following these steps will export all register information from CoraPPM and import into excel.

Smart form Data

In this example we will export Smart Form information from CoraPPM.

Note: In this example it is assumed the user knows the ID of the Smart Form page they wish to pull the information from. The API will soon support the possibility to use a GET request to find all Smart Form IDs

1. In this example we will use CustomPages from the index page.

2. Select GET /customPages{id}

3. In this example we will use ID5

4. Ensure your connection has not timed out and is authorized.

5. Click Try it out!

6. Repeat steps 9 – 26 from the first example, following these steps will export all information from the custom page with ID5 from CoraPPM and import into excel.

Note: In this particular example it exports all information from a custom page called Business Case

PowerBI is an extremely powerful visualization tool for large sets of data, you can extract data from CoraPPM using our API and then further manipulate it using PowerBI.

In this example shown below the data was imported using the same method used to import to excel, you are then left with a table similar to this:

You can then use the visualization tools to create reports that are suitable for you. 

Using the Cora PPM Power BI Desktop Connector (Beta)

Cora has acknowledged the demand for using external reporting tools such as Power BI to create interactive datasets. A beta connector now exists that allows users to connect with a Cora PPM site more easily.

There are a few items to note as of December 2020

• The connector does not filter any information it retrieves. For example, you cannot ask for the information for one project. You have all the data; you just need to develop the report that meets your needs.
• The connector does not retrieve any custom field information as a call to get every custom field value does not exist within the API.
• The connector uses the Personal Access token to retrieve the data. This is a long-life token that you can add via the administration screens.
• The “Beta” will go away when we are registered and approved by Microsoft.

Please request the beta connector file from the relevant contact within Cora, your account manager will be best suitable to help with this.

As this connector is pending approval from Microsoft it must be manually added to Power BI. Open Power BI and follow these instructions:

1. Click File

2. Options and Settings

3. Options

4. Security

5. Data Extensions

6. Select on the option: “(Not Recommended) Allow any extension to load without validation or warning

7. Save and exit Power BI as a restart is required

1. Create a folder under your Documents folder and call it “Microsoft Power BI Desktop”

2. Create a folder under” Microsoft Power BI Desktop” and call it “Custom Connectors”

3. Add the CoraDataConnector.mez file in to this folder

4. Launch Power BI and then click on Get Data as shown in the image

5. Navigate down the menu until you see CoraPPM (Beta) and click Connect

6. Power BI will warn that you are connecting to a third-party service, click Continue

7. Add your site URL to the pop-up screen with ­_api appended at the end. The next image will show how it should look.

8. Click OK

9. Insert your previously created personal access token

10. Click Connect

11. Select the data you now wish to utilize in your Power BI Reports. 

Please note the specifications of your PC may influence the amount of data that can be processed, be considerate and only select data that you require as the datasets can be quite large if there is a large portfolio of projects.

12. The data has now been added to Power BI. This data can be refreshed for as long as the personal access token is valid.