how to create a stored procedure to get opening balance from database to a date range crystal report

Question

I have a database with debit and credit column. I've created a crystal report from this database as a date range report and a new column added using formulas for balance. Now I need a row to get the previous day balance from database to my report.For this option i need to create a custom stored procedure. Can anyone help me to solve this problem

Answer 1

In general I suggest you not to use Crystal for extending the data you have in the database (adding columns, formulas, etc). Crystal scripts (for my experience) is cumbersome, unobvious and hardly-maintainable. Thereby I suggest you wherever it possible to use SQL - create and extend data-model through views and use these views as data-source for Crystal reports.

As for your specific case, if for example you have the table with columns debit and credit and you need to extend your data with additional column balance and previous_balance, the solution might look something like this:

create table your_table (id int, [day] date, credit float, debit float);

insert into your_table (id, [day], credit, debit) values
(1, '2014-12-04', 0.0, 340.00),
(2, '2014-12-05', 10000.0, 0.00),
(3, '2014-12-06', 7666.18, 89.00),
(4, '2014-12-07', 0.0, 990.00);

-- create view dbo.your_table_extension as
select t1.[day]
, t1.credit
, t1.debit
, t1.credit - t1.debit [balance]            --> balance formula
, t2.credit - t2.debit [previous_balance]   --> prev day balance formula
from your_table t1
left join your_table t2 on dateadd(day, -1, t1.[day]) = t2.[day]
  and t1.id != t2.id

Result:

DAY         CREDIT  DEBIT   BALANCE PREVIOUS_BALANCE
----------------------------------------------------
2014-12-04  0       340     -340    (null)
2014-12-05  10000   0       10000   -340
2014-12-06  7666.18 89      7577.18 10000
2014-12-07  0       990     -990    7577.18

Check SQLFiddle