带有期初和期末余额的收支水晶报表 [英] Crystal Report for Income and Expense wth opening and closing balance

问题描述

我需要使用mssql附加的输出并显示在水晶报表中

I need the output attached using mssql and display in crystal report

我的表名称为Income，Expense，Expense Details HeadMaster。

表结构

I have tables name Income, Expense, Expense Details HeadMaster.
Table Structure

HeadMaster

HeadMasterId

名称

HeadMaster
     HeadMasterId
     Name

收入

InMasterID

HeadMasterId

IncomeDetails

TxnDate

金额

Income
     IncomeId
     HeadMasterId
     IncomeDetails
     TxnDate
     Amount

费用

ExpenseId

HeadMasterId

ExpDetails

TxnDate

金额

Expense
     ExpenseId
     HeadMasterId
     ExpDetails
     TxnDate
     Amount

ExpenseDetails

ExpenseDetailsId

ExpenseId

CenterId

金额

ExpenseDetails
     ExpenseDetailsId
     ExpenseId
     CenterId
     Amount

我写了3个查询
1。收入详细信息

SELECT TxnDate, Name AS Particular, Amount FROM Income I INNER JOIN HeadMaster
HM ON HM.HeadMasterId = I.HeadMasterId WHERE TxnDate >= '2014-02-01 00:00:00' AND
TxnDate <= '2014-03-15 23:59:59' ORDER BY TxnDate 

2。费用明细

SELECT TxnDate, Name AS Particular, Amount FROM Expense E INNER JOIN ExpenseDetails 
ED ON ED.ExpenseId = E.ExpenseId INNER JOIN HeadMaster HM ON 
HM.HeadMasterId = E.HeadMasterId WHERE TxnDate >= '2014-02-01 00:00:00' AND 
TxnDate <= '2014-03-15 23:59:59' ORDER BY TxnDate 

3。期初余额

DECLARE @Expense NUMERIC(8,2)
DECLARE @Income NUMERIC(8,2)
DECLARE @Balance NUMERIC(8,2)
SELECT @Income = SUM(I.Amount) FROM Income WHERE TxnDate < '2014-02-01 00:00:00'
SELECT @Expense = SUM(I.Amount) FROM Expense WHERE TxnDate < '2014-02-01 00:00:00
SET @Balance = @Income - @Expense
SELECT @Balance AS OpeningBalance

如何合并查询输出，使其与Crystal Report中所需的输出类似？
还可以将此查询合并为一个查询吗？

How can Merge the query output to resemble my required output in Crystal Report?
Also Can I merge this query's into one single query?

我正在使用Visual Studio IDE 2012 for Win App，使用C＃和Crystal Report。

I am using Visual Studio IDE 2012 for Win App using C# and Crystal Report.

输出应如下所示

推荐答案

代替编写多个查询，您可以在Crystal报表本身中执行所需的操作。

Instead of writing the multiple queries.. you can do the required operation in crystal report itself.

1. 因为您分别有收入和费用。取得一个主报告，并在该主报告中并排放置2个子报告的详细信息部分。


2. 在子报告1中，获取收入。在建立连接时获取所有必需的表，并在Database Expert的链接选项卡中链接这些表。


3. 类似地，在第二个子报表中，将 Expences 表，并按照相同的过程链接表。

1. Since you have Income and Expences separately. Take a main report and in that main report place 2 sub reports side by side in detail section.
2. In sub report1 take the information of Income. Take all the required tables while making connection and link those tables in Links Tab of Database Expert.
3. Similarly in second sub report take Expences tables and follow the same process to link the tables.

Link1 和 Link2 来了解如何链接表。

Link1 and Link2 to know how to link the tables.

4。现在两个子报表中的分组如下：

4.Now in both the sub reports make groups as following:

Group1: Sub report1 `Income`..
        Sub Report2  `Expences`

5。现在，在两个子报表中都创建第二个组，如下所示：

5. Now create 2nd group in both the sub reports as following:

Group 2: Sub Report1: Date
         Sub Report2: Date

6。现在在两个子报告中创建第三个组，如下所示：

6.Now create 3rd group in both sub reports as following:

Group 3: Sub Report1: Balance
         Sub Report2: Balance

7。在详细信息中，将字段 Date ， Specials 和 Amount ，并根据需要在组页脚中获取摘要。

7.In details place the fields Date, Particulars and Amount and take the summary in group footers as required.

8。要获得准确的输出，如屏幕截图<$ c $在列

8.To get exact output as screenshot draw lines between the columns

之间的c>线​​，让我知道它的运行方式。

Let me know how it goes.

这篇关于带有期初和期末余额的收支水晶报表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！