Hello SCN-community,

I tried my first steps in the world of CDS. As you may recognized in my last blog, I have to provide a kind of HANA-readiness. The Core Data Services (CDS) are available on both platforms – HANA and non-HANA.

BRFplus – DB Lookup expression and currencies

In order to solve a problem in BRFplus, Carsten Ziegler gave me the advice to take a look at CDS. I have to build a few aggregates upon the new General Ledger for some kind of statistics. The problem addresses to sum a few amount columns whose currency information points to another table. This is currently
not supported by the BRFplus database lookup expression. Technically in the case of the ledger it’s getting more complicated due to its table design.

But what if you only operate with one single currency? In fact if you have the same problem with another table, CDS is definitely something you may look
at.
To give you an overview I built a small example table: 

Table.png

All amount fields refer to T000-MWAER (the clients default currency). For now, we want to sum each amount column of the table in BRFplus. This gives us the following errors:

BRFplusError.png

In order to solve it we think about a view in which we explicit forget the currency details. This is done by creating a CDS view. But in CDS we are able to do more. We can create a view hiding the currency information or we can map all amount columns to a constant defined currency. I prefer the last variant because we take the currency information into BRFplus and do not need to append it later to the data. But sometimes you may not need the currency so I provide examples for both implementations.

CDS-View 1: Removing currency Information

CDS_VIEW.png

By using the CAST function we transform the values of type amount to simple decimal numbers.

CDS-View 2: Mapping all amount columns to a static defined currency

CDS_VIEW_WITH_CURR.png

In order to give the currency information back to BRFplus we have to normalize all amount columns. This is done by defining a new currency key column. This column is annotated with @Semantics.currencyCode. To provide a constant value as currency information, I use the scalar value ‘EUR‘ and cast it to the appropriate type. Then we have to link all currency fields of the underlying table to the new currency key column. To do this we annotate each amount column with @Semantics.amount.currencyCode and provide the currency key column as the property.

Putting it all together

With one of those CDS views we can finalize the example in BRFplus.

BRFplusOK.png

If we try to simulate the database lookup expression we get something like this:

RESULT.png

CDS – Built In SQL-Functions

The example was written on a SAP NW7.40SP6 on HANA where I did not find many built in SQL-Functions.

Aggregations:

  • MIN
  • MAX
  • AVG
  • SUM

Control and/or programming:

  • CAST
  • CASE
  • LPAD
  • SUBSTRING
  • MOD
  • CEIL

 

On SCN I noticed a thread where the following functions are announced for SP8:

  • ABS
  • DIV
  • DIVISION
  • FLOOR
  • ROUND
  • CONCAT
  • REPLACE

That’s something I will check when SP8 is officially out.

Summary

CDS with it’s Advanced SQL will be one important functionality in todays application development. It even solves problems of already released projects like BRFplus. Due to the fact CDS is available for non-HANA and HANA (here with a wider feature set)  it becomes the backbone to build HANA-ready solutions I think.

To report this post you need to login first.

3 Comments

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

  1. Tobias Trapp

    Hi Daniel,

    thank you for sharing this blog entry.

    I completely agree with you that CDS are a cornerstone of new ABAP applications and I see them more and more:

    It is great that you showed a use case in BRFplus to overcome limitations of the DB Lookup expression. This shows the potential of the technology.

    Please be aware that not many ABAP developers will know CDS since many of them will think that they have good old DDIC so CDSis not that important. For CDS newbies I recommend to read the blog by Christiaan Edward Swanepoel about CDS: http://scn.sap.com/community/abap/eclipse/blog/2014/02/04/new-data-modeling-features-in-abap-for-hana

    I like your blog entry you proved that CDS is not only important for creation of high quality semantic data models inside HANA – it has use cases in plain ABAP scenarios as well.

    In fact I don’t know whether the ABAP in Eclipse space is optimal in your blog since in this place I would expect more tutorials like the above mentioned one by Chris. But we should Point Carsten Ziegler to this blog entry so that he can link from his BRFplus/DSM collection to this blog entry.

    The reason is that knowing the limitations of the database lookup expression of BRFplus  will be worth another blog entry. I like that you pasted many screenshots to your blog but unfortunately the error messages are hard to read. So could you explain the problems of DB Lookup for the ones who have not that much experience with this expression type like you?

    Cheers and keep on blogging,

    Tobias

    (0) 
  2. Carsten Ziegler

    Hi Daniel,

    Thanks a lot for verification of the idea with a CDS view. Thanks to your blog we now also have some kind of reference for future projects. We will definitely add it to our list of resources.

    I have to agree with Tobias: Keep on blogging.

    Carsten

    (0) 
  3. Attila Laczko

    Hi Daniel,

    Thanks a lot for this.

    I will use one of the two Solutions in our BRFplus Projekt – at least on the development enviroment.

    Cheers,

    Attila

    (0) 

Leave a Reply