Smartstaff
281
edits
Changes
m
→How to setup an authenticated user to consume a private OData Service
==What is OData?==OData is described in the following article: [https://en.wikipedia.org/wiki/Open_Data_ProtocolWikipedia]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 ? Smartsimple provide does it work==SmartSimple provides OData V2, V3 and V4 services - Can . These can be consumed by OData clients like such as MS Excel, MSSQL MS SQL server,SafeforceSaleForce, or used though another API such as API (.NET has very good support) etc. What SS ==SmartSimple Entities exposed Exposed to OData Services ? Report Only (however it does not exclude us to expose raw ==Currently Reports are the only entities like people, organization, Levelone, notes in exposed through Odata. In the future) Why Report Only ? 1other entities may be added. Reports were chosen for the following reasons: # Easier to control security and data format like such as fields and fieldname, any end user can perform this task 2. # Most clients wish to consume ready read only data anyway 3. # Data relationships are handled in SSSmartSimple, the external client does not need to understand underlying data relationships 4. # Easy to implement as there is no need to configure OData service and metadata enpointsendpoints (these are as required by the OData protocol), it is these are automatically handled by SS report engine automaticaly 5. More the SmartSimple Report Engine# Greater flexible as SS the SmartSimple user are is able to create dummy tables (like similar to SQL views) by combining tables , applying formulas and aggregrations 6. aggregations# Filtering ans and ordering are pre-defined in SS reportSmartSimple Report, eliminate SQL injection vulnerability Where is my ==OData Services Endpoints==SmartSimple OData provides two endpoints: Private and Public Access Endpoints.<br />Endpoints will return a list of collections (Reports that are OData enabled).Enable [https://wiki.smartsimple.com/wiki/OData_Connector OData services Connector] to use these endpoints. ===Private Access Endpoint===A private endpoint ? will need basic authentication for '''authenticated access''' user to access the collection of entities.<br />Version 4: https: //mysmartsimpleurl/OData/'''V4'''/'''pri'''/424623/Service.svc/<br />Version 3: https: //mysmartsimpleurl/OData/'''V3'''/'''pri'''/424623/Service.svc/<br />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.<br />Version 4: https://mysmartsimpleurl/OData/'''V4'''/Services'''pub'''/424623/Service.svc/<br />Version 3: https: //mysmartsimpleurl/OData/'''V3'''/'''pub'''/424623/Service.svc/<br />Version 2: https: //mysmartsimpleurl/OData/'''V2'''/'''pub'''/424623/Service.svc/ ==How to setup in SS ? 1an authenticated user to consume a private OData Service==For [[JSON API (SmartConnect) Prerequisite|'''authenticated access''']], you will need to create a new user with the API Access attribute. # Create a the new user and set access to "web access" 2to API Access. # Set a password 3for this user. For public access there is no requirement to create a user. ==Accessing a Report with OData==SmartSimple OData support requests for data via HTTP GET request. Edit report in neo builder Private and enable Public Endpoints exposes all collections (Reports enabled by OData checkBox 4Connector). Done How Syntax below is an example to connect view OData records from Salesforces a report named '''my_Neo_Report''' <pre>http://mysmartsimpleurl/OData/V2/pub/424623/Service.svc/my_Neo_Report_recordshttp://mysmartsimpleurl/OData/V3/pub/424623/Service.svc/my_Neo_Report_recordshttp://mysmartsimpleurl/OData/V4/pub/424623/Service.svc/my_Neo_Report_records</pre> ==OData Primitive Data Types==Primitive type representations in XML request is based on the [[Custom Field Type IDs|Data Type]] of the [[Standard Field|Standard Fields]] / [[Custom Fields – General Information|Custom Fields]]<br />Column headers from a report with the following data types will not be successfully transformed in the OData Service Report: * Display Only* Link* Read Only* Store Values ==Syntax of OData Queries==1. Service Metadata Document - describes the data model exposed as HTTP endpoints by the service. <pre>http://mysmartsimpleurl/OData/V2/pub/424623/Service.svc/$metadata</pre> 2. Search syntax i.e. criteria on report using “???”: the URL below returns OData records with firstname equal ‘steve’ only <pre>http://mysmartsimpleurl/OData/V4/pub/424623/Service.svc/report_Neo_records? $search=steve</pre> 3. Filter for dynamical search: the URL below returns OData records with firstname equal ‘steve’ only (eq stands for equal in OData protocol) <pre>http://mysmartsimpleurl/OData/V4/pub/424623/Service.svc/report_Neo_records?$filter=first_name +eq+steve I will add more details with screen shots How to connect from MSExcel </pre> 4. Ordering: the URL below orders by first name <pre>http://mysmartsimpleurl/OData/V4/pub/424623/Service.svc/report_Neo_records? $orderby=first_name+asc or I http://mysmartsimpleurl/OData/V4/pub/424623/Service.svc/report_Neo_records?$orderby=first_name</pre> 5. $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 add more details with screen shotsreturn) <pre> http://mysmartsimpleurl/OData/V4/pub/424623/Service.svc/report_Neo_records?$top=2&$skip=3</pre> 6. $count, used to determine how many rows in a collection <pre> http://mysmartsimpleurl/OData/V4/pub/424623/Service.svc/report_Neo_records/$count</pre> ==Consuming SmartSimple OData Feed==The following examples show how to consume OData. * [[SmartSimple_OData_Feed_to_Excel|Pulling OData feed to Excel]]* [[SmartSimple_OData_Feed_to_SQL_Server_Database|Import OData feed to SQL Server Database using SQL Server Integration Services]] ==Troubleshooting==* Report name '''must not''' contain special characters such as question marks, underscore, number sign. Some characters, such as hyphens, are supported.* 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. =See Also=* [[Reports: SmartSimple Reports]]* [[OData Connector]]* [http://www.odata.org More information on OData] [[Category:Integration]][[Category:API]]