Crystal报表创建收据注册报表 [英] Crystal Report creating a receipt register report
问题描述
我正在创建收据注册报告。我在SQL Server 2008中有一个数据库，我有表tblReceipt与以下字段：
** RefNo
AccCode1
AccTitle1
借记卡
AccCode2
AccTitle2
信用卡**
b $ b
如果我插入一个交易的例子，有人捐赠 $ 500 with referenceNumber：0001 ，有人支付他的债务 ReferenceNumber：0002价值$ 600 那么我需要在会计中记录这些。
** DEBIT（$ 500）** **现金（AccountTitle）**和**信用（$ 500）* * to **捐赠（AccountTitle）**
是
**债务（$ 600）**在**现金**和**信用（ - $ 600）**在**债务**
，表格将如下所示：
RefNo AccCode1 AccTitle1 借记 AccCode2 AccTitle2 信用
0001 1101 现金手续 $ 500 3100 捐款 - $ 500
0002 1101 ; 现金手续 $ 600 3200 债务 - $ 600
在水晶报表中，我设计的样子如下：
RefNo 手头现金[1101] 捐赠[3100] 欠款[3200]
0001 $ 500 ;
- $ 500
0002 $ 600 ;
- $ 600
现在，如果我们查看两个记录，他们是正确的。但我的问题是当有人捐赠，并在单笔交易中支付他的债务。我的表格如下所示：
RefNo AccCode1 AccTitle1 借记 AccCode2 AccTitle2 信用
0001 1101 现金手续 $ 500 3100 捐款 - $ 500
0002 1101 ; 现金手续 $ 600 3200 债务 - $ 600
0003 1101
; 现金手续 $ 1100 3100 捐款 - $ 500
0003 1101
; 现金手续 $ 1100 3100 债务 - $ 600
但我的水晶报告看起来像这样
RefNo ;手头现金[1101] 捐赠[3100] 欠款[3200]
0001 $ 500 ; - $ 500
0002 $ 600 ; ; - $ 600
0003 $ 1100 ; - $ 500
0003 $ 1100 ; ; - $ 600
这不是我想要的。因为它重复了引用号。并且如果我将尝试计算帐户的总额（手上的现金），我将有一个错误的输出。
我想我的报告看起来像这样。 p>
RefNo 手头现金[1101] 捐赠[3100] 欠款[3200]
0001 $ 500 ; - $ 500
0002 $ 600 ; ; - $ 600
0003 $ 1100 ; - $ 500 ; - $ 600
如何实现？有任何想法吗？提前感谢
注意：
执行以下过程以获取必需的输出：
$ b b
-
使用
RefNo和supress创建一个标题。 -
创建3个公式
@Cash on Hand，@捐赠和@Debt并放入详细信息并按详细信息部分
@Cash on Hand
if（AccCode1 = 1101）和ISNULL b then 0
else手头现金
@捐款
if（AccCode2 = 3100）和ISNULL（捐赠）
0
else捐赠
@Debt
if（AccCode2 = 3200）和ISNULL（Debt）
0
else Debt
在页脚中有3个公式，您可以得到所需的输出。
编辑--------------------- -------------------------------------------------- ------------------
将参考号放在
现在现金在组脚注中写下面的公式。
sum（{@ Cash on Hand}，{Refgroup}）/ Count（{Ref}，{Refgrop}）
I'm creating a receipt register report. I have a database in SQL Server 2008 and I have table tblReceipt with the following fields:
**RefNo
AccCode1
AccTitle1
Debit
AccCode2
AccTitle2
Credit**
If I insert a transactions for examples someone donated $500 with ReferenceNumber: 0001 and somebody pay for his debt with ReferenceNumber: 0002 worth $600 then I need to record these in accounting.
The first ex. is
**DEBIT($500)** in **Cash on Hand(AccountTitle)** and **Credit(-$500)** to **Donation(AccountTitle)**
The second ex. is
**DEBIT($600)** in **Cash on hand** and **Credit(-$600)** in **Debt**
and the table will looks like this
RefNo AccCode1 AccTitle1 Debit AccCode2 AccTitle2 Credit
0001 1101 Cash on Hand $500 3100 Donation -$500
0002 1101 Cash on Hand $600 3200 Debt -$600
and In crystal report I design it look like this
RefNo Cash on Hand[1101] Donation[3100] Debt[3200]
0001 $500 -$500
0002 $600 -$600
Now if we look in the two records they are correct. But my problem is when someone donated and also pay his debt in a single transaction. My table looks like this
RefNo AccCode1 AccTitle1 Debit AccCode2 AccTitle2 Credit
0001 1101 Cash on Hand $500 3100 Donation -$500
0002 1101 Cash on Hand $600 3200 Debt -$600
0003 1101 Cash on Hand $1100 3100 Donation -$500
0003 1101 Cash on Hand $1100 3100 Debt -$600
But my crystal report looks like this
RefNo Cash on Hand [1101] Donation [3100] Debt [3200]
0001 $500 -$500
0002 $600 -$600
0003 $1100 -$500
0003 $1100 -$600
This is not what I want. Because its duplicate the referencenumber. and also If I will try to compute the total of the account(Cash on Hand) I will have a wrong output.
I want my report look like this.
RefNo Cash on Hand [1101] Donation [3100] Debt [3200]
0001 $500 -$500
0002 $600 -$600
0003 $1100 -$500 -$600
How can I achieve this? Any ideas? Thanks in advance
Note: examples in your question are confusing below answer is the example for implementation
follow below process to get required output:
Create a group with
RefNoandsupressthe header.Create 3 formulas
@Cash on Hand,@ Donationand@Debtand place in details and supress the detail section
@Cash on Hand
if(AccCode1=1101) and ISNULL(Cash on Hand)
then 0
else Cash on Hand
@ Donation
if (AccCode2=3100) and ISNULL( Donation )
the 0
else Donation
@Debt
if (AccCode2=3200) and ISNULL( Debt )
the 0
else Debt
now take the summary of 3 formulas in the footer and you get the required output.
Edit-----------------------------------------------------------------------------------------
Place Ref no in detail to the leftmost part of the report.
now for Cash in Hand in group footer write below formula.
sum({@Cash on Hand},{Refgroup})/Count({Ref},{Refgrop})
这篇关于Crystal报表创建收据注册报表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
