Skip to Content

Do you need to run queries in order to get data on your SAP Business One company database other (or in other format) than the predefied Service Layer entities and services?

As of SAP Business One 9.3 PL02, version for SAP HANA, Service Layer supports automatic discovery and execution of the SAP Business One Semantic Layer views both system built-in
views and also custom views.

Semantic Layer works as an OData web service and exposes the views to clients via the OData protocol version 4.

If you want to execute a specific view like for example “AveragePurchasingPriceQuery” you simply send the following request to Service Layer:

GET  https://hanaserver:50000/b1s/v1/sml.svc/AveragePurchasingPriceQuery  

and you will get the following results:

{
 "@odata.context": "https://hanaserver:50000/b1s/v1/sml.svc/$metadata#AveragePurchasingPriceQuery",
 "value": [
  {
    ...
    "PurchaseAmountLC": 5000,
    "PurchaseQuantityInInventoryUoM": 100,
    "AverageUnitPriceLC": 50,
    "id__": 1
  },
  {
    ...
    "PurchaseAmountLC": 2000,
    "PurchaseQuantityInInventoryUoM": 10,
    "AverageUnitPriceLC": 200,
    "id__": 2
  },
  ...
 ]
}

 

Semantic Layer service allows you to retrieve data with query option combinations:

GET
https://hanaserver:50000/b1s/v1/sml.svc/AveragePurchasingPriceQuery?$select=PostingYear,BusinessPartnerCode&$s
kip=1&$filter=PostingYear eq '2017' and startswith(BusinessPartnerCode,
'1')&$orderby=PostingYear

to get a reduced number of records and properties per record:

{
 "@odata.context": "https://hanaserver:50000/b1s/v1/sml.svc/$metadata#AveragePurchasingPriceQuery",
 "value": [
   {
    "PostingYear": "2017",
    "BusinessPartnerCode": "1071287676" 
   },
   {
    "PostingYear": "2017",
    "BusinessPartnerCode": "1100270398"
   },
   {
    "PostingYear": "2017",
    "BusinessPartnerCode": "124052273"
   },
   {
    "PostingYear": "2017",
    "BusinessPartnerCode": "1785286082"
   }
  ]
}

 

You can also fill your view input parameters in your request to Service Layer.

For example to run the view “BalanceSheetQuery” equivalent to the following query:

SELECT * FROM
"_SYS_BIC"."sap.sbodemous.fin.fi/BalanceSheetQuery"('PLACEHOLDER'=('$$P_AddVoucher$$',
'N'),'PLACEHOLDER'=('$$P_FinancialPeriod$$','2017'))"

you send the following request:

GET
https://hanaserver:50000/b1s/v1/sml.svc/BalanceSheetQueryParameters(P_FinancialPeriod='2017',P_AddVoucher='N')
/BalanceSheetQuery?$select=FiscalYear, AccountCode

and get the following result

{
 "@odata.context": "https://hanaserver:50000/b1s/v1/sml.svc/$metadata#BalanceSheetQuery",
 "value": [
   {
    "FiscalYear": 2017,
    "AccountCode": "_SYS00000000049"
   },
   {
    "FiscalYear": 2017,
    "AccountCode": "_SYS00000000009"
   },
    ...
   {
    "FiscalYear": 2017,
    "AccountCode": "_SYS00000000029"
   }
  ],
 "@odata.nextLink": "BalanceSheetQueryParameters(P_FinancialPeriod='2017',P_AddVoucher='N')/BalanceSheetQuery?$select=FiscalYear,%20AccountCode&$skip=20"
}

The root URL for this service is /b1s/v1/sml.svc, to distinguish Semantic Layer service from Service Layer business entities and services.

You can get the Semantic Layer service metadata with the following query:

GET https://hanaserver:50000/b1s/v1/sml.svc/$metadata

that will return the list of views available to be executed from Service Layer:

