Access to Generic Inquiries Through OData: General Information

You can view the generic inquiries exposed through OData in your browser. To do this, you enter the URL of the OData endpoint of your MYOB Acumatica instance in the address bar of your browser.

When the system asks you to authenticate yourself, provide your MYOB Acumatica username and password. When your identity has been confirmed, the system displays the list of available inquiries.

Learning Objectives

In this chapter, you will learn how to access an exposed generic inquiry through Microsoft Excel and how to configure cross-origin resource sharing (CORS) to access an exposed inquiry through client-side web applications.

Applicable Scenarios

You may find the information in this chapter useful when you are a technical specialist with your company and your responsibilities include the management of different reports and inquiries. An accountant or other employee may have requested access to an MYOB Acumatica generic inquiry through a third-party OData client, such as Microsoft Excel, Microsoft Power BI, or a Java-based application.

Access to an Exposed Generic Inquiry Through an OData Client

By exposing data from MYOB Acumatica—in this case, data from a generic inquiry created on the Generic Inquiry (SM208000) form—through the OData interface, you can give users the ability to view your company’s data and perform detailed financial analysis by using third-party OData clients, such as Microsoft Excel and Microsoft Power BI.

To connect to your data, you specify the URL of the OData endpoint of your MYOB Acumatica instance to the OData client and authenticate yourself by entering your MYOB Acumatica credentials. The OData client can use the OAuth 2.0 authorization instead of direct authentication with a username and password. For details about OAuth 2.0 authorization, see Authorizing Client Applications to Work with MYOB Acumatica.

The OData client then connects to your MYOB Acumatica instance and obtains the data for you.

Tip: Even though you can view all exposed inquiries, you can obtain only the data to which your user account has sufficient access rights.

The URL of the OData Endpoint

Generally, the URL of the MYOB Acumatica OData endpoint is the URL of your instance concatenated with /OData. For example, suppose that the URL of your instance is https://sweetlife.com/erp; to view the list of exposed inquiries, you use the following URL: https://sweetlife.com/erp/OData.

The following screenshot shows a list of exposed inquiries that has been accessed through the browser.

Figure 1. A list of exposed inquiries accessed through the browser

If your instance has a multitenant configuration, you add /OData/<TenantName> to the end of the URL of your instance, where <TenantName> is the login name of the tenant in the MYOB Acumatica instance. You can find the login names of tenants on the Tenant List (SM203530) form (as shown in the following screenshot). For more information on single-tenant and multitenant configuration, see Tenants: General Information.

For example, if the URL of your MYOB Acumatica instance is https://sweetlife.com/erp and you want to view generic inquiries exposed in the Calipso LLC tenant, you use the https://sweetlife.com/erp/OData/Calipso LLC URL in your browser or in an OData client. (If you type this into a browser, you will notice that the browser automatically replaces each space with %20 in the URL.)

Also, you can view the login name of the tenant to which you are currently signed in by viewing the User menu (as shown in the following screenshot), which you access by clicking the User menu button on the top pane of the MYOB Acumatica screen.

Figure 3. User menu with the tenant login name

Use of OData to View a Generic Inquiry’s Fields and Parameters

Through OData, you can view the list of fields and parameters in exposed generic inquiries. You compose the URL to obtain the list of fields and parameters by doing one of the following:

If your MYOB Acumatica instance contains a single tenant, append /OData/$metadata to the URL of your instance.
If your MYOB Acumatica instance contains multiple tenants or a single tenant, append /OData/<TenantName>/$metadata to the URL of your instance.
Tip: We recommend that you use the approach with the login name of the tenant specified in the URL for an instance with a single tenant if you may add more tenants to the instance in the future.

For each exposed generic inquiry, the returned list contains an EntityType element, which includes the following:

The list of fields of the generic inquiry in the Property elements.
The list of the key fields of the tables used in the generic inquiry in the PropertyRef elements, even if these key fields have not been added to the Results Grid tab of the Generic Inquiry (SM208000) form for the generic inquiry.

For each exposed generic inquiry with parameters, the response includes a FunctionImport element, which specifies the following:

The name of the exposed generic inquiry that is used in an OData request that specifies parameters of the inquiry. This name has the _WithParameters postfix.
The list of parameters of the generic inquiry.

The field names and parameter names in the lists are changed according to the OData specifications; for more information, see Generic Inquiries and OData: Preparation of an Inquiry for Exposure.

Use of OData to View the Results of a Generic Inquiry

