Creating Dynamic Calculation Views

Performing Practical Exercises

Objective

After completing this lesson, you will be able to performing practical exercises

Implement Variables

Exercise Objectives

After completing this exercise, you will be able to:

  • Create variables to filter data based on a user's selection

Business Example

You are a consultant at a customer that sells electronics. You have been asked to build a calculation view to analyze sales data for certain customers. You would like to provide a pop-up prompt where the user must choose one or more customers each time the report is run.

Overview of Exercise Tasks

  • Task 1: Import a New Calculation View for the Marketing E-Mail Campaign Analysis
  • Task 2: Create a Variable to Enable Customer Selection and Filtering

Prerequisites

Prerequisites

Your SAP Business Application Studio development environment should have been set up and the HC300 project imported and deployed.

Task 1: Import a New Calculation View for the Marketing E-Mail Campaign Analysis

You will first import a Calculation View that displays the sales amount for each customer.

Steps

  1. If needed, launch SAP Business Application Studio and start your HCMOD development space.

  2. In the explorer, import the CVC_CAMPAIGN_ANALYSIS.hdbcalculationview file from your course files folder HC300→Calculation Views Templates into the folder HC300_A####→db→src→exercises.

    1. In the Explorer view, right-click your exercises folder and choose Upload....

    2. Select the file HC300→Calculation Views Templates→CVC_CAMPAIGN_ANALYSIS.hdbcalculationview and choose Open.

      Result

      A new calculation view CVC_CAMPAIGN_ANALYSIS is created in your project and opened in a new tab.

  3. Review the design of the CVC_CAMPAIGN_ANALYSIS calculation view.

    1. Review the Join_1 and Aggregation nodes.

  4. Deploy the CVC_CAMPAIGN_ANALYSIS calculation view.

    Check the deployment status.

    1. Choose the Deploy icon for the CVC_CAMPAIGN_ANALYSIS calculation view.

    2. In the Console pane, check that the deployment completes successfully.

  5. Preview the calculation view data in the Raw Data tab.

    1. Right-click the calculation view and choose Data Preview.

    2. Select the Raw Data tab and review the contents.

Task 2: Create a Variable to Enable Customer Selection and Filtering

Steps

  1. In the Semantics node, create a variable using the data from the following table and apply the filter to the CUSTOMER column.

    FieldValue
    NameVAR_CUSTOMER
    LabelCustomer Selector
    Selection TypeSingle Value
    Is Mandatory[Selected]
    Multiple Entries[Selected]
    View/Table Value Help[Keep the Current view (selected by default)]
    Reference ColumnCUSTOMER
    Apply FilterCUSTOMER

    1. In the Semantics node, select the Parameters tab.

    2. Choose + and choose Variable.

    3. Select the new variable to open its properties.

    4. Enter the details as shown in the table.

    5. In the Apply Filter section, use the value help button and select the CUSTOMER column.

    6. Choose Back at the top left of the variable properties screen.

  2. Deploy the view.

    Check the deployment status after activation.

    1. Choose the Deploy icon for the CVC_CAMPAIGN_ANALYSIS calculation view.

    2. In the Console pane, check that the deployment completes successfully.

  3. Preview the calculation view data in the Raw Data tab. Select the customers Becker Berlin and High Tech Park.

    Hint

    To set the value of a variable or input parameter, choose the corresponding From cell, and then the search help button.

    If needed, choose the + sign to add another entry.

    1. Right-click the CVC_CAMPAIGN_ANALYSIS calculation view.

    2. Choose Data Preview.

    3. In the variables and input parameters screen, for the VAR_CUSTOMER variable, choose From and then choose the search help radio button.

    4. From the list, select Becker Berlin.

    5. Choose OK.

    6. Choose the + icon to add another value.

    7. In the new row for the VAR_CUSTOMER variable, choose From and then choose the search help radio button.

    8. From the list, select High Tech Park.

    9. Choose OK.

    10. Choose Open Content and select the Raw Data tab.

    11. Check that the output includes the specified customer only.

  4. Close all open tabs in Business Application Studio.

    1. Right-click any open tab and choose Close All.

Implement Input Parameters

Exercise Objectives

After completing this exercise, you will be able to:

  • Create input parameters with direct input and static value list

  • Use input parameters in calculated columns

Business Example

You are a consultant at a customer that sells electronics. You have been asked to build a calculation view to analyze sales data for certain customers. Two marketing e-mails have been sent out to each customer. Your users want to find out how many days have passed between the two mailing campaigns and the order dates.

Overview of Exercise Tasks

  • Task 1: Modify the Calculation View from the Previous Exercise
  • Task 2: Create an Input Parameter to Select the E-mail Date and a Calculated Column to Determine the Number of Days Between the E-mail and the Sale Dates
  • Task 3: Modify the Input Parameter to Enable User Entry with Default Value

Prerequisites

Prerequisites

Your SAP Business Application Studio development environment should have been set up and the HC300 project imported and deployed.

You should also have completed the Implement Variables exercise. If not, you'll find an intermediate version of the view in your solutions folder : CVC_CAMPAIGN_ANALYSIS_00_STAGE1.

Task 1: Modify the Calculation View from the Previous Exercise

In your CVC_CAMPAIGN_ANALYSIS Calculation View, you will delete the VAR_CUSTOMER variable and add a new column from the source table to the Output.

The new column will be used later on to calculate the number of days elapsed since the last marketing campaigns.