<?xml version="1.0" encoding="UTF-8"?>
<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
 <edmx:DataServices>
  <Schema Namespace="SAPB1" xmlns="http://docs.oasis-open.org/odata/ns/edm">
   <EntityType Name="AveragePurchasingPriceQuery">
    <Key> <PropertyRef Name="id__"/> </Key>
    <Property MaxLength="160" Name="LineDocumentOwner" Nullable="true" Type="Edm.String"/>
    <Property MaxLength="15" Name="PaymentMethodCode" Nullable="true" Type="Edm.String"/>
    <Property Name="PostingDateSQL" Nullable="true" Type="Edm.DateTime"/>
     ...
    <Property Name="id__" Nullable="false" Type="Edm.Int32"/>
   </EntityType>
   <EntityType Name="OnTimeReceiptStatisticsQuery">
    <Key> <PropertyRef Name="id__"/> </Key>
    <Property MaxLength="160" Name="DocumentOwner" Nullable="true" Type="Edm.String"/>
    <Property Name="NumberOfPurchaseOrder" Nullable="true" Type="Edm.Int32"/>
    <Property Name="id__" Nullable="false" Type="Edm.Int32"/>
   </EntityType>
   <EntityContainer Name="SemanticLayer">
    <EntitySet EntityType="SAPB1.PurchaseOrderFulfillmentCycleTimeQuery" Name="PurchaseOrderFulfillmentCycleTimeQuery"/>
     ...
    <EntitySet EntityType="SAPB1.BalanceSheetComparisonQueryParameter" Name="BalanceSheetComparisonQueryParameters">
     <NavigationPropertyBinding Path="BalanceSheetComparisonQuery" Target="BalanceSheetComparisonQuery"/>
    </EntitySet>
     ...
    <EntitySet EntityType="SAPB1.KPICashFlowStatementQueryParameter" Name="KPICashFlowStatementQueryParameters">
     <NavigationPropertyBinding Path="KPICashFlowStatementQuery" Target="KPICashFlowStatementQuery"/>
    </EntitySet>
   </EntityContainer>
  </Schema>
 </edmx:DataServices>
</edmx:Edmx>

 

If you want to learn more details about this feature: how to make available your custom views for Semantic Layer, how to authorize B1 users to run Semantic Layer views,…) please check the document “Working with SAP Business One Service Layer” available in your SAP Business One installation at the link https://hana-server:50000/. You can also download this document from the SAP Note 2538519 – Service Layer Exposes Semantic Layer as an OData Service (S-user required).

Don’t hesitate to share your feedback with us in this blog!

If you have questions/problems while using this feature please don’t post them here but in the SAP Community forums to allow an easier follow up.

Hope you enjoy this feature!

To report this post you need to login first.

7 Comments

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

  1. Mel Dodd

    Hi Trinidad,

    Is the SAP Web IDE for SAP HANA and the the SAP HANA XS Advanced Model supported for Business One?

    Thanks,

    Mel Dodd

    (0) 
    1. Maria Trinidad MARTINEZ GEA
      Post author

      Hi Mel,

      You can develop your own XS Advanced apps with WebIDE and connect to B1 with Service Layer.

      In B1 we don’t have any extra APIs for XS Advanced but this shouldn’t be a limitation for you to use XS Advanced.

      We are waiting for B1 to adopt HANA 2.0 to start having some training materials around XSA.

      PS: For questions not releated to this blog please create separated threads in the SAP Community, we follow this community quite closely in our team.

      Regards,
      Trinidad.

      (0) 
  2. Former Member

    Hello Maria,

    Stuff like this is great.   We implemented something similar that provides easy access to views, functions (using Sap.Data.Hana), and b1 user queries (using the query service at /sap/sbo/platform/query).   With arrangements like this, the case for “pushing down” the reports into hana becomes very easy.   Performance is good, and you get the report as json for free, which is easily adapted to any web-capable client.  Even Excel!

    Daniel

    (0) 
  3. Bilal Sultan

    Hello Maria,

    i have a calculation view called “ABS_GetBPProperty”,
    when i perform

    https://192.168.12.9:50000/b1s/v1/sml.svc/ABS_GetBPProperty

    or

    https://192.168.12.9:50000/b1s/v1/ABS_GetBPProperty

    i get the following error

    {
       "error" : {
          "code" : -1000,
          "message" : {
             "lang" : "en-us",
             "value" : "Unknown entity."
          }
       }
    }

    Thanks in Advance,

    (0) 
    1. Maria Trinidad MARTINEZ GEA
      Post author

      Sorry Bilal, didn’t receive a notification for your request.

      Just to share in case someone else has the same question.

      Please check the Service Layer User Manual (https://your_sl_server:50000) and check the section “3.8.8Customized Views Exposure”. You will find all details how to have access to your custom views:

      Besides the system built-in views, the customer designed views are also able to be exposed as OData service.
      To achieve this, first use the latest SAP HANA Model Package Tool to generate a compressed model package after exporting the designed SAP HANA models from SAP HANA Studio. As for how to download and use this tool, see SAP Note 2008991 or this blog.
      Compared to the old versions, in the SAP HANA Model Packaging Wizard for SAP Business One, a column named as Enable for Service Layer is added with checkbox type as below

      Hope it helps,
      Trinidad.

       

       

      (0) 

Leave a Reply