Hi Derek,

Thanks for sending this our way. This is really interesting. Let's start small and work our way up from there.

Instead of using the Freehand SQL editor, can you see what happens when you use the Simple editor in the Formula Type dropdown? (Aggregate functions like max() aren't supported by the freehand editor anyway)

Do you get the same error when you use the Simple editor?


Thanks,

-Conner

So it works when I create the field using the formula builder






But the same formula doesn't work when using freehand SQL




Another thing to note is that this problem exists when creating calculated fields at the view level as well.

Derek,

Thanks for the clarification. I just spoke to some of the other members of the team, and I think I've got some information for you.

One of the primary back-end differences between the Simple SQL builder and the Freehand editor is that the Simple builder is capable of utilizing the "Optimized View" that Yellowfin generates. The Optimized View allows Yellowfin to only perform joins on the tables that it needs to, instead of all of the tables in the view.

So, by unfortunate default, the Freehand builder has to join ALL of the tables in your view, even if the query only returns results from a couple of tables. This is a rare case where an issue crops up that draws upon shortcomings in both a database and Yellowfin.

I'm sorry that I don't have better news for you here, but it looks like the best way to generate calculated fields would be to use the Simple editor or some of the pre-built functions if you can.

Please let me know if you have any further questions on this!


Thanks,

-Conner

Is there no plans to allow for the optimized view within a freehand calculated field? I use the simple editor where I can but this limitation effects all reports within that view, so I'm limited to only being able to use the simple editor which is not a reasonable solution.

I have asked some of the gatekeepers on this, and it doesn't sound like this functionality is in the product roadmap, as it would require extensive rewrites of key infrastructure.

I'm sorry to be the bearer of bad news on this one, please let me know if you have additional questions.


Thanks,

-Conner