In the first blog post about reporting with OData, we discovered how to get project data with the standard projectdata feed. We used an URL like https://contoso.sharepoint.com/sites/pwa/_api/projectdata/[en-us] to receive all enterprise custom fields without any filtering. It is a very simple approach to build a list or some graphics, but the performance isn’t good enough for productive environments, especially with time phased data. For this reason some adjustments are necessary, to request just the respective data that you really need in your report. We’ll check out how to do that in this article.
Techniques to increase the performance:
- Each table that’s necessary in the desired report will get an independent data connection.
- Every data feed has a select and/or filter command to minimize the data which is requested from Project Online.
- Timephased data will be reduced to a minimum.
The basic steps are as follows:
- Think about which enterprise custom fields you’ll need to build your report and in which table they are located. OData field names may change from the respective label in the client/PWA. Therefore, you can check the details in the unfiltered OData feed in the browser or in Excel (via Data -> Form Other Sources -> From OData Data Feed with the $metadata command behind). This is also the right place to verify which table contains the respective field, that you want to include in your report. Please note, that the enterprise custom field names are case sensitive.
- If your desired report contains fields from multiple tables, don’t forget to collect the fields which should connect them to each other, e.g. ProjectId, ResourceId.
- Build the OData feeds with a select command in order to be able to request just the fields that you really need.
The logic is like this: https://contoso.sharepoint.com/sites/pwa/_api/projectdata/[en-us]/Tablename?$select=Fieldnames
Example: https://contoso.sharepoint.com/sites/pwa/_api/projectdata/[en-us]/Projects?$select=ProjectName, ProjectWork, ProjectActualWork
Don’t wonder, e.g. a resource dashboard may contain up to 6 or even more data connections, depending on the number of the tables.
- If there’s any data that you don’t need, filter it!
The logic is like this: https://contoso.sharepoint.com/sites/pwa/_api/projectdata/[en-us]/Tablename?$filter=Filtercriteria&$select=Fieldnames
Example: https://contoso.sharepoint.com/sites/pwa/_api/projectdata/[en-us]/Projects?$filter=ProjectType ne 7 &$select=ProjectName, ProjectWork, ProjectActualWork A detailed list about the filter commands is listed here. Already the two commands ne (=not equals) and eq (=equals) do allow a variety of optimization, e.g. like above to filter out the superfluous timesheet project.
- Test all the adapted OData queries in the browser or within Excel to verify if they are working correctly. If there’s a mistake in it, you’ll get a result like this:
- Add the data connections one by one (each connection = one table with a select/filter command) to the workbooks data model:
- Build the relations from table to table manually by clicking on Data -> Relationships, or by Power Pivot for Excel.
- Start creating the tables and graphics in your report.
- If you’re happy with the result, upload the report to a document library in SharePoint and share the link with your colleagues.
Maybe you’ve already noticed that if we’re using the ProjectData feed, we just get standard fields, enterprise custom fields and some additional fields from the Project Site (e.g. Risks, Issues). In the next blog, I’ll give you a quick tour how we can request additional SharePoint fields from any site in your tenant.by