Virtual Fields in CRM Reporting Framework

Use Case

In one of our applications, we have a search over the 1Order documents.

It uses the tables:

• CRMD_SRV_SUBJECT (Service Subject) and
• CRMD_SRV_OSSET (Service Subject Reference Object).

However, we need to pass the following “Reasons” as search criteria.

Technical Key	Meaning
Z2ZS460001	Description 1
Z2ZS460002	Description 2
Z2ZS460003	Description 3
Z2ZS460004	Description 4
Z2ZS460005	blablabla
etc…	etc…

The problem is that those reasons (in other meaning “Results”) are for the combination of the fields KATALOGART (Catalog), CODEGRUPPE (Code Group) and CODE (Code) in the table CRMD_SRV_SUBJECT.

I.e. Z2ZS460001 is translated into KATALOGART = “Z2”, CODEGRUPPE = “ZS46”, CODE = “0001”.

If you are using just one technical key with search option EQ (equal), it’s not a problem.

But when you are using multiple technical keys referring to different catalogs or code groups, when you are not using just EQ, but NE (not equal), or Exclusions or even GE (Greater or Equal), then the processing of the search parameters becomes a real nightmare!

The root cause of the problem is that we are using search criteria, which are split among three different database fields.  If this would be just one field, no issue then.

Why not to do so?

With ABAP CDS we can easily add a virtual column to the table CRMD_SRV_SUBJECT, which would then concatenate those three fields: concat( concat(katalogart,codegruppe ),code ) as zs_full_code.

@AbapCatalog.sqlViewName: 'ZDMSHVCOLV'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'Dima''s Virtual Column'

define view ZDMSH_VIRT_COL as 

select from crmd_srv_subject as sbj

{

sbj.guid,

sbj.guid_ref,

sbj.username,

sbj.timestamp,

sbj.katalogart,

sbj.codegruppe,

sbj.code,

sbj.defectclass,

sbj.quality,

sbj.defquantity,

sbj.guid_hierarchy,

sbj.sort,

sbj.ext_code,

sbj.asp_id,

sbj.cat_id,

sbj.katalog_type,

concat( concat(katalogart,codegruppe ),code ) as zs_full_code        

}

Functionally this solution works perfectly fine, however there are some performance drawbacks. The thing is that, if you are using the new virtual column as a search criteria (as in our case), the database should first “materialize” a complete table (if no other criteria are applied) and only then do further steps. In our system, the table CRMD_SRV_SUBJECT contains more than 24 million entries, so the above solution does not seem to be an option for us…

Can we do something else? Yes…

After checking out the value help tables, we discovered that all possible combination for the fields KATALOGART, CODEGRUPPE and CODE are kept in the customizing table QPCD (Inspection Catalog Codes). Therefore, we can first apply all our filters to this little table (in our case it contains 6.5 thousand records) and then we can continue to process the bigger tables.  And again, we need a virtual column, so we create an ABAP CDS view as below.

@AbapCatalog.sqlViewName: 'ZS_QPCD_V'

@AbapCatalog.compiler.CompareFilter: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'Inspection Catalog Codes (Advanced)'

define view ZS_QPCD as 

select from qpcd 

{

mandt,

katalogart,

codegruppe,

code,

defectclass,

ersteller,

e_datum,

aenderer,

a_datum,

inaktiv,

verwendung,

concat( concat(katalogart,codegruppe ),code ) as zs_full_code

}

In your implementation, you can use ABAP FOR ALL ENTRIES option, combining those three fields. But we created another ABAP CDS, joining application tables and previously created ABAP CDS.

@AbapCatalog.sqlViewName: 'ZS_ACT_REASONV'

@AbapCatalog.compiler.CompareFilter: true

@AccessControl.authorizationCheck: #NOT_RELEVANT

@EndUserText.label: 'Activity Reason Code (RFW)'

define view ZS_ACTIVITY_REASON as 

select from crmd_srv_osset   as sos 

inner join  crmd_srv_subject as sbj on sbj.client       = sos.client

                                   and sbj.guid_ref     = sos.guid

                                   and sbj.client       = sos.client

/* Join Little Customizing Table For Performance Reasons */

inner join  ZS_QPCD          as cod on cod.mandt        = sbj.client

                                   and cod.katalogart   = sbj.katalogart

                                   and cod.codegruppe   = sbj.codegruppe

                                   and cod.code         = sbj.code                                  

{

sos.client,

sos.guid_set,

sos.profile_type,

sbj.guid,

sbj.guid_ref,

sbj.username,

sbj.timestamp,

sbj.katalogart,

sbj.codegruppe,

sbj.code,

sbj.defectclass,

sbj.quality,

sbj.defquantity,

sbj.guid_hierarchy,

sbj.sort,

sbj.ext_code,

sbj.asp_id,

sbj.cat_id,

sbj.katalog_type,

cod.zs_full_code as zs_reason_code     

}

Finally, CRM Reporting Framework (RFW) has been updated (tables CRMC_REPDY and CRMC_REPDY_DB) to be able to understand a new field.