Skip to Content

OData for ByD Analytics can be used to access SAP Business ByDesign and SAP Cloud for Customer analytical data using the Open Data Protocol (OData). You can query and retrieve characteristics and key figures exposed in ByD/C4C analytical reports. This includes

  • standard data sources and reports,
  • data sources and reports created via SAP Cloud Applications Studio,
  • custom reports based on standard or custom data sources, created as key user, and
  • extension fields added by key user tools or SAP Cloud Applications Studio.

As result, ByD OData for Analytics provides you a highly flexible framework to query, read and analyze ByD/C4C business data, well-suited to serve your integration scenario.
The ByD OData API enables you to

  1. discover available ByD OData services for your business user,
  2. get metadata incl. technical field names, labels, data types and annotations,
  3. retrieve the data of ByD analytical reports.

You can control the response of your OData request by expanding the OData URI.
The ByD Help Center provides a brief documentation of OData for Analytics. You find this documentation using key words “Retrieving Analytics Data Using OData” in the ByD Help Center or on SAP Help: Retrieving Analytics Data Using OData.

Below I will consider ByD OData rather from a business analytics point of view than from a REST point of view. Further information about REST, the OData-protocol in general and OData specifications can be found at http://www.odata.org or http://docs.oasis-open.org/odata.

In the following I refer to ByD and ByD examples, but the OData interface described works in a similar way for C4C as well.

 

Discover OData Services and get Meta Data

The goal is to get analytical reports that I can access and all meta data (characteristics, key figures, data types, …) for those reports.

Get OData containers for the logon user, or from a business user point of view: get all available work center for the logon user.
For containers with the following convention analytics data is available: <shortened name of work center>_analytics.svc.

URL pattern: <your system hostname>/sap/byd/odata

Example: https://my123456.sapbydesign.com/sap/byd/odata

Get OData entities for the logon user, or get all available analytical reports assigned to the logon user and visible in your Home work center.

URL pattern: <your system hostname>/sap/byd/odata/cc_home_analytics.svc

Example: https://my123456.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc

To retrieve the entity meta data you can expand your OData URI by /$metadata.

Get OData entities for a container, or get all available analytical reports per work center incl. all report meta data like properties (key figures, characteristics), data types, etc..

URL pattern: <your system hostname>/sap/byd/odata/<shortened name of work center>_analytics.svc/$metadata

Example: https://my123456.sapbydesign.com/sap/byd/odata/bpm_businesspartnerdata_analytics.svc/$metadata

Get meta data for a specific entity, or get all meta data for a specific analytical report.

URL pattern: <your system hostname>/sap/byd/odata/<shortened name of work center>_analytics.svc/$metadata?entityset=RP<report ID>QueryResults

Example: https://my123456.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/$metadata?entityset=RPCRMSLOIB_MQ0001QueryResults

 

Get Data of Analytical Reports

In all further examples I will skip the system host name. Let me list some simple examples before I go through the OData options step by step.

You can fetch entity data by adding the ByD analytics report ID to your OData URL.

URL pattern: <your system hostname>/sap/byd/odata/<shortened name of work center>_analytics.svc/RP<report ID>QueryResults

Example: /sap/byd/odata/cc_home_analytics.svc/RPCRMSLOIB_MQ0001QueryResults
As result the system returns the first 50 entries of report “Sales Order Volume” (50 entries is the default page size).

Some more examples for ByD standard business partner reports:

Account collaboration data: /sap/byd/odata/bpm_businesspartnerdata_analytics.svc/RPBPCSCARB_Q0001QueryResults

Account contacts data: /sap/byd/odata/bpm_businesspartnerdata_analytics.svc/RPBPCSCONTB_Q0001QueryResults

… or little more sophisticated:

Assume you would like to analyze the consumption of customer contracts. For this scenario you create a joined data source with ByD service confirmation items and customer contract items and a ByD report to list and analyze service confirmation items aggregated by customer contract information.
Assume further the custom report has the report ID ZABCDEF12345678ABCDEF.

Get service confirmations for contract CC1001:
/sap/byd/odata/cc_home_analytics.svc/RPZABCDEF12345678ABCDEFQueryResults?
$inlinecount=allpages&$select=FCISL_ACT_QTY_B,FCACCO_IAV_INV_QTY_BU,FCACCO_ITV_REQU_QTY_BU,FCZ0COUNT,CIBR_CUCO_ID,TIBR_CUCO_ID,CIBR_CUCO_I_ID,TIBR_CUCO_I_ID,CCCCO_DPY_MAINBUYERPTY,TCCCO_DPY_MAINBUYERPTY,CACCO_ITM_CREATIONDATE,CACCO_ITM_LASTCHANGEDATE&$filter=CIBR_CUCO_ID eq ‘CC1001’

Get service confirmations joined with contract information, filtered by contract ID and contract item last changed date greater than 19th Feb. 2014:
/sap/byd/odata/cc_home_analytics.svc/RPZABCDEF12345678ABCDEFQueryResults?$inlinecount=allpages&$select=FCISL_ACT_QTY_B,FCACCO_IAV_INV_QTY_BU,FCACCO_ITV_REQU_QTY_BU,FCZ0COUNT,CIBR_CUCO_ID,TIBR_CUCO_ID,CIBR_CUCO_I_ID,TIBR_CUCO_I_ID,CCCCO_DPY_MAINBUYERPTY,TCCCO_DPY_MAINBUYERPTY,CACCO_ITM_CREATIONDATE,CACCO_ITM_LASTCHANGEDATE&$filter=CIBR_CUCO_ID eq ‘CC1001′ and CACCO_ITM_LASTCHANGEDATE ge datetime’2014-02-19T00:00:00’

Get service confirmations joined with contract information, filtered by contract ID and some extension field of type datetime:
/sap/byd/odata/cc_home_analytics.svc/RPZABCDEF12345678ABCDEFQueryResults?$inlinecount=allpages&$select=FCISL_ACT_QTY_B,FCZ0COUNT,CITM_POST_DT,CITM_CREATED_DT,CITM_CHANGED_DT,ZY86ABC123_ABC8D1B123&$filter=CIBR_CUCO_ID eq ‘CC1001′ and ZY86ABC123_ABC1234567 ge datetime’2014-02-19T00:00:00’

 

OData Parameter

The ByD OData URL consists of 4 components:

  • your system host name,
  • a shortened name of the ByD work center,
  • a report ID, and
  • query options.

URL pattern: https://<your system host name>.com/sap/byd/odata/<shortened name of work center>_analytics.svc/RP<report ID>QueryResults?
<query options>

ByD OData supports the following query options:

  • $select
    Expansion: $select=<one or more specified characteristics separated by commas>
  • $orderby (ascending)
    Expansion: $orderby=<one or more specified characteristics followed by asc, separated by commas>
  • $orderby (descending)
    Expansion: $orderby=<one or more specified characteristics followed by desc, separated by commas>
  • $totals
    Expansion: $totals=<one or more specified characteristics separated by commas>
  • $filter
    Expansion: $filter=<specified characteristic> eq ‘<filter value>’
  • $top
    Expansion: $top=<specified number>
  • $skip
    Expansion: $skip=<specified number>
  • $count
  • $inlinecount
  • $format

 

Select Option

The select option can be used to choose properties (key figures and characteristics) that shall be returned. Properties are specified using its technical names as provided in the entity meta data (e.g. CACCDOCTYPE for Journal Entry Type ID).

If no select options are specified in the URL, then the system returns all key figures and characteristics of the entity definition (report definition).

Example:

Select multiple characteristics:
&select=CCUSTOMER,TCUSTOMER,CCUCOREF,TCUCOIUUID,CPOSTDATE,CACCDCHUID,CSETOFBKS,CSRCNTHREF,CITM_ID

Please note that key figures are aggregated according the selected characteristics.

Example:

You query sales order items and select the key figures Number of Sales Order Items and Net Value, and the characteristics Product Category:
/sap/byd/odata/cc_home_analytics.svc/RPCRMSLOIB_MQ0001QueryResults?$inlinecount=allpages&$select=FCITEM_COUNT,FCITV_NET_AMT_RC,CIPR_CATCP_N,TIPR_CATCP_N

As result the system returns one entry per product category with aggregated sales order net values and the number of sales order items per product category:                      

Product category ID: Product category name: Number of sales order items: Net value:
20 IT Equipment 3 5.415,00 EUR
310 Marketing 36 1.509,00 EUR
10-40 Customer Service 57 44.572,00 EUR

 

The OData interface returns at maximum 50 properties (corresponding to a report with 50 columns).

If an entity has more properties defined, then specifying select options in the OData URL is mandatory. However, in this case I would recommend to copy the report and reduce the number of properties to 50.

 

Filter Option

Operator: Description: Example:
eq Equal $filter=ServiceCategoryID eq ‘240-10’
ne Not equal $filter=ServiceCategoryID ne ‘240-10’
gt Greater than $filter=Quantity gt 15
ge Greater than or equal $filter=PostingDate ge datetime’2014-01-14T00:00:00′
lt Less than $filter=Quantity lt 95
le Less than or equal $filter=Quantity le 95
and Logical and $filter=Quantity le 95 and Quantity gt 15
or Logical or (for numeric characteristics) $filter=KCAMTCOMP le -10000 or KCAMTCOMP gt 10000

 

Examples:

  • Filter by customer ID and set of books ID:
    $filter=CCUSTOMER eq ‘1029754’ and CSETOFBKS eq ‘4010’
  • Filter by posting date (type date time):
    $filter=CPOSTDATE ge datetime’2014-01-14T00:00:00′

Filter by initial values

With ByD 1611 the OData API supports filtering by initial/null values: not assigned/null values has to be passed as filter condition in the format ” “ (double quotes separated by space – passed within single quotes), for example: CCUST_ABC eq ” “.

Examples:

…/sap/byd/odata/cc_home_analytics.svc/RP…QueryResults?$select=CDPY_PROS_CITY&$filter=CDPY_PROS_CITY%20eq%20′”%20″‘

…/sap/byd/odata/cc_home_analytics.svc/RP…QueryResults?$select=CDPY_PROS_CITY&$filter=CDPY_PROS_CITY%20ne%20%27%22%20%22%27

 

AND/OR Operators and Intervals

The AND operator is allowed for “between” use cases only like <characteristic> ge <value 1> and <characteristic> le <value 2>.

You can use the OR operator to include two values, for example:
$filter=CACCDOCTYPE eq ‘1000’ or CACCDOCTYPE eq ‘4000’

Excluding two values using the OR operator (for example: $filter=CACCDOCTYPE ne ‘1000’ or CACCDOCTYPE ne ‘4000’) is not possible.

Furthermore you can combine multiple intervals by using brackets, for example:

$filter=PARA_COMPANY eq ‘1000’ and PARA_SETOFBKS eq ‘7000’
and ( PARA_FISCYEARPER ge 52016 and PARA_FISCYEARPER le 72016 )
and ( PARA_POSTDAT ge datetime’2016-06-25T00:00:00′ and PARA_POSTDAT le datetime’2016-07-04T00:00:00′ )

Boolean values

