Hi Norman,
As mll md mentioned, you see the stars because the mechanism of the system report's column total is really only a sum of all figures in the column. As the FC column can hold many different currencies, it cannot show a reliable total.
So you are on the right track. You need to write a query. There are two things to note:
1. The opening balance you see in the system report, is not a value stored anywhere, it is a calculated number. That means that you will have to "double" your query. In pseudo sql code you will have to do this:
SELECT SUM(SomeValue) AS OpeningBalance FROM SomeTable WHERE DateField Between @BeginningOfTimes AND @TheDayBeforeYourStartingDateParameter UNION ALL SELECT SUM(SomeValue) AS Balance FROM SomeTable WHERE DateField Between @YourStartingDateParameter AND @YourEndingDateParameter
2. As the field values are in different currencies, you will have to be careful to include the ORTT table, and convert each sum to a common currency (probably system currency?), before you add them together. So again in pseudo sql code:
SELECT SUM(SomeValue * RateForTheGivenCurrency) AS Balance
Regards,
Johan
Dear Norman,
it display ***** because the balance involved more than two currency.
I think you cannot query direct from any table, but have to create mathematical formula to calculate the cumulative balance.
Add comment