Hello guys,
I would like to discuss with you the best approach to using SAP HANA calculation views exposed by a CAP service. I have several tables on the HANA DB that I have joined to get a single table on which to perform my analyses and aggregations. During the aggregation phase to get the measures I need, I used the Hidden property present on the Semantic node to ensure that the columns are not actually present as fields of the table but can be used as conditions in a where clause. For instance:
My initial table:
| Name | Last Name | Company | Office |
| John | Smith | Company 1 | Office 1 |
| Michael | Doe | Company 2 | Office 2 |
| Steve | John | Company 3 | Office 2 |
| Patrizia | Rossi | Company 2 | Office 3 |
My Calculation View:
| Company | Employee |
| Company 1 | 1 |
| Company 2 | 2 |
| Company 3 | 1 |
If I run a select * from my_calculation_view from the SQL console I get my Calculation view with 2 columns but it is also possible to run select * from my_calculation_view where Office= ‘Office 2’ to get this result
| Company | Employee |
| Company 2 | 1 |
| Company 3 | 1 |
So Office column is not visible in my view but it is possible to use it in the where condition at least on the SQL console.
We generated the OData with a CAP service, but the hidden fields are not in the metadata so when we try to apply the $filter=Office eq ‘Office2’ it doesn’t work.
which is the best approach in this case?