Example:
$filter=DebtorDoubtfulIndicator eq false
$filter=DebtorDoubtfulIndicator eq true

 

IDs and codes

Example: Filter by token (customer ID and set of books ID):
$filter=CCUSTOMER eq ‘1029754’ and CSETOFBKS eq ‘4010’

 

Date and Time

Filter by date (OData type Edm.DateTime):
$filter=CPOSTDATE ge datetime’2014-01-14T00:00:00′

Filter by time:
$filter=CLOGON_TIME ge time’PT00H00M32S’

Note:
You can filter by date-characteristics and time-characteristics only. Filtering by timestamps is not yet supported.
Date characteristics are exposed as OData type Edm.DateTime and require the literal form datetime’yyyy-mm-ddT00:00:00′ in filter expressions.
Some date characteristics that correspond to a timestamps in the business object model are converted for usage in ByD analytics: The timestamp is converted to UTC and time is truncated afterwards.

For example:

  1. I create an invoice via UI as an Australian employee in time zone UTC+9.30, which means I see on the UI, that the invoice has been created at 2017-01-31 01:05 (my Australian time zone).
  2. I read the invoice via web service or SAP Cloud Application Studio: The web service returns the CreationDateTime in UTC: 30.01.2017 14:35:46.0000000Z.
  3. I check the invoice via UI using ByD reports: Created On is 2017-01-30 which is the creation date time in UTC with truncated time.
  4. I read the invoice using OData for Analytics: Created On ID (CDOC_CREATED_DT) is 2017-01-30T00:00:00.

Any filtering using OData has to take into account the UTC conversion and time truncation:

  • The OData with filter $filter=CDOC_CREATED_DT ge datetime’2017-01-30T00:00:00′ returns the invoice above.
  • The OData with filter $filter=CDOC_CREATED_DT ge datetime’2017-01-31T00:00:00′ does not return the invoice, because the filter date is greater than the invoice date in UTC.
  • The OData with filter $filter=CDOC_CREATED_DT ge datetime’2017-01-30T18:45:00′ does return the invoice, because the time of the filter parameter is not taken into account.

 

Report Characteristics and Report Selection Parameter

When retrieving OData metadata you may observe that some report characteristics are mentioned twice in the entity metadata:

  • as property referring to a report characteristic (e.g. Property Name=”CIP_SALES_UNIT” sap:label=”Sales Unit ID”) and
  • as property referring to a report selection parameter (e.g. Property Name=”PAR_SEL_IP_SALES_UNIT” sap:label=”Sales Unit”).

The properties referring to report characteristics correspond to the characteristics as defined in the Report Wizard in step “3 – Select Characteristics“.

The properties referring to the selection parameters are indicated as sap:selection=”true” together with the selection type (for example sap:selection-type=”multi-value”) in the property attributes. The properties referring to selection parameters correspond to the report characteristics enabled for value selection in the Report Wizard in step “4 – Characteristic Properties“.

You can use both type of properties for filtering, but filtering using properties referring to the selection parameters is recommended for the purpose of runtime and memory consumption optimization, because the filter parameter values are applied on the database level always.

Filtering using properties referring to report characteristics might be applied on the selection result only, depending on the overall report setup.

 

Report Initial Selection Default Values

Initial Selection Default Values are default values for selection parameter defined in the Report Wizard step “5 – Define Variables“, button Maintain Default Values.

Report Initial Selection Default Values can be used to tailor ByD reports for OData consumption to simplify filtering in OData requests and to optimize runtime and memory consumption. Report Initial Selection Default Values can be overwritten or refined using OData filter parameter. If no corresponding filter parameter are provided in the OData request, then the system applies Initial Selection Default Values as provided in the report wizard.

 

Report Fixed Value Selections

Using the Report Wizard step “4 – Characteristic Properties” you can Set Fixed Value Selections for some characteristics.

Note: Report Fixed Value Selections and Initial View Default Selections are not the same. The Initial View Default Selection can be overrules using OData filter parameter whereas Fixed Value Selections cannot be changed in OData requests.

Therefore report Fixed Value Selections are effective possibilities to restrict access to ByD data using OData for Analytics.

 

Report Default Selection Variants

The Report Default Selection Variant is a report selection variant configured as default by a key user or business user in the report UI.

Report Default Selection Variants as well as all non-default report selection variants are not taken into account by the OData API.

 

Personalized characteristics and selection parameter

Personalized characteristics and Personalized selection parameter are characteristics added by a key user or business user in the report UI using button “Added field“.

Personalized characteristics are exposed via OData as filterable characteristic, but not as property referring to the selection parameter (hierarchical parameters and relative selects are not supported as well).

Personalized characteristics that are added as key user using work center Business Analytics are available to all users.

Personalized characteristics that are added as business user (not using work center Business Analytics) can be retrieved by the same user only.

Furthermore these business user characteristics are not exposed if you are using entity set ana_businessanalytics_analytics.svc as well.

 

Filter in financial reports

Most financial reports and data sources have mandatory selection parameters like company and set of book.

In particular all reports based on financial balance data sources (for example report Trial Balance based on data source FINGLAU01) require to set the mandatory default selection values or filter parameters: company (COMPANY), set of books (SETOFBKS), fiscal year (FISCYEAR) and fiscal period (FISCPER). The report returns no data if those selection/filter values are not provided.

 

For further details and examples regarding filter and selection parameter please check my blog post Using Filter and Selection Parameter in ByD OData for Analytics.

 

Order By Option

The options $orderby=<characteristics name> asc or $orderby=<characteristics name> desc enable you to sort your query result ascending or descending with respect to a specific characteristics. You can sort by characteristics, but not by key figures.

Example:

Select sales order net values per product category sorted by the product category ID and the product category name (ok, the example sorting doesn’t make much sense, but it shows how it works):
/sap/byd/odata/cc_home_analytics.svc/RPCRMSLOIB_MQ0001QueryResults?$inlinecount=allpages&$select=FCITEM_COUNT,FCITV_NET_AMT_RC,CIPR_CATCP_N,TIPR_CATCP_N&$orderby=CIPR_CATCP_N%20desc,TIPR_CATCP_N%20desc

 

Top, Skip Option (Paging)

The $top option specifies that only the first n records will be returned.

The $skip option specifies that the result shall not include the first n entities.

Both query options together can be used to process a paging through the server side hit list.
If no paging options are specified, ByD returns the first 50 entries due to default page size = 50.

Example:

Select the 3rd page of 10 entries (skip 20 and return 10), sorted by characteristic CIPY_BUY_CNTCD_N:
/sap/byd/odata/cc_home_analytics.svc/RPCRMSLOIB_MQ0001QueryResults?$inlinecount=allpages&$select=CIPY_BUY_CNTCD_N,FCITEM_COUNT,FCITV_NET_AMT_RC,
CIPY_BUYER_PTY,TIPY_BUYER_PTY,CIPR_CATCP_N,TIPR_CATCP_N
&$orderby=CIPY_BUY_CNTCD_N%20asc&$skip=20&$top=10

Totals Option

Using the totals option you can add entries with totals to the OData result.

Example:

The URL

/sap/byd/odata/cc_home_analytics.svc/RPCRMSLOIB_MQ0001QueryResults?$inlinecount=allpages&$select=CIPY_BUY_CNTCD_N,FCITEM_COUNT,FCITV_NET_AMT_RC,
CIPY_BUYER_PTY,TIPY_BUYER_PTY,CIPR_CATCP_N,TIPR_CATCP_N,
TotaledProperties&totals=CIPY_BUY_CNTCD_N,CIPY_BUYER_PTY,CIPY_BUY_CNTCD_N
&$orderby=CIPY_BUY_CNTCD_N%20asc

results in the additional totals entries (marked orange):

Product category ID Buyer party ID Buyer country Number of sales order items Net value
320 1000111 IN 1 220.00 USD
50-20 1000111 IN 4 320.00 USD
    IN 5 540.00 USD
10-40 MC4001 US 6 1000.00 USD
40 MC3123 US 8 600.00 USD
    US 14 1600.00 USD
      19 2140.00 USD

 

 

Other Options

Count

Returns the total number of entries.

Example:
/sap/byd/odata/cc_home_analytics.svc/RPCRMSLOIB_MQ0001QueryResults/$count

Inline Count

Returns the number of entries included in the query result.

Example:
/sap/byd/odata/cc_home_analytics.svc/RPCRMSLOIB_MQ0001QueryResults?$inlinecount=allpages&$select=FCITEM_COUNT,FCITV_NET_AMT_RC,CIPY_BUYER_PTY,
TIPY_BUYER_PTY,CIPR_CATCP_N,TIPR_CATCP_N,CIPY_BUY_CNTCD_N

 

Format

Allows you to specify the result format:

  • Atom (default)
  • JSON

Example:
/sap/byd/odata/cc_home_analytics.svc/RPCRMSLOIB_MQ0001QueryResults?$inlinecount=allpages&$select=FCITEM_COUNT,FCITV_NET_AMT_RC,CIPY_BUYER_PTY,
TIPY_BUYER_PTY,CIPR_CATCP_N,TIPR_CATCP_N,CIPY_BUY_CNTCD_N&$format=json

 

ID Access

Access an entry with a specified ID. IDs are provided in query results.

Example:
/sap/byd/odata/pmm_productdata_analytics.svc/RPSERVICE_Q0002QueryResults
(‘%7CCCHG_DATE%3D03/06/2015%7CCSERV_INT_ID%3DMCD-ICSP-1000%7C’)


URL encoding for special characters and spaces

Some applications or browsers may require URLs without spaces, unsafe characters and special characters outside the ASCII character-set. In this case replace those characters by the correctsopnding %-syntax. You find more details on HTML URL Encoding Reference.

Example:

<space> %20
%27
( %28
) %29

 

Readable version: $filter=CIBR_CUCO_ID eq ‘1001’

with %-Syntax: $filter=CIBR_CUCO_ID%20eq%20%271001%27

 

How to set the language of the data returned?

Using parameter sap-language you can specify in which language descriptions and texts shall be returned.

Parameter:

sap-language=<language ISO code> Example: sap-language=fr

 

Example:
myXXXXXX.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RPBUPCSD_Q0001QueryResults?$select=TMAIN_ADDRESS,CPRJTN_ROLE_CODE,TPRJTN_ROLE_CODE,CSTATUS,TSTATUS,CBP_UUID,CLEGAL_FORM,TLEGAL_FORM&sap-language=fr

 

 

Authorization for OData Access

Similar to the ByD UI every user has access to analytical reports that are assigned to work center views, which are covered by the business user access rights.

Technical users (for example communication users) cannot be used for OData access.

Steps to create a user with OData for Analytics authorization:

  1. Create a ByD analytics report and assign the report to a ByD work center view.
  2. Create a ByD employee or service agent (for service agents additionally request a ByD user).
  3. Assign the work center and work center view which contains the analytical report to the access rights of the employee or service agent.

The business user might get a specific security policy assigned that prevents password aging and hence necessity to change the password periodically.

If SAML SSO is configured on the tenant, and OData shall be accessed using basic authentication, SAML needs to be explicitly disabled for an OData call.

