获取Crystal Reports中的累积余额列(Getting accumulated balance column in Crystal Reports)

获取Crystal Reports中的累积余额列(Getting accumulated balance column in Crystal Reports)

我正在努力建造一个水晶。 我正在使用sql命令从数据库中检索数据。 以下是我的命令......

SELECT sum_id, sum_date, id, sum_accname, sum_description, credit, debit, dep_date, chq_due_date, dp_custname FROM sum_balance WHERE sum_accname = {?ac_name} and sum_date >= {?fromDate} and sum_date <={?toDate}

我的报告就像我附上的图像。

我的问题是如何使用借方和贷方添加余额列。 余额列应为累计余额列。 SQL dB中借记和贷记列的数据类型为[credit] [decimal]（18,2）和[debit] [decimal]（18,2）。 真的我被困在这个地方。 任何人都可以帮我解决这个问题。 另一件事是我使用两个参数来过滤日期。 如果我过滤报告，我还需要为报告添加Brough前向余额

I am trying to build a crystal. I am using sql command to retreive datas from database. Following is my command...

SELECT sum_id, sum_date, id, sum_accname, sum_description, credit, debit, dep_date, chq_due_date, dp_custname FROM sum_balance WHERE sum_accname = {?ac_name} and sum_date >= {?fromDate} and sum_date <={?toDate}

My report is like the image I've attached.

My problem is how to add the balance column using debit and credit. Balance column should be an accumulated balance column. Data types of debit and credit column in SQL dB are [credit] [decimal](18, 2) & [debit] [decimal](18, 2). Really I am stuck in this place. Can anyone help me with this issue. And the other thing is I am using two parameter to filter date. If I filter the report I need to add an Brough forward balance for the report too

最满意答案

我想你可以使用这个查询：

SELECT s1.sum_id, s1.sum_date, s1.id, s1.sum_accname, s1.sum_description, s1.credit, s1.debit, s1.dep_date, s1.chq_due_date, s1.dp_custname, SUM(ISNULL(s2.debit, 0) - ISNULL(s2.credit, 0)) As balance FROM sum_balance s1 LEFT JOIN sum_balance s2 ON s1.id >= s2.id AND s1.sum_id >= s2.sum_id AND s1.sum_date >= s2.sum_date WHERE s1.sum_accname = {?ac_name} and s1.sum_date >= {?fromDate} and s1.sum_date <={?toDate} GROUP BY s1.sum_id, s1.sum_date, s1.id, s1.sum_accname, s1.sum_description, s1.credit, s1.debit, s1.dep_date, s1.chq_due_date, s1.dp_custname

I think you can use this query for that:

SELECT s1.sum_id, s1.sum_date, s1.id, s1.sum_accname, s1.sum_description, s1.credit, s1.debit, s1.dep_date, s1.chq_due_date, s1.dp_custname, SUM(ISNULL(s2.debit, 0) - ISNULL(s2.credit, 0)) As balance FROM sum_balance s1 LEFT JOIN sum_balance s2 ON s1.id >= s2.id AND s1.sum_id >= s2.sum_id AND s1.sum_date >= s2.sum_date WHERE s1.sum_accname = {?ac_name} and s1.sum_date >= {?fromDate} and s1.sum_date <={?toDate} GROUP BY s1.sum_id, s1.sum_date, s1.id, s1.sum_accname, s1.sum_description, s1.credit, s1.debit, s1.dep_date, s1.chq_due_date, s1.dp_custname