CDS view which has a period, how to create Quarter field to update - detailed steps!

1. Analyze Your Existing CDS View

• Identify the Period Field: Determine the field in your CDS view that represents the period. This is typically a date field or a field with a date-related data type.
• Understand the Period Format: Is it stored as YYYYMMDD, MM.YYYY, or another format? This will influence how you extract the quarter.

2. Create a New CDS View or Extend the Existing One

• New View: If you want to keep the original view untouched, create a new CDS view that builds upon the existing one.

  define view Z_My_View_With_Quarter as select from Z_My_Existing_View {       // Field list   }  

• Extend the View: If you prefer to add the quarter field directly to the original view, use the extend view statement:

  extend view Z_My_Existing_View with Z_My_View_Extension {      // Quarter field definition  }  

3. Calculate the Quarter

• Using a CASE Statement: You can use a CASE statement to determine the quarter based on the month extracted from the period field.

  define view Z_My_View_With_Quarter as select from Z_My_Existing_View {      *,      case          when substring(period, 5, 2) between '01' and '03' then 'Q1'          when substring(period, 5, 2) between '04' and '06' then 'Q2'          when substring(period, 5, 2) between '07' and '09' then 'Q3'          else 'Q4'      end as quarter  }  

• Using a Formula: If your period field is a date field, you can use built-in date functions:

  define view Z_My_View_With_Quarter as select from Z_My_Existing_View {      *,      concat('Q', floor((month(period) + 2) / 3)) as quarter  }  

4. (Optional) Add Annotations

• Enhance the usability of your quarter field by adding annotations:
  • @UI.lineItem: To display the quarter in list reports.
  • @Analytics.dimension: To use the quarter as a dimension in analytical tools.
  • @Consumption.label: To provide a user-friendly label.

  extend view Z_My_Existing_View with Z_My_View_Extension {      @UI.lineItem: true      @Analytics.dimension: true      @Consumption.label: 'Quarter'      concat('Q', floor((month(period) + 2) / 3)) as quarter  }  

5. Save and Activate the CDS View

• Save your changes and activate the CDS view.

Example with a Complete CDS View Definition

@AbapCatalog.sqlViewName: 'Z_SALES_QTR'  define view Z_Sales_With_Quarter as select from I_SalesOrder {      key SalesOrderID,      CreationDate,      concat('Q', floor((month(CreationDate) + 2) / 3)) as Quarter,       NetAmount  }  

Important Notes:

• Adjust Field Names: Replace period, Z_My_View_With_Quarter, and Z_My_Existing_View with your actual field and view names.
• Data Type: Ensure the quarter field has an appropriate data type (e.g., String).
• Performance: For very large datasets, consider the performance implications of your chosen method for calculating the quarter.
• Testing: Always test your CDS view thoroughly after adding the quarter field to ensure it functions correctly.

By following these steps, you can effectively add a quarter field to your CDS view and enhance its analytical capabilities.