SAP HANA Calculation view Conversion Function Doesn't work in Semantics

[shyam_uthaman]

Hi All,

I wrote the scalar function to trim leading zeros using the process mentioned in SAP HANA Modeling Guide (https://help.sap.com/http.svc/rc/fb8f7a9f7860468b84a07eab0a7d0a98/2.0.02/en-US/SAP_HANA_Modeling_Guide_for_SAP_HANA_Studio_en.pdf) Page 148.

I used this in the semantics layer of a calculation view in the conversion functions column. Defined it as an Internal to external conversion but it doesn't seem to work.

Can you please help me understand why this doesn't work?

Thanks,

Shyam

FUNCTION "<packagepath>::LeadingZeroTrim" (POLineNumber NVARCHAR(100)) 
	RETURNS TrimmedValue NVARCHAR(100)
	LANGUAGE SQLSCRIPT
	SQL SECURITY DEFINER AS
BEGIN
/***************************** 
	Write your function logic
 *****************************/
 
 select ltrim(:POLineNumber,'0') into TrimmedValue from dummy;
 
 
END;

[pfefferf · Accepted solution]

Have you read the following two notes in the chapter?

1) This feature is currently not supported by SAP analytic client tools. You can convert attribute values through external or customer applications that read column meta data from the BIMC_DIMENSION_VIEW.

2) These functions are not applied in the database layer. It is used for providing hints to analytic clients on how to convert values before displaying them in the client user interface (UI).

So if your conversion function will be applied is depending if the client supports that, as the function is not executed on DB layer. If you have tested your calc. view e.g. in HANA Studio the conversion will not be applied, because HANA Studio does not support that. Also (as the first note says) other SAP analytic client tools will not support that too.

Maybe it is an option for you - if you do not have a client which supports the conversion - to add an additional calculation view on top of your existing calculation view as a kind of consumption view. The additional calculation view can provide the required data format via calculated columns.

Regards,
Florian

[shyam_uthaman · Accepted solution]

Hi Florian,

Thanks for the reply. I did read those notes but was hoping that there would be a way to directly apply these functions on the columns. Just like we did conversions in BW info objects. Would have been a cool feature.

I have a requirement where we need to remove leading zeroes from fields like Material numbers, Purchase order lines, cost centers, GL accounts. Could have been a life saver to have just that function applied at the semantics.

Shyam

[uli_m · Accepted solution]

Dear Florian,
I have the same requirement -> cutting leading zeroes. The CV is used to fead a TABLEAU dashboard.
After checking with our Architects I was told, that we are not allowed to create Scalar Functions, as we have no HANA native license, but BW4 only.

Does that mean that my Calcuation views cannot format a Material, Customer,.. Number as seen in ECC, or does SAP deliver any of them, like the Set of FMs available on SAP ABAP Stack? Currently I do not find any in our BW4 (2.0SP5).

Kind regards,

Uli

P.S. I do not really understand what this sentence means - does it convert - or does it provide a HINT!?

2) These functions are not applied in the database layer. It is used for providing hints to analytic clients on how to convert values before displaying them in the client user interface (UI).

[former_member751591 · Accepted solution]

Thanks for coming to SAP Community for answers. Please post your question as a new question here:

Since you're new in asking questions here, check out our tutorial about asking and answering questions (if you haven't already), as it provides tips for preparing questions more effectively, that draw responses from our members.

Please note, that your post here won't be answered.

[shanthi_bhaskar]

and performance wise it is not advisable to do the these string operations in the select statement where you are reading the database table directly.

Thanks,

SB.