Using Filter and Selection Parameter in ByD OData ...

knutheusermann · ‎02-08-2017

SAP Business ByDesign release 1702 comes with some major enhancements with regards to the ByD OData for Analytics API. OData filter parameter now support report selection parameter as well as personalized characteristics and master data characteristics. Overall OData API filters are now much closer to the embedded ByD reporting UI which makes it easier to understand OData for Analytics filtering capabilities and to optimize your integration scenario with regards to runtime and memory consumption.

The following OData examples illustrate existing and new possibilities to use OData for Analytics filter parameter. For general information and more details about ByD OData for Analytics please read my SAP Community blog post OData for SAP Business ByDesign Analytics.

Use Cases

Filter by Report Characteristics and Report Selection Parameter
1. Metadata using work center business analytics (entity container ana_businessanalytics_analytics.svc)
2. Metadata using work center for business user (for example entity container crm_customerinvoicing_analytics.svc)
3. OData request w/o parameter
4. OData request w/ select
5. OData request w/ select incl. a property referring to a selection parameter (negative test raising an error message)
6. Filter on selection parameter
7. Filter on characteristic
8. Filter on selection parameter and characteristic using same value
9. Filter on selection parameter and characteristic using different values

Report Fixed Value Selections
1. Metadata (negative test: characteristic with fixed value selection is not listed as selection parameter)
2. Repeat all tests of use case 1 and observe, that the fixed value selection always applies first

Report Initial Selection Default Values
1. OData request w/o parameter
2. Filter on selection parameter overwriting the Initial Selection Default Values
3. Filter on selection parameter not covered by Initial Selection Default Values
4. Filter on characteristic overwriting the Initial Selection Default Values (negative test)
5. Filter on characteristic not covered by Initial Selection Default Values

Report Default Selection Variants
1. OData request w/o parameter (negative test)

Personalized characteristics and selection parameter
1. Characteristics added in work center Business Analytics
2. Characteristics added by business users
3. Master data characteristics and selection parameter

Extension fields
1. Metadata
2. Use extension field as Fixed Value Selection, OData request w/o parameter
3. Use extension field with Initial Selection Default Value, OData request w/o parameter
4. Filter on extension field as personalized selection parameter
5. Filter on extension field as personalized characteristic

Glossary

Let me start with a little glossary of terms and definitions used in my examples to ensure a common understanding of terms and report features:

Key figure

Characteristic

OData Property - OData properties are fields exposed by the OData API incl. characteristics, key figures and selection parameter.

Selection parameter - Report characteristics used as selection criteria on the database (aka drill-down fields).

OData Filter Parameter - Report selection parameter or report characteristics used to filter report records.

Report Filter (UI) - A possibility to filter the records displayed on the UI; a pure UI feature not relevant for OData at all.

Initial View - The initial report view for all users defined in the report wizard.

Fixed Value Selection - A fixed selection criteria applied on database level and defined in the report wizard.

Initial Selection Default Values - Default values for selection parameter defined in the report wizard.

Report View - Any report view configured by a user in the report UI.

Report Default View - A report view configured as default view by a key user or business user in the report UI.

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

Personalized characteristic (Added field) - A characteristic added by a key user or business user in the report UI.

Personalized selection parameter (Added field) - A characteristic added as selection parameter by a key user or business user in the report UI.

Personalized key figure (Added field) - A key figure added by a key user or business user in the report UI.

Master data characteristic - Master data characteristics are characteristics that can be that added to a report from a master data object using report personalization.

Extension field

Use Case 1: Filter by Report Characteristics and Report Selection Parameter

Create extension fields for customer invoices and edit some extension field values to be able to select/filter by these values later on.

Create a custom report as copy of standard report Invoice Volume with report ID CRMCIVIB_Q0001. Select some key figures and characteristics. Try to cover all dimensions, but make sure to select less than 50 key figures and characteristics in total.
For the following examples the following key figures, characteristics and selection parameter should be included:

Key Figures: Total Formatted, Counter Formatted

Characteristics: Account, Invoice Date, Sales Unit, Product Category

Characteristics marked as selection parameter: Sales Unit, Product Category

The following characteristics should not be included: Item Processing Type, Payment Method, Ship-To, because we will use them for personalization.

Assign the report to some business user work center, e.g. work center Customer Invoicing.

