Enhance an Existing Calculation View with Formula and Currency Conversion

Organization wants and enterprise report. All Sales values should be shown in USD.

Enhance an Existing Calculation View for Date and Currency Conversion

1. Go to the SAP HANA Modeler perspective.

2. Copy the existing calculation view into your package and name it

3. In the aggregation node of your new calculation view, adjust the label, data type, and
expression of the two calculated columns named SALES_DAY and SALES_YEAR, based on
the field CHANGED_AT

a) In the Scenario screen area, highlight the Aggregation node.

b) In the Output pane on the right, expand the Calculated Columns folder.
c) Under Calculated Columns, right-click SALES_DAY and choose Edit….
d) Make sure that the data type is given as in the table. In the Label field, change the description.
e) Instead of ´´, enter the expression from the preceding table, choose Validate Syntax, and then OK twice.
f) Repeat steps b – e for SALES_YEAR using the expression from the preceding table.
g) Save and activate the view.

Note:
It is important to use ” ” for column names, and to use the correct data types. ‘ ‘ delimits a string. The functions date and string are type converting functions.

4. Check that DELIVERY_DATE and CREATED_AT are hidden. Save and Activate the Calculation View.

a) In the Scenario pane, click the Semantics button.
b) Make sure that in the properties for DELIVERY_DATE and CREATED_AT the flag “hidden” is set.
c) Choose Save and Activate .

5. Preview the data for your sales order ID

Implement a Currency Conversion Using a Star Join Calculation View

1. Create a new calculation view in your package as follows:

2. Insert a projection node and name it Data_Foundation_Sales.

Note:
(No spaces are allowed in node and column names. Instead, you use the _sign.)

3. Include FND_SO_C_CV2 in the Data_Foundation_Sales node and include all of its fields

4. In the Scenario pane, link the Data_Foundation_Sales node with the Star Join node. Make sure that all fields are passed on to the Semantic node

5. Add the following calculation views to the Star Join node:

● Your previous product master data dimension view, ZDIM_PD_C_CV2
● Your previous time view, ZDIM_DAY_C_CV2
● A prepared employee master data view, P_DIM_EM_C_CV2
● A prepared business partner master data view, P_DIM_BP_C_CV1

6. In the Details screen area, create left outer joins between the Data_Foundation_Sales and the dimension views. Join the following fields:

Note:
Be sure to map only fields with matching data types. Do not connect
CHANGED_AT with DATE_SQL because they have different data types. Always
connect the GUID with the GUID. You can see a little symbol at each field that
indicates a data type (characters, numbers, or binary).

a) In the Scenario screen area, choose the Star Join node.
b) Right-click in the Details area, and choose Create Join .
c) Choose as a left table the view Data_Foundation_Sales, and choose as the right table
the view ZDIM_PD_C_CV2.
d) Draw a line from the CLIENT field in the left table, to the CLIENT_PD field in the right
table,
e) Draw a line from the PRODUCT_GUID field to the PRODUCT_GUID field.
f) Select the Left Outer join type, and choose OK.
g) To join the other fields in a better layout, repeat steps b to f.
h) To optimize the view of the joins, at the bottom of the Details screen area, choose (Auto Layout). The orientation of your dimension views may vary from the following image.

7. To avoid automatic renaming of duplicates, rename three columns as follows:

8. On the Star Join level, add GROSS_AMOUNT and NET_AMOUNT to the output, again.

9. Guarantee currency specific sums of original amount values: For the columns CURRENCY_CODE, GROSS_AMOUNT_ORIGINAL, andNET_AMOUNT_ORIGINAL define the following Semantic Types:

a) In the Scenario screen area, choose Semantics, and in the Details screen area, choose the Columns tab if necessary.
b) Choose (Auto Assign). Make sure that all amount and quantity key figures are recognized as measures.
c) In the Name column, highlight CURRENCY_CODE.
d) On the toolbar, click Assign Semantics .
e) As Semantic Type, choose Currency Code. Then choose OK.
f) In the Name column, highlight GROSS_AMOUNT_ORIGINAL.
g) On the toolbar, click Assign Semantics .
h) Next to Semantic Type select Amount with Currency Code.
i) At the Currency: field, choose the eclipse value help button .
j) As Type:, choose Column. Expand the Columns folder. Double-click the CURRENCY_CODE entry. Confirm with OK.
k) Confirm that the Aggregation of all measures is Sum.

10. Turn the measures GROSS_AMOUNT (not: GROSS_AMOUNT_ORIGINAL) and NET_AMOUNT into columns of the Semantic Type: Amount with Currency Code, and configure a currency conversion. Enter the following data:

Caution:
It is important that you choose “Type: Column” for Source Currency and Conversion Date, and choose “Set to NULL” as “Upon Conversion Failure”

11. Define the view as a Cross Client view, and allow for the selection of CHANGED_AT.

On the View Properties tab page, as the Default Client, choose Cross Client.

12. Check the view and in case that naming changes appear to make the fields names unique, accept them. Activate and preview your view.

a) To check the view, choose Save and validate changes to this object , and confirm all naming changes.
b) To activate the view, choose the Save and Activate button.
c) Open the data preview .
d) Go to the Raw Data tab.
(You might note that some entries with original currency INR (Indian rupees) could not be converted due to a missing conversion factor.)
e) Close the open tabs in the HANA Studio.

Optional View the Result of the Calculation View in SAP BusinessObjects for Analysis

1. Use SAP BusinessObjects for Analysis, Edition for Microsoft Office to view the results with your SAP HANA database user.
a) Go to Start . Choose Analysis for Microsoft Excel.
b) Wait for Excel to start. Go to the Analysis tab.

2. Insert the view ZFCT_SO_C_CV2 as a data source at Excel cell A3. Log on directly to the HANA_DB connection with the SAP HANA credentials

a) Select cell A3.
b) Choose Insert Data Source → Select Data Source….
c) To log on directly to SAP HANA without SAP BusinessObjects BI Platform choose Skip.
d) Choose the HANA_DB connection. Press enter and log on with the credentials
e) In the Search For field, enter ZFCT and press Enter.

f) In the Area tab , select the ZFCT_SO_C_CV2 view, and choose OK.

3. Display the design panel with technical names.

a) Choose Display → Show Technical Names.
b) In cell A1, enter the text provided.
c) In the Design Panel area, use the Display drop down and select Show Technical Names.
d) Right click in A3 (GROSS_AMOUNT) and choose Filter Members.
e) In the Analysis panel, drag [SALES_YEAR] from the DATA SOURCE screen area to the COLUMNS screen area.
f) Drag the characteristic CATEGORY to the ROWS.
g) In the DATA SOURCE screen area, expand the folder for CATEGORY and its subfolder Hierarchies.
h) To change the flat representation to the hierarchical display, drag ZC_PCH to the characteristic CATEGORY.
i) To open the subobjects of DATE_SQL, choose the + button, and open the node Hierarchies.
j) Drag the hierarchy ZTIME_LEVELHIERARCHY to the BACKGROUND FILTER panel.
k) Right click ZTIME_LEVELHIERARCHY, choose Filter by Member, and deselect the node 12.2011.