Example:
/sap/byd/odata/srm_supplierbase_analytics.svc/RPBUPSPP_Q0001QueryResults?saml2=disabled

 

High Volume Data Sources

Using ByD OData interfaces means to extract and transfer data via internet and hence every integration architecture should be carefully planned with regards to data volume and message sizes.
Any approach based on a “select-all” will not be a sustainable integration setup and reach memory or timeout boundaries at some point in time. You should make sure that the total amount of data loaded from the ByD database and the size of OData responses (xml or json message sizes) are reasonable sized and remain stable over years, even if the total number of database records may grow fast.

In the following I consider some possibilities to control the data fetched from the database and message sizes.

 

Extract delta data or limit the data extracted to a specific time frame

It is always recommended to fetched data using filters resulting in stable max. volumes. That means instead of reloading all data on every replication event, load missing data by filtering data created or relevant in a specific time period (for example filter by creation date, change date, posting date, fiscal period, or similar).

You can get information about filterable properties from the OData entity metadata, which contains all relevant information to assemble the ByD OData URL incl. property names, labels and filterable properties referring to characteristics and report selection parameter.

Note: Filtering using properties referring to the selection parameters is recommended for the purpose of runtime and memory consumption optimization, because the filter parameter values are applied on the database level always. Filtering using properties referring to report characteristics might be applied on the selection result only, depending on the overall report setup.

Examples:

  • Filter by selection parameter invoice date:
    $filter=PAR_SEL_DOC_INV_DATE ge datetime’2016-12-01T00:00:00′
  • Filter by selection parameter item creation date:
    $filter=PAR_SEL_ITM_CREATED_DT ge datetime’2017-01-04T00:00:00′

Note:
Combined and joined data sources may not contain all business documents due to its join conditions.
Example: When you cancel a time recording, the corresponding record will no longer be returned by the combined data source Employee Times (HCMTLMU01).

 

Pre-filtering using ByD report Fixed Value Selections and Initial Selection Default Values

When creating a custom report using the ByD Report Wizard, you can define Fixed Value Selections and Initial Selection Default Values.

Both filter settings are applied on the database and therefore can be used to optimize runtime and consumption of system resources efficiently. Furthermore Fixed Value Selections and Initial Selection Default Values support relative selects, for example “Current financial period” and “Current Month to Date“.

Both settings are well-suited to tailor your ByD report for OData consumption in particular in case of high volume data sources.

 

Extract aggregated data with constant numbers of returned aggregated records

By choosing fields to be selected in the OData request, you implicitly define the level of aggregation in ByD before the data is transferred to the OData consumer. Using this aggregation and choosing the selected fields carefully, you can very efficiently reduce the number of records returned, or even reach constant numbers of records returned for growing data sources.

Basically requesting aggregated data is a possibility to optimize runtime, memory consumption and the size of transferred data volume. In general aggregations are done on database level reducing the impact of growing data source volumes.
However, aggregation still requires to select all entries and hence runtime and memory allocation increases over time.

Please note that restrictions in key figures are applied on OLAP level and hence aggregation might not be possible database level, but lead to an additional aggregation on OLAP level. Therefore using aggregation for the purpose to solve a data volume issue requires a very detailed analysis of the report setup. I would rather recommend to use OData filter and report selection parameter to optimize runtime and memory consumption.

Check calculated and restricted key figures: beyond characteristics listed in the select statement, all characteristics used for calculations and restrictions are loaded as well and significantly reduce the performance of aggregation.

Example:
You query sales order items and select the key figures Number of Sales Order Items and Net Value, and the characteristic Product Category:
/sap/byd/odata/cc_home_analytics.svc/RPCRMSLOIB_MQ0001QueryResults?$inlinecount=allpages&$select=FCITEM_COUNT,FCITV_NET_AMT_RC,CIPR_CATCP_N,TIPR_CATCP_N
As result the system returns one entry per product category with aggregated sales order net values and the number of sales order items per product category


Select required fields (select-parameter)

It is always better to fetch only required characteristics and key figures from the data source. For this purpose you can create custom reports for OData usage with minimum needed fields and characteristics or you can use the OData $select-parameter to specify the fields retrieved from the data source.

It does not make a difference if you apply the $select option on a big standard report with 50+ characteristics and key figures, on a small custom report built on a big standard data source, or on a small custom report build on a small custom data source. The system selects the specified fields from the database only. But: If key figures use additional characteristics, these are selected as well implicitly!

If no OData $select option is provided, then the system returns all key figures and characteristics of the entity definition (report definition); at max. 50 characteristics and key figures.

Example:
Select multiple characteristics: &select=CCUSTOMER,TCUSTOMER,CCUCOREF,TCUCOIUUID,CPOSTDATE,CACCDCHUID,CSETOFBKS,CSRCNTHREF,CITM_ID

 

Paging (top/skip parameters)

“Top” gives that many specific number of records. But this has nothing much to do with performance as this is calculated at runtime after fetching all data and taking into account report calculations, aggregations, etc.

This means paging using top/skip mainly has an effect on the transferred data, but not on the allocated memory and server-side runtime.

 

Performance impact of filter and selection parameter

In general you can change and enhance ByD reports from multiple point of views:

  • Using the Report Wizard in work center Business Analytics you can create or changing the basic report setup. The characteristics and key figures selected in the Report Wizard are called Initial View. The selection parameters and selection parameter values that are configured in the Report Wizard are called Initial Selection and Initial Selection Default Values. Furthermore you can define Fixed Value Selections.
  • If you open a report as key user or business user you have the possibility to add characteristics and key figures, and you can assemble a personalized report view and personalized selection variant. Those added report characteristics and report selection parameter are called Personalized Characteristics and Personalized Selection Parameter.
  • Using the option Edit With Web Browser in work center Business Analytics you have the possibility to add characteristics and key figures, and assemble a report view. Furthermore you can enter selection parameter values and filter values and save them as selection variant. The report view as well as the selection variant can be saved for later reuse by all users. Additionally you can mark report views and selection variants as Report Default View and Report Default Selection Variant.

Please check the following possibilities to setup filter and selection parameter for OData for Analytics requests and take into account its impact to runtime and consumption of system resources:

  1. Fixed Value Selection:
    • Fixed Value Selections are applied on database level.
    • Relative selects are supported.
    • These selection parameter values are applied for all OData requests. Using OData filter parameter you cannot overwrite Fixed Value Selections.
  2. Initial Selection Default Values:
    • Selection parameter values are applied on database level
    • Relative selects are supported
    • These selection parameter values are applied for all OData requests. Using OData filter parameter you can overwrite initial selection default values
  3. Report Selection Variants and Report Default Selection Variants:
    • Selection parameter:
      • Selection parameter values are applied on the DB
      • Relative selects are supported
      • Selection parameter values overwrite initial selection default values (2) if you open the report via UI
      • Default selection variants are not taken into account for OData requests
      • User-specific or non-default selection variants are not taken into account for OData requests
    • Filter parameter:
      • Filter parameter are applied on the selection result (Selection parameter values are first applied on database level. After that, OLAP applies the filter parameter values on the database selection result)
      • Filter parameter values are not taken into account for OData requests
  4. OData Parameter $filter using properties referring to selection parameters (property attribute sap:selection=”true”):
    • Example: Filter by creation date: $filter=PAR_SEL_ITM_CREATED_DT ge datetime’2017-01-04T00:00:00′
    • Filter parameter values referring to selection parameters are applied on the database level
    • These OData filter parameter values for selection parameter can be used to overrule initial selection default values (2). Selection parameters that are not provided as filter parameter in the OData request are still applied using the initial selection default values.
    • Relative selects are not supported
  5. OData Parameter $filter using properties referring to filterable characteristics:
    • Example: Filter by creation date: $filter=CCREATIONDATE eq datetime’2015-06-25T00:00:00′
    • Selection parameter values of (2) and (4) cannot be overruled. Initial selection default values (2) and OData filter referring to selection parameter (4) are applied first on database level. OData filter parameters referring to filterable characteristics (5) might be applied on the database selection result or directly on database level depending on the overall report setup. For runtime and memory consumption optimization it is better to use initial selection default values (2) and OData filter referring to selection parameter (4).
    • Relative selects are not supported
  6. OData Parameter $select for characteristics and key figures:
    • Example: $select=FCDEBIT_CURRCOMP,FCCREDIT_CURRCOMP,FCBALANCE_CURRCOMP
    • The OData select parameter does not reduce the number of records but the number of fields fetched from database
    • $select parameter values are applied on the DB directly. In general fields not listed are not fetched from DB and hence you can use this parameter to reduce memory consumption. However, characteristics used by key figures are selected additionally as well, even if not listed in the OData $select parameter.

For further details and examples regarding filter and selection parameter please check my blog post Using Filter and Selection Parameter in ByD OData for Analytics.

 

 

Some Trouble Shooting…

Error message: “Program error in class CL_RSBOLAP_QV_RESULT_SET method : TOO_MANY_DRILL_DOWN_OBJECTS”

=> You report has too many properties; the report must not have more than 50 properties. Reduce the number of requested properties using parameter $select.

Error message: “Ressource für das Segment ‘RP<report ID>QueryResults’ nicht gefunden”

=> Logon user does not have authorization to access the report.

Error code: http 404

=> IE Version (better use Firefox) or user/password.

Meta data for report not shown, even if report can be used.

=> (Re-)assign report to work center.

Why does the oData service return 50 entries only?

=> 50 entries is the default if the parameter $top is not specified in the oData URL.Use $top, $skip and $inlinecount=allpages to
specify the number of entries to be returned and for paging.

Why does the oData service return less entries than expected, even if I didn’t set any filter?

=> Report default selections cannot be overwritten with oData-based retrieval => Copy report w/o default selections.

MS Internet Explorer renders the OData result as feed:

You can turn off the RSS feed reading view on the internet explorer by the following steps:

  1. Open Internet explorer tools menu > select internet options > go to the content tab
  2. Click on Settings of the “Feeds and Web Slices”-section
  3. Uncheck the check box “Turn on feed reading view”
  4. Click on ok-button and restart the Internet Explorer

Error message 401 Unauthorized using SVC-documents in Microsoft Visual Studio or Excel-PowerPivot:

The OData metadata level service URL call requires  “/” at the end, e.g. https://myXXXXXX.sapbydesign.com/sap/byd/odata/crm_serviceentitlements_analytics.svc/

If not appending “/” after .svc, the system returns an 401 authorization error, since it was not getting the correct path and hence cannot not authenticating the web service call.

To report this post you need to login first.

110 Comments