Custom Report ID for the following examples is Z2321FC51ED9EE5457F43EC, which is a copy of report Invoice Volume with report ID CRMCIVIB_Q0001.

Base URL using work center Business Analytics (entity container ana_businessanalytics_analytics.svc😞
https://myXXXXXX.sapbydesign.com/sap/byd/odata/ana_businessanalytics_analytics.svc/

Base URL using a work center for business users (for example entity container crm_customerinvoicing_analytics.svc😞
https://myXXXXXX.sapbydesign.com/sap/byd/odata/crm_customerinvoicing_analytics.svc/

Metadata using work center business analytics:
myXXXXXX.sapbydesign.com/sap/byd/odata/crm_customerinvoicing_analytics.svc/$metadata?entityset=RPZ2321FC51ED9EE5457F43ECQueryResults

Metadata using work center for business user:
myXXXXXX.sapbydesign.com/sap/byd/odata/crm_customerinvoicing_analytics.svc/$metadata?entityset=RPZ2321FC51ED9EE5457F43ECQueryResults

Observe:

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 property referring to the selection parameter is indicated as sap:selection="true" together with the selection type sap:selection-type="multi-value" in the property attributes.

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 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 database selection result only, depending on the overall report setup.

OData request w/o parameter:
myXXXXXX.sapbydesign.com/sap/byd/odata/crm_customerinvoicing_analytics.svc/RPZ2321FC51ED9EE5457F43ECQueryResults
Note: The response does not contain properties referring to the selection parameter, e.g. PAR_SEL_IP_SALES_UNIT.

OData request w/ select:
myXXXXXX.sapbydesign.com/sap/byd/odata/ana_businessanalytics_analytics.svc/RPZ2321FC51ED9EE5457F43ECQueryResults?$format=atom&$select=CDOC_INV_DATE,CDPY_BUYER_UUID,CIPR_REFO_CATCP,CIP_SALES_UNIT,FCITM_GR_AM_RC,FCZ0COUNT,TDPY_BUYER_UUID,TIPR_REFO_CATCP

OData request w/ select incl. a property referring to a selection parameter (negative test raising an error message):
myXXXXXX.sapbydesign.com/sap/byd/odata/ana_businessanalytics_analytics.svc/RPZ2321FC51ED9EE5457F43ECQueryResults?$format=atom&$select=CDOC_INV_DATE,CDPY_BUYER_UUID,CIPR_REFO_CATCP,CIP_SALES_UNIT,FCITM_GR_AM_RC,FCZ0COUNT,TDPY_BUYER_UUID,TIPR_REFO_CATCP,PAR_SEL_IP_SALES_UNIT
Note: The system returns error message: "Invalid Property PAR_SEL_IP_SALES_UNIT of Entity type RPZ2321FC51ED9EE5457F43EC"

Filter on selection parameter:
myXXXXXX.sapbydesign.com/sap/byd/odata/ana_businessanalytics_analytics.svc/RPZ2321FC51ED9EE5457F43ECQueryResults?$format=atom&$select=CDOC_INV_DATE,CDPY_BUYER_UUID,CIPR_REFO_CATCP,CIP_SALES_UNIT,FCITM_GR_AM_RC,FCZ0COUNT,TDPY_BUYER_UUID,TIPR_REFO_CATCP&$filter=PAR_SEL_IP_SALES_UNIT eq 'GCFR42100'&$inlinecount=allpages

Filter on characteristic:
myXXXXXX.sapbydesign.com/sap/byd/odata/ana_businessanalytics_analytics.svc/RPZ2321FC51ED9EE5457F43ECQueryResults?$format=atom&$select=CDOC_INV_DATE,CDPY_BUYER_UUID,CIPR_REFO_CATCP,CIP_SALES_UNIT,FCITM_GR_AM_RC,FCZ0COUNT,TDPY_BUYER_UUID,TIPR_REFO_CATCP&$filter=CIP_SALES_UNIT eq 'GCFR42100'&$inlinecount=allpages

Filter on selection parameter and characteristic using same value:
myXXXXXX.sapbydesign.com/sap/byd/odata/ana_businessanalytics_analytics.svc/RPZ2321FC51ED9EE5457F43ECQueryResults?$format=atom&$select=CDOC_INV_DATE,CDPY_BUYER_UUID,CIPR_REFO_CATCP,CIP_SALES_UNIT,FCITM_GR_AM_RC,FCZ0COUNT,TDPY_BUYER_UUID,TIPR_REFO_CATCP&$filter=CIP_SALES_UNIT eq 'GCFR42100' and PAR_SEL_IP_SALES_UNIT eq 'GCFR42100'

Filter on selection parameter and characteristic using different values:
myXXXXXX.sapbydesign.com/sap/byd/odata/ana_businessanalytics_analytics.svc/RPZ2321FC51ED9EE5457F43ECQueryResults?$format=atom&$select=CDOC_INV_DATE,CDPY_BUYER_UUID,CIPR_REFO_CATCP,CIP_SALES_UNIT,FCITM_GR_AM_RC,FCZ0COUNT,TDPY_BUYER_UUID,TIPR_REFO_CATCP&$filter=CIP_SALES_UNIT eq 'GCFR42100' and PAR_SEL_IP_SALES_UNIT eq 'GCZA-1200'
Note: The system returns an empty response, because the sales unit cannot have 2 different values at the same time.

Use Case 2: Report Fixed Value Selections

Open report wizard, step "4 - Characteristic Properties" and Set Fixed Value Selections for some characteristics, for example Sales Unit = 'GCFR42100'.

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

Metadata (negative test: characteristic with fixed value selection is not listed as selection parameter):
myXXXXXX.sapbydesign.com/sap/byd/odata/ana_businessanalytics_analytics.svc/$metadata?entityset=RPZ2321FC51ED9EE5457F43ECQueryResults
Note: The Sales Unit is no longer listed as property referring to a report selection parameter.

Repeat all tests from use case 1 and observe, that the fixed value selection always applies first…

OData request w/o parameter and w/ select:
Observe: The OData request returns customer invoices with sales unit GCFR42100 only.

Filter on selection parameter:
Observe: The system returns error message "Property PAR_SEL_IP_SALES_UNIT not found in type RPZ2321FC51ED9EE5457F43ECQueryResult"

Filter on characteristic:
myXXXXXX.sapbydesign.com/sap/byd/odata/ana_businessanalytics_analytics.svc/RPZ2321FC51ED9EE5457F43ECQueryResults?$format=atom&$select=CDOC_INV_DATE,CDPY_BUYER_UUID,CIPR_REFO_CATCP,CIP_SALES_UNIT,FCITM_GR_AM_RC,FCZ0COUNT,TDPY_BUYER_UUID,TIPR_REFO_CATCP&$filter=CIP_SALES_UNIT eq 'GCFR42100'&$inlinecount=allpages

Note:

Filtering by the characteristic sales unit 'GCFR42100' ($filter=CIP_SALES_UNIT eq 'GCFR42100') returns customer invoices with sales unit GCFR42100

Filtering by the characteristic sales unit 'GCFR42100' ($filter=CIP_SALES_UNIT eq 'GCZA-1200') returns no customer invoices because the fixed value selection is applied first and cannot be overruled by OData filter

Conclusion:

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

Remove the report fixed value selection before you go ahead with use case 3.

Use Case 3: Report Initial Selection Default Values

Open report wizard, step "5 - Define Variables" and Maintain Default Values for some characteristics, for example Sales Unit = 'GCFR42100' and Product Category = '10-40'.

OData request w/o parameter:
Note: The system returns customer invoices according the maintained initial selection default values.

Filter on selection parameter overwriting the Initial Selection Default Values for sales unit and product category:
myXXXXXX.sapbydesign.com/sap/byd/odata/ana_businessanalytics_analytics.svc/RPZ2321FC51ED9EE5457F43ECQueryResults?$format=atom&$select=CDOC_INV_DATE,CDPY_BUYER_UUID,CIPR_REFO_CATCP,CIP_SALES_UNIT,FCITM_GR_AM_RC,FCZ0COUNT,TDPY_BUYER_UUID,TIPR_REFO_CATCP&$filter=PAR_SEL_IP_SALES_UNIT eq 'GCNL-1200' and PAR_SEL_IPR_REFO_CATCP eq '80-10'&$inlinecount=allpages
Note: Initial selection default values can be overwritten by OData selection parameter

Filter on selection parameter overwriting the Initial Selection Default Value for sales unit only:
myXXXXXX.sapbydesign.com/sap/byd/odata/ana_businessanalytics_analytics.svc/RPZ2321FC51ED9EE5457F43ECQueryResults?$format=atom&$select=CDOC_INV_DATE,CDPY_BUYER_UUID,CIPR_REFO_CATCP,CIP_SALES_UNIT,FCITM_GR_AM_RC,FCZ0COUNT,TDPY_BUYER_UUID,TIPR_REFO_CATCP&$filter=PAR_SEL_IP_SALES_UNIT eq 'MC45000'&$inlinecount=allpages
Note: Initial selection default values that are not provided in the OData request always apply

Filter on selection parameter not covered by Initial Selection Default Values:
myXXXXXX.sapbydesign.com/sap/byd/odata/ana_businessanalytics_analytics.svc/RPZ2321FC51ED9EE5457F43ECQueryResults?$format=atom&$select=CDOC_INV_DATE,CDPY_BUYER_UUID,CIPR_REFO_CATCP,CIP_SALES_UNIT,FCITM_GR_AM_RC,FCZ0COUNT,TDPY_BUYER_UUID,TIPR_REFO_CATCP&$filter=PAR_SEL_DOC_INV_DATE ge datetime'2016-12-01T00:00:00'&$inlinecount=allpages
Note: Additional selection parameter beyond the initial selection default values can be applied using OData filter to refine the result

Filter on characteristic overwriting the Initial Selection Default Values (negative test):
myXXXXXX.sapbydesign.com/sap/byd/odata/ana_businessanalytics_analytics.svc/RPZ2321FC51ED9EE5457F43ECQueryResults?$format=atom&$select=CDOC_INV_DATE,CDPY_BUYER_UUID,CIPR_REFO_CATCP,CIP_SALES_UNIT,FCITM_GR_AM_RC,FCZ0COUNT,TDPY_BUYER_UUID,TIPR_REFO_CATCP&$filter=CIP_SALES_UNIT eq 'MC45000'&$inlinecount=allpages
Note: The OData request returns an empty result, initial selection default values cannot be overwritten by OData filter properties referring to a report characteristic

Filter on characteristic not covered by Initial Selection Default Values:
myXXXXXX.sapbydesign.com/sap/byd/odata/ana_businessanalytics_analytics.svc/RPZ2321FC51ED9EE5457F43ECQueryResults?$format=atom&$select=CDOC_INV_DATE,CDPY_BUYER_UUID,CIPR_REFO_CATCP,CIP_SALES_UNIT,FCITM_GR_AM_RC,FCZ0COUNT,TDPY_BUYER_UUID,TIPR_REFO_CATCP&$filter=CDOC_INV_DATE ge datetime'2016-12-01T00:00:00'&$inlinecount=allpages

Conclusion:

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.

Use Case 4: Report Default Selection Variants

Login as key user and open work center Business Analytics, view Design Reports.

Open the Report Wizard, step "5 - Define Variables" and observe that the Initial Selection contains Default Values for some characteristics, for example:

Sales Unit = 'GCFR42100'

Product Category = '10-40'

Edit the report with your web browser and edit the report selection parameter incl. overwriting the Initial Selection Default Values, for example:

Sales Unit = 'MC43200'

Product Category = '20'

Click on Set As Default to save the selection variant as Default Selection Variant.

OData request w/o parameter:
myXXXXXX.sapbydesign.com/sap/byd/odata/crm_customerinvoicing_analytics.svc/RPZ2321FC51ED9EE5457F43ECQueryResults?$format=json&$inlinecount=allpages

Observe:

The Initial Selection Default Values (Sales Unit = 'GCFR42100', Product Category = '10-40') are applied by the system; the Report Default Selection Variant is not taken into account. The same applies for all non-default report selection variants.

Conclusion:

Report selection variants are not taken into account by the OData API.

Use Case 5: Personalized characteristics and selection parameter

Key user using work center Business Analytics

Login as key user and open work center Business Analytics, view Design Reports.

Select your report and click on "Edit With Web Browser".
Click on "Add Fields" and add a characteristic to the list of fields not currently shown ("Display in Report") and to the selection area ("Display in Selection Area"). In my example I added the field "Item Processing Type".
Click on "OK" and check if the fields added are available in the report.

Request OData Metadata:
Observe that the list of properties contains 2 new properties:

the characteristic Item Processing Type ID (CITM_PROC_TYP_CD) as filterable characteristic

and the corresponding text property Item Processing Type Text (TITM_PROC_TYP_CD).

Note, that the personalized characteristic Item Processing Type has not been added as property referring to the selection parameter (sap:selection="true").

OData request w/o parameter:
Observe, that the system returns the 2 new properties.

Filter on personalized characteristic:
myXXXXXX.sapbydesign.com/sap/byd/odata/ana_businessanalytics_analytics.svc/RPZ2321FC51ED9EE5457F43ECQueryResults?$format=atom&$select=CDOC_INV_DATE,CDPY_BUYER_UUID,CIPR_REFO_CATCP,CIP_SALES_UNIT,FCITM_GR_AM_RC,FCZ0COUNT,TDPY_BUYER_UUID,TIPR_REFO_CATCP,CITM_PROC_TYP_CD,TITM_PROC_TYP_CD&$inlinecount=allpages&$filter=PAR_SEL_IP_SALES_UNIT eq 'MC42162' and PAR_SEL_IPR_REFO_CATCP eq '50-10' and CITM_PROC_TYP_CD eq 'CCMI'
Note: The filter on the personalized characteristic is applied as expected; in this example credit memo items are returned only.

Request OData Metadata using some other business user:
Note: Personalized characteristics that are added as key user using work center Business Analytics are available to all users.

Conclusion:

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

Business user using a business user work center

Assume you are working with 3 ByD users:

User A with access to work center Business Analytics (acting as key user) as well as to work center Customer Invoicing (acting as business user)

User B with access to work center Customer Invoicing only (always acting as business user)

Login as user A and open work center Customer Invoicing, view Reports List, and open the report.
Click on "Add Fields" and add a characteristic to the list of fields not currently shown ("Display in Report") and to the selection area ("Display in Selection Area"). In my example I added the field "Payment Method".
Click on "OK" and check if the fields added are available in the report.

Login as user B and open work center Customer Invoicing, view Reports List, and open the report.
Click on "Add Fields" and add a characteristic to the list of fields not currently shown ("Display in Report") and to the selection area ("Display in Selection Area"). In my example I added the field "Ship-To".
Click on "OK" and check if the fields added are available in the report.

Request OData Metadata with login as user A (acting as key user) using entity set ana_businessanalytics_analytics:
myXXXXXX.sapbydesign.com/sap/byd/odata/ana_businessanalytics_analytics.svc/$metadata?entityset=RPZ2321FC51ED9EE5457F43ECQueryResults
OData request w/o parameter:
myXXXXXX.sapbydesign.com/sap/byd/odata/ana_businessanalytics_analytics.svc/RPZ2321FC51ED9EE5457F43ECQueryResults
Observe: In both OData responses the properties Payment Method and Ship-To are not included.

Request OData Metadata with login as user A (acting as business user) using entity set crm_customerinvoicing_analytics:
myXXXXXX.sapbydesign.com/sap/byd/odata/crm_customerinvoicing_analytics.svc/$metadata?entityset=RPZ2321FC51ED9EE5457F43ECQueryResults
OData request w/o parameter:
myXXXXXX.sapbydesign.com/sap/byd/odata/crm_customerinvoicing_analytics.svc/RPZ2321FC51ED9EE5457F43ECQueryResults
Observe: In both OData responses the property Payment Method is included as characteristics for ID and text, and the property Ship-To is not included.

Request OData Metadata with login as another business user B using entity set crm_customerinvoicing_analytics:
myXXXXXX.sapbydesign.com/sap/byd/odata/crm_customerinvoicing_analytics.svc/$metadata?entityset=RPZ2321FC51ED9EE5457F43ECQueryResults
OData request w/o parameter:
myXXXXXX.sapbydesign.com/sap/byd/odata/crm_customerinvoicing_analytics.svc/RPZ2321FC51ED9EE5457F43ECQueryResults
Observe: In both OData responses the property Payment Method is not included and the property Ship-To is included as characteristics for ID and text.

Conclusion:

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.

Master data characteristics and selection parameter

Master data characteristics are characteristics that can be that added to a report from a master data object using report personalization.

Login as key user and open work center Business Analytics, view Design Reports.
Select your report and click on "Edit With Web Browser".
Click on "Add Fields", expand some master data object (for example "Account") and add a characteristic to the list of fields not currently shown ("Display in Report") and to the selection area ("Display in Selection Area"). In my example I added the field "Industry (Account)" following the path "Dimension: Business Partner/Account/Industry".
Click on "OK" and check if the added field is available in the report as characteristic as well as selection parameter.

Request OData Metadata:
Note that the list of properties contains 2 new properties: the characteristic Industry ID as filterable characteristic and the corresponding text property Industry Text. Note furthermore that the master data characteristic has not been added as property referring to the selection parameter (sap:selection="true").

OData request w/o parameter:
Observe, that the system returns the 2 new properties.

Conclusion:

Master data characteristics can be added to ByD reports using personalization and can be used by the OData API similar to other personalized characteristics and selection parameter.

Use Case 6: Extension fields

Add a key user extension field to your business object if not done already. Edit the extension field in some business object instances to be able to test filtering later on.

Open the Report Wizard, add the extension field in step "3 - Select Characteristics" and enable the extension field for value selection in step "4 - Characteristic Properties" .

Request OData Metadata:
Note that the extension field is added as property referring to a report characteristic as well as property referring to a report selection parameter. Note as well, that the separator "-" has to be passed as "s" in lowercase, for example "Cs1ANsDBABF71A70044F3" and "PAR_SEL_s1ANsDBABF71A70044F3".

Use extension field as Fixed Value Selection:

Open the Report Wizard, step "4 - Characteristic Properties" and choose Set Fixed Value Selections for the extension field.

OData request w/o parameter:
Note, that the fixed value selection of the extension field is applied by the OData API as well.

Use extension field as Initial Selection Default Value:

Open the Report Wizard, remove the fixed value selection and maintain a Report Initial Selection Default Value for the extension field.

OData request w/o parameter:
myXXXXXX.sapbydesign.com/sap/byd/odata/ana_businessanalytics_analytics.svc/RPZ2321FC51ED9EE5457F43ECQueryResults?$format=atom&$select=CDOC_INV_DATE,CDPY_BUYER_UUID,CIPR_REFO_CATCP,CIP_SALES_UNIT,FCITM_GR_AM_RC,FCZ0COUNT,TDPY_BUYER_UUID,TIPR_REFO_CATCP,CITM_PROC_TYP_CD,TITM_PROC_TYP_CD,Cs1ANsDBABF71A70044F3&$inlinecount=allpages&$filter=PAR_SEL_s1ANsDBABF71A70044F3 eq 'KHi1'
Note: The Initial Selection Default Value for extension fields is applied on OData requests as well and can be overwritten using the corresponding property referring to the report selection parameter.

Former Member · ‎03-07-2017

Hello Knut,

First of all, thanks for your post, it was very helpfull !

I am also trying to filter a report on the Initial Selection Default Values “PARA_CAL_DAY” :

The query is :

https://myXXXXX.sapbydesign.com/sap/byd/odata/crm_customerinvoicing_analytics.svc/RPCRMCIVIB_Q0001Qu...

But I received the error : “Erreur système ds programme CL_RSBOLAP_QUERY_VIEW et formulaire VARIABLE_PROCESSING_FAILED”.

I also tried :

$filter=(MyFilter1) and (MyFilter2)
$filter=(MyFilter1 and MyFilter2)
$filter=(MyFilter1 AND MyFilter2)

But I still have the same error…

Some informations :

Without the ‘and’ clause, the filter works : https://myXXXXX.sapbydesign.com/sap/byd/odata/crm_customerinvoicing_analytics.svc/RPCRMCIVIB_Q0001Qu...
The metadata of this field are :
<Property Name=”PARA_CAL_DAY” Type=”Edm.DateTime” Nullable=”true” sap:label=”Période” sap:filterable=”true” sap:selection-type=”any” sap:selection=”true”/>

Do you have any idea on how to solve this problem ?

Best regards,

Etienne

knutheusermann · ‎03-21-2017

Hi Etienne,

thanks for your detailed comment. Indeed the 'and’ expression combining two filter with the same property name and operators 'ge' and 'le' should be converted to a between operation in the select option. This has been fixed and should work after deployment of the next hotfix collection.

Best regards,
Knut

Former Member · ‎04-27-2017

Hi Knut,

we have same problem in our HTA SAP BYD System environment.

If user date format setting is YYYY-MM-DD and we use ODATA Query e.g. PARA_IN%20eq%20datetime%272017-03-31T00:00:00%27% it works ok

But if user has date format DD.MM.YYYY setting as and we use ODATA Query

e.g. PARA_IN%20eq%20datetime%272017-03-31T00:00:00%27%

output is not inline and as expected .. ODATA return Initial Reponse.

HOW do we specify ODATA Query datetime for user with Date Format DD.MM.YYYY defined on SAP BYD My Settings.

Regards,

Rakesh Mourya.

knutheusermann · ‎05-04-2017

Hi Rakesh,

I tested the same in my system and I get the same OData results for both user date formats. Maybe there is some other root cause. Could you please create a ByD incident in your system with your example?

Thanks,
Knut

EelcoE · ‎10-29-2020

Hi Knut!

Thanks as always for a very detailed and helpful blog.

2 questions:

is there a way to specify the existing selections and views (that have been defined in ByD) when accessing a report through OData?

in the ByD UI, one of the "selection parameters" is the Display Currency - is there a way to pass that along when querying the report through OData?

Thanks!

Kind regards,
Eelco

knutheusermann · ‎10-29-2020

Hi Eelco,

you can't choose a selection variant of view variant - the system does not take into account user selection variants and view variants.

Most report selection parameters are available as OData properties. You can identify the properties referring to report selection parameters by the property attributes sap:selection and sap:selection-type.
For example:
<Property Name="BYD_P_COMPCURR" Type="Edm.String" Nullable="true" sap:label="Display Currency" sap:filterable="true" sap:selection-type="single-value" sap:selection="true"/>

Best regards,
Knut

EelcoE · ‎10-29-2020

Thanks Knut - I had somehow overlooked the COMPCURR property.

Kind regards,

Eelco

former_member645633 · ‎11-12-2020

Hi Knut,

I want to call up the last 6 months of my customer order volume via odata interface. In the filter options you can choose between two date ranges, but this is not dynamic. How can I add this filter to my query e.g. month(TimeByDay) eq month(now())-6. Then I want to insert this URL into my Power Bi.

my query:

https://myXXXXX.sapbydesign.com/sap/byd/odata/ana_businessanalytics_analytics.svc/RPZ412B4AAC8C9570F...)-6)

knutheusermann · ‎11-13-2020

Hi Azal,

unfortunately we don't support relative selects in OData filter parameters. However I see two alternative solution approaches:

Define suitable Initial Selection Default Values using relative selects like "last 6 months" in your report configuration (using the report wizard). Those Initial Selection Default Values apply in OData requests as well as long as you don't overwrite them by corresponding filter parameters.

Calculate the date (today - 6 months) on consumer side and filter for all records with a value greater than that date.

Best regards,
Knut

former_member645633 · ‎11-13-2020

Hi Knut,

thank you for your answer.

pascal_markwald · ‎12-01-2020

Hi Knut

First of all thank you very much for all your blog posts, they are a great help. At the moment I am struggeling with multiple selections on product categories. We have severall reports where we want to filter for certain product categories and this seems to be problematic. First of all I learned, that I need to copy the report and deactivate the hierarchy on product categories, because hierarchies are not supported at the momen. Then as far as I understood, I should be able to specifiy a multiple selection with an "or" filter as follows:

https://myXXXXXX.sapbydesign.com/sap/byd/odata/ana_businessanalytics_analytics.svc/RPZ37779C79A69A2C... eq '1010101') or (CIPR_REFO_CATCP eq '1010102') or (CIPR_REFO_CATCP eq '1010103'))

Can you help me on that? The same usecase would apply to the standard reports Sales Order Volume or Customer Invoice Volume, where you simply want to filter for some product categories.

Kind regards

Pascal

knutheusermann · ‎01-19-2021

Hi Pascal,

for the invoice volume you have to provide a key date always.

The following request does the job for me (using the standard report):

GET /sap/byd/odata/ana_businessanalytics_analytics.svc/RPCRMCIVIB_Q0001QueryResults?$format=json&$select=CDOC_INV_DATE,CDPY_BUYER_UUID,CIPR_REFO_CATCP,CIP_SALES_UNIT,TDPY_BUYER_UUID,TIPR_REFO_CATCP,KCINV_QTY,KCNT_REVENUE&$filter=(CIPR_REFO_CATCP eq '193' or CIPR_REFO_CATCP eq '130' or CIPR_REFO_CATCP eq '140') and PARA_CAL_DAY ge datetime'2019-01-01T00:00:00'&$inlinecount=allpages

If the list of categories becomes too long you may consider using an OData batch request.

Best regards,
Knut

former_member513552 · ‎05-27-2021

I'm having trouble with the most basic functions. As described in the documentation, you should be able to count using this simple syntax:

https://myxyz.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RPBUPCSD_Q0001QueryResults/$count

However this throws an error message:

00145E5B1CC71EE0ACBD33E944B1819DProgrammfehler in Klasse CL_RSBOLAP_QV_RESULT_SET Methode : TOO_MANY_DRILL_DOWN_OBJECTS

Any tips are appreciated!

knutheusermann · ‎05-27-2021

The OData API for reports supports up to 50 properties. The system raises the error message TOO_MANY_DRILL_DOWN_OBJECTS if the number of properties exceed the max number of 50 properties.
In general you can specify the number of properties either in the report itself, or by using the OData paramenter $select. Unfortunately the latter does not work for $count.

As an alternative approach to get the total number of records, you may use the Property "Counter":
https://myXXXXXX.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RPBUPCSD_Q0001QueryResults?$sel...

former_member764716 · ‎09-02-2021

Hello Knut,

first of all - all of your blog post has helped us very much to create some special Odata-Queries. But now we are getting in this problem. We fetched a text field which contains some notices. But after the first 60 Characters the textstring is cut.

We know that in the Excel-AddIn there is a special option where you can decide to get the whole text. Is there a similar way to get ist with Odata-API?

Thank you in advance
Manfred

knutheusermann · ‎09-02-2021

Hi Manfred,

unfortunately the OData API for reports does not support the "extended text"-option. Please create a ByD improvement request for this requirement (https://influence.sap.com/SAPByDesign).

As alternative approach you would have the following options:

You may use the ODP interface, which supports the "extended text"-option (for BW integrations).

You may use the OData for business objects (see https://blogs.sap.com/2019/09/26/sap-business-bydesign-an-api-overview/)

You may use corresponding SOAP web service APIs.

Best regards,
Knut

DanDeVil · ‎11-17-2021

Hi Knut,

You are doing a great job with your extremely helpful blogs. Thanks!

My question is: if I have a parameter (such as PARA_GLACCT against RPFINGLAU02_Q0001QueryResults), how do I retrieve the value list for this parameter via OData?

I can remove the "PARA_" from the parameter name, add a "C" to the front of it and then inject a Query into the ODATA Source (https://myXXXXXX.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RPFINGLAU02_Q0001QueryResults?$...), but that seems a bit random...

Any pointers?

Ta - D

knutheusermann · ‎11-17-2021

Hi Daneel,

yes, you are almost right, but there are no pointers or fixed rules unfortunately.

Basically the properties referring to a report selection parameter have the metadata property attributes sap:filterable="true" and sap:selection="true" along with some selection parameter type, e.g. sap:selection-type="multi-value".
In most cases these properties have a P as prefix (e.g. PARA_COMPANY).

To include the related data in your OData response, you would need to find and include the corresponding characteristic or text property. Typically these properties have a similar name and label with a C and T as prefix respectively. For example: CCOMPANY_UUID and TCOMPANY_UUID.

In rare cases there might be even no characteristic related to the report selection parameter, in particular if the selection parameter is not related to a report column, but rather a kind of "instruction to pre-process the data (e.g. a data to convert currencies).

Best regards,
Knut

Jacques-Antoine · ‎01-17-2023

Hello knut.heusermann and happy new year! 😉

Is there any way to use the batch method on reports or data sources ?
I tried the $batch on different analytics services but I always get the HTTP Method not allowed.
Is it normal or am I missing someting?

Thank you for your help!

Best regards,

Jacques-Antoine

knutheusermann · ‎01-18-2023

Hi Jacques-Antoine,

yes, we don't support batch-requests for the OData APIs for reports. For data sources batch-requests are supported. I just added a batch example to the Postman collection "Analytics - Data Sources" in Github repository https://github.com/SAP-samples/byd-api-samples.

Happy new year,
Knut