how to get the cumulative value in crystal reports

[former_member541807]

hi all,

this is the continuation of my question located at

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

[Former Member · Accepted solution]

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

[former_member541807 · Accepted solution]

hi Gordon,

i have pasted your code to the crystal report command and created 2 parameters (BPNumber, OBDate respectively) when ask to enter values i enter the ff. values

BPnumber = V0078

OBDate = 12/1/2010

but i got this error,

Failed to retrieve data from the database

Details: ADO Error code:0x

Source: Microsoft OLE DB Provider for SQL Server

Description: Invalid column name 'V0078'

SQL State: 42s22

Native Error: [Database Vendor code: 207]

thanks

fidel

[Former Member · Accepted solution]

I have forgot this is for CR. In CR's case, you need to get all value by a Command query:

SELECT

T1.ShortName,t0.RefDATE, T1.Credit, T1.Debit

FROM OJDT T0

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

Use parameters and formula to calculate the OB.

[former_member541807 · Accepted solution]

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

[Former Member]

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

[former_member541807]

hi Gordon,

i dont have any idea how will i do this. please help me to achieve this one.

thanks.

fidel