Is it possible to define the order when union sub query to concatenate rows
28 January, 2014
Is it possible to define the order when a union sub query is used to concatenate rows ? So that it first shows the Master query result and then the sub query result at the bottom.
For example, my query must return the " Opening Balance " in the main query, and the rest of the records from the sub query below that.
I am using a Union sub query. The report results return the opening balance and the rest of the records correctly calculated and returned. But the Opening balance is not appearing as the first record.
-thanks
Shanika
For example, my query must return the " Opening Balance " in the main query, and the rest of the records from the sub query below that.
I am using a Union sub query. The report results return the opening balance and the rest of the records correctly calculated and returned. But the Opening balance is not appearing as the first record.
-thanks
Shanika
Hi Shanika,
Here is a simple method to sort you Report
Create a Calculated field to store the word "Opening Balance" or the Transaction date.
Master Query
Add 2 spaces in-front of the word (e.g. "Opening Balance")
Sub-Query
Add 1 space in-front of the Date (this will convert it to a text field)
Sub-Query Link
Because both are text fields it will allow you to join the fields together
Output
You now have Opening Balance as your 1st row and the transactions that follow.
Hope this helps you to achieve what you are trying to do.
Regards,
Stephen
Here is a simple method to sort you Report
Create a Calculated field to store the word "Opening Balance" or the Transaction date.
Master Query
Add 2 spaces in-front of the word (e.g. "Opening Balance")
Sub-Query
Add 1 space in-front of the Date (this will convert it to a text field)
Sub-Query Link
Because both are text fields it will allow you to join the fields together
Output
You now have Opening Balance as your 1st row and the transactions that follow.
Hope this helps you to achieve what you are trying to do.
Regards,
Stephen
Thanks Stephen. Adding spaces in front of the "Opening Balance" calculated field did the trick.
-Shanika
-Shanika