Through OData, you can view the results of any exposed inquiry. You compose the URL to view the data by doing one of the following:

If your MYOB Acumatica instance contains a single tenant, append /OData/<GI_Name> to the URL of your instance.
If your MYOB Acumatica instance contains multiple tenants or a single tenant, append /OData/<TenantName>/<GI_Name> to the URL of your instance.
Tip: We recommend that you use the approach with the login name of the tenant specified in the URL for an instance with a single tenant if you may add more tenants to the instance in the future.

In the examples above, <TenantName> is the login name of the tenant in the MYOB Acumatica instance for which you have configured inquiries, and <GI_Name> is the case-sensitive title of the exposed generic inquiry.

For example, if the URL of your MYOB Acumatica instance is http://sweetlife.com/erp, the instance contains one tenant, and you want to obtain the results of the BI-Customer generic inquiry, you use the http://sweetlife.com/erp/OData/BI-Customer URL.

Use of OData to View the Results of a Generic Inquiry with Parameters

To specify the values for the parameters of a generic inquiry and obtain the result of the generic inquiry, you specify the values of the parameters of the generic inquiry in the URL parameters. In the URL, you use the name of the generic inquiry that is specified in the respective FunctionImport element.

For example, if the URL of your MYOB Acumatica instance is http://sweetlife.com/erp, the instance contains U100 tenant, and you want to obtain the results of the DB-StorageDetailsByItemWarehouseLocation generic inquiry for the WHOLESALE warehouse, you use the http://sweetlife.com/erp/odata/U100/DBStorageDetailsByItemWarehouseLocation_WithParameters?Warehouse=WHOLESALE URL.

Use of OData to View the Results of a Generic Inquiry in JSON Format

MYOB Acumatica supports JSON and ATOM formats; by default, the data is displayed on the screen in ATOM format. You can directly select a format by appending the $format parameter to the URL of an inquiry. The parameter can have one of the following values, which reflect how the data is displayed:

json: In JSON format
atom: In ATOM format
jsonverbose: In JSON Verbose format

For example, if the URL of your MYOB Acumatica instance is http://sweetlife.com/erp, the instance contains one tenant, and you want to obtain the results of the BI-Customer generic inquiry in JSON format, you use the http://sweetlife.com/erp/OData/BI-Customer?$format=json URL (either in your browser or in an OData client).

Use of OData to Filter or Order the Results of a Generic Inquiry

You can filter or order the data of an exposed generic inquiry. In the URL, you use a question mark to start the list of parameters, such as $filter and $orderby. The & character is used to specify multiple URL parameters.

For example, you use the http://sweetlife.com/erp/OData/SO-BI-SalesOrdersForYear?$format=json&$filter=Customer eq 'GOODFOOD' and OrderTotal ge 1000 URL in your browser or an OData client if the following are true:

The URL of your single-tenant Acumatica ERP instance is http://sweetlife.com/erp.
You want to obtain the results of the SO-BI-SalesOrdersForYear generic inquiry in JSON format.
You also need to retrieve the results only for the GOODFOOD customer and with a sales order total that is greater than or equal to $1000.

For more information on OData parameters, see https://www.odata.org/documentation/odata-version-3-0/. For the list of items that are not supported by OData in MYOB Acumatica, see Generic Inquiries and OData: Preparation of an Inquiry for Exposure.

Configuration of CORS

MYOB Acumatica supports cross-origin resource sharing (CORS), meaning that requests for resources can come from a different domain than that of the resource making the request. With CORS enabled, you can allow access to the OData endpoints of your MYOB Acumatica instance for the client-side web applications, including Java-based applications. For more information about CORS, see Cross-Origin Resource Sharing on the World Wide Web Consortium portal.

The CORS settings of the web server of your instance are defined by the cors section of the web.config file; see the following example of the default configuration for this section.

<cors enabled="true" origins="*" methods="*" headers="*" 
exposedHeaders="DataServiceVersion,MaxDataServiceVersion,OData-Version,
OData-MaxVersion" />

By default, CORS is enabled, all origins are allowed access to the server, and all supported headers are exposed and available for use. The web server of the application supports simple headers as well as the following headers: DataServiceVersion, MaxDataServiceVersion, OData-Version, and OData-MaxVersion. (You need to use these headers to access OData endpoints.)

You can enforce limitations on cross-origin requests by changing the settings. You can add your own headers as well. For details, see Access to Generic Inquiries Through OData: To Configure CORS.