January 11, 2018 3 minute read

Extending CDS Queries with CO-PA (WW) Fields

Business Background: Extensibility for CO-PA (WW) Fields

The operating concern can be extended with custom characteristics created in KEA5. These fields are also generated in journal entries in the ACDOCA table.

However, for some analytical applications which are based on CDS queries, CO-PA (WW) fields can’t be added to the CDS-based cubes and queries via S/4HANA key user extensibility nor via classic coding block extensibility (see Note 2453614).

Therefore, you need to manually add CO-PA (WW) fields to such queries. This applies to the queries included in Note 2535903 and to other queries based on I_GLAccountLineItemCube, I_GLAccountLineItemSemTag, I_JournalEntryItemCube and I_ActualPlanJrnlEntryItemCube.

The query and the underlying CDS views need to be extended with the custom field so that the field is available in the respective Fiori analytical app.

How to Extend Underlying Views with CO-PA (WW) Fields

In order for the custom field to be displayed in the Fiori analytical app, the underlying views need to be extended. In the following example (extension for Journal Entry Analyzer) the following views need to be extended:

The underlying views can be identified by looking at the consumption view in ABAP Development Tools for Eclipse.

Create a package in transaction SE21.
Open ABAP Development Tools for Eclipse and add the package to your favorites. In the following, names that can be freely defined are marked in green and views/fields that need to be assigned are marked in red.
Choose File > Create > ABAP Repository Object > Core Data Services > Data Definition and create an extension for E_JournalEntryItem (the extension view of the virtual data model).
• Use the Extend View template.
• Add the name of the append view and the view to be extended.
• Insert the WW fields you want to add to the report (in this case the WW field WWCLR). Use the Persistence alias as in E_JournalEntryItem to address the field of the underlying table ACDOCA.
Create an extension for I_GLAccountLineItemCube (the CDS view for the analytics cube).
• Use the Extend View template.
• Add the name of the append view and the view to be extended.
• Insert the WW fields that you want to add to the cube. Use the _Extension alias that is used for E_JournalEntryItem in I_GLAccountLineItemCube to address these fields.
Create an extension for C_GLLineItemsQ0001 (the CDS view for the analytics query).
• Use the Extend View template.
• Add the name of the append view and the view to be extended.
• Insert the WW fields that you want to add to the query. Use the I_GLAccountLineItemCube alias that is used for the cube in C_GLLineItemsQ0001 to address these fields.

Note: Don’t create such extensions for “private” CDS views. Only E_JournalEntryItem, released public views that have an association to E_JournalEntryItem (such as I_GLAccountLineItemCube), and views on top of these views (such as C_GLLineItemsQ0001) may be extended. The view stack below I_GLAccountLineItemCube, for example, might be changed in an incompatible way by SAP in a future release, which could damage the extension and lead to activation errors in the upgrade.

Result: CO-PA field in Fiori analytical app

The CO-PA (WW) field WWCLR (Colors) is shown in the Fiori analytical app Journal Entry Analyzer as an available field:

The CO-PA (WW) field can be included in the report as an additional characteristic:

10 Comments

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

Loucas Stylianou

April 25, 2018 at 7:06 am

Thanks Stephen Offenbacker for this very nice blog!

I did the same while extending CDS Queries for our customer (product s4hana 1610) last year.

