Group Aggregate Data with BRF+

[former_member348835]

E.g. I may want a part of a BRF+ function to count the occurrences of every material in table MVKE (select count ( * ) from MVKE group by MATNR), like I do it in transaction TAANA.

However, neither "group by" nor table result objects are supported by BRF+ > Expression > DB Lookup > Aggregate > Count.

Do you have any suggestions for highly performing alternatives?

[christianlechne · Accepted solution]

Hi,

for such scenarios I would prefer to use openSQL for the Select and encapsulate the statement in a method of a class. This method can then be called via a procedure call expression.

If you are on NW 740 >SP05 you can also model your statement as CDS view and call that one   via openSQL.

Hope that helps.

BR

Christian

[former_member348835 · Accepted solution]

Thank you for the answer. My conclusion is that very basic "group by" SQL statements cannot be processed efficiently inside BRF+ DB lookups yet.

[christianlechne · Accepted solution]

You are right. If it and/or when there will be support for that I cannot say.

Nevertheless I am somwewhat in doubt that complex SQL statements should really be modelled in BRFplus as they are for sure necessary for the decision making but I doubt that they are maintained by the business experts and have to be that agile as rules. So I would anyway go for the ABAP approach when it comes to SQL

BR

Christian

[carsten_ziegler · Accepted solution]

Pls see the documentation here:

DB Lookup Expression - Business Rule Framework plus (BRFplus) - SAP Library

It is possible but only with NetWeaver 7.4.

What release are you running?

[christianlechne · Accepted solution]

Perhaps we should sharpen the question a bit (perhaps it was a misunderstanding from my side):

Do you want to model a SQL statement in BRFplus like shown in the screenshot below :

This is to the best of my knowledge not possible in BRFplus ( may correct me if am wrong) and this was what thought was your goal,

In general BRFplus DB lookup expression supports several modes like data retrival and aggregation. The data retrival mode allows the mapping of the result to a data object of type table (as stated in the link of Carsten), The aggregation mode does not which makes sense if it is a pure aggregation as the result will be an element. So if your SQL can be mapped to one of these modes Carsten is right (for NW 740) and you can model your SQL in BRFplus.

Cheers

Christian

[former_member348835 · Accepted solution]

I confirm the explanation and example by Christian Lechner.

My business case is that I advocate for BRF+ to non-ABAP developers with a high level of SQL understanding. Opponent consultants advocate for Quick Viewer Infosets, if we need to make the SQL statement in ABAP at all. I disagree and attempt to find a generic BRF+ solution with this request.

I work with NW 7.40.

[former_member232496 · Accepted solution]

Hi there,

I created a simple example that models the sql statement that posted. It was done via Data Retrieval rather than Aggregation mode. The generated SQL is below:

I found this article quite helpful: DB Lookup Expression - Business Rule Framework plus (BRFplus) - SAP Library under the heading - Field mapping - Field aggregation and grouping.

In order to only group by one field all other fields have to be removed from the select.

So I think with BRFplus you can utilize the group by sql statements in a db lookup. The exported xml of the expression is also attached.

I hope this helps

[christianlechne · Accepted solution]

Hi Martina,

that was it! Reproduced it and it works (verified on NW 740 SP10). So I correct my last statement and say that the modeling the SQL statement is indeed possible with the DB lookup expression.

Cheers

Christian

P.S. Interesting to see that you are already working on a NW 7.51 

[former_member348835 · Accepted solution]

Thank you for a beautiful answer. If fulfills the very most of my request, except that I cannot count without "distinct" as in transaction TAANA.

Example

I want to count the records in table MBEW per material and price control. I set up this DB Lookup:

This only counts one occurrence per record. The reason can be found by clicking on the "View SQL" button:

Instead I want the highlighed SQL line to say

"COUNT( * ) AS FREQUENCY" or

"COUNT( MATNR ) AS FREQUENCY"

without DISTINCT.

If I attempt to remove MATNR as a source field, then I lose the opportunity to make aggregations. How do I solve my initial question?

[former_member232496 · Accepted solution]

Hi again,

you are right that in the 'Data Retrieval' mode only something like:

"COUNT( DISTINCT MATNR ) AS FREQUENCY"

is currently  possible and not the (also valid) sql below:

"COUNT( MATNR ) AS FREQUENCY"

Apparently with the mode 'aggregation' you can choose whether or not to count 'distinct'. However in that selection mode only one column can be selected... Have you tried this?

I think that your use case is valid, and I think that we should be able to use count without distinct.

If I find out anything else I'll get back to you on here.

BR,

Martina

[former_member348835 · Accepted solution]

Yes, I've tried the aggregation. Here it is possible to make COUNT without DISTINCT. But it is not possible to GROUP data. You can simply simulate that by trying to assign a table object to the result.

[former_member232496 · Accepted solution]

Yes you are right, the grouping functionality does not work on this. So no, right now there is no option to do the above.