You must be Logged on to comment or reply to a post.

    1. Knut Heusermann Post author

      Hi Tony,

      by default ByD returns metadata in Atom (xml) format.

      URL pattern: <your system hostname>/sap/byd/odata/<shortened name of work center>_analytics.svc/$metadata

      OData is embedded ready for use in ByD, you don’t need to setup gw to consume ByD OData services.

      BR, Knut

      (0) 
      1. tony shen

        Hi Knut,

        Thanks for your reply, the meta data is too large for me and it’s not necessary for me, it will lead to the result set very large. by the way, I don’t know why the filter argument is not efficient in our SAP gw, when I use the filter argument (like ?$filter=mantr eq ‘XXXX’), the system will return all the data as if without the filter argument. do you know if there are any setting for that? please see the attached screen. and do you know if there are any filter argument can do the fuzzy query?

        BR,Tony

        Unnamed QQ Screenshot20150311155129.png

        (0) 
  1. Ivan Bondarenko

    Finally, someone wrote about oData 🙂 Unfortunately, too late for me, I went through all of this by myself.

    I’d like to add, that if goal is to build reporting models than probably best solution is to use Power Query addin for Excel. There are two options to query through oData. PQ has oData feed option and Web.Contents.

    After some tests, I decided for myself that I will use oData.Feed only to request $metadata (default response is XML which is quite heavy for home_analytis.svc),

    but for reports use Web.Contents and $format=json in query string, because it is much more faster and works more stable.

    Would be glad if someone could add info in this thread

    1. How to get ID of workcenters (way for non-developers)?

    2. How to configure SSO to make possible to use PowerQuery without basic authorization?

    (0) 
    1. Knut Heusermann Post author

      Hallo Ivan,

      you are right, home_analytis.svc is perhaps not the most convenient work center to use OData. Basically I would recommend to assign the report to some work center that fits from a business point of view and then create a user with read-only access to this work center – that should be sufficient to consume OData.

      You can get the work center IDs via ByD UI:

      1. Open the ByD UI, work center view Application and User Mgmt. > Business Users
      2. Open (display or edit) the access rights of some user
        => you find the work center IDs on tab Work Center and View Assignment in column Work Center / View ID

      …or via OData with URL <your system hostname>/sap/byd/odata:

      BR, Knut

      (0) 
      1. Ivan Bondarenko

        Hello Knut!

        Thanks for such quick response!

        Tested way with Access Rights – it works fine for me. Thanks for advice!

        I even improved your idea – i.e. report “All Current Access Rights” can be used:

        list of workcenters.png

        However, second way doesn’t show me data.

        Link myXXXXXX.sapbydesign.com/sap/byd/odata?saml2=disabled

        shows me empty list in Internet Explorer

        error on odata.png

        -sso link shows HTTP 403 Forbidden error.

        (0) 
        1. Knut Heusermann Post author

          Hello Ivan,

          with regards to your URL myXXXXXX.sapbydesign.com/sap/byd/odata?saml2=disabled:

          This looks like your browser renders the OData result as feed.

          You can turn off the RSS feed reading view on the internet explorer by the following steps:

          1. Open Internet explorer tools menu > select internet options > go to the content tab
          2. Click on Settings of the “Feeds and Web Slices”-section
          3. Uncheck the check box “Turn on feed reading view”
          4. Click on ok-button and restart the Internet Explorer

          Now you should see the OData result as xml 🙂

          BR, Knut

          (0) 
          1. Ivan Bondarenko

            Dear Knut,

            thanks for efforts!

            Did like you said, however doesn’t work for me. Another error:
            “The XML page cannot be displayed Cannot view XML input using style sheet. Please correct the error and then click the  Refresh button, or try again later. Access is denied. Error processing resource ‘http://myXXXXXX.sapbydesign.com/sap/byd/odata?saml2=disabled‘”.

            Please, don’t waste a time on solution for this problem. Probably, I have restricted access rights on this tenant. Nevertheless, I have option with report “All Current Access Rights”. And it is totally enough for me.

            Thanks, Ivan

            (0) 
    2. Daniel Berwanger

      first of all, thank you Knut for the article!

      It’s a great summary and with Ivan’s hints you can build very fast an Excel or even an data model with Power Query (and Power Pivot).

      to get the ID of workcenters,[…] (Knut was faster 😉 ) additionally use the report “Access rights change log” in the same Workcenter.

      (0) 
    3. Jacques-Antoine Ollier

      Hello Ivan,

      Is it possible to bypass the limitation of 50 records with Power Query?

      I am trying the $inlinecount=allpages, but it keeps sending me only the first 50 records.

      Besides, is it possible to write a macro to automatically trigger this OData query through Power Query?

      Any help on this would be greatly appreciated.

      Thank you very much for your attention.

      Best regards.

      Jacques-Antoine Ollier

      (0) 
      1. Ivan Bondarenko

        Hi Jacques-Antoine!

        Should notice that for me $inlinecount=allpages does not work as well. I’m using $top=100000, to extract “unlimited” amount of rows.

        Power Query creates a Workbook.Connection object in Excel workbook. It can be refreshed via vba-macro or external vbscript, which can be scheduled using standard Task Scheduler, or specific software like Power Update can be used.

        BR,

        Ivan

        (0) 
        1. Jacques-Antoine Ollier

          Thank you Ivan Bondarenko and Knut Heusermann for your help.

          I did notice the $top was the way to go.

          I understand it is possible to execute the query and schedule the workbook automatically.

          However, I still do not understand how you can do the SAP basic authentication through a script in Excel.  Each time the report is refreshed, we need to log-in with a user/password credentials and I do not understand how to do this.

          Thank you very much for your help guys.

          Best regards.

          Jacques-Antoine Ollier

          (0) 
          1. Ivan Bondarenko

            Hi Jacques-Antoine,

            if we talk about Power Query, it saves info about each data source used for queries.

            Assume that you have one tenant. When you pull report from this tenant, doing basic authorization you have option to save credentials for tenant URL or for particular report URL (I use second option to aviod conflict with web-service query, where different credentials). It looks like this

            pq logon oData.png

            Once you used credentials for particular URL, next time PQ will not ask login and password (until you change them in the system).

            On the Power Query tab in Excel you may find button Data Source Settings. There is a list of saved data sources, where you can maintain privacy od data source, type of authorization and credentials.

            Hope it will help you.

            Don’t hesitate to contact if any questions left.

            BR,

            Ivan

            (0) 
            1. Jacques-Antoine Ollier

              Thank you very much for your help Ivan.

              I did see that after posting my question. 🙂

              I am now testing the schedule of the workbook through Power Update.

              Thank you for your hints.

              Best regards.

              Jacques-Antoine

              (0) 
      2. Knut Heusermann Post author

        Hi Jacques-Antoine,

        the parameter $inlinecount returns the number of records included in the query result. As Ivan wrote, you can use parameter $top to increase the number of records returned beyond the default of 50.

        However, I didn’t find a way yet to do paging with $skip and $top and you can’t increase $top to any arbitrary number because at some point you will reach the message size and time-out limit … any idea?

        Currently I’m using the inline-aggregation done by ByD before returning the data to make sure that I don’t get too many results (meaning I only select fields that do not lead to single document records).

        BR, Knut

        (0) 
  2. Daniel Berwanger

    by the way, the filter option “or” is available, too, but only for numeric results (maybe more, but I didn’t identified them)…

    e.g. $filter=KCAMTCOMP le -10000 or KCAMTCOMP gt 10000

    (0) 
  3. Ivan Bondarenko

    To avoid confusion I would change word “default” in phrase “Report default selections cannot be overwritten with OData based retrieval => I would recommend to copy the report and remove default selections.” on word “Initial”.

    Because default selection is not the same as Initial selection. We have option to set default selection for all users (doesn’t impact on oData) and peronal default selection for particular user (also doesn’t impact on oData even if credentials of this user used for oData connection). However we have no option to change Initial selection in standard SAP reports.

    This ByD feature makes difficulty for me. If I copy report in the system new report has unique ID (then I have to change initial selection in report wizard, then assign this report – it requires additional time, for 20 tenants it is valuable time). If I do the same in another system – I get new unique ID and I cannot simply copy solution from first system to second avoiding change of report id in connection string.

    In half of major reports we have extension fields, this do not allow to Download/Upload reports from tenant to tenant.

    I think, it would be good to have an opportunity to provide Selection Name or Selection ID in connection string, e.g. with new key word $selection=’some selection name’. If it is ommited – use Initial selection.

    In this case oData user can avoid copying/change/assignment of reports.

    Thanks,

    Ivan

    (0) 
    1. Daniel Berwanger

      I would  like it! 🙂
      with this opportunity you also coukd use standard reports with useless “initial selections”

      But in power query it isn’t a big effort to change (report) IDs

      (0) 
      1. Ivan Bondarenko

        Hi Daniel!

        You are absolutely right! In PQ it is very easy to change ID of report, tenant ID.

        The main problem is to create all reports that I need in all tenants. 🙂 For me it is at least half done. But for future developers it will be a great option.

        (0) 
        1. Knut Heusermann Post author

          Hi Ivan, hi Daniel,

          You can create ByD custom data source and ByD reports using SAP Cloud Applications Studio (aka PDI) as well.

          If you have multiple systems, I would propose to create the extension fields, the custom data sources and the custom reports in one common project using SAP Cloud Applications Studio (aka PDI). Then you can deploy this project to all your systems.

          As result you will have the same technical IDs and names for your extension fields, data sources and reports in all systems.

          Best regards,

          Knut

          (0) 
  4. Raphael Branger

    Hi everybody

    I’m just trying to fetch ByD data into SAP Lumira using the OData 2.0 connector. Using the full flechted URL doesn’t work, like https://<mysystem>.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RPZDC581C0B89C74330601F6FQueryResults?$top=100000

    It seems like Lumira always adds $metadata and tries to read the metadata (and not just process the real data). Therefore I tried https://<mysystem>.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/

    If I run https://<mysystem>.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/$metadata in a browser I get a response – but Lumira only returns the following error:

    com.businessobjects.connectionserver.CSerror: out of range


    If I use http://services.odata.org/Northwind/Northwind.svc/ everything works fine and I’m presented with a dialog to choose from existing tables etc.


    Anyone has experience with connecting Lumira with ByD?

    Cheers Raphael

    (0) 
    1. Knut Heusermann Post author

      Hi Raphael,

      finally I check this as well and I was successful with the following steps:

      1. I created my own analytic report in ByD (actually I copied the standard report “Sales Order Volume” and removed the “Default Values” in step “5 Define Variables”. I reduced the number of selected characteristics as well to make my life in Lumira a little bit easier.
        I assigned my new report to work center view “Account Management – Activities”.
      2. In SAP Lumira I created a Lumira Dataset with Generic OData 2.0 data source and with the Service Root URI: https://myXXXXXX.sapbydesign.com/sap/byd/odata/crm_accountmanagement_analytics.svc.
      3. On the Dataset I selected my custom ByD report in the CATALOG_VIEW tree.
        After clicking on Preview I selected the fields needed for my Lumira report – make sure you select less than 50 fields, because that is the max ByD OData supports.
      4. With clicking on Create you complete the Lumira Dataset and can continue in the Lumira Visualization view.


      Best regards,

      Knut

      (0) 
      1. Jacques-Antoine Ollier

        Hello all,

        Another important thing to notice with SAP Lumira is that for now, the OData connector for SAP Bydesign is only retrieving the first 50 rows, which is the default page size. And we cannot change this array in Lumira through OData.

        SAP is working on this matter and will fix the connector in order to be able to retrieve all the entity data.

        I will get back here as soon as I have some news from SAP development team.

        Thank you for your attention.

        Best regards.

        Jacques-Antoine Ollier

        (0) 
        1. Knut Heusermann Post author

          Hi again,

          if you add ByD OData parameter like $top, $select and $filter on screen “Add new dataset” in field “Custom Authentication Parameter” when creating a new Lumira Dataset, then the parameters seem to be added to the OData URL the right way.

          28-05-2015 10-02-23.gif

          Not sure if this field was designed for that purpose, but it works 😉

          Regards,

          Knut

          (0) 
          1. Jacques-Antoine Ollier

            Hello Knut,

            It is weird to be able to enter parameters here as you could select Key Figures and Characteristics that are not in all the reports under your URI. Lumira calls the root OData svc and not each report if I am not mistaking.

            Anyway, the issue is not when the query is done but when the data is acquired to create the Data Set.

            When I do my query on the report, I have all records. For example 204 records. I can see them all. However, as soon as I click on “Create”, I see the process bar with “Processing data source”, then “Acquiring records 0/204” and it always stops at 50/204 and then creates the data set with only 50.

            This is related to the paging of ByDesign I think.

            It does not stop for you? Can you create the dataset with all of your record in the Prepare view?

            Edit: wow I do not understand anything. Now it is working. 🙂 I am gonna make some tests and get back to the SAP Support for this.

            Thank you for your help and your attention.

            Best regards.

            Jacques-Antoine

            (0) 
  5. Stefan Kampa

    Hello all,

    I wanted to filter items in the odata feed with the not equal Operator but I only get the following error message. Can someone help me?

    Unbenannt.PNG

    (0) 
    1. Ivan Bondarenko

      Hi Stefan!

      Response is “bad request”. From the first sight, everything is good except “$filter=TSTATUS_LFC”.

      I suppose it happens because T* fields are “not filterable”. You should you C* field and use ID of object as criteria.

      BR,

      Ivan

      (0) 
      1. Stefan Kampa

        Hi Ivan,

        thanks for your answer. I also tried the odata request based on a C* field (CIPR_PRODUCT)and got the same result.

        Are there more “not filterable” fields?

        Best Regards,

        Stefan

        (0) 
          1. Jacques-Antoine Ollier

            Hello Knut,

            I am also facing an issue with filterable values.

            I am trying to filter on a specific creation date, the Created On field.

            Here is my query:

            https://my333092.sapbydesign.com/sap/byd/odata/fin_generalledger_analytics.svc/RPZ846FB62043D20B4E8E1706QueryResults?$select=CPROFITCTR_UUID,CCOST_CTR_UUID,CACC_DOC_UUID,CACC_DOC_IT_UUID,CNOTE_IT,TACCDOCTYPE,CFISCPER,CGLACCT,KCBALANCE_CURRCOMP,CPOSTING_DATE&$top=99999&$filter=CCREATION_DATE%20eq%20%2706/25/2015%27

            My query is OK, the only issue is with my filter. I try to filter on the CCREATIONDATE on the 06/25/2015.

            I always get this error even if the field is set as filterable=true:

            <error>

            <code>005056952A631ED0B0FE2236FCAB4627</code>

            <message xml:lang=”en”>Invalid parametertype used at function ‘eq'</message>

            </error>

            Anyone has an idea to fix this?

            Thank you for your attention.

            Best regards.

            Jacques-Antoine

            (0) 
            1. Knut Heusermann Post author

              Hi Jacques-Antoine,

              could you please try the following pattern and datetime format:

              $filter=CCREATIONDATE eq datetime’2015-06-25T00:00:00′ ?

              Best regards,

              Knut

              (0) 
              1. Jacques-Antoine Ollier

                It works Knut!!!

                Thank you very much!

                I did not know we needed to reproduce the data type before entering a value.

                NB: I forwarded your blog to the responsible of SAP ByDesign here in Canada because this functionality with Lumira is not enough shown during demos according to me.

                Thank you for your attention.

                Best regards.

                Jacques-Antoine

                (0) 
  6. Jacques-Antoine Ollier

    Hello Knut,

    Do you know if it is possible to retrieve the formatted name of the fields directly in Lumira and not the technical names?

    I just spent 4 hours renaming all the 300 dimensions of my data set, it was not an incredible experience. 🙂

    Thank you for your attention.

    Best regards.

    Jacques-Antoine Ollier

    (0) 
    1. Knut Heusermann Post author

      Hi Jacques-Antoine,

      I had the same probem. Using the Lumira data source type “Query with SQL” >> “Generic OData 2.0” as described in blog post Global Analytics: Lumira Dashboard with combined Analytics Data of multiple ByD Systems it is today not possible to directly retrieve and parse the field labels returned by the OData metadata.

      I think this would be a good question/post for the Lumira SCN space SAP BusinessObjects Lumira or the Lumira Ideas Place https://ideas.sap.com/SAPLumira.

      Best regards,

      Knut

      (0) 
  7. Knut Heusermann Post author

    Hi everybody,

    Today I added some ideas and considerations how to deal with high volume reports and data sources when working with ByD OData for Analytics.

    Any comments or further ideas are welcome 🙂

    Best regards,

    Knut

    (0) 
  8. Prashanth Rai

    Hi Knut,

    You have already mentioned in the article that Relative selection is also possible, Could you please give an example of how Relative selection filter would look like?

    Regards,

    Prashanth

    (0) 
    1. Knut Heusermann Post author

      Hi Prashanth,

      the relative selections are available for some data sources and characteristics only.

      If you for example create a report based on data source Journal Entries, then you can maintain default selection values for a couple of characteristics, for example the posting date:

      28-08-2015 13-46-13.gif

      Best regards,

      Knut

      (0) 
      1. Prashanth Rai

        Hi Knut,

        I wanted to understand how Relative Selection can be used when consuming the OData service.

        Suppose for example Journal Entry has posting date. If we have to filter on Posting Date from Year to Date, then how do we do use the Relative selection values, while consuming the Odata service for the same.

        /sap/byd/odata/fin_generalledger_analytics.svc/RPFINGLAU01_Q0001QueryResults?$filter=<???>

        Regards,

        Prashanth

        (0) 
  9. Ivan Bondarenko

    Hi everybody,

    can anyone suggest how to filter on boolean fields?

    I’m trying to query Invoice Volume report and filter it by field Cancellation Invoice Indicator ID (CDOC_CANC_IND). My connection string is

    https://myXXXXXX.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RPCRMCIVIB_Q0001QueryResults?$top=100000&$select=CDOC_YEAR,CDOC_MONTH,KCNT_VAL_INV,CDOC_CANC_IND&$filter=CDOC_YEAR eq ‘2015’ and CDOC_STA_RELEASE eq ‘3’ and CDOC_CANC_IND eq false

    However, “CDOC_CANC_IND eq false” doesn’t work. Response is empty. I also tried other options: ”, 1, 0, -1, FALSE, TRUE, true, False, True etc. Nothing works. Some give “bad request”, some – empty response.

    Currently, I omit this criteria, pull everything and make filtration on client side.

    However, I’d prefer to leave this task to server side.

    (0) 
    1. Knut Heusermann Post author

      Hi Ivan,

      the filter expressions &filter=CDOC_CANC_IND eq false respectively &filter=CDOC_CANC_IND eq true are correct. We plan to provide a correction with ByD 1602 to enable this kind of filtering by boolean characteristics.

      Best regards,

      Knut

      (0) 
  10. Jayash Joshi

    Hi Knut,

    You mentioned at the recent Sisheim ByDesign conference that a report variant can be used by an OData call. Do you have an example of how this would be done? Would this be the way to avoid problems of default values?

    Jay

    (0) 
    1. Knut Heusermann Post author

      Hi Jay,

      using OData you cannot refer to ByD report views or selection variants, but you can assemble a “view” using OData parameter $select.

      To avoid selection default values as specified in the report wizard, I would propose to create a copy the report for OData consumption and remove the default values in this report copy.

      Best regards,

      Knut

      (0) 
  11. Ivan Bondarenko

    Hi all!

    Had anyone experience of work with OData using SSO? How OData query should be performed with SSO authorization?

    Any help will be highly appreciated!

    Thanks,

    Ivan

    (0) 
    1. Tobias Kuhn

      Hi Ivan,

      we have experience with with OData and SSO.

      You can use for example the ByD User Credential for the OData reports by extending the URL with saml2=disabled although SSO is enabled.

      Best regards,

      Tobias

      (0) 
      1. Ivan Bondarenko

        Thanks Tobias! But what if standard way of entry is blocked for User, security policy S_BUSINESS_USER_WITHOUT_PASSWORD?

        In this case, is there way to go through SSO steps (redirects) to get right values of logon parameters and how to include them in resulting query?

        I suppose that method of trial and errors can help 🙂 But if someone know how to do this correclty, it would save a lot of time.

        (0) 
  12. SUSANTA DEY SARKAR

    Dear Knut,

    Thank you very much for the wonderful post.

    I tried to access a simple report in SAP Business ByDesign to show all the Users List from the Report “All Current Users (CCAB01_Q001)” under the “Application and User Management Work Center”.

    The report is working fine in the SAP Business ByDesign system.

    2. Report Screenshot.png

    I have derived the URL to access the Meta Data and the Filterable elements of the report. But afterwards I can’t proceed to get the data from the report.

    I need to see the list of all the available users and the filtered records for a particular User ID.

    I have reached till the below mentioned level and found the filterable elements :

    https://myxxxxxx.sapbydesign.com/sap/byd/odata/ana_businessanalytics_analytics.svc/$metadata?entityset=RPCCAB01_Q001QueryResults?$inlinecount=allpages

    5. Report Access by Link Screenshot.png

    But whenever I’m trying to get the report using some User ID or without User ID, I’m not getting any response XML. It’s throwing 404 Error.

    https://myxxxxxx.sapbydesign.com/sap/byd/odata/ana_businessanalytics_analytics.svc/$metadata?entityset=RPCCAB01_Q001QueryResults?$inlinecount=allpages&$select=CIDENTITY&$filter=CIDENTITYeq'G10025

    Can you please help me in this regard to fetch the record set from the report using and without using the filter parameters?

    If this will work smoothly, then we can try to implement the same method for some other reports.

    Thank you very much.

    Best Regards,

    Susanta Dey Sarkar

    15-12-2015

    Bangalore, India

    (0) 
    1. Knut Heusermann Post author
      (0) 
      1. SUSANTA DEY SARKAR

        Dear Knut,

        Thank you very much for your kind support.

        The report is working fine now.

        And can you please provide me the way of assigning the report view and selection at the time of execution?

        If the report has 5 Views and 3 Selections and I want to use any specific View and Selection with some filter parameters like User ID to get the record set, then how these two parameters along with the filter parameters should be assigned to the URL to get the specific set of records?

        Example :

        Report ID        :  REP_ID

        View ID           :  VIEW_ID  

        Selection ID    :  SEL_ID

        Report Retrieve URL :   ?

        Thank you very much.

        Best Regards,

        Susanta Dey Sarkar

        15-12-2015

        (0) 
        1. Knut Heusermann Post author

          Hi Susanta,

          Report views and selection variants cannot be selected using OData.

          OData always executes the report using the initial view/variant.

          However, you could copy the report and define the needed views and variants in your copies.

          Best regards,

          Knut

          (0) 
          1. SUSANTA DEY SARKAR

            Dear Knut,

            Thank you very much for the information.

            I will try to fetch the information in different way by setting the required view and selection as default view and selection.

            But whenever I’m trying to fetch the details from any reports which I have created by SAP Business ByDesign run time or by SDK (Not Standard Report), I’m not getting the Meta Data information from the following OData URL :

            https://myxxxxxx.sapbydesign.com/sap/byd/odata/ana_businessanalytics_analytics.svc/$metadata

            I have assigned the report to some of the work centers also and then again tried but still couldn’t find the reports in the list of meta data under the Business Analytics Work Center Reports also.

            Is it possible to fetch the report information from the reports which are not standard reports (based on standard data sources or partner defined data sources) ?

            Thank you very much.

            Best Regards,

            Susanta Dey Sarkar

            15-12-2015

            (0) 
            1. Knut Heusermann Post author

              Hi Susanta,

              using OData you can access ByD standard reports as well as ByD custom reports created using key user tools and SAP Cloud Applications Studio.

              In your example URL you are using the business analytics work center. This means you are generating and reading all metadata of all standard and custom reports. This may run into a timeout or exceed the size limit.

              Furthermore, for security/data protection reasons, I would recommend to use a user for your integration scenario with minimum authorizations. If you assign the analytics work center to the user used for your integration scenario, then this user has access to almost all data in ByD.

              Therefore I would recommend to assign the report to a suitable work center from a data protection point of view. If needed you can create such a work center view using SAP Cloud Applications Studio as well.

              Please check section “Discover available OData Services and get Meta Data” in the blog post above on how to get the metadata for reports assigned to a work center view or to get the metadata for a specific report.

              Best regards,

              Knut

              (0) 
              1. SUSANTA DEY SARKAR

                https://myxxxxxx.sapbydesign.com/sap/byd/odata/fin_costandrevenue_analytics.svc/$metadata?entityset=RPFINCACU04_Q0004QueryResult

                https://myxxxxxx.sapbydesign.com/sap/byd/odata/fin_costandrevenue_analytics.svc/$metadata?entityset=RPZ8C87117B63EA45B5AF4084QueryResult

                Dear Knut,

                Thank you very much for the information.

                I have figured out the XML and now the Meta Data is getting retrieved for the customer defined report based on a standard data source.

                But facing an issue again in the XML Response.

                Couldn’t able to find the field for the “Display Currency – Conversion Date”.

                But it’s a by default mandatory field and with this information we can’t access the report.

                Can you please open the XML Meta Data for the Standard Report and Customer Defined Report based on a Standard Data Source “FINCACU04” from the above mentioned URL.

                Standard Report Meta Data XML.png

                Partner Created Report Meta Data XML .png

                It would be helpful for me if you kindly provide me the way to get the details about the “Display Currency – Conversion Date” entity.

                Because it’s a mandatory field for these reports.

                Thank you very much.

                Best Regards,

                Susanta Dey Sarkar

                16-12-2015

                (0) 
  13. Yamato Tomaru

    Hello Knut,

    Thank you for sharing this awesome feature in ByDesign.  One of our clients is currently using Odata to extract data and it’s working well so far.

    Right now, my team is introducing Odata for our other client to let them use Odata a part of the interface between ByDesign and their legacy system.

    However, our client is concerned that SAP might not support them if any technical error comes up after the periodic upgrade in ByDesign because Odata is originally developped by Microsoft, not SAP.  Do you think we can rely on SAP or Microsoft when something like that happens?  If the interface stopps working because of Odata error, their system will have a big problem, they want to make sure about it. 

    Any thoughts will be appreciated.

    Yours sincerely,

    Yamato

    (0) 
  14. Raphael Branger

    Hi Knut

    I’m just about to retrive SAP By Design data from our time / activity reports. There we have the field CWORK_DESC which can contain quite a bit of text. Still, using the OData service to import the data into Excel only returns a maximum number of 60 characters. Any idea of how to overcome this limit? I couldn’t find much on the net so far…

    Any help is much appreciated!

    Cheers

    Raphael

    (0) 
    1. Knut Heusermann Post author

      Hi Raphael,

      ByD OData for Analytics is based on ByD analytical reports and ByD data sources and hence truncation of text fields for reporting applies to OData for Analytics as well.

      You could use ByD standard web services of custom web services using SAP Cloud Applications Studio to extract business documents with text collections.

      Best regards,

      Knut

      (0) 
  15. Sven Rassl

    Hello at all,

    First, Thanks to Knut for this helpful document.

    Did anyone tried to consume ODATA Feeds from ByDesign with/in SharePoint?

    Sven

    (0) 
  16. Sandeep N

    Hi Knut,

           Thanks for the detailed overview. We are also looking at consuming OData service from outside ByDesign and get the data into ByDesign. Can you share any details to achieve this ?

    Regards,

    Sandeep

    (0) 
    1. Knut Heusermann Post author

      Hi Sandeep,

      ByD OData for Analytics can be used for read access to ByD reports only. However, we plan OData services for write access as well, but this is not yet available.

      Best regards,

      Knut

      (0) 
  17. Baljinder Sidhu

    Hi Knut,

    I finally made it your forum.

    Firstly great introduction on OData for ByD.

    However as mentioned in my query during the Open SAP course, my issue is very specific where i am using Dell Boomi to extract data from  C4C and ByD. Now we have managed to use the SOAP connector for C4C and this for the best part seems to work ok, with ByD however I have tried the OData connector and get an error “can not find the meta data” even when we have removed “$metadata” from the URL.

    Have you had any experience of integration via Boomi for ByD or can you recommend where we can start to try and resolve our issues.

    Do we need to set up a communication arrangement beforehand?

    Do we need a specific communication scenario?

    The key areas we need to extract are around Financial data and Project related data.

    All advice is greatly appreciated.

    Kind Regards,

    Bal Sidhu

    (0) 
    1. Knut Heusermann Post author

      Hi Bal Sidhu,

      to use ByD OData for Analytics you don’t have to create communication scenarios and communication arrangements, but you can use ByD business users to access the OData interface.

      ByD business users can access ByD reports using OData according their access rights. That means you have to assign the report to a work center view and assign the work center/work center view to the access rights of the business user.

      Best regards,

      Knut

      (0) 
  18. Ivan Bondarenko

    Hi everybody,

    who can advise how to make queries with more or less complex filters?

    For example, how to exclude values:

    1.

    I tried Invoice Volume report CRMCIVIB_Q0001 $select=CDOC_INV_DATE,CDOC_UUID&$filter=CDOC_STA_RELEASE eq ‘3’ and CDOC_UUID ne ‘63620’

    gives me “bad request error”.

    2.

    Another example where I couldn’t find solution – G/L Accounts – Line Items report
    (FINGLAU02_Q0001)

    CSETOFBKS eq ‘Z001’ and CFISCYEAR eq 2016 and CFUNCAREA ne ‘Z063’ and CFUNCAREA ne ‘Z064’

    gives “bad request”

    3.
    How to exclude null values from string fields? For example I would like to exclude reversed and reversal Journal Entries
    CSETOFBKS eq ‘Z001’ and CFISCYEAR eq 2016 and CREVDOCUUID eq ” and CREVOF_ACDOCUUID eq ”
    Doesn’t help
    CSETOFBKS eq ‘Z001’ and CFISCYEAR eq 2016 and CREVDOCUUID eq null and CREVOF_ACDOCUUID eq null
    Gives me empty response

    It would be good if some complex samples would be included in basic post: like filter on date, dates period, combination of gt, lt, eq, ne in one query, exclude of null values from fields of all types: null in dates, strings, decimal, boolean etc. All of this is not obvious, unfortunately.
    Thanks in advance, Ivan

    (0) 
    1. Knut Heusermann Post author

      Hi Ivan,

      in report CRMCIVIB_Q0001 the field CDOC_UUID is restricted to multi-value filters (compare metadata file: sap:filter-restriction=”multi-value“).

      Therefore please use positive filter, for example:

      https://myXXXXXX.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RPCRMCIVIB_Q0001QueryResults?$select=CDOC_INV_DATE,CDOC_STA_RELEASE,CDOC_UUID&$filter=CDOC_STA_RELEASE%20eq%20'3'%20and%20CDOC_UUID%20eq%2063620


      The report metadata of report FINGLAU02_Q0001 does include the field CSETOFBKS (see OData metadata).

      However the set of books is defaulted in the report, which applies to OData access as well.

      The URL w/o set of books should work, for example:

      https://myXXXXXX.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RPFINGLAU02_Q0001QueryResults?$select=CFISCYEAR,CFUNCAREA&$filter=CFISCYEAR%20eq%202016%20and%20CFUNCAREA%20ne%20Z063


      Best regards,

      Knut

      (0) 
      1. Ivan Bondarenko

        Hi Knut,

        Thanks for quick response!

        Was glad to see you at  Open SAP course “Reporting with SAP ByDesign”. Nive overview of ByD capabilities. I wish more videos on this area with a “deep dive into”, with real enterprise samples. Because currently it is NOT easy to find any examples on the internet, hard to solve even simple questions like “how to build $filter string”.

        Sorry, I completely forgot about multi-value restriction. And I did experiments with filtering on a copy of G/L Accounts Line Items where I enabled filtering on Set of Books and removed other criterias from Initial selection.

        But let’s consider field which 100% has no multi-value restriction. To be on the same environment I’ll provide a link to report from demo tenant from Open SAP course:
        https://my336122.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RPFINGLAU02_Q0001QueryResults?$top=100000&$filter=CACCDOCTYPE ne ‘1000’ and CACCDOCTYPE ne ‘4000’&$select=CGLACCT,KCBALANCE_CURRCOMP

        It gives me “bad request” response. And the problem doesn’t related to emptiness of data for Current Fiscal Period. Because query without $filter
        https://my336122.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RPFINGLAU02_Q0001QueryResults?$top=100000&$select=CGLACCT,KCBALANCE_CURRCOMP
        works without error.

        Nevetheless it is very common scenario when user need to exclude several specific values of one field. Assume field contain ~100 values like “Functional area”, it is much easier to “exclude” couple of non-important values (e.g. data migration) instead of make 98 inclusions. Moreover, inclusion option cannot be applied in some situations, when we expect appearance of new values that should be automatically included in report.

        Such query works fine in SAP ByD Excel addin

        GL Selection FA.png
        Another question that is still open – how to filter null values in string fields?

        I’m trying to reproduce same query that I do using ByD Excel addin.

        Thanks in advance,
        Ivan

        (0) 
        1. Knut Heusermann Post author

          Hi Ivan,

          With regards to your use case to exclude values:

          The AND operator is allowed for “between” use cases only like

          <characteristic> ge <value 1> and <characteristic> le <value 2>.

          In your use case to exclude two values you should use the OR operator, for example:

          $filter=CACCDOCTYPE ne ‘1000’ or CACCDOCTYPE ne ‘4000’.

          Best regards,

          Knut

          (0) 
          1. Ivan Bondarenko

            Hi Knut,

            I tried your suggestion, however not fruitful.

            Sample 1

            using AND operator – Bad Request response

            https://my336124.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RPZ53D9189F7072FC58230057QueryResults?$top=100000&$filter=CSETOFBKS eq ‘7000’ and CGLACCT lt ‘105000’ and (CACCDOCTYPE ne ‘00011’ and CACCDOCTYPE ne ‘00078’)&$select=CDOC_DATE,KCAMTCOMP,CGLACCT,CACCDOCTYPE

            Bad Request PQ.png

            Sample 2

            using OR operator without brackets – bad request

            https://my336124.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RPZ53D9189F7072FC58230057QueryResults?$top=100000&$filter=CSETOFBKS eq ‘7000’ and CGLACCT lt ‘105000’ and CACCDOCTYPE ne ‘00011’ or CACCDOCTYPE ne ‘00078’ or CACCDOCTYPE ne ‘00079’&$select=CDOC_DATE,KCAMTCOMP,CGLACCT,CACCDOCTYPE

            Sample 3

            using OR operator with brackets – no error

            https://my336124.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RPZ53D9189F7072FC58230057QueryResults?$top=100000&$filter=CSETOFBKS eq ‘7000’ and CGLACCT lt ‘105000’ and (CACCDOCTYPE ne ‘00011’ or CACCDOCTYPE ne ‘00078’ or CACCDOCTYPE ne ‘00079’)&$select=CDOC_DATE,KCAMTCOMP,CGLACCT,CACCDOCTYPE

            But no filtration as well !

            I receive same 400 rows as without filter on CACCDOCTYPE.

            To be honest, if follow math logic such criteria as (CACCDOCTYPE ne ‘00011’ or CACCDOCTYPE ne ‘00078’ or CACCDOCTYPE ne ‘00079’) is meaningless.

            Each row matches such criteria because of OR. XXXXX always won’t be equal to two of values.

            Sample workbook with queries can be downloaded from https://onedrive.live.com/redir?resid=B3E628F4C7111C0%2135838

            Thanks in advance,

            Ivan

            (0) 
            1. Knut Heusermann Post author

              Hi Ivan,

              you are right – I was too much focused on the “and”/”or” operator: combining “or” with two exclusions does not make sense, even if processed correctly from a mathematical point of view it always returns all entries.

              In fact the “and”-operator for ranges (“between” use cases) like <characteristic> ge <value 1> and <characteristic> le <value 2>.

              To include two values the “or” operator can be used, for example: $filter=CACCDOCTYPE eq ‘1000’ or CACCDOCTYPE eq ‘4000’.

              Excluding two values using the “or” operator (for example: $filter=CACCDOCTYPE ne ‘1000’ or CACCDOCTYPE ne ‘4000’) is not possible.

              Initial or Null Values:

              The selection by initial or null values (for example CCUST_ABC eq ”, CCUST_ABC eq ‘ ‘, CCUST_ABC eq ‘#’, CCUST_ABC eq null) is not supported as of 1605.

              Best regards,

              Knut

              (0) 
              1. Frank Lindqvist

                If we are talking about ge and le, please be ware that if closed validity periods are exposed you can not use ge and le for start and enddate. You have to enter values eq for both start and end date to get a result.

                If you query for a period, you will get everything that was valid at least one day in this period (start and end date belongs to the period, i.e. key date = start date = end date, if you only want to have result for a specific data).

                Best regards

                Frank

                (0) 
  19. SUSANTA DEY SARKAR

    Hello everyone,

    Here are few important points are to be discussed regarding the OData Report accessing.

    Please identify the report which you want to fetch and then one thing you have to remember that by the OData access method, you can access the initial mode of data in the report.

    If you will apply additional filter and other things in the report filters, then that won’t consider in the OData Access Report.

    Example :

    Suppose you want to get the report for Sales Order Volume report and there are 5 Views and 5 Selections you have made.

    But only one Selection will be there named as “initial”.

    If you will select the selection as “initial”, then with that selection whatever data will be produced in the execution of the report, that can be accessed by OData Report access.

    Without adding any additional conditions in the initial selection.

    And one Important point is there.

    The report must be added to some work center and that shouldn’t be hidden.

    And the report access should be there to the user who is trying to access the data.

    • Report ID : CCAB01_Q001

    Thank you very much.

    Best regards,

    Susanta Dey Sarkar

    29-02-2016

    (0) 
  20. Christopher Buck

    Hi,

    I identified problems in some reports when I tried to bring them into Lumira.  I go through the normal steps as shown but I get the following error message :

    Screen Shot 2016-03-02 at 1.14.35 PM.png

    I then modify the Select statement to bring back just one column to test and this works fine

    Screen Shot 2016-03-02 at 1.15.44 PM.png

    I then add further columns to select to determine what might be the problem and I see that the BOOLEAN type field highlight is my issue.  If I have SELECT with all the other columns then I can import that data. 

    Screen Shot 2016-03-02 at 1.16.20 PM.png

    Has anyone else had this issue before with bringing back Boolean data types?

    (0) 
  21. Ivan Bondarenko

    Happy Friday to all!

    Knut, do you know, is there any chance that we can get opportunity to export hierarchies through OData in next releases? (e.g. Reporting Line Units hierarchy)

    Thanks,

    Ivan

    (0) 
  22. Baljinder Sidhu

    Hi All,

    Knut i am not sure if you can help with my query but similar to Ivan i am trying to change ByD standard behavior when it comes to ODATA.

    Basically is it possible to add fields to ByD standard ODATA services for example adding and “Account Determination” or “G/L Account” fields to the ManageSupplierInvoiceIn service?

    I know this information exists for the Supplier Invoice as you can find it at the document level with a “View All” setting? So how if possible could i change the fields that are permitted to be extracted or viewed?

    Many thanks as always for any advice,

    Bal

    (0) 
    1. Jacques-Antoine Ollier

      Hello Bal,

      If you do not find standard fields in the OData service, an easy work around is to duplicate them.

      Do an extension on the corresponding business object with as many elements as you want to duplicate standard fields.

      In a before-save event, populate your extension fields with the standard fields value.

      And finally, enhance the analytics report you are querying through the OData service with your extension fields.

      You should now be able to retrieve them in the OData service.

      Hope that helps.

      Best regards.

      Jacques-Antoine Ollier

      (0) 
    2. Knut Heusermann Post author

      Hi Bal,

      as a key user you can add all fields to a report that are supported by the underlying data source and exposed for usage in reports. This means as well that all extension fields added to the data source and the report are available via OData as well. This leads you to the solution approach proposed by Jacques-Antoine.

      Beyond that you can use the SAP Cloud Applications Studio to build your own custom data sources and reports using fields that part of the ByD public solution model (which are basically all fields visible in the Cloud Studio).

      Best regards,

      Knut

      (0) 
  23. Ronald van Herk

    Hi Knut et all,

    Thank you for this great post. It made my life much easier.

    I have a question about filtering with a required filter.

    I try to use the standard report Employee Turnover Yearly with Odata and it needs the required filter T0CHAR_STRUCTURE.

    I have no idea what the CHAR_STRUCTURE fields means, and maybe this will even not work because it is a T* field?

    Update: When quering (using Powershell) without filter i get this message, so maybe it is a C* field:

    C0CHAR_STRUCTURE (Entity Type RPHCMPAU01_Q0003): mandatory filter is missing

    Maybe you can explain.

    Warm regards,

    Ronald

    (0) 
    1. Ronald van Herk

      I solved this now.

      I found out that you had to include the mandatory field in the $select and have one of the results of the mandatory field in the $filter syntax.

       

      Thanks

       

      (0) 
  24. Jürgen Ravnik

    Hi community,

    I use the OData API of ByDesign to get data from a custom report, but as I just need the data for one specific customer the $filter parameter comes to use, but behaves strange. The ID of one business record changes when the $filter parameter is used, ‘changed’ meaning that the order of the single columns within the ID changes, therefore I am not able to query for the correct ID of a business record which I need.

    For example, this is an ID of a business record without using the $filter parameter (which is actually the correct ID):

    https://my320276.sapbydesign.com/sap/byd/odata/bpm_businesspartnerdata_analytics.svc/RPZ1D5FFD707C113995AFC2C5QueryResults('%7CCCNTRY_CODE%3DAT%7CCEMAIL_URI%3D%23%7CCFRMTD_PSTL_ADDR%3DLiebenauer%20Hauptstra%C3%9Fe%202-6%20%2F%208010%20Graz%20%2F%20AT%7CCINDSSCTR_CODE%3D%23%7CCMOBILE%3D%23%7CCWEB_URI%3D%23%7CCBP_UUID%3D10000046%7CCADDR_FRMTD_PH%3D%23%7C‘)

    And this is the ID for the same business record with using the $filter parameter:

    https://my320276.sapbydesign.com/sap/byd/odata/bpm_businesspartnerdata_analytics.svc/RPZ1D5FFD707C113995AFC2C5QueryResults('%7CCADDR_FRMTD_PH%3D%23%7CCBP_UUID%3D10000046%7CCCNTRY_CODE%3DAT%7CCEMAIL_URI%3D%23%7CCFRMTD_PSTL_ADDR%3DLiebenauer%20Hauptstra%C3%9Fe%202-6%20%2F%208010%20Graz%20%2F%20AT%7CCINDSSCTR_CODE%3D%23%7CCMOBILE%3D%23%7CCWEB_URI%3D%23%7C‘)

    As said before the order of the report columns between the brackets differs.

    The URL used for the examples above is the following (with and without the $filter parameter):

    https://my320276.sapbydesign.com/sap/byd/odata/bpm_businesspartnerdata_analytics.svc/RPZ1D5FFD707C113995AFC2C5QueryResults?$filter=CBP_UUID%20eq%20%2710000046%27

    Could this be a problem of the ByDesign OData API or of the custom report?

    Is there any solution to this?

    Thanks in advance and best regards,

    Jürgen

    (0) 
    1. Knut Heusermann Post author

      Hi Jürgen,

      the idea behind the ID access is rather to navigate from a query result to a specific record using the ID provided by the query result. I never tried to assemble the ID in brackets my self.

      To access a single record I would rather suggest to use selection parameters to choose the fields and filter parameter to fetch the record needed. If you use filter parameter which refers to a unique key (for example the sales order document ID, if your reports shows sales orders) and operator “equal” you can access a single record as well.

      Best regards,

      Knut

      (0) 
      1. Jürgen Ravnik

        Hi Knut,

        sorry if I expressed myself not clear enough, my use case is the following:

        I have the custom report with my customers data, every record has an ID similar to the example IDs I posted above. I need to get this ID (which should be the correct one, as said above) by calling the ByD OData API for further usage in an external system. To avoid searching through all customers data I would like to use the $filter parameter and filter for the business partners UUID (CBP_UUID) so I just get the data for this specific customer and do some string operations to fetch the ID from the HTTP response.

        Now the problem is, when using the $filter parameter the ID changes for some customers (as explained above) and if I use this changed ID (which is not the actual correct ID) I am not able to fetch data in the external system, since a record with this wrong ID can logically not be found.

        Also I must not use the $select parameter, since this would change the ID aswell.

        Therefore my question: Why does the ID change when I use the $filter parameter? Because this makes either the $filter parameter or the ID of the record itself pointless in my opinion.

        Best regards,

        Jürgen

        (0) 
  25. Toke Kit

    Dear Knut,

     

    I want to consume ByD oData service from java application.

    I am using Jersey oData client. I can consume other oData servicees

    successfully with the Jersey oData client. But I cannot consume ByD oData and getting this error. I had tested it with Postman and it works well.

     

    Exception in thread “main” javax.ws.rs.ProcessingException: java.net.ConnectException: Connection timed out: connect

      at org.glassfish.jersey.client.internal.HttpUrlConnector.apply(HttpUrlConnector.java:287)

      at org.glassfish.jersey.client.ClientRuntime.invoke(ClientRuntime.java:252)

      at org.glassfish.jersey.client.JerseyInvocation$1.call(JerseyInvocation.java:684)

      at org.glassfish.jersey.client.JerseyInvocation$1.call(JerseyInvocation.java:681)

      at org.glassfish.jersey.internal.Errors.process(Errors.java:315)

      at org.glassfish.jersey.internal.Errors.process(Errors.java:297)

      at org.glassfish.jersey.internal.Errors.process(Errors.java:228)

      at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:444)

      at org.glassfish.jersey.client.JerseyInvocation.invoke(JerseyInvocation.java:681)

      at org.glassfish.jersey.client.JerseyInvocation$Builder.method(JerseyInvocation.java:411)

      at org.glassfish.jersey.client.JerseyInvocation$Builder.get(JerseyInvocation.java:311)

      at arbalnace_test.ARBalnace_Test.main(ARBalnace_Test.java:36)

    Caused by: java.net.ConnectException: Connection timed out: connect

      at java.net.DualStackPlainSocketImpl.connect0(Native Method)

      at java.net.DualStackPlainSocketImpl.socketConnect(DualStackPlainSocketImpl.java:79)

      at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:339)

      at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:200)

      at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:182)

      at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:172)

      at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)

      at java.net.Socket.connect(Socket.java:579)

      at sun.security.ssl.SSLSocketImpl.connect(SSLSocketImpl.java:625)

      at sun.net.NetworkClient.doConnect(NetworkClient.java:175)

      at sun.net.www.http.HttpClient.openServer(HttpClient.java:432)

      at sun.net.www.http.HttpClient.openServer(HttpClient.java:527)

      at sun.net.www.protocol.https.HttpsClient.<init>(HttpsClient.java:264)

      at sun.net.www.protocol.https.HttpsClient.New(HttpsClient.java:367)

      at sun.net.www.protocol.https.AbstractDelegateHttpsURLConnection.getNewHttpClient(AbstractDelegateHttpsURLConnection.java:191)

      at sun.net.www.protocol.http.HttpURLConnection.plainConnect(HttpURLConnection.java:933)

      at sun.net.www.protocol.https.AbstractDelegateHttpsURLConnection.connect(AbstractDelegateHttpsURLConnection.java:177)

      at sun.net.www.protocol.http.HttpURLConnection.getInputStream(HttpURLConnection.java:1301)

      at java.net.HttpURLConnection.getResponseCode(HttpURLConnection.java:468)

      at sun.net.www.protocol.https.HttpsURLConnectionImpl.getResponseCode(HttpsURLConnectionImpl.java:338)

      at org.glassfish.jersey.client.internal.HttpUrlConnector._apply(HttpUrlConnector.java:394)

      at org.glassfish.jersey.client.internal.HttpUrlConnector.apply(HttpUrlConnector.java:285)

      … 11 more

    C:\Users\113414A008CNL\AppData\Local\NetBeans\Cache\8.1\executor-snippets\run.xml:53: Java returned: 1

    BUILD FAILED (total time: 22 seconds)

     

    Best regards,

    Toke Kit

    (0) 
  26. Rajesh Nimmakayala

     

    Hi All,

    It was very informative blog with all OData information related to ByD Analytics. I followed the blog for quite some time. I haven’t seen any information regarding range in Query by joining 2 Fields(CLOGON_DATE and CLOGON_TIME) together for specific range.  Can we query for a Range with 2 Fields(CLOGON_DATE and CLOGON_TIME) together ? ? Like Date(CLOGON_DATE) and Time(CLOGON_TIME) between “26 Nov 2016 11:47:47 AM” and “30 Dec 2016 11:47:47 AM” ?

     

    I tried with below Link and currently we are seeing incorrect results in Output for below Link(Query) where Date(CLOGON_DATE) “GE” “26 Nov 2016” and Time(CLOGON_TIME) “GE” “11:47:47 AM“. It’s skipping values for Time less than “11:47:47 AM” for Dates greater than “26 Nov 2016“. How can we get the correct data ?

    https://XXXXXXXX.crm.ondemand.com:443/sap/byd/odata/seodadminwcf_analytics.svc/RPCOD_USER_LOGON_ANA_Q0001QueryResults?$filter=CLOGON_DATE ge datetime’2016-11-29T00:00:00′ and CLOGON_TIME ge time’PT11H47M47S’&$inlinecount=allpages

    Thanks for the support.

    Thanks,
    Rajesh N

    (0) 
  27. Knut Heusermann Post author

    Hi everybody,

    I updated the blog post to add new OData features with regards to filtering capabilities. Please check in particular the chapters “Filter Options” and “High Volume Data Sources“.

    Best regards,
    Knut

     

    (1) 
    1. Ivan Bondarenko

      Hi Knut,

      earlier I could use filter on Key Figures in order to suppress zeros in result. Sample of filter for G/L Accounts – Line Items:

      &$filter=CSETOFBKS eq ‘Z001’ and KCVALUQTY ne 0 and CGLACCT eq ‘130*’ and CACCYEARPER le 22017

      however, this method doesn’t work after 1702. I’ve read your article once again but haven’t found any statement about filtering by Key Figures. Could you please add this in your guidance?

      Thanks in advance,

      Ivan

      (1) 
      1. Christine Toblier

         

        Hi Ivan, hi Knut,

        I had the same issue too. I was wondering, why the filtering by Key Figures does not work any more. I was able to do that a few weeks ago and now it does not work anymore.

        It could be since release upgrade. Is that a bug, that is going to be fixed or is there Workaround for that? We would really need that feature as we built a Integration Scenario on that.

        Kind regards,

        Christine

         

         

        (0) 
  28. Akif Farhan

    Hi

    How can we get the xml schema of ODATA ? We are using middle-ware integration tool (IBM Cast Iron) and in order to invoke ODATA API we need to provide the schema of ByDesign ODATA. Is there any way we can download it from ByDesign or automatically generate it in some way by providing the ByDesign Odata URL?

    We have tried to copy the contents of $metadata of a particular report and save it as an XML file, but when we use it in Cast Iron, we get the error “the document is not a schema@http //www.w3.org/2001/xmlschema”

    Any way to figure it out?

    Thank you in advance. Akif

    (0) 
  29. Daniel Weinberg

    Hi Knut,

     

    First of all thanks for the great blog. I am facing an issue with OData. I try to get data for a report that returns data when I call it from the UI. When I call it via OData with the exact same selection criteria I do not get any records:

    https://myXXXXXX.sapbydesign.com/sap/byd/odata/CRM_WOC_SALESORDERS_analytics.svc/RPCRMCCOIB_Q0001QueryResults?$filter=BYD_P_CURDATE%20eq%20datetime%272017-04-01T00:00:00%27

    Response is:

    <?xml version=”1.0″ encoding=”utf-8″?>
    <feed xml:base=”https://myXXXXXX.sapbydesign.com/sap/byd/odata/CRM_WOC_SALESORDERS_analytics.svc/” xmlns=”http://www.w3.org/2005/Atom” xmlns:m=”http://schemas.microsoft.com/ado/2007/08/dataservices/metadata” xmlns:d=”http://schemas.microsoft.com/ado/2007/08/dataservices”>
    <id>https://myXXXXXX.sapbydesign.com/sap/byd/odata/CRM_WOC_SALESORDERS_analytics.svc/RPCRMCCOIB_Q0001QueryResults</id&gt;
    <title type=”text”>RPCRMCCOIB_Q0001QueryResults</title>
    <updated>2017-04-03T07:25:16Z</updated>
    <author>
    <name/>
    </author>
    <link href=”RPCRMCCOIB_Q0001QueryResults” rel=”self” title=”RPCRMCCOIB_Q0001QueryResults”/>
    <m:count>0</m:count>
    </feed>

    Hope you can help.

    Best Regards,

    Daniel

    (0) 
  30. Knut Heusermann Post author

    Update in Chapter Filter Parameter:

    I added an example to combine multiple intervals using brackets:

    https://myXXXXXX.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RPZB9A728B38DD8A0CC1C0337QueryResults?$inlinecount=allpages
    &$select=CFISCALYEARPERIO,CPOSTING_DATE,KCZ0COUNT
    &$filter=PARA_COMPANY eq ‘1000’ and PARA_SETOFBKS eq ‘7000’
    and ( PARA_FISCYEARPER ge 52016 and PARA_FISCYEARPER le 72016 )
    and ( PARA_POSTDAT ge datetime’2016-06-25T00:00:00′ and PARA_POSTDAT le datetime’2016-07-04T00:00:00′ )&$top=1000

     

    Best regards,
    Knut

    (0) 
  31. Matt Evans

    I’m not sure how long it’s been supported but our implementation has long been missing gzip encoding & decoding. Regardless of the time to compress/deflate, this is an advantage on the client side implementation of OData and currently works with both OData Modeler custom endpoints and OData Analytic Reports as long as the following HTTP Header is included in the request:

    Accept-Encoding: gzip, deflate

    @Knut Heusermann– Do you know of any problems with using GZip and OData Analytics or is it generally recommended in every case in order to reduce HTTP traffic?

    Best Regards

    Matt

    (0) 
    1. Knut Heusermann Post author

      Hi Lee,

      The ByD consolidation preparation is based on ByD reports and using OData you can extract ByD report data. Therefore the answer is: Yes, but please take into account volume and delta extraction using proper filter as described in my blog.

      Best regards,
      Knut

      (0) 
  32. Lee Bown

    Hi Knut,

    Thanks for your reply.

    Assuming the 3rd party system can construct the correct URL to query the correct ByD analytics, how does the 3rd party system access the OData without the need for user credentials?

    Thanks,

    Lee

     

    Thanks,
    Lee

    (0) 
    1. Knut Heusermann Post author

      Hi Lee,

      ByD does not allow API access without authentication and authorization check. Possible authorization methods are for example basic authentication (user/password) and OAuth 2.0 SAML token Bearer protocol.

      Best regards,
      Knut

      (0) 

Leave a Reply