How to display Opening Balance

Hi Ramco,

You can use below query to get your achivable result.

-- Opening Balance
SELECT 
    'Op Bal' AS Ac#,
    NULL AS Date,
    NULL AS Debit,
    SUM(Credit) - SUM(Debit) AS Credit
FROM table1
WHERE Date < '2015-01-02'
  AND Ac# = 1

UNION ALL

-- Transactions in date range
SELECT 
    Ac#,
    Date,
    Debit,
    Credit
FROM table1
WHERE Date >= '2015-01-02'
  AND Date <= '2015-01-03'
  AND Ac# = 1
ORDER BY 
    CASE WHEN Date IS NULL THEN 0 ELSE 1 END,
    Date
 

In your Crystal Report, you need to include the formula I provided in my earlier response.

Hi Jignesh

  Iam not getting through S.P. Where i should place formula so that first Opening Balance gets displayed then Detailstransactions during the period should get displayed.

Thanks

Hello Ramco,

If your store procedure return correct values then you need to display on crystal report then please use below one,

If the "Op Bal" row does not directly appear in the dataset, create a formula to calculate it within Crystal Reports:

if {table1.Date} < {?StartDate} then 
    {table1.Credit} - {table1.Debit}
else 
    0