Crystal Report for Income and Expense wth opening

问题:

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

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

HeadMaster
     HeadMasterId
     Name

Income
     IncomeId
     HeadMasterId
     IncomeDetails
     TxnDate
     Amount

Expense
     ExpenseId
     HeadMasterId
     ExpDetails
     TxnDate
     Amount

ExpenseDetails
     ExpenseDetailsId
     ExpenseId
     CenterId
     Amount

I have written 3 querys
1. Income Details

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. Expense Details

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. Opening Balance

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

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?

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

Output should look like these

回答1:

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

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 and Link2 to know how to link the tables.

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

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

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

Group 2: Sub Report1: Date
         Sub Report2: Date

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

Group 3: Sub Report1: Balance
         Sub Report2: Balance

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

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

Let me know how it goes.