My question is how do we extend Actual vs Plan CDS consumption views (like since the union between ACDOCA and ACDOCP is done on a higher level between some private views?

I noticed that for s4hana 1709 the Actual Plan cube I_ActualPlanJrnlEntryItemCube has extensions for both ACDOCA and ACDOCP but in s4hana 1610 there is no such cds view (cube) . The only view that is available is the I_ActualPlanJournalEntryItem.

1709

1610

define view I_ActualPlanJournalEntryItem 
with parameters
  P_PlanningCategory: fcom_category

as select from P_ActualPlanJrnlEntryItm( P_PlanningCategory: $parameters.P_PlanningCategory ) 
 

association[1]     to I_CompanyCode            as _CompanyCode               on $projection.CompanyCode = _CompanyCode.CompanyCode
association [0..1] to I_FiscalYearForCompanyCode as _FiscalYear              on $projection.FiscalYear  = _FiscalYear.FiscalYear and
                                                                                $projection.CompanyCode = _FiscalYear.CompanyCode  
association[1]    to I_FiscalYearVariant      as _FiscalYearVariant          on $projection.FiscalYearVariant  = _FiscalYearVariant.FiscalYearVariant
association[0..1] to I_ControllingArea        as _ControllingArea            on $projection.ControllingArea    = _ControllingArea.ControllingArea

//association[0..1] to I_Currency               as _BalanceTransactionCurrency on $projection.BalanceTransactionCurrency  = _BalanceTransactionCurrency.Currency 
//association[0..1] to I_Currency               as _TransactionCurrency        on $projection.TransactionCurrency  = _TransactionCurrency.Currency                                                                                                              
association[1]    to I_Currency               as _CompanyCodeCurrency        on $projection.CompanyCodeCurrency  = _CompanyCodeCurrency.Currency 
association[0..1] to I_Currency               as _GlobalCurrency             on $projection.GlobalCurrency       = _GlobalCurrency.Currency 
//association[0..1] to I_Currency               as _FreeDefinedCurrency1       on $projection.FreeDefinedCurrency1  = _FreeDefinedCurrency1.Currency 
//association[0..1] to I_Currency               as _FreeDefinedCurrency2       on $projection.FreeDefinedCurrency2  = _FreeDefinedCurrency2.Currency
//association[0..1] to I_Currency               as _FreeDefinedCurrency3       on $projection.FreeDefinedCurrency3  = _FreeDefinedCurrency3.Currency

//association[0..1] to I_UnitOfMeasure          as _BaseUnit                   on $projection.BaseUnit  =  _BaseUnit.UnitOfMeasure
association[0..1] to I_UnitOfMeasure          as _CostSourceUnit             on $projection.CostSourceUnit        =  _CostSourceUnit.UnitOfMeasure
//association[0..1] to I_UnitOfMeasure          as _AdditionalQuantity1Unit    on $projection.AdditionalQuantity1Unit =  _AdditionalQuantity1Unit.UnitOfMeasure
//association[0..1] to I_UnitOfMeasure          as _AdditionalQuantity2Unit    on $projection.AdditionalQuantity2Unit =  _AdditionalQuantity2Unit.UnitOfMeasure
//association[0..1] to I_UnitOfMeasure          as _AdditionalQuantity3Unit    on $projection.AdditionalQuantity3Unit =  _AdditionalQuantity3Unit.UnitOfMeasure

association[0..1] to I_Segment                as _Segment                    on $projection.Segment = _Segment.Segment
association[0..*] to I_ProfitCenter           as _ProfitCenter               on $projection.ControllingArea   = _ProfitCenter.ControllingArea and  
                                                                                $projection.ProfitCenter      = _ProfitCenter.ProfitCenter
association[0..*] to I_CostCenter             as _CostCenter                 on $projection.ControllingArea   = _CostCenter.ControllingArea and  
                                                                                $projection.CostCenter        = _CostCenter.CostCenter 
association[0..1] to I_BusinessArea           as _BusinessArea               on $projection.BusinessArea      = _BusinessArea.BusinessArea
association[0..1] to I_FunctionalArea         as _FunctionalArea             on $projection.FunctionalArea    = _FunctionalArea.FunctionalArea
//association[0..1] to I_WBSElement             as _WBSElement                 on $projection.ps_posid   = _WBSElement.WBSElement
//association[0..1] to I_Project                as _Project                    on $projection.ps_pspid   = _Project.Project

//association[0..1] to I_BusinessArea           as _PartnerBusinessArea        on $projection.PartnerBusinessArea   = _PartnerBusinessArea.BusinessArea
//association[0..1] to I_Segment                as _PartnerSegment             on $projection.segment = _PartnerSegment.Segment
association[0..1] to I_FunctionalArea         as _PartnerFunctionalArea      on $projection.PartnerFunctionalArea  = _PartnerFunctionalArea.FunctionalArea
association[0..*] to I_ProfitCenter           as _PartnerProfitCenter        on $projection.ControllingArea        = _PartnerProfitCenter.ControllingArea and  
                                                                                $projection.PartnerProfitCenter    = _PartnerProfitCenter.ProfitCenter
association[0..*] to I_CostCenter             as _PartnerCostCenter          on $projection.ControllingArea        = _PartnerCostCenter.ControllingArea and  
                                                                                $projection.PartnerCostCenter      = _PartnerCostCenter.CostCenter 
//association[0..1] to I_WBSElement             as _PartnerWBSElement          on $projection.pps_posid   = _PartnerWBSElement.WBSElement
//association[0..1] to I_Project                as _PartnerProject             on $projection.pps_pspid   = _PartnerProject.Project

association[0..1] to I_GLAccountInChartOfAccounts as _GLAccountInChartOfAccounts on $projection.ChartOfAccounts  = _GLAccountInChartOfAccounts.ChartOfAccounts and 
                                                                                    $projection.GLAccount        = _GLAccountInChartOfAccounts.GLAccount 
association[1]    to I_ChartOfAccounts            as _ChartOfAccounts            on $projection.ChartOfAccounts  = _ChartOfAccounts.ChartOfAccounts

association[1]    to I_ControllingDebitCreditCode as _ControllingDebitCreditCode on $projection.ControllingDebitCreditCode = _ControllingDebitCreditCode.ControllingDebitCreditCode

association[0..1] to I_Material               as _Material                   on $projection.Material      = _Material.Material
association[0..1] to I_Plant                  as _Plant                      on $projection.Plant         = _Plant.Plant
association[0..1] to I_Ledger                 as _Ledger                     on $projection.Ledger        = _Ledger.Ledger
association[0..1] to I_Customer               as _Customer                   on $projection.Customer      = _Customer.Customer
//association[0..1] to I_CostCenterActivityType as _CostCenterActivityType     on $projection.lstar      = _CostCenterActivityType.CostCtrActivityType
//association[0..1] to I_CostCenterActivityType as _PartnerCostCtrActivityType on $projection.lstar      = _PartnerCostCtrActivityType.CostCtrActivityType
association[0..1] to I_SalesOrganization       as _SalesOrganization          on $projection.SalesOrganization        = _SalesOrganization.SalesOrganization
association[0..1] to I_DistributionChannel     as _DistributionChannel        on $projection.DistributionChannel      = _DistributionChannel.DistributionChannel
association[0..1] to I_MaterialGroup           as _MaterialGroup              on $projection.MaterialGroup      = _MaterialGroup.MaterialGroup
association[0..1] to I_CustomerGroup           as _CustomerGroup              on $projection.CustomerGroup      = _CustomerGroup.CustomerGroup

{

I understand that is not recommended to extend private views but unfortunately for Actual VS Plan query ( C_ProfitAndLossPlanActQ2903 ) in s4hana 1610 I couldn't find an alternative way.

Kind regards,

Loucas Stylianou

Sana BOUHACHEM

November 5, 2021 at 11:07 am

Hello,

Thank you for the response and the blog, but i have a question if you can help me,

I want to add atributes to the dimensions added, do you have an idea about that?

Thank you and best regards,

Sana.

Mayumi Lacerda Blak

May 12, 2020 at 3:10 pm

Hello

Loucas Stylianou and Stephen!

Did you find the answer for extending P&L Actual/Plan?

If I extend both E_FinancialPlanningEntryItem and E_JournalEntryItem, fields are shown duplicated in the CDS.

When I extend only E_JournalEntryItem, the field brings the Plan amount but it doesn't display the characteristic value.

kind regards

Mayumi

Loucas Stylianou

May 20, 2020 at 8:01 am

Hi Mayumi,

You need to extend also the cube and the query.

In the cube since you have characteristics from both Plan and Actual you need to combine this info that you have already added to E_FinancialPlanningEntryItem and E_JournalEntryItem. There is a field which marks each line (record of the view) as plan or actual. I recommend to use the CASE WHEN and the field which recognizes the line type (Actual/ Plan ) and then add the correct field either from E_FinancialPlanningEntryItem or E_JournalEntryItem.

So in simple words lets say you added a custom field on coding block which should appear on ACDOCA and ACDOCP and this field is lets say ZCUSTOM_FIELD1.

You extend both E_FinancialPlanningEntryItem and E_JournalEntryItem to include ZCUSTOM_FIELD1 and then you extend the cube whereas the two above extensions are used.

You add a CASE statement similar to the one below:

case ActualPlanIndicator

when 'A' then E_JournalEntryItem.ZCUSTOM_FIELD1

when 'P' then E_FinancialPlanningEntryItem.ZCUSTOM_FIELD1

end case

Then you need to extend the query to add the new field ZCUSTOM_FIELD1.

Kind regards,
Loucas Stylianou

Kind regards,

Loucas Stylianou

Mayumi Lacerda Blak

May 20, 2020 at 1:10 pm

Loucas,

Thanks a LOT for your help!

Kind regards

Mayumi Blak

Mayumi Lacerda Blak

May 29, 2020 at 9:23 pm

Hi Loucas!

I tried to write the code like this for the Cube, but I'm getting an error on Eclipse:

@AbapCatalog.sqlViewAppendName: 'ZIFIACTPLNJEIC'

@EndUserText.label: 'ZI_ActualPlanJrnlEntryItemCube'

extend view I_ActualPlanJrnlEntryItemCube with ZI_ActualPlanJrnlEntryItemCube

{case ActualPlanCode ==> error: "An alias name must be assigned for the expression (CASE ...)"

when 'A' then _Extension_acdoca.Ze_ww001_pa

when 'P' then _Extension_acdocp.Ze_ww001_pa

end

}

I don't know what I'm doing wrong, if you could help...

kind regards

Mayumi

Mayumi Lacerda Blak

June 1, 2020 at 3:22 am

I found the answer,

the "end as" was missing. Now I can see values on both plan and Actual for this COPA field.

{ case ActualPlanCode
when 'A' then _Extension_acdoca.ze_ww001_pa

when 'P' then _Extension_acdocp.ze_ww001_pa
else 'unspecified'
end as ze_ww001_pa

kind regards

Mayumi

}

Anuradha Sharma

March 8, 2021 at 12:28 pm

Hi,

I performed the same steps as mentioned above for Market segment - Actual (C_MarketSegmentQ2501). i'm able to see customized fields in query browser but in App report, fields are not visible. looking for guidance here.

Fields%20are%20visible%20in%20Query%20browser

Fields are visible in Query browser

This%20is%20screen%20shot%20id%20from%20Merket%20segment%20-%20actual%2C

This is screen shot id from Merket segment - actual, Here customizing fields are not visible.

Nurshid K

December 28, 2021 at 8:06 am

Hi Anuradha,

Were you able to get a solution for the above issue? I am facing the same issue, could you please guide me here?

Regards,

Nurshid

Sana BOUHACHEM

November 5, 2021 at 11:11 am

Hello Stephan,

Thank you for the blog, it was very helpful, i have a question if you can help me.

I have applied the recommended instruction to add new dimension to the standard CDS C_ProfitCenterQ2701 and it works perfectly. Now i want to add attributes under a dimension but it's not visible when i click on teh dimension i want to get a list of attributes like hierarchy.

Can you please help me if you have an idea?

Thanks and best regards,

Sana.

Extending CDS Queries with CO-PA (WW) Fields

Business Background: Extensibility for CO-PA (WW) Fields

How to Extend Underlying Views with CO-PA (WW) Fields

Result: CO-PA field in Fiori analytical app

Assigned Tags