Skip to main content

SAP HANA - display value formatted like a currency with commas and 2 decimals in a B1UP label

Thomas
  • Updated

Please note: This sample contains .NET code. We do not offer support in modifying or debugging .NET code. Only for advanced users.

Scenario: Customer wants to display a monetary value formatted like a currency with commas and 2 decimals in an IPT label. This should be displayed on the Sales Order and should be updated when a new customer is selected.

Solution: This is unfortunately not as easy as it is on MSSQL as you have no way of converting the decimal to a string value while keeping the formatting on SAP HANA. This makes it impossible to set the label value with the correct formatting using the macro as when it is read as a string by B1UP it will lose the formatting.
To make it work you will need a .NET Code snippet that reads the value as a double value and then convert it to a string.

Setup:

You have a label on the Sales order screen that should contain the value (This one is created by IPT):

You have a B1 Validation that is triggered when the CardCode is validated:

That calls a macro:

That uses a .NET snippet to get the remaining credit and then sets it on the label

The .NET snippet pulls the CardCode from the screen and execute a SQL to get the remaining credit and then returns it formatted as a string.

.NET Code:

var cardcode = ((SAPbouiCOM.EditText)form.Items.Item("4").Specific).Value;

var oRecordSet = (SAPbobsCOM.Recordset)company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);

oRecordSet.DoQuery("SELECT TO_DECIMAL((OCRD.\"CreditLine\" - (\"Balance\" + \"OrdersBal\"))) FROM OCRD WHERE OCRD.\"CardCode\" = N'"+cardcode+"'");

return Convert.ToDouble(oRecordSet.Fields.Item(0).Value, System.Globalization.CultureInfo.InvariantCulture).ToString("N");

Related articles

Comments

1 comment

Date Votes
  • Richard Smith

    Hi,

    This might help others but we managed to get this to work by using the TO_VARCHAR function as detailed here TO_VARCHAR Function (Data Type Conversion) | SAP Help Portal

    For example, our client wanted a quotation value on BP Master Data:

    @Store1 = SQL(select TO_VARCHAR(sum(T0."DocTotal"),'9,999.00') FROM OQUT T0 WHERE T0."CardCode" = $[$5.0.0])

    This returned a nicely formatted value of 15,036,672.00 to an IPT edittext field (rather than a label). Without the TO_VARCHAR function the value returned was either 15036672 or 15036672.000000 depending on the syntax used for the IPT edit text field.

     

    0

Please sign in to leave a comment.