Hana Modeler / Interactive Analysis / Pivot Table / Calculated Column

[#1]

Hello everyone,

It is my first post on the forum so I hope I am using the proper SAP Managed Tags.

I am using SAP Business One version 10 with SAP HANA version 10.00.160. My goal is to make create Modeler Fact tables, Query and Reuse views so I can create Interactive Analysis pivot tables for my company.

So far, I have been using SQL Procedures and have been calling them via an ODBC connection in Microsoft Excel. Everything worked fine but storing data in big files is never convenient for many obvious reasons and this is why we wanted to use Interactive Analysis. And this is where I encountered my problem.

My Query View looks like this :

It's a Star Join, linking two Dimensions (Customers and Products), connected to the projection of the Fact Table, which is an Union between Orders (OINV) and A/R Credits (ORIN). What I am trying to do is to get the Gross Profit of our documents (Orders and A/R Credits), both per document or per line.

(The Union/Fact Table between OINV/ORIN)

Usually with our previous setups (Stored Procedures and ODBC driver in MS Excel), we were able to insert calculated fields, to calculate the Gross Profit for instance. Apparently this is not possible using the Modeler. Instead, according to the SAP forums posts I browsed, the right approach is to create a calculated column. Which I did, using the following way :

In both the Orders and Credits facts tables, i created a calculated column which looked like this :

I had to use a CASE statement to handle the fact that some values could have been at zero and then could have generated a division by zero error. The CASE statement for my A/R Credits is the same, except that it is multiplied by -1 (to create negative lines in the Pivot tables).

This is almost working properly but not exactly. Here is a screen of the pivot table that I generated using the Modeler approach :

The columns are the following :

- 1 : DocNum of our Invoices (OINV)

- 2 : Total amount of the Document/Line

- 3: Gross Profit given by SAP (INV1.GrossProfit)

- 4: The % of Gross Profit that was generated by the Calculated Column

- 5 : The % of Gross Profit I calculated manually in the Pivot Table

As you can see, each line is calculated correctly, however the % of Gross Profit per Document is never correct (here highlighted in red).

Do you have any idea how I can fix this ? I feel like I am missing something obvious but could not find the solution after long researches.

Thanks in advance.