OData Reporting Service
- RecordPoint (Unlicensed)
Overview
The Reporting OData Web Service for RecordPoint provides data from the RecordPoint Storage layer to clients in an OData compliant format. The service is designed to be consumed by client applications that support OData. The client application is not enforced by the service and as such, customers have the ability to select any number of reporting applications for RecordPoint.
Background
The service was created to replace the existing ReportService.asmx which was implemented to support SQL Server Reporting Services (SSRS). The existing service used standard RecordPoint Search to return results to SSRS which could then be manipulated into Reports and other formats. SSRS is an ageing technology which has technical issues in some complex environments which made it difficult to support as an enterprise reporting solution for RecordPoint. The OData Service was developed to enhance the reporting capabilities of RecordPoint. Since OData is an open standard, customers have the ability to select their reporting tool of choice that supports OData, thus removing the dependency on SSRS.
RecordPoint OData Reporting Service
The web service has the following implementation details:
Access
The following table outlines the access details of the service.
| Item | Value | Details |
|---|---|---|
| Service Name | RecordPoint.SI.WebServices.ReportODataService.svc | The name of the OData service |
| Service Path | /_layouts/15/RecordPoint/Services/ | The server relative path where the service resides |
| Authentication | SharePoint | Supports any SharePoint Authentication Scheme |
| Data Access | Logged on User | The data returned is trimmed based on the current user |
Data
The data returned by the service is dependent on the configuration of RecordPoint. The schema is dependent on the fields within RecordPoint. Managing the fields returned from the web service is performed from the general settings in RecordPoint site administration.
The service supports a subset of the full OData URI Conventions. The following details outline both the data schema and URI support that the service provides
Schema
The following schema is dependent on the Record Content Types in RecordPoint. The table outlines the content in RecordPoint mapped to OData Resource Paths. The schema can be determined by using the OData Service Metadata Document which is located by appending a $metadata to the url of the service.
Eg. http:// servername /_layouts/15/RecordPoint/Services/RecordPoint.SI.WebServices.ReportODataService.svc/$ metadata
| ResourcePath | Details |
|---|---|
| /Record | Contains the MetaData for Records |
| /File | Contains the MetaData for Files |
| /Box | Contains the MetaData for Boxes |
Paging
The dataset size returned is configured in RecordPoint, and has a default value. Customers can set the page size from the Search Settings page in RecordPoint. OData client reporting RecordPoint Core OData Reporting Service Page 8 applications like PowerBI and Excel recursively fetch the data from the server using the ‘nextlink’ in the service data before any operations. If the customer wants to fetch only a specific subset of the data, OData query options like $top and $skip should be used. The server side paging will be turned off when using these options, however the configured upper limit in the search settings page will always be respected.
RecordPoint Search Settings Page
Supported Uri Conventions
The following table details the supported standard OData Uri Conventions.
| OData Reference | Details | Supported | Example |
|---|---|---|---|
| 5. Service Metadata Document | $metadata | Yes | /$metadata |
| 4.2. Orderby System Query Option ($orderby) | $orderby | Yes | $orderby=Record_Number desc |
| 4.3. Top System Query Option ($top) | $top | Yes | $top=10 |
| 4.4. Skip System Query Option ($skip) | $skip | Yes | $skip=15 |
| 4.5. Filter System Query Option ($filter) | $filter | Yes | $filter=Record_Number eq 'R0000001' |
| Eq | Yes | $filter=Container eq 'F0000000003' | |
| Ne | Yes | $filter=RecordClassification ne 'Class1' | |
| Gt | Yes | $filter=Modified gt DateTime'2012-05- 29T09:13:28' | |
| Ge | Yes | $filter=Modified ge DateTime'2012-05- 29T09:13:28' | |
| Lt | Yes | $filter=Modified lt DateTime'2012-05- 29T09:13:28' | |
| Le | Yes | $filter=Modified le DateTime'2012-05- 29T09:13:28' | |
| And | Yes | $filter=Record_Type eq 'Record' and Record_Status eq 'Active' | |
| bool substringof(string po, string p1) | Yes | $filter=substringof('Wel', Title) | |
| bool startswith(string p0, string p1) | Yes | $filter=startswith(Title, 'Wel') |
Sample Reports using Power BI
In this example we will query the Records service to fetch the metadata for records from RecordPoint and filter it to limit the amount of data returned.
Fetch Records using Power BI
Click Get Data in Power BI and select OData feed as the data source type. Type in “http://[domainname]/_layouts/15/RecordPoint/Services/RecordPoint.SI.WebServices.ReportODataService.svc/Record” as the URL
Power BI Authentication for OData
Select the relevant authentication and click connect. A dialog box with the preview of data will be displayed. Click load and Power BI will recursively fetch all the records from RecordPoint. Select the fields to be displayed from the search results in this view from the Field List under the query in the right corner. By default, the visualization is table and the Record data is displayed in rows.
Field List in Power BI
Results Table
Limit the number of Records using $top
Let’s edit the query so that it only returns the top 15 last modified records. Click edit query in Power BI and select Source from the Query Settings Pane on the right. Update the URL to “http://[domainname]/_layouts/15/RecordPoint/Services/RecordPoint.SI.WebServices.ReportODataService.svc/Record?$orderby=Modified desc & $top=15” Click Close & Apply in the ribbon set. The resulting table now display top 15 records which were modified last.
Limit the number of Records using $filter
Let’s edit the query so that it only returns records which have a Record Status of Active. Before adding query parameters to URL, ensure that “Record Status” is included in the list of fields for ServiceOData in the RecordPoint General Settings page. Edit the Query and update the query URL to “http://[domainname]/_layouts/15/RecordPoint/Services/RecordPoint.SI.WebServices.ReportODataService.svc/Record?$filter = Record_Status eq 'Active'” Click Close & Apply in the ribbon set. The resulting table now display only active records. RecordPoint Core OData
Use Power BI to filter at client-side and apply visualizations
Power BI can be used to filter the already fetched data. Let’s transform the current filtered data to get the list and number of records in each lists. Later we will create a chart of all list having more than 5 records. Click Edit query and select “Group By” from the ribbon set. Select “Record_List_Name” as the “Group By” column and name the new column as “Number Of Records”. The operation should be “Count Rows”
Group By Dialog Box in Power BI
Close and Apply and select the two fields in the Query. The list names with number of records in each list is displayed in the tabular form.
Results Table
Edit the filters in the right pane to display only lists having more than 5 records. Apply the filters and select “Clustered Column Chart” from the Visualizations
Filters Pane
Column Chart of Lists and number of Records
Limitations
- The Reporting OData service in RecordPoint only returns the data that has been indexed.
- The time portion of the ‘DateTime’ query will be ignored by the search.
- The following OData URI conventions are not supported by the Reporting OData service:
| OData Reference | Details | Supported |
|---|---|---|
| 3.2. Addressing Links between Entries | $links | No |
| 3.3. Addressing Service Operations | /METHODNAME | No |
| 4.5. Filter System Query Option ($filter) | Or | No |
| Not | No | |
| AddSub | No | |
| Mul | No | |
| Div | No | |
| Mod | No | |
| Grouping | No | |
| ( ) | No | |
| bool endswith(string p0, string p1) | No | |
| int length(string p0) | No | |
| int indexof(string p0, string p1) | No | |
| string replace(string p0, string find, string replace) | No | |
| string substring(string p0, int pos) | No | |
| string substring(string p0, int pos, int length) | No | |
| string tolower(string p0) | No | |
| string toupper(string p0) | No | |
| string trim(string p0) | No | |
| string concat(string p0, string p1) | No | |
| int day(DateTime p0) | No | |
| int hour(DateTime p0) | No | |
| int minute(DateTime p0) | No | |
| int month(DateTime p0) | No | |
| int second(DateTime p0) | No | |
| int year(DateTime p0) | No | |
| double round(double p0) | No | |
| decimal round(decimal p0) | No | |
| double floor(double p0) | No | |
| decimal floor(decimal p0) | No | |
| double ceiling(double p0) | No | |
| decimal ceiling(decimal p0) | No | |
| bool IsOf(type p0) | No | |
| bool IsOf(expression p0, type p1) | No | |
| 4.6. Expand System Query Option ($expand) | $expand | No |
| 4.7. Format System Query Option ($format) | $format | No |
| 4.8. Select System Query Option ($select) | $select | No |
| 4.9. Inlinecount System Query Option ($inlinecount) | $inlinecount | No |
| 5. Custom Query Options | No | |
| 6. Service Operation Parameters | No |