How to get by Crystal report the BP balance ? if I get column balance it's not true.
Owen Slater replied
The JDT1 table contains all the information you need to calculate the current balance for a BP. The ShortName field will contain the BP Code for any transaction type posted against the BP.
For example, the following query will return the balances for all BPs:
select T0.CardCode, T0.CardName, T0.CardType, isnull(sum(T1.Debit - T1.Credit), 0) as Balance from OCRD T0 left outer join JDT1 T1 on T0.CardCode = T1.ShortName and T0.CardType in ('C', 'S') group by T0.CardCode, T0.CardName, T0.CardType