Skip to Content
avatar image
Former Member

how to get the cumulative value in crystal reports

hi all,

this is the continuation of my question located at BP Account Balance using Crystal Reports

now for my question about the Cumulative Balance. when we run bp account balance in SBO let say the date range is from 12/1/2010 - 1/14/2011(today), in the column cumulative balance there is an OB in the 1st row. how will i get this amount using crystal reports.

please help.

Fidel

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Jan 15, 2011 at 12:45 AM

    Something like this:

    SELECT

    T1.ShortName,

    SUM(T1.Credit - T1.Debit) 'Open Balance'

    FROM OJDT T0

    INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

    WHERE T1.ShortName = '[%0\]' and t0.RefDate <= '[%1\]'

    GROUP BY T1.ShortName

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      hello Gordon,

      thanks a lot, it works this time., but i use you previous post to get directly the sum of Credit-Debit, i have figured out what was my wrong and causes the error above. ill post my solution for for the others that may face the same error i have.

      for the date Parameter is {?ParamName} without single quote

      for the string parameter is '{?ParamName}' with single quote.

      best regards,

      Fidel

  • avatar image
    Former Member
    Jan 14, 2011 at 05:05 PM

    Hi Fidel,

    This OB is calculated from the first transaction posted to the account until the date you need OB.

    You need another query as command for this OB amount.

    Thanks,

    Gordon

    Add comment
    10|10000 characters needed characters exceeded