I calculated the Period Balance in a sub query giving the From Date filter set as Link to filter "Same Operator" the selected from date of the main report and the calculation comes correctly.

In order to display the "Period Balance" just above the Grand Total I am using a char with a higher decimal value in the ASCII as "{Period Balance}" and it comes correctly at the bottom just above the Grand Totals are displayed.

Is this the way to go or is there a better way of doing this?


Only draw back is if I am using the above method, I must not allow column sorting on the report by the user, as then it will affect the positioning s of the Opening Balance and The Period Balance rows.

Any recommendations on this?

Hi Shanika,

I achieved your above requirement using sections:

I create a calculated field for the Master Query with the value "Balance", and one for the Sub-Query called Transactions, and used this field as my Section field.

I applied a Section Total and a Grand Total in the Section Formatting

This allows for each section to be totaled separately (ie Balance Total and Transactions Total), and a grand total of all sections.


Regards,
Stephen

Hi Shanika,

I achieved your above requirement using Sections:

I create a calculated field for the Master Query with the value "Balance", and one for the Sub-Query called Transactions, and used this field as my Section field.



I then enable the Section Total as well as a Grand Total in the Section Formatting



This allows for each section to be totaled separately (ie Balance Total and Transactions Total), and a grand total of all sections.



Even with Column Sorting enabled, only data within each section will be sorted.

Hope this helps you to achieve what you are trying to do.

Regards,
Stephen