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.
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:
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:
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.
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.
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:
Edit the report with your web browser and edit the report selection parameter incl. overwriting the Initial Selection Default Values, for example:
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.
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:
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.
Assume you are working with 3 ByD users:
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 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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
13 | |
11 | |
10 | |
8 | |
7 | |
6 | |
4 | |
4 | |
4 | |
3 |