Union of two or more reports

Stefan shared this question 1 week ago
Answered

Is there a way to do the Union All of values of two REPORTS and not views ?


I have two reports with different tables and different conditions but the same columns.

I need same columns to be sumed together and displayed as one single column.

Is it even possible to do that ?

Comments (7)

photo
1

Hi Stefan,

Thanks for reaching out. I'm assuming you're referring to using the Add Sub Query section in the report builder?

/AF8MwPcni0vUAAAAAElFTkSuQmCC

If so, the only option is to choose another View. Of course, reports are built on top of Views, so as long as your choosing the reports corresponding View, all the fields in the report will be found in the View itself.

In terms of the second part of this question, you should be able to draft a Calculated Field that sums up the values of two columns no problem: field1 + field2.

/jlVDERABEYgEgf8PaQyScTtfOBoAAAAASUVORK5CYII=

The part that may get tricky is scripting Freehand SQL statements to match certain conditions, which may not be possible depending on the conditions and may indeed require use of joins. In using the example above, if I wanted to do something like Invoice Estimate + Invoiced Amount after 2016, I'd need to filter Invoiced Amount by Year > 2016 in a separate sub-query and Join it to the Invoice Estimate in the main query.

In short, while what you're asking is likely possible, the exact scripting of which, as well as the actual report setup falls more in the realm of Consulting, but I'd be happy to provide further pointers where I can.

Regards,

Mike

photo
1

Perfect!I will try this and comment again with my progress.

Thank you, Mike!

photo
1

Hi Stefan,

You're welcome! Please do.

Regards,

Mike

photo
1

Mike, thanks for current assistance.

I just have one more question for you.

Since I have CASE WHEN statements with parameters in one of my reports and user prompted 'Submit Date' filter in other how do I manage that in UNION ?

photo
1

Hi Stefan,

I can only speak at a general level as this depends on the scripting of each calculated field, and Support doesn't really handle specific report building problems, but basically as long as the columns are matched in the main query and sub-queries, the Union should add rows for each of those respective columns. I'd think that once you applied the user prompt filter values, assuming the Joins are done correctly, the other report with the Calculated Field would still be applied.

What you're asking may be possible, but if you require further assistance with us I'm afraid I can only recommend reaching out to your Account Manager to discuss Consulting options.

Regards,

Mike

photo
1

You may mark this as closed/answered question.

photo
1

Hi Stefan,

Great, thanks for the update!

Please don't hesitate to reach out with any other questions or concerns.

Regards,

Mike

photo