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

[jgonzal]

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!

[vinay1210]

Hi,

To include the nodeInvoiceLines data in your Reporting view and ensure it appears in the results, follow these steps:

1. Update the Reporting View:
   Modify the Reporting view to include the nodeInvoiceLines composition explicitly. For example:

   javascript

   @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
   nodeInvoiceLines // Add the composition here
   };

2. Expand the Composition in the Query:
   When querying the Reporting entity, use the $expand parameter to explicitly include the nodeInvoiceLines data. For example:

   text

   /Reporting?$expand=nodeInvoiceLines

   This ensures that data from nodeInvoiceLines is retrieved along with the Invoice data.

Best Practices:

• Expose Compositions Explicitly: Always include compositions in the CDS view definition if you want them to be accessible in the reporting views.
• Use Expands for Nested Data: Use $expand for retrieving nested or related data, ensuring efficient querying and avoiding unnecessary joins at the database level.

By including nodeInvoiceLines in your view and using the $expand parameter, you should be able to retrieve the desired data in your reporting view. Let us know if you face any further issues!