bobtins

Actually, the very last formatting option, which just shows three places, will actually show from three to sixteen, but the number used as an example only has two decimal places. 

Here are some more details if the provided formats are not enough:
You can do further configuration of formatting in Ad Hoc by modifying the file WEB-INF/bundles/adhoc_masks.properties.
Here are the formats for decimal types:
ADH_100_MASK_dec_0 = #,##0.00ADH_100_MASK_dec_1 = 0ADH_100_MASK_dec_2 = $#,##0.00;($#,##0.00)ADH_100_MASK_dec_3 = $#,##0;($#,##0)ADH_100_MASK_dec_4 = $#,##0.000#############  Decimal formats are specified by the Java DecimalFormat class; here's some documentation:
https://docs.oracle.com/javase/tutorial/i18n/format/decimalFormat.html

The definition of the Mid function is in the form of a Groovy language snippet which is part of the SQLGenerator bean.
This definition is in WEB-INF/applicationContext-semanticLayer.xml.
There is a defaultSQLGenerator bean which defines the Mid function this way:
<entry key="Mid"> <value>"substr(" + sqlArgs[0] + ", " + sqlArgs[1] + (sqlArgs.size() == 3 ? (", " + sqlArgs[2]) : "") + ")"</value> </entry>[/code]If you only need to get Redshift working for domains, then you can change this default definition so it uses "substring" instead of "substr".
If you need to get other data sources working, then this config change may affect the Mid function for other datasources, and you would have to configure a Redshift-specific SQLGenerator. Try changing the default and see how that works.

Thanks, this is a good example of something we should be able to support in the product. We are implementing an expansion of the calculated fields capabilities in Ad Hoc for next release, and one thing I would like to enhance is the way we handle summary calculations. Currently, we just allow the user to specify a function, but for cases like this, the user should be allowed to have full control over how to calculate the summary.
A few releases back, we did make a change so that the sum of a calculated field "a/b" would be "sum(a)/sum(b)". The formula that you want for the summary is actually "1 - sum(discount)/sum(retail)".
I put your sample data into a database and tried it out. What I found was that the summary for "discount/retail" comes out as "sum(discount)/sum(retail)", but not summary for "1 - discount/retail" is really "sum(1 - discount/retail)", which is not correct.
Thanks again for bringing this up. Often we are trying to strike a balance between ease of use and power in designing the user interface. In cases like this, users have a good idea of what to do, and we should figure out how to get out of the way and let them do it!
 

I would change your initialValueExpression to BigDecimal.valueOf(0)