cancel
Showing results for 
Search instead for 
Did you mean: 

How to Join Composition Data in a CDS View for Reporting in SAP CAP

jgonzal
Explorer
0 Kudos

Hello SAP Community,

I'm working on an SAP CAP project and need some assistance with a specific reporting requirement. I have an entity called Invoice that has a composition to another entity InvoiceLines. I'm trying to create a reporting view that includes data from both Invoice and its composition InvoiceLines, but I'm facing issues with the nodeInvoiceLines data not appearing in my reporting view.

Here is a simplified version of my current CDS model:

 

context InvoiceObject {

    entity Invoice : managed, cuid {
        key invoiceCode       : String(20) @(Core.Computed: true) @readonly;
        status                : Association to one InvoiceStatus @readonly;
        type                  : Association to one InvoiceType @mandatory;
        company               : Association to one RECompanies @mandatory;
        date                  : Date @mandatory;
        customer              : Association to one BusinessPartner @mandatory;
        billCustomer          : Association to one BusinessPartner;
        billAddress           : Association to one BillingAddressNode;
        externalReference     : String(50);
        currency              : Currency @mandatory;
        netAmount             : Amount @readonly;
        grossAmount           : Amount @readonly;
        taxAmount             : Amount @readonly;
        retentionAmount       : Amount @readonly;
        salesUnit             : Association to one SalesUnit @mandatory;
        invoiceUnit           : Association to one InvoiceUnit @mandatory;
        externalSystem        : String(40);
        nodeInvoiceLines      : Composition of many InvoiceLines on nodeInvoiceLines.invoice = $self;
        month                 : String;
    }

    entity InvoiceStatus : CodeList {
        key code : String(1);
    }

    entity InvoiceType : CodeList {
        key code : Integer;
    }

    entity InvoiceLines : managed, cuid {
        line            : Integer @readonly;
        service         : Association to one Services @mandatory;
        description     : String(100);
        netAmount       : Amount;
        grossAmount     : Amount @readonly;
        taxAmount       : Amount @readonly;
        tax             : Association to one cfg.TaxCodes @mandatory;
        retention       : Decimal(5, 2) @assert.range : [0, 100] @Measures.Unit: '%';
        retentionAmount : Amount @readonly;
        invoice         : Association to one Invoice;
        site            : Association to one obj.SitesObject.Sites;
        building        : Association to one obj.BuildingObjects.Buildings;
        unit            : Association to one obj.UnitObjects.Units;
        contract        : Association to one obj.ContractObject.Contract;
    }

    @readonly
    entity Reporting as select from Invoice {
        invoiceCode,
        status,
        type,
        company,
        date,
        customer,
        billCustomer,
        billAddress,
        externalReference,
        currency,
        netAmount,
        grossAmount,
        taxAmount,
        retentionAmount,
        salesUnit,
        invoiceUnit,
        externalSystem,
        month // Include the computed month
    };
}

 

However, when I query the Reporting entity, the nodeInvoiceLines data does not appear in the results.

What I've Tried:

  • Ensuring that nodeInvoiceLines is properly defined as a composition in the Invoice entity.
  • Using the expand keyword to explicitly include the nodeInvoiceLines data in the Reporting view.

Questions:

  1. How can I join the Invoice and InvoiceLines entities in my CDS view to ensure that nodeInvoiceLines data is included in the Reporting view?
  2. Are there best practices for joining data from an entity and its composition in a CDS view?

Any advice or examples would be greatly appreciated!

Thank you!

Accepted Solutions (0)

Answers (0)