Question

Lobby Element Designer- List Element- Formatting $$$

  • 14 July 2020
  • 7 replies
  • 755 views

Userlevel 4
Badge +10
  • Sidekick (Customer)
  • 119 replies

I have a List Element and was wondering how do I format the numbers as currency?

You’re able to do it for Lists and Pie Charts numbers but not Lists.

 


7 replies

Userlevel 5
Badge +10

This is handled in the datasource of the lobby element, you can mark the formatting for the specific column. (Text, Number, Currency etc)

Down in the column section.

Userlevel 4
Badge +10

thx. Hmm, I have those 4 columns set to Currency:

 

Userlevel 5
Badge +10

How would you like to have it formatted? With the currency sign in front or…?

Otherwiase a to_char might work, this sometimes scrambles the List-Lobby-Item though in term of outlining.

Userlevel 4
Badge +10

I set the Type column to Currency. That work on Line and Pie Chart elements but apparently not in Lists?

I then shouldn’t have to put a to_char in the SQL, correct?

Userlevel 5
Badge +10

This is the I've used for showing the open amount.

to_char(round(SUM(open_amount), '9G999G999','NLS_NUMERIC_CHARACTERS = '',.''')

what also, just for inspiration, could be a good idea is to add the possibility to divide the number and make it possible to show it as THOUSANDS.

then it would look like this;

to_char(round(SUM(open_amount) keep (dense_rank last order by balance_date)/$DIVIDE_BY$, 0), '9G999G999','NLS_NUMERIC_CHARACTERS = '',.''')

 

Userlevel 4
Badge +10

looks great- however I get an syntax err when testing it in SQL Query Tool- where is the 3rd right parentheses?

 

my code:

to_char( round( SUM (
case when TRUNC(wanted_delivery_date) <= TRUNC(sysdate - INTERVAL '1' DAY) then
( SELECT
SUM( (buy_qty_due + (qty_shipdiff / DECODE(conv_factor,0,1, conv_factor*inverted_conv_factor)) - qty_invoiced) * sale_unit_price )

FROM rega1app.CUSTOMER_ORDER_LINE col

WHERE col.order_no = co.order_no

and ( BUY_QTY_DUE + (QTY_SHIPDIFF / decode(CONV_FACTOR,0,1, CONV_FACTOR*INVERTED_CONV_FACTOR)) - QTY_INVOICED) > 0
and LINE_ITEM_NO <= 0
and OBJSTATE in ('Delivered', 'PartiallyDelivered', 'Picked', 'Released', 'Reserved')
)
else 0 end
), '9G999G999','NLS_NUMERIC_CHARACTERS = '',.''')) "Past Due Order Gross Amount"

 

your example:

to_char(round(SUM(open_amount), '9G999G999','NLS_NUMERIC_CHARACTERS = '',.''')

 

Userlevel 4
Badge +10

I was able to format it w/o a syntax err but then get this when i run it:

 

to_char( ROUND( SUM (
case when TRUNC(wanted_delivery_date) <= TRUNC(sysdate - INTERVAL '1' DAY) then
( SELECT
SUM( (buy_qty_due + (qty_shipdiff / DECODE(conv_factor,0,1, conv_factor*inverted_conv_factor)) - qty_invoiced) * sale_unit_price )

FROM rega1app.CUSTOMER_ORDER_LINE col

WHERE col.order_no = co.order_no

and ( BUY_QTY_DUE + (QTY_SHIPDIFF / decode(CONV_FACTOR,0,1, CONV_FACTOR*INVERTED_CONV_FACTOR)) - QTY_INVOICED) > 0
and LINE_ITEM_NO <= 0
and OBJSTATE in ('Delivered', 'PartiallyDelivered', 'Picked', 'Released', 'Reserved')
)
else 0 end
) , '99G999G999'),'NLS_NUMERIC_CHARACTERS = '',.''') "Past Due Order Gross Amount"

 

 

Reply