Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

When I look at the prepare step I see similar differences. One is listing the summation symbol and the other is not. I don't understand why.

I ran a monitor on my SQL Server and was able to capture the query Yellowfin is attempting. The problem is the SUM expression wrapping the other SUM. I don't understand what part of Yellowfin is triggering this and how to turn it off. Obviously it's not doing that for the PPG ratio I setup above.

If I manually remove that in SQL Server Management Studio, the SQL works exactly as expected.

I have gotten my OTP Ratio to start working. I went through all of the fields in my view, made sure all of the fields that were intended as dimensions were correctly marked as dimensions (some were not), and made sure every non-pre-built-calculated-field metric had a default aggregation selected. Once I did this, the OTP Ratio started working.

Then I tried reversing the step I had taken (by setting default aggregation to None and changing dimensions back to metrics) to reproduce the broken condition, but I could not. Somehow once the ratio started working it stayed working.

I guess it's just another mysterious bug in YellowFin...

Created a new view and tried to use the ratio function and this problem has returned. My strategy of ensuring that all of the other fields have default aggregations and everything is labeled properly is not working. This is getting very frustrating.

If I go to the advanced functions menu item for the sum that is not working and choose the no aggregation option:

I can get Yellowfin to generate the proper SQL:

When I execute this SQL in SQL Server Management Studio I get the expected results:

But Yellowfin refuses to execute the correct SQL and give me the results I want. I guess the software is not happy unless it's giving me errors and not working:

I think I finally figured out how to fix this issue. The problem seems to be that when you choose a pre-defined calculated function, you no longer have the ability to choose a default aggregation. This suggests that there is no default aggregation for this field, but that is not true.

To fix this I had to change my calculated field from a pre-defined function back to a simple function (it doesn't matter what your function is, I just put in the number 5):

This re-enables the default aggregation selection, and you need to change it to None.

Now you can change your calculated field back to the pre-defined function you were using before (ratio in my case) and when you try to use the field in reports, Yellowfin will stop trying to apply that secret default aggregation and breaking your SQL

The Yellowfin devs need to fix this by making sure that when the user sets up a pre-defined calculation and you disable the users ability to change the default aggregation, that default aggregation variable in the background needs to be set to None.

Hi Stephen,

Thanks for reaching out and for detailing your thoughts and actions here. Glad you appear to have gotten to the bottom of this.

I'll investigate and replicate this (and will likely be logging it it seems) on Monday morning/afternoon.

Regards,

Mike

Hi Stephen,

I've had a chance to test this, but am thus far unable to replicate what you're seeing.

This does not occur using the embedded HSQL database:

I then tried this against MS SQL Server, thinking this may be a driver and/or RDBMS related issue again, but I'm still not seeing the corresponding value beingn applied as a SUM aggregation:

This seems to imply it's either your underlying data and/or how the rest of the report or view is setup, may be causing this in your case.

As such, can you perhaps provide more information on how the 'OTP Numerator' field is setup so I can try and replicate this?

When I put 1.0 as the Denominator in my example all appears to be working as it should. In my case, the 1.0 is being applied against a, at the report level: SUM Aggregation Numeric field, which is a SUM Decimal value at the View level, as well as a Decimal at the db level. If you can highlight the differences here that'd be great.

Thanks,

Mike

Hi Stephen,

I just wanted to check in and see how things are going regarding my latest reply.

Regards,

Mike

Have you tried following my solution in reverse?:

1. Change the ratio calculated field back to a simple calculation field
2. Change the default aggregation to summation
3. Change the calculated field back to the ratio pre-defined type
4. Try to aggregate in rport

What build are you using? We're still on 20180515

Hi Stephen,

Thanks for your response. While I can break the report by following the steps you've laid out. I can't get the calculated field to default to Sum aggregation upon building the calculation in either 20180515 or the latest build:

However, after following the steps you've laid out in your last response I can replicate what you're seeing:

I agree a good safety would be to make it so that when a user sets up a pre-defined calculation, the users ability to change the default aggregation is not disabled, considering the default aggregation variable in the background needs to be set to 'None'.

Of course this no longer occur occurs if you switch the calculated field to a Simple formula and remove the Aggregation, so this would be the workaround here.

I've gone ahead and logged this as a defect. Any potential updates regarding this will be posted here.

Regards,

Mike

It seemed inconsistent for me. Sometimes the calculated field would default to the sum aggregation upon creation and sometimes it would not.

Hi Stephen,

Noted. I've added to our internal task that this is particularly problematic if/when a Pre-Defined Calculated Field defaults to a Sum aggregation, though I was not able to replicate this particular aspect of the issue.

I'll keep you posted.

Regards,

Mike