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.