Steps

  1. Open your CVC_CAMPAIGN_ANALYSIS view.

    Note

    If needed, you can use the CVC_CAMPAIGN_ANALYSIS_00_STAGE1 view in the solutions folder.

  2. Delete the variable VAR_CUSTOMER, which will no longer be used because you want to visualize data for all the customers.

    1. In the Parameters tab of the Semantics node, select the variable and choose Remove.

    2. Confirm by choosing Yes.

  3. In the Join_1 node, add the column SALES_DATA.SQL_DATE to the output.

    1. In the Scenario tab, select the Join_1 node.

    2. On the Mapping tab, select then right-click the column SALES_DATA.SQL_DATE and choose Add to Output.

  4. Add the new column to the Aggregation node.

    1. In the Scenario, select the Aggregation node.

    2. On the Mapping tab, select then right-click the column SQL_DATE and select Add to Output from the context menu.

  5. In the Semantics node, rename the column SQL_DATE as SALE_DATE.

    1. In the Scenario, select the Semantics node.

    2. In the Columns tab, change the name and label of the column SQL_DATE to SALE_DATE.

  6. In the Semantics node, check that the new column has the type Attribute.

    1. On the Columns tab, check the Type cell for the SALE_DATE field; it must be Attribute.

Task 2: Create an Input Parameter to Select the E-mail Date and a Calculated Column to Determine the Number of Days Between the E-mail and the Sale Dates

Calculate the number of days elapsed between the e-mail marketing campaign and the sales orders.

Note

There were two marketing campaigns in 2019; one on January 25 and another on February 23.

Steps

  1. In the semantics, create a new input parameter using the following details:

    Hint

    To create an input parameter, select the Parameters tab of the Semantics node.

    General properties

    FieldValue
    NameINP_EMAIL_DATE
    LabelThe date when the e-mail was sent
    Is Mandatory[Selected]
    Multiple Entries[NOT selected]
    Parameter TypeStatic List

    Parameter Type - Static List

      
    Data TypeDate
    List of values

    • Value: 2019-01-25

      Description: First e-mail

    • Value: 2019-02-23

      Description: Second e-mail

      

    Hint

    Use Add to define the list of values.

    1. On the Parameters tab of the Semantics node, choose + and then choose Input Parameter.

    2. Select the new input parameter to open its properties.

    3. Enter the details as shown in the General information table.

    4. Expand the Parameter Type - Static List pane and enter the values specified in the second table. Use the + (Add) button to add each value.

    5. Choose Back at the top left of the input parameter properties screen.

  2. In the Aggregation node, add a calculated column to determine the number of days elapsed between the e-mail and the sale dates, using the following details:

    FieldValue
    General information 
    NameDAYS_ELAPSED
    DescriptionDays between Sale Date and e-mail
    Data TypeINTEGER
    Semantics 
    Column TypeAttribute
    Expression 
    ExpressionDAYS_BETWEEN("SALE_DATE",'$$INP_EMAIL_DATE$$')

    1. On the Calculated Column tab of the Aggregation node, choose + and then choose Create Calculated Column.

    2. Select the newly created calculated column to open its properties.

    3. Enter the details as shown in the table.

    4. Choose Back.

  3. Deploy the calculation view.

    Check the deployment status after activation.

    1. Choose the Deploy icon for the CVC_CAMPAIGN_ANALYSIS calculation view.

    2. In the Console pane, check that the deployment completes successfully.

    Note

    The following intermediate solution object is available at this stage:

    solutions→CVC_CAMPAIGN_ANALYSIS_00_STAGE2

  4. Preview the data, and select a date for the parameter INP_EMAIL_DATE; for example 2019-01-25.

    Hint

    If no input parameter shows up in the data preview tab the first time, close this tab and re-execute the data preview.

    1. Right-click the calculation view and choose Data Preview.

    2. For your input parameter INP_EMAIL_DATE, choose the From cell and then the value help button.

    3. Select a date; for example, 2019-01-25.

    4. Choose OK.

    5. Choose Open Content and select the Raw Data tab .

  5. Close the Data Preview.

Result

In the displayed data, check that the number of elapsed days is correctly calculated based on the input parameter that you selected.

Task 3: Modify the Input Parameter to Enable User Entry with Default Value and Calendar Value Help

When you query the view, the input parameter should be set by default to 2019-01-25, and enable the user to enter a different date.

Steps

  1. Edit the input parameter INP_EMAIL_DATE to change its parameter according to the following table:

    FieldValue
    Parameter TypeDirect
    Semantic TypeDate
    Default Value(s)Constant: 2019-01-25
    Keep the other parameters as is.

    1. In the Semantics node, select the Parameter tab and select the input parameter INP_EMAIL_DATE.

      Note

      The Parameter tab is also accessible from the other nodes.

    2. Set the values using the data in the table. The semantic type can be found in the Parameter Type - Direct section.

    3. To define the default value, select the type Constant and enter the value 2019-01-25.

  2. Deploy the calculation view.

    1. Choose the Deploy icon for the CVC_CAMPAIGN_ANALYSIS calculation view.

    2. In the Console pane, check that the deployment completes successfully.

  3. Preview the calculation view data on the Raw Data tab. In the Variables and Input Parameters screen, keep the default value or enter a different date. In the displayed data, check that the number of elapsed days is correctly calculated based on the input parameter you defined.

    Caution

    If you choose the Value Help, a new dialog opens and you can select a date from the displayed calendar.

    1. Right-click the calculation view and choose Data Preview.

    2. For your input parameter INP_EMAIL_DATE, keep the default value, enter a different date, or select one from the calendar; for example, 2020-01-01.

    3. Choose Open Content.

    4. Check that the data displayed corresponds to the date you entered.

  4. Close all open tabs in Business Application Studio.

    1. Right-click any open tab and choose Close All.

Learning

SAP FacebookSAP YoutubeSAP LinkedIn