I had an issue yesterday with a field that contained a concatenated type code (Type in this report) and a ROW_WID . The report is supposed to show the Type value only:CA instead of CA384639 for the Type field while maintaining the correct subtotal count.
Substring function to extract the first two letters as follows doesn’t work because the subtotal operation is performed AFTER the substring command is done
You get then for the first subtotal 1 instead of 2 because even though the aggregation is set to “count distinct” , it's counting 'CA'
The solution we used kind of a kludge but it worked): change the Type data type to “HTML” and using a CSS to hide the WID part, enter the following in the column formula:
And voila you get
