What is OData?

OData is described in the following article: Wikipedia article.

OData provides clients with the ability to publish their data to their communities. It's as if each clients has their own API for their own communities. For example, a client can expose their annual grant/funding information so tax payers can retrieve in real time data for further analysis.

How does it work

SmartSimple provides OData V2, V3 and V4 services.

These can be consumed by OData clients such as MS Excel, MS SQL server, SaleForce, or used though another API such as .NET.

SmartSimple Entities Exposed to OData Services

Currently Reports are the only entities exposed through Odata. In the future other entities may be added. Reports were chosen for the following reasons:

Easier to control security and data format such as fields and fieldname
Most clients wish to consume read only data
Data relationships are handled in SmartSimple, the external client does not need to understand underlying data relationships
Easy to implement as there is no need to configure OData service and metadata endpoints (as required by the OData protocol), these are automatically handled by the SmartSimple Report Engine
Greater flexible as the SmartSimple user is able to create dummy tables (similar to SQL views) by combining tables, applying formulas and aggregations
Filtering and ordering are pre-defined in SmartSimple Report, eliminate SQL injection vulnerability

OData Services Endpoints

SmartSimple OData provides two endpoints (Service Root URL): Private and Public Access Endpoints.
Endpoints will return a list of collections available.

Private Access Endpoint

A private endpoint will need basic authentication for authenticated access user to access the collection of entities.
Version 4: https: //mysmartsimpleurl/OData/V4/pri/424623/Service.svc/
Version 3: https: //mysmartsimpleurl/OData/V3/pri/424623/Service.svc/
Version 2: https: //mysmartsimpleurl/OData/V2/pri/424623/Service.svc/

Public Access Endpoint

If you wish data to be exposed publicly without basic authentication. Note the underlying report must be Internet and OData enabled.
Version 4: https: //mysmartsimpleurl/OData/V4/pub/424623/Service.svc/
Version 3: https: //mysmartsimpleurl/OData/V3/pub/424623/Service.svc/
Version 2: https: //mysmartsimpleurl/OData/V2/pub/424623/Service.svc/

How to setup an authenticated user to consume private OData Service

For authenticated access, you will need to create a new user with the Web Service Client Access attribute.

Create the new user and set access to Web Service Client Access.
Set a password for this user.

For public access there is no requirement to create a user.

Modifying a Report for Use with OData

Edit the Report in Neo Report Builder
Enable the OData Connector checkbox on the Advanced tab
Enable the Internet Enabled checkbox if you wish the data to be public

Accessing a Report with OData Service

OData services support requests for data via HTTP GET request. Endpoints listed above will enumerate all collections of entities (Reports enabled by OData Connector).

Syntax below is an example to view OData records from a report named my_Neo_Report

http://mysmartsimpleurl/OData/V2/pub/424623/Service.svc/my_Neo_Report_records<br />
http://mysmartsimpleurl/OData/V3/pub/424623/Service.svc/my_Neo_Report_records<br />
http://mysmartsimpleurl/OData/V4/pub/424623/Service.svc/my_Neo_Report_records

The webpage output will display maximum of 1024 rows only..

Syntax of OData Queries

1. Search syntax i.e. criteria on report using “???”: the URL below returns OData records with firstname equal ‘steve’ only

http://mysmartsimpleurl/OData/V4/pub/424623/Service.svc/report_Neo_records?$search=steve

2. Filter for dynamical search: the URL below returns OData records with firstname equal ‘steve’ only (eq stands for equal in OData protocol)

http://mysmartsimpleurl/OData/V4/pub/424623/Service.svc/report_Neo_records?$filter=first_name +eq+steve

3. Ordering: the URL below orders by first name

http://mysmartsimpleurl/OData/V4/pub/424623/Service.svc/report_Neo_records?$orderby=first_name+asc 
or  
http://mysmartsimpleurl/OData/V4/pub/424623/Service.svc/report_Neo_records?$orderby=first_name

4. $top and $skip, used mainly for pagination: The syntax below skips 3 records and return 2 records only (i.e. only 4th and 5th will return)

 http://mysmartsimpleurl/OData/V4/pub/424623/Service.svc/report_Neo_records?$top=2&$skip=3

Troubleshooting

Column Headings must not contain special characters such as question marks, number sign or brackets. Some characters, such as hyphens, are supported.
Some applications, such as older versions of MS Excel / .NET , may not support the appropriate level of TLS Encryption (i.e. https) required.

Odata Overview