I realized after I built the visual in arena, part of the problem we are experiencing is this works in spice, but not on direct query, so I can’t give a working example of how this is broken. We have a pivot table with calculated fields in the Rows, Columns, and Values so this can be somewhat dynamic. When the ‘Record Type’ is set to Totals Only, we get the “your database generated a SQL Exception…” error.
When you choose any value for ‘Record Type’ that is not ‘Totals Only’ it works.
For some reason it doesn’t like a string being there, rather than a field. And if you create a calculated field that is just a blank string, it still causes the error. This is a snippet of the calculated field that returns the blank string if ‘Totals Only’ is selected.
The most curious thing is, this setup works on other analyses, just not this one. We’ve tried different type casting on strings and dates, but so far nothing resolves it. Putting it in spice does resolve the issue, but I’d like to see if there is a better solution than that.
Any help is much appreciated!