Two decimals sql to table on the perspective page

cast(coalesce(100.0*(Rejects)/nullif(attemped,0),0)as numeric(38,2)) 
as '% Rejects/Attempted'

So I both used a cast and tried to make sure I used a float.

I also went to format the number as 4 decimals, and then deleted two zeros.

Whatever I do though, when I get 100%, it shows 100 instead of 100.00

replacing the 38 with 5 would work, but sometimes I get data that I both can’t ignore, need to show, and yet is 3000% for some reason or another, like part of the line broke and circumventing resulting in some counting irregularities, so then the 5 will result in an overflow error.
I could catch the overflow, I could do a case, see the value size then set the decimal
hoping there is a better cleaner way

Return the data in whatever way is easiest to query (ie don’t wory about casting to numeric) and then edit the numberFormat string in the table. I didn’t check your SQL, so I’m taking your word for it that the forumla returns the data you want. Your alias name may work, but I don’t like spaces, so I’d go with:

COALESCE(100.0*(rejects)/NULLIF(attemped,0),0) AS rejectPercent

In props.columns of the table, create as many objects as you have columns you want to display (looks like 1). Use the plus button and an object with the correct structure will be automatically created.

  • Set the field value of the appropriate column to rejectPercent (or whatever you use as an alias).
  • In the same object, set numberFormat to 0,0.00. This will guarentee you two decimal points.
  • Set header.title to % Rejects/Attempted to preserve the display you want.

I set the number format to 0,0.0000 and I can get 4 digits in the decimal.

Then, when 100 comes in though, the zeroes seem to be ignored.

I can live with it, but if it can be fixed that would be good.

This is the default table data edited to add some 100s. It seems to work here, so I’m not sure what the issue is. I’m using Ignition V8.1.5.

I will try it again.

I am on 8.1.0