How to GROUP BY on a UNION query to summarise the UNION ed data set

Hi Shanika,

Thanks for reaching out with your question. Report creation as it pertains to specific client data falls outside of the Yellowfin Support scope and more under the Consulting category. I'm happy to provide suggestions as to how this should be working.

To prevent having multiple rows for each 'Type', you will want to apply an aggregation to both of your metric columns. It sounds like in this case you will want a 'Sum' aggregation.

I don't have a 7.1 instance running to test with, since it has been End of Life for Support for some time now. In supported versions you can apply a metric by clicking the arrow next to the metric on the 'Data' tab of the Report Editor and choosing your 'Aggregation':

Let me know if this helps.

Thanks,

Ryan

Hi Ryan,

Thank you for your reply. In my scenario, in the main query, I don't want the SUM to be applied for Saving column as it's saved only at booking level - 1 saving value per booking. If I apply SUM on the Saving, the values multiply by the number of items, which is inaccurate. I have applied SUM on Cost column already.

Further more in my UNION query where I do the Item level savings query, I have applied the SUM on both the metric columns as in this case I need the item level Savings value totals as opposed to the Booking level.

Shanika

Hi Shanika,

Thanks for the reply. Another option in this case may be to use the 'Group Data' option to combine your rows. In my example, I'm combining 'Adventure' and 'Culture' into A.

It's a bit difficult to comment on how to best achieve this, as I don't have any picture of your Report Builder to see how it's all configured. If the Group Data option doesn't work for you, kindly provide some additional screenshots of your Report Builder so that I can better visualize what's happening on the Yellowfin side of things.

Thanks,

Ryan

Your outer query is joined on "item". Because it is a sub-query and not a true table join, "item" is going to be included in the group by of the sub-query. I've asked for a way to be able to sum after the join as an idea but that may . You're going to have to revamp your view so that your table is joined in the view rather than the report, again because of the sub-query. Then, you can union making booking and item null on the report. What I typically do is create a calculated field with 'Total' as the value for booking.

Hi Larry,

Thanks for providing your Answer. Let us know how it all goes Shanika.

Thanks,

Ryan

Thanks Ryan and Larry,

Larry : My Item table is joined at the view level. So its an Item based.

In the Booking level (Intersect Query):

I apply SUM on Cost as I need the individual Item costs summed up to get total cost for booking.

I don't apply SUM on Saving as I need to get the Saving at the Booking level which is one Saving value per booking.

In the Item Level (Union Query) :

I apply SUM on each Item Cost as I need the individual Item costs summed up to get total cost for booking.

I apply SUM on Saving as I need to get the Saving at the Item level calculated.

Now I need these two results to be merged together so that I can have summary level detail for each Type having total cost and Saving calculated at individual subqueries.

Ryan : I am not sure how to apply Group in my case.

Thanks,

Shanika

Hi Shanika,

Thanks for the reply. Report creation specific to data and joins starts falling outside of the scope of Yellowfin Support and is generally handled by Consulting directly. I'm happy to reach out to your Account Manager regarding this if it's a path you wish to pursue.

I look forward to your reply.

Thanks,

Ryan

Hi Shanika,

I wanted to check in on this and see if you wanted assistance in contacting your Account Manager regarding possible consulting for this item. If I don't hear back, I'll presume we're ok to close this out.

Thanks,

Ryan

Hi Shanika,

I'm going to go ahead and mark this as Answered, as I haven't heard back for some time.

Thanks,

Ryan