Hi Paul,

The only way to do this is to create a calc field in the master query and then sum all the fields from the sub-queries.

It will then show as a seperate column, and you can apply column totals if needed.

E.g.





There is no way around this since you're totalling up values from the sub-queries.
If no sub-queries were involved things are a lot more simple ;).

Please let me know if this doesn't give you the results you were after.

Regards,
David

Hi David

Thanks for that. I only saw your response today; I did not receive an email alert, which was strange.

One further issue please.

I am trying to create a calculation in a report that uses advanced sub-queries. The numerator will come from one source (sub-query 1) and the denominator from a second source (sub-query 2).

In v 6.3, I could create a calculation in the master query because I could select fields from both sources. However, in v 7.0, I cannot see the fields in the other other source. As the Master query is based on source one, I can only see the fields in sub-query 1.

How do I accomplish this now?

Thanks

Paul

Hi Paul,

All good, not sure why, maybe check your spam folder?

In terms of not being able to see the fields from both sub-queries.
What version of 7.0 are you using?

I have created a report with 2 sub-queries from different views and all fields are being returned in the calculated field :




The only thing I could think that would cause this, would be if these fields themselves are calculated fields, so you would be using calc fields in calc fields.
In this case you would need to ensure that the 'calculation' check box has been ticked at the view level :




Please let me know if I'm missing something.

Regards,
David

Hi David

Thanks for your response.

I went back to the view level and ticked boxes on 'calculations' and 'grouping' on the calculated fields that I created, but it does not appear to have changed the situation.

I attach a pdf to show what I see at report level. The Master and first sub-query are from 'Source 1'. The second and third sub-queries are from 'Source 2'.

I created this report in v 6.2 or 6.3, and it still works fine. However, when I look at the calculated field formula creator in the master query (from Source 1), the part of the formula that comes from the Source 2 says 'null'. In the select field drop down, I can only see Source 1 fields; Source 2 fields are not visible.

I also attach the sql statement.

Below you will see our system information.

Application Version: 7.0
Build: 20140512
Java Version: 1.6.0_03
Operating System: Windows Vista 6.0 (x86)

Your help is appreciated.

Regards

Paul

Hi Paul,

Thanks for the detailed info.
I can replicate your issue and raised a defect (TASK ID = 162830) which will be allocated to the dev team next week. So this is something that should be fixed in the July 7.0 update..

I also tested this in 6.3 and confirmed it works. Then exported this report into 7.0, and also see the 'null' where the calc field name should be.

Sorry for the inconvenience.
Expect this to be fixed an included in the patch that will be released on Friday July 25th.

Please let us know if there was anything else you were after.

Regards,
David